Validating nullability, cardinality, and ranges

Databricks enforces data quality through Delta Lake table constraints (NOT NULL, CHECK) that validate nullability and value ranges at write time, while cardinality and more complex business-rule checks are typically implemented using SQL aggregations or Lakeflow Declarative Pipelines (formerly Delta Live Tables) expectations. Understanding how constraint violations behave and where each validation technique fits is essential for building reliable data pipelines.

1 · Learn the must-know

• 1Delta Lake supports NOT NULL and CHECK constraints via ALTER TABLE ADD CONSTRAINT, and any write violating them fails the entire transaction (no partial writes).
• 2CHECK constraints use boolean SQL expressions (e.g., CHECK (age >= 0 AND age <= 120)) for range validation, but cannot reference subqueries or non-deterministic functions.
• 3When adding a constraint to an existing table, Databricks first validates all existing rows and will fail the ALTER TABLE statement if any violate the new rule.
• 4Cardinality checks (e.g., ensuring a column has expected distinct value counts or detecting unexpected duplicates) are not native Delta constraints and must be implemented with SQL (COUNT DISTINCT, GROUP BY HAVING) or custom validation logic.
• 5Lakeflow Declarative Pipelines (formerly Delta Live Tables) offer built-in expectations (expect, expect_or_drop, expect_or_fail) that let you define nullability, range, and cardinality rules declaratively and choose whether to warn, drop, or fail on violations.
• 6Constraints are metadata-level and enforced only on write; they do not retroactively validate data written before the constraint existed unless you explicitly run a validation pass.

2 · Check your understanding