How do I run incremental transformations in SQL without reprocessing the whole table every time?

Summary

• A materialized view refreshes incrementally on serverless compute when its query supports it, and otherwise falls back to a full recompute.
• A streaming table refresh evaluates only the rows that arrived after the last update, so it suits sources that only grow.
• AUTO CDC applies change feeds in sequence order, and REPLACE WHERE flows recompute only a predicate-defined slice of a streaming table.
• When none of those fit, MERGE a bounded, deduplicated slice of the source into a Delta table.

How do I run incremental transformations in SQL without reprocessing the whole table every time?

The right way to process only new or changed data depends on how your source changes. Use a streaming table for sources that only grow, a materialized view for aggregations and joins over data that can change, AUTO CDC for change feeds, and a hand-written MERGE otherwise.

Which option fits my source?

Option	What it processes	Use it when	Documented limit
Materialized view	New or changed source data, when the query supports it	Aggregations, joins and filters over data that can change	Incremental refresh needs serverless compute; best effort
Streaming table	Rows that arrived after the last update	Sources that only grow, such as files or message buses	Requires append-only sources by default
AUTO CDC	Change records, ordered by a sequence column	Feeds of inserts, updates and deletes	Needs serverless pipelines or the Pro or Advanced editions
REPLACE WHERE flow	Rows matching a predicate on a streaming table	Late-arriving data and recent-window reprocessing	Incremental refresh needs serverless compute
MERGE	The slice of the source you select	Any Delta table where you manage the filter	Fails if several source rows match one target row

How does a materialized view avoid recomputing everything?

You write the query with batch semantics. On refresh, Databricks makes a best-effort attempt to process only new or changed source data, and a cost model chooses between incremental and full recompute. The result is the same.

CREATE OR REPLACE MATERIALIZED VIEW transaction_summary AS
SELECT account_id,
       COUNT(txn_id) AS txn_count,
       SUM(txn_amount) AS account_revenue
FROM transactions_table
GROUP BY account_id;

Some incremental operations need row tracking on the source tables, which you enable with ALTER TABLE transactions_table SET TBLPROPERTIES (delta.enableRowTracking = true);. Run EXPLAIN CREATE MATERIALIZED VIEW to test whether a query can refresh incrementally. Add REFRESH POLICY INCREMENTAL STRICT to the definition if you want a refresh to fail instead of falling back to a full recompute, and run REFRESH MATERIALIZED VIEW transaction_summary FULL to force a full refresh. Do not build a materialized view on a source that must process each record once, such as Kafka or Auto Loader ingestion. Use a streaming table there.

How do I process only new rows with a streaming table?

CREATE OR REFRESH STREAMING TABLE sales
SCHEDULE EVERY 1 hour
AS SELECT product, price FROM STREAM raw_data;

A streaming table refresh only evaluates new rows that have arrived after the last update, and appends only the new data.

How do I handle updates, deletes and late data?

For a change feed, AUTO CDC applies inserts, updates and deletes in the order of a sequence column, so an out-of-order record does not overwrite a newer one. The docs note that MERGE INTO can produce incorrect results with out-of-sequence records or needs complex logic to re-order them.

CREATE OR REFRESH STREAMING TABLE target
FLOW AUTO CDC
FROM stream(cdc_data.users)
KEYS (userId)
SEQUENCE BY sequenceNum
STORED AS SCD TYPE 1;

To reprocess only a recent slice, such as late-arriving rows, define a REPLACE WHERE flow. Each refresh deletes the target rows that match the predicate and recomputes them from the source. A full refresh keeps only rows that match the predicate and permanently deletes older ones, so set pipelines.reset.allowed to false on tables that hold history.

CREATE OR REFRESH STREAMING TABLE orders_enriched
SCHEDULE EVERY 1 DAY
FLOW REPLACE WHERE date >= date_add(current_date(), -7) BY NAME
SELECT order_id, date, region, qty
FROM orders_fct;

How do I write the incremental filter myself with MERGE?

On a Delta table, MERGE a bounded window of the source. This pattern from the docs updates and inserts rows in the window and deletes target rows in the same window that no longer exist in the source.

MERGE INTO target AS t
USING (
  SELECT * FROM source
  WHERE created_at >= (current_date() - INTERVAL '5' DAY)
  QUALIFY row_number() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1
) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
WHEN NOT MATCHED BY SOURCE AND t.created_at >= (current_date() - INTERVAL '5' DAY) THEN DELETE;

If more than one source row matches a target row, MERGE fails with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE, so the QUALIFY clause keeps only the newest row per key. UPDATE SET * and INSERT * assume the source has the same columns as the target. Apply the same filter to the source and the target so changes and deletes propagate. Choose a window that covers how long source rows can still change. The docs example uses five days.

FAQs

Does a materialized view always refresh incrementally?

No. Incremental refresh is best effort: some source changes require a full refresh, and queries with unsupported expressions fall back to a full recompute.

What happens when source rows are deleted?

A materialized view recomputes the affected results. AUTO CDC applies deletes with APPLY AS DELETE WHEN, and the MERGE pattern above uses WHEN NOT MATCHED BY SOURCE.

Do I need serverless compute?

For incremental refresh of a materialized view, yes. On classic compute a materialized view is fully recomputed.