Incremental Modeling¶

Group 1 - Part 2

Every model in mediapulse_base is currently a view or a full-rebuild table. That's fine at this data volume, but fct_ad_impressions and fct_streaming_events are exactly the kind of high-volume, append-heavy fact tables that stop being practical to fully rebuild once real data volumes show up.

Exercise¶

Step 1 - Pick your candidate and understand its grain¶

Step complete

Open mediapulse_base/models/marts/ads/fct_ad_impressions.sql. Confirm its grain (one row per what?) and identify a column that only ever increases over time (a good incremental filter candidate).

Step 2 - Convert it to incremental¶

Step complete

Add an incremental config() to the model: materialized='incremental', a unique_key, and an incremental_strategy. Add the is_incremental() guard so a full run still works the first time.

Hint: the moving parts of an incremental model

An incremental model needs a materialization config, a unique_key so dbt knows which rows to update versus insert, and an {% if is_incremental() %} block that's skipped entirely on the first (full) run. See Configure incremental models for the full anatomy, and About incremental strategy for what merge, append, delete+insert, and insert_overwrite each actually do on Snowflake.

Step 3 - Justify your strategy choice¶

Step complete

Explain why you picked merge over append (or vice versa) for this specific table, referencing whether ads.impressions rows can ever be corrected/updated after the fact versus only ever appended.

Step 4 - Prove it's actually incremental¶

Step complete

Run the model twice in a row and compare what changes. What evidence would convince you it processed less data on the second run, not just that it ran without error?

Deliverable: your converted model, your strategy justification, and the evidence from your two-run comparison.

Extension¶

Step 1 - Handle a full-refresh scenario¶

Step complete

Identify a realistic situation where your incremental logic would need --full-refresh to produce correct results (e.g. a schema change to the model, or a change to the allocation logic that needs to be applied retroactively to historical rows). Explain why an ordinary incremental run wouldn't fix it.

Hint: full-refresh is a reset, not a patch

--full-refresh drops and rebuilds the entire incremental table from scratch, ignoring the is_incremental() filter for that run - it's the right tool when the logic changed, not just the data. See Configure incremental models for when dbt recommends reaching for it.

Step 2 - Convert a second, harder candidate¶

Step complete

fct_streaming_events joins impressions-equivalent watch events to two other models (content catalog and subscriptions) without a date filter today. Convert it to incremental, and work out how your is_incremental() filter needs to account for the fact that a late-arriving watch event could reference a watched_at timestamp earlier than your current max, but still needs to be picked up (batched_at may help here - read the model's columns before deciding).

Step 3 - Compare strategies on paper¶

Step complete

Without implementing it, describe what would change about your fct_streaming_events model if Snowflake volumes grew large enough that insert_overwrite (partition-based) made more sense than merge. What would you need to partition on, and what would you give up by switching?

Deliverable: your full-refresh scenario writeup, your converted fct_streaming_events model with late-arrival handling, and your merge-vs-insert_overwrite comparison.

Done?

You've taken mediapulse_base from an all-views-and-tables project to one with a working incremental model, and you can justify every config choice you made along the way.

That's Group 1 complete - well done. You've refreshed staging-to-marts modeling, closed real test and governance gaps, written macros, built a snapshot, and gone incremental, all against a real project rather than a toy one.