10.2 Data Quality Expectations: expect, expect or drop, & expect or fail

10.2 Data Quality Expectations: expect, expect or drop, & expect or fail

DP-750 Exam Focus: Master the declarative Data Quality Expectations framework in Delta Live Tables. Understand the distinct behavioral guarantees, target table row outcomes, and pipeline lifecycle impacts of the three core constraint actions: EXPECT (Warn), EXPECT ... ON VIOLATION DROP ROW, and EXPECT ... ON VIOLATION FAIL UPDATE. Learn how to configure single and multi-expectation decorators in Python (@dlt.expect_all) and SQL, monitor validation metrics in the event log, and implement the enterprise Quarantine Table pattern.

---

1. The Declarative Data Quality Framework

In traditional ETL and streaming architectures, validating data quality requires writing custom assertions, running post-ingestion validation scripts, or embedding defensive CASE WHEN logic inside every SQL transformation. These approaches suffer from severe drawbacks:

• Silent Failures: Invalid records pollute downstream analytics if validation scripts are decoupled from the ingestion transaction.
• Lack of Observability: Metrics on dropped or bad rows are scattered across disparate log files rather than centralized in queryable governance tables.
• Pipeline Brittle Design: Hard-coded assertion exceptions in Spark UDFs cause pipeline crashes without diagnostic context.

Delta Live Tables (DLT) provides a native, declarative data quality framework called Expectations. Expectations allow data engineers to define data quality rules directly on Streaming Tables, Materialized Views, and Temporary Views using standard SQL boolean expressions.

+---------------------------------------------------------------------------------------------------------+
|                                 DLT EXPECTATION ENFORCEMENT PIPELINE                                    |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|                                     Incoming Raw Data Stream                                            |
|                                                |                                                        |
|                                                v                                                        |
|                     +-----------------------------------------------------+                             |
|                     |           EVALUATE EXPECTATION CONSTRAINTS          |                             |
|                     |   - CONSTRAINT valid_id EXPECT (order_id IS NOT NULL) |                             |
|                     |   - CONSTRAINT valid_amt EXPECT (amount > 0)        |                             |
|                     +-----------------------------------------------------+                             |
|                                                |                                                        |
|            +-----------------------------------+-----------------------------------+                    |
|            |                                   |                                   |                    |
|            v                                   v                                   v                    |
|    [ ACTION: WARN ]                   [ ACTION: DROP ROW ]                [ ACTION: FAIL UPDATE ]       |
|    `EXPECT (condition)`              `ON VIOLATION DROP ROW`             `ON VIOLATION FAIL UPDATE`     |
|    - Write row to target             - Discard row from target           - Halt pipeline update         |
|    - Log metric to Event Log         - Log drop metric to Event Log      - Fail & abort transaction     |
|    - Target receives ALL rows        - Target receives CLEAN rows        - Target receives NO rows      |
|                                                                                                         |
+---------------------------------------------------------------------------------------------------------+

---

2. The Three Core Expectation Actions

DLT classifies expectation handling into three distinct operational behaviors based on the severity of data quality violations.

1. Warn / Track Metrics (EXPECT)

• Behavior: Evaluates the boolean condition. If a record violates the rule, the record is retained and written into the target table.
• Pipeline Status: The pipeline continues running normally.
• Telemetry: The violation count, total record count, and passing percentage are recorded in the pipeline event log and displayed in the DLT UI.
• Best Use Case: Non-critical data hygiene checks, telemetry tracking, anomaly monitoring, or tracking nulls in optional attributes (e.g., user_middle_name).

2. Drop Invalid Rows (EXPECT ... ON VIOLATION DROP ROW)

• Behavior: Evaluates the condition. If a record fails the condition, the record is silently dropped and excluded from the target table.
• Pipeline Status: The pipeline continues running without interruption.
• Telemetry: The count of dropped rows is recorded in the event log.
• Best Use Case: Ingesting messy external telemetry or web clickstreams where malformed rows (e.g., missing timestamps or negative prices) should be filtered out to prevent downstream Silver layer corruption without blocking valid traffic.

3. Fail Pipeline Update (EXPECT ... ON VIOLATION FAIL UPDATE)

• Behavior: Evaluates the condition. If even a single record violates the condition, the DLT engine immediately aborts the update transaction and fails the pipeline run.
• Pipeline Status: The pipeline enters an error state. No records from the failing micro-batch are committed to the target table (guaranteeing transactional atomicity).
• Telemetry: The fatal error and the failing constraint expression are written to the event log.
• Best Use Case: Regulatory, financial, or strict compliance datasets where unverified data cannot enter the lakehouse under any circumstances (e.g., null primary keys in general ledger accounts, or mismatched currency codes in bank transactions).

Action Comparison Matrix

---

3. SQL Syntax for Data Quality Expectations

In SQL, expectations are declared as CONSTRAINT clauses immediately following the table definition and before the AS SELECT statement.

