Data Diffing in CI: Datafold, data-diff & Row-Level Regression Testing for Pipelines

A data diff is the one test that answers the question every unit test dodges: not "does this transformation still pass its assertions?" but "does the new code produce different rows than the old code, and if so, exactly which rows and which columns changed?" Every analytics pipeline is a chain of SQL models, and every merge to that chain is a silent bet that a one-line change to a CASE expression, a JOIN condition, or a COALESCE default did not quietly shift a revenue number in a dashboard three hops downstream. Unit tests catch the failures you predicted; a row-level diff between the old output and the new output catches the regressions you did not — the ones that never throw an error, never fail a NOT NULL check, and only surface when finance asks why last quarter's number moved.

This guide is the senior-data-engineering walkthrough for building that safety net, framed the way interviewers probe it: what a data diff actually computes at the row and value-level diff grain, how the open-source data-diff engine diffs two tables across two different databases without dragging every row over the wire, how teams wire diffing into CI as a pull-request check — the workflow Datafold productised — so regression testing runs on every commit, and how the same diff engine graduates from a dev-time PR check into continuous production pipeline testing with drift thresholds and alerting. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the data validation practice library →, rehearse on the ETL practice library →, and sharpen the transformation axis with the data transformation practice library →.

---

On this page

• Why data diffing is the missing pipeline regression test
• Row-level and value-level diffing
• data-diff — the open-source diffing engine
• Datafold and CI — PR checks that gate every merge
• Regression testing pipelines end to end
• Cheat sheet — data diffing recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why data diffing is the missing pipeline regression test

Data diffing is regression testing for data — assert on the delta between two versions of a table, not on a handful of known cases

The one-sentence invariant: a data diff compares two versions of a dataset — old code vs new code, prod vs dev, source vs target — row by row on a shared primary key and value by value inside each matched row, then reports exactly which rows were added, removed, or changed and which columns inside the changed rows moved — which makes it the only test that catches the regression you did not think to assert, because it asserts on the difference itself rather than on a fixed list of expected values. A dbt test proves order_id is unique and non-null; it says nothing about the 12 rows whose revenue silently doubled because someone changed a SUM to a SUM ... OVER. The diff catches those 12 rows because it is comparing outputs, not checking rules.

The four axes interviewers actually probe.

• Diff scope. Three grains stack: schema diff (did columns/types change?), row-count / key diff (which primary keys were added or removed?), and value-level diff (inside matched keys, which column values changed?). A senior answer names all three and knows that value-level is the expensive, high-signal one.
• Where it runs. The same diff engine runs at three lifecycle stages: dev-time as a CI pull-request check comparing your branch's output to prod, pre-prod as a dual-run reconciliation before promotion, and production as a scheduled monitor. Interviewers want to hear "shift the diff left into the PR" as the headline.
• Cross-database vs in-database. Diffing two tables in the same warehouse can be a single FULL OUTER JOIN. Diffing Postgres against Snowflake cannot — you must not stream both tables to one host. The cross-database case is where checksum-based algorithms earn their keep.
• Sampling vs exactness. A cheap "are these roughly equal?" check samples rows; a real regression gate is exact — it must find the one wrong row in ten million. Senior candidates distinguish "profiling" (approximate, always cheap) from "diffing" (exact, must be made cheap by algorithm).

Why dbt test and unit tests are not enough.

• Assertions encode what you predicted. unique, not_null, accepted_values, relationships — every one is a rule you wrote in advance. A regression that keeps every rule valid but changes the numbers passes all of them.
• Unit tests fix the input. A dbt unit test (or a dbt-unit-testing fixture) feeds a tiny synthetic input and checks a tiny expected output. It proves the logic on the cases you invented; production data has cases you did not.
• Diffs compare real outputs. A data diff runs the old transformation and the new transformation over the same real data and compares. No expected values to maintain — the previous version is the oracle.
• The gap they close. "Refactor that should not change anything" is the single most dangerous PR in analytics. A diff turns "should not change anything" into a proven, reviewable claim: 0 rows changed, or these 12 rows changed and here is why.

Datafold vs open-source data-diff — the 2026 landscape.

• data-diff (open source). A Python CLI + library (open-sourced by Datafold) that diffs two tables — same database or cross-database — using a checksum-bisection algorithm. Free, scriptable, integrates with dbt. The workhorse for self-hosted CI.
• Datafold (commercial). A hosted platform built around the same idea plus column-level lineage, a CI app that posts diff summaries as PR comments, downstream impact analysis, and value-level diffs through a UI. You pay for lineage, UX, and the managed CI integration.
• The relationship. Learn the mechanics on open-source data-diff; reach for Datafold when you want lineage-aware impact ("this diff touches revenue_daily, which feeds the exec dashboard") without building it yourself.
• Other neighbours. dbt's own --defer / state:modified+ (Slim CI) selects what to test; recce and dbt-data-diff packages wrap comparisons; great-expectations / dbt test cover rule-based validation. Diffing is the delta-based complement, not a replacement.

What interviewers listen for.

• Do you say "a diff asserts on the delta, an assertion asserts on a rule" — the one-sentence framing? — senior signal.
• Do you name the three grains (schema, key/row, value-level) without prompting? — required answer.
• Do you push back on "just add more dbt tests" with "tests catch predicted failures; diffs catch regressions"? — senior signal.
• Do you know that cross-database diffing cannot stream both sides to one host and needs checksums? — senior signal.
• Do you frame the goal as "see the data impact of a code change on the PR, before merge"? — required answer.

Worked example — a refactor that passes every test and still breaks a number

Detailed explanation. The most instructive data-diff scenario is the PR that is supposed to be a no-op. An engineer "cleans up" a revenue model by switching a subquery to a window function. Every dbt test still passes. A data diff between the prod output and the branch output reveals that 12 orders now carry a doubled revenue. Walk through why the tests missed it and the diff caught it.

• The model. revenue_by_order — one row per order_id with a revenue column.
• The change. A GROUP BY subquery replaced by SUM(amount) OVER (PARTITION BY order_id) without a later DISTINCT / dedup — so multi-line orders now emit one row per line, each with the full order total.
• The tests. unique(order_id) still passes in dev because dev data happens to have single-line orders; not_null(revenue) passes; accepted_range was never written.

Question. Given the prod output and the branch output for revenue_by_order, what does a data diff report that the test suite does not?

Input.

order_id	revenue (prod)	revenue (branch)
1001	50.00	50.00
1002	120.00	240.00
1003	30.00	30.00
1004	80.00	160.00

Code.

-- Diff query: align both versions on the primary key, compare the value column
SELECT
    COALESCE(p.order_id, b.order_id)                       AS order_id,
    p.revenue                                              AS revenue_prod,
    b.revenue                                              AS revenue_branch,
    CASE
        WHEN p.order_id IS NULL                THEN 'added'
        WHEN b.order_id IS NULL                THEN 'removed'
        WHEN p.revenue  IS DISTINCT FROM b.revenue THEN 'changed'
        ELSE 'same'
    END                                                    AS diff_status
FROM        prod.revenue_by_order   AS p
FULL OUTER JOIN branch.revenue_by_order AS b
       ON p.order_id = b.order_id
WHERE  p.revenue IS DISTINCT FROM b.revenue
   OR  p.order_id IS NULL
   OR  b.order_id IS NULL
ORDER BY order_id;

Step-by-step explanation.

1. The FULL OUTER JOIN on order_id aligns the two versions: matched keys appear on both sides, keys only in branch are additions, keys only in prod are removals.
2. IS DISTINCT FROM is the null-safe inequality — it treats NULL vs 50.00 as a change and NULL vs NULL as equal, which plain <> gets wrong.
3. The CASE classifies each aligned key into added / removed / changed / same. This is the row-level classification every diff produces.
4. The WHERE keeps only the interesting rows — value changed, or key present on one side only — so the output is the delta, not the whole table.
5. The suite missed the bug because unique(order_id) held in dev's single-line sample and no range assertion existed. The diff missed nothing: it compared the actual revenue numbers.

Output.

order_id	revenue_prod	revenue_branch	diff_status
1002	120.00	240.00	changed
1004	160.00*	80.00 → 160.00	changed

The diff reports 2 of 4 rows changed (50% value drift on revenue) — a loud, reviewable signal that the "no-op refactor" was anything but.

Rule of thumb. If a PR claims "this changes no data," prove it with a diff that returns zero changed rows. "Should not change anything" is a hypothesis; a zero-row diff is evidence.

Worked example — the three grains of a diff

Detailed explanation. Every full data diff answers three nested questions in order: did the schema change, did the set of keys change, and did values inside matched keys change. Running them in that order lets you stop early and localises the failure. Walk through the three grains on a customers table.

• Schema grain. Column set + types. Cheapest; run first.
• Key grain. COUNT, plus set-difference of primary keys. Medium cost.
• Value grain. Per-column comparison inside matched keys. Most expensive; highest signal.

Question. For two versions of customers, what does each grain report and in what order should you evaluate them?

Input.

Grain	prod	branch
columns	id, name, email, tier	id, name, email, tier, region
row count	10,000	10,001
values (email)	9,998 match	2 changed

Code.

-- Grain 1 — schema (information_schema)
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'customers' AND table_schema = 'branch'
EXCEPT
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'customers' AND table_schema = 'prod';
-- → ('region','text')  : a column was ADDED

-- Grain 2 — key set difference
SELECT 'added'   AS kind, id FROM branch.customers
EXCEPT SELECT 'added', id FROM prod.customers
UNION ALL
SELECT 'removed' AS kind, id FROM prod.customers
EXCEPT SELECT 'removed', id FROM branch.customers;

-- Grain 3 — value-level, per column (example: email)
SELECT COUNT(*) AS email_changed
FROM        prod.customers   p
JOIN        branch.customers b USING (id)
WHERE  p.email IS DISTINCT FROM b.email;

Step-by-step explanation.

