Study guide

9.5 Enforcing Data Quality: Check Constraints & Table Invariants

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

9.5 Enforcing Data Quality: Check Constraints & Table Invariants

Key Takeaways Explain This Topic AI

Delta Lake Check Constraints enforce data quality rules at the storage/table metadata layer via ALTER TABLE <table> ADD CONSTRAINT <name> CHECK (<boolean_expression>).

NOT NULL constraints can be defined at table creation or added post-creation, preventing null insertions in primary keys, foreign keys, or mandatory business attributes.

Constraint enforcement is strictly atomic and transactional: if a single row in an INSERT, UPDATE, COPY INTO, or MERGE operation violates a check constraint, the entire transaction fails with an InvariantViolationException and rolls back.

When adding a check constraint to an existing populated table, Delta Lake verifies that all existing historical rows satisfy the constraint before committing the metadata change; if existing rows fail, the ADD CONSTRAINT command fails.

Table constraints differ fundamentally from Lakeflow (DLT) expectations: Delta constraints abort write transactions immediately, whereas Lakeflow expectations provide configurable policies (expect report only, expect or drop discard rows, expect or fail fail pipeline).

Last updated: August 2026

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?

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 .

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.

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

Scan Execution : Delta Lake scans the entire existing dataset to verify that all existing records satisfy the boolean expression.

If Existing Data Passes : The constraint metadata is successfully committed to _delta_log/ , and all future writes are subject to the constraint.

If Any Historical Record Fails : The ALTER TABLE ADD CONSTRAINT command fails immediately with an InvariantViolationException . The constraint is NOT added to the table.

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

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.

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.

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.

Loading diagram... Constraint Enforcement & ACID Transaction Rollback Lifecycle