Streaming table versus materialized view: when to use each?

Summary

• In Spark Declarative Pipelines (SDP), a streaming table is "a definition of a table and one or more streaming flows written into it". A materialized view is "a view that is precomputed into a table" and "always has exactly one batch flow writing to it".
• In Python, a streaming table is @dp.table with a streaming read, and a materialized view is @dp.materialized_view with a batch read. In SQL they are CREATE STREAMING TABLE and CREATE MATERIALIZED VIEW.
• Use a streaming table for a source that keeps growing and is processed once. Use a materialized view for aggregations, joins, or sources that change through updates and deletes.
• The two combine in one pipeline: streaming tables ingest, materialized views transform. On Databricks, pipeline mode is independent of the table type.

Streaming table versus materialized view: when to use each?

Both are dataset types in Spark Declarative Pipelines (SDP), the Apache Spark framework added in Spark 4.1.0. The Spark guide defines a streaming table as "a definition of a table and one or more streaming flows written into it" and says streaming tables "support incremental processing of data, allowing you to process only new data as it arrives." It defines a materialized view as "a view that is precomputed into a table" that "always has exactly one batch flow writing to it." In short, a streaming table reads a stream and a materialized view reads a batch.

What does the same source look like as each?

The guide's two basic examples read the same table both ways:

from pyspark import pipelines as dp
from pyspark.sql import DataFrame

@dp.table
def basic_st() -> DataFrame:
    return spark.readStream.table("samples.nyctaxi.trips")

@dp.materialized_view
def basic_mv() -> DataFrame:
    return spark.table("samples.nyctaxi.trips")

The SQL forms are CREATE STREAMING TABLE basic_st AS SELECT * FROM STREAM samples.nyctaxi.trips; and CREATE MATERIALIZED VIEW basic_mv AS SELECT * FROM samples.nyctaxi.trips;. Spark 4.2 injects spark into each pipeline file. On Spark 4.1, add spark = SparkSession.active() yourself.

How do they differ?

Aspect	Streaming table	Materialized view
Flows (Spark guide)	One or more streaming flows	Exactly one batch flow
Read	Streaming: spark.readStream, STREAM keyword in SQL	Batch: spark.table, spark.read
Processing (Databricks docs)	Each record is processed once, assuming an append-only source	Results are recomputed as needed to reflect the current state of the data
Default refresh (Databricks docs)	Processes new records through the defining logic	Updates results to match the defining query, incrementally when that is more efficient
Full refresh (Databricks docs)	Clears data and checkpoints, then reprocesses all source records	Recomputes results
Joins (Databricks docs)	Don't recompute when a dimension changes	Recompute when a dimension changes
Latency (Databricks docs)	Suited to low-latency streaming	"not designed for low-latency use cases"

When should you use each?

The Databricks docs list the cases. Use a streaming table when a query is defined against a source that is continuously or incrementally growing, results should be computed incrementally, and the pipeline needs high throughput and low latency. For ingestion, Databricks recommends streaming tables for most use cases. Use a materialized view when multiple downstream queries or other jobs consume the table, when the query performs aggregations or joins, or when the source data can change through updates and deletes instead of only growing. A materialized view also fits small or static reference data, because "There's no benefit to streaming something that barely changes."

Can they work together?

Yes. The guide's SQL example lands a stream in a streaming table and builds materialized views on top:

CREATE STREAMING TABLE orders
AS SELECT * FROM STREAM orders_source;

CREATE MATERIALIZED VIEW customers
AS SELECT * FROM customers_source;

CREATE MATERIALIZED VIEW customer_orders
AS SELECT
  c.customer_id,
  o.order_number,
  c.state,
  date(timestamp(int(o.order_datetime))) order_date
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;

CREATE MATERIALIZED VIEW daily_orders_by_state
AS SELECT state, order_date, count(*) order_count
FROM customer_orders
GROUP BY state, order_date;

Because the pipeline "analyzes the dependencies of your defined objects and orchestrates their order of execution and parallelization automatically", it works out that orders and customers come before customer_orders, and customer_orders comes before daily_orders_by_state.

How do they behave on Databricks?

Lakeflow Pipelines extend SDP. Databricks says the pipeline mode, triggered or continuous, "is independent of the type of table being computed", so both kinds can be updated in either mode. Standalone streaming tables and materialized views, which you create outside a Lakeflow pipeline, always refresh in triggered mode. Databricks also notes that a changed query in a streaming table only affects rows processed afterward, while a materialized view always shows the correct result for its current query.

FAQs

Can one pipeline contain both?

Yes. The guide's SQL example defines the streaming table orders, the materialized view customers, a materialized view customer_orders that joins them, and daily_orders_by_state, which aggregates the join.

Does the table type decide how often it refreshes?

On Databricks, no. Pipeline mode is independent of the dataset type.

Where should small or static reference data live?

A materialized view with a batch read, per the Databricks load guidance for small or static reference data.

How do I reprocess a streaming table versus a materialized view?

For a streaming table, a Databricks full refresh clears the data and checkpoints and reprocesses all records from the source. In Apache Spark, spark-pipelines run --full-refresh clears existing data and checkpoints for the datasets you list. A materialized view returns the same results after a default or full refresh.