When `dbt test` isn't enough: validating a migration table by table

There is a category of bug that dbt test will not catch and cannot reasonably be expected to catch. It’s the bug where the model produces a perfectly valid output that disagrees, in the third decimal place or in 2.97% of rows, with the system you just migrated from. The shape is right, the schema is right, the unique constraint holds, the not-null tests pass. The numbers are wrong.

Stock dbt testing — unique, not_null, relationships, accepted_values, plus the pile of macros in dbt-expectations — is excellent for intra-system invariants. It assumes you know what the right answers are. During a migration, you don’t. The right answer is whatever the previous pipeline was producing, which lives in a different warehouse, with different SQL semantics, in a column that may have been a DOUBLE over there and a DECIMAL(18,6) over here. dbt has nothing to say about that.

This article is about the framework that grew from that gap. Two production validation systems, each ~700–1000 lines of Python, each catching real regressions before they shipped. Both built on top of dbt — they don’t replace it, they extend it into the cross-system territory it doesn’t cover.

The diagnostic engine: row-aligned comparison with numeric tolerance

The core function is depressingly simple to state. Two tables, same logical content, sitting in two warehouses. You want to know:

1. Do they contain the same set of rows? (Cardinality.)
2. For the rows they share, do all the columns agree? (Equality.)
3. For the columns that don’t agree, how don’t they agree? (Drift classification.)

The first attempt at this looks like a wide JOIN. That doesn’t survive contact with a 9.5-million-row invoice table that has 157 columns. You hit memory limits in Spark before you get to “do the numbers match”. The second attempt, which is what shipped, processes one column at a time, aggregates per-side, then compares aggregates. You lose the ability to point at a specific row that disagrees, but you gain the ability to say “across these 9.5M rows, the sum of price_local differs by 0.0000004%, which is rounding noise, move on”.

# Per-column aggregation, single pass
agg_exprs = []
for col_name in numeric_cols:
    agg_exprs.extend([
        F.sum(F.col(col_name).cast("double")).alias(f"{col_name}__sum"),
        F.percentile_approx(F.col(col_name).cast("double"), 0.5).alias(f"{col_name}__median"),
        F.min(F.col(col_name).cast("double")).alias(f"{col_name}__min"),
        F.max(F.col(col_name).cast("double")).alias(f"{col_name}__max"),
    ])

stats_a = df_old.agg(*agg_exprs).collect()[0]
stats_b = df_new.agg(*agg_exprs).collect()[0]

The numeric comparison uses relative tolerance, not absolute:

# Float-vs-decimal precision differences should not register as drift.
TOLERANCE = 1e-6

def is_rounding_only(a: float, b: float) -> bool:
    if a == 0 and b == 0:
        return True
    return abs(a - b) / max(abs(a), abs(b)) <= TOLERANCE

Two values 12.3456789 (Athena DOUBLE) and 12.345679 (Databricks DECIMAL(18,6)) compare equal. They disagree at the seventh digit, which is below the type’s representable precision. This isn’t a bug — it’s the type system doing exactly what it claims. The framework recognises this and classifies the column as agreeing, not drifting. Without this, you get drowned in false positives within five minutes of running the first comparison.

Outlier trimming: filtering ETL noise from real disagreement

The trickier comparison is sums of large columns. Tax amounts. Order values. Anything where one outlier row can dominate the aggregate. If the migration introduced a single 2-billion-euro phantom row (it has happened), the SUM will diverge wildly even if every other row is correct.

The solution is trimmed sums — exclude the top and bottom 0.01% of each column’s values before summing. PySpark won’t let you reference an aggregate inside a row-level when predicate, so the implementation is two passes: compute the percentile bounds in pass one, then use them as Python scalars inside the row-level expression in pass two:

OUTLIER_TRIM = 0.0001  # 0.01% per side

# Pass 1: get the percentile bounds out as scalars (one Spark job per side)
def _bounds(df, columns):
    aggs = [
        F.percentile_approx(F.col(c).cast("double"),
                            [OUTLIER_TRIM, 1 - OUTLIER_TRIM],
                            1_000_000).alias(f"{c}__pct")
        for c in columns
    ]
    row = df.agg(*aggs).collect()[0]
    return {c: row[f"{c}__pct"] for c in columns}

old_bounds = _bounds(df_old, numeric_cols)
new_bounds = _bounds(df_new, numeric_cols)

# Pass 2: full + trimmed aggregations in one expression (one Spark job per side)
trimmed_aggs = []
for col_name in numeric_cols:
    c = F.col(col_name).cast("double")
    # Use the union of both sides' bounds — wider range, same rows trimmed on both sides
    lo = min(old_bounds[col_name][0], new_bounds[col_name][0])
    hi = max(old_bounds[col_name][1], new_bounds[col_name][1])
    trimmed_aggs.append(
        F.sum(F.when(c.between(lo, hi), c)).alias(f"{col_name}__trimmed_sum")
    )

Two correctness details that are easy to get wrong on a first cut: the percentile call uses accuracy=1_000_000 instead of the default ~10,000, because the default isn’t precise enough at the 0.01% tail; and the trim bounds are taken as the union of the two sides’ bounds (widest range), so both sides drop the same rows by index rather than each side dropping its own slightly different outliers and arriving at non-comparable trimmed sums.

