Data Engineering Recipes

The data engineering recipes are copy-paste prompts for Claude Code, Codex, and Cursor covering dbt models, SQL pipelines, notebooks, and data tests. Every recipe follows the same contract: the agent reads the project through the dbt MCP server, builds only on a dev target, and returns dbt build output plus a row-level diff instead of SQL for a human to read.

It is for developers and analytics engineers who let an agent change a pipeline, and it guards against one failure: the model compiles, then the revenue dashboard moves because a join fanned out.

Which data engineering recipe fits your task?

Your task	Recipe	The gate that proves it
Add or change a dbt model	Build a dbt model test-first	Unit tests written before the SQL; dbt build on the model and everything downstream
Refactor a model without changing its numbers	Refactor with a row-level diff	audit_helper comparison against production: zero changed rows
Clean up or productionize a Jupyter notebook	Keep notebooks reviewable	Paired .py diff plus pytest --nbval-lax on a clean kernel
Tune a slow warehouse query or plan a schema change	SQL optimization and migration patterns	Query plan and timing before and after; up, down, up on a copy

For ingestion jobs and APIs in Python, use the Python workflow recipes; for everything else, the AI Developer Cookbook.

Give the agent your data rules before any recipe

Without rules, an agent runs dbt run against the default target and “fixes” a failing test by deleting it. Write the rules once, where each tool loads them:

## Data rules
- Never run dbt against the prod target. Use --target dev; the default target in profiles.yml is dev.
- Select only what changed: --select state:modified+ --defer --state prod-artifacts
- Never delete, disable, or loosen a data test (severity, where:, error_if) to make a build pass.
  Report the failure and stop.
- Never SELECT raw columns tagged pii; profile them with counts and null rates only.
- After any change, run scripts/verify-dbt.sh and paste the full output in your summary.

Claude Code

Add the block to CLAUDE.md at the repository root. Claude Code loads it in every session, including headless claude -p runs.

Codex

Add the block to AGENTS.md at the repository root. An untrusted project does not supply its project-level AGENTS.md (Codex 0.150.0 and later), so mark the repository as trusted: add [projects."/abs/path/to/repo"] with trust_level = "trusted" to ~/.codex/config.toml (key checked in the Codex 0.157.1 source).

Cursor

Save the same text as a project rule. Cursor’s Rules documentation shows where project rules live and how to make one apply to every request.

prod-artifacts/ holds the last production manifest.json, so only your changes build. Before each run, download that manifest.json from your last production run’s CI artifacts into prod-artifacts/; state:modified and --defer fail without it.

Connect the dbt MCP server and the dbt agent skills

The dbt MCP server from dbt Labs (PyPI dbt-mcp, requires Python 3.12 or 3.13) gives the agent lineage, node details, list, compile, and show. The dbt agent skills (dbt-labs/dbt-agent-skills) teach dbt conventions such as unit tests. The commands below disable the server’s run, build, test, clone, and docs tools, so every build goes through the gate script:

Claude Code

# Terminal, from the dbt project root. Requires uv and dbt on PATH (or set DBT_PATH).
claude mcp add dbt -e DBT_PROJECT_DIR="$PWD" -e DISABLE_TOOLS=run,build,test,clone,docs -- uvx dbt-mcp

# In the Claude Code prompt: install the skills as a plugin
/plugin marketplace add dbt-labs/dbt-agent-skills
/plugin install dbt@dbt-agent-marketplace

Codex

# Terminal, from the dbt project root. Requires uv and dbt on PATH (or set DBT_PATH).
codex mcp add dbt --env DBT_PROJECT_DIR="$PWD" --env DISABLE_TOOLS=run,build,test,clone,docs -- uvx dbt-mcp
npx skills add dbt-labs/dbt-agent-skills --skill using-dbt-for-analytics-engineering --skill adding-dbt-unit-test -a codex

Cursor

Add the server to .cursor/mcp.json, with the absolute path of your dbt project:

