In this post we are going to cover three power tools in Claude Code for a data pipeline: hooks, sub-agents and parallel agents. We add a hook that checks every SQL file Claude edits, build a read-only SQL reviewer sub-agent, run two Claude sessions at the same time in git worktrees, and the last step is testing and merging their work back into main.
A hook is a command that Claude Code runs at a fixed moment, every time. It enforces a rule instead of asking nicely. A sub-agent is a helper with its own instructions, its own tools and its own context window: it does one job and reports back. Parallel agents are two or more Claude sessions working at the same time, each in its own copy of the code. For a data engineer that means rules on every SQL change, reviews that do not fill your main conversation, and independent tasks done side by side. Every check in this post really runs.
Part 8 of the Claude for Data Engineers series on TechBrothersIT.
Let's create some sample data
Our project is shop-pipeline, a small online shop in DuckDB. It has three CSV files (customers, orders, payments), a build.py that loads them into shop.duckdb and builds every model in models/ as a view, two SQL models, and a data check script in checks/. A few problems are planted on purpose: order 1012 uses customer 107, who does not exist, payment 9 points to order 1099, which does not exist, and customer_orders.sql uses select *. The setup commands build the database and make the first git commit.
customer_id,name,country,signup_date
101,Ava Brooks,US,2026-08-02
102,Liam Chen,CA,2026-08-05
103,Noah Patel,US,2026-08-11
104,Mia Garcia,MX,2026-08-19
105,Zoe Martin,UK,2026-09-01
106,Omar Khan,US,2026-09-03order_id,customer_id,order_date,status,amount
1001,101,2026-10-01,completed,120.00
1002,102,2026-10-01,completed,75.50
1003,103,2026-10-01,cancelled,40.00
1004,101,2026-10-02,completed,210.00
1005,104,2026-10-02,completed,35.25
1006,105,2026-10-02,completed,99.00
1007,106,2026-10-03,completed,150.00
1008,102,2026-10-03,completed,64.75
1009,103,2026-10-03,completed,88.00
1010,104,2026-10-04,completed,45.00
1011,105,2026-10-04,completed,130.00
1012,107,2026-10-04,completed,72.00payment_id,order_id,paid_at,amount,refund_amount
1,1001,2026-10-01,120.00,0.00
2,1002,2026-10-01,75.50,0.00
3,1004,2026-10-02,210.00,30.00
4,1005,2026-10-02,35.25,0.00
5,1006,2026-10-02,99.00,0.00
6,1007,2026-10-03,150.00,0.00
7,1008,2026-10-03,64.75,10.00
8,1009,2026-10-03,88.00,0.00
9,1099,2026-10-04,45.00,0.00
10,1011,2026-10-04,130.00,0.00
11,1012,2026-10-04,72.00,0.00import pathlib
import duckdb
con = duckdb.connect("shop.duckdb")
for table in ("customers", "orders", "payments"):
con.execute(f"CREATE OR REPLACE TABLE {table} AS SELECT * FROM read_csv('data/{table}.csv', header = true)")
for f in sorted(pathlib.Path("models").glob("*.sql")):
con.execute(f"CREATE OR REPLACE VIEW {f.stem} AS " + f.read_text())
rows = con.execute(f"SELECT count(*) FROM {f.stem}").fetchone()[0]
print(f"built {f.stem:<16} {rows:>3} rows")select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue
from orders o
where o.status = 'completed'
group by o.order_date
order by o.order_dateselect *
from orders o
join customers c using (customer_id)"""Data checks for the shop pipeline. Exit code 1 when any check fails."""
import pathlib
import re
import sys
import duckdb
con = duckdb.connect("shop.duckdb", read_only=True)
CHECKS = {
"orders: duplicate order_id": "select order_id from orders group by order_id having count(*) > 1",
"orders: unknown customer": "select 'order ' || order_id from orders where customer_id not in (select customer_id from customers)",
"payments: unknown order": "select 'payment ' || payment_id from payments where order_id not in (select order_id from orders)",
"payments: amount <= 0": "select 'payment ' || payment_id from payments where amount <= 0",
}
results = {name: [str(r[0]) for r in con.execute(sql).fetchall()] for name, sql in CHECKS.items()}
results["models: select *"] = [f.name for f in sorted(pathlib.Path("models").glob("*.sql"))
if re.search(r"select\s+\*", f.read_text(), re.IGNORECASE)]
print(f"{'check':<28} {'bad':>3} result")
for name, bad in results.items():
print(f"{name:<28} {len(bad):>3} " + ("FAIL " + ", ".join(bad) if bad else "pass"))
failed = sum(1 for bad in results.values() if bad)
print(f"{failed} of {len(results)} checks failed")
sys.exit(1 if failed else 0)shop.duckdb
__pycache__/
.pytest_cache/
.claude/worktrees/python build.py
git init -q -b main && git add -A && git commit -qm "Initial shop pipeline"Three tools, three jobs
Before we start, here is what each tool does and how they differ.
Enforce, delegate, parallelize
- Hook: a command Claude Code runs at a fixed moment, every time
- Sub-agent: own prompt, own tools, own context, returns a summary
- Parallel agents: several sessions, one git worktree each
We use all three on the same project, in this order: first a hook, then a sub-agent, then two parallel sessions.
Step 1: A hook that checks every SQL edit
Let's start with the hook. We ask Claude for a PostToolUse hook: after every Edit or Write of a SQL file it should check the file, and block bad SQL with exit code 2.
Add a PostToolUse hook: after any Edit or Write of a .sql file, check it (no SELECT *, must compile in shop.duckdb) and block with exit 2Claude Code starts in auto mode, so it writes the files without asking. First the hook script in .claude/hooks/, then the registration in .claude/settings.json.
#!/usr/bin/env python3
"""PostToolUse hook: check every SQL file Claude edits or writes.
Exit 0 = fine. Exit 2 = blocked: Claude sees the message on stderr and fixes the file."""
import json
import os
import re
import sys
import duckdb
event = json.load(sys.stdin) # Claude Code sends the event as JSON on stdin
path = event.get("tool_input", {}).get("file_path", "")
if not path.endswith(".sql"):
sys.exit(0) # only SQL files are checked
project = os.environ.get("CLAUDE_PROJECT_DIR", event.get("cwd", os.getcwd()))
full = os.path.join(project, path)
sql = open(full).read()
problems = []
if re.search(r"select\s+\*", sql, re.IGNORECASE):
problems.append("SELECT * is not allowed in models: list the columns")
try:
con = duckdb.connect(os.path.join(project, "shop.duckdb"), read_only=True)
con.execute("EXPLAIN " + sql) # compiles the query: every table and column must exist
except duckdb.Error as e:
problems += [l for l in str(e).split("\n") if l and not l.startswith(("LINE", " "))]
name = os.path.relpath(full, project)
if problems:
print(f"check_sql BLOCKED {name}", file=sys.stderr)
for p in problems:
print(f" - {p}", file=sys.stderr)
sys.exit(2)
print(f"check_sql OK {name}"){
"hooks": {
"PostToolUse": [
{
"matcher": "Edit|Write",
"hooks": [
{
"type": "command",
"command": "$CLAUDE_PROJECT_DIR/.claude/hooks/check_sql.py",
"timeout": 30
}
]
}
]
}
}The script reads the event as JSON from standard input, takes the file path, and skips anything that is not a .sql file. Then it checks two things: no SELECT * in a model, and the query must compile against our DuckDB database with EXPLAIN, so every table and column must exist. In the settings file the hook sits under PostToolUse with the matcher Edit|Write.
Now Claude tests the hook by hand. It pipes in the same kind of JSON that Claude Code sends, once for each model.
chmod +x .claude/hooks/check_sql.py && echo '{"tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}' | .claude/hooks/check_sql.py; echo "exit $?"check_sql OK models/daily_revenue.sql
exit 0echo '{"tool_name":"Edit","tool_input":{"file_path":"models/customer_orders.sql"}}' | .claude/hooks/check_sql.py; echo "exit $?"check_sql BLOCKED models/customer_orders.sql
- SELECT * is not allowed in models: list the columns
exit 2daily_revenue.sql passes with exit 0. customer_orders.sql is blocked with exit 2, because it uses SELECT *. The hook already found a real problem.
Commit .claude/settings.json. Then everyone on the team, and every session, gets the same hook.
Step 2: The hook catches a real mistake
Now let's see the hook catch a mistake during real work. We ask Claude to add refunds to the daily revenue model, and we give it the column name from memory: refund_amt.
Add refunds to models/daily_revenue.sql: left join payments and sum payments.refund_amtClaude edits the model exactly as we asked. It joins payments and sums that column. Right after the edit, Claude Code runs the hook. Here is the edit and what the hook script printed for it.
select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue,
sum(p.refund_amt) as refunds
from orders o
left join payments p on p.order_id = o.order_id
where o.status = 'completed'
group by o.order_date
order by o.order_dateexport EVENT='{"hook_event_name":"PostToolUse","tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}'
echo "$EVENT" | .claude/hooks/check_sql.py; echo "exit code: $?"check_sql BLOCKED models/daily_revenue.sql
- Binder Error: Table "p" does not have a column named "refund_amt"
- Candidate bindings: : "refund_amount"
exit code: 2DuckDB says Table "p" does not have a column named "refund_amt" and suggests refund_amount. A PostToolUse hook cannot undo the edit, because the edit already happened. But exit code 2 sends this message back to Claude, so Claude fixes the file straight away.
The hook blocked it: payments has no refund_amt column. DuckDB suggests refund_amount, so I'll use that.
The hook runs again after the fix, and this time the model passes with exit code 0.
select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue,
coalesce(sum(p.refund_amount), 0) as refunds
from orders o
left join payments p on p.order_id = o.order_id
where o.status = 'completed'
group by o.order_date
order by o.order_dateexport EVENT='{"hook_event_name":"PostToolUse","tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}'
echo "$EVENT" | .claude/hooks/check_sql.py; echo "exit code: $?"check_sql OK models/daily_revenue.sql
exit code: 0Then Claude rebuilds the models and queries the new daily revenue.
python build.pybuilt customer_orders 11 rows
built daily_revenue 4 rowsduckdb shop.duckdb -c "SELECT * FROM daily_revenue"┌────────────┬────────┬─────────┬─────────┐
│ order_date │ orders │ revenue │ refunds │
│ date │ int64 │ double │ double │
├────────────┼────────┼─────────┼─────────┤
│ 2026-10-01 │ 2 │ 195.5 │ 0.0 │
│ 2026-10-02 │ 3 │ 344.25 │ 30.0 │
│ 2026-10-03 │ 3 │ 302.75 │ 10.0 │
│ 2026-10-04 │ 3 │ 247.0 │ 0.0 │
└────────────┴────────┴─────────┴─────────┘The build prints customer_orders with 11 rows and daily_revenue with 4 rows. Daily revenue now has a refunds column: 30.0 on 2026-10-02 and 10.0 on 2026-10-03, and 0.0 on the other two days.
Keep hooks fast and safe. This one opens the database read only and only explains the query, so it never changes your data.
Step 3: A read-only SQL reviewer sub-agent
Next, a sub-agent that reviews our SQL. We ask Claude to create a read-only reviewer as a project sub-agent.
Create a read-only sql-reviewer subagent in .claude/agents that reviews models/ and runs checks/run_checks.pyA sub-agent is a Markdown file in the agents folder inside .claude. name and description are required, and the description tells Claude when to use it. The tools line gives it only Read and Bash: it can look at files and run the checks, but it has no edit or write tool. Below the front matter are its instructions.
---
name: sql-reviewer
description: Reviews the SQL models and runs the data checks. Use proactively after SQL changes. Read-only - reports problems, never edits files.
tools: Read, Bash
model: sonnet
---
You are a senior data engineer reviewing this shop pipeline.
When invoked:
1. Read every file in models/.
2. Run `python checks/run_checks.py` and read the result.
3. Report each problem as: where, what is wrong, the fix.
Do not edit any file. Keep the report under 10 lines.The agents folder is new in this project, so we restart Claude Code once to load it: /exit, then claude -c to continue the same conversation. Now we call the sub-agent by name with @agent- and the agent name.
@agent-sql-reviewer review the models and run the data checksClaude hands the job to the sql-reviewer. The sub-agent works in its own context window. It reads the models and runs the check script. Here is the real output of the checks it ran.
python checks/run_checks.pycheck bad result
orders: duplicate order_id 0 pass
orders: unknown customer 1 FAIL order 1012
payments: unknown order 1 FAIL payment 9
payments: amount <= 0 0 pass
models: select * 1 FAIL customer_orders.sql
3 of 5 checks failed3 of 5 checks fail, and the script exits with code 1. Order 1012 has an unknown customer, payment 9 points to an order that does not exist, and customer_orders.sql uses select *. Claude gets back only a short summary, not the whole review, so the main conversation stays small.
sql-reviewer found 3 problems (no files changed):
1. orders: order 1012 has customer 107, which is not in customers.
2. payments: payment 9 points to order 1099, which does not exist.
3. models/customer_orders.sql uses SELECT *: list the columns.
Keep reviewers read only, so they report problems instead of quietly changing code.
Step 4: Two worktrees for parallel sessions
Now parallel agents. Two Claude sessions can work at the same time, but not in the same folder, or they edit the same files. The answer is a git worktree: a second folder of the same repo on its own branch. We ask Claude to commit our work and create two worktrees, one for tests and one for a new model.
Commit our work, then create two worktrees in .claude/worktrees: add-tests and orders-modelClaude first commits the hook, the sub-agent and the refunds change, so both worktrees start from the same point. Then it creates two worktrees inside .claude/worktrees, each on a new branch, and lists them.
git add -A && git commit -m "Add check_sql hook, sql-reviewer agent and refunds"[main d797584] Add check_sql hook, sql-reviewer agent and refunds
4 files changed, 69 insertions(+), 2 deletions(-)
create mode 100644 .claude/agents/sql-reviewer.md
create mode 100755 .claude/hooks/check_sql.py
create mode 100644 .claude/settings.jsongit worktree add .claude/worktrees/add-tests -b worktree-add-testsPreparing worktree (new branch 'worktree-add-tests')
HEAD is now at d797584 Add check_sql hook, sql-reviewer agent and refundsgit worktree add .claude/worktrees/orders-model -b worktree-orders-modelPreparing worktree (new branch 'worktree-orders-model')
HEAD is now at d797584 Add check_sql hook, sql-reviewer agent and refundsgit worktree list/home/aamir/shop-pipeline d797584 [main]
/home/aamir/shop-pipeline/.claude/worktrees/add-tests d797584 [worktree-add-tests]
/home/aamir/shop-pipeline/.claude/worktrees/orders-model d797584 [worktree-orders-model]The commit is d797584 with 4 files changed. git worktree list shows the main folder and the two new ones, all on d797584, on the branches main, worktree-add-tests and worktree-orders-model. This is the same layout that claude --worktree creates for you. Claude tells us to open a new terminal and run claude --worktree add-tests, and Claude starts inside that worktree. The same goes for orders-model.
In those two sessions, one Claude writes tests and the other writes a customer lifetime value model. Each commits on its own branch. Here are the files they wrote and their commits.
import subprocess
import duckdb
import pytest
@pytest.fixture(scope="module")
def con():
subprocess.run(["python", "build.py"], check=True, capture_output=True)
return duckdb.connect("shop.duckdb", read_only=True)
def test_daily_revenue_counts_only_completed_orders(con):
orders = con.execute("select sum(orders) from daily_revenue").fetchone()[0]
completed = con.execute("select count(*) from orders where status = 'completed'").fetchone()[0]
assert orders == completed
def test_refunds_never_exceed_revenue(con):
assert con.execute("select count(*) from daily_revenue where refunds > revenue").fetchone()[0] == 0
def test_every_model_has_rows(con):
views = con.execute("select view_name from duckdb_views() where not internal").fetchall()
for (name,) in views:
assert con.execute(f"select count(*) from {name}").fetchone()[0] > 0, nameselect
c.customer_id,
c.name,
count(o.order_id) as orders,
coalesce(sum(o.amount), 0) as lifetime_value
from customers c
left join orders o
on o.customer_id = c.customer_id
and o.status = 'completed'
group by c.customer_id, c.name
order by lifetime_value descgit -C .claude/worktrees/add-tests add -A && git -C .claude/worktrees/add-tests commit -qm "Add model tests"The command prints nothing.
git -C .claude/worktrees/orders-model add -A && git -C .claude/worktrees/orders-model commit -qm "Add customer_ltv model"The command prints nothing.
Add .claude/worktrees/ to your .gitignore. This project already has it, so the worktree copies never end up in a commit.
Step 5: Test and merge the parallel work
Both parallel sessions have finished. Back in the main session, we ask Claude to check the work and merge it.
Both sessions are done. Run the tests in add-tests, merge both branches into main, then remove the worktreesClaude runs the new tests inside the add-tests worktree, merges both branches into main, and removes the two worktrees.
(cd .claude/worktrees/add-tests && python -m pytest -q)... [100%]
3 passed in 0.27sgit merge worktree-add-tests && git merge --no-edit worktree-orders-modelUpdating d797584..36afead
Fast-forward
tests/test_models.py | 26 ++++++++++++++++++++++++++
1 file changed, 26 insertions(+)
create mode 100644 tests/test_models.py
Merge made by the 'ort' strategy.
models/customer_ltv.sql | 11 +++++++++++
1 file changed, 11 insertions(+)
create mode 100644 models/customer_ltv.sqlgit worktree remove .claude/worktrees/add-tests && git worktree remove .claude/worktrees/orders-model && git log --oneline --graph* a9d893d Merge branch 'worktree-orders-model'
|\
| * 49d371d Add customer_ltv model
* | 36afead Add model tests
|/
* d797584 Add check_sql hook, sql-reviewer agent and refunds
* 60bb3c2 Initial shop pipelineAll 3 tests pass. The first merge is a fast-forward from d797584 to 36afead that adds tests/test_models.py. The second is a merge commit made by the 'ort' strategy that adds models/customer_ltv.sql. The log graph shows both branches joined back into main on top of the initial commit.
Every parallel session has its own context, so token usage goes up with each one. Use parallel sessions for independent tasks that touch different files.
Which one to use when
So which tool should you use, and when?
Pick the right tool
- Hook: rules that must run every time (CLAUDE.md guides, a hook enforces)
- Sub-agent: a focused job, like a review, in its own context
- Worktrees: independent tasks in parallel, then test and merge
Use a hook for rules that must run every time. Your CLAUDE.md file is guidance, and a hook is enforcement. Use a sub-agent for a focused job, like a review, that should not fill your main conversation. And use parallel sessions in worktrees for independent tasks, then test and merge them like any other branch.
That is hooks, sub-agents and parallel agents on a real data pipeline: a hook that blocks bad SQL, a read-only reviewer, and two sessions merged back into main. In the next part we take the same review into CI, with Claude Code GitHub Actions on a pull request.
Key points to remember
- PostToolUse hook: exit 2 blocks bad SQL
- Hook settings in .claude/settings.json
- Read-only sql-reviewer sub-agent
- @agent-name to call a sub-agent
- Git worktrees for parallel sessions
- Test and merge the parallel work
The full script
This bash script writes every file Claude wrote, in the same order, and runs every command, so you can reproduce the results without Claude.
Show the full script (every file and every command, in order)
#!/usr/bin/env bash
# Claude for Data Engineers #8: Hooks, Sub-agents and Parallel Agents for Data Pipelines
# Rebuilds the demo project without Claude: writes every file Claude wrote, in the same order,
# and runs every command from the video. Run it in an empty folder.
# Tools used in the video: Python 3.13, DuckDB 2.0.0, pandas 3.0.5, PySpark 4.3.0 (local mode, Java 21),
# dbt-core 1.12.6 with dbt-duckdb 1.11.0, PostgreSQL 16. Install the ones this project uses first.
mkdir -p shop-pipeline && cd shop-pipeline
# ---------- starting files ----------
mkdir -p data
cat > data/customers.csv <<'CLAUDE_DE_EOF'
customer_id,name,country,signup_date
101,Ava Brooks,US,2026-08-02
102,Liam Chen,CA,2026-08-05
103,Noah Patel,US,2026-08-11
104,Mia Garcia,MX,2026-08-19
105,Zoe Martin,UK,2026-09-01
106,Omar Khan,US,2026-09-03
CLAUDE_DE_EOF
mkdir -p data
cat > data/orders.csv <<'CLAUDE_DE_EOF'
order_id,customer_id,order_date,status,amount
1001,101,2026-10-01,completed,120.00
1002,102,2026-10-01,completed,75.50
1003,103,2026-10-01,cancelled,40.00
1004,101,2026-10-02,completed,210.00
1005,104,2026-10-02,completed,35.25
1006,105,2026-10-02,completed,99.00
1007,106,2026-10-03,completed,150.00
1008,102,2026-10-03,completed,64.75
1009,103,2026-10-03,completed,88.00
1010,104,2026-10-04,completed,45.00
1011,105,2026-10-04,completed,130.00
1012,107,2026-10-04,completed,72.00
CLAUDE_DE_EOF
mkdir -p data
cat > data/payments.csv <<'CLAUDE_DE_EOF'
payment_id,order_id,paid_at,amount,refund_amount
1,1001,2026-10-01,120.00,0.00
2,1002,2026-10-01,75.50,0.00
3,1004,2026-10-02,210.00,30.00
4,1005,2026-10-02,35.25,0.00
5,1006,2026-10-02,99.00,0.00
6,1007,2026-10-03,150.00,0.00
7,1008,2026-10-03,64.75,10.00
8,1009,2026-10-03,88.00,0.00
9,1099,2026-10-04,45.00,0.00
10,1011,2026-10-04,130.00,0.00
11,1012,2026-10-04,72.00,0.00
CLAUDE_DE_EOF
cat > build.py <<'CLAUDE_DE_EOF'
import pathlib
import duckdb
con = duckdb.connect("shop.duckdb")
for table in ("customers", "orders", "payments"):
con.execute(f"CREATE OR REPLACE TABLE {table} AS SELECT * FROM read_csv('data/{table}.csv', header = true)")
for f in sorted(pathlib.Path("models").glob("*.sql")):
con.execute(f"CREATE OR REPLACE VIEW {f.stem} AS " + f.read_text())
rows = con.execute(f"SELECT count(*) FROM {f.stem}").fetchone()[0]
print(f"built {f.stem:<16} {rows:>3} rows")
CLAUDE_DE_EOF
mkdir -p models
cat > models/daily_revenue.sql <<'CLAUDE_DE_EOF'
select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue
from orders o
where o.status = 'completed'
group by o.order_date
order by o.order_date
CLAUDE_DE_EOF
mkdir -p models
cat > models/customer_orders.sql <<'CLAUDE_DE_EOF'
select *
from orders o
join customers c using (customer_id)
CLAUDE_DE_EOF
mkdir -p checks
cat > checks/run_checks.py <<'CLAUDE_DE_EOF'
"""Data checks for the shop pipeline. Exit code 1 when any check fails."""
import pathlib
import re
import sys
import duckdb
con = duckdb.connect("shop.duckdb", read_only=True)
CHECKS = {
"orders: duplicate order_id": "select order_id from orders group by order_id having count(*) > 1",
"orders: unknown customer": "select 'order ' || order_id from orders where customer_id not in (select customer_id from customers)",
"payments: unknown order": "select 'payment ' || payment_id from payments where order_id not in (select order_id from orders)",
"payments: amount <= 0": "select 'payment ' || payment_id from payments where amount <= 0",
}
results = {name: [str(r[0]) for r in con.execute(sql).fetchall()] for name, sql in CHECKS.items()}
results["models: select *"] = [f.name for f in sorted(pathlib.Path("models").glob("*.sql"))
if re.search(r"select\s+\*", f.read_text(), re.IGNORECASE)]
print(f"{'check':<28} {'bad':>3} result")
for name, bad in results.items():
print(f"{name:<28} {len(bad):>3} " + ("FAIL " + ", ".join(bad) if bad else "pass"))
failed = sum(1 for bad in results.values() if bad)
print(f"{failed} of {len(results)} checks failed")
sys.exit(1 if failed else 0)
CLAUDE_DE_EOF
cat > .gitignore <<'CLAUDE_DE_EOF'
shop.duckdb
__pycache__/
.pytest_cache/
.claude/worktrees/
CLAUDE_DE_EOF
# ---------- setup ----------
python build.py
git init -q -b main && git add -A && git commit -qm "Initial shop pipeline"
# ---------- step 1: Write the PostToolUse hook script and register it in .claude/settings.json ----------
mkdir -p .claude/hooks
cat > .claude/hooks/check_sql.py <<'CLAUDE_DE_EOF'
#!/usr/bin/env python3
""PostToolUse hook: check every SQL file Claude edits or writes.
Exit 0 = fine. Exit 2 = blocked: Claude sees the message on stderr and fixes the file.""
import json
import os
import re
import sys
import duckdb
event = json.load(sys.stdin) # Claude Code sends the event as JSON on stdin
path = event.get("tool_input", {}).get("file_path", "")
if not path.endswith(".sql"):
sys.exit(0) # only SQL files are checked
project = os.environ.get("CLAUDE_PROJECT_DIR", event.get("cwd", os.getcwd()))
full = os.path.join(project, path)
sql = open(full).read()
problems = []
if re.search(r"select\s+\*", sql, re.IGNORECASE):
problems.append("SELECT * is not allowed in models: list the columns")
try:
con = duckdb.connect(os.path.join(project, "shop.duckdb"), read_only=True)
con.execute("EXPLAIN " + sql) # compiles the query: every table and column must exist
except duckdb.Error as e:
problems += [l for l in str(e).split("\n") if l and not l.startswith(("LINE", " "))]
name = os.path.relpath(full, project)
if problems:
print(f"check_sql BLOCKED {name}", file=sys.stderr)
for p in problems:
print(f" - {p}", file=sys.stderr)
sys.exit(2)
print(f"check_sql OK {name}")
CLAUDE_DE_EOF
mkdir -p .claude
cat > .claude/settings.json <<'CLAUDE_DE_EOF'
{
"hooks": {
"PostToolUse": [
{
"matcher": "Edit|Write",
"hooks": [
{
"type": "command",
"command": "$CLAUDE_PROJECT_DIR/.claude/hooks/check_sql.py",
"timeout": 30
}
]
}
]
}
}
CLAUDE_DE_EOF
# ---------- step 2: Test the hook by piping in the JSON event Claude Code sends ----------
chmod +x .claude/hooks/check_sql.py && echo '{"tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}' | .claude/hooks/check_sql.py; echo "exit $?"
echo '{"tool_name":"Edit","tool_input":{"file_path":"models/customer_orders.sql"}}' | .claude/hooks/check_sql.py; echo "exit $?"
# ---------- step 3: The hook flags an edit that uses a column that does not exist, and Claude sees the message ----------
mkdir -p models
cat > models/daily_revenue.sql <<'CLAUDE_DE_EOF'
select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue,
sum(p.refund_amt) as refunds
from orders o
left join payments p on p.order_id = o.order_id
where o.status = 'completed'
group by o.order_date
order by o.order_date
CLAUDE_DE_EOF
export EVENT='{"hook_event_name":"PostToolUse","tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}'
echo "$EVENT" | .claude/hooks/check_sql.py; echo "exit code: $?"
# ---------- step 4: Claude fixes the column name and the hook passes ----------
mkdir -p models
cat > models/daily_revenue.sql <<'CLAUDE_DE_EOF'
select
o.order_date,
count(*) as orders,
sum(o.amount) as revenue,
coalesce(sum(p.refund_amount), 0) as refunds
from orders o
left join payments p on p.order_id = o.order_id
where o.status = 'completed'
group by o.order_date
order by o.order_date
CLAUDE_DE_EOF
export EVENT='{"hook_event_name":"PostToolUse","tool_name":"Edit","tool_input":{"file_path":"models/daily_revenue.sql"}}'
echo "$EVENT" | .claude/hooks/check_sql.py; echo "exit code: $?"
# ---------- step 5: Rebuild the models and look at daily revenue with refunds ----------
python build.py
duckdb shop.duckdb -c "SELECT * FROM daily_revenue"
# ---------- step 6: Create the read-only sql-reviewer sub-agent ----------
mkdir -p .claude/agents
cat > .claude/agents/sql-reviewer.md <<'CLAUDE_DE_EOF'
---
name: sql-reviewer
description: Reviews the SQL models and runs the data checks. Use proactively after SQL changes. Read-only - reports problems, never edits files.
tools: Read, Bash
model: sonnet
---
You are a senior data engineer reviewing this shop pipeline.
When invoked:
1. Read every file in models/.
2. Run `python checks/run_checks.py` and read the result.
3. Report each problem as: where, what is wrong, the fix.
Do not edit any file. Keep the report under 10 lines.
CLAUDE_DE_EOF
# ---------- step 7: The checks the sql-reviewer runs ----------
python checks/run_checks.py # expected to fail
# ---------- step 8: Commit, then create two git worktrees for two parallel sessions ----------
git add -A && git commit -m "Add check_sql hook, sql-reviewer agent and refunds"
git worktree add .claude/worktrees/add-tests -b worktree-add-tests
git worktree add .claude/worktrees/orders-model -b worktree-orders-model
git worktree list
# ---------- step 9: The two parallel sessions: tests in one worktree, a new model in the other (each commits on its branch) ----------
mkdir -p .claude/worktrees/add-tests/tests
cat > .claude/worktrees/add-tests/tests/test_models.py <<'CLAUDE_DE_EOF'
import subprocess
import duckdb
import pytest
@pytest.fixture(scope="module")
def con():
subprocess.run(["python", "build.py"], check=True, capture_output=True)
return duckdb.connect("shop.duckdb", read_only=True)
def test_daily_revenue_counts_only_completed_orders(con):
orders = con.execute("select sum(orders) from daily_revenue").fetchone()[0]
completed = con.execute("select count(*) from orders where status = 'completed'").fetchone()[0]
assert orders == completed
def test_refunds_never_exceed_revenue(con):
assert con.execute("select count(*) from daily_revenue where refunds > revenue").fetchone()[0] == 0
def test_every_model_has_rows(con):
views = con.execute("select view_name from duckdb_views() where not internal").fetchall()
for (name,) in views:
assert con.execute(f"select count(*) from {name}").fetchone()[0] > 0, name
CLAUDE_DE_EOF
mkdir -p .claude/worktrees/orders-model/models
cat > .claude/worktrees/orders-model/models/customer_ltv.sql <<'CLAUDE_DE_EOF'
select
c.customer_id,
c.name,
count(o.order_id) as orders,
coalesce(sum(o.amount), 0) as lifetime_value
from customers c
left join orders o
on o.customer_id = c.customer_id
and o.status = 'completed'
group by c.customer_id, c.name
order by lifetime_value desc
CLAUDE_DE_EOF
git -C .claude/worktrees/add-tests add -A && git -C .claude/worktrees/add-tests commit -qm "Add model tests"
git -C .claude/worktrees/orders-model add -A && git -C .claude/worktrees/orders-model commit -qm "Add customer_ltv model"
# ---------- step 10: Run the tests in the worktree, merge both branches, remove the worktrees ----------
(cd .claude/worktrees/add-tests && python -m pytest -q)
git merge worktree-add-tests && git merge --no-edit worktree-orders-model
git worktree remove .claude/worktrees/add-tests && git worktree remove .claude/worktrees/orders-model && git log --oneline --graph
Part 9: Claude Code in CI/CD: Review Data Pipeline Pull Requests with GitHub Actions
The project and its data are invented for teaching. Every file on this page is the real file from the demo project, and every output is the real output of the command above it, captured when the video was made (each step ran twice in a fresh folder and the outputs were compared). The Claude screens in the video are a simplified recreation for teaching. Always review what Claude changes and runs. Claude plans, features and limits are current as of November 2026; check the official Claude and Claude Code documentation before you rely on them. Part of the Claude for Data Engineers series on TechBrothersIT.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.