Module 4 · Days 31–40 · Part II

Data Modeling with dbt

Transforming staged data into production-ready analytical marts.

Start Day 31

0/10 days completed

0/10 videos published

What you'll learn

1
Days 31–33
Project initialization, materializations (views/tables), and basic Jinja templating.
By the end: Students have a working dbt project, macro_dbt, that rebuilds the Module 2 staging and intermediate layers from raw_dlt. dbt works out the build order itself, materialisations are chosen deliberately, reference data is managed as seeds, and repetitive SQL is generated with Jinja. It all runs **alongside** the Module 2 warehouse in separate dev_* schemas, so nothing breaks while you learn.
2
Days 34–37
Modular modeling (staging, intermediate, marts), DRY principles, and dbt macros.
By the end: The macro_dbt project is **organised** (one staging model per source table, intermediate models by economic concept, marts by consumer), **DRY** (growth rates, aggregation rules and recession logic each written once as macros), **self-configuring** (series lists read from seeds with run_query) and **tested** (generic, singular, custom, unit tests and source freshness). It also includes an incremental model and a snapshot, ready for the macro data mart on Days 38–40.
3
Days 38–40
A macro data mart joining yield curves with USD/CAD: an open-economy dashboard schema.
By the end: A production dbt mart layer that answers open-economy questions: how Canadian and US yield curves, the policy-rate differential, oil and USD/CAD move together, daily and monthly. It is documented with exposures, protected by model contracts, exported for the dashboard notebook, and **live in prod**, with the Module 2 hand-built warehouse retired after a final regression test.

Tools

dbtJinjaDuckDB

The ten days

DAY 31

Your First dbt Project

6 outcomes Soon

DAY 32

Materializations, Configuration, Seeds and Documentation

6 outcomes Soon

DAY 33

Basic Jinja Templating

DRY Principles and dbt Macros

Incremental Models, Snapshots and Project Quality

6 outcomes Soon

DAY 38

Designing the Mart and Building Its Foundations

5 outcomes Soon

DAY 39

The Open-Economy Mart

Before you start: module setup and project structure

Audience: MSc Economics students who completed Modules 1–3 (Days 1–30) Environment: GitHub Codespaces · course .venv · macro.duckdb fed by ingest.py (dlt → raw_dlt) Time per day: ~2.5 hours (including breaks and exercises) Goal by end of Day 33: Students have a working dbt project, macro_dbt, that rebuilds the Module 2 staging and intermediate layers from raw_dlt. dbt works out the build order itself, materialisations are chosen deliberately, reference data is managed as seeds, and repetitive SQL is generated with Jinja. It all runs alongside the Module 2 warehouse in separate dev_* schemas, so nothing breaks while you learn.

Why dbt, and why now?

In Module 2 you built a layered warehouse by hand. It works, but you felt the friction:

Module 2 (hand-built)	dbt
Files numbered `10_`, `20_`, `30_` so they run in the right order	Order inferred from `ref()` — no numbering
`CREATE OR REPLACE VIEW …` boilerplate in every file	You write only the `SELECT`; dbt writes the DDL
Changing view ↔ table means rewriting the file	One configuration word: `materialized`
`load_warehouse.py` + `run_tests.py` written by you	`dbt run`, `dbt test`, `dbt build`
Seeds as `INSERT INTO … VALUES`	Seeds as CSV files, loaded with `dbt seed`
Documentation in SQL comments	Documentation site with a lineage graph
Copy-pasted SQL patterns (growth rates, pivots)	Jinja templates and macros
One environment	`dev` and `prod` targets from the same code

The key idea: dbt is a compiler and runner for SELECT statements. You write SELECTs that reference other models; dbt compiles them into plain SQL, works out the dependency graph, and runs CREATE VIEW/CREATE TABLE in the right order.

Everything you learned in Module 2 (layers, naming, CTE style, window functions, gap-safe growth rates, tests as queries) carries over unchanged. dbt just removes the plumbing.

The plan for Module 4

Days	Focus
31–33	Project setup, `source()`/`ref()`, materialisations, seeds, Jinja basics. Port staging + intermediate.
34–37	Modular modelling, DRY principles, macros, tests, packages
38–40	The open-economy macro data mart (yield curves × USD/CAD), docs, cut-over to production

Target structure (by end of Day 33)

macro-data-course/
├── macro.duckdb
├── ingest.py                      ← Module 3 (unchanged)
└── macro_dbt/                     ← NEW: the dbt project
    ├── dbt_project.yml
    ├── profiles.yml
    ├── seeds/
    │   ├── seed_geo.csv
    │   ├── seed_series.csv
    │   ├── seed_recession_bands.csv
    │   ├── seed_cpi_weights.csv
    │   ├── seed_boc_decisions.csv
    │   └── _seeds.yml
    ├── models/
    │   ├── staging/
    │   │   ├── _sources.yml
    │   │   ├── _staging.yml
    │   │   ├── stg_statcan__cpi.sql
    │   │   ├── stg_statcan__gdp.sql
    │   │   └── stg_macro__observations.sql
    │   └── intermediate/
    │       ├── int_cpi__growth.sql
    │       ├── int_cpi__smoothed.sql
    │       ├── int_gdp__growth.sql
    │       └── int_rates__monthly.sql
    ├── macros/
    │   └── generate_schema_name.sql
    └── analyses/