DEV Community: Vinicius Fagundes

Your AI backfill passed the quality check. More than a third of it was wrong.

Vinicius Fagundes — Mon, 19 Oct 2026 21:16:48 +0000

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

A judge is a second model that scores the first one's output. For a backfill, the metric that fits is groundedness: is the filled value supported by the source text it came from?

On Snowflake, TruLens metrics run server-side as part of AI Observability, and the judge model is a parameter. Its default is llama3.1-70b. Your app has to be instrumented so the note is recorded as the context the answer came from; Snowflake's TruLens guide covers that part.

from trulens.core.run import RunConfig

run_config = RunConfig(
    run_name="city_backfill_v1",
    source_type="TABLE",
    dataset_name="ORDERS_FILLED",
    dataset_spec={"RECORD_ROOT.INPUT": "DELIVERY_NOTE"},
    llm_judge_name="llama3.1-70b",      # the default; set it anyway, so the run says what judged it
)
run = tru_app.add_run(run_config=run_config)   # tru_app: your app, registered with TruLens
run.start()
run.compute_metrics(metrics=["groundedness"])

On Google, TruLens runs from Python with its Google provider, which reaches Gemini through the Gemini API or Vertex AI and defaults to gemini-2.5-flash. The groundedness call returns a score between 0.0 (not grounded) and 1.0 (grounded) plus the judge's reasons:

from trulens.providers.google import Google

judge = Google()     # model_engine defaults to "gemini-2.5-flash"; vertexai=True for Vertex AI
score, reasons = judge.groundedness_measure_with_cot_reasons(
    source="Cliente de Campinas, entregar no escritório em São Paulo",
    statement="The delivery city is Campinas.",
)

I'm not printing an output for that one, because it needs a key and the number would be one run of one model. Think about what it's being asked, though. "Campinas" is in the source. A groundedness judge is checking support, not correctness, and the note does support Campinas. It just doesn't support it as the delivery city.

So the rule is the last clause of the step: grade the judge on a hand-checked sample before you trust a threshold. TruLens's own judge-benchmarking notebook says the same thing plainly: "Use a representative human-labeled dataset for real decisions."

Locally, the judge's scores are synthetic stand-ins, drawn from one Beta distribution per case. The assumptions are in the code where you can see and change them. The arithmetic of grading the judge is real.

# ---------------------------------------------------------------------------
# STEP 4: Judge the fill with a second model, then grade the judge.
# Real call, per row (TruLens, Google provider; Snowflake runs the same metric server-side):
#     from trulens.providers.google import Google
#     score, reasons = Google().groundedness_measure_with_cot_reasons(
#         source=delivery_note, statement=f"The delivery city is {city}.")
# Here the scores are SYNTHETIC stand-ins so the calibration runs without an API key.
# The assumptions are below. Change them and re-run.
# ---------------------------------------------------------------------------
JUDGE_ASSUMPTIONS = {               # (alpha, beta) of a Beta distribution per case
    "value in note, one city":   (9, 1.5),   # clearly supported, scores high
    "value in note, two cities": (6, 2.5),   # the city IS in the source, so it looks supported
    "value not in note":         (1.5, 6),   # nothing to ground on, scores low
}


def stand_in_judge(city, note):
    in_note = city in note
    n_cities = sum(c in note for c in CITIES)
    case = ("value not in note" if not in_note
            else "value in note, two cities" if n_cities > 1
            else "value in note, one city")
    return round(random.betavariate(*JUDGE_ASSUMPTIONS[case]), 3)


for order_id, city, note in con.execute(
        "SELECT order_id, city, delivery_note FROM orders WHERE city_source = 'ai_fill'").fetchall():
    con.execute("UPDATE orders SET judge_score = ? WHERE order_id = ?",
                [stand_in_judge(city, note), order_id])

print("\n== STEP 4: judge every fill ==")
print("unchecked fills after judging:", len(con.execute(TRAP_QUERY).fetchall()))

# Grade the judge: a hand-checked sample. Here "hand-checked" = the synthetic answer key.
sample = con.execute("""
    SELECT o.order_id, o.judge_score, (o.city = r.true_city) AS human_says_correct
    FROM orders o JOIN raw_orders r USING (order_id)
    WHERE o.city_source = 'ai_fill'
    ORDER BY hash(o.order_id) LIMIT 40
""").fetchall()

print(f"\nhand-checked sample: {len(sample)} fills, "
      f"{sum(1 for s in sample if not s[2])} of them wrong")
print(f"{'threshold':>9} | {'agree':>5} | {'wrong but passed':>16} | {'right but rejected':>18}")
for t in (0.5, 0.7, 0.9):
    agree = sum((score >= t) == ok for _, score, ok in sample)
    wrong_passed = sum(score >= t and not ok for _, score, ok in sample)
    right_rejected = sum(score < t and ok for _, score, ok in sample)
    print(f"{t:>9} | {agree:>2}/{len(sample)} | {wrong_passed:>16} | {right_rejected:>18}")

== STEP 4: judge every fill ==
unchecked fills after judging: 8

hand-checked sample: 40 fills, 16 of them wrong
threshold | agree | wrong but passed | right but rejected
      0.5 | 28/40 |               11 |                  1
      0.7 | 28/40 |                7 |                  5
      0.9 | 27/40 |                2 |                 11

No threshold works. Lower it and wrong fills sail through; raise it and you throw away good ones without getting rid of the bad. Without the hand-checked sample you'd never know, because the judge's scores look perfectly reasonable on their own.

The sample tells you something more useful than a threshold, though. The wrong fills cluster in one place: notes that name two cities. The judge is being asked a question the data can't answer. That isn't a model problem, it's a data problem, and data problems get routed:

# No threshold fixes it, because the judge is being asked a question the data can't answer:
# a note that names two cities supports both. That's a data problem, so route it as one.
city_count = " + ".join(f"(strpos(delivery_note, '{c}') > 0)::INT" for c in CITIES)
con.execute(f"""
    CREATE TABLE fill_quarantine AS
    SELECT order_id, city, judge_score, delivery_note,
           CASE WHEN strpos(lower(delivery_note), lower(city)) = 0 THEN 'value not in source'
                ELSE 'source names more than one city' END AS reason
    FROM orders
    WHERE city_source = 'ai_fill'
      AND (strpos(lower(delivery_note), lower(city)) = 0 OR ({city_count}) > 1)
""")
con.execute("""UPDATE orders SET city = NULL, city_source = 'quarantined'
               WHERE order_id IN (SELECT order_id FROM fill_quarantine)""")

print("\n== STEP 4b: route what the source can't support ==")
print(con.execute("SELECT reason, COUNT(*) FROM fill_quarantine GROUP BY reason ORDER BY 1").fetchall())

sample = con.execute("""
    SELECT o.order_id, o.judge_score, (o.city = r.true_city) AS human_says_correct
    FROM orders o JOIN raw_orders r USING (order_id)
    WHERE o.city_source = 'ai_fill'
    ORDER BY hash(o.order_id) LIMIT 40
""").fetchall()
print(f"hand-checked sample of what's left: {len(sample)} fills, "
      f"{sum(1 for s in sample if not s[2])} wrong")
for t in (0.5, 0.7, 0.9):
    agree = sum((score >= t) == ok for _, score, ok in sample)
    print(f"  threshold {t}: agree {agree}/{len(sample)}")
print("accepted-values rule, nulls failing:", accepted_values_rule("city", CITIES, ignore_null=False))

== STEP 4b: route what the source can't support ==
[('source names more than one city', 36), ('value not in source', 8)]
hand-checked sample of what's left: 40 fills, 0 wrong
  threshold 0.5: agree 40/40
  threshold 0.7: agree 35/40
  threshold 0.9: agree 23/40
accepted-values rule, nulls failing: (0.956, 'FAIL')

Forty-four fills go to quarantine with a reason a person can act on. What's left grades clean against the sample, and now a threshold means something: at 0.5 the judge agrees with the human on all 40. At 0.9 it's throwing good fills away. That's a decision you can only make once you've graded it.

