Data pipeline code review checker · free AI skill

Reviews SQL, dbt models, or Python pipeline code across 9 dimensions — correctness, idempotency, testability, performance, data quality, error handling, naming/structure, dependency safety, and documentation — and…

Purpose

Review data pipeline code — SQL, dbt models, or Python ETL/ELT — for correctness, reliability, testability, and maintainability, returning severity-ranked findings with concrete fixes so a reviewer can approve, request changes, or refactor with confidence. Catches the failure modes that only surface in production: silent data loss, non-idempotent reruns, fan-out joins, and untested assumptions.

When to Use

Invoke this skill when the user asks to:

Review a pull request or diff containing SQL, dbt, or Python pipeline code
Check a dbt model before merging it into a project
Audit a SQL transformation for correctness and performance
Review Python ETL/ELT scripts for reliability and error handling
Get a second opinion on whether pipeline code is production-ready
Identify why a pipeline is flaky, slow, or producing wrong numbers

Inputs Required

Before starting, collect:

Code type: SQL / dbt model / Python (ETL, ELT, or orchestration)
The code: The full file(s) or diff to review
Warehouse/engine: Snowflake / BigQuery / Redshift / Postgres / Spark / DuckDB (affects performance and syntax guidance)
Materialization (dbt): table / view / incremental / ephemeral — or "unknown"
Run frequency: How often it runs (hourly, daily, on-demand) and whether it's backfilled
Upstream sources: Where the data comes from and known volume/cardinality
Existing tests: What tests or assertions already exist, if any

If warehouse, materialization, or frequency are missing, assume a daily-scheduled Snowflake incremental model and flag the assumption in the output.

Instructions

Review the code across all 9 dimensions below. For each finding, assign a severity, cite the specific line or block, explain the failure it causes, and give a concrete fix (rewritten snippet where practical).

Severity scale:

Blocker — will produce wrong data, lose rows, or fail in production
Major — reliability, performance, or maintainability risk that should be fixed before merge
Minor — style, naming, or improvement that can follow up

Dimension 1 — Correctness & Logic

Join fan-out: Any join on a non-unique key that silently multiplies rows — check that join keys are unique on at least one side, or that the many-to-many is intentional
Null-handling in filters/joins: WHERE col != x silently drops nulls; = joins skip null keys — flag both
Aggregation grain: Does the GROUP BY grain match what downstream consumers expect? Flag mixed grain (aggregate + non-aggregate columns without grouping)
Window frame defaults: Implicit RANGE vs ROWS frames producing unexpected results with ties
Type coercion: Implicit casts (string → date, int → float) that can silently corrupt or error

Dimension 2 — Idempotency & Reruns

Can this run twice and produce the same result? Flag INSERT without dedup, CREATE TABLE without OR REPLACE, appends without a delete-first strategy
Incremental logic (dbt): Does the is_incremental() block use a watermark that handles late-arriving data? Flag > when >= plus dedup is safer
Non-deterministic sources: CURRENT_TIMESTAMP, RANDOM(), or unordered LIMIT that make reruns non-reproducible
Backfill safety: Will a full-refresh or backfill double-count or clash with the incremental path?

Dimension 3 — Testability

Are there tests for the assumptions this code depends on? Flag missing unique + not_null tests on primary keys (dbt) or missing assertions (Python)
Referential integrity: Foreign keys without relationships tests
Accepted values / ranges: Enum columns and numeric bounds without tests
Row-count / freshness expectations: No guard against silent zero-row output or stale source data
For Python: is business logic in pure, unit-testable functions or tangled with I/O?

Dimension 4 — Performance

Full scans on partitioned/clustered tables: Filters that don't hit the partition/cluster key on BigQuery/Snowflake/Redshift
SELECT * in models feeding downstream tables — bloats storage and breaks on schema drift
Subquery vs CTE vs window: Repeated subqueries that should be CTEs; correlated subqueries that should be joins or windows
Data-type-driven cost (BigQuery bytes scanned, Redshift dist/sort keys, Snowflake pruning) — flag anti-patterns for the stated engine
Python: row-by-row loops over DataFrames, unbatched API/DB calls, loading full tables into memory when streaming/chunking is possible

Dimension 5 — Data Quality Guards

