What a diff of a table looks like

Every code change I ship comes with a diff. Someone reads the red and green lines and decides. The data that code produces gets no such treatment: the model runs, the tests pass, the number on the dashboard moves, and nobody can say which rows moved it. That gap is where most of the bad numbers I have shipped came from.

What the tests do not catch

dbt tests are good at the questions they ask. Is the key unique. Is the column non-null. Are the values in the accepted set. Does the foreign key resolve. I run them on everything and I would not ship without them.

They do not ask the question a reviewer actually has, which is: what changed. A model can pass every test before and after a change while quietly dropping a customer, doubling a row, or shifting revenue between months. The tests are assertions about one version of the table. A diff is a statement about two.

The table above is small enough to eyeball, and even so the first scene should have looked like nothing happened. Fourteen rows against fifteen. Real models are millions of rows. Nobody is eyeballing anything.

How to diff a model in practice

The cheapest version needs no tooling. Build the model twice, once from the branch you are merging into and once from your branch, into two schemas. Then ask the warehouse.

EXCEPT gives you the rows that differ in either direction. It is fast to write and it is the first thing I run.

(SELECT 'removed' AS status, * FROM analytics_main.customers
 EXCEPT
 SELECT 'removed', * FROM analytics_branch.customers)
UNION ALL
(SELECT 'added' AS status, * FROM analytics_branch.customers
 EXCEPT
 SELECT 'added', * FROM analytics_main.customers);

EXCEPT has the same weakness as the naive diff in scene three: a changed cell shows up as one row removed and one row added, and it cannot tell you which cell. For that you need the key.

WITH a AS (SELECT * FROM analytics_main.customers),
     b AS (SELECT * FROM analytics_branch.customers)
SELECT
  COALESCE(a.id, b.id) AS id,
  CASE
    WHEN a.id IS NULL THEN 'added'
    WHEN b.id IS NULL THEN 'removed'
    ELSE 'changed'
  END AS status,
  a.plan AS plan_before, b.plan AS plan_after,
  a.mrr  AS mrr_before,  b.mrr  AS mrr_after
FROM a
FULL OUTER JOIN b ON a.id = b.id
WHERE a.id IS NULL
   OR b.id IS NULL
   OR a.plan IS DISTINCT FROM b.plan
   OR a.mrr  IS DISTINCT FROM b.mrr;

That query is the whole algorithm in the component. Full outer join on the key, null on one side means added or removed, IS DISTINCT FROM per column means changed. Wrap it in a macro that reads the column list from the information schema and you have a diff for any model in the project. Aggregate mrr_after - mrr_before over the result and you have scene four: the metric delta with the rows responsible.

The key matters more than the tool

Scene five is the part I would put on a poster. Take the key away and the same three changes become 48, and the removed row vanishes from the report because positional comparison cannot see removal, only shifted values. Every diff tool on the market, paid or open source, degrades to that when the model has no stable primary key. The tool is not the hard part. The key is.

That has a consequence for how you write models. A mart with no natural key, or with a surrogate key built from ROW_NUMBER(), is not diffable. The key changes whenever the row order changes, and the row order changes whenever the warehouse feels like it. If you want to be able to review what a change did, every model needs a key that means something, and a test that says it is unique.

The rename in scene three is the other half of the same lesson. A diff compares columns by name, so a rename looks like a delete and an insert. Tools handle this with a column map, and so does the component. Do not rely on that. Rename columns in their own pull request, with a diff that shows zero cell changes, and change values in another.

What to do this week

• Pick your most-read model and confirm it has a key that is both unique and stable across runs. If it does not, that is the first fix.
• Add a two-schema build to your CI: main and branch, same seeds, same vars.
• Write the full outer join once as a macro. Make it print added, removed, changed, and the delta for every numeric column.
• Put the output in the pull request. A zero-line diff is a valid and useful result.
• Never rename a column in the same change that alters values. Two diffs are easier to read than one that lies.

Once the diff is in the review, the conversation changes. Nobody asks whether the tests passed. They ask why row 9 went from 79 to 149, which is the right question.