Review Pipeline PRs with a Data-Engineering AI Checklist

This playbook sets up AI-assisted PR review tuned to data-engineering failure modes, with a checklist the reviewer must walk explicitly, so review comments point at the bugs that actually take pipelines down.

When to reach for this

Use this on every nontrivial pipeline PR — new models, DAG changes, refactors — either as the first-pass reviewer before a human, or as the second pair of eyes on your own PR before requesting review. It matters double when the PR itself contains AI-generated code: roughly half of current DE job postings expect exactly this skill, reviewing and debugging AI-written pipeline code.

Triggers:

• •
  A PR touching transformation logic, incremental configs, or DAG structure.
• •
  Your own AI-assisted change, before a human sees it.
• •
  A team decision to add a consistent first-pass review gate to data repos.

The workflow

1. 1.

   Encode the checklist once, in the repo. Create a review-checklist file the agent reads every time (e.g., .claude/review-checklist.md or your review bot's config). Seed it with the DE core:

   Copy

   Data pipeline review checklist — walk EVERY item, answer per item:
   PASS / FAIL / NOT APPLICABLE, with file:line evidence.
   
   1. Idempotency: can every changed model/task rerun safely after a
      partial failure without duplicating or dropping rows?
   2. Grain: does any join or aggregation change output grain? Is the
      grain still asserted by a uniqueness test?
   3. Incremental correctness: unique_key valid? lookback window
      justified? is_incremental() predicate prunes partitions?
   4. Partition/cluster pruning: do new filters and joins preserve
      pruning, or force full scans?
   5. Timezone and date math: any naive timestamp comparison, implicit
      session-timezone dependence, or date truncation at a boundary?
   6. NULL semantics: joins on nullable keys, NOT IN with nullable
      subqueries, COALESCE that masks data errors?
   7. Schema contracts: does any output column change type, name, or
      nullability that a downstream consumer depends on?
   8. Backfill blast radius: if this needs a backfill, what does it cost
      and who is impacted?
   9. Tests and docs updated to match the change?
   

2. 2.

   Run the review against the diff plus context. In an agentic tool, the prompt is short because the checklist carries the weight:

   Copy

   Review PR #482 using .claude/review-checklist.md. Read the changed
   files AND their upstream/downstream models (follow refs) before
   answering. Output: the checklist verdict table first, then detailed
   findings ranked by severity, each with file:line and a suggested fix.
   Do not comment on formatting. If you cannot verify an item from the
   code alone, say what query or run would verify it.
   

3. 3.

   Triage findings by verifiability. Sort AI findings into: verifiable now (a query or dry run settles it), design questions (human judgment), and noise. The first bucket is where AI review earns its place — run the verifications immediately, inline:

   Copy

   -- finding: "join to dim_customer may fan out fct rows"
   SELECT customer_id, COUNT(*) AS n
   FROM dim_customer
   GROUP BY customer_id
   HAVING COUNT(*) > 1;
   

   A nonzero result confirms the finding; zero rows with a unique constraint refutes it. Paste refuted findings back — the agent's next pass on this repo improves when corrected in-conversation.

4. 4.

   Demand evidence on the two chronic blind spots. Whatever the tool, explicitly ask: "Show me the rerun path after a mid-run failure" (idempotency) and "Which downstream models select the columns this PR changes?" (blast radius). These two produce the worst incidents and get the vaguest generic-review coverage.

5. 5.

   Gate, do not replace. The AI verdict table plus your verification results go into the PR as a comment; a human still approves. The checklist FAILs must be resolved or explicitly waived with a reason — a waived-without-reason FAIL is how the process decays into theater.

6. 6.

   Feed incidents back into the checklist. Every pipeline incident postmortem asks: would a checklist item have caught this? If yes and none exists, add it. The checklist is a living asset; six months of this loop makes it worth more than the tool running it.

Verify before you trust it

• •
  Every "verifiable now" finding has been confirmed or refuted by an actual query or dry run — no finding accepted on the reviewer's say-so.
• •
  Grain assertions: uniqueness query on the stated key of every changed model returns zero duplicates.
• •
  Idempotency: a deliberate rerun of the changed job on the same input window produces identical row counts.
• •
  Pruning claims checked against the engine: EXPLAIN (or the warehouse query profile) shows partition pruning still active on the changed queries.
• •
  Downstream blast radius listed from lineage (dbt ls --select model+), not from the reviewer's memory.

Pitfalls

• Confident nitpicks, missed disasters. AI reviewers reliably flag naming and miss a fan-out join. The checklist's forced walk is the countermeasure — without it you get whatever the model noticed first.
• Hallucinated repo context. Reviewers assert "downstream model X uses this column" without reading it. Require file:line evidence and follow one citation per review to keep the tool honest.
• Rubber-stamp drift. When the AI comment block appears on every PR, humans stop reading it. Severity ranking, a hard cap on minor findings, and the waive-with-reason rule keep signal above noise.
• Reviewing the diff without the DAG. A safe-looking one-line filter change can orphan a downstream join. The "read upstream/downstream before answering" instruction is load-bearing.
• Treating N/A as PASS. "Not applicable — no incremental models changed" on a PR that edits an incremental config means the reviewer misread the diff. Spot-check N/A verdicts; they are where misreads hide.

Tools

• Claude Code (/review or custom command)repo-aware, follows refs across files, and honors a checklist file; strongest fit for the forced-walk pattern.
• GitHub Copilot code reviewconvenient in-platform first pass; pair it with the checklist as a PR template since its default review is generic.
• CodeRabbit / Graphite-class review botsautomated on every PR with configurable instructions; good for enforcing the gate org-wide once the checklist is proven.

Client-safe variant

Client repos need review too, and diffs are client IP. Run the same checklist through an assistant on approved tenancy — Bedrock or Azure OpenAI wired into the client's own GitHub/GitLab via their sanctioned integration — or, where nothing is approved, a local model via Ollama reviewing locally checked-out diffs. The checklist file itself is generic and travels between engagements; the evidence queries run inside the client warehouse. Never paste client diffs into public review tools, and keep the PR-comment evidence (query results) masked to schema-level information rather than row-level data.