Delta Constraints for Quality Control: NOT NULL, CHECK, and DLT Expectations Compared

In data pipelines, quality issues like 'a NULL slipped into a non-nullable column' or 'a negative amount got through' propagate to downstream reports and ML models, causing massive rework. Delta Constraints let you define data quality rules at the table level and automatically reject any write that violates them.

This article covers the SQL syntax for NOT NULL and CHECK constraints, when to use them versus DLT Expectations, how to inspect and drop constraints, behavior during streaming writes, and the points you need to know for the exam.

NOT NULL Constraints

The simplest constraint. It prevents NULL values from being written to the specified column.

-- Specify NOT NULL at table creation
CREATE TABLE prod.silver.orders (
  order_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status STRING,
  created_at TIMESTAMP NOT NULL
);

-- Add a NOT NULL constraint to an existing table
ALTER TABLE prod.silver.orders
ALTER COLUMN status SET NOT NULL;

-- Drop a NOT NULL constraint
ALTER TABLE prod.silver.orders
ALTER COLUMN status DROP NOT NULL;

When adding NOT NULL to an existing table, ALTER TABLE will fail if NULL values already exist. Update or delete the NULL rows first, then add the constraint.

CHECK Constraints

CHECK constraints define a condition as a SQL expression and reject any row that violates it. You can define multiple CHECK constraints on the same table — only rows that satisfy every constraint are written.

-- Add a named CHECK constraint
ALTER TABLE prod.silver.orders
ADD CONSTRAINT chk_amount_positive CHECK (amount > 0);

ALTER TABLE prod.silver.orders
ADD CONSTRAINT chk_status_valid CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'));

ALTER TABLE prod.silver.orders
ADD CONSTRAINT chk_created_after_2020 CHECK (created_at >= '2020-01-01');

-- CHECK constraint with a compound condition
ALTER TABLE prod.silver.orders
ADD CONSTRAINT chk_cancelled_has_reason CHECK (
  status != 'cancelled' OR cancel_reason IS NOT NULL
);

-- Drop a CHECK constraint
ALTER TABLE prod.silver.orders
DROP CONSTRAINT chk_amount_positive;

When a constraint is violated, the entire transaction is rolled back. If even one row in a batch write violates a constraint, the entire batch is rejected.

Inspecting Constraints

-- List all constraints on a table
DESCRIBE DETAIL prod.silver.orders;
-- Constraint definitions live under delta.constraints.xxx in the properties column

-- Check NOT NULL from table info
DESCRIBE TABLE prod.silver.orders;
-- Columns where the nullable column shows false have a NOT NULL constraint

-- Inspect as table properties
SHOW TBLPROPERTIES prod.silver.orders;
-- delta.constraints.chk_amount_positive = 'amount > 0'
-- delta.constraints.chk_status_valid = 'status IN ...'

Comparison with DLT Expectations

-- DLT Expectations example (for reference)
CREATE OR REFRESH STREAMING LIVE TABLE silver_orders (
  CONSTRAINT valid_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT valid_status EXPECT (status IS NOT NULL) ON VIOLATION FAIL UPDATE
)
AS SELECT * FROM STREAM(LIVE.bronze_orders);

Behavior During Streaming Writes

Delta Constraints are also enforced during Structured Streaming micro-batch writes. If a micro-batch contains a row that violates a constraint, the entire batch is rolled back and the stream stops with an error.

• Constraint checks also apply when running MERGE inside foreachBatch
• Constraint checks also fire on writes from Auto Loader
• After the stream stops, you must fix the source of the bad data and restart the stream

In streaming pipelines, Delta Constraints' 'fail only' behavior can be too strict. A practical best practice is to drop bad records with DLT Expectations' DROP ROW and keep Delta Constraints as the final safety net.

Constraint Design Best Practices

• Always put NOT NULL on primary-key-like columns (e.g., order_id)
• Use CHECK constraints to enforce positive values on amounts and quantities (amount > 0)
• Use IN clauses to define enum-like constraints on status columns
• Give constraints descriptive names (we recommend a chk_ prefix)
• Before adding a constraint to an existing table, check that there are no violating rows
• Combine with DLT Expectations' DROP ROW for defense in depth

-- Check existing data for violations (run before adding the constraint)
SELECT COUNT(*) AS violation_count
FROM prod.silver.orders
WHERE amount <= 0;

SELECT COUNT(*) AS null_count
FROM prod.silver.orders
WHERE customer_id IS NULL;

Exam Focus Points

• Syntax for NOT NULL and CHECK constraints (ALTER TABLE ... ADD CONSTRAINT)
• Violation behavior: the entire transaction is rolled back
• Differences from DLT Expectations: scope and flexibility of violation behavior
• Expressions that are NOT allowed in CHECK: subqueries, aggregates, UDFs
• How to inspect constraints: DESCRIBE DETAIL / SHOW TBLPROPERTIES
• Constraints are also enforced during streaming

Check Your Understanding

Frequently Asked Questions