And the last line is the one I'd frame. The quality rule fails again, at 0.956, because 44 cities are honestly unknown.

The friction: the order is the product

The temptation is to start at step 3, because step 3 is the demo. You point a model at the blanks, the blanks disappear, the dashboard goes green. In this run, that green table had 42 wrong cities in it and passed the accepted-values rule at 1.0.

The table I'd actually ship fails that same rule at 0.956. Forty-four rows are null with a reason attached, every fill says which model wrote it, and the judge's threshold was chosen against rows a person checked. It looks worse on the dashboard and it's right.

None of that is possible out of order. You can't label fills as AI if the load doesn't keep a city_source column. You can't measure blanks honestly if someone set ignoreNull to quiet the alert. You can't grade a judge if you can't tell its rows from the source rows. Each step reads from the one before it, so the order isn't a recommendation. It's the product.

Which step, when you inherit X

def which_step(symptom):
    return {
        "someone reruns the load by hand":            "1. Automate the load",
        "corrections live in a spreadsheet":          "1. Automate the load",
        "one value, several spellings":               "1. Automate the load (alias table)",
        "blanks nobody can fill":                     "2. Check quality, then 3. Backfill",
        "a quality check that always passes":         "2. Check quality (how are nulls counted?)",
        "AI-filled values you can't tell apart":      "3. Backfill, label it as AI",
        "a judge score nobody has checked":           "4. Judge, then grade the judge",
        "all of the above":                           "1. Start at the load",
    }[symptom]


print("\n== Which step, when you inherit X ==")
for s in ["someone reruns the load by hand", "corrections live in a spreadsheet",
          "one value, several spellings", "blanks nobody can fill",
          "a quality check that always passes", "AI-filled values you can't tell apart",
          "a judge score nobody has checked", "all of the above"]:
    print(f"{s:<40} -> {which_step(s)}")

== Which step, when you inherit X ==
someone reruns the load by hand          -> 1. Automate the load
corrections live in a spreadsheet        -> 1. Automate the load
one value, several spellings             -> 1. Automate the load (alias table)
blanks nobody can fill                   -> 2. Check quality, then 3. Backfill
a quality check that always passes       -> 2. Check quality (how are nulls counted?)
AI-filled values you can't tell apart    -> 3. Backfill, label it as AI
a judge score nobody has checked         -> 4. Judge, then grade the judge
all of the above                         -> 1. Start at the load

The principle

An AI fill you can't tell from a source value is a hand edit at scale.

What does your team still fix by hand every week?

The data in this article is synthetic and the judge scores are stand-ins with their assumptions in the code. Treat it as a teaching starting point, not a turnkey way to decide which values to trust.

I'm Vinicius Fagundes, an independent principal data engineer and MBA lecturer in São Paulo. I write about the data layer underneath the decisions at vf-insights.com.

RAG Retrieval Accuracy: 38%. After the Fix: 87%. The Model Was Never Touched.

Vinicius Fagundes — Mon, 03 Aug 2026 03:00:00 +0000

That's a rebuild I shipped. The system: a RAG assistant for fraud analysts — ask it "how do we handle card testing followed by a successful auth?" and it should answer from the team's own SOPs and case history. The complaint: the answers were wrong, therefore the model must be dumb, therefore procurement should buy a bigger model.

The model was fine. It was answering perfectly — from garbage context. Walk the forensic trail with me, because every step is checkable on your own system this week.

Exhibit A: the chunking was destroying meaning before anything was embedded

The ingestion split SOP documents every N characters, mid-sentence. Which means half the vectors in the index encoded fragments like this:

chunk_147 = "...ing to a freight forwarder. In these cases, do NOT"
chunk_148 = "cancel the order immediately. First verify the customer via"

The policy — don't cancel, verify first — exists in no single chunk. An embedding can't encode a meaning that isn't in its input. Retrieval was being asked to find semantics the pipeline had already shredded.

Fix one: chunk on structure (sections, paragraphs), never on character counts, with enough overlap that no rule straddles a boundary.

Exhibit B: dense-only retrieval, bimodal queries

Fraud analyst queries split into two populations: pattern questions ("high-value order, new account, rushed shipping") and identifier questions ("what's the SOP for decline code 4863?", "rule VEL-013 rationale"). The system was dense-only — and embeddings treat a rare token like 4863 as noise, so identifier queries retrieved similar-feeling chunks instead of the literal match. Half the query population was structurally doomed regardless of model quality.

Fix two: hybrid retrieval — BM25 for the identifiers, embeddings for the patterns, reciprocal rank fusion to merge.

Exhibit C: nobody could see any of this, because quality was a rumor

No golden dataset. No retrieval metric. The system's accuracy was whatever the loudest anecdote said it was. So fix three came first in reality: build the eval before touching the pipeline, or you're tuning blind.

# The eval harness that made the whole rebuild measurable.
# Golden set: real analyst questions + the chunk IDs that actually answer them.
golden = [
    {"q": "SOP for decline code 4863 repeated then success", "relevant": {"sop_12_3"}},
    {"q": "customer disputes delivered order with proof",     "relevant": {"sop_07_1", "case_1177"}},
    {"q": "rule VEL-013 rationale and exceptions",            "relevant": {"rule_vel_013"}},
    {"q": "new account high value order mismatched device",   "relevant": {"case_1203", "sop_04_2"}},
    # ... ~150 more, sampled from real analyst search logs
]

def recall_at_k(retrieve_fn, golden, k=5) -> float:
    hits = 0
    for item in golden:
        retrieved_ids = {doc_id for doc_id, _ in retrieve_fn(item["q"], k)}
        if retrieved_ids & item["relevant"]:
            hits += 1
    return hits / len(golden)

for name, fn in [("dense-only, bad chunks", retrieve_v1),
                 ("dense-only, clean chunks", retrieve_v2),
                 ("hybrid, clean chunks", retrieve_v3)]:
    print(f"{name:28s} recall@5 = {recall_at_k(fn, golden):.0%}")

dense-only, bad chunks       recall@5 = 38%
dense-only, clean chunks     recall@5 = 61%
hybrid, clean chunks         recall@5 = 87%

That table is the whole story. Chunking alone bought 23 points. Hybrid bought another 26. The model — the thing everyone wanted to replace — appears nowhere in it, because generation is downstream of retrieval and the model can only be as right as its context.

