# Data Engineering Recipes

URL: https://developertoolkit.ai/en/cookbook/data-recipes/
Description: Copy-paste recipes for dbt models, SQL pipelines, notebooks and data tests with Claude Code, Codex or Cursor, each proven by a dbt build on a dev target.

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](#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](#refactor-a-dbt-model-without-changing-the-numbers) | `audit_helper` comparison against production: zero changed rows |
| Clean up or productionize a Jupyter notebook | [Keep notebooks reviewable](#keep-notebooks-reviewable-and-runnable) | Paired `.py` diff plus `pytest --nbval-lax` on a clean kernel |
| Tune a slow warehouse query or plan a schema change | [SQL optimization](/en/cookbook/database-recipes/sql-patterns/) and [migration patterns](/en/cookbook/database-recipes/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](/en/cookbook/backend-recipes/python-patterns/); for everything else, the [AI Developer Cookbook](/en/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:

```markdown
## 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](https://cursor.com/docs/rules) 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**

```bash
# 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**

```bash
# 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:

```json
{
  "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](/en/ecosystem/mcp/database-mcp/).

## 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](/en/method/principles/test-driven-development/) applied to SQL.

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

```text
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**

```text
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**

```text
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:

```bash
#!/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:

```bash
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](/en/workflows/ship/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**

```text
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**

```bash
# 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**

```bash
# 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](/en/workflows/test/protect-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](/en/workflows/harness/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](/en/workflows/test/test-data-management/).

## Where to go next with data engineering

- [Database recipes](/en/cookbook/database-recipes/) — Migrations, ORM code, and SQL tuning for the transactional side.
- [Protecting the oracle](/en/workflows/test/protect-the-oracle/) — Keep the data tests out of the agent's reach, so a green build still means something.
- [Database design and queries](/en/workflows/build/database-development/) — The end-to-end workflow around schema changes.
- [Installing, sharing and pinning skills](/en/ecosystem/skills/installing-managing/) — How skills load and update in all three tools.
- [Evidence bundle](/en/workflows/ship/evidence-bundle/) — What a pull request carries so a reviewer checks results, not lines.
