Going Further: Optional Deep Dives

This page is optional. Nothing here is required for Week 10's learning goals or the assignment. Use it after you finish the week if you want to keep learning about dbt, analytics engineering, or the broader modern data stack.

The resources below are organized by how much time they take and how they relate to Week 10. Pick what interests you. Do not try to work through all of it during the week itself.

Full courses and tutorials

Large structured resources that take hours to days to work through. Pick one if you want to reinforce this week's material with guided exercises on a different dataset or warehouse.

dbt Fundamentals (free course, dbt Labs): The official self-paced video course covering models, tests, documentation, and lineage. Uses BigQuery, but every concept transfers to the dbt Core + Azure PostgreSQL stack you learned this week.
DataTalks.Club DE Zoomcamp - Module 4: Analytics Engineering: A free 4-week module covering analytics engineering end-to-end on NYC taxi data, the same dataset you used this week. Includes video lectures, readings, and a homework assignment. Do not try to finish it during Week 10; budget for a separate week after the course.
Start Data Engineering - dbt Tutorial: End-to-end walkthrough from install to docs, aimed at data engineers who already know SQL.

Deep dives on specific topics

Longer-form reading that goes beyond what the Week 10 chapters introduced.

dbt-utils package: A collection of community macros covering surrogate keys, pivots, date spines, and common test patterns. Most real dbt projects install this on day one.
dbt packages hub: Browse the full catalog of community packages for specific data sources and patterns.
dbt Semantic Layer overview: Metrics-as-code, the next layer beyond dbt models. Only relevant once you have a mature dbt project with multiple BI consumers.
SQLMesh: A newer alternative to dbt focused on virtual data environments and column-level lineage. Worth knowing about but not a replacement for learning dbt first.
dbt Fusion engine announcement (May 2025): The Rust-based rewrite of the dbt engine. See the History chapter for context on what this means for dbt Core.

Concepts covered later in the track

A few dbt features sit naturally next to the Week 10 material but need an environment the Week 10 dataset cannot provide. They are introduced and practiced in later weeks of the Data Track, where the scale and the schedule make them meaningful. Short previews here so you recognize the names if you run into them before then.

Incremental models

The fourth materialization (alongside view, table, and ephemeral). An incremental model rebuilds only the new rows instead of rewriting the whole table on every run. The pattern is mentioned in Chapter 4 and practiced later in the track on a 100M-row slice where a full table rebuild is no longer cheap.

The graduation rule is worth remembering even without the code: start every mart as a table. Switch to incremental only when the full rebuild takes longer than the freshness your consumers need: roughly, when the dataset crosses tens of millions of rows and the pipeline runs often enough that full rebuilds stack up.

<aside> 🤓 Curious Geek: why Week 10 uses one month and not the full 3 billion rows

The TLC's public trip-record dataset is 3+ billion rows / ~267 GB of CSVs covering 2009 through today: yellow, green, and for-hire vehicles. At that scale a table rebuild takes tens of minutes on a typical warehouse and incremental stops being optional. We pinned Week 10 to January 2024 green taxi only (~57K rows) so every dbt run finishes in seconds. In a real project you start with one month for development, lock the logic, then switch to incremental and backfill. Two years of yellow taxi (~80-100M rows) is the rough threshold.

</aside>

Reference material if you want to read ahead:

Configure incremental models (dbt docs): the is_incremental() macro, {{ this }}, unique_key, and the merge / delete+insert / insert_overwrite strategies.
Incremental strategies explained (dbt docs): how each strategy handles updates, deletes, and late-arriving rows; the classic > vs >= bug that silently double-counts.

Source freshness

dbt has a freshness: config you attach to a source table: "the most recent row must have been loaded within the last N hours, otherwise warn or fail." Running dbt source freshness before the rest of the pipeline catches a stalled ingestion job before downstream models silently rebuild against stale data.

The feature only earns its keep when the ingestion is supposed to run on a schedule and sometimes does not. Week 10's January 2024 slice is a static snapshot, so dbt source freshness would always error here: not because anything broke, but because the dataset is permanently old. Freshness is practiced later in the track, where your pipeline first has a real schedule (ingestion → dbt source freshness → dbt build). When the ingestion step fails silently (network blip, auth expired, rate-limited), freshness is the gate that stops the pipeline before the transform ships yesterday's numbers as today's.

Source freshness config (dbt docs): reference for loaded_at_field, warn_after, error_after.
Test source freshness (dbt docs): short writeup of the common patterns teams use it for.