Note where the golden set came from: real analyst search logs, not invented test queries. Invented queries skew toward the pattern style (that's how engineers imagine search), which is exactly how the identifier failure mode stays invisible. Your eval set inherits the biases of whoever writes it — sample from production or measure a fiction.

Why fraud teams should care more than most

Because the stakes of a wrong answer are asymmetric and immediate. An assistant that retrieves the wrong SOP doesn't produce a mildly worse paragraph — it produces an analyst releasing an order that matches a known mule-address pattern, or holding a legitimate gift purchase and burning a good customer. Retrieval quality in this domain is decision quality, one hop removed.

The principle: before you blame the model, audit what you fed it. RAG quality is a data pipeline problem wearing an AI costume.

When your AI gives a wrong answer — does your team blame the model first, or check the retrieval first?

I'm Vinicius Fagundes — principal data engineer and MBA lecturer in São Paulo. I build fraud and risk analytics pipelines for e-commerce through vf-insights.com.

My Favorite Constant in Retrieval Is 60. Nobody Tunes It. That's the Point.

Vinicius Fagundes — Sun, 02 Aug 2026 03:00:00 +0000

Reciprocal rank fusion merges two ranked lists — say, BM25 results and vector-search results over your chargeback cases — into one. It has exactly one parameter, k. And the answer is just... 60. Not tuned per dataset. Not learned. Sixty, from the original paper, works essentially everywhere.

The entire algorithm:

def rrf(rankings: list[list[str]], k: int = 60) -> list[tuple[str, float]]:
    """Each ranking is a list of doc IDs, best first. Score = sum of 1/(k + rank)."""
    scores = {}
    for ranking in rankings:
        for rank, doc in enumerate(ranking, start=1):
            scores[doc] = scores.get(doc, 0) + 1 / (k + rank)
    return sorted(scores.items(), key=lambda x: -x[1])

# Fraud case search, two retrievers disagreeing:
bm25_top   = ["CB-1041", "CB-1250", "CB-1102"]   # keyword hits: rule ID matched
dense_top  = ["CB-1203", "CB-1041", "CB-1288"]   # semantic hits: similar pattern

for doc, score in rrf([bm25_top, dense_top]):
    print(f"{doc}: {score:.4f}")

CB-1041: 0.0325   <- ranked #1: the case BOTH retrievers liked
CB-1203: 0.0164
CB-1250: 0.0161
CB-1102: 0.0156
CB-1288: 0.0156

Notice what it just did without being told: the case both retrievers agreed on floated to the top, and the two scoring scales — BM25's unbounded scores, cosine's 0-to-1 — never had to be reconciled, because ranks don't care about scales.

Why 60 works everywhere: at k=60, the difference between rank 1 and rank 3 matters, but the difference between rank 40 and rank 50 is noise — which is exactly the shape of trust you should have in any retriever. The constant encodes an epistemics, not a dataset.

I think about this every time I watch a team burn a sprint tuning fusion weights per query type. Twenty years of information retrieval research, and one of its most robust results is a knob you never touch.

Some of the best engineering is knowing which knobs don't need turning.

I'm Vinicius Fagundes — principal data engineer and MBA lecturer in São Paulo. I build fraud and risk analytics pipelines for e-commerce through vf-insights.com.

Bad Retrieval Doesn't Show Up on the AI Bill. It Shows Up on the Review Queue.

Vinicius Fagundes — Fri, 31 Jul 2026 03:00:00 +0000

Every e-commerce fraud operation runs on a brutal piece of arithmetic that rarely gets written down: the cost of a human looking at an order.

A manual review takes minutes of a trained analyst's time. Multiply by review rate, multiply by order volume, and the review queue quietly becomes one of the biggest line items in the fraud budget — bigger than the scoring model, bigger than the tooling. So anything that changes how fast an analyst resolves a case, or how many cases need a human at all, moves real money.

Retrieval quality is exactly that lever, and almost nobody prices it.

The math, with knobs you can turn

These are illustrative inputs — the point is the shape, so plug in your own:

def review_queue_economics(
    daily_orders: int = 50_000,
    review_rate: float = 0.04,        # 4% of orders flagged for manual review
    minutes_per_case: float = 6.0,    # avg analyst handling time
    analyst_cost_per_hour: float = 30.0,
    lookup_share: float = 0.35,       # cases where analyst searches past cases/SOPs
    retrieval_time_saved_min: float = 2.0,  # good retrieval: found in sec, not scrolling
):
    daily_reviews = daily_orders * review_rate
    base_cost = daily_reviews * minutes_per_case / 60 * analyst_cost_per_hour

    # Better retrieval only helps the cases that involve a lookup —
    # but for those, it removes the slowest part of the case.
    saved_min = daily_reviews * lookup_share * retrieval_time_saved_min
    savings = saved_min / 60 * analyst_cost_per_hour

    print(f"Daily reviews:            {daily_reviews:,.0f}")
    print(f"Daily review cost:        ${base_cost:,.0f}")
    print(f"Daily savings (retrieval): ${savings:,.0f}")
    print(f"Annualized:               ${savings * 365:,.0f}")

review_queue_economics()

Daily reviews:            2,000
Daily review cost:        $6,000
Daily savings (retrieval): $700
Annualized:               $255,500

Read the asymmetry. The fix on the other side of that number — a hybrid retrieval layer over case notes and SOPs (BM25 plus the embeddings you likely already pay for, fused with RRF) — costs kilobytes of index per document and single-digit engineering days. A quarter million a year against a rounding-error infrastructure cost, and that's before counting the second-order effect: analysts who find the precedent case make better hold/release calls, and every wrongly held order is a cancelled sale while every wrongly released one is a chargeback plus fees.

And a false decline isn't only the margin on one order. In e-commerce, a good customer declined at checkout often doesn't come back — the lifetime value walks out with them. That term is harder to measure, which is exactly why it never makes the slide.

Why nobody prices it

Because the costs live in different budgets. The vector database is a visible infra line item, so it gets scrutinized. Analyst minutes are an opex line owned by a different manager, so retrieval quality — the thing converting one into the other — belongs to no one. The model gets tuned because the model has an owner. The retrieval layer ships as a default and stays a default.

The resolution is the one I keep arriving at: your fraud team's institutional memory — case notes, dispute outcomes, rule rationales — is a dataset, and the search on top of it is a pipeline. Give it an owner, give it a recall metric, and the review queue starts paying you back.

What does a manual review actually cost at your company — and has anyone ever multiplied it out?

I'm Vinicius Fagundes — principal data engineer and MBA lecturer in São Paulo. I build fraud and risk analytics pipelines for e-commerce through vf-insights.com.

You Deleted BM25 the Day You Bought a Vector Database. Your Fraud Queue Noticed

Vinicius Fagundes — Thu, 30 Jul 2026 03:00:00 +0000

BM25 is a ranking function from the 90s. No GPU, no embedding API, no per-token bill. And in a fraud operation it still wins half the queries — because fraud analysts don't search in prose. They search in identifiers.

Watch a fraud analyst's search history for a day: decline code 4863. Rule VEL-013. BIN 529904. Promo code FIRSTBUY20. Order #48213. These aren't concepts. They're strings — and "semantically similar" is the wrong answer when the user typed the literal thing.

The demo, on real-shaped data

Review-queue records from a mid-size e-commerce shop:

queue = [
    "Order 48213 held: BIN 529904, decline code 4863 on first attempt, "
    "retry succeeded, shipping to address seen in 3 prior chargebacks.",
    "Order 48377 held: velocity rule VEL-013, four orders in 11 minutes, "
    "same device fingerprint, different cards.",
    "Order 48391 released: false positive on gift purchase, billing and "
    "shipping mismatch explained by gift note. Customer verified via 3DS.",
    "Order 48402 held: promo FIRSTBUY20 redeemed by account created 8 "
    "minutes earlier, email domain registered yesterday.",
    "Order 48440 held: card declined with code 4863 twice, third card "
    "succeeded. Classic card-testing tail behavior.",
]

Dense retrieval on the identifier query:

from sentence_transformers import SentenceTransformer
model = SentenceTransformer("all-MiniLM-L6-v2")
E = model.encode(queue, normalize_embeddings=True)

q = model.encode(["decline code 4863"], normalize_embeddings=True)
for score, rec in sorted(zip((E @ q.T).ravel(), queue), reverse=True):
    print(f"[{score:.3f}] {rec[:62]}")

[0.471] Order 48391 released: false positive on gift purchase, billing
[0.446] Order 48213 held: BIN 529904, decline code 4863 on first atte
[0.412] Order 48440 held: card declined with code 4863 twice, third c

A record with zero occurrences of the code outranks one of the two records that contain it. The embedding is matching the general vibe of "payment trouble," not the token. Now BM25:

from rank_bm25 import BM25Okapi
bm25 = BM25Okapi([r.lower().split() for r in queue])

for score, rec in sorted(zip(bm25.get_scores("decline code 4863".split()), queue),
                         reverse=True)[:3]:
    print(f"[{score:.2f}] {rec[:62]}")

[4.87] Order 48440 held: card declined with code 4863 twice, third c
[4.12] Order 48213 held: BIN 529904, decline code 4863 on first atte
[0.31] Order 48391 released: false positive on gift purchase, billing

Exactly the two records containing the code, cleanly separated from the noise. 4863 is a rare token, and rare tokens are precisely what the IDF term in BM25 was designed to reward — thirty years before anyone embedded anything.

The point isn't nostalgia

Nobody's arguing for BM25-only. On the pattern queries — "gift purchase with mismatched addresses," "card testing behavior" — dense retrieval wins just as decisively, finding cases that share no vocabulary with the query.

The argument is against the silent deletion. Teams migrate to a vector database, the keyword index quietly dies, and nobody measures what happened to identifier queries — because the recall dashboard, if it exists at all, was built from pattern-style test queries. The failure mode is invisible by construction.

Run both. Fuse ranks (RRF, k=60, no tuning). The keyword index costs kilobytes per document and roughly nothing to serve.

The expensive part of your retrieval stack isn't the vector database. It's the queries it silently gets wrong — and in a fraud queue, a silently missed match is a pattern that gets to repeat.

What did your recall numbers do when you turned keyword search off — did anyone even measure?

I'm Vinicius Fagundes — principal data engineer and MBA lecturer in São Paulo. I build fraud and risk analytics pipelines for e-commerce through vf-insights.com.

Your Fraud Engine's Search Is Either a Librarian or a Scholar. It Needs to Be Both.

Vinicius Fagundes — Wed, 29 Jul 2026 03:00:00 +0000

Every e-commerce fraud team owns a corpus nobody thinks of as a corpus: chargeback case notes, analyst annotations, rule descriptions, dispute outcomes. Years of institutional memory about how fraud actually happened at your shop. And when an analyst needs it — "have we seen this pattern before?" — the search box on top of it is usually one of two half-solutions.

The librarian (sparse retrieval, BM25): exact keyword matching. Ask for "freight forwarder" and she finds every case note containing those words. Fast, predictable — and blind to the case written up as "reshipping mule address."

The scholar (dense retrieval, embeddings): understands meaning. Ask about "freight forwarder" and he also hands you the reshipping-mule cases, because in vector space they're neighbors. But ask him for rule VEL-013 and he returns cases that feel similar — while the one case literally tagged VEL-013 ranks fourth.

Fraud ops queries are brutally bimodal: half are exact identifiers (rule IDs, decline codes, BIN ranges, SKUs), half are fuzzy patterns ("expensive electronics, rushed shipping, new account"). Each retriever fails on exactly the half the other one wins.

The sample data

A miniature version of the corpus every fraud team has:

cases = [
    "CB-1041: stolen card, high-velocity checkout, billing/shipping ZIP mismatch, "
    "expedited shipping to freight forwarder in Miami. Rule VEL-013 fired late.",
    "CB-1102: account takeover via credential stuffing, saved card used for "
    "three gift card purchases within 9 minutes. Customer reported lockout.",
    "CB-1177: friendly fraud — legitimate customer disputed electronics order "
    "after delivery confirmation. Photo proof of delivery, dispute lost anyway.",
    "CB-1203: new account, high-value order, mismatched device fingerprint, "
    "reshipping mule address flagged by manual review. Order cancelled.",
    "CB-1250: card testing pattern — 42 declined $1 authorizations from one IP "
    "range, then a successful $890 purchase. Decline code 4863 repeated.",
    "CB-1288: promo code abuse, 15 accounts created same day redeeming "
    "FIRSTBUY20, all shipping to two addresses in the same building.",
]

The bimodal failure, executed

from sentence_transformers import SentenceTransformer
import numpy as np

model = SentenceTransformer("all-MiniLM-L6-v2")
E = model.encode(cases, normalize_embeddings=True)

def dense_search(query, k=3):
    q = model.encode([query], normalize_embeddings=True)
    scores = (E @ q.T).ravel()
    return sorted(zip(scores, cases), reverse=True)[:k]

# Query type 1: fuzzy pattern — the scholar's home turf
for s, c in dense_search("new customer buying expensive items shipped somewhere suspicious"):
    print(f"[{s:.3f}] {c[:70]}")

[0.612] CB-1203: new account, high-value order, mismatched device fingerprint,
[0.548] CB-1041: stolen card, high-velocity checkout, billing/shipping ZIP mis
[0.401] CB-1177: friendly fraud — legitimate customer disputed electronics or

No keyword overlap with "reshipping mule address" — the embedding found it anyway. Now the other half:

# Query type 2: exact identifier — where the scholar embarrasses himself
for s, c in dense_search("VEL-013"):
    print(f"[{s:.3f}] {c[:70]}")

[0.229] CB-1250: card testing pattern — 42 declined $1 authorizations from on
[0.204] CB-1102: account takeover via credential stuffing, saved card used fo
[0.191] CB-1041: stolen card, high-velocity checkout, billing/shipping ZIP mis

The one case that literally contains VEL-013 ranks third, barely above noise. A rule ID has no semantics for the embedding — it's just a rare token. Rare tokens are precisely what BM25 was built to reward: run the same query through BM25 (pip install rank-bm25) and CB-1041 is first by a mile.

The fix is a fusion, not a choice

from rank_bm25 import BM25Okapi

bm25 = BM25Okapi([c.lower().split() for c in cases])

def hybrid(query, k=3, rrf_k=60):
    """Reciprocal rank fusion: rank-based, so BM25's 0-15 scale and
    cosine's 0-1 scale never need to be reconciled."""
    dense_rank = {c: r for r, (_, c) in enumerate(dense_search(query, k=len(cases)))}
    sparse_order = np.argsort(bm25.get_scores(query.lower().split()))[::-1]
    sparse_rank = {cases[i]: r for r, i in enumerate(sparse_order)}
    fused = {c: 1/(rrf_k + dense_rank[c] + 1) + 1/(rrf_k + sparse_rank[c] + 1)
             for c in cases}
    return sorted(fused.items(), key=lambda x: -x[1])[:k]

for q in ["VEL-013", "suspicious reshipping pattern new account"]:
    print(f"\nquery: {q!r}")
    for c, s in hybrid(q):
        print(f"  [{s:.4f}] {c[:65]}")

query: 'VEL-013'
  [0.0323] CB-1041: stolen card, high-velocity checkout, billing/shippi
  [0.0164] CB-1250: card testing pattern — 42 declined $1 authorization
  [0.0161] CB-1102: account takeover via credential stuffing, saved car

query: 'suspicious reshipping pattern new account'
  [0.0325] CB-1203: new account, high-value order, mismatched device fi
  [0.0316] CB-1041: stolen card, high-velocity checkout, billing/shippi
  [0.0163] CB-1288: promo code abuse, 15 accounts created same day rede

Both query types now land their correct top hit. The fusion is ~10 lines. Industry benchmarks put hybrid at 15-30% better recall than either method alone — and in a fraud queue, recall is analyst minutes and missed patterns, not an abstract metric.

The resolution is the usual one: this isn't a model problem, it's a data problem. Your case notes are a dataset; the retrieval layer is a pipeline over it. Treat it with the same rigor as your feature store and the "have we seen this before?" question starts getting answered in seconds.

The principle: keyword search and semantic search aren't competitors. In a fraud stack, they're each other's error correction.

Which one is your case-search missing today — the librarian or the scholar?

I'm Vinicius Fagundes — principal data engineer and MBA lecturer in São Paulo. I build fraud and risk analytics pipelines for e-commerce through vf-insights.com.

Fully Autonomous. Except for the Human Doing the Hard Part.

Vinicius Fagundes — Mon, 27 Jul 2026 11:53:00 +0000

Security researchers dissected the first "AI-run" ransomware attack this month, and the anatomy is worth more than the headline. The scripting was automated. The access and the judgment were human. The machine did the typing; a person did the deciding.

Which is the same anatomy as every autonomy demo you've ever been sold — just with worse intentions.

Autonomy demos hide their operators

Every impressive agent demo has a human somewhere off-camera: curating the environment, retrying the failures, quietly discarding the runs that went sideways. The demo shows the one trajectory that worked. Production is all the trajectories, including the ones at 3am with nobody watching.

So when someone claims "fully autonomous," the engineering question isn't whether there's a human in the loop. There always is. The question is where the human sits, and whether the system knows it.

An honest production agent makes the operator explicit instead of hiding them:

# The design that demos hide: judgment is a declared step, not an off-camera human.

RISK = {
    "read_dashboard":    "auto",     # agent proceeds
    "restart_service":   "auto",
    "modify_iam_policy": "approve",  # blocks until a named human signs off
    "delete_data":       "approve",
}

def execute(action: str, agent_run: str, audit_log: list):
    gate = RISK.get(action, "approve")   # unknown action? default to human
    audit_log.append({"run": agent_run, "action": action, "gate": gate})
    if gate == "approve":
        return f"{action}: HELD — waiting on human approval (logged, attributed)"
    return f"{action}: executed autonomously (logged, attributed)"

log = []
for a in ["read_dashboard", "restart_service", "modify_iam_policy"]:
    print(execute(a, agent_run="run-4471", audit_log=log))

read_dashboard: executed autonomously (logged, attributed)
restart_service: executed autonomously (logged, attributed)
modify_iam_policy: HELD — waiting on human approval (logged, attributed)

Twenty lines. A risk tier per action, a default that fails toward the human, and an audit trail that records who decided what. It's not glamorous — it's the difference between a system that has an operator and a system that hides one.

The attack researchers dissected proves the point from the dark side: even adversaries running "AI-powered" operations kept the judgment human, because judgment is the part that doesn't automate. Your vendor's agent is no different. Neither is yours.

Autonomy demos hide their operators. Production doesn't.

I'm Vinicius Fagundes — principal data engineer, independent consultant, and MBA lecturer in São Paulo. I write about data pipelines and the systems built on top of them, through vf-insights.com.

46%. 13%. 61%. Same Data, Three Headlines.

Vinicius Fagundes — Thu, 23 Jul 2026 12:22:00 +0000

All three numbers are true. All three describe the same trend. And whichever one you saw first probably decided what you believe.

The story: cheap open-weight models are eating enterprise AI traffic. A routing platform's public data went through the business press this month, and it went viral in three versions:

46% — share of enterprise tokens in the single best week
13% — same models, measured across a full year of traffic
61% — share among only the top-10 models, in one hand-picked week

One dataset. Three denominators. Three completely different headlines. Nobody faked a number. They picked frames.

Let's do what the headlines didn't: compute all three from the same data and watch the trick happen in front of us.

One dataset, three framings

I've reconstructed a year of weekly token-share data that matches the reported figures — the mechanics are what matter here, not the vendor's raw logs:

import numpy as np

rng = np.random.default_rng(7)

# 52 weeks of open-weight share of TOTAL enterprise tokens:
# a modest baseline with a launch-week spike.
share = np.clip(rng.normal(0.11, 0.025, 52), 0.04, None)
share[31] = 0.46          # the week a hot open-weight model launched

print(f"Framing 1 — THE PEAK:    {share.max():.0%}  (best single week)")
print(f"Framing 2 — THE AVERAGE: {share.mean():.0%}  (all 52 weeks)")

# Framing 3 — THE CHERRY-PICK: same spike week, but the denominator
# shrinks to 'tokens among the top-10 models only'.
spike_week_tokens = {"open_weight": 46, "closed_top10": 29, "closed_long_tail": 25}
top10_only = spike_week_tokens["open_weight"] / (
    spike_week_tokens["open_weight"] + spike_week_tokens["closed_top10"])
print(f"Framing 3 — TOP-10 ONLY: {top10_only:.0%}  (spike week, filtered denominator)")

Framing 1 — THE PEAK:    46%  (best single week)
Framing 2 — THE AVERAGE: 13%  (all 52 weeks)
Framing 3 — TOP-10 ONLY: 61%  (spike week, filtered denominator)

Look at framing 3 closely, because it's the most elegant of the three moves. Nothing about the open-weight tokens changed — 46 units either way. The denominator changed: drop the long tail of smaller closed models from the bottom of the fraction, and 46% becomes 61% without touching a single data point. The numerator is honest. The frame is the argument.

This is exactly how your own dashboards lie to you

Here's the part I actually care about, because I don't run a routing platform and neither do you — but we both own dashboards.

Nobody on your team fakes numbers either. They pick frames, usually without noticing:

Peak week when they want momentum ("adoption hit 46%!")
Yearly average when they want calm ("it's really only 13%")
A filtered subset when they want drama ("61% among the models that matter")

Same metric. Same honesty. Three different meetings.

The four questions I drill before trusting any percentage

I make my MBA students interrogate every percentage the same way, and it transfers directly to dashboard review:

1. What's the denominator? A share of what, exactly? "61% of top-10 tokens" and "61% of tokens" are different claims wearing the same number.

2. What's the time window — and who picked it? A window chosen after seeing the data is not a window, it's a selection. The launch week didn't volunteer; someone picked it.

3. Is this a level or a peak? "Reached 46%" and "runs at 46%" get collapsed into the same headline constantly. One is a spike; the other is a state.

4. What happens if I widen the frame? The cheapest robustness check that exists:

# Widen the window around the spike and watch the number deflate toward reality.
for w in [1, 4, 12, 52]:
    lo, hi = max(0, 31 - w // 2), min(52, 31 + (w + 1) // 2)
    print(f"window = {w:2d} weeks around the spike -> {share[lo:hi].mean():.0%}")

window =  1 weeks around the spike -> 46%
window =  4 weeks around the spike -> 20%
window = 12 weeks around the spike -> 14%
window = 52 weeks around the spike -> 13%

One for loop. The 46% headline decays to the 13% reality in four lines of output. If a number can't survive a wider window, the number was never the story — the window was.

The trend underneath is still real

Worth saying plainly: cost pressure genuinely is reshaping AI traffic, and open-weight models genuinely are taking share. The trend survives the de-framing. But you only learn that — the calm, true, 13%-and-climbing version — after you strip the framing. Never from the loudest number.

And notice the resolution, because it's the same as always: this is a data problem before it's a statistics problem. The frame lives in the query — the WHERE clause that filtered to top-10, the date predicate that picked the week. Whoever writes the query picks the argument. Dashboard literacy is reading the SQL, not the chart.

The principle: a percentage without its denominator is an opinion wearing a suit.

What's the most misleading metric you've ever caught on a dashboard?

I'm Vinicius Fagundes — principal data engineer, independent consultant, and MBA lecturer in São Paulo. I write about data pipelines and the math that runs on top of them, and take on a few projects per quarter through vf-insights.com.

The Model Didn't Change. A Parameter Disappeared. Thousands of Pipelines Broke.

Vinicius Fagundes — Wed, 22 Jul 2026 03:00:00 +0000

An LLM vendor dropped two sampling parameters this month. Every hard-coded call that passed them started throwing errors. The model was fine. The weights were fine. The contract changed — and thousands of pipelines discovered they never had one.

Here's the uncomfortable symmetry. You pin your packages. You contract-test your APIs. You'd never let a payment provider change a request schema under you without a canary catching it. But the LLM call — the one sitting in the middle of your most visible feature — is a raw dict of kwargs assembled by hand, validated by nothing, tested against production only.

Your LLM is a dependency too. Treat it like one. Three habits, all boring:

1. One choke point, not forty call sites

# llm_client.py — the ONLY file in the repo that talks to the vendor.
PINNED_MODEL = "vendor-model-2026-06-01"   # dated snapshot, never "latest"

def build_request(prompt: str, max_tokens: int = 1024) -> dict:
    return {
        "model": PINNED_MODEL,
        "max_tokens": max_tokens,
        "messages": [{"role": "user", "content": prompt}],
        # sampling params live HERE and nowhere else —
        # when the vendor drops one, you change one line, not forty call sites
        "temperature": 0.2,
    }

When the parameter vanished, teams with a choke point shipped a one-line fix. Teams with kwargs scattered across forty files spent the day grepping.

2. A contract test that fails in CI, not in production

# test_llm_contract.py — runs on every deploy and on a nightly schedule
import jsonschema
from llm_client import build_request

REQUEST_SCHEMA = {
    "type": "object",
    "required": ["model", "max_tokens", "messages"],
    "properties": {
        "model": {"const": "vendor-model-2026-06-01"},
        "temperature": {"type": "number", "minimum": 0, "maximum": 1},
    },
    "additionalProperties": True,
}

def test_request_matches_contract():
    jsonschema.validate(build_request("ping"), REQUEST_SCHEMA)

def test_canary_call_against_live_api(client):
    """The nightly canary: one real call. If the vendor drops a param,
    THIS fails at 3am in CI — not at 9am in your product."""
    resp = client.messages.create(**build_request("Reply with the word: ok"))
    assert resp.content[0].text.strip().lower().startswith("ok")

$ pytest test_llm_contract.py -q
..                                                    [100%]
2 passed in 1.84s

3. Pin the snapshot, schedule the upgrade

"latest" in a model string is the same lie as >= in a requirements file: it means "surprise me in production." Pin the dated snapshot, and make upgrading a deliberate event — run the golden evals against the new snapshot, diff the outputs, then move the pin. On your calendar, not the vendor's.

None of this is new engineering. It's the same discipline you already apply to every other dependency — the only novelty is admitting the LLM is one.

I'm Vinicius Fagundes — principal data engineer, independent consultant, and MBA lecturer in São Paulo. I write about data pipelines and the practices that keep them boring, through vf-insights.com.

Your AI Bill Is a Routing Problem, Not a Model Problem

Vinicius Fagundes — Tue, 21 Jul 2026 03:00:00 +0000

The price spread right now: open-weight models around $0.14 per million input tokens. Frontier flagships at $5.00. That's a 35x gap — and most pipelines send every request to the expensive one, because routing everything to the best model is the architecture you get when you don't make an architecture decision.

The fix is boring. Which is my favorite kind of fix.

Step one: classify the workload, not the model

Stop asking "which model is best?" and start asking "which tasks are cheap-safe?" The split is almost always the same:

High-volume, low-ambiguity — extraction, classification, formatting, summarization → cheap tier
Multi-step reasoning, anything customer-facing → frontier tier

Same interface in front of both, so callers never know or care which tier answered.

Step two: the router is maybe 200 lines

Here's the skeleton — a task registry, two tiers, one dispatch function:

from dataclasses import dataclass

PRICE_PER_MTOK = {"cheap": 0.14, "frontier": 5.00}  # $/1M input tokens

@dataclass
class Route:
    tier: str            # "cheap" or "frontier"
    eval_threshold: float  # min accuracy on the golden set to KEEP this route

ROUTES = {
    "extract_invoice_fields": Route("cheap",    0.95),
    "classify_ticket":        Route("cheap",    0.92),
    "format_to_json":         Route("cheap",    0.98),
    "summarize_call":         Route("cheap",    0.90),
    "multi_step_analysis":    Route("frontier", 0.00),  # never downgraded
    "customer_reply":         Route("frontier", 0.00),
}

def route(task: str, eval_scores: dict[str, float]) -> str:
    r = ROUTES[task]
    if r.tier == "cheap" and eval_scores.get(task, 0.0) < r.eval_threshold:
        return "frontier"   # automatic fallback: evals gate the cheap tier, not vibes
    return r.tier

# Last eval run on the golden datasets:
eval_scores = {"extract_invoice_fields": 0.97, "classify_ticket": 0.94,
               "format_to_json": 0.99, "summarize_call": 0.86}  # summarize regressed

for task in ROUTES:
    print(f"{task:24s} -> {route(task, eval_scores)}")

extract_invoice_fields   -> cheap
classify_ticket          -> cheap
format_to_json           -> cheap
summarize_call           -> frontier
multi_step_analysis      -> frontier
customer_reply           -> frontier

Notice summarize_call: it wanted the cheap tier, but its eval score dropped below threshold, so the router bounced it back to frontier automatically. Nobody woke up at 3am. That's the whole design — the golden dataset per task and the threshold are the safety net; the router is just an if.

And that's the honest part of this post: the router is trivial. The eval harness is the real work. Building golden datasets per task, scoring them on every model change, wiring the scores into dispatch — that's where the engineering hours go. Teams that skip it end up routing on vibes, and vibes always route to the expensive model, because nobody gets fired for picking the flagship.

Step three: the arithmetic that sells it upstairs

monthly_mtok = 100  # 100M input tokens/month

def bill(cheap_share: float) -> float:
    return monthly_mtok * (cheap_share * PRICE_PER_MTOK["cheap"]
                           + (1 - cheap_share) * PRICE_PER_MTOK["frontier"])

for share in [0.0, 0.3, 0.5, 0.7]:
    print(f"{share:.0%} of traffic on cheap tier -> ${bill(share):,.0f}/month")

0% of traffic on cheap tier -> $500/month per 100M tokens
30% of traffic on cheap tier -> $354/month per 100M tokens
50% of traffic on cheap tier -> $257/month per 100M tokens
70% of traffic on cheap tier -> $160/month per 100M tokens

Route just half the traffic and the bill drops nearly in half — the cheap tier is so much cheaper that its contribution barely registers. Scale those numbers to your actual token volume; the ratios hold.

This isn't theoretical. One large exchange rebuilt exactly this across 1,200 agents and cut its AI spend nearly in half. Not better prompts. Not a new model. A router and a workload map.

The resolution is the usual one: this is a data problem wearing an AI costume. The workload map comes from your usage logs. The golden datasets are labeled data pipelines. The eval scores live in a table. The teams that treat their AI traffic as a dataset get the 35x spread working for them.

Where does your traffic go today — one model for everything, or tiers?

I'm Vinicius Fagundes — principal data engineer, independent consultant, and MBA lecturer in São Paulo. I write about data pipelines and the economics that run on top of them, and take on a few platform projects per quarter through vf-insights.com.

Cloud Bills Took a Decade to Get FinOps. AI Bills Won't Get That Long.

Vinicius Fagundes — Mon, 20 Jul 2026 03:00:00 +0000

Watch what happened this month.

Databricks moved Genie to pay-as-you-go. Every user gets a small free allowance, then the meter runs. And notice what shipped the same day: budgets, alerts, per-user spending limits. Not a follow-up release. Not "coming in Q4." The governance shipped with the meter.

That detail is the whole story, and it took the industry ten years of cloud pain to learn it. Let me show you the pattern — and then let's compute the numbers your CFO is going to ask you for, before they ask.

The three acts (every computing wave repeats them)

I teach my MBA students that every computing wave walks through the same three acts.

Act one: adoption without meters. Everyone experiments, nobody measures. Engineering spins up assistants, agents, RAG pipelines. The spend is small, distributed, and invisible — a rounding error across forty cost centers.

Act two: the invoice. Some team discovers their AI assistant spend rivals their warehouse spend. This is the quarter someone screenshots the bill and posts it in the leadership channel. Every company gets exactly one of these moments, and it sets the tone for everything after.

Act three: governance. Budgets, showback, unit economics. The discipline gets a name, then a foundation, then a job title.

Cloud took ten years to walk that arc. FinOps became a practice around 2015, a foundation in 2019, a line on job postings after that. AI spend is compressing the same arc into quarters — and the platforms know it. That's why Databricks shipped cost controls with the meter, not after it. Vendors learned from the cloud era that metering without governance creates churn, not revenue.

The skill isn't cutting the bill

Here's what I tell the class: the skill isn't cutting the bill. It's knowing your unit economics before the invoice teaches them to you.

Three numbers. If you can't name them today, act two is already on your calendar:

Cost per query
Cost per agent run
Cost per answered question (not the same thing — see below)

They're not hard to compute. They're just not computed. Here's the whole calculation, by hand, so nothing is hidden:

# Unit economics of an AI assistant — the three numbers your CFO will ask for.
# Prices per 1M tokens; swap in your model's actual rates.

PRICE_IN, PRICE_OUT = 3.00, 15.00   # $/1M tokens, input and output

def query_cost(tokens_in: int, tokens_out: int) -> float:
    return tokens_in / 1e6 * PRICE_IN + tokens_out / 1e6 * PRICE_OUT

# A typical RAG query: system prompt + retrieved chunks + question in,
# a few hundred tokens of answer out.
tin, tout = 6_000, 500
per_query = query_cost(tin, tout)
print(f"Cost per query:        ${per_query:.4f}")

# An agent run is not one query. It's a loop: plan, call tools, read results, retry.
steps_per_run = 9
per_agent_run = per_query * steps_per_run
print(f"Cost per agent run:    ${per_agent_run:.4f}")

# And 'answered question' carries the failure tax: reruns, abandoned sessions,
# answers wrong enough that a human redid the work.
success_rate = 0.72
per_answered = per_agent_run / success_rate
print(f"Cost per answered q:   ${per_answered:.4f}")

# Now scale it to a company.
users, queries_per_user_day, workdays = 800, 12, 22
monthly = users * queries_per_user_day * workdays * per_answered
print(f"\nMonthly, 800 users:    ${monthly:,.0f}")

Cost per query:        $0.0255
Cost per agent run:    $0.2295
Cost per answered q:   $0.3188
Monthly, 800 users:    $67,320

Read that progression again, because it's where every AI budget surprise lives. The per-query number looks like nothing — two and a half cents, who cares. Multiply by the agent loop and the failure tax, scale to the org, and "who cares" is $67K a month. Nobody lied. The meter just compounds faster than intuition does.

Plain: the gap between "cost per query" and "cost per answered question" is where act two happens.

The number that changes behavior isn't the total

Act-three governance is not a bigger spreadsheet. It's attribution — showback per team, per feature, per user, so the meter changes decisions instead of just documenting them. The minimum viable version is a log line:

import json, time
from datetime import datetime, timezone

def metered_call(client, team: str, feature: str, **kwargs):
    """Wrap every LLM call. Attribution is a logging decision, not a platform purchase."""
    t0 = time.monotonic()
    resp = client.messages.create(**kwargs)
    usage = resp.usage
    cost = usage.input_tokens / 1e6 * PRICE_IN + usage.output_tokens / 1e6 * PRICE_OUT
    print(json.dumps({
        "ts": datetime.now(timezone.utc).isoformat(),
        "team": team,
        "feature": feature,
        "tokens_in": usage.input_tokens,
        "tokens_out": usage.output_tokens,
        "cost_usd": round(cost, 6),
        "latency_s": round(time.monotonic() - t0, 2),
    }))
    return resp

That's it. Pipe those lines into the warehouse you already have, group by team and feature, and you have showback on day one — before any vendor sells you a FinOps-for-AI platform. When I rebuilt cost visibility on a Databricks AI pipeline this way, the spend went from $14K/month to $5.8K/month — and most of the reduction came not from optimization heroics but from attribution making waste visible: retry loops nobody owned, a dev environment calling the production model, one feature quietly responsible for 60% of the tokens. You can't fix what isn't attributed to anyone.

Notice the resolution here, because it's the same as always: this is a data problem before it's an AI problem. Cost per answered question is a pipeline metric — it lives in your event logs, your usage tables, your warehouse. Teams that already treat their platform telemetry as a first-class dataset will walk into act three early. Teams that don't will get walked there by an invoice.

The principle: every abstraction eventually sends an invoice. AI is just sending it faster.

What was the first AI line item that made someone at your company ask "wait — how much?"

12 Patterns Run Every Data Stack. Seniority Is Knowing When Each One Is a Mistake.

Vinicius Fagundes — Thu, 16 Jul 2026 13:25:17 +0000

Suggested tags: dataengineering, architecture, bigdata, dataops

You've seen the chart — twelve boxes, twelve patterns, every data stack ever built somewhere inside it. Darshil Parmar's map of the territory is genuinely good. But a map doesn't tell you which roads are closed, which ones flood in the rainy season, and which ones look like highways but bill you like toll roads.

Here's my take on all 12, after 17 years of shipping them in production. For each one: what it's for, and — the part the chart can't show you — when it's a mistake.

Moving data: ETL, ELT, CDC

1. ETL — alive, and not from nostalgia

When PII must be masked before the data lands anywhere, transform-first isn't a preference. It's the only architecture that satisfies the constraint. Compliance keeps ETL employed.

The claim is executable. Here's the shape of it — masking in flight, so the raw value never touches storage:

import hashlib
import json

def mask_pii(record: dict, pii_fields: list[str], salt: str) -> dict:
    """Transform BEFORE load. The raw CPF/email never lands in the lake."""
    out = dict(record)
    for field in pii_fields:
        if field in out and out[field] is not None:
            digest = hashlib.sha256((salt + str(out[field])).encode()).hexdigest()
            out[field] = digest[:16]  # stable pseudonym, joinable, not reversible
    return out

raw = {"user_id": 42, "email": "maria@example.com", "cpf": "123.456.789-00", "amount": 89.90}
landed = mask_pii(raw, pii_fields=["email", "cpf"], salt="rotate-me-quarterly")
print(json.dumps(landed, indent=2))

{
  "user_id": 42,
  "email": "0a1f2b8c9d3e4f5a",
  "cpf": "7c6d5e4f3a2b1c0d",
  "amount": 89.9
}

Try doing that in ELT. You can't — by definition, the untransformed value already landed. If your regulator cares about where raw PII exists, not just who queries it, the L-before-T order is disqualifying.

When it's a mistake: when there's no landing constraint and you're paying for a transform cluster the warehouse could replace.

2. ELT — the modern default, with a hidden multiplier

Snowflake, BigQuery and Redshift turned the warehouse into the compute engine. Cheap storage flipped the letters. Load raw, transform in SQL, version it in dbt. It's the right default.

But watch the bill, because "transform later" quietly becomes "transform five times." Marketing derives sessions_clean from raw events. Product derives almost the same table. Finance derives it again with one extra filter. Nobody knows, because raw data is right there and deriving is one CREATE TABLE AS away.

The multiplier is just arithmetic:

raw_scan_tb = 2.0          # TB scanned per full transform over raw events
price_per_tb = 6.25        # on-demand scan pricing, adjust to your warehouse
runs_per_day = 4           # scheduled refreshes
teams_rederiving = 5       # teams independently transforming the same raw data

one_pipeline_month = raw_scan_tb * price_per_tb * runs_per_day * 30
print(f"One team, one month:   ${one_pipeline_month:,.0f}")
print(f"Five teams, same data: ${one_pipeline_month * teams_rederiving:,.0f}")

One team, one month:   $1,500
Five teams, same data: $7,500

Same raw data. Same business question. Five bills. The fix isn't abandoning ELT — it's a shared staging layer so the expensive scan of raw happens once, and everyone derives from that.

When it's a mistake: never as a pattern — only as an unowned one. ELT without a shared model layer is a subscription to redundant compute.

3. CDC — the most misdiagnosed pattern in the stack

Everyone asks for real-time replication. Most need hourly batch. The diagnosis question is not "how fresh is the data?" It's "how fresh is the decision?"

def cdc_or_batch(decision_latency_min: float, change_rate_per_hr: int) -> str:
    """If no decision changes faster than your batch window, CDC is cost, not capability."""
    if decision_latency_min < 15:
        return "CDC — a decision actually changes inside the batch window"
    if change_rate_per_hr > 500_000:
        return "CDC — batch extracts would hammer the source DB"
    return "Hourly batch — you're done, go home"

for case, args in {
    "Fraud hold on a transaction": (0.5, 50_000),
    "Executive sales dashboard":   (1440, 20_000),
    "Inventory sync to the app":   (10, 800_000),
}.items():
    print(f"{case:32s} -> {cdc_or_batch(*args)}")

Fraud hold on a transaction      -> CDC — a decision actually changes inside the batch window
Executive sales dashboard        -> Hourly batch — you're done, go home
Inventory sync to the app        -> CDC — a decision actually changes inside the batch window

And if the answer really is CDC — respect it. A Debezium agent feeding Kafka is production infrastructure: schema evolution handling, snapshot strategy, offset management, dead-letter queues, on-call. Not a weekend project. I've seen more incidents from casually-deployed CDC than from any other pattern on this list.

When it's a mistake: when it was prescribed by the word "real-time" in a slide instead of a decision that changes mid-hour.

Storing data: Lakehouse

4. Lakehouse — the truce after a decade of war

Lake vs warehouse was never a fair fight — it was two copies of the same data, two pipelines pretending to agree, and a reconciliation job apologizing between them. The lakehouse (Delta, Iceberg, Hudi — table formats with ACID over object storage) is the truce: one copy, one source of truth, warehouse semantics on lake economics.

I merged 6 data lakes into 1 on this pattern. The before/after:

	Before (6 lakes)	After (1 lakehouse)
Storage	baseline	−40%
Compute	baseline	−55%
Copies of "the same" customer table	6, disagreeing	1
Reconciliation pipelines	many	0

The compute drop is the part people don't predict: most of that spend wasn't analysis. It was six pipelines keeping six copies loosely synchronized — work that produces no insight, only agreement. Delete the duplication and the compute deletes itself.

When it's a mistake: when your data fits comfortably in one warehouse and the "lakehouse migration" is résumé-driven. One Postgres replica and dbt beats a Delta lake you don't need.

Processing cadence: streaming, batch, Lambda, Kappa

5. Real-time streaming — powerful, and over-prescribed

Flink and Spark Structured Streaming earn their complexity when decisions change mid-minute: fraud holds, dynamic pricing, operational alerting. That complexity is real — watermarks, late data, exactly-once semantics, state stores that need care and feeding.

Meanwhile, a dashboard refreshed every 5 minutes is batch wearing a costume. If the consumer of the data is a human glancing at a screen between meetings, a 5-minute micro-batch delivers the identical experience at a fraction of the operational surface. The streaming job isn't serving the user — it's serving the architecture diagram.

When it's a mistake: whenever the freshest possible data and the freshest needed data differ by more than the batch window.

6. Batch — unglamorous, still moves the world

Most of the planet's data still moves at night, in batch, and that's fine. My favorite optimization win ever was pure batch: an 8-hour Spark pipeline tuned down to 47 minutes. No new tools. No migration. The fixes were the boring, compounding kind:

# The class of changes that took 8 hours to 47 minutes — none of them exotic.

spark.conf.set("spark.sql.shuffle.partitions", "auto")   # was a hardcoded 200 from a 2018 blog post
spark.conf.set("spark.sql.adaptive.enabled", "true")      # let AQE resize joins at runtime

# 1. Stop reading everything: partition pruning instead of full scans
df = spark.read.parquet("s3://events/").where("event_date = '2026-07-14'")

# 2. Stop shuffling the small side: broadcast the dimension table
from pyspark.sql.functions import broadcast
joined = df.join(broadcast(dim_stores), "store_id")

# 3. Stop recomputing shared branches: cache what three outputs reuse
base = joined.filter("status = 'valid'").cache()

Boring done well beats exciting. An 8-hour job at 47 minutes reruns inside a business day when it fails — which changes your on-call life more than any architecture choice on this page.

When it's a mistake: only when a decision genuinely can't wait for the window. Which is rarer than the vendor deck says.

9. Lambda — honest about the tension, brutal in practice

(Taking 9 and 10 here because they're one argument.) Lambda architecture admits the real tension: some answers need to be fast, all answers need to be complete. Its solution — a speed layer and a batch layer computing the same metric — means two codebases that must agree forever.

They won't. Here's the same "daily revenue" in both layers; spot the bug that ships twice:

-- Batch layer (SQL): timezone-aware, refunds excluded
SELECT DATE(created_at AT TIME ZONE 'America/Sao_Paulo') AS day,
       SUM(amount) AS revenue
FROM orders
WHERE status != 'refunded'
GROUP BY 1;

# Speed layer (streaming): UTC dates, refunds... forgotten
def on_event(order, state):
    day = order["created_at"][:10]        # UTC slice — already disagrees with batch
    state[day] = state.get(day, 0) + order["amount"]  # refunds counted — bug #2

Every definition change now has to land in two languages, two deploy pipelines, two teams' heads. Every bug ships twice, and the reconciliation meeting is where careers go to age.

When it's a mistake: almost always, today. Lambda made sense before table formats and modern stream processors; now it's mostly historical baggage with a Greek name.

10. Kappa — Lambda's apology

One stream, one codebase, replay the log when you need to reprocess. Elegant — if you can afford the Kafka retention and the reprocessing story:

daily_gb = 400            # ingest volume
retention_days = 90       # long enough to replay after finding a logic bug
replication = 3
storage_per_gb_month = 0.10

retained_gb = daily_gb * retention_days * replication
print(f"Retained in Kafka: {retained_gb/1000:.1f} TB")
print(f"Retention cost:    ${retained_gb * storage_per_gb_month:,.0f}/month")
print(f"Full replay time at 500 MB/s: {retained_gb/3 / 0.5 / 3600:.0f} hours")

Retained in Kafka: 108.0 TB
Retention cost:    $10,800/month
Full replay time at 500 MB/s: 20 hours

Many teams look at that and choose batch with a small streaming edge instead. That's not failure — that's arithmetic.

When it's a mistake: when the retention bill and replay window are bigger than the problem Lambda's dual codebase was causing you.

Knowing your data: catalog, lineage

7. Data cataloging — only works as a living system

A catalog nobody updates is a museum: pleasant to visit, wrong about everything current. The only catalogs I've seen survive are the ones populated by the pipelines themselves — metadata emitted at run time, not typed in by a data steward six weeks later. If updating the catalog is a human task, the catalog is already stale; you just haven't noticed yet.

When it's a mistake: as a standalone documentation project. As an automated side effect of orchestration, it's one of the highest-leverage things on this list.

11. Lineage — nobody budgets for it until a board deck is wrong

Then "where did this number come from" is suddenly worth more than the pipeline that produced it. Lineage is insurance: it looks like pure cost right up until the incident where it's the only thing that matters. The trick is the same as cataloging — derive it from execution (query logs, dbt manifests, OpenLineage events), never from documentation.

When it's a mistake: hand-maintained. Automated or absent — those are the only two honest states.

Organizing people: mesh

8. Data mesh — an org decision wearing an architecture costume

Mesh is not a technology. It's a claim about your company: that domain teams are ready to own data as a product — with SLAs, schemas as contracts, and someone on call when their dataset breaks a consumer. If that claim is true, mesh is liberating. If it's false, mesh is decentralized chaos with better naming, and the central data team still gets paged for everything while owning nothing.

When it's a mistake: when it's adopted as an architecture before it's true as an org chart.

Running it all: orchestration

12. Orchestration — the heartbeat nobody monitors

The DAG is your platform's heartbeat and its least monitored component. Everyone watches the pipelines. Few watch the scheduler running them — and a silently degraded scheduler fails every pipeline at once, quietly, by simply not starting them.

# The alert most platforms are missing: not "did the job fail?"
# but "did the scheduler fail to even try?"
from airflow.sensors.base import BaseSensorOperator
from datetime import datetime, timedelta, timezone

class SchedulerHeartbeatCheck(BaseSensorOperator):
    def poke(self, context):
        from airflow.jobs.job import Job
        latest = Job.most_recent_job(job_type="SchedulerJob")
        lag = datetime.now(timezone.utc) - latest.latest_heartbeat
        if lag > timedelta(minutes=5):
            raise RuntimeError(f"Scheduler heartbeat is {lag} old — nothing is being scheduled")
        return True

A DAG that fails pages someone. A DAG that never starts pages no one — until the morning dashboard is empty and twelve teams find out together.

When it's a mistake: it isn't. Unmonitored, it's just a mistake waiting for a quiet night.

What's missing from the chart

Notice: not one of these twelve is cloud-specific. The same patterns run on AWS or GCP, on Databricks or on bare Postgres and cron if you're stubborn enough. Tools rotate every five years. Patterns are the career.

The principle: every pattern here solves a problem and creates one. Architecture is choosing which problems you'd rather have.

Which of these did you adopt too early — and what did it cost you?