Delta writes failing with CHECK or NOT NULL constraint violated on Azure Databricks: find the bad rows, quarantine them and change constraints safely

#Symptom

A batch job, streaming query or MERGE that ran fine yesterday now fails with an error like one of these:

[DELTA_VIOLATE_CONSTRAINT_WITH_VALUES] CHECK constraint valid_amount (amount >= 0) violated by row with values:
 - amount : -12.5

[DELTA_NOT_NULL_CONSTRAINT_VIOLATED] NOT NULL constraint violated for column: customer_id.

Or an ALTER TABLE ... ADD CONSTRAINT or SET NOT NULL fails because existing rows don't comply. Error class names and wording vary by runtime version, so match on the words "constraint" and "violated" in your output.

Constraints are enforced per transaction. One bad row fails the whole write, and nothing from that write is committed. The table is safe. Your pipeline is stopped, and everything downstream is waiting.

#Quick map

#Step 1: See which constraints the table has

CHECK constraints are stored as table properties named delta.constraints.<name>:

SHOW TBLPROPERTIES prod.sales.orders;

DESCRIBE DETAIL prod.sales.orders;   -- minWriterVersion, tableFeatures

NOT NULL appears in the schema:

DESCRIBE TABLE prod.sales.orders;
SHOW CREATE TABLE prod.sales.orders;

Check whether a constraint changed recently, and who changed it:

SELECT version, timestamp, userName, operation, operationParameters
FROM (DESCRIBE HISTORY prod.sales.orders)
WHERE operation IN ('ADD CONSTRAINT', 'DROP CONSTRAINT', 'CHANGE COLUMN')
ORDER BY version DESC
LIMIT 20;

If a constraint was added last night, the cause is the new rule, not the source data.

#Step 2: Find the offending rows in the source

The error shows only one row. Run the constraint expression negated against the batch you tried to load to count all of them:

src = spark.read.table("prod.sales.orders_staging")   # or the DataFrame your job builds

bad = src.filter("NOT coalesce(amount >= 0, false) OR customer_id IS NULL")
print(bad.count())
display(bad.limit(50))

Build the predicate from the constraint text, NOT coalesce(<expr>, false), rather than writing the opposite comparison by hand. The docs say a CHECK condition must evaluate to true. The coalesce treats a NULL result as a failure, the conservative reading. If NULLs in that column are legitimate, test one insert on a scratch table on your runtime and adjust.

Use the result to decide:

• A handful of rows, genuinely bad: quarantine them and load the rest (Step 3).
• Most or all rows: an upstream change, such as new units, a renamed column or a new sign convention. Stop, escalate to the source owner, and don't loosen the constraint to get green.

#Step 3: Quarantine bad rows and load the good ones

Split the batch with the same expressions the table enforces. Write rejects to a quarantine table with run metadata:

from pyspark.sql import functions as F

rule = "(amount >= 0) AND customer_id IS NOT NULL"

good = src.filter(F.expr(f"coalesce({rule}, false)"))
bad  = src.filter(F.expr(f"NOT coalesce({rule}, false)"))

(bad.withColumn("_rejected_at", F.current_timestamp())
    .withColumn("_rule", F.lit(rule))
    .write.mode("append").saveAsTable("prod.sales_quarantine.orders_rejects"))

good.write.mode("append").saveAsTable("prod.sales.orders")

The coalesce(..., false) sends rows where the rule evaluates to NULL to quarantine, so every row in good passes the rules. Every row lands in exactly one of the two DataFrames.

For MERGE, filter the source DataFrame before the merge. A matched UPDATE that sets a violating value fails the same way as an insert.

For Structured Streaming, put the split inside foreachBatch so one bad record doesn't stop the stream. After the stream has failed, restart it from the same checkpoint once the fix is deployed. The failed micro-batch is reprocessed.

Tip

If you use Lakeflow Declarative Pipelines, expectations (expect, expect_or_drop, expect_or_fail) give you the same behavior with metrics in the event log. Table constraints are still worth keeping as the last line of defence for writers outside the pipeline.

#Step 4: Adding or tightening a constraint on an existing table

ADD CONSTRAINT and SET NOT NULL validate every existing row first. On large tables this is a full scan, and it fails if any row breaks the rule.

Check before you run it:

SELECT count(*) AS violating_rows
FROM prod.sales.orders
WHERE NOT coalesce(amount >= 0, false);

SELECT count(*) AS null_rows
FROM prod.sales.orders
WHERE customer_id IS NULL;

Fix or move those rows, then add the rules:

ALTER TABLE prod.sales.orders ADD CONSTRAINT valid_amount CHECK (amount >= 0);
ALTER TABLE prod.sales.orders ALTER COLUMN customer_id SET NOT NULL;

Caution

Don't fix violating history with DELETE in production unless you've agreed it with the data owner. Copy the rows to a quarantine table first. Time travel won't save you once VACUUM passes the retention window.

Run the constraint change outside the load window. It commits like any other write and can conflict with concurrent writers.

CHECK expressions must be deterministic. You can't use UDFs, aggregates, window functions or functions that return multiple rows. NOT NULL isn't allowed on columns nested inside arrays or maps. On a struct field, the parent struct must be NOT NULL too.

#Step 5: Loosening or removing a constraint

To change a rule, drop it and add the new version. There is no ALTER CONSTRAINT:

ALTER TABLE prod.sales.orders DROP CONSTRAINT IF EXISTS valid_amount;
ALTER TABLE prod.sales.orders ADD CONSTRAINT valid_amount CHECK (amount >= 0 OR order_type = 'REFUND');

ALTER TABLE prod.sales.orders ALTER COLUMN middle_name DROP NOT NULL;

Between the two statements, the table accepts anything. Do this when writers are paused.

Warning

Dropping a constraint so a failing job goes green moves the bad data into every consumer of the table. Do it only when the rule itself is wrong, and record why.

#External readers and writers

Adding a CHECK constraint upgrades the table's writer protocol to at least version 3 if it was lower. Older or non-Databricks Delta writers may then refuse to write. Check minWriterVersion in DESCRIBE DETAIL. On Databricks Runtime 15.4 LTS and above you can remove CHECK constraints and downgrade the protocol with ALTER TABLE ... DROP FEATURE. Check the exact feature name and steps in the docs for your runtime before you run it.

Primary key, foreign key and unique constraints in Unity Catalog are informational only. They don't reject duplicates or orphans. If your job relies on uniqueness, enforce it in the job, for example by deduplicating before MERGE.

#Prevent it next time

• Keep constraint DDL in version control with the table definition. Deploy it through the same pipeline as code changes, not ad hoc.
• Before adding any constraint, run the NOT coalesce(<expr>, false) count query in the change ticket.
• Apply the same rules in the job (Step 3) so bad rows go to quarantine instead of failing the run, and alert when quarantine counts rise.
• Agree data contracts with upstream owners for columns that carry constraints.
• Monitor job failures for the word "constraint" and route those failures to the data owner as well as to on-call.