How to choose an incremental data loading strategy

Quick answer: choose an incremental data loading strategy from the way the source changes. Use a full refresh when the result is cheap to rebuild, append for immutable new rows, merge for inserts and updates identified by a stable key, delete+insert or time_interval when you can safely rebuild a complete group or time window, and SCD2 when you need historical versions. If the source cannot reliably expose updates and deletes, start with CDC instead of pretending a timestamp filter will find them.

One of the fastest ways to make a pipeline expensive is to process every row every time it runs. On BigQuery, that means scanning and rewriting more data than the job needs. On ClickHouse, it means more CPU, I/O, and background merges. Neither is much fun when the table grows.

But I also see data engineers reach for an incremental model before it has earned its complexity. If a table is small, changes rarely, or can be rebuilt in seconds, a view or full refresh is often the better choice. It is easier to understand, easier to backfill, and much harder to corrupt quietly. Saving a few seconds today is not worth six months of repairing a brittle loading scheme.

The useful question is not "which strategy is fastest?" It is "what can the source prove about change?"

Start with the source, not the destination

Before picking a strategy, get specific about how the source behaves. This usually means asking the people who own it a few slightly annoying questions:

• Can an old row change after it has landed in the warehouse?
• Is updated_at reliable, and does every update touch it?
• Are rows really append only, or do they only look append only right now?
• Can a source delete a row?
• Do you have a stable primary key?
• How late can an update or event arrive?

Those answers decide whether an incremental filter is safe. A table with a reliable updated_at and a real primary key is a very different problem from an event log with late arrivals or a source that mutates history without recording when it did so.

Incremental loading strategy decision tree

This is the decision tree I use:

Is a complete rebuild still cheap?
  -> yes: create+replace
  -> no:
     Are rows immutable after they arrive?
       -> yes: append
       -> no:
          Do you have a stable, non-null key and a reliable update cursor?
            -> yes, and only current state matters: merge
            -> no, but you can rebuild a complete group: delete+insert
            -> no, but each SQL run owns a complete time window: time_interval
            -> historical versions matter: scd2_by_column or scd2_by_time
            -> updates or deletes are otherwise invisible: capture them with CDC

The destination still matters for cost, locking, partitions, and supported SQL. But it comes second. A cheap warehouse operation cannot recover a source change that your filter never saw.

Incremental loading strategy comparison

CDC belongs beside this table, but it is not another materialization strategy. It is a way to capture inserts, updates, and deletes from the source when timestamps and lookback windows are not trustworthy. The destination can still use merge, append an immutable change log, or build an SCD2 table from those captured changes.

Bruin SQL materialization syntax

The snippets below use Bruin SQL assets because the materialization declaration sits beside the SELECT. The Bruin materialization reference covers the full schema and platform support.

These are examples, not a requirement: use your warehouse's SQL dialect and your orchestrator's configuration in a real project. The decision about how a source changes comes first. The date examples use the CLI's inclusive start_date and end_date values, so render an asset before a custom backfill to confirm its exact window.

Use a full refresh when a rebuild is cheaper than complexity

If a table is small or fast to rebuild, start with create+replace. It replaces the destination table with the query result on every run:

/* @bruin
name: analytics.daily_revenue
type: bq.sql

materialization:
  type: table
  strategy: create+replace
@bruin */

SELECT
  order_date,
  SUM(amount) AS revenue
FROM raw.orders
GROUP BY order_date;

There is no incremental filter because the query intentionally rebuilds the whole result. Keep this strategy while the table is cheap enough to recreate and the simpler failure mode is worth more than a smaller bill.

truncate+insert is the other full-reload option. It empties the existing table and inserts the new result without dropping and recreating the table:

/* @bruin
name: analytics.current_inventory
type: bq.sql

materialization:
  type: table
  strategy: truncate+insert
@bruin */

SELECT product_id, warehouse_id, stock
FROM raw.current_inventory;

Use it when keeping the existing table object matters. Warehouse behaviour around transactions, metadata, and permissions varies, so render and test it on the platform you actually run.

Use append only when new rows stay new

append is the simplest strategy. Each run inserts the rows in its window and leaves what is already in the destination alone.

It works well for immutable event streams: application events, log records, or transactions where an event never changes after it is written. The source query must return only data that has not been loaded before.

The word "immutable" matters. If the source sometimes updates an event, delivers it late, or returns the same range twice during a backfill, append will happily insert it again. It is not doing anything wrong. It has no instruction to look for an existing version.

When I use append, I want a proper event ID or another clear deduplication plan. "It probably will not happen" is not a plan.

