Glossary

  • Task (Snowflake): a scheduled SQL statement or procedure call. Created suspended.
  • Scheduled query (BigQuery): a query the Data Transfer Service runs on a schedule. Runs as its creator unless you give it a service account.
  • Service account: a non-human identity a job runs as, so it doesn't depend on one person's access.
  • Data metric function (DMF): a Snowflake function that measures one quality property of a table, such as nulls in a column, on the table's schedule.
  • Data quality scan: BigQuery's rule-based quality check, configured with rules such as setExpectation (value must be in a list) and rangeExpectation.
  • ignoreNull: a data quality rule setting. When false, the default, a null row fails the rule.
  • threshold: the share of rows that must pass for a rule to pass. Defaults to 1.0.
  • AI_COMPLETE / AI.GENERATE_TABLE: the SQL functions that call a language model from inside Snowflake and BigQuery. Both accept a schema for the answer.
  • Structured output: a model response constrained to a schema you supply.
  • Backfill: filling missing values in rows that already exist.
  • Provenance columns: columns that record where a value came from: source, correction, or AI fill, and which model and prompt.
  • LLM-as-a-judge: using a second language model to score the output of the first.
  • Groundedness: a judge metric. How well a statement is supported by the source text it was drawn from.
  • TruLens: an open-source library for tracing and evaluating LLM applications. Snowflake runs its metrics server-side; elsewhere you call it from Python.
  • Hand-checked sample: a set of outputs a person has labelled right or wrong. The only thing a judge can be graded against.
  • Quarantine: a holding table for rows the pipeline can't make right on its own. Better than a confident wrong value.

The as-is

Say you inherit an orders table. The load breaks now and then, and someone reruns it by hand. A spreadsheet of Monday corrections gets pasted in. The city column has "SP", "São Paulo", "Sao Paulo" and "sao paulo", and about one row in ten is blank because nobody could work out the city.

Nothing in that table fails. Nothing in it is traceable either. And the moment somebody says "let's just have the AI fill the blanks", you've got one more way for a row to be wrong that looks exactly like a row that's right.

The fix is a path: four steps, in order. Snowflake and BigQuery each have a name for every one of them, and both are below. Under each step there's also a piece of a single Python file that runs on DuckDB with synthetic data, so you can watch each step do its job without a cloud account. Every output block from that file is a real run, seeded, and reproduces exactly. The Snowflake and BigQuery snippets follow the vendor docs linked in each section. I haven't run them for this article.

First, the as-is, and the checks it passes:

""" From hand fixes to an audited AI backfill, on a laptop. Synthetic data, seeded, so every number below is reproducible. Runs on DuckDB so the SQL reads the same as it would on Snowflake or BigQuery. pip install duckdb jsonschema """ import hashlib import random import duckdb import jsonschema random.seed(7) con = duckdb.connect() # --------------------------------------------------------------------------- # AS-IS: 1,000 orders, the city column the way a hand-maintained table ends up. # --------------------------------------------------------------------------- CITIES = ["São Paulo", "Campinas", "Santos", "Rio de Janeiro", "Belo Horizonte"] SP_SPELLINGS = ["São Paulo", "Sao Paulo", "SP", "sao paulo"] # same city, four ways  def make_order(i): true_city = random.choices(CITIES, weights=[50, 15, 10, 15, 10])[0] kind = "clean" note = f"Entregar na portaria, {true_city}" city = random.choice(SP_SPELLINGS) if true_city == "São Paulo" else true_city r = random.random() if r < 0.06: # blank, but the note names the city  city, kind = None, "blank_clear" elif r < 0.09: # blank, and the note names two cities  city, kind = None, "blank_ambiguous" other = random.choice([c for c in CITIES if c != true_city]) note = f"Cliente de {other}, entregar no escritório em {true_city}" elif r < 0.10: # blank, and the note names no city at all  city, kind = None, "blank_empty" note = "Entregar na portaria" return (i, city, note, true_city, kind) rows = [make_order(i) for i in range(1, 1001)] con.execute("""CREATE TABLE raw_orders (order_id INT, city VARCHAR, delivery_note VARCHAR, true_city VARCHAR, kind VARCHAR)""") con.executemany("INSERT INTO raw_orders VALUES (?, ?, ?, ?, ?)", rows) print("== AS-IS: the usual checks ==") print(con.execute(""" SELECT COUNT(*) AS row_count, COUNT(DISTINCT order_id) AS distinct_ids, COUNT(*) - COUNT(order_id) AS null_ids FROM raw_orders """).fetchall()) print(con.execute(""" SELECT city, COUNT(*) AS orders FROM raw_orders GROUP BY city ORDER BY orders DESC """).fetchall()) 
== AS-IS: the usual checks == [(1000, 1000, 0)] [('Campinas', 131), ('sao paulo', 129), ('São Paulo', 123), ('Rio de Janeiro', 123), ('Sao Paulo', 117), ('SP', 115), (None, 111), ('Belo Horizonte', 85), ('Santos', 66)]

