Run It Twice and Count the Rows
An incremental model is a table plus a promise about which rows the last run was allowed to touch. Everything that goes wrong is a row outside that promise — never selected, inserted twice, or no longer produced upstream.
fct_listens has been a table since Chapter 1: 5 rows, rebuilt from scratch in 7.6 seconds every run. Today it converts to incremental. Run the same model at 12:30 and again at 15:30 below and watch what actually changes — not a description of it.
Everything that goes wrong in this chapter is one of exactly three shapes: a row the filter never selected, a row the strategy inserted twice, or a row the source stopped producing. Every section maps to one of those three.
create or replace transient table analytics.dbt_vijay.fct_listens
as (
select
l.listen_id,
l.user_id,
l.episode_id,
l.show_id,
l.listened_at,
cast(l.listened_at as date) as listen_date,
l.ms_played,
round(l.ms_played * 100.0 / l.duration_ms, 1) as pct_completed,
l.ms_played >= l.duration_ms * 0.9 as is_completed
from analytics.dbt_vijay.int_listens_enriched l
join analytics.dbt_vijay.dim_show s using (show_id)
);- $ dbt run -s fct_listens --full-refresh
- 12:30:01 1 of 1 START sql incremental model dbt_vijay.fct_listens ..... [RUN]
- # is_incremental(): False — --full-refresh was passed on this invocation
- 12:30:04 1 of 1 OK created sql incremental model dbt_vijay.fct_listens . [SUCCESS 1 in 7.60s]7.60s
- Completed successfully
- # 9 rows in fct_listens after this run
An incremental model is a table plus a promise about which rows the last run was allowed to touch. Everything that goes wrong is a row outside that promise — never selected, inserted twice, or no longer produced upstream.
An incremental build is a filter plus a write strategy, and correctness lives in the pair, not in either one alone — a perfect filter with the wrong strategy still duplicates rows, and the right strategy applied to the wrong filter still loses them. This chapter builds the pair, not just the two halves.
- is_incremental()
- True only when three documented conditions hold together — materialized as incremental, the target relation already exists, and --full-refresh was not passed — plus one implicit rule this chapter states out loud: the existing object must be a table, not a view.
- The column-set ruling
- This chapter's 9-column select — through int_listens_enriched and dim_show, plus episode_id and listened_at added for the composite key and the lookback filter — is the authoritative shape of fct_listens; the callout below has the full lineage history.
- listened_at
- The event-time column this project settled on in Chapters 6–8 (Chapter 1’s started_at is superseded). Every filter in this chapter keys on it.
- The too-early gate
- Three conjunctive conditions, not a vibe: append-mostly, a full rebuild provably approaching the schedule window, and you can name the event-time column and its lateness distribution. Fail any one and you are not ready — Section 9 scores five real candidates against it.
Reasoning about "going incremental" from the dev sandbox's build time. fct_listens builds in 7.6 seconds over 5 rows in dev — nowhere near any schedule window. The conversion this chapter makes is justified by production volume (millions of rows, a rebuild approaching a 6-hour nightly window), not by the dev transcript. The asymmetry that makes this dangerous: going incremental is cheap to undo; the expensive part is that nobody ever notices they should.
Before touching incremental_strategy, name the filter and the strategy as two separate decisions, then ask what the pair does together — not what each does alone.
Read a green build as "the rows the filter selected were written correctly," never as "the table is correct" — those are different claims.
Keep the three failure shapes (never selected / inserted twice / no longer produced) in mind as a checklist for any incremental model you inherit, not just this one.
Chapter 4 introduced incremental at the concept level and quoted the honest limit: incremental buys build time, not freshness. This chapter is where that limit gets mechanics — the filter, the lookback, and the four write strategies (plus microbatch, the config-declared fifth) that decide what the write actually does.
The concept this whole chapter answers to — a repeat has no extra effect — lives in the Data Pipeline KB, not here: idempotent processing, keying every write to a stable identity so a re-run is a no-op instead of a duplicate. This chapter is the dbt-specific question that concept raises: which incremental_strategy actually gives you that property, and which one silently does not. Chapter 4 quoted the honest limit and parked the correctness risk for later — this is the chapter that manages it.
Idempotency, retries, and exactly-once (Data Pipeline KB)Chapter 1 introduced fct_listens reading its grandparents directly — ref('stg_wavelength__listens') / ref('stg_wavelength__episodes') — and Chapter 8's unit test mocks those same grandparents. Chapters 3 and 4 shipped the real lineage this chapter actually uses, through ref('int_listens_enriched') + ref('dim_show'); porting Chapter 8's given blocks forward means moving them to those two refs. Chapter 3/Chapter 4's own "exact contents" snippet for this file is abridged — a 4-column body captioned "unchanged from Chapter 3" — while this chapter's 9-column select is the one that matches Chapter 1's canonical result and Chapter 8's "7 output columns"; treat this chapter's column set as authoritative and that earlier snippet as shorthand, not a second, conflicting "exact contents" claim. The published set also grows by two here — episode_id and listened_at, both selected straight from int_listens_enriched — because this chapter's composite unique_key and its high-water-mark filter both need them sitting in the target, not just upstream.
An incremental build is a filter plus a write strategy; every failure in this chapter is a row that fell outside the promise the pair made.