Lakeflow Declarative Pipelines in Azure Databricks: Streaming Tables, Materialized Views, Expectations, Medallion Architecture, CDC with APPLY CHANGES, Pipeline Modes, and Production Patterns

Table of Contents

• What Are Lakeflow Declarative Pipelines?
• Core Syntax — SQL
• Core Syntax — Python
• Expectations — Data Quality Gates
• Medallion Architecture with Lakeflow
• CDC with APPLY CHANGES (SCD Type 1 and Type 2)
• Pipeline Modes — Triggered vs Continuous
• Pipeline Configuration and Deployment
• Monitoring and Observability
• Common Mistakes
• Interview Questions
• Wrapping Up

Our Streaming with Databricks post introduced Spark Structured Streaming for custom streaming pipelines. This post covers Lakeflow Declarative Pipelines (formerly Delta Live Tables / DLT) — Databricks’ managed framework for building reliable, maintainable batch and streaming pipelines declaratively. Instead of writing imperative Spark code that defines how to process data step by step, you define what transformations you want and Lakeflow handles the execution, ordering, retries, and data quality enforcement.

Analogy — A self-driving car vs a manual transmission. Spark Structured Streaming is a manual transmission — you control every gear shift (checkpoint management), every turn signal (trigger interval), and every lane change (watermark handling). Lakeflow Declarative Pipelines is a self-driving car — you tell it the destination (the SQL/Python transformation), and it figures out the route (execution order), handles traffic (retries and error recovery), follows road rules (expectations/data quality), and parks itself (auto-scaling compute). You get the same result with far less code and far fewer operational headaches.

What Are Lakeflow Declarative Pipelines?

Lakeflow Declarative Pipelines provide three core building blocks for data pipelines: streaming tables (append-only, incremental processing), materialized views (precomputed query results that auto-refresh), and views (temporary, non-materialized transformations within a pipeline). You write SQL or Python that defines the transformation logic, and Lakeflow handles everything else: dependency resolution, execution ordering, compute management, error recovery, and data quality validation.

Concept	What It Is	When to Use
**Streaming table**	Append-only Delta table that processes data incrementally (new rows only)	Ingestion from files (AutoLoader), Kafka, Event Hubs, CDC feeds
**Materialized view**	Precomputed Delta table that auto-refreshes when source data changes	Aggregations, joins, any query that downstream consumers read frequently
**View**	Temporary transformation visible only within the pipeline (not materialized)	Intermediate steps, validation, breaking complex queries into stages
**Expectation**	Data quality constraint with configurable action (warn, drop, fail)	Null checks, range validation, uniqueness, business rules

Core Syntax — SQL

-- Bronze: Ingest raw CSV files using AutoLoader
CREATE OR REFRESH STREAMING TABLE bronze_orders
AS SELECT * FROM STREAM read_files(
  '/mnt/landing/orders/',
  format => 'csv',
  header => true,
  schema => 'order_id INT, product STRING, amount DOUBLE, order_date DATE, region STRING'
);

-- Silver: Clean and validate with expectations
CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT positive_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT valid_region EXPECT (region IN ('Ontario', 'Quebec', 'Alberta', 'BC')) ON VIOLATION FAIL UPDATE
)
AS SELECT
  order_id,
  TRIM(LOWER(product)) AS product,
  amount,
  order_date,
  region,
  current_timestamp() AS processed_at
FROM STREAM(LIVE.bronze_orders);

-- Gold: Aggregate into a materialized view
CREATE OR REFRESH MATERIALIZED VIEW gold_revenue_by_region
AS SELECT
  region,
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM LIVE.silver_orders
GROUP BY region, DATE_TRUNC('month', order_date);

Core Syntax — Python

from pyspark.pipelines import *

# Bronze: Ingest raw files
@table(name="bronze_orders")
@expect_all({"valid_id": "order_id IS NOT NULL"})
def bronze_orders():
    return (
        spark.readStream
        .format("cloudFiles")
        .option("cloudFiles.format", "csv")
        .option("header", "true")
        .schema("order_id INT, product STRING, amount DOUBLE, order_date DATE, region STRING")
        .load("/mnt/landing/orders/")
    )

# Silver: Clean and validate
@table(name="silver_orders")
@expect("positive_amount", "amount > 0", action="drop")
@expect("valid_region", "region IN ('Ontario','Quebec','Alberta','BC')", action="fail")
def silver_orders():
    return (
        spark.readStream.table("LIVE.bronze_orders")
        .withColumn("product", lower(trim(col("product"))))
        .withColumn("processed_at", current_timestamp())
    )

