Cloud Warehouse and Semantic Layer: BigQuery and dbt.

You're setting up BigQuery and dbt Core for Aisha Siddiqui, co-founder and COO of Kapra Market — a Karachi marketplace for secondhand clothing — so her team can self-serve against governed metrics without you in the loop.

The discipline skills: cost-aware BigQuery SQL (dialect differences from DuckDB, partition filters, dry-runs, bytes-scanned as a verification metric), dbt models and YAML metric definitions, a metric-definition pull request a non-technical reviewer can approve, rewiring a Metabase dashboard onto a dbt-governed view, curating AI's auto-memory before the dbt build, and a one-page note on when dbt is the right governance model.

The AI-direction lesson: the metric definition leaves the dashboard and the prose document and lands in version-controlled code. From here, dbt is the singular source of truth. Two failure patterns come with that. AI writes BigQuery SQL in whatever dialect it defaults to and skips partition filters; on a warehouse that charges per byte scanned, that's a bill. AI's first dbt YAML compiles and dbt test goes green — but the SQL inside can implement a slightly wrong definition. Compilation is necessary but not sufficient. You verify cost at the query layer, semantic correctness at the model layer, and the dashboard's number at the consumption layer.

Your Role

You're the analyst Aisha hired to unblock her data engineer. The deliverable is infrastructure: a dbt project the team can extend, a rewired dashboard, a pull request Aisha herself reviews, and a one-page note on when this approach fits an organisation.

The relationship with AI deepens. The infrastructure floor — project memory, the profiling skill, the pre-commit hook, MCP scopes, two AI tools — runs in the background. What's new is that AI directs across three computation surfaces (BigQuery dialect, dbt YAML, the Metabase consumption layer) and verification has to match each failure mode: dry-run before the expensive query, read the compiled SQL against the plain-English definition, cross-check the dashboard. You also curate AI's auto-memory before the dbt build, because the model will inherit whatever AI thinks GMV means right now.

What's New

Last time you stood up GrowthBook for a Prague SaaS team and ran its statistical engine against a manual Python audit, reconciling two computations of the same metric.

This time the cross-system check moves into the warehouse and into code. One side is BigQuery — its own SQL dialect, a per-byte-scanned cost model, partition behaviour that determines whether queries are cheap or expensive. The other side is dbt — metric definitions in version-controlled YAML and SQL, governed through pull requests rather than Slack approvals. AI generates plausible artifacts on both sides: BigQuery SQL that runs but in the wrong dialect or scans the whole table, and dbt YAML that compiles green but encodes a slightly different definition from what CLAUDE.md says.

The audience is also different. Aisha is a co-founder, not a data engineer; the PR description has to be readable by someone who approves a change because they understand what it means, not because they recognise the syntax. AI will draft a generic recommendation; you preserve the conditions under which dbt is right and under which a documented spreadsheet would be.

The hard part is the moment the model compiles, the test passes, and the GMV number is still wrong — and you have to read the compiled SQL before Aisha approves the PR.

Tools

• Established and carried forward: Claude Code with DuckDB MCP, Codex CLI, DuckDB, Python and Jupyter, Git and GitHub with pre-commit (the hook extends to govern the dbt/ directory), CLAUDE.md, the profile-dataset skill, Metabase from P10/P11/P13 (one panel rewires onto the dbt model).
• Google Cloud Platform — new; sandbox/free-tier signup, project + billing alarm. The unit that uses it walks through setup.
• BigQuery — new; the cloud warehouse, its dialect, partitioning, and the bq query --dry-run cost workflow.
• BigQuery MCP server — new; the established MCP pattern applied to a cloud target with scoped credentials.
• dbt Core — new; project structure, models, YAML metric definitions, dbt run, dbt test.
• GitHub pull request flow — established tool, new use; the PR is the governance moment for a metric change.

Materials

• Aisha's first-contact email — formal opener, a paragraph each on symptoms and wishes. The framing work is yours.
• Synthetic BigQuery dataset — transactions (about 15M rows, intentionally unpartitioned so the cost diagnosis lands), users (about 85k, with a duplicate-ID pattern on a subset), listings, and fulfillment (with conjoined authentication-and-shipping timestamps). A small legacy_user_mapping.csv lives locally.
• Data dictionary — schema, ranges, null policy, joins, and the attributes the profiling skill cross-checks.
• Starter dbt project — dbt_project.yml, a sources YAML, one example staging model, one example test. You author the metrics within it, not the project structure.
• Starter CLAUDE.md — context, tech stack, established skill and hook populated. Metric definitions, BigQuery configuration, dbt references, auto-memory curation log, and migration rationale are placeholders you fill in.
• Reference material — BigQuery primer, dbt Core primer, BigQuery MCP setup, metric-migration trade-offs, and a guide to PR descriptions for non-technical reviewers.
• Carried-forward Metabase dashboard — from P10/P11/P13. One panel rewires onto the dbt-governed view.
• Established profile-dataset skill and .pre-commit-config.yaml — copied in; you invoke and extend, not re-author.

A senior colleague — Dr. Nkechi Obi, an analytics director — is on a separate Slack-style chat. She surfaces around the semantic-layer metric definitions. Worth pinging on a governance-fit or scope question; not someone who'll write the YAML for you.

Download the materials zip (https://learntodriveai.dev/materials/analytics/p-15/materials.zip) and unzip it to ~/dev/analytics/p-15.