Poznámka
Na prístup k tejto stránke sa vyžaduje oprávnenie. Môžete sa skúsiť prihlásiť alebo zmeniť adresáre.
Na prístup k tejto stránke sa vyžaduje oprávnenie. Môžete skúsiť zmeniť adresáre.
Both REPLACE WHERE flows and standalone materialized views let you maintain derived tables that stay current as source data changes, but they solve different problems. For most batch extract, transform, and load (ETL) workloads that periodically overwrite a time window or a partition, use an incremental REPLACE WHERE flow. Use materialized views for a query-acceleration layer that serves precomputed results to dashboards and reports.
This page covers standalone streaming tables and materialized views. For REPLACE WHERE flows in a Lakeflow pipeline, see Batch processing with REPLACE WHERE flows.
Use REPLACE WHERE for batch overwrites
A REPLACE WHERE flow recomputes and overwrites only the rows that match a predicate, such as a time window or a partition. All other rows stay untouched. Because the flow re-evaluates the source query for just that predicate range, the cost of each refresh depends on the size of the range, not the size of the table.
A materialized view maintains the full result of its defining query. Azure Databricks refreshes a materialized view incrementally when the query and source data support it, and otherwise runs a full refresh. Whether a refresh is incremental depends on the query structure and the source tables, so the cost of each refresh is harder to predict. See How are standalone materialized views refreshed?.
For a batch ETL data model that changes in bounded ranges, a REPLACE WHERE flow lets you control exactly which rows each refresh rewrites. A materialized view is the better fit when readers need fast access to a precomputed result.
Map constructs to medallion layers
The following table maps each layer of a medallion architecture to the construct Databricks recommends:
| Layer | Construct | Why |
|---|---|---|
| Bronze (raw ingestion) | Ingestion with Lakeflow Connect or Auto Loader | Lands raw records incrementally as they arrive, without reprocessing history. |
| Silver (facts) | Incremental REPLACE WHERE flow | Overwrites only the affected time windows or partitions for fact updates, late-arriving data, and corrections. |
| Silver (dimensions) | AUTO CDC flow |
Applies insert, update, and delete semantics automatically and preserves history with slowly changing dimension (SCD) Type 1 or Type 2. |
| Gold (aggregations and data models) | Incremental REPLACE WHERE flow | Recomputes only the predicate range for aggregates and models, avoiding full-table recomputes. |
| Query acceleration and serving | materialized view | Serves precomputed results to dashboards and reports and keeps query results current. |
Decision guide
Use the following guide to choose between the two constructs.
Use a REPLACE WHERE flow when you:
- Run periodic batch overwrites of a time window or partition.
- Correct late-arriving data.
- Apply compliance deletions to a bounded range.
- Refresh data at the partition level.
Use a materialized view when you:
- Accelerate dashboard queries.
- Serve precomputed aggregations to readers.
- Keep query results current for a serving layer.
Example: incremental overwrite with REPLACE WHERE
The following example defines a target table that overwrites only the last 7 days of data on each refresh. During refresh, the flow deletes the rows matching the predicate, recomputes the source query for that same range, and inserts the results. Rows outside the window are left untouched:
CREATE OR REFRESH STREAMING TABLE payments_latest
FLOW REPLACE WHERE payment_date >= date_add(current_date(), -7) BY NAME
SELECT payment_id, booking_id, status, payment_date
FROM samples.wanderbricks.payments;
BY NAME is required so that columns match by name rather than position. For the full syntax, predicate overrides, backfills, and incremental refresh behavior, see REPLACE WHERE flows for standalone streaming tables.