1. Grain 1 uses information_schema set-difference to surface added/removed columns and type changes. A new region column is an additive schema change — usually safe, but it must be seen, not discovered in production.
2. Grain 2 diffs the key sets with EXCEPT. The branch has one extra id (an added customer). If keys are stable, you skip straight to value diffs; if keys churn heavily, that itself is the headline.
3. Grain 3 compares each column inside matched keys. Reporting per column (email_changed = 2) localises the regression to a column, which is far more actionable than "2 rows differ."
4. The order matters: a schema change can explain a value change (new column defaults), and a key change can explain a count delta. Evaluating cheapest-first also short-circuits — identical schema + identical keys + identical checksums means "no diff, stop."
5. This three-grain ladder is exactly what tools like data-diff and Datafold automate; knowing it by hand is what lets you reason about their output.

Output.

Grain	Result	Interpretation
Schema	+region (text)	additive, review default
Keys	+1 id (added), 0 removed	one new customer
Values (email)	2 changed	localised regression to investigate

Rule of thumb. Run diffs cheapest-grain-first: schema, then keys, then values. Stop at the first grain that is clean enough to explain the rest, and always report value changes per column, never as a bare row count.

Worked example — where the diff runs across the lifecycle

Detailed explanation. The same comparison is valuable at three points in a change's life, and the interview answer that scores highest names all three and explains why the dev-time PR check is the highest-leverage. Walk the lifecycle for a single model change.

• Dev / PR (CI). Build the model into a temporary dev schema, diff against prod, post the summary on the pull request.
• Pre-prod (dual-run). Run old and new pipeline side by side over a full cycle; gate promotion on the diff.
• Production (monitoring). Schedule a recurring diff (source vs warehouse, or run N vs run N-1) and alert on drift.

Question. Map each lifecycle stage to what it compares, what it gates, and its latency budget.

Input.

Stage	Compares	Gates	Latency budget
Dev / PR (CI)	branch output vs prod	the merge	minutes
Pre-prod dual-run	new pipeline vs old pipeline	promotion	hours
Production monitor	today's load vs yesterday / source	an alert	scheduled

Code.

Change lifecycle with diffing at every gate
============================================

  feature branch ──► CI: build dev schema ──► data-diff(branch, prod)
                                               │
                                               ├─ 0 changed  → auto-mergeable
                                               └─ N changed  → comment on PR, human reviews

  merged ──► pre-prod: run old + new pipeline ──► diff full cycle ──► promote if within threshold

  in prod ──► nightly: diff(load_today, load_yesterday) ──► alert if drift > threshold

Step-by-step explanation.

1. The dev/PR stage is the highest leverage because it catches the regression before it merges — the cheapest possible place to fix it. This is the "shift left" story interviewers want.
2. It compares the branch's freshly-built output against current prod, so the previous behaviour is the oracle. No expected-value fixtures to maintain.
3. The pre-prod dual-run is for large or risky migrations where a PR-time sample is not enough — you run both pipelines over a real cycle and diff the full result before cutting consumers over.
4. The production monitor closes the loop: even code that diffed clean can drift when upstream data changes. A scheduled diff (load N vs N-1, or warehouse vs source) turns silent drift into a page.
5. One engine, three stages — the reusable insight is that "diffing" is not a one-time migration tool but a standing regression-testing capability.

Output.

Stage	Oracle (baseline)	Failure mode caught
Dev / PR	current prod	code regressions before merge
Pre-prod	old pipeline	migration correctness at full scale
Production	prior run / source	upstream data drift after merge

Rule of thumb. Do not treat diffing as a migration-only tool. Wire the same diff into the PR check, the promotion gate, and the nightly monitor — three stages, one engine, three classes of regression caught.

Senior interview question on data diffing strategy

A senior interviewer often opens with: "Your team keeps shipping 'harmless' analytics refactors that quietly move dashboard numbers, and dbt test catches none of them. Design a data-diffing strategy that makes every pull request prove its data impact, and explain where diffing fits relative to your existing tests."

Solution Using a three-grain diff wired as a PR gate with dbt test as the complement

-- A reusable value-level diff, parameterised by table + key + columns.
-- Run branch build vs prod for the models a PR touches.
WITH aligned AS (
    SELECT
        COALESCE(p.id, b.id)                               AS id,
        p.id IS NOT NULL                                   AS in_prod,
        b.id IS NOT NULL                                   AS in_branch,
        (p.email  IS DISTINCT FROM b.email)::int           AS d_email,
        (p.tier   IS DISTINCT FROM b.tier)::int            AS d_tier,
        (p.revenue IS DISTINCT FROM b.revenue)::int        AS d_revenue
    FROM        prod.dim_customers   p
    FULL OUTER JOIN branch.dim_customers b ON p.id = b.id
)
SELECT
    COUNT(*)                                       AS total_keys,
    COUNT(*) FILTER (WHERE NOT in_prod)            AS added,
    COUNT(*) FILTER (WHERE NOT in_branch)          AS removed,
    SUM(d_email)                                   AS email_changed,
    SUM(d_tier)                                    AS tier_changed,
    SUM(d_revenue)                                 AS revenue_changed
FROM aligned;

# The PR gate: dbt tests (rules) AND a diff (delta), both required
# .github/workflows/pr.yml (abridged)
- name: dbt build (rules) — must pass
  run: dbt build --select state:modified+ --defer --state prod-manifest/

- name: data diff (delta) — summarise impact, gate on unexpected change
  run: |
    python run_diff.py \
      --models "$(dbt ls --select state:modified+ --resource-type model)" \
      --baseline prod --candidate "$DEV_SCHEMA" \
      --fail-on-unexpected

Step-by-step trace.

Input: dim_customers — prod has 10,000 keys; the branch added 1 key and changed email on 2 keys.

1. The FULL OUTER JOIN aligns all 10,001 distinct keys; in_prod / in_branch flags classify presence.
2. Per-column IS DISTINCT FROM casts to int so a SUM counts changed values per column — email accumulates 2, tier and revenue accumulate 0.
3. The aggregate collapses 10,001 rows into one summary row: added=1, removed=0, email_changed=2.
4. In CI, dbt build runs the rules (unique/not-null) and must pass; run_diff.py runs the delta and gates on unexpected change.
5. Final result — the PR carries both a green rule-check and a diff summary a reviewer can read in five seconds.

Output:

total_keys	added	removed	email_changed	tier_changed	revenue_changed
10,001	1	0	2	0	0

Why this works — concept by concept:

• Full outer join on the key — the alignment primitive. It is the only join that surfaces added and removed keys alongside matched ones, so no row is silently dropped from the comparison.
• IS DISTINCT FROM — the null-safe comparator. It counts NULL → value and value → NULL as changes and NULL → NULL as equal, avoiding the classic <>-drops-nulls bug that makes a naive diff under-report.
• Per-column change counters — casting each column comparison to int and summing localises the regression to a column, turning "2 rows differ" into "2 emails changed," which is what a reviewer can act on.
• Rules AND delta — dbt test asserts invariants you predicted; the diff asserts on the change itself. They are complements: keep both required in CI, not either/or.
• Cost — one FULL OUTER JOIN over the key: O(n) with a hash join when both sides are in-warehouse, plus O(1) aggregation. The expensive case (cross-database) is deferred to Section 3's checksum algorithm.

SQL
Topic — data-validation
Data validation and diffing problems

Practice →

ETL Topic — etl ETL problems on pipeline regression testing

Practice →

---

2. Row-level and value-level diffing

row-level diff aligns two tables on a key; value-level diff compares each column inside the matched rows — together they tell you what changed and where

The mental model in one line: a row-level diff answers "which keys are added, removed, or present-in-both?" by set-comparing the primary keys of two tables, and a value-level diff answers "inside the present-in-both keys, which columns hold different values?" by comparing each column with a null-safe operator — and a production diff engine does both, plus a fast checksum pre-filter so it only pays the value-comparison cost on rows that actually differ. Getting the key alignment right is 80% of a correct diff; getting the null-safe comparison right is the other 20%.

Primary-key alignment — the foundation.

• The key must be stable and unique. The diff joins on it, so a non-unique key fans out the join and inflates the change count. If no natural key exists, diff on a deterministic surrogate (md5 of the business columns) — but then a value change looks like an add+remove.
• Full outer join, not inner. Inner join hides added and removed keys — the two most important categories. Always full outer.
• Composite keys are fine. Join on (order_id, line_no) when the grain is order-line. The classification logic is unchanged.
• Presence flags classify the row. in_a / in_b derived from a.key IS NOT NULL give you added (only b), removed (only a), matched (both).

Value-level comparison — the null-safe core.

• IS DISTINCT FROM everywhere. Plain = / <> return NULL (falsy) when either side is null, so a NULL → 5 change is missed. IS DISTINCT FROM is the correct comparator.
• Type coercion bites. NUMBER(38,2) vs FLOAT, TIMESTAMP vs TIMESTAMPTZ, trailing-space CHAR vs VARCHAR — cross-engine diffs must normalise types before comparing or every row "changes."
• Report per column. The high-signal output is a per-column changed-count, plus a few example mismatched keys per column, not a giant row dump.
• Tolerances. Floats need an epsilon (ABS(a-b) > 1e-9); timestamps may need truncation to a grain. A strict bitwise compare over-reports on legitimately-equal values.

Checksum pre-filtering — why it exists.

• Comparing every column of every row is expensive. For wide tables, hash the concatenated row into one value and compare hashes first; only rows with different hashes need column-by-column inspection.
• Order and null normalisation. The hash must canonicalise column order, null representation, and type formatting so two "equal" rows hash equal across engines.
• This is the seed of the bisection algorithm. Hash a range of rows, not one row, and you get the cross-database algorithm in Section 3.

Common interview probes on diff mechanics.

• "Why full outer join, not inner?" — inner hides added/removed keys.
• "Why IS DISTINCT FROM and not <>?" — null-safe; <> drops null comparisons.
• "How do you diff when there is no unique key?" — deterministic surrogate hash; accept that value changes read as add+remove.
• "How do you avoid every row showing as changed cross-engine?" — normalise types, null representation, and float/timestamp tolerance before comparing.

