surface↓cleaning↓normalize_quantity
Cleaningscalar · returns json

NORMALIZE_QUANTITY

Parse and normalize a quantity expression to a structured JSON

Per-row — runs once for each row.

cleaningllmtext

Syntax

NORMALIZE_QUANTITY({{ value }})
NORMALIZE_QUANTITY({{ value }}, '{{ system }}')

Arguments

nametypedescription
valueVARCHAR—
system(optional)VARCHAR—

About

Parse a quantity expression (with or without units) into a structured JSON object, optionally converting to a target unit system. Backend: deterministic Python via `pint`, the de facto unit-aware quantity library. Handles: - Simple: "500 mg" → value=500, unit='milligram' - Compound: "5 lb 8 oz" → normalized to one unit - Ranges: "2-3 cups" → range_min/range_max - Approximations: "about 5 gallons", "~5 gal" → is_approximate=true - Fractions: "1/2 cup" → value=0.5 - Free-text number phrases for simple cases: "half a pound" The `system` argument can be: - "metric" → normalize to SI (kg, g, mg, m, cm, L, mL, …) - "imperial" → normalize to imperial (lb, oz, ft, in, gal, …) - "auto" → keep the input's native unit and also populate `normalized` with the metric equivalent For LLM-style parsing (unusual quantity phrasings, domain-specific abbreviations, mixed natural language), use NORMALIZE_QUANTITY_LLM — see normalize_quantity_single_llm.cascade.yaml.

Examples

Simple quantity parsed with metric unit

SELECT
  normalize_quantity ('500 mg')

Imperial-to-metric conversion preserves unit type

SELECT
  normalize_quantity ('about 5 gallons', 'metric')

Nearby rabbit holes

same domain
scalar

ANONYMIZE

Remove or mask personally identifiable information from text

cleaningllmtext
scalar

AS_SMART

Type-cast messy real-world values that trip up standard CAST

cleaningllmtext
scalar

CANONICAL

Return the canonical/official form of a value (auto-detects entity type)

cleaningllmtext
scalar

CLEAN_YEAR

Extracts 4-digit year from messy text, returns -1 if undetermined

cleaningllmdate
scalar

CLEAN_YEAR_LLM

LLM-backed year extraction (escape hatch for CLEAN_YEAR)

cleaningllmllm-escape-hatchdate
aggregate

COALESCE_SMART

Pick the best non-null value from a group (quality-aware COALESCE)

cleaningllmtext
Climb back to The Looking Glass