surface↓cleaning↓normalize_currency
Cleaningscalar · returns json

NORMALIZE_CURRENCY

Parse currency to structured JSON (format normalization only)

Per-row — runs once for each row.

cleaningllmcurrency

Syntax

NORMALIZE_CURRENCY({{ value }})
NORMALIZE_CURRENCY({{ value }}, '{{ target_currency }}')
NORMALIZE_CURRENCY({{ value }}, '{{ target_currency }}', '{{ reference_date }}')

Arguments

nametypedescription
valueVARCHAR—
target_currency(optional)VARCHAR—
reference_date(optional)VARCHAR—

About

Parse a currency value into a structured JSON object (amount, currency code, symbol, formatted). Backend: deterministic Python via `babel.numbers` for amount parsing and a small hand-curated ISO 4217 symbol map for currency detection. Handles: - Symbols: "$100" → USD, "€100" → EUR, "£100" → GBP, "¥100" → JPY, "₹100" → INR, "R$100" → BRL, "CHF 100" → CHF - Codes: "100 USD", "USD 100", "100 EUR" - US format: "1,234.56" - European format: "1.234,56" or "1 234,56" - Negative amounts: "-100", "(100)" **Important:** This cascade does **not** do exchange rate conversion. The previous LLM version produced fake "approximate" exchange rates from stale training data — that's actively misleading for anything financial. For real conversion, point `target_currency` at an external forex API at the application layer and use NORMALIZE_CURRENCY only for format normalization + currency detection. The JSON output always contains the ORIGINAL amount and currency. If `target_currency` differs from the detected one, the result will have `converted_amount=null` and `conversion_note` explaining the limitation. For LLM-style conversion-with-plausible-rates, use NORMALIZE_CURRENCY_LLM — see normalize_currency_single_llm.cascade.yaml.

Examples

US dollar symbol detected

SELECT
  normalize_currency ('$100.50')

European number format + currency code

SELECT
  normalize_currency ('EUR 1.234,56')

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