Key design decisions:
sanitize_text() normalizes whitespace, case, accents
Inner COALESCE converts NULL → empty string
Outer COALESCE on CONCAT handles all-NULL rows
to_hex() converts binary MD5 to readable string
NULL-safe hashing = consistent results.
MD5(COALESCE(CONCAT( COALESCE(sanitize_text(column1), ''), COALESCE(sanitize_text(column2), ''), COALESCE(sanitize_text(column3), '') )))
Not all columns matter for uniqueness.
✓ Hash only business data columns
This prevents false negatives where same business data appears "different" due to metadata.
Same data = same hash, regardless of when/where it was imported. Like Git commits - content determines identity. Source lineage tracked separately in pivot tables.
Traditional dedup: Compare row IDs or timestamps (fragile, arbitrary)
Content-addressable dedup: Hash the actual data content
Content-addressable deduplication at scale: MD5 hash all columns (minus metadata) → find hash collisions → save duplicates to _log table → keep one record. Implemented as BigQuery stored procedures. Track everything, delete nothing permanently. Easy rollback if needed.
The AI doesn't replace human judgment - it triages at scale. Humans focus on the 5% that truly needs expertise, not the 95% that's tedious pattern matching.
This is the future of data cleaning: semantic understanding + programmatic validation + comprehensive audit trails.
Traditional ETL: Write 50+ regex rules, maintain edge case dictionary, manual QA team reviews thousands of records.
LLM-powered ETL: One prompt template, automatic fuzzy matching, quarantine pattern for edge cases, 95%+ automation rate
Response validation: Parse JSON, check all values exist in official set, flag mismatches. One prompt template, infinite reuse.
Prompts are stored as JSON configs in /models/prompts/ - not hardcoded. The AI gets:
List of messy values: ["Madrd", "barcelona ", "VALENCIA"]
Official reference set: ["Madrid", "Barcelona", "Valencia", ...]
Task: Return JSON mapping dirty → clean
Stage 4: Extract invalid records to _invalid_provinces table
Stage 5: Fill remaining nulls with most common province (or default to "Madrid")
Every stage logs its actions. Every change is tracked.
Stage 1: Fill null provinces with city names when available
Stage 2: Send unique provinces to Claude with official province list → get mapping dict
Stage 3: Validate AI mappings against official set (reject hallucinated provinces)
Manual cleaning doesn't scale. LLMs understand context and can fuzzy match with semantic understanding of what users meant to type.
Real-world data is messy. Users type "Madrd", "madrid", "MADRID", "Mdrid", "Madrid, España" instead of "Madrid". Traditional string matching fails. RegEx is brittle.
Track every change in before/after comparison tables. Human review only for edge cases that AI can't handle.
Using LLMs for data standardization at scale:
Send messy province names to Claude with an official list → get back a mapping dictionary → validate programmatically → quarantine unmappable records to separate tables.
AGI?
New Anthropic research: Signs of introspection in LLMs.
Can language models recognize their own internal thoughts? Or do they just make up plausible answers when asked about them? We found evidence for genuine—though limited—introspective capabilities in Claude.
ALT An example in which Claude Opus 4.1 detects a concept being injected into its activations.