/* @bruin
name: analytics.events
type: bq.sql

materialization:
  type: table
  strategy: append
@bruin */

SELECT event_id, event_type, event_date
FROM raw.events
WHERE event_date BETWEEN '{{ start_date }}' AND '{{ end_date }}';

For the default daily schedule, both values resolve to the day being processed. The query therefore inserts that day's events once. A backfill needs the same care: make its date range explicit and check it before running.

Use merge when the latest version is what matters

merge updates rows that match a primary key and inserts rows it has not seen before. It is the normal choice for entities such as customers, orders, subscriptions, or product records where the current state matters more than the old one.

The key must be stable and complete. A missing key is not a small data-quality issue here. It changes the behaviour of the load.

In particular, check for NULL values in merge keys. In SQL, NULL = NULL is not TRUE. A source row with a nullable key cannot match its existing target row with a normal equality condition, so a MERGE can insert it again on every run. Both I and Sabri have lost sleep over versions of this.

Use not_null and unique checks for merge keys. If you are working in BigQuery, our null-safe merge benchmark shows why a nullable key needs more care than adding COALESCE and hoping for the best.

Also make sure the query reads a safe window of updates. If the source updates a row without updating updated_at, no incremental strategy based on that timestamp can find it.

/* @bruin
name: analytics.customers
type: bq.sql

materialization:
  type: table
  strategy: merge

columns:
  - name: customer_id
    type: STRING
    primary_key: true
  - name: email
    type: STRING
    update_on_merge: true
  - name: plan
    type: STRING
    update_on_merge: true
@bruin */

SELECT customer_id, email, plan
FROM raw.customers
WHERE DATE(updated_at) BETWEEN '{{ start_date }}' AND '{{ end_date }}';

The primary key decides whether a row matches. update_on_merge: true names the columns that should take the incoming value when it does.

One more catch: a normal merge does not delete a target row just because that row disappeared from the source query. Propagating source deletes requires a delete signal, such as CDC, a tombstone record, a soft-delete column, or a complete group replacement.

Rebuild a complete group with delete+insert

delete+insert is a good fit when the source can return a complete group of records that you can replace safely. A date partition is the usual example: remove one day's rows, then insert that day again.

This handles corrections much better than append, because a re-run replaces the records for the group rather than adding more copies. It also avoids the row-by-row matching cost of a merge when the natural unit of change is a whole partition.

The hard rule is simple: only delete what you can fully reconstruct. If the job deletes a day but the query reloads only part of that day, the destination now has a gap. If the query reaches farther back than the deleted range, the extra rows may duplicate what was already there.

/* @bruin
name: analytics.orders
type: bq.sql

materialization:
  type: table
  strategy: delete+insert
  incremental_key: order_date
@bruin */

SELECT order_id, order_date, status
FROM raw.orders
WHERE order_date BETWEEN '{{ start_date }}' AND '{{ end_date }}';

The materialization deletes target rows whose order_date appears in the query result, then inserts that result. So the query must return the complete set for every date it touches.

Use time_interval for scheduled SQL windows

time_interval makes the time window explicit for a SQL asset. Bruin gives each scheduled run a start_date and end_date. The strategy deletes the destination rows in that interval, then inserts the query result for the same interval.

This SQL asset rebuilds a daily event window. bq.sql is just the concrete dialect used in the example:

/* @bruin
name: analytics.events
type: bq.sql

materialization:
  type: table
  strategy: time_interval
  incremental_key: event_date
  time_granularity: date
@bruin */

SELECT event_id, event_date
FROM raw.events
WHERE event_date BETWEEN '{{ start_date }}' AND '{{ end_date }}';

For the default daily run, both variables render to the same date, so the asset owns that day. If you manually pass --start-date 2026-07-20 --end-date 2026-07-21, it owns both calendar days. This is why bruin render is worth using before a backfill.

The query filter and the materialization window have to stay in sync. Do not reach three days back in the WHERE clause while the strategy deletes one day. That looks like a late-arrival fix, but it inserts three days of rows that the load did not delete first. Use an interval modifier to widen the window instead.

Bruin's interval modifiers widen the start and end dates together. Run bruin render assets/events.sql before executing a new materialization. It shows the compiled SQL with the real dates and makes boundary mistakes much easier to catch. The Bruin render guide walks through the output, and the incremental vs full refresh guide covers regular runs, backfills, and --full-refresh.

Use SCD2 when history is the point

Sometimes the latest value is not enough. If you need to answer "what did this customer plan look like in March?" or "which account owner was assigned when the opportunity closed?", keep the history with an SCD2 strategy.