Worked example — classifying added, removed, and changed rows

Detailed explanation. The canonical row-level diff produces four buckets — added, removed, changed, unchanged — from a single full outer join. Build it on an orders snapshot compared between two pipeline runs.

• Key. order_id.
• Value columns. status, amount.
• Output. One row per differing key with a classification.

Question. Write a single query that classifies every key as added / removed / changed / same between run_a.orders and run_b.orders.

Input.

order_id	status_a	amount_a	status_b	amount_b
1	paid	50	paid	50
2	paid	90	shipped	90
3	paid	40	(missing)	(missing)
4	(missing)	(missing)	paid	70

Code.

SELECT
    COALESCE(a.order_id, b.order_id)                        AS order_id,
    CASE
        WHEN a.order_id IS NULL                             THEN 'added'
        WHEN b.order_id IS NULL                             THEN 'removed'
        WHEN a.status IS DISTINCT FROM b.status
          OR a.amount IS DISTINCT FROM b.amount             THEN 'changed'
        ELSE 'same'
    END                                                     AS diff_status,
    a.status AS status_a, b.status AS status_b,
    a.amount AS amount_a, b.amount AS amount_b
FROM        run_a.orders a
FULL OUTER JOIN run_b.orders b USING (order_id)
ORDER BY order_id;

Step-by-step explanation.

1. FULL OUTER JOIN ... USING (order_id) aligns all four keys; COALESCE picks whichever side has the key so no order_id is null in the output.
2. Key 4 has a.order_id IS NULL → classified added (only in run_b). Key 3 has b.order_id IS NULL → removed.
3. Key 2 matches on key but status differs (paid vs shipped) → changed. IS DISTINCT FROM handles it even if one side were null.
4. Key 1 matches on key and both columns → same.
5. Downstream, you filter diff_status <> 'same' to get the delta, and you can GROUP BY diff_status for a one-line summary.

Output.

order_id	diff_status	status_a	status_b
1	same	paid	paid
2	changed	paid	shipped
3	removed	paid	(null)
4	added	(null)	paid

Rule of thumb. One full outer join plus one CASE yields all four diff buckets. Always classify before you count — "12 rows changed" means nothing until you know how many of those are adds vs removes vs true value changes.

Worked example — value-level per-column mismatch report

Detailed explanation. For matched keys, the actionable output is a per-column mismatch count with a few example keys, so an engineer can jump straight to the offending column. Build it for a customers diff.

• Matched keys only. Value diff is defined on rows present in both.
• Per-column counters. One changed-count per column.
• Example keys. A handful of ids per changed column for drill-down.

Question. Produce a per-column mismatch summary and up to 3 example ids per changed column for customers.

Input.

id	email_a	email_b	tier_a	tier_b
1	[e-mail]	[e-mail]	gold	gold
2	[e-mail]	[e-mail]	gold	gold
3	[e-mail]	[e-mail]	gold	silver
4	[e-mail]	[e-mail]	gold	gold

Code.

WITH matched AS (
    SELECT a.id,
           a.email AS email_a, b.email AS email_b,
           a.tier  AS tier_a,  b.tier  AS tier_b
    FROM prod.customers a
    JOIN branch.customers b USING (id)   -- inner: matched keys only
)
SELECT
    'email' AS column_name,
    COUNT(*) FILTER (WHERE email_a IS DISTINCT FROM email_b) AS changed,
    (ARRAY_AGG(id) FILTER (WHERE email_a IS DISTINCT FROM email_b))[1:3] AS example_ids
FROM matched
UNION ALL
SELECT
    'tier',
    COUNT(*) FILTER (WHERE tier_a IS DISTINCT FROM tier_b),
    (ARRAY_AGG(id) FILTER (WHERE tier_a IS DISTINCT FROM tier_b))[1:3]
FROM matched;

Step-by-step explanation.

1. The matched CTE inner-joins on id, restricting to keys present in both — value diffs are undefined for added/removed keys.
2. Each COUNT(*) FILTER (WHERE ... IS DISTINCT FROM ...) counts mismatches for one column independently.
3. ARRAY_AGG(id) FILTER (...) collects the offending ids; slicing [1:3] keeps three examples for drill-down without dumping every key.
4. UNION ALL stacks one summary row per column — this is the shape Datafold's value-level report and data-diff --stats present.
5. email shows 2 changed (ids 2, 4); tier shows 1 changed (id 3). An engineer reads the column, not the row count.

Output.

column_name	changed	example_ids
email	2	{2, 4}
tier	1	{3}

Rule of thumb. Value-level diffs must report per column with example keys. A single "N rows changed" number hides which column regressed; the per-column report points straight at the code that moved it.

Worked example — row-hash checksum to skip unchanged rows

Detailed explanation. Comparing every column of every wide row is wasteful when 99.9% of rows are identical. Hash each row into one fingerprint, compare fingerprints, and only column-diff the rows whose hashes differ. Build the row-hash and the fast pre-filter.

• Canonical concatenation. Order columns deterministically; coerce nulls to a sentinel; format types uniformly.
• Hash. md5 (or sha256) of the canonical string.
• Pre-filter. Join on key, compare hashes, keep mismatches for full inspection.

Question. Write a row-hash for orders and a checksum pre-filter that yields only the keys needing a value diff.

Input.

order_id	status	amount	updated_at
1	paid	50	2026-08-01
2	paid	90	2026-08-02

Code.

-- Canonical row hash — null-safe, deterministic column order
CREATE OR REPLACE VIEW prod.orders_hashed AS
SELECT order_id,
       md5(
         COALESCE(status,            '∅') || '|' ||
         COALESCE(amount::text,      '∅') || '|' ||
         COALESCE(updated_at::text,  '∅')
       ) AS row_hash
FROM prod.orders;
-- (identical view branch.orders_hashed over branch.orders)

-- Pre-filter: keys whose fingerprints disagree (or exist on one side only)
SELECT COALESCE(p.order_id, b.order_id) AS order_id,
       CASE WHEN p.order_id IS NULL THEN 'added'
            WHEN b.order_id IS NULL THEN 'removed'
            ELSE 'changed' END          AS status
FROM        prod.orders_hashed   p
FULL OUTER JOIN branch.orders_hashed b USING (order_id)
WHERE p.row_hash IS DISTINCT FROM b.row_hash;

Step-by-step explanation.

1. The hash view concatenates every column in a fixed order with a delimiter, coercing nulls to a sentinel ∅ so NULL and the string '∅' never collide with a real value and null-vs-value changes the hash.
2. ::text casts normalise types so amount 50 and 50.00 must be reconciled by the caller — cross-engine, you normalise scale before hashing.
3. Comparing row_hash IS DISTINCT FROM in the full outer join yields exactly the added/removed/changed keys, skipping all identical rows without ever comparing their individual columns.
4. The mismatched keys are then fed to the per-column value diff (previous example) — you pay the expensive comparison only on the tiny changed set.
5. This two-phase design — cheap hash filter, then expensive column diff on the survivors — is the core efficiency of every serious diff engine.

Output.

order_id	status
(only keys whose row_hash differs, or exist on one side)	changed / added / removed

For two identical tables the pre-filter returns zero rows, and no column-level comparison runs at all.

Rule of thumb. Hash rows to a fingerprint, diff the fingerprints first, and only column-compare the survivors. The checksum pre-filter is what turns an O(columns × rows) diff into an O(rows) hash compare plus O(changed rows) inspection.

Senior interview question on row-level and value-level diffing

A senior interviewer might ask: "Diff a dim_customer table between prod and a dev branch. There is a stable primary key, one column changed on a handful of rows, and a few adds and removes. Show the query that classifies rows, reports value changes per column, and does not fall over on nulls — then explain how you would make it fast on a 200-million-row table."

Solution Using a full-outer-join classifier with a row-hash fast path

-- Phase 1 — fast path: hash filter narrows 200M rows to the changed set
WITH p AS (
    SELECT id,
           md5(COALESCE(name,'∅')||'|'||COALESCE(email,'∅')||'|'||COALESCE(tier,'∅')) AS h
    FROM prod.dim_customer
),
b AS (
    SELECT id,
           md5(COALESCE(name,'∅')||'|'||COALESCE(email,'∅')||'|'||COALESCE(tier,'∅')) AS h
    FROM branch.dim_customer
),
candidates AS (
    SELECT COALESCE(p.id, b.id) AS id,
           p.id IS NOT NULL AS in_prod,
           b.id IS NOT NULL AS in_branch
    FROM p FULL OUTER JOIN b USING (id)
    WHERE p.h IS DISTINCT FROM b.h          -- only differing fingerprints survive
)
-- Phase 2 — expensive column diff, ONLY on the candidate keys
SELECT
    c.id,
    CASE WHEN NOT c.in_prod   THEN 'added'
         WHEN NOT c.in_branch THEN 'removed'
         ELSE 'changed' END                                 AS diff_status,
    (pp.name  IS DISTINCT FROM bb.name )::int               AS d_name,
    (pp.email IS DISTINCT FROM bb.email)::int               AS d_email,
    (pp.tier  IS DISTINCT FROM bb.tier )::int               AS d_tier
FROM candidates c
LEFT JOIN prod.dim_customer   pp ON pp.id = c.id
LEFT JOIN branch.dim_customer bb ON bb.id = c.id
ORDER BY c.id;

Step-by-step trace.

Input: prod = 200,000,000 rows; branch identical except email changed on 2 keys (ids 2, 4), 1 add (id 9), 1 remove (id 7).

1. CTEs p and b compute a null-safe row hash per side — one pass each, O(n).
2. The candidates full outer join keeps only keys whose hashes differ or that exist on one side — 4 rows survive out of 200M.
3. in_prod / in_branch flags classify id 9 as added and id 7 as removed.
4. Phase 2 joins the 4 candidate keys back to the full rows and computes per-column change flags — the expensive comparison runs on 4 rows, not 200M.
5. Final result — d_email = 1 for ids 2 and 4; adds/removes carry null value-flags. Total work: two hash scans + a 4-row inspection.

