Streaming Tables & Materialized Views in DLT

1. The Two Core DLT Dataset Types

As a quick refresher, DLT pipelines are built by defining datasets that depend on one another. DLT provides two types of datasets to store the results of your transformations: streaming tables and materialized views. Each serves a distinct purpose in a data pipeline.

Let's start with a high-level overview from Databricks that frames the distinction between these two concepts.

Now, let's dissect each of these, starting with streaming tables.

2. Deep Dive: Streaming Tables

A streaming table is a Delta table designed for append-only, incremental processing. It's the workhorse for handling data that grows over time, like logs, IoT events, or change data capture (CDC) feeds.

The key behavioral characteristic is that a streaming table processes data from its source incrementally. When the pipeline runs, it only reads the new data that has arrived since the last update. This makes it highly efficient and cost-effective for large, ever-growing datasets.

Use Cases and Implementation

Streaming tables are ideal for:

• Ingestion (Bronze Layer): Landing raw data from sources like cloud storage (using Auto Loader) or message queues.
• Low-Latency Transformations (Silver Layer): Performing simple, append-only transformations like filtering records, casting data types, or adding metadata columns.

Let's look at the implementation again, but this time with the behavior in mind.

Python Implementation:
To create a streaming table in Python, your function must return a streaming DataFrame. The key is using spark.readStream.

import dlt

@dlt.table(
  comment="Raw sensor readings from cloud files, processed incrementally."
)
def sensor_readings_bronze():



  # The use of readStream tells DLT this will be a streaming table
  return (
    spark.readStream.format("cloudFiles")
      .option("cloudFiles.format", "json")
      .load("/path/to/raw/sensor_data/")
  )

SQL Implementation:
In SQL, you explicitly use the CREATE STREAMING TABLE syntax.

CREATE OR REFRESH STREAMING TABLE sensor_readings_bronze
COMMENT "Raw sensor readings from cloud files, processed incrementally."
AS SELECT * FROM STREAM read_files('/path/to/raw/sensor_data/', format => 'json');

Notice the STREAM keyword when reading the source—this is the SQL equivalent of readStream.

This video segment provides more detail on the intended use and behavior of streaming tables.

3. Deep Dive: Materialized Views

A materialized view (MV) represents the pre-computed result of a query at a specific point in time. Unlike a streaming table that just appends new data, a materialized view is fully recomputed during each pipeline update to reflect the latest state of its upstream source tables.

Because they re-process their inputs, materialized views can handle complex logic that is difficult or impossible to express incrementally, such as aggregations that need to consider the entire dataset.

Use Cases and Implementation

Materialized views are best suited for:

• Aggregations and Business Logic (Gold Layer): Creating summary tables for BI dashboards and reporting (e.g., daily sales per store).
• Data Quality Enforcement: Joining datasets to validate records before downstream use.
• Speeding up Queries: Pre-calculating the results of expensive queries.

Python Implementation:
To create a materialized view, your function must return a batch DataFrame. You achieve this by using spark.read or dlt.read() to reference an upstream table.

import dlt
from pyspark.sql.functions import *

@dlt.table(
  comment="Daily average temperature per sensor, recomputed on each update."
)
def daily_avg_temp_gold():



  # dlt.read() performs a batch read on the upstream table
  return (
    dlt.read("sensor_readings_bronze")
      .groupBy(col("sensor_id"), window("timestamp", "1 day").alias("day"))
      .agg(avg("temperature").alias("avg_temp"))
  )

SQL Implementation:
In SQL, you use the CREATE MATERIALIZED VIEW syntax.

CREATE OR REFRESH MATERIALIZED VIEW daily_avg_temp_gold
COMMENT "Daily average temperature per sensor, recomputed on each update."
AS SELECT
  sensor_id,
  window(timestamp, '1 day') as day,
  avg(temperature) as avg_temp
FROM LIVE.sensor_readings_bronze
GROUP BY sensor_id, window(timestamp, '1 day');

The LIVE schema instructs DLT to read from another table within the same pipeline, and since it's a standard SELECT, DLT handles it as a batch query.

4. Head-to-Head Comparison

The choice between a streaming table and a materialized view fundamentally changes how your pipeline behaves, especially when handling changes in your source data.

This next video segment provides an excellent, in-depth comparison of their behavior, particularly regarding updates and deletes.

Based on what we've learned, here is a summary of the key behavioral differences:

5. Best Practices: Combining Them in a Pipeline

You don't choose one or the other; you use them together. A robust DLT pipeline leverages both streaming tables and materialized views for what they do best, often following the Medallion Architecture.

The best practice is clear and simple. Let's hear the final recommendation.

To reinforce this, let's review the Python implementation from the official documentation, which shows how both are used in a single pipeline.

Conclusion

In this lesson, we moved beyond syntax to explore the behavior and purpose of DLT's core dataset types. You are now equipped to make deliberate design choices in your pipelines.

Key Takeaways:

• Streaming Tables are for incremental, append-only processing. Use them for efficient data ingestion and simple transformations where you only need to process new data. They are the foundation of your Bronze and Silver layers.
• Materialized Views are for full, stateful transformations. Use them to create aggregated Gold tables for analytics and reporting. They automatically handle changes (updates, deletes) in source data by recomputing the results on each run.
• Best Practice: The most effective pipelines combine both. Use streaming tables to land and clean data efficiently, then build materialized views on top of them to serve your business use cases.

Preview of the Next Lesson:

Now that we know how to define the structure and behavior of our tables, the next step is to ensure the data within them is correct. In our next lesson, we will learn how to implement data quality constraints using DLT expectations (expect, expect or drop, expect or fail), a powerful feature for building trustworthy and reliable data pipelines.