Deduplication strategy present and correct where the source can contain duplicates
Explicit handling of nulls, empty strings, and sentinel values ("N/A", 0, 1900-01-01)
Currency/unit/timezone consistency — flag naive datetimes and mixed timezones
Schema-drift resilience: does the code assume columns that may disappear or rename?

Dimension 6 — Error Handling & Observability (Python-heavy, SQL where relevant)

Bare except: or swallowed exceptions that hide failures
No retry/backoff on transient network or warehouse errors; no idempotent retry
Silent partial failures — a batch fails mid-loop and the job still reports success
Missing logging of row counts, run duration, and key decision points
No alerting hook or dead-letter path for bad records

Dimension 7 — Naming, Structure & Readability

CTE and column names that describe intent, not mechanics (active_customers, not t2)
Model/file naming follows layer conventions (staging → intermediate → marts; stg_, int_, fct_, dim_)
Single-responsibility: one model/function does one thing; flag 300-line monolith CTEs
Consistent casing, aliasing, and formatting

Dimension 8 — Dependency & Lineage Safety

dbt: ref() / source() used instead of hardcoded table names (breaks lineage and environments)
Hardcoded database/schema/project names instead of env-aware config
Circular or hidden dependencies; undeclared upstream reads
Secrets, connection strings, or credentials in code (Blocker on sight)

Dimension 9 — Documentation & Contracts

Model/column descriptions present for anything consumed downstream (dbt .yml)
Non-obvious business logic explained inline (why, not what)
Assumptions about grain, freshness, and source guarantees documented
Contract/schema declared where downstream systems depend on stability

Final Step — Synthesize

Count findings by severity
Produce a prioritized fix list — Blockers first, then Majors by effort-to-impact, then Minors
Give a one-line verdict: Approve / Approve with comments / Request changes / Needs refactor — and state the single most important thing to fix first

Output Format

Structure: Verdict banner → summary table of findings by severity → dimension-by-dimension findings (only dimensions with findings) → prioritized fix list
Length: 700–1,300 words depending on code size
Format: Structured document — a severity summary table, then findings as grouped bullets with line references and code snippets, then a numbered fix list
Sections: Verdict → Findings Summary (table: Severity | Count) → Findings by Dimension → Prioritized Fix List → Most-Important-First recommendation
Tone: Senior data engineer in code review — direct, specific, evidence-based; every finding cites a line and gives a fix, never vague praise or vague criticism

Quality Checklist

Before delivering output, verify:

[ ] Every finding cites a specific line, block, or CTE — no "somewhere in the file"
[ ] Every Blocker and Major includes a concrete fix or rewritten snippet
[ ] Findings are labeled with severity from the defined scale (Blocker / Major / Minor)
[ ] The findings summary table's counts match the number of findings listed
[ ] Join fan-out and idempotency are explicitly checked, even if the verdict is "no issue found"
[ ] A single verdict is stated and the top-priority fix is named — no hedging

Notes

The two findings that most often reach production undetected are join fan-out (silent row multiplication) and non-idempotent reruns (double-counting on backfill) — always report on both explicitly, even to confirm they're clean.
Tailor performance guidance to the stated engine: partition pruning matters most on BigQuery, dist/sort keys on Redshift, clustering and micro-partition pruning on Snowflake.
For Python, favor separating pure transformation logic from I/O — this single change resolves most testability and error-handling findings at once.
If the code is a diff rather than a full file, review the changed lines but flag when a change depends on unseen upstream context that should be verified.

How to use this skill

This file is a Claude Code skill in Anthropic's standard format — markdown with YAML frontmatter at the top.

Installing in Claude Code:

~/.claude/skills/<folder-name>/SKILL.md — auto-loads in every session anywhere on your machine (recommended for skills you'll use across multiple projects)
<your-project>/.claude/skills/<folder-name>/SKILL.md — auto-loads only when working in that project (recommended for project-specific skills)

Restart Claude Code or open a new session to register.

Using in Claude.ai, ChatGPT, Gemini, or other AI tools:

Drag this file into a new chat, or copy and paste its contents
Send your task or request as your next message — the AI will apply the skill where it fits

For Claude Code skill details: https://docs.anthropic.com/en/docs/claude-code/skills