surface↓cleaning↓default_smart
Cleaningscalar · returns varchar

DEFAULT_SMART

Provide context-aware defaults for null/empty values (smarter COALESCE)

Per-row — runs once for each row.

cleaningllmtext

Syntax

DEFAULT_SMART({{ value }}, '{{ context }}')
{{ value }} DEFAULT '{{ context }}'

Arguments

nametypedescription
valueVARCHAR—
contextVARCHARContext: field type, related values, business rules

About

Provide an intelligent default for a null or empty value, tailored to the field's type and the surrounding context — not a single global fallback. Useful when a hardcoded `COALESCE(col, 'unknown')` is too blunt: a missing country code for an address in "Vancouver, BC" should default to "CA", not "unknown". Pass a context string describing what the field is for and the operator returns a sensible typed default. For plain nulls with a constant fallback, stick with SQL's COALESCE. Use DEFAULT_SMART only when the default should depend on the row's other values.

Examples

Default value suggested

SELECT
  default_smart ('missing', 'country field')

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