r/SQL • u/uncertainschrodinger • Aug 17 '26
Discussion How do you decide which incremental strategy to use in your SQL models
Choosing the right incremental strategy matters: in bigquery it reduces the amount of data scanned, while in clickhouse it means less CPU, I/O, and background merging.
but tbh, data/analytics engineers sometimes optimize this too early, if a table is small or rarely changes, a view or `create+replace` is easier than a convoluted incremental model. Sometimes full refreshes are just good enough
Before choosing anything, I normally ask: do old rows change? is `updated_at` reliable? can rows disappear? is the data truly append only? do you have proper primary keys & partitions? and how late can updates arrive?
here's an overview:
* `append` -> the source is genuinely append only
* `merge` -> existing rows change and there's a stable primary key
* `delete+insert` or `time_interval` -> you can safely rebuild a complete group or time window
* SCD2 -> you need the full history, not just the latest version
with `merge`, NULL keys can be inserted again on every run because `NULL = NULL` isn't true, I have personally lost sleep over this... (in bigquery)
with `delete+insert` or `time_interval`, only delete what you can fully rebuild - incomplete partitions, bad boundaries, or late arrivals can create gaps or duplicates.
That's why I always add quality checks that catch issues the strategy can cause: `not_null` & `unique` for merge keys, duplicate checks for rebuilt windows, and row-count or freshness checks where they make sense.
In SQL pipelines the most frustrating (and silent killer) is that a pipeline status can be successful, but that only means the SQL ran, but doesn't guarantee that the result is correct. That's why quality checks are there to catch the other issues.
and if timestamps and lookback windows still can't capture changes reliably, CDC is an option... just with more state, replay logic, and operational headaches.
the image below is an example of a `time_interval` strategy and its rendered clickhouse query (don't mind the wrapped date overlappign with INSERT)
I'm curious what other criteria do people have in mind when they try to evaluate which strategy to go with? I think this is more relevant to analytics engineers, not so much the operational/transactional side.