A thousand rows, a thousand distinct IDs, no null keys. Every count a dashboard would show is green. And by city, Campinas is your biggest market, which it isn't: São Paulo is split four ways and adds up to 484. The true_city and kind columns are the answer key a synthetic dataset gets to have. Your real table doesn't, and that's the whole problem.

1. Automate the load

Every later step reads from this one. You can't check, fill or judge a table someone still edits by hand, because you can't tell the edit from the data.

Two things make a load automated rather than scheduled. It runs without a person. And corrections arrive as data the load consumes, not as edits to the table it produces.

On Snowflake that's a task. The detail that catches people: newly created tasks are created suspended. Nothing runs until you resume it.

-- the rebuild-from-raw logic lives in a view (same SQL as the Python below); -- the task only materialises it CREATE OR REPLACE TASK load_orders WAREHOUSE = transform_wh SCHEDULE = '60 MINUTE' AS CREATE OR REPLACE TABLE orders AS SELECT * FROM orders_clean_v; ALTER TASK load_orders RESUME; -- without this line, the task exists and never runs

On BigQuery it's a scheduled query. Its detail: unless you give it a service account, it runs with the credentials of the person who created it. Give it one.

bq mk \ --transfer_config \ --target_dataset=mydataset \ --display_name='load_orders' \ --schedule='every 24 hours' \ --params='{"query":"SELECT * FROM mydataset.orders_clean_v","destination_table_name_template":"orders","write_disposition":"WRITE_TRUNCATE"}' \ --data_source=scheduled_query \ --service_account_name=loader@myproject.iam.gserviceaccount.com

WRITE_TRUNCATE isn't decoration. A load that rebuilds the target from raw every time is safe to run twice, and that is what lets you stop being afraid of it. Here's the same idea locally, with an alias table for the spellings and a corrections feed instead of a spreadsheet:

# --------------------------------------------------------------------------- # STEP 1: Automate the load. One code path from raw to clean, no hand edits. # Corrections arrive as data (a feed table), not as edits to the target. # --------------------------------------------------------------------------- con.execute("""CREATE TABLE city_aliases AS SELECT * FROM (VALUES ('são paulo','São Paulo'), ('sao paulo','São Paulo'), ('sp','São Paulo'), ('campinas','Campinas'), ('santos','Santos'), ('rio de janeiro','Rio de Janeiro'), ('belo horizonte','Belo Horizonte') ) t(alias, city)""") con.execute("CREATE TABLE corrections (order_id INT, city VARCHAR, reason VARCHAR)") con.execute("INSERT INTO corrections VALUES (42, 'Santos', 'customer called support')") def load_orders(): """The whole load. Safe to run twice: it rebuilds from raw every time.""" con.execute(""" CREATE OR REPLACE TABLE orders AS SELECT r.order_id, COALESCE(c.city, a.city) AS city, CASE WHEN c.city IS NOT NULL THEN 'correction' WHEN a.city IS NOT NULL THEN 'source' ELSE NULL END AS city_source, r.delivery_note FROM raw_orders r LEFT JOIN city_aliases a ON lower(trim(r.city)) = a.alias LEFT JOIN corrections c ON c.order_id = r.order_id """) # a fingerprint of the whole table, so "same result" is checked, not assumed  return con.execute("""SELECT COUNT(*), md5(string_agg(order_id || ':' || coalesce(city,'∅'), ',' ORDER BY order_id)) FROM orders""").fetchone() print("\n== STEP 1: run the load twice ==") print("run 1:", load_orders()) print("run 2:", load_orders()) print(con.execute("""SELECT city, COUNT(*) FROM orders GROUP BY city ORDER BY 2 DESC""").fetchall()) 
== STEP 1: run the load twice == run 1: (1000, '9fb6899493deff927b480901368c59d5') run 2: (1000, '9fb6899493deff927b480901368c59d5') [('São Paulo', 484), ('Campinas', 130), ('Rio de Janeiro', 123), (None, 111), ('Belo Horizonte', 85), ('Santos', 67)]

Same fingerprint twice. São Paulo is one city again. The correction for order 42 moved one row from Campinas to Santos, and the row says so in city_source. That column is the start of provenance, and step 3 depends on it.

2. Check quality where it lands

