You're building a UDI chain-of-custody pipeline for an orthopedic medical-device manufacturer in Minneapolis — extracting data from three systems (MES, sterilization vendor, hospital consignment) into DuckDB, resolving identity across different identification schemes, and producing a mart for recall-response traversal queries.
The discipline skills: multi-source extraction with per-source error quarantine, resolving identity across systems that use different keys, building a full layer architecture (staging to intermediate to mart), and orchestrating it all with Dagster's asset lineage.
The AI-direction lesson: when you ask AI to join data across sources, it matches on column names. Column names are not semantic contracts. The MES lot_number refers to a manufacturing lot. The sterilization vendor's lot_number refers to an irradiation batch. The column names are identical. The meanings are not. Whether a join across them makes business sense is your judgment, not AI's. This is the first project where AI's output requires domain judgment to verify — not just counting rows or checking totals, but evaluating whether a join makes business sense. AI will propose joins confidently. Some of them will be wrong in ways that produce plausible-looking numbers.
Your Role
You're building the pipeline that connects Rashida's three systems. Dagster coordinates extraction and transformation. Its asset lineage helps trace problems when numbers look wrong. You direct AI through multi-source work — and you decide whether the connections it proposes are real.
What's New
Last time you built a dbt project from three data sources for Mihai's cheese operation. The sources were straightforward CSV files that loaded into staging models with naming conventions you applied.
This time, the sources have different identification schemes. MES uses device serials. The sterilization vendor uses irradiation lot numbers. No field directly connects them. A SharePoint bridge table maintained by Ops analysts spans the gap — but the bridge has inconsistencies. Records that can't be matched don't just disappear into wrong counts. They go to an error quarantine table so you know exactly what's missing and why.
The transformation layer gets deeper too. In P3, you went from staging to mart. Here you'll add an intermediate layer for the business logic that's too complex to put in a mart query — the cross-source chain-of-custody assembly, first-pass yield calculations, traceability status. And Dagster enters for the first time, turning your pipeline from something you run manually into something with dependency tracking, lineage, and failure propagation.
Tools
- Python — via your Miniconda
deenvironment - DuckDB — analytical database for all sources
- SQL — transformation logic inside dbt models
- dbt Core + dbt-duckdb adapter — transformation framework
- Dagster — pipeline orchestration (new this project). Dagster was built as a response to Airflow's limitations — software-defined assets instead of task DAGs, built-in lineage, type checking. It represents the next generation of orchestration. The unit that introduces Dagster walks through asset definitions and the UI.
- Claude Code — your AI agent
- Git / GitHub — version control
Materials
You'll receive:
- Pipeline spec template — requirements are filled in, schema design sections are yours to complete after profiling
- Sample data from each source — MES, sterilization vendor, hospital consignment, and the SharePoint bridge table
- Full datasets — the complete data for each source
- dbt project scaffold — directory structure and configuration, no models
- Dagster project scaffold — basic structure, no assets defined
- Verification checklist — row counts, match rates, chain-of-custody statistics, yield ranges
- Project governance file — CLAUDE.md with data dictionary, naming conventions, and ticket backlog