Study notes and hands-on dbt project based on Analytics Engineering with SQL and dbt by Rui Machado & Hélder Russa (O'Reilly, 2023).
This repository is an opinionated, runnable dbt project — not a rewrite of the book. Theory lives in the chapter READMEs; the real value is in the working models, tests, and data pipeline you can run locally.
make install # Install Python dependencies (dbt-core, dbt-postgres)
make up # Start PostgreSQL via Docker
make dbt-seed # Load sample data (Jaffle Shop + Omnichannel)
make dbt-run # Run all models (staging → intermediate → marts)
make dbt-test # Run all tests (generic + singular)
make dbt-docs # Generate and serve dbt documentationOr run the full pipeline in one command:
make dbt-build # seed + run + test| # | Chapter | Type | Status |
|---|---|---|---|
| 1 | Analytics Engineering | Theory | Done |
| 2 | Data Modeling for Analytics | Theory | Done |
| 3 | SQL for Analytics | Theory | Done |
| 4 | Data Transformation with dbt | Hands-on | Done |
| 5 | dbt Advanced Topics | Hands-on | Done |
| 6 | End-to-End Use Case | Hands-on | Done |
├── models/
│ ├── staging/ # 1:1 with sources, light transforms
│ │ ├── jaffle_shop/ # Ch4: customers, orders
│ │ ├── stripe/ # Ch4: payments
│ │ └── omnichannel/ # Ch6: channels, products, purchases, visits
│ ├── intermediate/ # Ch4: payment aggregations
│ └── marts/
│ ├── core/ # Ch4: dim_customers, fct_orders
│ └── omnichannel/ # Ch6: star schema (3 dims + 2 facts)
├── seeds/ # CSV sample data for all chapters
├── macros/ # Ch5: reusable Jinja macros
├── snapshots/ # Ch5: SCD type 2 order tracking
├── analyses/ # Ad hoc analytical queries
├── tests/ # Singular dbt tests
├── docs/ # Chapter READMEs + book PDF (gitignored)
├── dbt_project.yml # dbt configuration
├── packages.yml # dbt packages (dbt_utils)
├── profiles.yml # Database connection (local PostgreSQL)
├── docker-compose.yml # PostgreSQL 16
├── Makefile # Automation commands
└── pyproject.toml # Poetry dependencies
| Concept | Chapter | Models |
|---|---|---|
| Staging / Intermediate / Marts layers | 4 | stg_*, int_*, dim_*, fct_* |
Sources and {{ ref() }} |
4 | All models |
| Generic + singular tests | 4, 6 | YAML configs + tests/ |
| Seeds (CSV → tables) | 4, 6 | seeds/*.csv |
| Documentation (YAML + doc blocks) | 4 | _core_docs.md |
| Snapshots (SCD type 2) | 5 | snap_orders.sql |
| Jinja macros | 5 | macros/ |
dbt packages (dbt_utils) |
5, 6 | Surrogate keys in omnichannel dims |
| Star schema (dims + facts) | 6 | models/marts/omnichannel/ |
| Surrogate keys | 6 | dim_omni_* |
| Naming conventions (sk_, nk_, mtr_, dsc_) | 6 | Omnichannel models |
| Repository | Topic |
|---|---|
| data-algorithms-with-pyspark | Data algorithms with PySpark |
| learning-python | Python fundamentals to advanced |
| postgres-notes | PostgreSQL notes |
| mysql-notes | MySQL notes |
Place the book PDF in docs/ (gitignored):
cp ~/path/to/"Analytics Engineering with SQL and dbt (2023).pdf" docs/MIT