-- Complete SQL Table Definition with Mixed Expectations
CREATE OR REFRESH STREAMING TABLE silver_financial_transactions (
    CONSTRAINT valid_tx_id 
        EXPECT (transaction_id IS NOT NULL) 
        ON VIOLATION FAIL UPDATE,
        
    CONSTRAINT positive_transfer_amount 
        EXPECT (transfer_amount > 0.00) 
        ON VIOLATION DROP ROW,
        
    CONSTRAINT reasonable_fee_ratio 
        EXPECT (processing_fee <= transfer_amount * 0.10)
)
COMMENT "Financial transactions with multi-tiered data quality enforcement"
AS SELECT 
    transaction_id,
    account_id,
    transfer_amount,
    processing_fee,
    currency_code,
    event_timestamp
FROM STREAM(LIVE.bronze_financial_feed);

---

4. Python Syntax: Single and Multi-Expectation Decorators

The Python dlt library provides specialized decorators for individual rules as well as dictionary-based decorators for multi-rule validation.

Single Rule Decorators

• @dlt.expect("rule_name", "boolean_condition")
• @dlt.expect_or_drop("rule_name", "boolean_condition")
• @dlt.expect_or_fail("rule_name", "boolean_condition")

Multi-Rule Decorators (expect_all)

When applying dozens of data quality rules, individual decorators can clutter code. Python DLT supports dictionary-mapped decorators:

import dlt
from pyspark.sql.functions import col

# Define expectation dictionaries
rules_warn = {
    "valid_email": "customer_email RLIKE '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'",
    "phone_not_null": "customer_phone IS NOT NULL"
}

rules_drop = {
    "valid_age": "customer_age >= 18 AND customer_age <= 120",
    "valid_signup_date": "signup_date <= current_date()"
}

rules_fail = {
    "pk_not_null": "customer_id IS NOT NULL",
    "account_status_valid": "account_status IN ('ACTIVE', 'PENDING', 'SUSPENDED', 'CLOSED')"
}

@dlt.table(
    name="silver_customers",
    comment="Customer profiles validated with multi-rule expectations"
)
@dlt.expect_all(rules_warn)             # Warn on invalid email/phone
@dlt.expect_all_or_drop(rules_drop)     # Drop invalid age or future signup dates
@dlt.expect_all_or_fail(rules_fail)     # Fail pipeline if customer_id is null or status invalid
def silver_customers():
    return dlt.read_stream("bronze_customers_raw")

---

5. The Advanced Quarantine Table Pattern

A critical limitation of ON VIOLATION DROP ROW is that dropped records disappear from the target table. While the event log records how many rows were dropped, it does not persist the payload of those dropped records. If data engineers need to analyze, audit, or repair invalid records, they must implement the Quarantine Pattern.

Quarantine Architecture Principles

1. Read the raw stream once into a Temporary Streaming Live View (@dlt.view or CREATE TEMPORARY STREAMING LIVE VIEW).
2. Create the Clean Silver Table with EXPECT ... ON VIOLATION DROP ROW.
3. Create a parallel Quarantine Streaming Table that filters for the exact inverse boolean condition (WHERE NOT (rules)).
4. Persisting invalid records into the quarantine table allows upstream producers to review malformed records without halting the production pipeline.

                             QUARANTINE TABLE DESIGN PATTERN

                                  +-----------------------+
                                  |  bronze_raw_orders    |
                                  |   (Streaming Table)   |
                                  +-----------------------+
                                              |
                                              v
                                  +-----------------------+
                                  |   temp_orders_view    |
                                  | (Temporary Live View) |
                                  +-----------------------+
                                              |
                       +----------------------+----------------------+
                       |                                             |
                       v                                             v
        [ Positive Rules: DROP ROW ]                 [ Inverted Rules: WHERE NOT ]
        +------------------------------+             +------------------------------+
        |      silver_clean_orders     |             |   quarantine_invalid_orders  |
        | - Validated target dataset   |             | - Stores failed payloads     |
        | - Consumed by BI / Gold      |             | - Diagnostic triage & audit  |
        +------------------------------+             +------------------------------+

Python Implementation of Quarantine Pattern

import dlt
from pyspark.sql.functions import col, current_timestamp

# 1. Base Temporary View (Reads raw stream once)
@dlt.view(
    name="orders_staged_view"
)
def orders_staged_view():
    return dlt.read_stream("bronze_raw_orders")

# 2. Clean Silver Table (Drops violating rows)
@dlt.table(
    name="silver_clean_orders",
    comment="Clean order records passing all critical business checks"
)
@dlt.expect_or_drop("valid_order_id", "order_id IS NOT NULL")
@dlt.expect_or_drop("valid_amount", "order_amount > 0")
def silver_clean_orders():
    return dlt.read_stream("orders_staged_view")

# 3. Quarantine Error Table (Captures rejected rows with failure metadata)
@dlt.table(
    name="silver_quarantine_orders",
    comment="Quarantined invalid order records for audit and triage"
)
def silver_quarantine_orders():
    return (
        dlt.read_stream("orders_staged_view")
            .filter("(order_id IS NULL) OR (order_amount <= 0) OR (order_amount IS NULL)")
            .withColumn("quarantine_timestamp", current_timestamp())
    )