Week 10 - SQL Transformations with dbt

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.

Introduction to dbt Core

dbt Setup for Azure PostgreSQL

SQL and Jinja Templating

Materializations & Layers

dbt Tests

Docs & Extras

Practice

Week 10 Assignment: Build a Daily Borough Stats Mart with dbt

Week 10 Gotchas & Pitfalls

Week 10 Lesson Plan (Teachers)

Week 10 - SQL Transformations with dbt

Welcome to Week 10! This week introduces dbt (data build tool), the industry standard for SQL-based data transformations. You will set up dbt for Azure PostgreSQL, learn how Jinja templating extends SQL, write tests to ensure data quality, and build your first mart that will power next week's dashboard.

You continue working with the NYC taxi dataset from Week 9 (raw_trips and raw_zones already loaded in Azure PostgreSQL).

Learning goals

• Install and configure dbt Core to work with Azure PostgreSQL
• Understand the dbt project structure: models, sources, profiles, and macros
• Write SQL models enhanced with Jinja templating for DRY, reusable transformations
• Explain materializations (view, table, incremental) and when to use each
• Build a staging/intermediate/mart layer structure with a mart that powers a dashboard
• Implement data tests (not_null, unique, relationships) and unit tests for transformation logic
• Generate and navigate dbt documentation to understand data lineage

Prerequisites

• Completed Week 9 with raw_trips and raw_zones loaded into the shared Azure PostgreSQL instance
• PG_PASSWORD environment variable and access to your personal dev_<your_name> schema
• uv installed (used to install dbt-core + dbt-postgres)

Chapters

1. Introduction to dbt Core
2. dbt Setup for Azure PostgreSQL
3. SQL and Jinja Templating
4. Materializations & Layers
5. dbt Tests
6. Docs & Extras
7. Practice
8. Assignment
9. Gotchas & Pitfalls

• Teachers

Lesson plan

Assignment rubric

• Supplementary

Career relevance: Week 10 in the NL data job market

Glossary

History of Analytics Engineering and dbt

Going Further: Optional Deep Dives