{
  "mcpServers": {
    "dbt": {
      "command": "uvx",
      "args": ["dbt-mcp"],
      "env": {
        "DBT_PROJECT_DIR": "/Users/you/code/analytics",
        "DISABLE_TOOLS": "run,build,test,clone,docs"
      }
    }
  }
}

Then install the skills: npx skills add dbt-labs/dbt-agent-skills --skill using-dbt-for-analytics-engineering --skill adding-dbt-unit-test -a cursor.

The show tool still runs SQL through profiles.yml, so the default target must be dev. For warehouse access outside dbt, see database MCP servers.

Build a dbt model test-first

Write the expected rows before the SQL. dbt unit tests (unit_tests:, dbt Core 1.8 and later) assert the output for fixed input rows, so guessing cannot pass. This is the test-driven loop applied to SQL.

Copy-paste prompt: a new dbt model, test-first

I need a model fct_daily_revenue: one row per order_date and currency, summing
net revenue from stg_orders (exclude status = 'cancelled') and refunds from
stg_refunds, converted to EUR with stg_fx_rates on the order date.

1. Use the dbt MCP server to read the lineage and columns of every input.
   Print the grain and primary key of each input as a table.
2. Before writing SQL, add a unit test in models/marts/_fct_daily_revenue.yml
   with fixed input rows covering: a cancelled order, a partial refund, a
   missing FX rate, and two currencies on one day. Show me the expected rows.
3. Write the model. Add a dbt_utils.unique_combination_of_columns test on
   (order_date, currency) and not_null tests on both columns. dbt_utils must
   already be in packages.yml; if it is not, stop and ask me.
4. Run scripts/verify-dbt.sh and paste the full output.
Stop and ask me if an input's grain makes the join fan out.

Refactor a dbt model without changing the numbers

Data proves a refactor, not the diff. The audit_helper package (dbt Hub dbt-labs/audit_helper) compares the dev build with the production relation row by row. The prompt aggregates the result, because dbt show prints five rows by default and the macro returns at most 20 sample rows per status.

Copy-paste prompt: refactor with a row-level proof

Refactor models/marts/fct_orders.sql: replace the three nested subqueries with
CTEs and move the currency logic into int_orders_converted. The output must not
change.

1. Add dbt-labs/audit_helper to packages.yml if it is missing, then run dbt deps.
2. Build the refactored model with --target dev.
3. Write analyses/compare_fct_orders.sql using
   audit_helper.compare_and_classify_relation_rows to compare the dev relation
   with the production relation on primary key order_id. Wrap it as
   select dbt_audit_row_status, max(dbt_audit_num_rows_in_status)
   from (...) group by 1
   and run dbt show --select compare_fct_orders --limit -1.
4. Report the row count of each relation and the row count per status.
Done means the only status is identical and both row counts are equal. If
not, show five examples and stop; do not edit tests or the comparison.

Keep notebooks reviewable and runnable

Agents edit .ipynb JSON poorly. Pair each notebook with a script via Jupytext (jupytext --set-formats ipynb,py:percent notebook.ipynb), let the agent edit the .py file, and prove the result with nbval on a clean kernel.

Copy-paste prompt: clean up a notebook before it becomes a pipeline

Work on notebooks/churn_analysis.py (the Jupytext pair of the .ipynb), never
the .ipynb JSON. Make it run top to bottom on a fresh kernel: remove cells
that depend on execution order, replace hard-coded file paths with a
DATA_DIR variable, and move the feature-engineering cells into
src/features/churn.py with pytest tests. Then run
jupytext --sync notebooks/churn_analysis.py and
pytest --nbval-lax notebooks/churn_analysis.ipynb, and paste both outputs.
List any cell whose output changed and why.

How do you verify agent-written data changes without reading every line?

Put the gates in one script that every tool, human, and CI runs. It refuses prod and builds with zero rows before the real build:

