Add Data-Diff Summaries to Every Transformation PR

Pairing an automated diff of changed models (production vs PR build) with an LLM summary of the semantic impact — "2.3% of rows change owner_id, all in the EMEA segment, revenue totals unchanged" — turns the scariest question in data PR review ("what does this actually do to the data?") into a two-minute read. This playbook wires that loop into your PR workflow.

When to reach for this

Use this on any PR that changes transformation logic where the intended data impact is nonzero or nontrivial to reason about — refactors that should change nothing, logic fixes that should change specific rows, and everything in between where "should" needs checking.

Triggers:

• •
  A refactor PR claiming zero behavior change.
• •
  A business-logic fix whose blast radius the author cannot precisely state.
• •
  Recurring reviewer anxiety: approvals based on reading SQL and hoping.

The workflow

1. 1.

   Build the PR version alongside production. Using dbt CI (or your equivalent), build changed models and their downstream dependents into a PR schema against the same source data as production — same-input comparison is the entire foundation; a diff across different source snapshots measures drift, not your change.

2. 2.

   Diff structurally first, then row-level. For each changed model, capture: row counts both sides, per-column match rates on key-joined rows, added/removed rows by key, and value-change distributions for modified columns. Tools like Recce or data-diff utilities generate this; the manual core is:

   Copy

   -- rows whose content changed, joined on the model's grain key
   SELECT COUNT(*) AS changed_rows
   FROM prod.fct_orders p
   JOIN pr_1234.fct_orders d USING (order_id)
   WHERE p.total_amount IS DISTINCT FROM d.total_amount
      OR p.owner_id     IS DISTINCT FROM d.owner_id
      OR p.status       IS DISTINCT FROM d.status;
   
   -- keys present on one side only
   SELECT COUNT(*) AS only_in_prod FROM (
     SELECT order_id FROM prod.fct_orders
     EXCEPT
     SELECT order_id FROM pr_1234.fct_orders);
   

3. 3.

   Have the LLM turn diff output into a reviewer-facing impact summary.

   Copy

   Below: the PR description (intended change) and the data-diff output
   for fct_orders (row counts, per-column change rates, changed-row
   samples by segment, added/removed keys).
   
   Write a PR impact summary for a human reviewer:
   1. One-sentence verdict: does the observed diff MATCH the stated
      intent? (match / partial / mismatch)
   2. What changed: percentages and absolute counts per column, and
      which segments/date-ranges concentrate the changes.
   3. Anything the intent does NOT explain: unexpected columns changed,
      unexpected segments touched, rows added/removed when none expected.
   4. Downstream metrics at risk given these columns (list is provided).
   Be quantitative. If the diff shows nothing outside the stated intent,
   say exactly that. Do not soften mismatches.
   
   [paste PR description + diff output]
   

4. 4.

   Verify the summary against the raw diff before posting — inline, every time. Spot-check the two or three headline numbers ("2.3% of rows", "all in EMEA") against the diff output they summarize. The LLM adds interpretation, which is valuable, and occasionally adds arithmetic, which is not. Then post summary plus raw-diff link as a PR comment.

5. 5.

   Review to a simple contract. Refactor PRs: the summary must read "no data changes" and the diff must back it — anything else fails review. Logic-change PRs: the changed population in the diff must match the population named in the PR description; unexplained deltas are questions for the author, not footnotes. This contract is what makes the loop enforceable rather than decorative.

6. 6.

   Automate the boring 90%. Once the pattern proves out manually, move it into CI: diff on every PR touching models/, LLM summary auto-posted, human review required only on the summary — with a hard rule that a mismatch verdict blocks merge until resolved.

Verify before you trust it

• •
  Same-input guarantee confirmed: PR build and prod read identical source snapshots (check source freshness timestamps on both sides).
• •
  Headline summary numbers re-derived from raw diff output for at least the verdict-driving claims.
• •
  Grain stability: key-uniqueness holds on both sides before any joined comparison — a fan-out makes every downstream number in the diff meaningless.
• •
  "Nothing else changed" claims tested in both directions: EXCEPT prod-to-PR and PR-to-prod both zero for models the PR should not touch.
• •
  Non-deterministic columns (timestamps, run IDs) explicitly excluded from the diff, and the exclusion list itself reviewed — an over-broad exclusion hides real changes.

Pitfalls

• Diffing across different source data. The classic false alarm: prod built at 6 a.m., PR built at noon, and the "impact" is six hours of new orders. Pin both builds to the same snapshot or filter both to a closed date window.
• Summary arithmetic drift. The LLM says "0.3%" where the diff says 3%. Rare, but reviewers repeat what the summary says — which is why the headline numbers get re-checked by hand.
• Green-diff overconfidence on sampled comparisons. If the diff tool samples, a clean sample is weaker evidence than a clean full comparison. Know which one you ran; say so in the PR comment.
• Ignoring removed rows. Attention goes to changed values; a filter change that silently drops 40 rows reads as "counts roughly stable" unless added/removed keys are first-class in the diff and the summary prompt.
• Summary-only review culture. Six months in, nobody opens the raw diff. The mismatch-blocks-merge rule and occasional spot audits keep the evidence layer honest.

Tools

• RecceOSS, dbt-native PR data-diff and lineage checks, built for exactly this review workflow; the free-path default.
• data-diff-class utilities (Datafold lineage/diff tooling)cross-database row-level diffing at scale for pairs beyond a single warehouse.
• Claude / frontier LLMthe summary layer over any diff output; in CI, call it with the diff artifact and post the result as the PR comment.

Client-safe variant

Diff outputs contain aggregate statistics plus sampled changed rows — the samples are the sensitive part. On client engagements, configure the diff to emit counts, percentages, and segment aggregates but mask or drop row-level samples before anything reaches an LLM; the summary prompt works nearly as well on aggregates alone. Route summarization through client-tenancy Bedrock/Azure OpenAI, or run a local Ollama model inside the client environment for the CI step. The raw diff artifact stays in the client's warehouse and CI system, and PR comments reference it by link rather than embedding row data.