Now the 111 blanks are a measured property of the table instead of a feeling.

On Snowflake, quality checks are data metric functions. They run on the table's schedule, one hour by default, and every DMF on that table shares that one schedule. You can't run the null check every five minutes and the accepted-values check daily on the same table.

ALTER TABLE orders SET DATA_METRIC_SCHEDULE = '60 MINUTE'; -- built-in: nulls in the column ALTER TABLE orders ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (city); -- custom: values outside the list you accept CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.unknown_city_count( arg_t TABLE(arg_city VARCHAR) ) RETURNS NUMBER AS $$ SELECT COUNT(*) FROM arg_t WHERE arg_city NOT IN ('São Paulo','Campinas','Santos','Rio de Janeiro','Belo Horizonte') $$; ALTER TABLE orders ADD DATA METRIC FUNCTION governance.dmfs.unknown_city_count ON (city); -- results land here; measurement_time is when the check actually ran SELECT measurement_time, metric_name, argument_names, value FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS WHERE table_name = 'ORDERS' ORDER BY measurement_time DESC;

On BigQuery, the same check is a rule in a data quality scan. Google's default here is the right one, and worth knowing before someone changes it: rows with null values automatically fail a rule unless ignoreNull is true. Set it to true and nulls are, in Google's words, "trivially considered passing."

# spec.yaml rules: - column: city dimension: VALIDITY setExpectation: values: ['São Paulo', 'Campinas', 'Santos', 'Rio de Janeiro', 'Belo Horizonte'] ignoreNull: false threshold: 1.0 
gcloud dataplex datascans create data-quality orders-city-check \ --location=us-central1 \ --data-quality-spec-file=spec.yaml \ --data-source-resource=//bigquery.googleapis.com/projects/myproject/datasets/mydataset/tables/orders

The same rule locally, so you can watch what one flag does:

# --------------------------------------------------------------------------- # STEP 2: Check quality where it lands. # An accepted-values rule, the same shape as a BigQuery setExpectation rule. # --------------------------------------------------------------------------- def accepted_values_rule(column, values, ignore_null, threshold=1.0): """Pass ratio the way the rule defines it: passing rows / total rows. ignore_null=False mirrors the documented default: a null row fails.""" in_list = ", ".join(f"'{v}'" for v in values) null_outcome = "1" if ignore_null else "0" passing, total = con.execute(f""" SELECT SUM(CASE WHEN {column} IS NULL THEN {null_outcome} WHEN {column} IN ({in_list}) THEN 1 ELSE 0 END), COUNT(*) FROM orders""").fetchone() ratio = passing / total return round(ratio, 3), "PASS" if ratio >= threshold else "FAIL" print("\n== STEP 2: accepted-values rule on city ==") print("ignoreNull = true :", accepted_values_rule("city", CITIES, ignore_null=True)) print("ignoreNull = false:", accepted_values_rule("city", CITIES, ignore_null=False)) 
== STEP 2: accepted-values rule on city == ignoreNull = true : (1.0, 'PASS') ignoreNull = false: (0.889, 'FAIL')

Same table, same 111 blanks, opposite verdicts. The failing one is telling the truth. Hold on to that, because in the next step a table with 42 wrong values is going to pass this exact rule.

3. Backfill with AI, and label it as AI

This is the step everyone wants to start with, and it's the one with the trap.

Both platforms let you hand the model a schema. On Snowflake, AI_COMPLETE takes a response_format, and Snowflake's docs describe what it does with it: it "verifies each generated token against your structured output definition."

SELECT order_id, AI_COMPLETE( model => 'mistral-large2', prompt => 'Return the delivery city named in this note: ' || delivery_note, response_format => { 'type': 'json', 'schema': {'type': 'object', 'properties': {'city': {'type': 'string'}}, 'required': ['city']} } ) AS fill FROM orders WHERE city IS NULL;

On BigQuery, AI.GENERATE_TABLE takes an output_schema, written like a CREATE TABLE column list, against a remote model you've created over Gemini:

SELECT * FROM AI.GENERATE_TABLE( MODEL `mydataset.gemini_model`, ( SELECT order_id, CONCAT('Return the delivery city named in this note: ', delivery_note) AS prompt FROM mydataset.orders WHERE city IS NULL ), STRUCT('city STRING' AS output_schema) );

Here's the trap. A schema check proves the shape of the answer. It can never prove the value. "Campinas" is a perfectly valid string. So is the wrong city.

Locally, stand_in_model plays the model. It isn't an LLM and doesn't pretend to be one: it takes the first city it finds in the note, and guesses São Paulo when there's none. That's a fair impression of a cheap model on an ambiguous note, and it lets the run reproduce without an API key.

