9.5 Enforcing Data Quality: Check Constraints & Table Invariants

9.5 Enforcing Data Quality: Check Constraints & Table Invariants

DP-750 Exam Focus: Master Delta Lake table invariants and check constraints in Azure Databricks. Understand how to declare NOT NULL and boolean CHECK constraints using ALTER TABLE ADD CONSTRAINT, transaction atomicity and write-time abort behavior (InvariantViolationException), pre-commit historical validation rules, inspecting constraints via DESCRIBE DETAIL, and distinguishing table constraints from Lakeflow (Delta Live Tables) expectations.

---

1. Architectural Foundations of Delta Table Invariants

In enterprise lakehouses, data quality cannot rely solely on upstream client discipline. Multiple disparate pipelines, ad-hoc notebooks, streaming ingestion jobs, and external SQL users write concurrently to Silver and Gold Delta tables. If invalid, out-of-range, or corrupted data is written to storage, downstream models and reports become compromised.

Delta Lake Check Constraints (also known as Table Invariants) embed data quality validation rules directly into the table metadata stored in the Delta transaction log (_delta_log/).

+-------------------------------------------------------------------------------------+
|                     DELTA LAKE CONSTRAINT ENFORCEMENT ARCHITECTURE                  |
+-------------------------------------------------------------------------------------+
|                                                                                     |
|  [ Batch INSERT / MERGE INTO / Streaming Sink / COPY INTO / PySpark Write ]         |
|                                          |                                          |
|                                          v                                          |
|                     +-----------------------------------------+                     |
|                     |   Delta Lake Transaction Commit Engine  |                     |
|                     |   - Evaluates Check Constraints on Rows |                     |
|                     |   - Evaluates NOT NULL Invariants       |                     |
|                     +-----------------------------------------+                     |
|                                    /           \                                    |
|                    (All Rows Valid)             (>= 1 Row Violates Constraint)       |
|                           /                             \                           |
|                          v                               v                          |
|           +-------------------------------+    +---------------------------------+  |
|           | ACID Transaction Committed    |    | InvariantViolationException     |  |
|           | - New Parquet files indexed   |    | - Entire Transaction ABORTED    |  |
|           | - Commit recorded in delta log|    | - Complete Rollback (0 files)   |  |
|           +-------------------------------+    +---------------------------------+  |
+-------------------------------------------------------------------------------------+

Key Invariant Principles

• Storage-Level Enforcement: Constraints are enforced natively by Delta Lake regardless of what tool or API writes to the table (PySpark DataFrame API, Spark SQL, Databricks SQL Warehouse, Lakeflow Jobs, or Delta Rust API).
• Zero External Infrastructure: Validation logic requires no external servers, database triggers, or separate validation jobs.
• Zero Cost Reads: Check constraints are evaluated exclusively during write operations. Read queries incur zero performance overhead.

---

2. Declaring NOT NULL & Boolean CHECK Constraints

Delta Lake supports two primary categories of table invariants: NOT NULL constraints and arbitrary Boolean CHECK constraints.

1. NOT NULL Constraints

NOT NULL constraints prevent null values from being inserted into specified columns. They can be defined during CREATE TABLE or added to existing columns:

-- Option A: Defined at table creation
CREATE TABLE silver.finance.accounts (
    account_id BIGINT NOT NULL,
    account_number STRING NOT NULL,
    account_status STRING,
    created_at TIMESTAMP NOT NULL
)
USING DELTA;

-- Option B: Added to an existing table column
ALTER TABLE silver.finance.accounts 
ALTER COLUMN account_status SET NOT NULL;

2. Boolean CHECK Constraints

CHECK constraints allow defining arbitrary boolean expressions using standard Spark SQL syntax. The constraint name must be unique within the table.

-- Adding named CHECK constraints to a Delta table
ALTER TABLE silver.finance.accounts
ADD CONSTRAINT check_valid_status 
CHECK (account_status IN ('ACTIVE', 'PENDING', 'SUSPENDED', 'CLOSED'));

ALTER TABLE silver.finance.accounts
ADD CONSTRAINT check_positive_balance 
CHECK (current_balance >= 0.0);

ALTER TABLE silver.sales.orders
ADD CONSTRAINT check_valid_dates 
CHECK (ship_date >= order_date);

Supported Expressions & Limitations in Constraints

• Supported: Comparison operators (>, <, =, !=), logical operators (AND, OR, NOT), IN lists, LIKE, RLIKE, null checks (IS NOT NULL), and deterministic scalar functions (LENGTH(), UPPER(), DATE(), YEAR()).
• Unsupported: Subqueries (CHECK (col IN (SELECT id FROM other_table))), non-deterministic functions (CURRENT_TIMESTAMP(), RAND(), UUID()), and user-defined functions (UDFs).

---

3. Transaction Atomicity & Write-Time Violation Behavior

Delta Lake guarantees strict ACID transaction atomicity. When a write operation (e.g., INSERT INTO, COPY INTO, UPDATE, or MERGE INTO) executes against a table with check constraints, Delta Lake validates every single incoming record before committing the transaction.

What Happens on Constraint Violation?

1. Immediate Exception: If even a single row in an incoming batch of 10 million records violates a constraint, the Delta engine immediately aborts execution and throws an InvariantViolationException.
2. Complete Rollback: No data from the batch is committed. Any temporary staging Parquet files written to storage are orphaned and cleaned up, leaving the Delta table in its exact pre-transaction state.
3. Zero Partial Writes: Unlike traditional data lakes that might write partial corrupted partitions, Delta Lake guarantees that corrupt records never enter the table.