Ephemeral materialization

dbt's fifth materialization (view and table are the two Week 10 requires; incremental and snapshot are previewed on this page). An ephemeral model is never created in the database: dbt inlines its SQL as a CTE inside every downstream model that references it. The realistic case is shared helper logic you do not want analysts to discover or query directly.

-- models/intermediate/int_trips_cleaned.sql
{{ config(materialized='ephemeral') }}

select * from {{ ref('stg_trips') }}
where fare_amount > 0 and trip_distance > 0

Why most projects skip it. You cannot select * from an ephemeral model when debugging. Errors surface in the consuming model rather than at the ephemeral source. The inlined SQL bloats compiled queries on any project with more than a handful of refs. A view gets you the same "no storage cost" property with none of these drawbacks. Reach for ephemeral only when hiding the relation from the schema is the whole point.

Ephemeral materialization (dbt docs): config options and the full list of caveats.

Snapshots: tracking rows that change over time

Snapshots (SCD Type 2 row history) are a genuinely useful dbt feature, but they are one step past what Week 10 requires. Unlike incremental models, you can practice snapshots on the Week 10 dataset (the exercise below builds a personal mutable dimension), so if you have finished the week and want to go deeper, this is a good next stop. Nothing here is needed for the assignment.

Most marts assume new rows are appended and old rows stay unchanged: good enough for event-shaped data. But some dimensional data changes in place: a customer's address updates, a taxi zone is redrawn, a product's price changes. Overwriting the old value loses history that queries asking "what did this row look like on date X?" still need. A snapshot keeps both.

A snapshot materializes a table with two extra columns: dbt_valid_from and dbt_valid_to: that record when each version of a row was current. This is the classic Kimball Slowly Changing Dimension Type 2 pattern: every time a row changes, the old version gets a dbt_valid_to timestamp and a new row is added with dbt_valid_to = NULL.

The pattern is older than dbt by two decades, and worth a brief detour:

<aside> 🤓 Curious Geek: SCD Type 2 is older than the warehouse

Slowly Changing Dimension Type 2 was named by Ralph Kimball in 1996 when the data warehouse was a mainframe-era idea. The "Type 2" in the name is because Kimball numbered the patterns: Type 1 overwrites, Type 2 keeps history with new rows, Type 3 keeps the previous value in a second column. Type 2 won because it answered the most common analytics question ("what did this look like on date X?") without losing the current state. dbt's contribution is not the pattern; it is making the pattern declarative (write a config block, not a 200-line MERGE statement) and version-controllable.

</aside>

Concrete taxi example. The TLC revises its published zone lookup every few years: zones get added, boundaries get redrawn, service_zone classifications flip. The TLC just overwrites the file; there is no version or changelog. Without a snapshot, fct_trips rebuilt after a revision silently reports different borough totals for the same historical trips. Snapshotting raw_zones captures each revision as a new dated row, so you can always answer "which zone did pickup_location_id belong to on the day the trip happened?":

-- snapshots/zones_snapshot.sql
{% snapshot zones_snapshot %}

{{ config(
    target_schema='snapshots',
    unique_key='location_id',
    strategy='check',
    check_cols=['borough', 'zone', 'service_zone']
) }}

select * from {{ source('nyc_taxi', 'raw_zones') }}

{% endsnapshot %}

To use the historical zone for a given trip, join against the snapshot on location_id and on the trip's pickup_datetime falling between dbt_valid_from and coalesce(dbt_valid_to, now()). For the January 2024 dataset this changes no numbers: nothing has been revised since the trips landed: but the pattern matters the moment the next revision ships.

A quick detour: seeds: small reference CSVs managed in the project

Before the snapshot exercise, one dbt concept to name: a seed. A seed is a small CSV file in your project's seeds/ folder that dbt loads into the warehouse as a table. The command is dbt seed; dbt build also runs seeds alongside models.

The line between a seed and a source is a matter of ownership, not size:

A source is data another system writes (ingestion pipelines, application databases, third-party exports). dbt reads but never writes it. raw_trips is a source.
A seed is data your dbt project owns. It lives in version control next to the SQL, gets code-reviewed like any other change, and is recreated in every warehouse you deploy to. Typical uses: country-code → region lookups, holiday calendars, a small allow-list of approved product SKUs, the per-student sandbox you are about to build.

Keep seeds small (under a few thousand rows). Large CSVs belong in a real ingestion pipeline: not because dbt refuses, but because dbt seed re-inserts every row on every run.