# Gold: Materialized view
@materialized_view(name="gold_revenue_by_region")
def gold_revenue():
    return (
        spark.table("LIVE.silver_orders")
        .groupBy("region", date_trunc("month", col("order_date")).alias("month"))
        .agg(
            count("*").alias("order_count"),
            sum("amount").alias("total_revenue"),
            avg("amount").alias("avg_order_value")
        )
    )

Expectations — Data Quality Gates

Expectations are the killer feature of Lakeflow Pipelines. They define data quality rules directly in your pipeline and enforce them at every run. Each expectation is a SQL boolean expression with one of three actions when a row fails.

Analogy — Airport security with three response levels. A passenger with a small water bottle (minor issue) gets a warning — noted but allowed through. A passenger with a prohibited item (medium issue) gets dropped — sent back, not allowed on the flight. A passenger with something dangerous (critical issue) triggers a full stop — the entire security line shuts down until the issue is resolved.

Action	SQL Syntax	Python Syntax	Behavior
**Warn** (log and continue)	`EXPECT (condition)`	`action=”allow”`	Record passes, violation logged in metrics
**Drop** (remove bad rows)	`EXPECT (condition) ON VIOLATION DROP ROW`	`action=”drop”`	Record removed from output, logged
**Fail** (halt pipeline)	`EXPECT (condition) ON VIOLATION FAIL UPDATE`	`action=”fail”`	Entire pipeline update fails, no data written

-- Multiple expectations on one table
CREATE OR REFRESH STREAMING TABLE validated_orders (
  -- Warn: log but allow (data quality metric only)
  CONSTRAINT has_product EXPECT (product IS NOT NULL),

  -- Drop: remove invalid rows silently
  CONSTRAINT positive_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT valid_date EXPECT (order_date >= '2020-01-01') ON VIOLATION DROP ROW,

  -- Fail: halt the entire pipeline if this breaks
  CONSTRAINT unique_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION FAIL UPDATE
)
AS SELECT * FROM STREAM(LIVE.bronze_orders);

Medallion Architecture with Lakeflow

Lakeflow Pipelines are purpose-built for the Bronze-Silver-Gold medallion architecture. Each layer is a separate streaming table or materialized view, connected by LIVE. references that Lakeflow resolves automatically.

Analogy — A water treatment plant (extended). Bronze is the intake tank (raw water, unfiltered). Silver is the filtration stage (sediment removed, bacteria killed, pH balanced). Gold is the bottled water on store shelves (packaged, labeled, ready for consumption). Lakeflow Pipelines automate the entire plant — water flows from tank to filter to bottle without manual intervention.

-- BRONZE: Raw ingestion (append-only, schema-on-read)
CREATE OR REFRESH STREAMING TABLE bronze_events
AS SELECT *, _metadata.file_path AS source_file, current_timestamp() AS ingested_at
FROM STREAM read_files('/mnt/landing/events/', format => 'json');