-- Example Error Output upon Constraint Violation:
Error in SQL statement: InvariantViolationException: CHECK constraint check_positive_balance (current_balance >= 0.0) violated by row with values:
 - current_balance : -142.50

Exam Tip: Delta Lake check constraints are binary and uncompromising. If an incoming batch contains 9,999 valid rows and 1 invalid row, the entire batch of 10,000 rows is rejected. There is no partial acceptance mode in Delta table check constraints.

---

4. Pre-Commit Historical Validation on Existing Tables

When applying ADD CONSTRAINT to a table that already contains data, Delta Lake performs a synchronous pre-validation scan across all existing historical rows:

-- Applying constraint to an existing populated table
ALTER TABLE silver.inventory.products
ADD CONSTRAINT check_unit_price_positive
CHECK (unit_price > 0.0);

Historical Pre-Validation Rules

1. Scan Execution: Delta Lake scans the entire existing dataset to verify that all existing records satisfy the boolean expression.
2. If Existing Data Passes: The constraint metadata is successfully committed to _delta_log/, and all future writes are subject to the constraint.
3. If Any Historical Record Fails: The ALTER TABLE ADD CONSTRAINT command fails immediately with an InvariantViolationException. The constraint is NOT added to the table.
4. Remediation Workflow: Before re-attempting ADD CONSTRAINT, data engineers must execute an UPDATE or DELETE statement to correct or remove non-compliant historical records.

-- Remediation: Clean existing historical data before re-adding constraint
UPDATE silver.inventory.products
SET unit_price = 0.01
WHERE unit_price <= 0.0 OR unit_price IS NULL;

-- Re-attempt constraint declaration
ALTER TABLE silver.inventory.products
ADD CONSTRAINT check_unit_price_positive
CHECK (unit_price > 0.0);

---

5. Constraint Lifecycle Management & Inspection

Data engineers must know how to inspect, verify, and drop constraints during table maintenance and schema migrations.

Inspecting Table Constraints

Table constraints are stored in Delta table properties and can be viewed using DESCRIBE DETAIL or DESCRIBE EXTENDED:

-- View table metadata including active constraints
DESCRIBE DETAIL silver.finance.accounts;

Inspect the properties map column in the output. Constraints appear as key-value pairs formatted as: delta.constraints.<constraint_name> = <expression>.

-- Alternative: View column-level nullability
DESCRIBE TABLE EXTENDED silver.finance.accounts;

Dropping Table Constraints

If a business rule changes or a constraint needs revision, drop the existing constraint using DROP CONSTRAINT:

-- Drop a named CHECK constraint
ALTER TABLE silver.finance.accounts
DROP CONSTRAINT check_valid_status;

-- Drop a NOT NULL constraint on a specific column
ALTER TABLE silver.finance.accounts
ALTER COLUMN account_status DROP NOT NULL;

---

6. Delta Check Constraints vs. Lakeflow Pipeline Expectations

Understanding when to use Delta Table Check Constraints versus Lakeflow (Delta Live Tables) Expectations is a frequent DP-750 exam scenario.

+-----------------------------------------------------------------------------------------+
|                    DELTA CHECK CONSTRAINTS VS. LAKEFLOW EXPECTATIONS                    |
+-----------------------------------------------------------------------------------------+
|                                                                                         |
|  1. DELTA TABLE CHECK CONSTRAINTS (Storage Level)                                       |
|     - Mechanism: Hard invariant embedded in _delta_log.                                 |
|     - Action on Failure: Hard abort (InvariantViolationException). Total rollback.     |
|     - Best For: Multi-writer tables, ad-hoc SQL tables, strict regulatory invariants.   |
|                                                                                         |
|  2. LAKEFLOW PIPELINE EXPECTATIONS (Pipeline Orchestration Level)                       |
|     - Mechanism: Declarative quality rules in Lakeflow DLT pipelines.                   |
|     - Action on Failure: Highly configurable policies:                                  |
|       * expect (Warn & Record Metric only; allows bad row to pass)                      |
|       * expect or drop (Silently discards bad row; writes valid rows)                   |
|       * expect or fail (Aborts pipeline run)                                            |
|     - Best For: Automated streaming/batch ingestion pipelines with error routing.       |
+-----------------------------------------------------------------------------------------+

Comparison Matrix

| Capability / Dimension | Delta Table Check Constraints | Lakeflow (DLT) Expectations | |:---|:---|:---|:---| | Enforcement Layer | Delta Lake Storage / Transaction Log | Lakeflow Pipeline Orchestration Engine | | Violation Actions | Single behavior: Hard abort & rollback | Three policies: Warn (expect), Drop (expect or drop), Fail (expect or fail) | | Partial Acceptance | Never (All-or-nothing atomicity) | Yes (With expect or drop, valid rows are committed) | | Scope of Protection| Protects table across ALL external writers | Enforced only within the specific Lakeflow pipeline | | Audit Telemetry | Logged as runtime exception in Spark logs | Automatically tracked in DLT Event Log & UI dashboard |

---

7. Best Practices for Enterprise Data Quality Enforcement

1. Enforce NOT NULL on Primary & Foreign Keys: Always apply NOT NULL constraints to entity identifiers and relational join keys in Silver and Gold tables to guarantee join integrity.
2. Keep Constraints Simple and Deterministic: Avoid overly complex string regex expressions in table constraints; offload heavy business validation to upstream Silver cleansing steps or Lakeflow expectations.
3. Combine with Unity Catalog Row Filters & Column Masks: Use Delta constraints to enforce physical data validity at write time, and layer Unity Catalog dynamic row filters and column masks for role-based data privacy at query time.