# --------------------------------------------------------------------------- # STEP 3: Backfill with AI, and label it as AI. # stand_in_model is NOT an LLM. It behaves like a cheap one on this task: # it returns the first city it can find in the note, or its best guess. # Swap in AI_COMPLETE or AI.GENERATE_TABLE and the shape of the problem is the same. # --------------------------------------------------------------------------- FILL_SCHEMA = {"type": "object", "properties": {"city": {"type": "string"}}, "required": ["city"]} PROMPT = "Return the delivery city named in this note: {note}" MODEL = "stand-in-v1" def stand_in_model(note): found = [c for c in CITIES if c in note] found.sort(key=note.index) # first mention wins  return {"city": found[0] if found else "São Paulo"} # no city? guess the big one  blanks = con.execute("SELECT order_id, delivery_note FROM orders WHERE city IS NULL").fetchall() fills, schema_failures = [], 0 for order_id, note in blanks: answer = stand_in_model(note) try: jsonschema.validate(answer, FILL_SCHEMA) # the check the platforms give you  except jsonschema.ValidationError: schema_failures += 1 continue prompt_hash = hashlib.sha256(PROMPT.format(note=note).encode()).hexdigest()[:12] fills.append((order_id, answer["city"], MODEL, prompt_hash)) con.execute("CREATE TABLE fills (order_id INT, city VARCHAR, fill_model VARCHAR, prompt_hash VARCHAR)") con.executemany("INSERT INTO fills VALUES (?, ?, ?, ?)", fills) con.execute(""" CREATE OR REPLACE TABLE orders AS SELECT o.order_id, COALESCE(o.city, f.city) AS city, CASE WHEN f.city IS NOT NULL THEN 'ai_fill' ELSE o.city_source END AS city_source, f.fill_model, f.prompt_hash, o.delivery_note, CAST(NULL AS DOUBLE) AS judge_score FROM orders o LEFT JOIN fills f USING (order_id) """) print("\n== STEP 3: the backfill ==") print("blanks:", len(blanks), "| filled:", len(fills), "| schema failures:", schema_failures) print("accepted-values rule now:", accepted_values_rule("city", CITIES, ignore_null=False)) # the answer key only a synthetic dataset has print(con.execute(""" SELECT r.kind, COUNT(*) AS fills, SUM(CASE WHEN o.city <> r.true_city THEN 1 ELSE 0 END) AS wrong FROM orders o JOIN raw_orders r USING (order_id) WHERE o.city_source = 'ai_fill' GROUP BY r.kind ORDER BY r.kind """).fetchall()) 
== STEP 3: the backfill == blanks: 111 | filled: 111 | schema failures: 0 accepted-values rule now: (1.0, 'PASS') [('blank_ambiguous', 36, 36), ('blank_clear', 67, 0), ('blank_empty', 8, 6)]

Read that slowly. Every blank filled, no schema failures, and the quality rule that failed at 0.889 now passes at 1.0. Meanwhile 42 of the 111 fills are wrong: all 36 where the note named two cities, and 6 of the 8 where it named none. The check didn't get weaker. The table got more confident.

That's why the step title says label it as AI. The city_source, fill_model and prompt_hash columns are what keep a fill from passing as a source value. Without them, the 42 wrong rows look exactly like the 484 São Paulo rows that came from the source. With them, you can at least ask the question. And a trap you can't query for is a warning, not a tool, so here's the query:

# The trap query. Every AI-filled value that nothing has checked beyond its shape. TRAP_QUERY = """ SELECT order_id, city, fill_model, delivery_note FROM orders WHERE city_source = 'ai_fill' AND (judge_score IS NULL -- never judged OR strpos(lower(delivery_note), lower(city)) = 0) -- value not in its own source """ print("unchecked fills:", len(con.execute(TRAP_QUERY).fetchall())) print("of those, value not in the note:", con.execute(f"SELECT COUNT(*) FROM ({TRAP_QUERY}) WHERE strpos(lower(delivery_note), lower(city)) = 0").fetchone()[0]) 
unchecked fills: 111 of those, value not in the note: 8

The SQL is plain enough to port to either warehouse; check your platform's substring function when you do. Notice the second line. The cheap check, "is the value even in its own source?", catches the 8 guesses. It can't catch the 36 ambiguous ones, because the wrong city really is in the note. That's the job for step 4.

One Snowflake detail for the comments: when a field marked required can't be extracted, AI_COMPLETE raises an error rather than inventing one. That protects you from the empty answer. It doesn't protect you from the confident wrong one.

4. Judge the fill with a second model

...