LinkedIn
Problem Statement
Modern analytics platforms require scalable and cost-efficient ways to maintain continuously updated reporting datasets. Traditional ETL pipelines often rely on complex orchestration, MERGE logic, and custom incremental processing frameworks that become difficult to maintain at scale.
Databricks Materialized Views simplify this challenge through declarative data engineering and automated refresh optimization on Delta Lake.
Abstract
Databricks Materialized Views (MVs) provide a declarative approach for building and maintaining analytical datasets on Delta Lake. Instead of manually implementing MERGE logic, orchestration, dependency handling, and refresh management, Databricks automatically manages these operational concerns.
When combined with Lakeflow Declarative Pipelines, Materialized Views become scalable, governed analytical assets capable of efficient refresh execution, observability, and optimized query performance.
The Challenge of Incremental Analytics
Traditional data pipelines often require custom watermarking, MERGE logic, orchestration, retries, and dependency management. As data platforms scale, these implementations become increasingly difficult to maintain and expensive to operate.
Large datasets are frequently reprocessed even when only a small subset of records has changed, resulting in higher compute costs and longer execution times.
Declarative Data Engineering with Databricks
Databricks addresses these challenges through a declarative data engineering model. In this approach, engineers define the target dataset while the platform determines how the data should be processed and maintained.
Materialized Views are a key construct in this architecture. Engineers can define transformations using SQL or Python while Databricks manages incremental refresh logic, query optimization, execution planning, and dependency tracking behind the scenes.
Databricks Materialized Views
A Databricks Materialized View is a physically materialized Delta table defined through a SQL query. The platform manages refresh execution logic, dependency tracking, and optimization behavior, while refresh scheduling can be configured through SQL jobs, Workflows, or Lakeflow Declarative Pipelines.
Materialized View Patterns
Databricks supports Materialized Views in two primary deployment patterns: Standalone Materialized Views created directly in Databricks SQL, and Materialized Views managed within Lakeflow Declarative Pipelines. These two approaches serve different architectural and operational requirements.
Standalone Materialized Views are suitable for analytical workloads that require precomputed datasets with managed refresh behavior. They are commonly used when the transformation logic is relatively self-contained and does not require broader pipeline orchestration.
Materialized Views managed in Lakeflow Declarative Pipelines are better suited for more complex data engineering scenarios. In this model, Materialized Views are incorporated into declarative pipelines, where they can participate in dependency management, incremental processing, and coordinated execution across related data assets and pipelines.
The selection between these approaches should be based on workload complexity, refresh requirements, and governance considerations.
Incremental Processing Model
Databricks Materialized Views use incremental processing whenever the SQL logic and source Delta tables support it. Instead of recomputing entire datasets during refresh operations, Databricks tracks changes through Delta Lake transaction logs and updates only the affected rows, partitions, or aggregation groups. This significantly improves refresh performance while reducing compute costs and latency.
During refresh execution, Databricks compares the latest source table state with the previously processed version stored in the Materialized View metadata and applies only the detected changes to the persisted result set.
Incremental maintenance is most effective when source datasets are stored as Delta tables because Databricks relies on Delta transaction logs for efficient change tracking.
However, incremental refresh is not supported for all query patterns. Certain operations — including non-deterministic functions (eg., CURRENT_TIMESTAMP(), UUID()), complex window functions, unbounded aggregations, and schema changes — may require a complete recomputation of the Materialized View to ensure correctness and consistency.
Use Case: Near Real-Time Retail Intelligence with Materialized Views
A global retail enterprise processes millions of transactions daily from e-commerce, mobile, and physical stores through Kafka and CDC pipelines. Raw events are ingested into the Bronze layer for auditability and replayability, then refined in the Silver layer through cleansing, enrichment, deduplication, and SCD processing.
In the Gold layer, Databricks Materialized Views provide optimized analytical datasets for dashboards and reporting. By incrementally refreshing only affected aggregations instead of recalculating entire datasets, Materialized Views enable scalable, low-latency analytics with improved performance and reduced compute costs.
Lakehouse Architecture
Materialized Views are best positioned in the Gold layer of a Medallion Architecture, where they serve as BI-ready analytical datasets. These Materialized Views can be directly consumed by BI platforms such as Power BI, Qlik, and other SQL-based reporting tools.
In this architecture, the Bronze layer focuses on raw ingestion and streaming capture, the Silver layer handles cleansing, standardization, deduplication, and SCD logic, while the Gold layer exposes business-ready aggregations and KPI datasets.
The organization can configure Lakeflow Declarative Pipelines in either Continuous or Triggered execution mode depending on latency and SLA requirements.
Continuous mode is typically used for low-latency analytical updates, while Triggered mode is preferred for scheduled batch-style refresh workloads.
eg., weekly sales per region
%python
# Create Materialized View using Lakeflow Declarative Pipelines
from pyspark.sql.functions import (date_trunc,sum as _sum,count as _count,col)
@dp.materialized_view(
name="weekly_sales",
comment="Weekly aggregated sales by region"
)
def weekly_sales():
return (
spark.read.table("silver.orders")
.groupBy(
"region",
date_trunc("week", col("order_timestamp")).alias("week")
)
.agg(
_sum("amount").alias("total_sales"),
_count("*").alias("order_count")
)
)
%sql
-- Create a Materialized View in standalone mode
CREATE OR REPLACE MATERIALIZED VIEW gold.weekly_sales AS
SELECT
region,
DATE_TRUNC('week', order_timestamp) AS week,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM silver.orders
GROUP BY
region,
DATE_TRUNC('week', order_timestamp);
REFRESH MATERIALIZED VIEW gold.weekly_sales;
In standalone mode, refresh operations can be executed manually or scheduled through Databricks Workflows and SQL jobs. This approach improves query performance and reduces compute overhead by allowing dashboards to query precomputed aggregations instead of repeatedly scanning large transactional datasets.
Databricks manages refresh execution and incremental maintenance logic, while refresh scheduling can be configured through SQL jobs, Workflows, or declarative pipelines.
This approach improves query performance and reduces compute overhead by allowing dashboards to query precomputed aggregations instead of repeatedly scanning large transactional datasets.
Standalone vs. Managed Materialized Views
|
Feature |
Standalone Materialized View |
Managed Materialized View |
|
Creation |
Created directly using SQL |
Created within Lakeflow Declarative Pipelines |
|
Refresh |
Manual or scheduled refresh |
Pipeline-managed refresh |
|
Orchestration |
External scheduling required |
Built-in pipeline orchestration |
|
Best For |
Simple reporting and aggregations |
Complex enterprise data pipelines |
|
Execution Mode |
Refresh-based |
Continuous or Triggered |
|
Dependency Management |
Limited |
Integrated dependency handling |
|
Typical Usage |
Independent BI datasets |
Coordinated Bronze/Silver/Gold workflows |
|
Cost |
Lower operational cost for simple workloads |
Higher operational cost but better scalability and automation |
Benefits
Materialized Views reduce engineering overhead by removing the need for custom incremental pipelines, watermarking, MERGE logic, and orchestration. They also improve compute efficiency by processing only changed data when possible, which makes them well suited for large analytical and near real-time workloads.
They further speed up development because teams can spend more time on business logic and less on maintaining pipeline infrastructure.
Limitations and Design Considerations
Materialized Views are optimized for analytical workloads rather than low-latency serving. Some queries or transformations cannot be incrementally refreshed, so Databricks may need to recompute them fully.
They are also not ideal for complex stateful logic such as SCD Type 2 handling, which is usually better implemented upstream in Silver-layer pipelines or Delta tables.
Materialized Views are optimized for analytical acceleration rather than sub-second transactional serving workloads.
Conclusion
Databricks Materialized Views simplify the creation of scalable analytical datasets by abstracting refresh orchestration, dependency management, and incremental execution logic.
When combined with Delta Lake and Lakeflow Declarative Pipelines, Materialized Views enable organizations to build governed, cost-efficient, and low-latency reporting layers without maintaining complex custom pipeline frameworks.
They are particularly effective in Gold-layer analytical workloads where precomputed aggregations, BI acceleration, and operational simplicity are critical.
