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

  1. Need a new dataset in the pipeline
  2. Is the source append-only and you only need new rows? Yes: Streaming table (FROM STREAM ...). No: Next question.
  3. Does the result aggregate, join changing data or need corrections? Yes: Materialized view. No: Use a view for light reshaping.
  4. Declare it with CREATE OR REFRESH ...
  5. 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 UPDATE

Example

-- 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic