surface↓cleaning↓as_smart
Cleaningscalar · returns varchar

AS_SMART

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

Per-row — runs once for each row.

cleaningllmtext

Syntax

CAST_SMART({{ value }}, '{{ target_type }}')
{{ value }} AS_SMART {{ target_type }}

Arguments

nametypedescription
valueVARCHAR—
target_typeVARCHARTarget type: INTEGER, DECIMAL, DATE, TIMESTAMP, BOOLEAN, VARCHAR

About

Cast a messy real-world value to a target type without choking on formatting quirks. Where SQL's `CAST` fails on "1,234.56", "USD 1500", "yesterday", or "yes"/"y"/"1", CAST_SMART reads the intent and returns the right typed value. Useful when ingesting CSV exports, scraped data, spreadsheet dumps, or user-submitted forms where values come in every format a human might type. Supports the usual DuckDB types (INTEGER, DOUBLE, DATE, BOOLEAN, TIMESTAMP, etc.). If your data is already clean, stick with the built-in `CAST` — every cleanup value passed through CAST_SMART costs an LLM call. Use it where the mess is real.

Examples

String to integer

SELECT
  cast_smart ('25', 'integer')

Nearby rabbit holes

same domain
scalar

ANONYMIZE

Remove or mask personally identifiable information from text

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
scalar

COMPLETE

Fill in missing parts of a partial value using context

cleaningllmtext
Climb back to The Looking Glass