10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views

10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views

DP-750 Exam Focus: Master the declarative data engineering paradigm in Azure Databricks. Understand the core architectural differences, execution semantics, and syntax for Streaming Tables versus Materialized Views across both SQL (CREATE OR REFRESH) and Python (@dlt.table). Learn how Delta Live Tables (DLT) and Lakeflow Declarative Pipelines resolve the Directed Acyclic Graph (DAG) using the LIVE virtual schema and dlt.read() / dlt.read_stream() APIs.

---

1. Declarative vs. Imperative Data Engineering

Traditional data engineering on Apache Spark relies on an imperative paradigm. In an imperative workflow, the data engineer is responsible for explicitly orchestrating every operational detail: creating and sizing compute clusters, managing checkpoint directories, handling cluster failures, configuring trigger intervals, sequencing task dependencies via orchestrators (such as Azure Data Factory or Apache Airflow), and writing boilerplate code for table optimization and vacuuming.

# Imperative Structured Streaming (Traditional Spark)
# The engineer must manually manage checkpoints, output modes, triggers, and target formats
(df_raw.writeStream
    .format("delta")
    .outputMode("append")
    .option("checkpointLocation", "abfss://checkpoints@adlsgen2.dfs.core.windows.net/orders/")
    .trigger(availableNow=True)
    .toTable("silver.sales.orders"))

The Declarative Lakeflow / DLT Paradigm

Delta Live Tables (DLT)—and the broader Lakeflow Declarative Pipelines engine—replaces manual plumbing with a declarative paradigm. Instead of writing code that defines how to execute a pipeline step-by-step, engineers write queries that define what datasets should exist and what transformations produce them. The underlying runtime engine assumes full responsibility for:

• Automated DAG Generation: Parsing dependencies across tables and views to determine the optimal execution sequence.
• State & Checkpoint Management: Automatically managing streaming checkpoints, watermarks, and state stores without manual storage path declarations.
• Compute Orchestration & Auto-Scaling: Provisioning, scaling, and terminating compute clusters based on workload demands.
• Data Quality Governance: Enforcing declarative expectations and logging validation metrics directly into an internal event log.
• Automatic Maintenance: Applying Delta table optimizations, file compactions, and metadata cleanups during pipeline execution.

+---------------------------------------------------------------------------------------------------------+
|                                 DECLARATIVE PIPELINE ENGINE ARCHITECTURE                                |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|   +-------------------------------------------------------------------------------------------------+   |
|   |                        DECLARATIVE DEFINITIONS (SQL & Python Source Files)                      |   |
|   | - CREATE OR REFRESH STREAMING TABLE bronze_events AS SELECT * FROM cloud_files(...)             |   |
|   | - @dlt.table: def silver_orders(): return dlt.read_stream("bronze_events").filter(...)          |   |
|   | - CREATE OR REFRESH MATERIALIZED VIEW gold_summary AS SELECT date, sum(amt) FROM LIVE.silver...|   |
|   +-------------------------------------------------------------------------------------------------+   |
|                                                  |                                                      |
|                                                  v                                                      |
|   +-------------------------------------------------------------------------------------------------+   |
|   |                     LAKEFLOW / DLT RUNTIME ENGINE & COMPILER RESOLUTION                         |   |
|   |  - Dependency Graph (DAG) Compilation     - Lineage Resolution via Virtual `LIVE` Namespace     |   |
|   |  - Automated Checkpointing & State Store  - Declarative Expectation & Quality Enforcement       |   |
|   |  - Serverless Auto-Scaling Compute        - Event Log Telemetry & SLA Tracking                  |   |
|   +-------------------------------------------------------------------------------------------------+   |
|                                                  |                                                      |
|                                                  v                                                      |
|   +-------------------------------------------------------------------------------------------------+   |
|   |                          TARGET DATASETS IN UNITY CATALOG (Delta Lake)                          |   |
|   |         Bronze (Streaming Tables)  ===>  Silver (Streaming Tables)  ===>  Gold (Materialized)  |   |
|   +-------------------------------------------------------------------------------------------------+   |
+---------------------------------------------------------------------------------------------------------+

---

2. Core Abstractions: Streaming Tables vs. Materialized Views

In Lakeflow Declarative Pipelines and Delta Live Tables, datasets are classified into two primary table abstractions: Streaming Tables and Materialized Views.

+---------------------------------------------------------------------------------------------------------+
|                          STREAMING TABLES VS. MATERIALIZED VIEWS AT A GLANCE                            |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|   STREAMING TABLE                                      MATERIALIZED VIEW                                |
|   - Append-only, incremental processing                - Full or incremental recomputation              |
|   - Source must be an append-only stream               - Source can be any table, view, or stream       |
|   - Reads each incoming record exactly once            - Computes complete result of the query          |
|   - Maintains state via checkpoints & watermarks       - Computes stateful aggregations, joins, dedup   |
|   - Ideal for Bronze/Silver Ingestion                  - Ideal for Gold KPIs, Star Schemas, Summaries   |
|                                                                                                         |
+---------------------------------------------------------------------------------------------------------+