-- SILVER: Cleaned, typed, deduplicated
CREATE OR REFRESH STREAMING TABLE silver_events (
  CONSTRAINT not_null_event_id EXPECT (event_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT valid_event_type EXPECT (event_type IN ('click','purchase','view')) ON VIOLATION DROP ROW
)
AS SELECT
  CAST(event_id AS BIGINT) AS event_id,
  LOWER(TRIM(event_type)) AS event_type,
  CAST(user_id AS BIGINT) AS user_id,
  CAST(event_timestamp AS TIMESTAMP) AS event_timestamp,
  amount,
  ingested_at
FROM STREAM(LIVE.bronze_events);

-- GOLD: Business-ready aggregations
CREATE OR REFRESH MATERIALIZED VIEW gold_daily_metrics
AS SELECT
  DATE(event_timestamp) AS event_date,
  event_type,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users,
  SUM(CASE WHEN event_type = 'purchase' THEN amount ELSE 0 END) AS revenue
FROM LIVE.silver_events
GROUP BY DATE(event_timestamp), event_type;

CDC with APPLY CHANGES (SCD Type 1 and Type 2)

Lakeflow Pipelines have built-in support for Change Data Capture using APPLY CHANGES INTO. This automatically handles inserts, updates, and deletes from a CDC source (like Debezium or Lakeflow Connect) and materializes the results as either SCD Type 1 (latest value only) or SCD Type 2 (full history).

-- Create a streaming table as the CDC target
CREATE OR REFRESH STREAMING TABLE customers_scd1;

-- Apply CDC changes (SCD Type 1 -- overwrite with latest)
APPLY CHANGES INTO LIVE.customers_scd1
FROM STREAM(LIVE.bronze_cdc_customers)
KEYS (customer_id)
SEQUENCE BY updated_at
COLUMNS * EXCEPT (_metadata, _rescued_data)
STORED AS SCD TYPE 1;

-- SCD Type 2 -- keep full history with start/end dates
CREATE OR REFRESH STREAMING TABLE customers_scd2;

APPLY CHANGES INTO LIVE.customers_scd2
FROM STREAM(LIVE.bronze_cdc_customers)
KEYS (customer_id)
SEQUENCE BY updated_at
COLUMNS * EXCEPT (_metadata, _rescued_data)
STORED AS SCD TYPE 2;
-- Automatically adds __START_AT, __END_AT, __IS_CURRENT columns

Pipeline Modes — Triggered vs Continuous

Mode	Behavior	Best For	Cost
**Triggered**	Runs once, processes all available data, then stops	Scheduled batch loads (hourly, daily)	Lower (cluster terminates after)
**Continuous**	Runs indefinitely, processes data as it arrives	Real-time streaming, low-latency requirements	Higher (cluster always running)

Triggered mode (default):
  Schedule: Every hour via Lakeflow Jobs
  Behavior: Start cluster -> Process new data -> Stop cluster
  Latency: Minutes to hours
  Cost: Pay only when running

Continuous mode:
  Schedule: Always running
  Behavior: Cluster stays alive, processes micro-batches continuously
  Latency: Seconds to minutes
  Cost: Pay continuously (use serverless to optimize)

Pipeline Configuration and Deployment

Lakeflow Pipelines are created in the Databricks UI or via the REST API. Key configuration options include the target catalog and schema (Unity Catalog destination), compute settings, and pipeline mode.

Creating a pipeline:
  1. Workspace -> Pipelines -> Create Pipeline
  2. Name: "orders_medallion_pipeline"
  3. Source code: Select notebook(s) with SQL/Python definitions
  4. Destination:
     - Catalog: prod_catalog
     - Schema: orders
  5. Compute:
     - Use serverless compute (recommended)
     - Or: configure cluster size, Photon, autoscaling
  6. Pipeline mode: Triggered (for batch) or Continuous (for streaming)
  7. Click "Start" to run the first update

Scheduling via Lakeflow Jobs:
  1. Workflows -> Create Job
  2. Add task: type = "Pipeline"
  3. Select your pipeline
  4. Set schedule (cron) or trigger (file arrival)

Monitoring and Observability

Lakeflow Pipelines provide built-in monitoring through the pipeline UI: a visual DAG showing all tables and their dependencies, real-time status of each table (running, completed, failed), data quality metrics (expectations pass/fail counts), and detailed logs for troubleshooting.

Pipeline Event Log:
  - Records every update: start time, end time, rows processed, rows dropped
  - Expectation metrics: how many rows passed/failed each constraint
  - Error details: stack traces for failed updates
  - Accessible via: system.lakeflow.events (Unity Catalog system table)

Querying pipeline metrics:
  SELECT
    pipeline_name,
    expectation_name,
    passed_records,
    failed_records,
    ROUND(failed_records * 100.0 / (passed_records + failed_records), 2) AS failure_pct
  FROM system.lakeflow.events
  WHERE event_type = 'expectation'
  ORDER BY timestamp DESC;

Common Mistakes

1. Using streaming tables for aggregations. Streaming tables are append-only — they do not recompute. If you need GROUP BY, SUM, or JOIN that should refresh when source data changes, use a materialized view. Streaming tables are for ingestion; materialized views are for aggregations.

2. Setting all expectations to FAIL UPDATE. If every minor data quality issue halts the pipeline, you will spend more time restarting pipelines than processing data. Use FAIL only for critical constraints (null primary keys, schema violations). Use DROP for fixable issues (invalid values) and WARN for monitoring (unexpected patterns).

3. Not using STREAM() for streaming table sources. When reading from another streaming table, you must wrap the source in STREAM(LIVE.table_name). Without STREAM(), Lakeflow treats it as a batch read and reprocesses all data on every update instead of processing only new rows.

4. Putting all transformations in one table. A single table with 20 transformations is hard to debug, test, and monitor. Break complex logic into multiple tables (bronze, silver intermediate, silver final, gold) so each step is independently observable and expectations can validate intermediate results.

5. Not using Unity Catalog as the destination. Lakeflow Pipelines can publish to either the Hive metastore (legacy) or Unity Catalog. Always use Unity Catalog for governance, lineage tracking, and compatibility with the rest of your Databricks platform.

6. Ignoring the event log. The pipeline event log records exactly how many rows passed and failed each expectation. If you are not querying system.lakeflow.events, you are missing data quality trends that predict pipeline failures before they become critical.

7. Running continuous mode for batch workloads. Continuous mode keeps a cluster running 24/7. If your data arrives hourly or daily, triggered mode (scheduled via Lakeflow Jobs) processes data and shuts down, costing a fraction of continuous mode.

8. Manually ordering pipeline steps. Lakeflow automatically resolves dependencies from LIVE. references. You do not need to specify execution order — if silver reads from LIVE.bronze, Lakeflow runs bronze first. Do not try to force ordering with external orchestration.

Interview Questions

Q: What is the difference between a streaming table and a materialized view in Lakeflow Pipelines? A: A streaming table is append-only — it processes new rows incrementally and never recomputes old data. Use it for ingestion (AutoLoader, Kafka, CDC). A materialized view is a precomputed query result that automatically refreshes when source data changes — it recomputes the entire result. Use it for aggregations, joins, and any transformation that downstream consumers query frequently. The key distinction: streaming tables process deltas (new data only); materialized views recompute the full result.

Q: What are expectations and what are the three violation actions? A: Expectations are data quality constraints defined on pipeline tables. Each expectation is a SQL boolean expression. The three actions on violation: WARN (log the failure, allow the row through — for monitoring), DROP ROW (remove the failing row from the output — for recoverable issues), and FAIL UPDATE (halt the entire pipeline update — for critical constraints like null primary keys). Expectations are logged in the pipeline event log for monitoring and auditing.

Q: How does APPLY CHANGES work for CDC in Lakeflow Pipelines? A: APPLY CHANGES INTO automatically processes a CDC feed (inserts, updates, deletes) and materializes the result as a Delta table. You specify the target table, source stream, key columns, and sequence column (to order changes). It supports SCD Type 1 (latest value only — overwrites) and SCD Type 2 (full history — adds start/end date columns and an is_current flag). This replaces manual Delta MERGE logic for CDC workloads.

Q: What is the difference between triggered and continuous pipeline modes? A: Triggered mode runs once (processes all available new data, then stops). It is cheaper because compute shuts down between runs. Schedule it via Lakeflow Jobs for hourly or daily batch loads. Continuous mode runs indefinitely, processing data as it arrives in micro-batches. It provides lower latency (seconds vs minutes) but costs more because compute is always running. Use continuous for real-time requirements; triggered for everything else.

Q: How does Lakeflow Pipelines handle dependency resolution? A: When you reference LIVE.table_name in a query, Lakeflow automatically builds a dependency graph (DAG). It determines execution order, runs upstream tables before downstream tables, and parallelizes independent branches. You never need to specify execution order explicitly — it is derived from the LIVE. references in your SQL or Python code.

Q: How would you implement the medallion architecture with Lakeflow Pipelines? A: Bronze: streaming table ingesting raw data with AutoLoader (read_files()), minimal transformation, add metadata columns. Silver: streaming table reading from STREAM(LIVE.bronze), applying expectations for data quality, cleaning column names, casting types, deduplicating. Gold: materialized view reading from LIVE.silver, performing aggregations (GROUP BY, SUM, COUNT) for business consumption. Each layer is defined in the same pipeline notebook, and Lakeflow resolves the dependency chain automatically.

Q: Why should you use Lakeflow Declarative Pipelines instead of writing custom Spark Structured Streaming code? A: Lakeflow handles infrastructure that you would otherwise code manually: automatic dependency resolution (no explicit DAG definition), built-in data quality enforcement (expectations), automatic retry and error recovery, visual monitoring with the pipeline DAG UI, built-in CDC support (APPLY CHANGES), and serverless compute management. Custom Structured Streaming gives more control but requires you to manage checkpoints, trigger intervals, error handling, and monitoring yourself. Use Lakeflow for standard ETL patterns; use custom streaming for advanced scenarios that Lakeflow cannot express.

Wrapping Up

Lakeflow Declarative Pipelines are how modern Databricks data engineering teams build production pipelines. Define your transformations in SQL or Python, attach expectations for data quality, and let Lakeflow handle execution, ordering, retries, and monitoring. Start with the medallion architecture: bronze streaming table for ingestion, silver streaming table for cleaning, gold materialized view for aggregation. Use expectations at the silver layer to catch bad data before it reaches gold. And always use Unity Catalog as your destination for full governance and lineage.

Related posts: — Streaming with Databricks — AutoLoader (cloudFiles) — SCD Type 1 & 2 with Delta MERGE — Data Quality Framework — Delta Lake Deep Dive

---

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.