SCD2 creates a new version when a tracked row changes and records the period that version was valid. It is useful for audit, attribution, and historical reporting. It also asks more of the model: define the business key, decide which attributes should trigger a new version, and be clear about late updates. Do not use it because it feels more complete. Use it when someone will query the history.

scd2_by_column compares the non-key columns in a complete current snapshot. It adds _valid_from, _valid_until, and _is_current to preserve the history:

/* @bruin
name: analytics.customer_plan_history
type: bq.sql

materialization:
  type: table
  strategy: scd2_by_column
  incremental_key: updated_at

columns:
  - name: customer_id
    type: STRING
    primary_key: true
  - name: plan
    type: STRING
  - name: region
    type: STRING
  - name: updated_at
    type: TIMESTAMP
@bruin */

SELECT customer_id, plan, region, updated_at
FROM raw.customer_snapshot;

The incremental_key is optional for scd2_by_column, but useful here because it makes the validity range follow the source's updated_at rather than the processing time. The input must be a complete snapshot. A partial incremental query would make omitted current records look deleted.

scd2_by_time is the stricter option. It requires a date or timestamp incremental_key and derives each version's validity period from it:

/* @bruin
name: analytics.product_stock_history
type: bq.sql

materialization:
  type: table
  strategy: scd2_by_time
  incremental_key: source_updated_at

columns:
  - name: product_id
    type: INTEGER
    primary_key: true
  - name: stock
    type: INTEGER
  - name: source_updated_at
    type: TIMESTAMP
@bruin */

SELECT product_id, stock, source_updated_at
FROM raw.product_snapshot;

Use this version only when source_updated_at is trustworthy. A timestamp that is late, missing, or updated inconsistently makes the historical timeline just as misleading as a bad merge key.

Incremental loading vs CDC

Incremental loading and change data capture solve different parts of the pipeline.

• Incremental loading decides how a batch of rows changes the destination table.
• CDC decides how the pipeline learns that the source inserted, updated, or deleted a row.

Polling updated_at can be enough when the source guarantees that every change updates the column and rows are not hard deleted. It is simple, easy to inspect, and often the right starting point.

CDC is the safer choice when deletes matter, updates do not reliably touch a timestamp, or a lookback query would put too much load on the source. A log-based CDC reader captures the change at the database layer, then the destination still needs an application strategy. That may be a merge into a current-state table, an append-only raw change log, or an SCD2 model.

The longer explanation is in What is CDC in data engineering?. The short version: CDC is not a magic replacement for modelling. It gives the pipeline a better record of what changed.

FAQ

What is an incremental data loading strategy?

An incremental data loading strategy defines how a pipeline applies new or changed source rows to an existing destination table. Common choices include append, merge, partition replacement, time-window replacement, and SCD2 history.

How do I choose between append and merge?

Use append when source rows are immutable and every run returns only new records. Use merge when existing rows can change and you have a stable, non-null primary key plus a reliable way to find updated rows.

When should I use a full refresh instead of incremental loading?

Use a full refresh when the table is small or cheap to rebuild, when the source cannot reliably identify changed rows, or when you need a complete reconciliation. The simpler load is often safer until scale makes it too expensive.

How should an incremental pipeline handle late-arriving data?

Reprocess a complete lookback window with delete+insert or time_interval so the pipeline removes and reloads the same range. In Bruin, use interval modifiers to widen both the query and materialization window together.

Can merge handle source deletes?

Not by itself. A normal merge updates matching rows and inserts new ones, but it does not know that a missing source row was deleted. Use CDC, tombstone records, soft-delete flags, or a complete group replacement when deletes must propagate.

What is the difference between incremental loading and CDC?

Incremental loading describes how a destination table is updated. Change data capture describes how inserts, updates, and deletes are detected at the source. A pipeline can capture changes with CDC and then apply them with merge or another destination strategy.

Match the checks to the load

A successful pipeline run only tells you that the SQL executed. It does not tell you that the load was correct.

The checks should follow the strategy:

• For merge, check that keys are not_null and unique.
• For append, check duplicate event IDs if a replay is possible.
• For delete+insert and time_interval, check the rebuilt window for duplicates, unexpected row-count changes, and freshness.
• For SCD2, check that only one version of a business key is current at a time.

The first choice does not need to be permanent. Start with the least complicated strategy that accurately captures the source's changes, then move to CDC or a more involved model when the source gives you evidence that you need it. A boring pipeline that you can re-run safely is usually the professional move.

So, what does your source actually promise about change, and can your next backfill prove that promise?