Snapshots¶

Group 1 - Part 2

Staging and marts models show you the data as it is right now. Snapshots exist for when "right now" isn't enough - when you need to know what a row looked like before it changed. mediapulse_base/snapshots/ is currently empty; you're building the first one.

Exercise¶

Step 1 - Pick a slowly-changing record¶

Step complete

mediapulse_base/models/marts/ads/dim_campaigns.sql sources from stg_ads__campaigns, which has a budget_cents column - a campaign's budget can be renegotiated after launch. Decide: is this a good snapshot candidate, and why does snapshotting it (rather than just re-running dbt build daily) preserve information you'd otherwise lose?

Step 2 - Choose a strategy¶

Step complete

Check whether ads.campaigns has a reliable updated_at-style column. If it does, that points you to one strategy; if it doesn't, that points you to the other.

Hint: timestamp vs check

The timestamp strategy uses a single updated_at column to detect changes; the check strategy compares a specific list of columns (or all of them) between runs when there's no reliable updated-at field. See Add snapshots to your DAG for exactly how each strategy decides a row has changed, and the strategy config reference for the required arguments per strategy.

Step 3 - Build the snapshot¶

Step complete

Create your snapshot in mediapulse_base/snapshots/. It should snapshot the staging model (stg_ads__campaigns), not the raw source directly, so you inherit the renaming/casting already done there. Give it a unique_key and configure your chosen strategy.

Step 4 - Run it, then simulate a change¶

Step complete

Run dbt snapshot, then find a way to simulate a budget change for one campaign (without touching production data destructively), and run it again.

What did dbt add to the table?

Query your snapshot table afterwards. What are dbt_valid_from, dbt_valid_to, and dbt_scd_id for, and how do they let you answer "what was this campaign's budget on a given date"?

Deliverable: your snapshot file, and a query against the resulting table showing both the old and new version of the campaign you changed, with dbt_valid_to populated on the old row.

Extension¶

Step 1 - Snapshot something with a messier change signal¶

Step complete

mediapulse_base/models/staging/streaming/stg_streaming__subscriptions_lifecycle_rec.sql tracks subscriptions with a status column that's known to have inconsistent casing at the source. Design a snapshot for it that won't falsely detect a "change" purely because of a casing difference between runs.

Hint: what check_cols actually compares

If you use the check strategy, it compares raw column values run-over-run - which means normalising a column (e.g. lowercasing status) before the snapshot sees it changes what counts as a real change versus noise. Decide deliberately whether to snapshot the raw source or a cleaned-up staging column, and be able to justify it.

Step 2 - Think about snapshot ownership across the mesh¶

Step complete

mediapulse_analytics has no snapshots/ usage of its own. If a future analytics-only domain needed SCD tracking on data it owns (like the CRM domain), would that snapshot belong in mediapulse_base or mediapulse_analytics? Justify your answer against how dbt Mesh project boundaries are meant to be drawn.

Step 3 - Query across snapshot versions¶

Step complete

Using your Exercise snapshot, write a query that reconstructs what a specific campaign's budget was on a specific past date (not just "what changed" - "what was true on this date").

Deliverable: your normalised-casing snapshot design decision with reasoning, your ownership answer for a hypothetical analytics snapshot, and your point-in-time reconstruction query.

Done?

You've built this project's first snapshot, chosen a change-detection strategy deliberately, and proven you can reconstruct history from it, not just detect that something changed.

Now head to Model governance to fix a real access-level bug this project already has.