Convert a Full-Refresh Model to Incremental with Proof of Parity

This playbook uses an assistant to propose the incremental strategy and generate the code, then proves correctness with a full-refresh-vs-incremental parity check before the old behavior is retired.

When to reach for this

Use this when a model's runtime or scanned bytes have grown with table size, but each run only genuinely changes recent partitions. Best applied to fact-style models with a reliable event timestamp or updated-at column.

Triggers:

• •
  A daily model that scans a year of history to update yesterday.
• •
  Warehouse cost review flags a model among the top spenders.
• •
  Runtime SLA misses on models whose source data is append-mostly.

The workflow

1. 1.

   Gather the evidence the strategy depends on. Before prompting, answer with queries, not intuition: What is the model's grain and unique key? Which column marks recency (event_ts, updated_at)? How late does data arrive?

   Copy

   -- how late do records really arrive?
   SELECT CAST(loaded_at AS DATE) - CAST(event_ts AS DATE) AS days_late,
          COUNT(*) AS n
   FROM stg_events
   GROUP BY 1
   ORDER BY 1;
   

   The tail of this distribution sets your lookback window. Guessing it is how incremental models silently drop late rows.

2. 2.

   Prompt with the evidence, ask for strategy before code.

   Copy

   Convert the dbt model below from full refresh to incremental.
   
   Facts (measured, not assumed):
   - Grain: one row per event_id. unique_key candidate: event_id.
   - Recency column: event_ts. Late-arrival distribution: 99.7% within
     2 days, max observed 6 days.
   - Source is append-only except for a rare status backfill (updates).
   - Target warehouse: [X]. Current runtime 34 min, scans 1.2 TB.
   
   First: propose the incremental strategy (merge vs delete+insert vs
   append), the unique_key, and the lookback window, with reasoning and
   the failure mode of each rejected option. Wait for my confirmation
   before writing any code.
   
   [paste current model SQL]
   

   Making the assistant argue strategy first surfaces wrong assumptions while they are still cheap.

3. 3.

   Review the strategy against your evidence. Updates in the source rule out pure append. A 6-day observed maximum lateness means a 7-day lookback minimum. If the assistant proposes a 3-day window "as is typical," it ignored your data — push back.

4. 4.

   Accept the code, then verify parity before trusting it. Build the incremental version alongside a fresh full-refresh build and diff:

   Copy

   SELECT
     (SELECT COUNT(*) FROM fct_events_incr) AS incr_rows,
     (SELECT COUNT(*) FROM fct_events_full) AS full_rows,
     (SELECT COUNT(*) FROM
        (SELECT * FROM fct_events_incr
         EXCEPT
         SELECT * FROM fct_events_full)) AS incr_not_full,
     (SELECT COUNT(*) FROM
        (SELECT * FROM fct_events_full
         EXCEPT
         SELECT * FROM fct_events_incr)) AS full_not_incr;
   

   Both EXCEPT counts must be zero after at least three consecutive incremental runs on live data — a single run proves nothing about the merge logic.

5. 5.

   Test the late-data path deliberately. Insert a synthetic source row with an event_ts inside the lookback window but a fresh loaded_at, run incrementally, and confirm it lands. Then insert one just outside the window and confirm you understand (and accept) that it does not.

6. 6.

   Add the safety rails before merging. A unique test on the key, a scheduled periodic full-refresh (weekly or monthly) as a self-healing backstop, and a comment in the model recording the measured late-arrival basis for the lookback window.

Verify before you trust it

• •
  Bidirectional EXCEPT vs a fresh full-refresh build is zero/zero after 3+ incremental runs.
• •
  Uniqueness on the merge key holds: duplicate-key query returns zero rows.
• •
  Synthetic late-arriving row inside the window is captured on the next run.
• •
  Cost check: bytes scanned per run dropped as expected (compare query history before/after) — if it did not, the incremental predicate is not pruning.
• •
  An intentional mid-run failure followed by a rerun produces no duplicates (idempotency).

Pitfalls

• Copy-pasted lookback windows. "3 days" from a blog post, applied to a source with week-late data, drops rows forever with no error. Measure, then set.
• Wrong unique_key grain. A key that is not actually unique makes merge nondeterministic — which source row wins varies by run. The uniqueness test is mandatory, on the source side too.
• is_incremental() predicate on the wrong column. Filtering on event_ts when arrival is what matters (or vice versa) either misses late rows or rescans everything. The parity check plus the cost check together catch both directions.
• One-run parity theater. The first incremental run after a full build almost always matches. The bugs live in run three, when merges and late data interact.
• Timezone mismatch in the cutoff. A MAX(event_ts) in UTC compared against local-time source data shifts the window by hours at the boundary. State the timezone in the prompt and check the boundary explicitly.

Tools

• dbt Copilotknows dbt incremental materializations and project metadata natively; good first drafts of config and predicates.
• Claude Code / Cursorbetter when the conversion needs surrounding changes (tests, backfill runbook, schema YAML) done in one scoped session.
• Great Expectations / dbt teststhe uniqueness and row-count expectations that make the parity guarantees permanent rather than one-time.

Client-safe variant

The strategy discussion needs schema shape and measured distributions, not client data. In restricted environments, run steps 1, 4, 5 (all queries) inside the client warehouse, and give the assistant — via client-tenancy Bedrock/Azure OpenAI, or local Ollama if no approved endpoint exists — only the model SQL with identifiers genericized plus the summary statistics (late-arrival percentiles, row counts). Numbers like "99.7% within 2 days" carry no client-identifying information but are exactly what the strategy depends on. Parity evidence stays in the client environment as the audit artifact.