🎒 Assignment: Borough Stats

Build a Daily Borough Stats Mart with dbt. The chapter hands-on walked you through building fct_trips: a one-row-per-trip mart: step by step. The practice exercises drilled specific skills (macros, singular tests, debugging refs, propagating column changes) against that same project.

This assignment asks you to build a second, independent dbt project end to end, against the same raw_trips and raw_zones source data, but producing a mart at a different grain: one row per pickup_borough per date. You will redo every step (sources → staging → mart → tests → docs) on your own, without the chapter's line-by-line guidance. The goal is to prove you can take a business question and deliver a versioned, tested, documented dbt project that answers it.

By the end of this assignment, you should be able to:

Configure a dbt project's connection to a shared warehouse with a personal dev schema, keeping secrets out of git.
Translate a business question ("how many trips and how much revenue does each borough generate per day?") into a staging → mart layer structure.
Choose appropriate materializations, tests, and column-level documentation.
Submit a reproducible git repo another engineer could clone, dbt build, and understand without asking you questions.

The business question

Your stakeholder (imagine a product manager for NYC Green Taxi) wants a dashboard showing, for each day in the January 2024 data:

Trip volume per borough: how many trips were picked up in each borough? (Heads up: the TLC zone data contains a few values beyond the five famous NYC boroughs: EWR and Unknown are legitimate zone labels, and you will also see a dirty NaN borough. Do not hardcode a borough list, and expect NaN to appear as its own row.)
Revenue per borough: total fare_amount per borough per day.
Tipping behaviour per borough: average tip_pct per borough per day.
Distance profile per borough: average trip_distance per borough per day.

Grain of the mart: one row per (pickup_borough, pickup_date).

The load covers January 2024, but a handful of trips are timestamped in the final hours of December 31, 2023 and will land in your mart with a 2023-12-31 date. That is expected, not a bug: do not filter them out unless a task tells you to.

<aside> 💡 This is a separate project from your Week 10 chapter work. The scaffold gives you a fresh nyc_taxi_borough_daily/ project; you reuse the connection settings from Chapter 2, but the models, tests, and docs are yours to write.

</aside>

Task 1: Set up the project and connect

The Week 10 assignment repo lives in the HYF organization. Your cohort uses a fork in the HackYourAssignment organization: your teacher will share the link at the start of the week. Fork your cohort's repo into your own GitHub account and clone your fork. The scaffold is a ready-to-run dbt project: it ships dbt_project.yml, packages.yml, TODO stubs for every model and test, templates for reports/answers.md and AI_ASSIST.md, and an autograder you can run locally with bash .hyf/test.sh. Your job is to fill in the stubs, not to scaffold the project.

Copy profiles.yml.example to a project-local profiles.yml pointing at the same shared Azure PostgreSQL instance you used in Chapter 2, and set your personal dev_<your_name> schema.

<aside> ⚠️ Use the schema you already own. Your shared-DB login can only build into your own dev_<your_name> schema, the one you used for the Week 10 chapters: it does not have permission to create new schemas. Reusing it is fine: the assignment's stg_trips and stg_zones replace the chapter versions (identical staging models), and the new fct_daily_borough_stats mart is added alongside.

</aside>

Run dbt debug and confirm All checks passed!.

Task 2: Declare sources

Create models/staging/_sources.yml that declares raw_trips and raw_zones in the nyc_taxi schema. This is identical to Define sources for the Week 9 raw tables: the point is that you write it again from scratch, not copy-paste.

Task 3: Build staging models

Create two staging models:

stg_trips.sql: one row per trip. Include the columns you will need for the mart: pickup_datetime, pickup_location_id, fare_amount, tip_amount, trip_distance, plus a derived tip_pct using your own safe_divide macro. Copy macros/safe_divide.sql from your Week 10 chapter project (or from the reference repo's solution branch if you don't have it): macros are reusable infrastructure, not the modelling logic you are practising here, so copying is fine. Drop rows where pickup_location_id is null or fare_amount < 0.
stg_zones.sql: one row per TLC zone. Expose at least location_id and borough.

Materialize both as view.

Task 4: Build the mart

Create models/marts/fct_daily_borough_stats.sql materialized as table, grain one row per (pickup_borough, pickup_date). Join stg_trips to stg_zones on pickup_location_id, then aggregate. Columns to expose:

<aside> ⚠️ Use an INNER JOIN, not a LEFT JOIN. A few trips carry a pickup_location_id that has no matching zone (for example 999). A LEFT JOIN keeps them with a NULL borough, which breaks your not_null test on pickup_borough. An INNER JOIN drops those few unmatched trips so pickup_borough can serve as part of the mart's primary key. The real Unknown borough is a valid zone label and stays in.

</aside>

