Study guide
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views
Key Takeaways Explain This Topic AI
Lakeflow Declarative Pipelines and Delta Live Tables (DLT) allow data engineers to define what data transformations to execute in SQL or Python while the runtime engine automatically manages DAG resolution, state tracking, and error recovery.
Streaming Tables process data incrementally from append-only streaming sources (such as Auto Loader, Kafka, or Event Hubs), maintaining internal checkpoint state and exactly-once processing guarantees without recomputing historical data.
Materialized Views precompute and persist the results of complex queries, aggregations, deduplications, and joins, refreshing incrementally or via full recomputation to deliver high-performance Gold-layer dimensional models and BI datasets.
Lineage graphs and execution Directed Acyclic Graphs (DAGs) are automatically resolved by the pipeline compiler using virtual schema qualifiers (LIVE in SQL or dlt.read() / dlt.read_stream() in Python), decoupling logic from physical catalog namespaces.
Temporary Live Views (TEMPORARY LIVE VIEW or @dlt.view) enable modular, intermediate data transformations within the pipeline execution graph without persisting intermediate datasets to underlying storage.
Last updated: August 2026
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
Technical Feature Streaming Table ( STREAMING TABLE ) Materialized View ( MATERIALIZED VIEW )
Primary Purpose Incremental, append-only data ingestion Precomputed queries, aggregations, and business metrics
Ingestion Engine Apache Spark Structured Streaming Spark Batch Engine / Incremental Materialization
Source Requirement Append-only stream (Auto Loader, Event Hubs, Delta CDF) Any Delta Table, Streaming Table, View, or external source
Processing Mode Reads only new/unprocessed records per micro-batch Evaluates full query logic against current source state
State Management Checkpoint directories & internal RocksDB state stores Persisted Delta table snapshot refreshed on schedule
Support for Non-Additive Aggs Restricted (requires watermarking and windowing) Fully supported without restrictions ( COUNT(DISTINCT) , AVG )
Handling of Upstream Updates/Deletes Fails or requires explicit stream-read bypass options Automatically reflected upon next pipeline refresh
Medallion Tier Fit Bronze Ingestion, Silver Cleansing & Filtering Silver Conformed Tables, Gold Star Schemas & KPI Marts
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.