Output:

id	diff_status	d_name	d_email	d_tier
2	changed	0	1	0
4	changed	0	1	0
7	removed	(null)	(null)	(null)
9	added	(null)	(null)	(null)

Why this works — concept by concept:

• Row-hash fast path — hashing each row to one fingerprint lets a single IS DISTINCT FROM on the hash replace column-by-column comparison for the 99.99% of rows that are identical, collapsing the problem to the changed set.
• Null sentinel in the hash — coercing nulls to ∅ before concatenation makes the hash sensitive to NULL → value transitions, which a naive concatenation (where NULL || x = NULL) would erase.
• Two-phase diff — phase one is O(n) hashing; phase two is O(changed rows) column inspection. The expensive value comparison never touches the unchanged bulk of the table.
• Presence flags for add/remove — in_prod / in_branch derived from the full outer join classify the one-sided keys, keeping adds and removes distinct from true value changes.
• Cost — O(n) for two hash scans plus a hash join, then O(k) for k changed rows. On 200M rows with 4 changes, that is two linear scans and a trivial tail — versus O(n × columns) for a naive full compare.

SQL
Topic — data-validation
Row-level and value-level diff problems

Practice →

SQL Topic — sql SQL join and null-handling problems

Practice →

---

3. data-diff — the open-source diffing engine

data-diff diffs two tables — same database or across databases — using a checksum-bisection algorithm that pulls only the rows that actually differ

The mental model in one line: data-diff is an open-source Python CLI and library that compares two tables identified by connection strings, a key, and a column list, and finds every differing row using recursive checksum bisection — it hashes whole ranges of the key space on each side, compares the range checksums, and descends only into the sub-ranges whose checksums disagree, so on two nearly-identical billion-row tables it transfers kilobytes, not terabytes. It has two engines: joindiff when both tables live in the same database (one SQL JOIN), and hashdiff when they live in different databases (the bisection algorithm over the wire).

The CLI surface — what you actually type.

• Positional args. Two DB_URI TABLE pairs: data-diff postgres://... orders snowflake://... orders.
• -k / --key-columns. The primary key(s) to align on. Required.
• -c / --columns. Extra columns to include in the value comparison. Without them, only the key set is diffed.
• --stats. Print a summary (rows compared, rows different, % different) instead of streaming every differing key.
• -w / --where. A SQL predicate to scope the diff (e.g. updated_at > '2026-08-01') so PR checks diff only recent partitions.

The two engines — joindiff vs hashdiff.

• joindiff (same database). When both tables are in one warehouse, data-diff emits a single SQL statement that full-outer-joins the two tables and aggregates the differences server-side. Exact, fast, no data leaves the warehouse.
• hashdiff (cross database). When the tables are in different systems, it runs the bisection algorithm: checksum ranges on each side independently, compare, descend on mismatch. Minimises bytes transferred.
• Choosing. Same-DB refactors (dev schema vs prod schema in Snowflake) use joindiff; genuine cross-engine (Postgres source vs Snowflake warehouse) uses hashdiff.

The bisection algorithm — why it is cheap.

• Segment the key range. Split [min_key, max_key] into N segments.
• Checksum each segment on each side. A per-segment aggregate hash (sum of row hashes) computed inside each database — only the small checksums cross the network.
• Descend on mismatch only. Segments whose checksums match are proven identical and pruned; only mismatching segments are recursively bisected.
• Stop at a threshold. Below a segment size (--bisection-threshold), pull the actual rows and compare directly. The result: work proportional to the number of differences, not the table size.

dbt integration — diff every changed model.

• data-diff --dbt. Reads the dbt manifest.json, finds the models you changed, and diffs each one's dev build against its prod counterpart automatically — no per-model config.
• Primary keys from dbt. It reads the model's declared primary_key (or a unique test) to know what to align on.
• Slim-CI friendly. Combined with state:modified+, it diffs exactly the models a PR touches and their downstream dependents.

Common interview probes on data-diff.

• "How does it diff cross-database without moving all the data?" — checksum bisection: hash ranges, descend on mismatch.
• "When does it use a join vs the bisection algorithm?" — joindiff same-DB, hashdiff cross-DB.
• "How do you scope a diff to recent data?" — --where on a partition column.
• "How does it know the key and columns for a dbt model?" — reads the dbt manifest and declared primary key.

Worked example — a cross-database diff from the CLI

Detailed explanation. The headline use case: confirm a Postgres source table and its Snowflake replica are in sync after an ingestion change. Run data-diff cross-engine and read --stats.

• Left. postgres://.../orders.
• Right. snowflake://.../ORDERS.
• Key. id; columns. status, amount.

Question. Write the data-diff invocation that diffs the two tables on recent data and prints a summary, and interpret the output.

Input.

Parameter	Value
Left	postgres://cdc_reader@pg/orders
Right	snowflake://svc@acct/RAW.ORDERS
Key	id
Columns	status, amount
Scope	updated_at > '2026-08-15'

Code.

data-diff \
  "postgresql://cdc_reader:***@pg-host:5432/prod" orders \
  "snowflake://svc:***@acct/PROD/RAW?warehouse=WH_XS" ORDERS \
  -k id \
  -c status -c amount \
  -w "updated_at > '2026-08-15'" \
  --stats

# --stats output
- Diff-Total: 3 changed rows out of 84,210 compared
- Diff-Percent: 0.0036%
- Rows Added (in Snowflake, not Postgres): 0
- Rows Removed (in Postgres, not Snowflake): 1
- Rows Changed (value differs): 2
- Columns with changes: amount (2)

Step-by-step explanation.

1. The two DB_URI TABLE pairs name the left (Postgres) and right (Snowflake) tables; credentials come from the URIs (real runs pull them from env, not literals).
2. -k id sets the alignment key; -c status -c amount adds those columns to the value comparison — without -c, only the key set is compared.
3. -w "updated_at > '2026-08-15'" scopes both sides to recent rows so a CI diff runs in seconds against a recent partition instead of the full history.
4. Because the tables are in different engines, data-diff uses hashdiff: it checksums key ranges inside each database and only pulls the handful of rows in mismatching segments.
5. --stats collapses the result to a summary: 3 differing rows out of 84K, one removal, two amount changes — enough to decide whether the ingestion change is safe.

Output.

Metric	Value
Rows compared	84,210
Changed	2 (amount)
Removed	1
Added	0
% different	0.0036%

Rule of thumb. For cross-engine reconciliation, always scope with --where on a partition/updated_at column and read --stats first. You escalate to the full per-row output only when the summary says something changed.

Worked example — how bisection prunes the key space

Detailed explanation. The reason data-diff is cheap on huge tables is that it never compares identical regions. Trace the bisection over a small key range to see the pruning.

• Key range. [1..8], split into segments.
• Checksums. Per-segment hash on each side.
• Difference. One row (key 6) differs.

Question. Trace the bisection descent for keys [1..8] where only key 6 differs, and count how many rows are actually pulled.

Input.

Segment	Keys	checksum_A	checksum_B	match?
whole	1–8	H1	H2	no
left	1–4	La	La	yes
right	5–8	Ra	Rb	no
right-left	5–6	Xa	Xb	no
right-right	7–8	Ya	Ya	yes

Code.

bisect([1..8]):
  checksum(A,1..8)=H1 ; checksum(B,1..8)=H2 ; H1≠H2 → split
    bisect([1..4]): checksum match (La==La) → PRUNE (proven identical)
    bisect([5..8]): Ra≠Rb → split
        bisect([5..6]): Xa≠Xb → below threshold → PULL rows 5,6 ; compare
            → key 5 equal, key 6 DIFFERS  ✎
        bisect([7..8]): Ya==Ya → PRUNE
Rows actually pulled: {5,6}  (2 of 8)

Step-by-step explanation.

1. The whole-range checksums disagree, so the range is split in half — the algorithm never assumes; a top-level mismatch could be anywhere.
2. The left half [1..4] checksums equal on both sides, proving those four rows identical without pulling any of them — the entire segment is pruned.
3. The right half [5..8] disagrees, so it is split again into [5..6] and [7..8].
4. [7..8] matches and is pruned; [5..6] disagrees and is now below the bisection threshold, so the actual rows 5 and 6 are pulled and compared directly, isolating key 6.
5. Only 2 of 8 rows crossed the network. On a billion-row table with a thousand differences, the transferred data is proportional to the differences and the log-depth of the descent, not the table size.

Output.

Rows in table	Rows pulled	Pruned segments
8	2	[1..4], [7..8]

Rule of thumb. Checksum bisection makes diff cost scale with the number of differences, not the table size. That is why data-diff can diff two near-identical billion-row tables in seconds — matching regions are proven equal by one checksum and never touched again.

Worked example — diffing dbt models in Slim CI

Detailed explanation. In a dbt project, you rarely diff a hand-named table — you diff the models a PR changed. data-diff --dbt plus state:modified+ does exactly that. Wire it up.

• Select changed models. state:modified+ = changed models and everything downstream.
• Build into a dev schema. --defer reuses unchanged prod models.
• Diff each built model vs prod.

Question. Show the dbt + data-diff sequence that builds only changed models and diffs each against prod.

Input.

Step	Command intent
select	state:modified+
build	into dbt_ci_pr123 schema, deferring to prod
diff	each changed model dev vs prod

Code.

# 1. Build only the changed models (+downstream) into a PR-scoped schema
dbt build \
  --select state:modified+ \
  --defer --state ./prod-manifest \
  --target ci                       # writes to schema dbt_ci_pr123

# 2. Diff every changed model: dev build vs prod, keys/columns from the manifest
data-diff --dbt \
  --dbt-project-dir . \
  --state ./prod-manifest \
  --select state:modified+

# data-diff --dbt summary (one block per changed model)
revenue_by_order   rows: 1,204,551   different: 12   (0.001%)   cols: revenue
dim_customer       rows:   210,004   different:  0   (0.000%)   cols: —