Exercising snapshots safely: `mutable_zones` in your own schema

There is an obvious pedagogical problem: to see a snapshot capture a change, something has to change. But nyc_taxi.raw_zones is shared: you cannot mutate it to generate history without breaking every classmate's project.

The fix is a per-student seed. Every student has a personal dev_<your_name> schema (from Personal dev schemas); that is a sandbox you own. A 10-row subset of the TLC zone lookup is published as a gist: mutable_zones_seed.csv. Download it into your project under seeds/mutable_zones.csv and load it:

curl -L <https://gist.githubusercontent.com/lassebenni/7536be5cff0e237302f30551619288e1/raw/mutable_zones_seed.csv> \
  -o seeds/mutable_zones.csv
dbt seed --select mutable_zones

The result is dev_<your_name>.mutable_zones: yours to mutate freely. Now you can point a snapshot at it:

-- snapshots/mutable_zones_snapshot.sql
{% snapshot mutable_zones_snapshot %}

{{ config(
    target_schema=target.schema ~ '_snapshots',
    unique_key='location_id',
    strategy='check',
    check_cols=['borough', 'zone', 'service_zone']
) }}

select * from {{ ref('mutable_zones') }}

{% endsnapshot %}

The target_schema=target.schema ~ '_snapshots' expression uses your profile's schema: value (your personal dev schema) and appends _snapshots, so the snapshot table lives at dev_<your_name>_snapshots.mutable_zones_snapshot. Per-student, same pattern as your model schema.

Run the snapshot to capture the initial state:

dbt snapshot

1 of 1 START snapshot dev_<your_name>_snapshots.mutable_zones_snapshot ......... [RUN]
1 of 1 OK snapshotted dev_<your_name>_snapshots.mutable_zones_snapshot ......... [SELECT 10 in 0.55s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1

The SELECT 10 in the status line is dbt telling you it captured 10 rows: the initial state. Now mutate a row in psql and re-snapshot to watch the history grow:

-- imagine the TLC reclassifies zone 132 (JFK) from 'Airports' to 'Yellow Zone'
UPDATE dev_<your_name>.mutable_zones
SET service_zone = 'Yellow Zone'
WHERE location_id = 132;

dbt snapshot

1 of 1 OK snapshotted dev_<your_name>_snapshots.mutable_zones_snapshot ......... [INSERT 0 1 in 0.87s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1

Note the status changed from SELECT 10 on the initial run to INSERT 0 1 on this one: dbt detected one row whose check_cols changed and inserted exactly one new history row (the snapshot table now has 11 rows, up from 10). Query the snapshot:

SELECT location_id, service_zone, dbt_valid_from, dbt_valid_to
FROM dev_<your_name>_snapshots.mutable_zones_snapshot
WHERE location_id = 132
ORDER BY dbt_valid_from;

You will see two rows: the original 'Airports' version with a dbt_valid_to timestamp, and the new 'Yellow Zone' version with dbt_valid_to IS NULL. That is the SCD Type 2 pattern working end to end. Revert the change (UPDATE ... SET service_zone = 'Airports' WHERE location_id = 132;) and run dbt snapshot again to add a third history row when you are done exploring.

Two strategies to know:

timestamp strategy compares an updated_at column on the source row. If the source row's timestamp is newer than the snapshot's latest version, dbt records a new version. Use this when the source reliably writes an update timestamp.
check strategy (above) compares the specified check_cols between source and snapshot. If any of the listed columns changed, record a new version. Use this when the source has no update timestamp: which is the common case.

Running dbt snapshot on a schedule (hourly, daily) builds up the history. Querying the snapshot with where dbt_valid_to is null gives you the current rows; without that filter, you get every historical version.

Rule of thumb: reach for a snapshot when you need to answer "what did this row look like on date X?" Skip it for data that is purely additive (events, log lines): an append-only table handles those. Skip it for high-volume mutable data (user sessions, IoT readings): those want event sourcing in a different tool.

Snapshots (dbt docs): SCD Type 2 tracking with timestamp and check strategies.

Developer tooling

dbt Power User (VS Code extension)

The dbt Power User extension turns VS Code into an IDE that understands your dbt project. Not a dbt feature, not required, not part of the Week 10 assignment: but most professional dbt Core users install it on day one. It is the closest free equivalent to the paid dbt Cloud IDE, and if the company you join runs dbt Core (most do) this is probably the setup you will see on a senior engineer's screen.