What is a materialized view in SDP?

Summary

• A materialized view in Spark Declarative Pipelines (SDP) is a view that is precomputed into a table, and it always has exactly one batch flow writing to it.
• Define one in Python with the @dp.materialized_view decorator on a function that performs a batch read, or in SQL with CREATE MATERIALIZED VIEW.
• Use a streaming table (@dp.table with a streaming read, or CREATE STREAMING TABLE) when the source is a stream and you want to process new data as it arrives.
• On Databricks, Lakeflow Pipelines extend SDP. A materialized view refresh is incremental when Databricks judges that cheaper and the pipeline is serverless.

What is a materialized view in SDP?

The Apache Spark programming guide defines a materialized view as "a view that is precomputed into a table" and adds that it "always has exactly one batch flow writing to it". A standard view computes its results on demand, while a materialized view caches them, so downstream queries read stored results. SDP is a declarative framework: you define the datasets, and SDP analyzes their dependencies and orchestrates execution order and parallelization.

How do I define a materialized view?

In Python, import the module as dp and put @dp.materialized_view on a function that performs a batch read and returns a DataFrame. The view takes the function's name unless you pass name.

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

@dp.materialized_view
def daily_orders_by_state() -> DataFrame:
    return (
        spark.table("customer_orders")
        .groupBy("state", "order_date")
        .count()
        .withColumnRenamed("count", "order_count")
    )

Use @dp.materialized_view(name="trips_mv") to choose the name. Spark 4.2 injects spark into each pipeline file. In Spark 4.1, add spark = SparkSession.active() at the top of the file, after importing SparkSession from pyspark.sql.
In SQL:

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

How does a materialized view differ from a streaming table?

	Streaming table	Materialized view
Python	@dp.table on a function with a streaming read (spark.readStream)	@dp.materialized_view on a function with a batch read
SQL	CREATE STREAMING TABLE ... AS SELECT * FROM STREAM ...	CREATE MATERIALIZED VIEW ... AS SELECT ...
Flows	One or more streaming flows	Exactly one batch flow
Processing	Handles only new data as it arrives	Precomputed result of the defining query
Databricks semantics	Each record is processed once, assuming an append-only source	Results are recomputed as needed to reflect the current state of the data

When should I use a materialized view?

The Databricks docs suggest a materialized view when:

• several downstream queries consume the table, or other pipelines, jobs or queries do
• you want to inspect the results during development
• the query performs aggregations or joins, or the source can change through updates and deletes rather than only growing

They advise against a materialized view when records should only be processed once, for example on a source that does not retain history, such as Kafka, or for Auto Loader ingestion. Use a streaming table there.

How is a materialized view refreshed?

In open-source SDP, spark-pipelines run performs a default incremental update when no refresh option is given. --refresh orders updates the listed datasets without clearing existing data. --full-refresh orders clears their data and checkpoints and recomputes them from scratch.
On Databricks, a default refresh updates the view to match its defining query and performs an incremental refresh when that is more efficient. Incremental refresh needs a serverless pipeline, and a full refresh returns the same results. Run EXPLAIN CREATE MATERIALIZED VIEW to test whether a query can refresh incrementally. In Databricks SQL, REFRESH MATERIALIZED VIEW mv_name FULL forces a full refresh, and a REFRESH POLICY in the view definition can prefer incremental or full refreshes over the default cost-based choice. In a Lakeflow pipeline, a materialized view can be updated in triggered or continuous pipeline mode, while standalone materialized views always run in triggered mode.

How does Databricks run materialized views?

Lakeflow Pipelines extend and are interoperable with SDP. A materialized view there is a Unity Catalog managed table, so consumers outside the pipeline can query it. You can also create standalone materialized views in a Databricks SQL warehouse, refresh them with REFRESH MATERIALIZED VIEW mv1;, and hide data with ROW FILTER and MASK clauses.

FAQs

Can a materialized view have more than one flow?

In Apache Spark SDP, no. The guide says a materialized view always has exactly one batch flow. To combine sources, read them all in the one defining query. The Databricks docs word it as "one or more materialized view flows" and add that you always define the flows implicitly as part of the materialized view definition.

Can other queries read a materialized view?

Yes. Inside a pipeline, reference it by name with spark.table, as the guide does with customer_orders. On Databricks, queries outside the pipeline can read it too.

What should stay out of a dataset function?

The function must return a DataFrame. The guide says never to save or write inside it and lists collect(), count(), pivot(), toPandas(), save(), saveAsTable(), start() and toTable() as methods to avoid.