Domain 3 of 4 · Chapter 4 of 4

Data Quality Constraints in Unity Catalog

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Implementing Data Engineering Solutions Using Azure Databricks premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Three gates a bad row passes
  • Enforced constraints: NOT NULL and CHECK
  • Informational keys: PRIMARY KEY, FOREIGN KEY, UNIQUE
  • Schema enforcement and handling drift
  • Pipeline expectations: warn, drop, and fail
  • Choosing a mechanism, and exam patterns

Data-quality mechanisms: what each does to a bad row

MechanismEnforced?Action on a bad rowWhere it applies
NOT NULL / CHECK constraintYesReject the write (transaction fails)Every writer of the Delta table
PRIMARY KEY / FOREIGN KEY / UNIQUENo (informational)Not checkedAdvisory; optimizer only, with RELY
Schema & type enforcementYes (by default)Reject the mismatched writeEvery writer of the Delta table
Schema evolution (mergeSchema)Opt-inAdd new column; conflicting type still failsThe write that enables it
expect (warn)NoKeep the row, record metricsInside the Lakeflow pipeline
expect_or_dropYes (drop)Drop the row, update continuesInside the Lakeflow pipeline
expect_or_failYes (fail)Stop update, atomic rollbackInside the Lakeflow pipeline

Decision tree

In a LakeflowDeclarative Pipeline?Must a bad row stopthe whole update?What are youvalidating?expect_or_failstop + roll backKeep bad rowsor discard them?expect (warn)keep row, count itexpect_or_dropdrop row, keep loadingNOT NULL / CHECKenforced constraintPK / FK / UNIQUEinformational; MERGE dedupSchema enforcement+ evolution, _rescued_datayes: in a pipelineno: any writeryesnokeepdiscardnulls, rangesuniquenesscolumns, types
Cheat sheet

Cheat sheet

  • NOT NULL and CHECK constraints reject invalid writes
  • Use CHECK for range/cardinality; PK and UNIQUE are informational
  • The column schema enforces data-type validity
  • Delta schema enforcement rejects mismatched writes
  • Handle additive schema drift with schema evolution
  • Auto Loader preserves unmatched data in _rescued_data
  • Expectations offer warn, drop, and fail actions
  • expect_or_fail stops the update and rolls back
  • Dropped and failed record counts are tracked in the event log
  • expect_all applies multiple named constraints together

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. Update table schemas with schema evolution
  2. Constraints on Azure Databricks
  3. Manage data quality with pipeline expectations
  4. ADD CONSTRAINT clause
  5. DECIMAL type
  6. MERGE INTO
  7. Query optimization using primary key and unique constraints
  8. Configure schema inference and evolution in Auto Loader
  9. Develop pipeline code with Python
  10. Pipeline event log