1. Streaming Tables (STREAMING TABLE / @dlt.table with readStream)

A Streaming Table is an append-only Delta table designed for low-latency, incremental data processing. It processes incoming data from streaming sources (e.g., Auto Loader cloud_files(), Azure Event Hubs, Apache Kafka, or upstream Streaming Tables) without rescanning previously processed data.

• Incremental Consumption: A Streaming Table tracks which records have been ingested using internal streaming checkpoints. When the pipeline runs (either in Triggered or Continuous mode), only new records appended since the last update are read.
• Source Requirements: The upstream source must be an append-only stream. If an upstream table undergoes in-place UPDATE or DELETE operations, a downstream Streaming Table reading it via STREAM() or dlt.read_stream() will throw an analysis error unless specific stream-read options (such as skipChangeCommits) are configured.
• Operational Fit: Perfect for raw ingestion (Bronze layer), log enrichment, and filtering where data flows continuously and historical records do not change.

2. Materialized Views (MATERIALIZED VIEW / @dlt.table with dlt.read)

A Materialized View is a precomputed dataset whose contents are defined by a query over one or more base tables. When refreshed, the engine computes the query logic to reflect the latest state of upstream sources.

• Stateful & Complex Transformations: Materialized Views support arbitrary SQL queries, including non-additive aggregations (COUNT(DISTINCT customer_id)), complex multi-table joins, window functions (ROW_NUMBER() OVER (...)), and full-table deduplications.
• Recomputation Semantics: Depending on the query complexity and source capabilities, Materialized Views are updated either incrementally or through optimized full recomputation. Unlike Streaming Tables, they always reflect the latest current state of the source data.
• Operational Fit: Perfect for conformed dimensions, aggregated business marts (Gold layer), executive KPI dashboards, and analytics consumption layers queried by Power BI and SQL Warehouses.

Detailed Comparison Matrix

---

3. SQL Syntax for Declarative Pipelines

Delta Live Tables and Lakeflow Pipelines provide specialized SQL extensions for declaring tables and views.

Defining a Streaming Table in SQL

To ingest raw cloud storage files using Auto Loader (cloud_files) into a Bronze Streaming Table:

-- Bronze Layer: Incremental Auto Loader Streaming Table
CREATE OR REFRESH STREAMING TABLE bronze_orders
COMMENT "Raw order ingestion stream from ADLS Gen2 landing zone"
TBLPROPERTIES ("quality" = "bronze", "pipelines.autoOptimize.zOrderCols" = "order_id")
AS SELECT 
    *, 
    _metadata.file_name AS source_file,
    current_timestamp() AS ingest_time
FROM STREAM read_files(
    'abfss://landing@storageaccount.dfs.core.windows.net/orders/',
    format => 'json',
    header => 'true'
);

Syntax Note: In Databricks SQL Declarative Pipelines, you can use read_files() or cloud_files('path', 'format') inside FROM STREAM ... to trigger Auto Loader streaming ingestion.

Defining an Intermediate Silver Streaming Table in SQL

To read from an upstream Streaming Table, use the STREAM(LIVE.table_name) syntax:

-- Silver Layer: Incremental filtering and type casting
CREATE OR REFRESH STREAMING TABLE silver_orders
COMMENT "Cleaned and conformed orders stream"
AS SELECT 
    CAST(order_id AS BIGINT) AS order_id,
    CAST(customer_id AS BIGINT) AS customer_id,
    CAST(order_amount AS DECIMAL(10,2)) AS order_amount,
    TO_TIMESTAMP(order_timestamp) AS order_time,
    UPPER(TRIM(status)) AS order_status
FROM STREAM(LIVE.bronze_orders)
WHERE order_id IS NOT NULL;

Defining a Gold Materialized View in SQL

To aggregate data across the Silver layer into a Gold KPI dataset:

-- Gold Layer: Materialized View computing daily revenue aggregations
CREATE OR REFRESH MATERIALIZED VIEW gold_daily_sales_summary
COMMENT "Daily aggregated sales revenue and distinct customer counts"
AS SELECT 
    CAST(order_time AS DATE) AS sales_date,
    order_status,
    COUNT(DISTINCT customer_id) AS active_customers,
    COUNT(order_id) AS total_orders,
    SUM(order_amount) AS total_revenue,
    AVG(order_amount) AS avg_order_value
FROM LIVE.silver_orders
GROUP BY CAST(order_time AS DATE), order_status;

Temporary Live Views (TEMPORARY LIVE VIEW)

