Capstone — the warehousing checklist, end to end

At a glance

Learning objectives

• Audit a warehouse project against the model-tests-docs triad
• Verify layering, grain, incremental lookback, and CI staging on one project
• Leave a repo whose structure a new joiner can read

What you need

• A warehouse project you own or review — the dbt-warehouse-tests starter works
• The project's query history and CI configuration
• One afternoon to run the checklist for real

Steps

1. Audit the triad — every model is code with three faces: model (SQL) + tests (schema.yml) + docs (descriptions); ref() builds the DAG, tests encode the invariants, and materialisation is a deliberate choice per model. (from pattern-dbt-tests-docs )
2. Check the layers and the load — staging, intermediate, and marts with the grain declared, and incremental on fct_orders with a 3-day lookback. (from project-dbt-warehouse-tests )
3. Check the test set — unique + not_null on the fct grain key, relationships from fct to dims, and accepted_values on status. (from project-dbt-warehouse-tests )
4. Check the production gate — CI builds to a per-PR staging schema and runs full tests, source freshness checks on raw, and exposures declared for dashboards. (from project-dbt-warehouse-tests )
5. Check the watchtower — alert on the partitions_scanned/partitions_total ratio and review query history weekly for the top cost queries. (from runbook-warehouse-query-slow )
6. Check the team constraints — logic must be reviewable, changes must be testable, and new joiners need to understand the lineage. (from pattern-dbt-tests-docs )
7. Check the repo reads clean — models/staging, models/intermediate, models/marts, schema.yml with tests and descriptions, singular tests, macros, and the CI workflow. (from tpl-dbt-project )

Check yourself

• Every model you audited has SQL, tests in schema.yml, and a description — no exceptions found or all filed.
• The fact grain key carries unique + not_null, relationships tie fct to dims, and CI runs full tests on a staging schema.
• The partitions-ratio alert exists and someone owns the weekly top-cost review.

Worked example

dbt-project/
  dbt_project.yml
  models/
    staging/
    intermediate/
    marts/
  macros/
  tests/
  .github/workflows/ci.yml

raw.orders ─▶ stg_orders ─▶ int_orders_enriched ─┐
raw.customers ─▶ stg_customers ──────────────────┼─▶ fct_orders ─▶ marts
seeds/ ─▶ dim_date ──────────────────────────────┘
            (tests + docs on every node; CI on staging schema)

Sourced from

• dbt as Models + Tests + Docs
• dbt → warehouse → tests → docs
• Warehouse query is slow
• dbt Project Template

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Grain key not unique → a join fanned out upstream.
• CI green but prod differs → CI ran on a different target/schema.
• Late-arriving order missed → lookback window too short.

Production considerations

• Keep tests and descriptions on every node; CI on a staging schema with source freshness on raw.
• Keep the partitions-ratio alert and the weekly top-cost review on the calendar.

Diagram

warehousing capstone checklist
   |
   |- triad --> model SQL + tests + docs per model
   |
   |- layers --> staging / intermediate / marts, grain declared
   |
   |- load --> incremental + 3-day lookback on fct_orders
   |
   |- gate --> CI on staging schema + freshness + exposures
   |
   |- watchtower --> partitions-ratio alert + weekly top-cost review

Registry stats