Streaming Tables vs Materialized Views
Databricks · Declarative Pipelines (Lakeflow)
Theory
A pipeline has two main kinds of datasets. A streaming table is a Delta table fed by one or more streaming flows: each update reads only new rows from an append-only source (files via Auto Loader, Kafka, another streaming table) and appends them, processing each row once. A materialized view (MV) stores the result of a query and, on each refresh, makes that stored result match the current source data, recomputing incrementally when it can and fully when it must. MVs handle updates, deletes, joins and aggregations correctly because they always reflect the query's current answer.
Example: orders_bronze is a streaming table over new CSV files. sales_by_region is an MV that sums orders_silver; if an old order's amount is corrected, the MV total is corrected on the next refresh.
Why we use it
Choosing the right type keeps pipelines cheap and correct. Streaming tables avoid re-reading history for high-volume append data, and materialized views give always-correct aggregates and joins without you writing merge logic.
When to use it
Use streaming tables for ingestion and append-only transformations (bronze, most silver). Use materialized views for aggregates, joins with changing dimensions and gold tables. Both can also be created directly in Databricks SQL outside a pipeline, with a refresh schedule.
How it works
- Need a new dataset in the pipeline
- Is the source append-only and you only need new rows? Yes: Streaming table (FROM STREAM ...). No: Next question.
- Does the result aggregate, join changing data or need corrections? Yes: Materialized view. No: Use a view for light reshaping.
- Declare it with CREATE OR REFRESH ...
- Run the pipeline and check the table type in Catalog
Syntax
CREATE OR REFRESH STREAMING TABLE <name> AS SELECT ... FROM STREAM <source>;
CREATE OR REFRESH MATERIALIZED VIEW <name> AS SELECT ... FROM <table>;
Python: @dp.table (streaming when the query reads a stream) | @dp.materialized_view
Stand-alone in DBSQL: ... SCHEDULE EVERY 1 HOUR or TRIGGER ON UPDATEExample
-- Streaming table: reads only new rows each update
CREATE OR REFRESH STREAMING TABLE orders_silver AS
SELECT order_id, customer_id, region, amount, order_date
FROM STREAM(orders_bronze)
WHERE amount > 0;
-- Materialized view: always matches the current query result
CREATE OR REFRESH MATERIALIZED VIEW customer_sales AS
SELECT c.city, c.segment, SUM(o.amount) AS total_sales
FROM orders_silver o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city, c.segment;Key points
- Streaming tables need an append-only source; updated or deleted source rows are not reprocessed
- Materialized views can refresh incrementally, but fall back to full recompute when needed
- A stream-static join in a streaming table uses the dimension as it was at processing time
- Use STREAM(...) or STREAM read_files(...) only in streaming table queries
- Full refresh of a streaming table clears it and reprocesses all source data
- Both are Unity Catalog objects you can grant SELECT on