A fully-runnable dbt project for a beginner data-engineering class, on real public data: Pagila — the standard Postgres sample database (a DVD-rental store, ~51k payments & rentals). Both Postgres and dbt Core run in Docker — no local Python required. Every step here maps to a ▶ LAB CHECKPOINT in the slides.
📊 Slides: https://thosangs.github.io/dbt_lecture/ · brutalist × teal, diagram-driven
flowchart LR
subgraph docker["🐳 docker compose up"]
direction LR
subgraph pg["postgres:18"]
raw[("Pagila public schema<br/>customer · rental<br/>payment · film")]
end
dbgate["DbGate :15424<br/>tables · SQL · ER diagram"]
docs["dbt-docs :15480<br/>lineage · model docs"]
end
subgraph ondemand["docker compose run dbt"]
dbt["dbt Core<br/>run · test · build"]
end
dbgate -->|browse| raw
docs -->|catalog| raw
dbt -->|reads / writes SQL| raw
flowchart LR
subgraph sources["sources"]
S1[customer]
S2[payment]
S3[film]
S4[address · city · country]
S5[film_category · category]
end
subgraph staging["staging (views)"]
M1[stg_customers]
M2[stg_payments]
M3[stg_films]
M4[stg_addresses · cities · countries]
M5[stg_film_categories · categories]
end
subgraph intermediate["intermediate (views)"]
I1[int_addresses]
I2[int_film_categories]
I3[int_customer_payments]
end
subgraph marts["marts (tables)"]
D1[dim_customers]
D2[dim_films]
F1[fct_payments]
end
SEED[rating_descriptions]
S1 --> M1
S2 --> M2
S3 --> M3
S4 --> M4
S5 --> M5
M4 --> I1
M5 --> I2
M2 --> I3
M1 --> D1
I1 --> D1
I3 --> D1
M3 --> D2
I2 --> D2
SEED --> D2
M2 --> F1
M1 --> F1
| Service | What it is | URL |
|---|---|---|
postgres |
The warehouse (Postgres 18). Pagila is auto-loaded into public on first boot. |
localhost:15432 |
dbgate |
DbGate — a modern browser UI: data grid with per-column filters, SQL editor, ER diagrams. Opens straight to a pre-defined Pagila connection (no login), and shows the partitioned payment table as one (51k rows). |
http://localhost:15424 |
dbt-docs |
dbt docs site — model descriptions, column metadata, interactive lineage graph. Regenerates on container start. | http://localhost:15480 |
dbt |
dbt Core CLI for labs. Invoked on demand with docker compose run (profile dbt; not started by plain up). |
— |
docker compose up -d # postgres + dbgate + dbt-docs
# Browse the raw Pagila data — opens straight to the "Pagila" connection, no login:
open http://localhost:15424
# dbt docs / lineage (after models are built, restart to refresh):
open http://localhost:15480
# drop into the dbt container — every dbt command runs in here:
docker compose run --rm dbt bashYou are now inside the dbt container when you run bash. Run the labs below from that shell.
(One-off without a shell: docker compose run --rm dbt dbt debug.)
After changing models or YAML docs, refresh the docs site:
docker compose restart dbt-docsdbt debug # expect: "All checks passed!"
dbt run --select my_first_modeldbt seed # load rating_descriptions.csv
dbt run --select staging # stg_* views (1:1 with sources)
dbt run --select intermediate # int_* views (joins & rollups)
dbt run --select dim_customers dim_films # dimension tables
dbt run --select fct_payments # incremental: full build (~51k rows)
dbt run --select fct_payments # incremental: delta only (INSERT 0 0)dbt run --select stg_customers # uses the full_name() macro
dbt run --select dim_customers # +post-hook grants to "reporter"
dbt run --select fct_payments --vars '{"start_date": "2022-04-01"}'dbt test # generic + singular tests
dbt snapshot # SCD Type 2 history of customer.last_update
dbt build --select +fct_payments # run + test the model and everything upstream
docker compose restart dbt-docs # regenerate + serve at http://localhost:15480docker exec -it dbt_class_pg psql -U dbt -d analytics \
-c "select * from dev.dim_customers order by lifetime_value desc limit 10;"docker-compose.yml postgres + dbgate + dbt-docs + dbt (on-demand)
docker/init/ 01_setup.sql + Pagila dump -> loaded into public (dbt SOURCES)
docker/dbt/Dockerfile the dbt Core + Postgres adapter image
dbt_project.yml project config (paths, vars, post-hook, materializations)
profiles.yml connection; host is env-driven (container vs local)
seeds/ rating_descriptions.csv (a tiny static lookup)
models/
example/ my_first_model.sql (hello world, table)
staging/ stg_* views (1:1 with Pagila sources)
intermediate/ int_* views (joins & rollups between staging and marts)
marts/ dim_customers, dim_films (tables), fct_payments (incremental)
macros/ full_name.sql
snapshots/ customers_snapshot.sql (SCD Type 2 on last_update)
tests/ assert_fct_payments_amount_positive.sql (singular test)
slides/ the lecture deck (Slidev)
assignment/ student assignment sub-project (Pagila) — no setup needed
sandbox/ scratch space for the `dbt init` demo (git-ignored)
A graded, end-to-end assignment (staging → intermediate → mart → snapshot → test)
built on this same Pagila data lives in assignment/ — its own
dbt sub-project that runs in the same container and writes to an isolated
dev_assignment schema, so students just start:
docker compose run --rm dbt bash
cd assignment && dbt debugThis repo is already scaffolded, but to show how dbt init bootstraps a brand-new
project, run it in the throwaway sandbox/ folder. sandbox/ is bind-mounted
into the dbt container at /sandbox (a path outside /usr/app, so dbt init
doesn't detect the parent project and refuse to scaffold) and is git-ignored, so
the real dbt_project.yml / profiles.yml are never touched.
docker compose run --rm dbt bash
cd /sandbox # ↔ host ./sandbox — mounted, visible in your editor, git-ignored
export DBT_PROFILES_DIR=/sandbox # the wizard writes its profile HERE, not the real one
dbt init demo_shop # full interactive wizard- The
exportis scoped to that shell; a freshdocker compose runresetsDBT_PROFILES_DIRback to/usr/app. Or justcd /usr/appto keep working on the project. - To point the wizard at the running Pagila DB, enter: host
postgres, port5432, userdbt, passworddbt, dbnameanalytics, schemademo. - Just the folder tree, no wizard / no profile:
dbt init demo_shop --skip-profile-setup.
docker compose down # stop everything (keeps data volume)
docker compose down -v # stop + wipe data (fresh raw layer next boot)profiles.yml reads DBT_HOST (defaults to localhost), so a host install works too:
uv venv --python 3.11 && uv pip install -r requirements.txt
source .venv/bin/activate
export DBT_PROFILES_DIR=$(pwd)
docker compose up -d postgres # just the DB
dbt debug