Why Data Changes Need a Data Diff, Not Just Code Review
A reviewer can approve a tidy five-line SQL change that quietly rewrites a million rows downstream, because the pull request shows the query and never the table. Data diff code review closes that gap: you compare the changed model's row counts, key sets and column values before and after, and post the result on the PR.
The code diff says what the author typed. The data diff says what the table now contains. Reviewers need both.
A clean SQL diff can still change the data
Two hypothetical PRs, though you've met their cousins. One adds a left join to pull in a postcode, and the address table has two rows for some customers, so those orders now appear twice. The other changes a date cast to a local timezone, and late-evening orders slide into the next day, so every daily total shifts a little.
The second keeps the row count, the keys and every uniqueness test intact. Tests check the things you thought of. A data diff shows the things you didn't.
What a data diff adds to code review
A data diff answers three questions about the model the PR touches:
- Did the row count move?
- Did keys appear or vanish?
- Among the rows that stayed, which column values changed, and how many?
If the PR says "refactor, no output change" and revenue moved on some rows, the review just got very short.
Run a data diff in plain SQL
The first query compares the production table with the copy built from the PR branch, written into a schema named after the PR, such as pr_1234. It is written for psql, where :"dev_schema" is a variable.
-- ci/data_diff.sql
select
count(p.order_id) as prod_rows,
count(d.order_id) as dev_rows,
sum(case when d.order_id is null then 1 else 0 end) as keys_removed,
sum(case when p.order_id is null then 1 else 0 end) as keys_added,
sum(case when p.order_id is not null and d.order_id is not null
and p.revenue is distinct from d.revenue
then 1 else 0 end) as revenue_changed,
sum(case when p.order_id is not null and d.order_id is not null
and p.status is distinct from d.status
then 1 else 0 end) as status_changed
from analytics.fct_orders as p
full outer join :"dev_schema".fct_orders as d
on p.order_id = d.order_id;
-- a join on a non-unique key multiplies rows, so check the key itself
select count(*) - count(distinct order_id) as dev_duplicate_keys
from :"dev_schema".fct_orders;
One full outer join on the key yields every count in a single pass. Add one sum(case ...) line per column you care about. is distinct from is null-safe, so null to 0 counts as a change. The second query matters because duplicate keys muddy every number above it.
When the summary shows a change, EXCEPT returns rows from the first query that are missing from the second:
-- in prod, but gone or different in the PR build
select order_id, status, revenue from analytics.fct_orders
except
select order_id, status, revenue from pr_1234.fct_orders
limit 20;
-- in the PR build, but new or different from prod
select order_id, status, revenue from pr_1234.fct_orders
except
select order_id, status, revenue from analytics.fct_orders
limit 20;
A changed row appears in both results, old values in one, new in the other. Both sides need the same columns in the same order; BigQuery spells it EXCEPT DISTINCT. The PostgreSQL docs on combining queries cover the set-operation rules.
Post the data diff on the pull request
A diff nobody sees changes nothing. This GitHub Actions workflow builds the PR schema, runs the summary query and posts the output with gh pr comment --body-file.
name: data-diff
on:
pull_request:
paths:
- 'models/**'
permissions:
contents: read
pull-requests: write
jobs:
diff:
runs-on: ubuntu-latest
env:
DATABASE_URL: ${{ secrets.CI_DATABASE_URL }}
DEV_SCHEMA: pr_${{ github.event.number }}
steps:
- uses: actions/checkout@v4
- name: Build the changed models into the PR schema
run: ./ci/build_pr_schema.sh "$DEV_SCHEMA" # your own pipeline run
- name: Run the data diff
run: psql "$DATABASE_URL" -X -x -v ON_ERROR_STOP=1 -v dev_schema="$DEV_SCHEMA" -f ci/data_diff.sql > diff.txt
- name: Post the diff on the PR
env:
GH_TOKEN: ${{ github.token }}
run: |
{
echo '### Data diff: fct_orders, prod vs this PR'
echo '```'
cat diff.txt
echo '```'
} > comment.md
gh pr comment ${{ github.event.number }} --body-file comment.md
The pull-requests: write permission lets the built-in token comment; see GitHub's workflow syntax reference for the permission keys. On pull requests from forks the token is read-only, so the comment step will fail there.
Cost, noise, and the "we already have tests" objection
Tests already cover this. Only what someone thought to assert. A diff needs no prediction, so it catches the timezone case. Keep both: tests guard every build, the diff explains every change.
It's expensive. Diff only the models the PR changed; the paths filter skips PRs that touch no models. Filter both sides with the same predicate, such as closed partitions from the last week, never random(): each side would sample different rows and manufacture differences.
It's noisy. Drop columns that change on every run (loaded_at, run ids), round floats, and pin both sides to a closed time window, because production keeps moving while your PR builds. Gate the PR only on rules you would defend in review, such as any duplicate key.
It can't judge. The diff shows what changed, not whether it should have; that stays the reviewer's call. A brand-new model has no production table to compare, so check key uniqueness and row counts there.
Add one diff query to your next pipeline PR
Run the first query by hand against a dev copy of your next PR's model and paste the output into the PR description. If it finds nothing, you lost ten minutes. If it finds something, it just paid for the CI step.
Dashboards, retrieval indexes and model features consume tables without reading your SQL, so a data diff catches a silent change before it shows up as an unexplained drop in your AI system's answers.