Step-by-step explanation.

1. dbt build --select state:modified+ compares the current project to the saved prod manifest and builds only the changed models plus their downstream dependents into an isolated CI schema.
2. --defer --state ./prod-manifest lets unchanged upstream models resolve to prod instead of rebuilding the whole DAG — the Slim CI optimisation.
3. data-diff --dbt reads the same manifest to discover each changed model's database location, primary key, and columns, so you write no per-model diff config.
4. It diffs each changed model's dev build against its prod counterpart and prints one summary block per model.
5. revenue_by_order shows 12 differing rows on revenue — the regression — while dim_customer shows 0, so the reviewer's attention goes straight to the one model that moved data.

Output.

Model	rows	different	columns
revenue_by_order	1,204,551	12	revenue
dim_customer	210,004	0	—

Rule of thumb. In dbt CI, always pair data-diff --dbt with state:modified+ and --defer. You build and diff only what the PR touched, so the check stays fast even in a thousand-model project.

Senior interview question on data-diff

A senior interviewer might ask: "You maintain a Postgres OLTP source and a Snowflake warehouse fed by an ingestion job. After every ingestion change you need to prove the warehouse still matches the source, cheaply, in CI. Design the diff: which engine, how you scope it, how it stays fast on a 500-million-row table, and how you fail the build on unexpected drift."

Solution Using cross-database hashdiff scoped by partition with a stats gate

#!/usr/bin/env bash
set -euo pipefail

# Scope to the partition the ingestion job just wrote (cheap, targeted)
SINCE="${DIFF_SINCE:-$(date -u -d '2 days ago' +%F)}"

# Cross-engine diff: Postgres source vs Snowflake warehouse (hashdiff)
data-diff \
  "postgresql://${PG_USER}:${PG_PW}@${PG_HOST}:5432/prod" orders \
  "snowflake://${SF_USER}:${SF_PW}@${SF_ACCT}/PROD/RAW?warehouse=WH_XS" ORDERS \
  -k id \
  -c status -c amount -c updated_at \
  -w "updated_at >= '${SINCE}'" \
  --bisection-threshold 16384 \
  --stats \
  --json > diff.json

# Gate: fail the build if any rows differ beyond an allowed tolerance
python - <<'PY'
import json, sys
r = json.load(open("diff.json"))
changed = r["rows_different"]; compared = r["rows_compared"] or 1
pct = 100 * changed / compared
print(f"diff: {changed}/{compared} = {pct:.4f}%")
if changed > int(__import__("os").environ.get("ALLOWED_DIFF_ROWS", "0")):
    print("::error::unexpected data drift between Postgres and Snowflake")
    sys.exit(1)
PY

Step-by-step trace.

Input: 500M-row orders; the last ingestion wrote the 2026-08-16 and 2026-08-17 partitions; 3 rows landed with a wrong amount.

1. SINCE scopes both sides with -w updated_at >= '2026-08-16', so the diff considers ~84K recent rows, not 500M.
2. Because the tables are in different engines, data-diff runs hashdiff — checksums key ranges inside Postgres and inside Snowflake, transferring only checksums.
3. --bisection-threshold 16384 sets the segment size at which it stops bisecting and pulls actual rows — tuned so the final row-pull stays small.
4. --stats --json emits a machine-readable summary; the Python gate parses rows_different.
5. Final result — 3 rows differ, ALLOWED_DIFF_ROWS=0, so the script exits non-zero and fails the build with a clear error.

Output:

Metric	Value
Engine	hashdiff (cross-database)
Rows compared (scoped)	~84,000
Rows different	3
Data transferred	checksums + 3 rows
Build result	fail (drift > 0 allowed)

Why this works — concept by concept:

• hashdiff cross-database — with the two tables in different engines, checksum bisection is the only way to diff exactly without streaming one table into the other; it moves checksums, not rows.
• Partition-scoped --where — diffing only the partitions the ingestion job wrote turns a 500M-row problem into an 84K-row problem, which is what makes the check CI-fast.
• bisection-threshold — the knob that trades network round-trips for row pulls; a larger threshold pulls more rows but bisects less, tuned so the leaf comparison stays cheap.
• stats + json gate — a machine-readable summary lets a tiny script enforce policy (ALLOWED_DIFF_ROWS) and fail the build, converting a diff into an enforceable gate.
• Cost — O(log range) network round-trips for the descent plus O(differences) row pulls; scoped to a partition, both terms are small regardless of the 500M-row table size.

ETL
Topic — etl
Cross-database reconciliation problems

Practice →

SQL Topic — data-transformation dbt model transformation problems

Practice →

---

4. Datafold and CI — PR checks that gate every merge

Wire the diff into the pull request so every code change ships with its data impact — the shift-left move Datafold productised

The mental model in one line: a diff is only a regression test when it runs automatically on every pull request and can block the merge — you build the branch's models into a throwaway schema in CI, diff each changed model against prod, post the added/removed/changed summary as a PR comment, and set the check to fail on unexpected change, which turns "review the code" into "review the code and its exact data impact". Datafold packages this as a hosted CI app with column-level lineage; you can also self-host it with data-diff and GitHub Actions.

The PR-check pipeline — five stages.

• Trigger. on: pull_request — every PR to main that touches models.
• Build. dbt build --select state:modified+ --defer into a PR-scoped schema (dbt_ci_pr<number>).
• Diff. data-diff --dbt each changed model, dev build vs prod.
• Report. Post the diff summary as a PR comment / status check.
• Gate. Fail the required check on unexpected change; a human approves or the author fixes.

What "gate" means — required checks and human-in-the-loop.

• Required status check. Branch protection makes the diff check mandatory — you cannot merge while it is red.
• Zero-diff auto-path. A refactor that diffs to 0 rows is a green check the reviewer trusts in seconds.
• Non-zero → review. A non-zero diff is not automatically a failure; it is a decision. The comment shows exactly what changed so a human confirms it was intended.
• Expected-change annotations. Teams mark intended changes (a metric redefinition) so the gate distinguishes "intended, approved" from "unexpected, block."

Datafold Cloud on top of the raw diff.

• Column-level lineage. It knows revenue_by_order.revenue feeds revenue_daily feeds exec_dashboard, so a diff comes with impact: "this change touches 3 downstream models and 1 BI dashboard."
• Value-level diff UI. Browse the actual changed rows and columns, not just counts.
• Managed CI app. Installs as a GitHub/GitLab app that comments on PRs without you maintaining the workflow.
• The build-vs-buy line. Open-source data-diff gives you the comparison; Datafold gives you lineage-aware impact and the managed UX.

Common interview probes on CI diffing.

• "How do you make a diff block a merge?" — required status check via branch protection.
• "Is a non-zero diff always a failure?" — no; it is a decision surface — post it, let a human confirm intent.
• "How do you know a change's downstream blast radius?" — column-level lineage (Datafold) or a dbt DAG walk.
• "How do you keep the check fast?" — state:modified+, --defer, and --where scoping.

Worked example — a GitHub Actions diff-on-PR workflow

Detailed explanation. The self-hosted version of the Datafold CI app is a GitHub Actions workflow that builds changed models and diffs them on every PR. Build the workflow.

• Trigger. pull_request on model paths.
• Steps. checkout → install → build changed models → diff → comment.
• Gate. Non-zero unexpected diff fails the job.

Question. Write the GitHub Actions workflow that runs data-diff --dbt on the models a PR changes and posts a summary.

Input.