The trimmed sum is the headline diagnostic. The full sum is also reported, but the trimmed version is what the reviewer looks at first. If the trimmed sum agrees and the full sum doesn’t, the divergence lives in the tails — usually a handful of outliers worth investigating individually. If the trimmed sum also disagrees, you have systemic drift, which is a different category of problem. The trade-off the trim makes is honest and worth stating: a real systematic bug that affects only the trimmed population (one office’s records, one product line, one month’s worth of data) will be invisible to the trimmed sum. We watch the full sum and the trimmed sum together for that reason; a divergence that shows up in only one of them is the interesting case.

Cardinality checks: declared composite keys, with a single-column fallback

The native dbt unique test fails on a single column. Real fact tables have composite keys: (invoice_number, line_item_number, ship_to_sequence_number). dbt’s dbt_utils.unique_combination_of_columns covers this, but it doesn’t tell you which combinations are missing on one side or the other.

The framework’s per-table config carries an explicit composite_key parameter. When it’s set, the comparison uses that tuple verbatim; when it isn’t, the framework falls back through three heuristics in order — first single column whose distinct count equals the row count (a unique single-column key), failing that, the highest-distinct-count column whose name contains id, failing that, the highest-distinct-count column overall. The fallback is good enough for tables you’re getting to know and not good enough for production blocking; in practice the discipline is “declare the key in config the first time you run validation against a table” rather than “trust the autodetect.”

Once the key columns are pinned, the membership check is three set operations:

keys_old = df_old.select(*key_cols).distinct()
keys_new = df_new.select(*key_cols).distinct()

only_in_old = keys_old.subtract(keys_new).count()
only_in_new = keys_new.subtract(keys_old).count()
matched     = keys_old.intersect(keys_new).count()

Three numbers: matched, missing-from-new, missing-from-old. A migration that “works” should have matched ≈ both sides and the two only_in_* numbers near zero. When they aren’t, you immediately know whether the problem is rows lost in the migration (only_in_old > 0) or phantom rows added by the new pipeline (only_in_new > 0). (A single full outer join would compute the same three numbers in one shuffle instead of three; the three-set form is more readable, the join form is more efficient on large tables — we use the three-set form because the validation tables top out around the 30-million-row mark.)

This is the check that found 398,000 phantom rows in a snapshot table just before deployment. The cause was an upstream change that omitted the dbt snapshot’s hard_deletes: invalidate flag — soft-deleted rows were never being marked invalid in the new pipeline. The fix was a one-line config change. The detection was a count(*) of phantom keys.

A real bug, surfaced by the framework

The clearest example of why this exists, and why naive testing wouldn’t have caught it: a fact table started showing a 2.97% divergence in cge_gp_in_progress between Athena and Databricks. The values weren’t far off — they were genuinely close, but consistently a tiny bit different.

The cause was Spark’s date_sub(timestamp, N) truncating to midnight before subtracting the N days, while Athena’s interval arithmetic preserves the time component. A timestamp of 2026-02-15 14:32:01 minus 30 days became 2026-01-16 00:00:00 in Spark and 2026-01-16 14:32:01 in Athena. Downstream, this 14-hour difference rippled into a 365-day rolling window and produced the observed drift in five different fact tables.

The framework caught it because the divergence was systematic and showed up in the trimmed sum — not just the full sum, not just the row count. The fix was a one-line change to the date arithmetic. The diagnostic ran again, the drift dropped from 2.97% to 0.0003%, and five downstream tables resolved themselves in the same merge.

How it integrates with dbt

The framework is not a dbt plugin. It runs after dbt build, against the materialised tables, comparing them to the legacy system. Two integration shapes work in production:

• CLI for dev/CI: python validation/run_diagnostic.py --env prod --table fact_invoice — runs ad-hoc, prints a markdown report with per-column verdicts. Used during deployments and before risky merges.
• Notebook for nightly runs: a Databricks notebook that walks a configured table list, runs the same comparison logic, and writes a tier-based summary (PASS / WARN / TIMEOUT / ERROR). Tier-1 tables run first, get a longer timeout, and block the pipeline on failure. Tier-5 tables run last and only WARN.

TABLE_CONFIG = {
    # Tier 1: production-critical, blocking
    "fact_order_impact":         {"tier": 1, "date_col": "order_placement_date"},
    # Tier 2: large, sample 10% to fit in timeout
    "fact_trigger_all_opps":     {"tier": 2, "date_col": "write_date",
                                  "timeout": 1800, "sample": 0.1},
    # Tier 5: nice-to-have, non-blocking
    "fact_user_month_legacy":    {"tier": 5, "date_col": "month"},
}

The tier-based config is the part that makes the framework usable in practice. Without it, every migration failure becomes a P1 incident; with it, the noise self-classifies and the human attention goes to the things that actually warrant it.

What I’d carry forward

Three things, in order of how often they’ve paid back:

1. Per-column aggregation beats wide joins for cross-system comparison at scale. You give up row-level diff resolution and gain the ability to compare tables that don’t fit in memory together.
2. Numeric tolerance has to be relative, not absolute, and outlier trimming is mandatory. Without these, the framework is a false-positive generator and people stop reading its output, which is worse than having no framework.
3. A configured key beats a guessed one, with a guess as the fallback. The framework’s heuristic single-column key detection is fine for first-pass exploration; nothing beats the per-table composite_key config entry once the table’s structure is understood, and a validation that blocks production should be running against the configured form.

dbt builds the new pipeline; it doesn’t know what the old pipeline was producing, and dbt test doesn’t know either. The validation layer that compares the two has to be built deliberately, and the cost of skipping it is the kind of cost that only shows up after the migration has shipped — by which point the old pipeline is already off.