When a transformation is only needed as an intermediate calculation and should not be published as a queryable Delta table in Unity Catalog, declare it as a TEMPORARY LIVE VIEW:

-- Temporary intermediate view: Evaluated in memory / execution graph only
CREATE TEMPORARY STREAMING LIVE VIEW temp_filtered_orders
AS SELECT * 
FROM STREAM(LIVE.bronze_orders)
WHERE order_amount > 0;

---

4. Python Syntax for Declarative Pipelines (dlt Module)

In Python, declarative pipelines leverage the @dlt decorator library (import dlt). Functions decorated with @dlt.table or @dlt.view return PySpark DataFrames that the pipeline compiler evaluates.

import dlt
from pyspark.sql.functions import col, current_timestamp, upper, trim, to_timestamp

# 1. Bronze Streaming Table via Auto Loader
@dlt.table(
    name="bronze_orders_py",
    comment="Ingested raw orders using Auto Loader",
    table_properties={"quality": "bronze"}
)
def bronze_orders_py():
    return (
        spark.readStream
            .format("cloudFiles")
            .option("cloudFiles.format", "json")
            .option("cloudFiles.inferColumnTypes", "true")
            .load("abfss://landing@storageaccount.dfs.core.windows.net/orders/")
            .withColumn("ingest_timestamp", current_timestamp())
    )

# 2. Intermediate Temporary View
@dlt.view(
    name="temp_valid_orders"
)
def temp_valid_orders():
    return (
        dlt.read_stream("bronze_orders_py")
            .filter(col("order_id").isNotNull())
            .withColumn("status", upper(trim(col("status"))))
    )

# 3. Silver Streaming Table
@dlt.table(
    name="silver_orders_py",
    comment="Conformed silver orders dataset"
)
def silver_orders_py():
    return (
        dlt.read_stream("temp_valid_orders")
            .withColumn("order_amount", col("order_amount").cast("decimal(10,2)"))
            .withColumn("order_time", to_timestamp(col("order_timestamp")))
    )

# 4. Gold Materialized View (Batch DataFrame read)
@dlt.table(
    name="gold_customer_metrics_py",
    comment="Aggregated customer lifetime value summary"
)
def gold_customer_metrics_py():
    # Note: Using dlt.read() for batch read into Materialized View
    return (
        dlt.read("silver_orders_py")
            .groupBy("customer_id")
            .agg(
                {"order_amount": "sum", "order_id": "count"}
            )
            .withColumnRenamed("sum(order_amount)", "lifetime_spend")
            .withColumnRenamed("count(order_id)", "order_count")
    )

Exam Tip: In Python DLT pipelines, reading an upstream table incrementally requires dlt.read_stream("table_name"). Reading an upstream table as a static batch snapshot (for aggregations or joins in Materialized Views) requires dlt.read("table_name").

---

5. Lineage Graph & DAG Resolution Mechanics

When a Delta Live Tables pipeline is executed, the pipeline runtime does not simply execute files from top to bottom. Instead, it runs a two-phase compilation process:

                        DLT TWO-PHASE COMPILATION & EXECUTION

  +-------------------------------------------------------------------------+
  | PHASE 1: GRAPH INITIALIZATION & SYNTAX COMPILATION                      |
  | - Parses all Python and SQL files registered in the pipeline settings.  |
  | - Resolves virtual dataset references (`LIVE.table` and `dlt.read`).     |
  | - Constructs the Directed Acyclic Graph (DAG) of dataset dependencies.  |
  | - Validates that no circular references (e.g., A -> B -> A) exist.     |
  +-------------------------------------------------------------------------+
                                     |
                                     v
  +-------------------------------------------------------------------------+
  | PHASE 2: TOPOLOGICAL EXECUTION & STATE PERSISTENCE                      |
  | - Initializes compute cluster or acquires serverless workers.           |
  | - Traverses DAG in topological order: Leaf sources -> Bronze -> Silver  |
  | - Executes Streaming Tables incrementally with checkpointing.           |
  | - Computes Materialized Views and persists Delta Lake Parquet tables.    |
  | - Emits execution telemetry and quality metrics to the Event Log.       |
  +-------------------------------------------------------------------------+

The LIVE Virtual Schema Qualifier

In SQL pipeline queries, tables are referenced using the LIVE. schema qualifier (e.g., FROM LIVE.bronze_orders).

• Decoupling from Physical Targets: The LIVE qualifier acts as an abstract virtual namespace. It informs the compiler that the query references a dataset defined within the same pipeline.
• Environment Portability: Because the query does not hardcode physical catalogs or schemas (such as prod_catalog.sales_schema.bronze_orders), the pipeline definition can be deployed seamlessly across Development, Staging, and Production workspaces simply by changing the target catalog and schema in the pipeline settings.