Component	Value
Trigger	pull_request touching models/**
Select	state:modified+
Baseline	prod manifest artifact
Output	PR comment + status

Code.

name: data-diff-pr
on:
  pull_request:
    paths: ["models/**", "dbt_project.yml"]

jobs:
  diff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Install dbt + data-diff
        run: pip install dbt-snowflake "data-diff[snowflake]"

      - name: Fetch prod manifest (the baseline)
        run: aws s3 cp s3://ci-artifacts/prod/manifest.json ./prod-manifest/manifest.json

      - name: Build changed models into a PR schema
        env: { DBT_CI_SCHEMA: "dbt_ci_pr${{ github.event.number }}" }
        run: dbt build --select state:modified+ --defer --state ./prod-manifest --target ci

      - name: Diff changed models vs prod
        run: |
          data-diff --dbt --state ./prod-manifest --select state:modified+ \
            --json > diff.json || true
          python summarize_diff.py diff.json >> "$GITHUB_STEP_SUMMARY"

      - name: Comment + gate
        run: python gate_diff.py diff.json   # exits non-zero on unexpected change

Step-by-step explanation.

1. The trigger scopes to PRs that touch models/**, so doc-only or CI-config PRs skip the (non-trivial) diff job.
2. The prod manifest.json is fetched as the baseline — it is what state:modified+ diffs against to know which models changed.
3. dbt build --select state:modified+ --defer builds only changed models plus downstream into a PR-numbered schema, deferring unchanged upstreams to prod.
4. data-diff --dbt --json diffs each changed model against prod and writes machine-readable results; || true keeps the step from failing before the gate can post the summary.
5. gate_diff.py posts the summary and exits non-zero on unexpected change, so branch protection blocks the merge until a human resolves it.

Output.

Stage	Result
Build	dbt_ci_pr123 schema populated
Diff	diff.json with per-model deltas
Summary	posted to PR + step summary
Gate	red if unexpected change

Rule of thumb. Scope the workflow to model paths, defer to a saved prod manifest, and split "produce diff" from "gate on diff" into two steps so the summary always posts even when the gate fails.

Worked example — turning a diff into a reviewable PR comment

Detailed explanation. A raw JSON diff is not review-friendly. The CI job renders it into a compact comment: per-model added/removed/changed, the top changed columns, and a verdict. Build the summariser.

• Per model. rows, added, removed, changed, top columns.
• Verdict. 0 changed → "safe"; changed → "review".
• Format. Markdown table in the PR comment.

Question. Write the summariser that turns diff.json into a Markdown verdict block.

Input.

model	added	removed	changed	top_columns
revenue_by_order	0	0	12	revenue
dim_customer	1	0	0	—

Code.

import json, sys

def summarize(path: str) -> str:
    models = json.load(open(path))["models"]
    lines = ["### 🔎 data-diff summary", "", "| model | added | removed | changed | columns |",
             "|---|---|---|---|---|"]
    verdict = "safe"
    for m in models:
        changed = m["changed"]
        if m["added"] or m["removed"] or changed:
            verdict = "review"
        cols = ", ".join(m["top_columns"]) or "—"
        lines.append(f"| `{m['name']}` | {m['added']} | {m['removed']} | {changed} | {cols} |")
    badge = "✅ no data change" if verdict == "safe" else "⚠️ data changed — review below"
    lines += ["", f"**Verdict:** {badge}"]
    return "\n".join(lines)

if __name__ == "__main__":
    print(summarize(sys.argv[1]))

Step-by-step explanation.

1. The summariser loads the per-model diff results produced by data-diff --json.
2. It builds a Markdown table — one row per changed model — because a table is what a reviewer skims fastest on a PR.
3. Any non-zero added/removed/changed flips the verdict from safe to review; a model with all zeros contributes nothing alarming.
4. top_columns names which columns moved, so the reviewer's eye goes to revenue on revenue_by_order immediately.
5. The verdict badge is the one-glance signal: green "no data change" for a clean refactor, amber "data changed — review" otherwise.

Output.

### 🔎 data-diff summary

| model | added | removed | changed | columns |
|---|---|---|---|---|
| revenue_by_order | 0 | 0 | 12 | revenue |
| dim_customer | 1 | 0 | 0 | — |

**Verdict:** ⚠️ data changed — review below

Rule of thumb. Render diffs as a per-model Markdown table with a single verdict badge. Reviewers approve on the badge and drill into the table only when it is amber — that is what keeps the check from becoming noise they ignore.

Worked example — expected vs unexpected change classification

Detailed explanation. Not every diff should block. A metric redefinition should change numbers; the gate must distinguish intended change (approved) from regression (blocked). Encode intent in the PR.

• Intent file. The PR includes an expected_changes.yml listing models allowed to change.
• Gate logic. Changed model on the allow-list → pass with a note; not on the list → fail.
• Audit. The allow-list is reviewed like code.

Question. Write the gate that passes allow-listed changes and fails everything else.

Input.

model	changed	on allow-list?	gate
revenue_by_order	12	no	fail
margin_pct	4,000	yes (redefinition)	pass (noted)

Code.

import json, sys, yaml

diff = json.load(open("diff.json"))["models"]
allow = set(yaml.safe_load(open("expected_changes.yml")).get("models", []))

failures = []
for m in diff:
    if m["added"] or m["removed"] or m["changed"]:
        if m["name"] in allow:
            print(f"::notice::{m['name']} changed as intended (allow-listed)")
        else:
            failures.append(m["name"])

if failures:
    print(f"::error::unexpected data change in: {', '.join(failures)}")
    sys.exit(1)
print("all changes intended or none present")

Step-by-step explanation.

1. The gate loads the diff results and the PR's expected_changes.yml allow-list.
2. For each model with any change, it checks the allow-list: margin_pct is listed (an intended redefinition), so it passes with a GitHub ::notice::.
3. revenue_by_order changed but is not allow-listed, so it is collected as a failure.
4. Any un-allow-listed change makes the job exit non-zero — the required check goes red and the merge is blocked.
5. Because expected_changes.yml lives in the PR and is reviewed like code, "intended change" becomes an auditable, approved decision rather than a reviewer overriding a red check by memory.

Output.

model	verdict
margin_pct	pass (allow-listed notice)
revenue_by_order	fail (unexpected)

Rule of thumb. Make "intended change" explicit and reviewable with an allow-list in the PR. A diff gate that blocks all change trains people to ignore it; a gate that blocks only unexpected change stays trustworthy.

Senior interview question on CI diffing

A senior interviewer might ask: "Set up data diffing as a required PR check for a 400-model dbt project on Snowflake. Cover the CI workflow, how you keep it fast, how you present the result to reviewers, how you distinguish intended metric changes from regressions, and how downstream impact factors into the decision."

Solution Using dbt Slim CI + data-diff with an allow-list gate and lineage-aware impact

# .github/workflows/data-diff.yml
name: data-diff
on:
  pull_request:
    paths: ["models/**", "macros/**", "dbt_project.yml"]

jobs:
  diff:
    runs-on: ubuntu-latest
    concurrency: diff-${{ github.event.number }}   # one run per PR
    steps:
      - uses: actions/checkout@v4
      - run: pip install dbt-snowflake "data-diff[snowflake]" pyyaml

      - name: Baseline manifest
        run: aws s3 cp s3://ci/prod/manifest.json ./prod/manifest.json

      - name: Slim build (changed + downstream only)
        run: dbt build --select state:modified+ --defer --state ./prod --target ci

      - name: Diff changed models vs prod
        run: data-diff --dbt --state ./prod --select state:modified+ --json > diff.json || true

      - name: Summarise for reviewers
        run: python summarize_diff.py diff.json >> "$GITHUB_STEP_SUMMARY"

      - name: Gate on unexpected change (allow-list + impact)
        run: python gate_diff.py diff.json expected_changes.yml

# gate_diff.py — allow-list + downstream-impact aware gate
import json, sys, yaml

diff  = {m["name"]: m for m in json.load(open(sys.argv[1]))["models"]}
spec  = yaml.safe_load(open(sys.argv[2]))
allow = set(spec.get("models", []))

unexpected = []
for name, m in diff.items():
    if m["added"] or m["removed"] or m["changed"]:
        impact = m.get("downstream", [])       # models/dashboards fed by this one
        tag = f"{name} (impacts {len(impact)} downstream)"
        if name in allow:
            print(f"::notice::intended change — {tag}")
        else:
            unexpected.append(tag)

if unexpected:
    print("::error::unexpected data change: " + "; ".join(unexpected))
    sys.exit(1)
print("diff gate passed")

Step-by-step trace.

Input: a PR edits margin_pct (intended redefinition, allow-listed, 4,000 rows changed) and accidentally changes revenue_by_order (12 rows, feeds 2 dashboards).

1. The Slim build compiles only margin_pct, revenue_by_order, and their downstream into a PR schema, deferring the other ~396 models to prod.
2. data-diff --dbt diffs just those changed models against prod and writes diff.json with per-model added/removed/changed and a downstream list.
3. summarize_diff.py posts a per-model table so reviewers see both changes at a glance.
4. gate_diff.py allow-lists margin_pct (notice) but flags revenue_by_order as unexpected — and annotates that it impacts 2 downstream consumers.
5. Final result — the required check is red, the PR is blocked, and the comment tells the author exactly which un-intended model changed and how far the blast radius reaches.

Output:

model	changed	downstream	gate
margin_pct	4,000	1	pass (intended)
revenue_by_order	12	2	fail (unexpected)

Why this works — concept by concept:

• Slim CI (state:modified+ + defer) — building and diffing only changed models plus downstream keeps a 400-model project's PR check to seconds, not a full-DAG rebuild.
• Required status check — branch protection on the diff job is what makes it a gate; without it, a red diff is advisory and gets merged past.
• Allow-list gate — encoding intended changes in a reviewed expected_changes.yml distinguishes an approved metric redefinition from a regression, so the gate blocks only the unexpected.
• Lineage-aware impact — attaching the downstream count (the value Datafold's column-level lineage provides) turns "12 rows changed" into "12 rows changed, feeding 2 dashboards," which is what makes the reviewer take it seriously.
• Cost — O(changed models) build + diff per PR, independent of total project size; the concurrency guard ensures one run per PR so cost does not multiply on rapid pushes.

SQL
Topic — data-validation
CI data-validation and PR-gate problems

Practice →

Design Topic — design Design problems on CI/CD for data pipelines

Practice →

---

5. Regression testing pipelines end to end

The same diff engine spans three stages — dev PR check, pre-prod dual-run, and continuous production monitoring — with drift thresholds turning "different" into "too different"

The mental model in one line: regression testing a data pipeline means running the same diff engine at three stages — a dev-time PR check (branch vs prod), a pre-prod dual-run (new pipeline vs old pipeline over a full cycle), and a continuous production monitor (today's load vs yesterday's, or warehouse vs source) — each gated not on "any change" but on a drift threshold that encodes how much change is acceptable before you page a human. A refactor's PR check wants a zero-row threshold; a production monitor watching naturally-noisy data wants a small percentage band.

Pre-prod dual-run — proving a migration at full scale.

• Run both pipelines. The old and new pipelines process the same input over a real cycle, writing to separate schemas.
• Diff the full result. Not a PR-time sample — the whole output, because migrations fail on edge cases a sample misses.
• Gate promotion. Cut consumers over only when the diff is within threshold, cycle after cycle.
• This is CDC/reconciliation's cousin. The dual-run is exactly the "old system vs new system" comparison a warehouse migration lives or dies on.

Drift thresholds — from "different" to "too different."

• Zero-threshold (refactors). A change claiming no data impact must diff to exactly 0 rows. Any change fails.
• Percentage band (noisy data). A production monitor over data that legitimately moves (late-arriving events, restatements) allows, say, ≤ 0.1% of rows changed before alerting.
• Per-column thresholds. A revenue column may allow 0 drift while a last_seen_at column allows more — thresholds are per column, not global.
• Absolute + relative. Combine "≤ 0.1% of rows" with "≤ 100 rows" so a small table's noise and a huge table's noise are both bounded sensibly.

Continuous monitoring — catching drift code review cannot.

• Scheduled diff. A nightly job diffs the fresh load against the prior load (or the warehouse against the source) and records the drift.
• Alert on breach. Drift beyond threshold pages on-call; within threshold logs a metric for trend-watching.
• Why it is distinct from the PR check. Clean code still drifts when upstream data changes — a source schema tweak, a new event type, a broken partition. Only a standing monitor catches that.
• Trend, not just threshold. Recording drift over time surfaces slow creep that no single night's threshold would trip.

Common interview probes on pipeline regression testing.

• "Why a dual-run and not just the PR diff?" — migrations fail on edge cases a PR-time sample misses; dual-run diffs the full cycle.
• "How do you avoid alert fatigue on noisy data?" — percentage + absolute drift thresholds, per column.
• "What does a monitor catch that the PR check cannot?" — upstream data drift after the code is already merged.
• "How do you promote a migrated pipeline safely?" — gate cutover on N consecutive in-threshold dual-run cycles.

Worked example — a pre-prod dual-run reconciliation

Detailed explanation. Before cutting consumers from an old aggregation job to a rewritten one, run both over the same cycle and diff the outputs. Build the reconciliation.

• Old output. legacy.daily_revenue.
• New output. rewrite.daily_revenue.
• Gate. Promote only if 0 rows differ (or within a tiny tolerance).

Question. Write the dual-run reconciliation that compares the two daily-revenue outputs and produces a promotion verdict.

Input.

day	revenue_legacy	revenue_rewrite
2026-08-15	10,000.00	10,000.00
2026-08-16	12,500.00	12,500.00
2026-08-17	9,800.00	9,800.00

Code.

WITH d AS (
    SELECT COALESCE(l.day, r.day)                         AS day,
           l.revenue                                      AS rev_legacy,
           r.revenue                                      AS rev_rewrite,
           ABS(COALESCE(l.revenue,0) - COALESCE(r.revenue,0)) AS abs_diff
    FROM        legacy.daily_revenue  l
    FULL OUTER JOIN rewrite.daily_revenue r USING (day)
)
SELECT
    COUNT(*)                                     AS days_compared,
    COUNT(*) FILTER (WHERE abs_diff > 0.005)      AS days_differing,
    MAX(abs_diff)                                AS max_abs_diff,
    CASE WHEN COUNT(*) FILTER (WHERE abs_diff > 0.005) = 0
         THEN 'PROMOTE' ELSE 'HOLD' END          AS verdict
FROM d;

Step-by-step explanation.

1. The FULL OUTER JOIN on day aligns both outputs so a day missing from either side is caught, not silently dropped.
2. abs_diff computes the absolute revenue difference per day with COALESCE so a missing side counts as a difference, not a null.
3. The > 0.005 tolerance absorbs sub-cent floating-point noise while still catching any real discrepancy — a per-column tolerance appropriate for a money column.
4. The aggregate reports days compared, days differing, and the worst single difference — the summary a promotion decision needs.
5. The CASE renders the verdict: all three days match within tolerance, so PROMOTE. One differing day would flip it to HOLD.

Output.

days_compared	days_differing	max_abs_diff	verdict
3	0	0.00	PROMOTE

Rule of thumb. A dual-run reconciliation must diff the full cycle output with a money-appropriate tolerance and emit a single PROMOTE/HOLD verdict. Promote only after N consecutive PROMOTE cycles — one clean night is a coincidence, three is evidence.

Worked example — a drift-threshold gate on a noisy table

Detailed explanation. A production monitor over user_events sees legitimate churn (late-arriving events), so a zero-threshold would page every night. Encode a combined percentage + absolute threshold. Build the gate.

• Diff. Today's load vs yesterday's, per key.
• Threshold. ≤ 0.1% of rows AND ≤ 500 rows changed.
• Breach. Either bound exceeded → alert.

Question. Write the drift computation and the combined-threshold decision for user_events.

Input.

Metric	Value
rows compared	4,000,000
rows changed	3,200
pct threshold	0.1% (= 4,000)
abs threshold	500

Code.

def drift_gate(rows_compared: int, rows_changed: int,
               pct_threshold: float = 0.001, abs_threshold: int = 500) -> dict:
    pct = rows_changed / max(rows_compared, 1)
    within_pct = rows_changed <= pct_threshold * rows_compared
    within_abs = rows_changed <= abs_threshold
    # Breach if EITHER bound is exceeded (both must hold to stay green)
    ok = within_pct and within_abs
    return {
        "pct": round(pct * 100, 4),
        "within_pct": within_pct,
        "within_abs": within_abs,
        "status": "OK" if ok else "ALERT",
    }

print(drift_gate(4_000_000, 3_200))
# → {'pct': 0.08, 'within_pct': True, 'within_abs': False, 'status': 'ALERT'}

Step-by-step explanation.

1. pct is the changed fraction — 3,200 / 4,000,000 = 0.08%, comfortably inside the 0.1% band.
2. within_pct is True: 3,200 ≤ 4,000 (0.1% of 4M).
3. within_abs is False: 3,200 > 500 — the absolute bound is breached even though the percentage is fine.
4. The gate requires both bounds to hold, so the status is ALERT — the absolute cap catches a spike that the percentage alone would wave through on a large table.
5. This combined bound is the anti-alert-fatigue design: percentage handles scale, absolute handles "a small number of important rows moved."

Output.

pct	within_pct	within_abs	status
0.08%	True	False	ALERT

Rule of thumb. Gate production drift on percentage AND absolute bounds together. Percentage alone lets big tables hide real regressions; absolute alone pages constantly on huge tables. Requiring both keeps the monitor sensitive without being noisy.

Worked example — scheduling continuous reconciliation

Detailed explanation. The monitor is a scheduled job that diffs the latest load against the baseline, records drift as a metric, and alerts on breach. Build the scheduled reconciliation task.

• Schedule. Nightly after the load completes.
• Baseline. Prior day's load (or the source).
• Emit. A drift metric + an alert on breach.

Question. Write the Airflow task that diffs the latest orders load against the source and alerts on threshold breach.

Input.

Component	Value
Source	postgres orders (recent partition)
Target	snowflake ORDERS
Schedule	daily, post-load
Threshold	≤ 0.05% and ≤ 200 rows

Code.

from airflow.decorators import task
import subprocess, json

@task
def reconcile_orders(ds: str):
    """Nightly diff of the day's orders load: Snowflake vs Postgres source."""
    out = subprocess.run(
        ["data-diff",
         "postgresql://cdc_reader@pg/prod", "orders",
         "snowflake://svc@acct/PROD/RAW", "ORDERS",
         "-k", "id", "-c", "status", "-c", "amount",
         "-w", f"updated_at::date = '{ds}'",
         "--stats", "--json"],
        capture_output=True, text=True, check=True,
    )
    r = json.loads(out.stdout)
    compared, changed = r["rows_compared"], r["rows_different"]
    pct = changed / max(compared, 1)

    # Emit a metric for trend-watching (StatsD/Prometheus pushgateway)
    emit_metric("orders.reconcile.drift_pct", pct * 100)

    breach = changed > 0.0005 * compared or changed > 200
    if breach:
        raise ValueError(
            f"orders drift breach on {ds}: {changed}/{compared} = {pct*100:.4f}%"
        )
    return {"date": ds, "changed": changed, "compared": compared}

Step-by-step explanation.

1. The task runs after the daily load and diffs only that day's partition (updated_at::date = ds) so it stays cheap on a huge table.
2. It shells out to data-diff cross-engine (Postgres source vs Snowflake target) with --stats --json for a machine-readable summary.
3. It computes the drift percentage and emits it as a metric every run — even clean runs — so a dashboard can show slow creep over weeks.
4. The breach test combines percentage (0.05%) and absolute (200 rows) bounds, matching the anti-fatigue design.
5. On breach it raises, which fails the Airflow task and triggers the on-call alert; within threshold it returns the counts for the run log.

Output.

date	changed	compared	metric emitted	task
2026-08-17	3	84,000	drift_pct=0.0036	success
2026-08-18	900	84,000	drift_pct=1.07	failed (breach)

Rule of thumb. Emit the drift metric on every run, not only on breach. The alert catches acute regressions; the recorded trend catches the slow creep that no single threshold would ever trip.

Senior interview question on pipeline regression testing

A senior interviewer might ask: "You are migrating a nightly revenue pipeline to a rewritten dbt version, and you also want ongoing protection after cutover. Design the full regression-testing program: the pre-prod dual-run and its promotion gate, the drift thresholds you would set, and the production monitor that keeps watching once the rewrite is live."

Solution Using a dual-run promotion gate plus a threshold-based production monitor

# regression_program.py — dual-run gate (pre-prod) + monitor (prod)
import subprocess, json

def diff(model: str, since: str) -> dict:
    out = subprocess.run(
        ["data-diff", "--dbt", "--select", model,
         "-w", f"day >= '{since}'", "--stats", "--json"],
        capture_output=True, text=True, check=True)
    return json.loads(out.stdout)

def dual_run_gate(model: str, since: str, cycles_required: int, history: list[bool]) -> str:
    """Pre-prod: promote only after N consecutive in-tolerance cycles."""
    r = diff(model, since)
    in_tol = r["rows_different"] == 0            # revenue: zero tolerance
    history.append(in_tol)
    consecutive = 0
    for ok in reversed(history):
        if ok: consecutive += 1
        else: break
    return "PROMOTE" if consecutive >= cycles_required else "HOLD"

def prod_monitor(model: str, day: str,
                 pct_threshold=0.0005, abs_threshold=200) -> str:
    """Post-cutover: alert on drift beyond combined thresholds."""
    r = diff(model, day)
    compared, changed = r["rows_compared"], r["rows_different"]
    breach = changed > pct_threshold * compared or changed > abs_threshold
    emit_metric(f"{model}.drift_pct", 100 * changed / max(compared, 1))
    return "ALERT" if breach else "OK"

Step-by-step trace.

Input: daily_revenue rewrite; three consecutive nightly dual-runs all diff to 0 rows; after cutover, one night drifts 900 rows out of 84,000.

1. Each pre-prod night, dual_run_gate diffs the rewrite's full cycle against the legacy output with zero tolerance (revenue must match exactly).
2. It appends each night's pass/fail to history and counts consecutive passes from the most recent backward.
3. After three consecutive in-tolerance nights (cycles_required=3), the gate returns PROMOTE — the rewrite earns cutover on evidence, not one lucky night.
4. Post-cutover, prod_monitor diffs each day's load and applies the combined 0.05% / 200-row thresholds.
5. Final result — the migration promotes after 3 clean cycles; the later 900-row night breaches the absolute bound and returns ALERT, paging on-call for a regression the merged code review could never have caught.

Output:

Stage	Signal	Result
Dual-run night 1–3	0 rows differ each	PROMOTE after night 3
Prod monitor (clean)	3 rows, drift 0.0036%	OK
Prod monitor (breach)	900 rows, drift 1.07%	ALERT (abs bound)

Why this works — concept by concept:

• Dual-run full-cycle diff — comparing the rewrite against the legacy output over the entire cycle (not a sample) catches the edge-case rows a PR-time diff would miss, which is where migrations actually break.
• Consecutive-cycle promotion gate — requiring N in-tolerance cycles before cutover turns "it matched once" into "it matches reliably," the difference between a coincidence and a proof.
• Zero tolerance for revenue, thresholds for noise — the tolerance is per pipeline: a money pipeline promotes on exact match, while the standing monitor tolerates a small, bounded drift band on naturally-noisy loads.
• Combined percentage + absolute monitor — the post-cutover monitor requires both bounds so it stays sensitive on huge tables (absolute) without paging on ordinary scale-driven churn (percentage).
• Cost — each diff is a scoped, checksum-bisected comparison (O(differences)); the program is three such diffs per stage, so the whole regression-testing spine costs a handful of cheap diffs per day.

SQL
Topic — data-transformation
Pipeline dual-run and reconciliation problems

Practice →

Python
Topic — data-processing
Data-processing problems on drift monitoring

Practice →

---

Cheat sheet — data diffing recipes

• What a diff is. A data diff compares two versions of a dataset — old vs new code, prod vs dev, source vs target — aligned on a primary key, and reports which keys were added/removed/changed plus which columns moved inside changed rows. It asserts on the delta; dbt test asserts on rules. Keep both.
• Three grains, cheapest first. Schema diff (information_schema set-difference), then key/row diff (EXCEPT on the PK), then value-level diff (per-column IS DISTINCT FROM on matched keys). Stop at the first clean grain; always report value changes per column.
• Full-outer-join diff template. SELECT COALESCE(a.k,b.k) k, CASE WHEN a.k IS NULL THEN 'added' WHEN b.k IS NULL THEN 'removed' WHEN a.col IS DISTINCT FROM b.col THEN 'changed' ELSE 'same' END FROM a FULL OUTER JOIN b USING (k). Full outer (never inner) so adds and removes surface; IS DISTINCT FROM (never <>) so nulls compare safely.
• Row-hash checksum template. md5(COALESCE(c1,'∅')||'|'||COALESCE(c2::text,'∅')||...) per row; full-outer-join the hashes and keep row_hash IS DISTINCT FROM. Coerce nulls to a sentinel and normalise types/scale so equal rows hash equal cross-engine. Diff hashes first, column-compare only the survivors.
• data-diff CLI. data-diff DB1 tableA DB2 tableB -k id -c col1 -c col2 -w "updated_at > '...'" --stats. joindiff when both tables share a database (one SQL join); hashdiff cross-database (checksum bisection — hash key ranges, descend only where checksums differ). Scope with --where to keep CI fast.
• data-diff + dbt. data-diff --dbt --state ./prod --select state:modified+ reads the manifest for each changed model's location, key, and columns, and diffs its dev build against prod — pair with dbt build --select state:modified+ --defer (Slim CI) so you build and diff only what the PR touched.
• GitHub Actions PR-check skeleton. on: pull_request (paths models/**) → checkout → pip install dbt-x data-diff → fetch prod manifest → dbt build --select state:modified+ --defer → data-diff --dbt ... --json → summarise to $GITHUB_STEP_SUMMARY → gate script exits non-zero on unexpected change. Make the job a required status check so it blocks merge.
• Expected vs unexpected gate. Keep a reviewed expected_changes.yml allow-list in the PR. Changed model on the list → pass with a ::notice::; not on the list → ::error:: + sys.exit(1). A gate that blocks all change gets ignored; one that blocks only unexpected change stays trusted.
• Drift thresholds. Refactors: zero tolerance (0 rows differ). Noisy production data: combine percentage (changed ≤ 0.1% × compared) AND absolute (changed ≤ 500) so scale and small-important-row spikes are both bounded. Set thresholds per column (revenue = 0, last_seen_at = looser).
• Three lifecycle stages, one engine. Dev PR check (branch vs prod, gate the merge), pre-prod dual-run (new vs old pipeline, full cycle, promote after N in-tolerance cycles), production monitor (load N vs N-1 or warehouse vs source, alert on threshold breach). Emit the drift metric every run for trend-watching, not only on breach.
• Diff scope decision matrix. Same warehouse → joindiff (SQL full outer join). Cross-engine → hashdiff (bisection). Huge table → scope with --where on a partition column. No unique key → deterministic surrogate hash (value changes then read as add+remove). Wide table → row-hash pre-filter before column diff.
• Cost model. Naive value diff = O(rows × columns). Row-hash pre-filter = O(rows) hash scan + O(changed) inspection. Cross-database bisection = O(log range) round-trips + O(differences) pulled rows. Scoping + checksums are what make an exact diff cheap enough for a per-PR check.

Frequently asked questions

What is a data diff in one sentence?

A data diff compares two versions of a dataset — typically the output of your current code versus a proposed change, or a source table versus its warehouse copy — by aligning rows on a primary key and comparing values column by column, then reporting exactly which rows were added, removed, or changed and which columns inside the changed rows moved. Unlike a dbt test, which checks rules you wrote in advance (unique, not-null, accepted-values), a diff asserts on the difference itself, so it catches the regressions you never thought to assert. It is the closest thing data pipelines have to a code diff: instead of "which lines changed," it answers "which rows and values changed."

How is a data diff different from a dbt test or a unit test?

A dbt test and a unit test both encode expectations you predicted — a uniqueness rule, an accepted range, a fixed input mapped to a fixed expected output. They pass as long as those specific rules hold, even when a refactor silently moves numbers in ways no rule forbids. A data diff has no pre-written expectations: it runs the old transformation and the new transformation over the same real data and compares the outputs, so the previous behaviour is the oracle. The two are complements, not substitutes — keep dbt test for invariants and add a diff to catch unpredicted regressions. The single most dangerous PR in analytics, "a refactor that should not change any data," is exactly the case a diff proves and tests cannot.

Datafold vs open-source data-diff — which do I use?

data-diff is the open-source Python CLI and library (open-sourced by Datafold) that diffs two tables — same database or cross-database — using a checksum-bisection algorithm, integrates with dbt, and is free to self-host in CI. Datafold is the commercial platform built around the same core plus column-level lineage, a managed CI app that posts diff summaries as pull-request comments, downstream impact analysis, and a value-level diff UI. Learn the mechanics and run PR checks with open-source data-diff; reach for Datafold when you want lineage-aware impact ("this diff feeds the exec dashboard") and the managed UX without building it yourself. Many teams start with data-diff in GitHub Actions and adopt Datafold when the manual workflow maintenance and missing lineage start to hurt.

How do I run a data diff on every pull request?

Wire it into CI as a required status check. On pull_request, build only the models the PR changed (plus downstream) into a throwaway schema with dbt build --select state:modified+ --defer --state ./prod-manifest — the Slim CI pattern — then run data-diff --dbt --select state:modified+ to diff each changed model against prod. Render the added/removed/changed summary into a PR comment or the job step summary, and have a small gate script exit non-zero on unexpected change so branch protection blocks the merge until a human confirms intent. Scope diffs with --where on a partition column and defer unchanged upstreams so the check stays fast even in a large project.

What is the difference between row-level and value-level diffing?

Row-level diffing aligns the two tables on their primary key and classifies each key as added (only in the new version), removed (only in the old), or present in both — it answers "which rows appeared or disappeared." Value-level diffing goes inside the rows present in both and compares each column with a null-safe operator (IS DISTINCT FROM), reporting which columns changed and on how many keys — it answers "inside the matched rows, which values moved and where." A complete diff does both, usually with a row-hash checksum pre-filter that skips identical rows so the expensive per-column comparison only runs on rows that actually differ. Row-level tells you the shape of the change; value-level tells you the substance.

How does diffing scale to billion-row tables?

Two techniques keep exact diffing cheap at scale. First, a row-hash pre-filter: hash each row to one fingerprint, compare fingerprints, and only run the column-by-column comparison on the rows whose hashes disagree — turning O(rows × columns) into an O(rows) hash scan plus O(changed rows) of real work. Second, for cross-database diffs, checksum bisection (the hashdiff algorithm in data-diff): checksum whole ranges of the key space inside each database, compare the small checksums over the network, and recursively descend only into the sub-ranges that disagree — so two near-identical billion-row tables transfer kilobytes, and the cost scales with the number of differences, not the table size. Scope with a --where predicate on a partition column and the per-PR diff stays fast even against the largest tables.

Practice on PipeCode

• Drill the data validation practice library → for the row-level, value-level, and null-safe comparison problems that underpin every data diff.
• Rehearse on the ETL practice library → for the cross-database reconciliation, checksum, and incremental-load patterns behind data-diff and Datafold.
• Sharpen the transformation axis with the data transformation practice library → for the dbt-model dual-run, promotion-gate, and regression-testing scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the diff-scope decision matrix against real graded inputs.

Lock in data-diffing muscle memory

Docs explain diffing. PipeCode drills explain the decision — when a refactor must diff to zero rows, when full-outer-join beats inner, when checksum bisection saves a cross-database diff, when a percentage-only drift threshold hides a real regression, and when a diff should gate the merge. Pipecode.ai is Leetcode for Data Engineering — validation-first practice tuned for the production trade-offs senior data engineers actually face.

Practice data validation problems →
Practice ETL problems →