You're migrating supply chain analytics for a building materials company in Kuwait to BigQuery — designing cost-conscious schema architecture across four factories, implementing RBAC with column-level security, and setting up business-outcome monitoring.
The discipline skills: partitioning and clustering as cost architecture in BigQuery, incremental dbt models with strategy selection, RBAC tested through effective permissions queries, quality test strategy design across layers, and business-outcome alerting that tells the CFO something useful.
The AI-direction lesson: AI connects directly to the database and pipeline for the first time, via MCP. Before this, you described the schema and AI worked from your description. Now AI reads the schema directly, verifies column types, runs queries against live data. This is a capability shift — AI with direct access produces better references, fewer hallucinated column names, more accurate queries. But it also means AI can run expensive full-table scans, return results from the wrong partition, or take actions through connections that you need to verify in real time. The directing relationship changes: you're no longer just reviewing code AI wrote. You're evaluating actions AI takes.
Your Role
You're building Fatimah's supply chain analytics in BigQuery. Every query costs money. Partitioning and clustering aren't performance optimizations — they're cost architecture. The brief has deliberate ambiguity you'll need to resolve. Templates provide structure.
What's New
Last time you built the AI infrastructure before the pipeline — project memory files that changed what AI knew at session start. You designed incremental extraction with MERGE for Jamie Okafor's two Clayton Pharmacies distribution centres and experienced the before/after contrast that made "infrastructure determines outcomes" concrete.
This time cost enters as a design concern. The same SQL that was free in DuckDB now produces real bytes-billed numbers in BigQuery — zero dollars in your sandbox, but the shape is real. You'll design partitioning, clustering, and materialization strategy as financial decisions and measure the impact directly in INFORMATION_SCHEMA.JOBS_BY_PROJECT. RBAC enters — who can see what data, tested by actually querying as each role against the restricted views you'll deploy. Quality testing becomes a deliberate strategy: which tests at which layer, with coverage analysis as judgment about risk. And business-outcome alerting replaces generic "job failed" alerts.
The hard part is that everything is connected. Partitioning affects cost. RBAC affects cost. The wrong incremental strategy produces silently stale data. A quality test at the wrong layer catches the failure after it cascades instead of before. This is the densest project yet, by design.
Tools
- dbt Core with
dbt-bigqueryadapter (canonical) ordbt-duckdb(documented fallback) — incremental models with strategy selection, partitioning, clustering (new this project) - BigQuery in sandbox mode — real cloud warehouse, no billing, no credit card. Real
INFORMATION_SCHEMA.JOBS_BY_PROJECTfor cost attribution (new this project) - gcloud CLI — sandbox setup and authentication (new this project)
- DuckDB — local analytical database (used for the documented fallback path)
- Soda Core — quality monitoring, trend-based anomaly detection
- Dagster — orchestration, business-outcome alerting (new this project). Ships as a starter project at
dagster_project/. - Claude Code — AI directing tool, with MCP connections to BigQuery and Dagster (new this project), context briefs (new this project)
- Git / GitHub — version control
Materials
You'll receive:
- Factory data — export samples from all four factories (CSV from Factories 1-2, JSON from Factories 3-4), each with different column names and material coding schemes
- Material mapping — master reconciliation table mapping all four coding schemes to standard product codes
- BigQuery sandbox setup script —
scripts/setup-bigquery-sandbox.shwalks you through creating the GCP project, installing gcloud, authenticating, and creating the warehouse dataset (one-time, ~10-15 min) - BigQuery loader —
scripts/load-to-bigquery.pyloads the four factory exports + mapping table into your sandbox asraw_*tables - MCP config —
mcp.jsonwires Claude Code to your BigQuery sandbox and your local Dagster instance - Restricted view scaffolding — three SQL files at
sql/views/that the Unit 6 RBAC lesson hangs on - Dagster starter project — minimal Dagster project at
dagster_project/that Unit 7 extends - DuckDB-only fallback —
fallback/for students who can't use GCP; documents what's lost in fallback mode - Pipeline spec template — empty structure for you to fill from your work with Fatimah
- Project governance file — CLAUDE.md with the project context, cloud strategy, schema design reference, RBAC role-to-view map, and known data issues