You're building a daily morning-ops pipeline for a UK community pharmacy chain -- loading data from two distribution centres in different formats and SKU schemes, handling corrections without creating duplicates via incremental extraction and MERGE, and producing the numbers the COO reads at the 09:00 huddle.
The discipline skills: incremental extraction with watermark columns, MERGE as the idempotent loading pattern (instead of INSERT), choosing between full and incremental refresh per source, and monitoring watermark progression to catch stale pipelines.
The AI-direction lesson: building persistent AI infrastructure. Before you write pipeline code, you author a project memory file -- CLAUDE.md and AGENTS.md -- carrying the data dictionary, naming conventions, known issues, and design decisions. Then you run two sessions: one with project memory loaded, one without. The difference in output quality makes a principle you've heard before -- infrastructure determines outcomes -- something you experience directly. Meta-prompting for verification also enters: directing AI to check its own work using structured criteria, then evaluating whether the check was actually thorough. AI reviewing its own output sounds useful. Whether it catches real problems depends entirely on how you prompt it.
Your Role
You're building Jamie Okafor's daily pipeline. Templates provide structure for the new terrain. Guides are thinner than last time. You build the AI infrastructure before the pipeline -- and the infrastructure changes what AI produces.
What's New
Last time you designed schemas that track change over time -- SCD strategies for Katrine's turbines, MetricFlow for standardized metrics, PII masking across every output surface. The schema design was the hard part.
This time the extraction design is the hard part. The data arrives daily, but loading it isn't as simple as adding each day's file. Corrections mean the same records show up again. Loading them alongside the originals inflates the numbers without any error message. You'll design the extraction pattern that handles this -- and you'll build the AI infrastructure before you build the pipeline.
Meta-prompting for verification enters: directing AI to check its own work using structured criteria, then evaluating whether the check was thorough.
Tools
- dbt Core with DuckDB adapter -- transformation framework, with MERGE patterns (new this project)
- DuckDB -- local analytical database
- Soda Core -- quality monitoring
- Dagster -- orchestration, with watermark monitoring (new this project)
- GitHub Actions -- CI/CD, with pre-commit and pre-push hooks (new this project)
- Claude Code -- AI directing tool, with project memory authoring and meta-prompting for verification (new this project)
- Git / GitHub -- version control
Materials
You'll receive:
- DC data -- daily export samples from both distribution centres (CSV from Preston, JSON from Coventry), including a correction scenario for testing
- SKU bridge -- the spreadsheet mapping Clayton's internal codes to GTIN-13 barcodes, maintained by the senior Ops analyst
- Field mapping -- how the two DCs' different field names and SKU schemes correspond to each other
- Pipeline spec template -- empty structure for you to fill from what you learn from Jamie
- Project memory template -- format guide for CLAUDE.md and AGENTS.md with examples of good vs vague entries
- Incremental extraction guide -- conceptual overview of full vs incremental refresh, watermarks, and MERGE patterns