Column	Type	Definition
`pickup_borough`	text	Borough from `stg_zones.borough`
`pickup_date`	date	`pickup_datetime::date`
`trip_count`	bigint	`count(*)`
`total_fare`	numeric	`sum(fare_amount)`
`avg_tip_pct`	numeric	`avg(tip_pct)`
`avg_trip_distance`	numeric	`avg(trip_distance)`

<aside> ⌨️ Sanity check: after dbt run --select +fct_daily_borough_stats, query your mart and confirm Manhattan has the highest trip_count on most days. If it does not, you likely joined on the wrong key or filtered too aggressively in staging.

</aside>

Task 5: Add tests

Attach at least the following to your staging and mart models via YAML schema files:

Staging: not_null on every column used as a join or group-by key.
Mart: a compound uniqueness test on (pickup_borough, pickup_date) using dbt_utils.unique_combination_of_columns. This is the mart's primary key: if it fails, your GROUP BY is wrong.
At least one singular test in tests/ that expresses a business rule your generic tests cannot. A good candidate: flag rows where avg_tip_pct > 1 (tip exceeded the fare) at severity: warn. The test is expected to return a small number of rows for the Unknown borough: you will explain this in Task 7 Q3.

Install dbt_utils via packages.yml + dbt deps before running the compound uniqueness test.

Run dbt build (not dbt run + dbt test separately) and ensure the summary ends with ERROR=0. Warnings are acceptable.

Task 6: Document the project

Add a description: on every column of fct_daily_borough_stats in its YAML schema file. Descriptions must explain what the column means and what its units are, not restate the column name.
Add a model-level description: with the mart's grain, source lineage, and known caveats (rows dropped in staging, any WARN-severity tests, etc.).
Run dbt docs generate and dbt docs serve. Take a screenshot of the lineage graph showing raw_trips + raw_zones → stg_* → fct_daily_borough_stats, and save it as docs/lineage.png in your repo.

<aside> 💡 Codespace users: dbt docs serve opens localhost:8080. In a Codespace, forward the port first: in VS Code, open the Ports panel (Terminal → Ports), click Forward a Port, enter 8080, then open the forwarded URL. You can then screenshot the lineage graph in your browser.

</aside>

Task 7: Answer the business questions

Query your mart to answer the stakeholder's questions. Save the queries and results in reports/answers.md:

Which borough had the highest total total_fare across the whole loaded dataset?
Which day had the highest overall trip_count (sum across all boroughs)?
What was the highest avg_tip_pct seen for any (borough, day) combination, and on which borough/date?
What was the median daily trip_count for Manhattan vs Brooklyn? Hint: Postgres has an ordered-set aggregate for median: percentile_cont. Filter by borough and apply it to trip_count.

Each answer must include: the SQL you ran, the result, and one sentence interpreting it.

Task 8: AI Assist Report

Use an LLM at least once during this assignment (for debugging, understanding an error, or reviewing your SQL) and document that use in AI_ASSIST.md. Describe:

One place you used an LLM during this assignment (a specific debugging question, a SQL explanation, or a review of your YAML).
The exact prompt you sent.
What the LLM returned.
What you changed, kept, or discarded after reviewing its response.

<aside> ⚠️ Do not paste any real data into an LLM: the NYC TLC dataset is public so sample rows are safe here, but the discipline of scrubbing data is what you must practise.

</aside>

Technical requirements

Project structure (as an example):

nyc_taxi_borough_daily/
├── .gitignore                    # must exclude profiles.yml
├── dbt_project.yml
├── packages.yml
├── profiles.yml.example          # commit a sanitized copy (no password)
├── macros/
│   └── safe_divide.sql
├── models/
│   ├── staging/
│   │   ├── _sources.yml
│   │   ├── _stg_trips.yml
│   │   ├── _stg_zones.yml
│   │   ├── stg_trips.sql
│   │   └── stg_zones.sql
│   └── marts/
│       ├── _fct_daily_borough_stats.yml
│       └── fct_daily_borough_stats.sql
├── tests/
│   └── assert_avg_tip_pct_within_bounds.sql
├── docs/
│   └── lineage.png
├── reports/
│   └── answers.md
└── AI_ASSIST.md

The final dbt build must exit 0 (ERROR=0 in the summary line). Any WARN-severity failures must be called out in reports/answers.md with one sentence explaining whether they represent a real data issue or a known limitation.
No secrets committed. profiles.yml must be git-ignored; commit profiles.yml.example with password: "{{ env_var('PG_PASSWORD') }}" instead.
Column descriptions in the mart's YAML must go past restating the column name.

Deliverables

The dbt project folder in a git repo, pushed to your GitHub
A lineage graph screenshot at docs/lineage.png
reports/answers.md with the four business-question answers
AI_ASSIST.md

How you will be evaluated

Your mentor reviews your PR along these dimensions. No point values here: the review is feedback-first, not a score.

Correctness: dbt build runs green end to end, and the mart's numbers hold up against the raw data.
Modelling: staging stays thin (rename, cast, filter); the business logic lives in the mart, and the grain is stated explicitly.