Incremental Models Without dbt: Scheduled Queries That Don't Re-Scan the Year

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.

The three patterns, and which one you want

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.

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.

The three operational pieces

A full-refresh job needs no operations: it is always right by construction. An incremental one needs three small habits.

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.

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:

The honest summary: the pattern above gives you 90% of dbt's incremental value at zero adoption cost, and stops being enough at roughly the point where dependency ordering between models becomes something you think about.

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.

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. MERGE on a trailing window is the right default for marketing data, which restates constantly.
• Partition-filter both sides. The target-side filter in the ON clause 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_at column, 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.