#!/usr/bin/env bash
# scripts/verify-dbt.sh: dev target only. Delete the lines your project does not use.
set -euo pipefail
TARGET="${DBT_TARGET:-dev}"
if [ "$TARGET" = "prod" ]; then echo "refusing to run against prod"; exit 1; fi
SEL=(--select state:modified+ --defer --state prod-artifacts --target "$TARGET")
[ -d dbt_packages ] || dbt deps   # needs network; skipped once packages are installed
dbt parse --warn-error --target "$TARGET"
dbt build "${SEL[@]}" --empty
dbt build "${SEL[@]}"
sqlfluff lint models/
pytest --nbval-lax notebooks/
echo "PASS all data gates"

Make the script executable once, so the allow rules below can call it without a bash prefix:

chmod +x scripts/verify-dbt.sh

A human signs off on two things only: the row-level comparison for any change to a model that feeds a dashboard or an export, and any --full-refresh of an incremental model. Both go in the pull request’s evidence bundle. To run the gates unattended, save this fix-until-green prompt as prompts/dbt-gates.txt:

Copy-paste prompt: prompts/dbt-gates.txt

Run scripts/verify-dbt.sh exactly as written, with no ./ or bash prefix.
If it fails, fix only the model SQL. Never edit tests, test YAML,
packages.yml, profiles.yml, or scripts/verify-dbt.sh.
Rerun until it prints PASS all data gates, or stop after 3 failed attempts.
Then paste the final output of the script and list every file you changed.

Claude Code

# Terminal, on a trusted branch. Edits allowed; shell limited to the gate
# script; dbt MCP tools allowed (run/build/test/clone/docs are disabled on the server).
claude -p "$(cat prompts/dbt-gates.txt)" \
  --allowedTools "Read,Grep,Glob,Edit,Bash(scripts/verify-dbt.sh),Bash(./scripts/verify-dbt.sh),mcp__dbt" \
  --output-format json > dbt-gates.json

Codex

# Terminal. dbt deps runs outside the sandbox; -o saves the final summary.
dbt deps

# Local DuckDB target: the permission profile (beta) keeps writes in the workspace.
codex exec -c default_permissions=":workspace" -o dbt-gates.md "$(cat prompts/dbt-gates.txt)"

# Cloud warehouse, only where the agent's credentials reach dev schemas alone.
codex exec --sandbox workspace-write -c sandbox_workspace_write.network_access=true \
  -o dbt-gates.md "$(cat prompts/dbt-gates.txt)"

The sandbox blocks outbound network by default (workspace-write ships with network_access = false, Codex 0.157.1 source), so install packages first; the script then skips dbt deps. The cloud form adds the network override because dbt must reach the warehouse. The permission profile and the legacy --sandbox do not compose, so use one or the other in a single call, never both.

Cursor

Paste the prompt into the agent and accept the diff only after PASS.

What breaks when agents write data pipelines?

Data recipe failure modes and recovery

• A join that fans out and doubles revenue. Recovery: a unique test on the model’s grain.
• A green build because a test was weakened (severity: warn, a where: filter, a deleted test). Recovery: revert, and require code-owner approval on test YAML; see protecting the oracle.
• The agent built into production. Recovery: default target dev, the gate script, and no production write grant for the agent’s credentials; see permissions and sandboxing.
• An incremental model that drifts from its full rebuild (the is_incremental() branch was not updated). Recovery: --full-refresh in dev, compared with the refactor recipe.
• Tests that pass on toy data. Recovery: realistic, masked fixtures; see test data management.

Where to go next with data engineering

Database recipes Migrations, ORM code, and SQL tuning for the transactional side.

Protecting the oracle Keep the data tests out of the agent's reach, so a green build still means something.

Database design and queries The end-to-end workflow around schema changes.

Installing, sharing and pinning skills How skills load and update in all three tools.

Evidence bundle What a pull request carries so a reviewer checks results, not lines.