Content
dbt Setup for Azure PostgreSQL
Career relevance: Week 10 in the NL data job market
History of Analytics Engineering
Going Further: Optional Deep Dives
Each exercise targets a single skill from the content chapters. They live as branches in the reference repo, each branching off the finished project (ch7-practice) so dbt actually runs: no copy-pasting loose files into a project that isn't there. Clone the repo once, git switch to an exercise branch, read its EXERCISE.md, do the work, then compare against the matching -solution branch.
git clone <https://github.com/lassebenni/nyc-taxi-dbt-reference.git> nyc_taxi
cd nyc_taxi
cp profiles.yml.example profiles.yml # set your personal Week 9 login + schema
git switch exercise-macros # then read EXERCISE.md
Each exercise starts from the complete project, so they are independent: do them in any order. The EXERCISE.md on each branch has the full step-by-step; the summaries below tell you what the branch is for and how to know you are done.
<aside> 📝 These exercises are optional but strongly recommended.
</aside>
By the end of this chapter, you should have:
stg_trips model with tip_pct, fare_per_mile, and payment_type_label columns, plus a safe_divide macro.relationships set to warn for the known zone gap.tests/ folder that catches a business-rule violation.<aside>
📦 Reference repo: git switch exercise-macros, then follow EXERCISE.md. Compare your work with git diff exercise-macros exercise-macros-solution.
</aside>
stg_trips computes two ratio columns, tip_pct and fare_per_mile, and both must guard against a zero denominator. That repeated guard is exactly what a macro removes. The branch already calls {{ safe_divide(...) }} for both columns, but the macro is unimplemented, so dbt compile fails until you write it.
Success criteria: tip_pct and fare_per_mile are non-null for rows with non-zero denominators (NULL otherwise), and the compiled SQL contains NULLIF(...).
<aside>
📦 Reference repo: git switch exercise-singular-test, then follow EXERCISE.md. Compare with git diff exercise-singular-test exercise-singular-test-solution.
</aside>
Singular tests are plain .sql files that return the bad rows: introduced in dbt Tests but not drilled. You write one that returns every stg_trips row with a negative fare_amount. It fails with FAIL 182 against the January 2024 data (182 real negative fares), then passes once you filter them out in stg_trips.
Success criteria: the test reports FAIL 182 before the fix and PASS after, and you can state in one sentence why a singular test is the right tool here rather than a generic YAML test.
ref() (SQL and Jinja Templating)<aside>
📦 Reference repo: git switch exercise-debug-ref, then follow EXERCISE.md. Compare with git diff exercise-debug-ref exercise-debug-ref-solution.
</aside>
When a {{ ref() }} is misspelled, dbt fails at compile time, not run time, and the fix lives in a different file than the error points at. The branch ships fct_trips.sql with one broken ref(). Drill the compile-to-find-the-typo workflow now, before you meet it under pressure in the assignment.
Success criteria: you spotted the typo from the error message alone, and can explain why dbt reports it at compile time rather than run time.
<aside>
📦 Reference repo: git switch exercise-column-propagation, then follow EXERCISE.md. Compare with git diff exercise-column-propagation exercise-column-propagation-solution.
</aside>
Adding a column to a staging model and making it appear in the mart is the single most common dbt change in a real job. Because fct_trips uses an explicit column list, a new trip_duration_minutes in stg_trips does not reach the mart until you add it there too and rebuild with the + prefix.
Success criteria: the column appears in both stg_trips and fct_trips, and you can state why the mart query failed before you edited the mart, and what --select +fct_trips does that plain --select fct_trips would not.
<aside>
📦 Reference repo: git switch exercise-generic-tests, then follow EXERCISE.md. Compare with git diff exercise-generic-tests exercise-generic-tests-solution.
</aside>
Generic tests (not_null, unique, accepted_values, relationships) are the highest-frequency test type in real dbt projects, and the one an interviewer is most likely to ask you to write on the spot. The branch ships the staging schema files with the column-level tests removed. You attach all four, then read a real failure: the relationships test flags the trips whose pickup zone has no match, a source-data gap you resolve by setting severity: warn.
Success criteria: you can state in one sentence what each of the four generic tests checks, and why warn (not error) is reasonable for a known source-data gap.
These do not require running dbt. Write down your answers, then check them against the chapter text or the collapsible answers in each chapter's Knowledge Check section.
fct_trips were a plain SQL CREATE VIEW in Postgres instead of a dbt model, at which stage (write / save / query) would the same typo surface? Why does dbt compile its SQL separately from executing it?stg_trips but querying it from fct_trips still failed until you also edited the mart. What does that tell you about how dbt propagates changes (and about why --select +fct_trips is the right pattern on a real team)?accepted_values test on payment_type allowing [1, 2, 3, 4, 5, 6]. A trip with payment_type = 6 (voided) appears in the source. What happens when you run dbt test, and what severity would you set on the relationships test that fails on real data?Next up: Assignment, where you apply the full Week 10 workflow on a new slice of the taxi dataset and submit a working dbt project with models, tests, and generated docs.
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.