Incremental models without dbt (stop re-scanning the whole year)
Chinmay Raibagkar · runs a warehouse on scheduled queries and stubbornness🔁 with a copy-paste MERGE pattern, not a lecturePublished August 28, 2026
The 60-second version
- Nightly full refreshes re-scan history that never changed. Fourteen months of events to rewrite a single day.
- MERGE on a 7-day window is the right default: idempotent, handles restated spend and late conversions, roughly 98% cheaper.
- Filter both sides on the partition date — the target-side clause in the ON condition roughly halves the cost.
Your nightly job rebuilds daily_campaign_performance from scratch. It scans fourteen months of raw events to produce a table where thirteen months and twenty-nine days of it did not change. It has done this every night since March.
The fix is called an incremental model, and you do not need dbt to have one. You need three things: a watermark, a MERGE statement, and a late-arriving-data window. All three fit in a single scheduled query.
What this post is not. dbt is genuinely good, and if you already run it, use its incremental materialisation — it handles the state management described below for you. This is for teams whose entire transformation layer is a handful of BigQuery scheduled queries, which is most teams before their first data hire, and who want the cost profile of an incremental model without adopting a framework to get it.
A watermark
Two dates bounding what gets reprocessed — a trailing window ending yesterday. Everything outside it is assumed finished.
A MERGE statement
One atomic statement that updates changed rows and inserts new ones. Re-run it five times and the table is identical each time.
A late-data window
Wide enough to cover restated spend and slow conversions — measured from your own late-arrival distribution, not a round number.
Three patterns, one winner
Full Refresh vs. Append vs. Merge
Data JourneyFull refresh
Drop and rebuild the whole table each run. Always correct, cost grows linearly with history, and eventually takes longer than the gap between runs.
Insert-only append
Insert yesterday's rows. Cheapest possible, but any re-run duplicates rows and any restated data is silently wrong.
MERGE on a window
Re-process a trailing window and merge it in, updating what changed and inserting what is new. Idempotent, handles restatement, still cheap.
The third one is what you want in nearly every marketing reporting case, for one reason: marketing data restates. Ad platforms revise yesterday's spend. Conversions arrive days after the click. Refunds reverse revenue on orders placed last week. An insert-only model assumes the past is immutable, and in this domain it simply is not.
The core pattern
Here is a complete incremental model. Read it once, then we will take it apart.
The four things that make it work
The window, not the day. INTERVAL 7 DAY means every run re-derives the last week. Yesterday's row gets written seven times over seven days, and each time it is more complete than the last. That redundancy is the entire mechanism for handling late data, and it costs seven days of scan instead of one — still a rounding error against fourteen months.
The partition filter on the source. WHERE event_date BETWEEN window_start AND window_end is what makes this cheap. Without it you scan the full history and the exercise is pointless.
The partition filter on the target. The AND target.report_date BETWEEN ... clause in the ON condition is the line people leave out, and leaving it out roughly doubles the cost — BigQuery cannot prune the target table's partitions unless the join condition constrains them. It looks redundant. It is not.
MERGE, so it is idempotent. Run this query five times in a row and the table is identical each time. That property is what lets you re-run after a failure without thinking, backfill by widening the window, and schedule it without fear of double-counting.
Match your window to your conversion lag, not to a round number. If your median time from click to purchase is nine days, a 7-day window permanently under-reports conversions on days 8–14 — they arrive after the window has moved past them. Measure your actual conversion lag distribution and set the window to roughly where the curve flattens, typically the 90th percentile.
Sizing the window
Read cumulative_pct down the rows and stop where it crosses 99%. That day count is your window. Most marketing sources land between 3 and 14 days; Google Ads conversion restatement and COD order status changes are usually the long tails.
Full refresh vs. 7-day merge on the same table
Size from your measured late-arrival curve, not a round number
The window covers a small fraction of history, so the merge scans a small fraction of the bytes for identical correctness.
The three operational pieces
A full-refresh job needs no operations: it is always right by construction. An incremental one needs three small habits.
Running an Incremental Model Safely
Process FlowKeep a loaded_at column
Every row records when it was last written. Without it you cannot tell a stale row from a fresh one, and debugging becomes guesswork.
Have a documented backfill path
A logic change does not retroactively fix old rows. You need a parameterised way to re-run any historical range on demand.
Reconcile against a full rebuild monthly
Drift is silent. One monthly check against a from-scratch rebuild catches a window that has become too narrow before anyone reports off the wrong number.
Backfilling after a logic change
The same statement, with the window parameterised. Run it in chunks rather than one enormous range, so a failure does not cost you the whole scan:
The monthly reconciliation
This is the check that catches a silently-too-narrow window:
An empty result is a passing test. Rows clustered at the far edge of your window mean the window is too narrow. Rows scattered randomly mean something else is wrong — usually a source that restates further back than you thought, or a MERGE key that is not actually unique.
Your monthly drift check empty result means passing →
- Run the reconciliation query monthly — incremental output against a from-scratch rebuild over the trailing 60 days.
- Rows clustered at the window edge mean the window is too narrow — widen it to the new 99th percentile of days-late.
- Scattered rows mean a deeper problem — a source restating further back than assumed, or a MERGE key that stopped being unique.
The most common silent bug is a non-unique merge key. If (report_date, campaign_id, channel) is not genuinely unique in your source subquery, MERGE throws an error on the duplicate — which is the good case. The bad case is a key that is unique today and stops being unique when a new dimension is added upstream, at which point the job starts failing at 3am with an error nobody reads. Assert uniqueness in the source subquery, or add the missing dimension to the key.
When to stop doing this by hand
This pattern scales to perhaps a dozen models. Past that, you are hand-maintaining things a framework does better:
🧵 Hand-rolled scheduled queries
This post's pattern is enough. One MERGE per model, run times as the dependency order, and manual reconciliation.
- Models: under ~12
- Dependencies between models: handled by run times
- Testing: manual reconciliation
- Contributors: one or two
- Backfills: occasional, manual
🏗️ Time for a framework
Adopt dbt or equivalent. Dependency ordering, assertions in CI and routine backfills stop being hand-maintainable.
- Models: more than ~15
- Dependencies: need a real DAG
- Testing: assertions in CI
- Contributors: a team needing review
- Backfills: routine
The honest summary: the pattern above gives you 90% of incremental value at zero adoption cost, and stops being enough at roughly the point where dependency ordering becomes something you think about. you will feel this moment, promise
Frequently asked questions
Why MERGE instead of DELETE then INSERT?
MERGE is atomic — readers never see a window where the data is missing. DELETE followed by INSERT is two statements, and any dashboard querying between them gets a table with a hole in it. MERGE also handles the update case in one pass rather than rewriting rows.
Does MERGE cost more than INSERT?
Slightly, because it scans the matching partitions of the target table as well as the source. That is why the partition filter on the target side matters so much — with it, you scan N days of target instead of the whole table, and the overhead is small. Without it, the target scan can exceed the source scan.
What if my source has no reliable event date?
Then you cannot partition-prune and this pattern does not save you anything. Fix that first: add an ingestion date column at load time if the source has no usable event date. Everything about warehouse cost control depends on having one date column you can filter on cheaply.
Can I run this more than once a day?
Yes. MERGE is idempotent, so hourly runs of the same statement are safe. Narrow the window for frequency — an hourly job with a 7-day window scans 168 days of partitions per day, which defeats the purpose. Run hourly with a 2-day window and nightly with the full 7-day one.
One conceptual check before the summary. window sizing is the whole game
Your click-to-purchase p90 is 12 days, but the MERGE window is 7 days. What happens?
How does this interact with require_partition_filter?
It works with it, provided both the source subquery and the target ON clause carry partition filters — which the pattern above does. If you have require_partition_filter = TRUE set on the target table and omit the target-side filter, the MERGE fails outright. That is a useful accident: the option that saves you money also enforces the clause that makes MERGE cheap.
The summary
- Full refresh is correct and gets expensive; insert-only is cheap and breaks on restatement.
MERGEon a trailing window is the right default for marketing data, which restates constantly. - Partition-filter both sides. The target-side filter in the
ONclause looks redundant and roughly halves the cost. - Size the window from your measured late-arrival distribution, not a round number — the 99th percentile of
days_late. - Carry a
loaded_atcolumn, keep a parameterised backfill ready, and reconcile against a full rebuild monthly. - Move to a framework when dependency ordering between models becomes something you have to think about.
Related: BigQuery cost guardrails for the controls that catch the job you forget to make incremental.
Takeaways for your next report
- Full refresh is correct and gets expensive; insert-only is cheap and breaks on restatement. MERGE on a trailing window is the right default.
- Partition-filter both sides — the target-side clause in the ON condition looks redundant and roughly halves the cost.
- Size the window from your measured late-arrival distribution (near the 99th percentile), never from a round number.
- Carry a loaded_at column, keep a parameterised backfill ready, and reconcile against a full rebuild monthly.
BigQuery Cost Estimator
Estimate what a BigQuery query will cost before you run it, from validator bytes, with a table-scan simulator and a paste-your-query cost audit.
Chinmay Raibagkar
About author →Founder of DataLens AI. He helps non-technical teams read their ad and database numbers with confidence — which number to trust, what to do next, and what to ignore.