Module 2 · Days 11–20 · Part I

Analytical SQL with DuckDB

In-process analytical database architecture and query optimization.

Start Day 11

0/10 days completed

0/10 videos published

What you'll learn

1
Days 11–13
DuckDB CLI, relational algebra, and loading external CSV/Parquet files.
By the end: Students can query CSV and Parquet files directly with SQL. They understand the relational operations behind every query, and they build a persistent DuckDB warehouse (macro.duckdb) loaded from their Day 10 macro_extract pipeline, ready for the window functions and CTEs of Days 14–17.
2
Days 14–17
Advanced SQL, window functions, and Common Table Expressions (CTEs).
By the end: Students write readable multi-step SQL with CTEs. They compute growth rates, rankings, moving averages, running peaks and episode analysis entirely with window functions, and they build their first analytical table, marts.policy_cycles, inside the warehouse.
3
Days 18–20
Staging CPI and GDP; year-over-year changes and moving averages directly in SQL.
By the end: Students turn raw Statistics Canada tables into a documented, tested, layered warehouse with raw → staging → intermediate → marts layers. It produces monthly inflation measures and quarterly GDP growth, computed and validated entirely in SQL, and rebuilt with one command. This closes Module 2 and sets up dlt (Module 3) and dbt (Module 4).

Tools

DuckDBSQLParquet

The ten days

DAY 11

First Steps with SQL in the DuckDB CLI

Loading External CSV and Parquet Files

7 outcomes Soon

DAY 14

Common Table Expressions

Advanced DuckDB Features and a First Analytical Mart

GDP, Mixed Frequencies and the Module 2 Capstone

6 outcomes Soon

Before you start: module setup and project structure

Audience: MSc Economics students who completed Module 1 (Days 1–10) Environment: GitHub Codespaces with the course .venv Time per day: ~2.5 hours (including breaks and exercises) Goal by end of Day 13: Students can query CSV and Parquet files directly with SQL. They understand the relational operations behind every query, and they build a persistent DuckDB warehouse (macro.duckdb) loaded from their Day 10 macro_extract pipeline, ready for the window functions and CTEs of Days 14–17.

Why SQL, and why now?

In Module 1 you learned to reshape data with pandas. SQL is the other universal language of data. It's what dbt (Days 31–40) is written in, what Great Expectations (Days 41–50) validates, and what almost every organisation's data lives behind.

DuckDB is an analytical database that runs inside your program, with no server to install or manage. It reads CSV and Parquet files directly and is extremely fast on the kind of aggregations economists run all day.

Tool	Best at
Spreadsheet	Small tables, quick eyeballing
pandas	Flexible transformations in Python
DuckDB (SQL)	Declarative queries, large files, joins, reproducible transformations, sharing logic with non-Python users

You don't have to choose: on Day 13 you'll pass data freely between pandas and DuckDB.

Target folder structure (added over Days 11–13)

macro-data-course/
├── macro.duckdb                ← your persistent warehouse (not committed)
├── load_warehouse.py           ← Day 13 capstone
├── sql/
│   ├── 01_schemas.sql
│   ├── 02_raw_tables.sql
│   └── 03_checks.sql
├── day11/
├── day12/
└── day13/

Add the database files to .gitignore:

macro.duckdb
*.duckdb
*.duckdb.wal