Validation tables, quarantine, and Delta constraints

1.Keeping rules in one place

A Lakeflow pipeline expectation is a named SQL Boolean constraint on a dataset, with an action of warn, drop, or fail. When many Silver tables need the same checks, writing each rule inline in every pipeline is hard to maintain. Databricks recommends storing expectation definitions separately from pipeline logic, grouping related rules with custom tags, and applying the same expectations consistently across similar datasets. Then you can update and audit a rule without touching pipeline source code.

The documented pattern keeps rules in a Delta table called rules, with columns name, constraint, and tag. A rule might be website_not_null with the constraint Website IS NOT NULL, tagged validity. A Python helper reads the rules that match a tag and returns them as a dict. That dict is exactly the input an expect_all decorator needs.

def get_rules(tag):
  """
    loads data quality rules from a table
    :param tag: tag to match
    :return: dictionary of rules that matched the tag
  """
  df = spark.read.table("rules").filter(col("tag") == tag).collect()
  return {
      row['name']: row['constraint']
      for row in df
  }

@dp.table
@dp.expect_all_or_drop(get_rules('validity'))
def raw_farmers_market():
  return (
    spark.read.format('csv').option("header", "true")
      .load('/databricks-datasets/data.gov/farmers_markets_geographic_data/data-001/')
  )

You can also keep the same rules as a list of dicts in a Python module, rules_module.py, stored in the same folder as the pipeline notebook. Either way, records that fail any rule tagged validity are dropped from raw_farmers_market, and a second table applies the rules tagged maintained.

Sources12

2.Validation tables: checks that span datasets

An expectation tests one record at a time, so it cannot directly answer questions such as "did we lose rows?" or "is this key unique?". The solution is a validation table: a separate dataset whose rows describe a property of other tables, with an expect_or_fail on those rows. In SQL, the fail action is written ON VIOLATION FAIL UPDATE. A bare EXPECT is the default warn action: it keeps the rows and only records metrics. The SQL examples below are shown as the documentation lists them, with a bare EXPECT, so as written they only warn. Add ON VIOLATION FAIL UPDATE to make a violation fail the update. To check that no data is lost during a transformation, compare row counts:

CREATE OR REFRESH MATERIALIZED VIEW count_verification(
  CONSTRAINT no_rows_dropped EXPECT (a_count == b_count)
) AS SELECT * FROM
  (SELECT COUNT(*) AS a_count FROM table_a),
  (SELECT COUNT(*) AS b_count FROM table_b)

The same validation-table idea covers other cross-dataset checks. For completeness, left-outer-join a validation copy to the report and expect r_key IS NOT NULL. For primary key uniqueness, group by the key and expect each group's count to be 1. Outliers use a different action. The documented Python pattern cross-joins new data with bounds computed from the last 30 days of history (average ± 3 standard deviations) and applies expect_or_drop on amount BETWEEN lower_bound AND upper_bound, so out-of-range rows are dropped rather than failing the update.

CREATE OR REFRESH MATERIALIZED VIEW report_compare_tests(
  CONSTRAINT no_missing_records EXPECT (r_key IS NOT NULL)
)
AS SELECT v.*, r.key as r_key FROM validation_copy v
  LEFT OUTER JOIN report r ON v.key = r.key

CREATE OR REFRESH MATERIALIZED VIEW report_pk_tests(
  CONSTRAINT unique_pk EXPECT (num_entries = 1)
)
AS SELECT pk, count(*) as num_entries
  FROM report
  GROUP BY pk

CREATE OR REFRESH MATERIALIZED VIEW validated_amounts (
  CONSTRAINT within_statistical_range EXPECT (amount BETWEEN lower_bound AND upper_bound)
)
AS SELECT * FROM stats_validation_view;

Databricks puts it plainly: expectations enforce data quality, not orchestration. To keep Gold from updating after a failed validation, put the validation logic and the downstream work in separate pipelines and orchestrate them with a job, making the downstream pipeline's task depend on the validation pipeline's task. A pipeline task fails when its update fails, so the downstream task runs only if validation succeeds.

Sources13

3.Quarantining instead of dropping

Dropping a bad record throws it away, but sometimes Silver needs to keep invalid records so someone can review and repair them. In a pipeline, the quarantine pattern applies the rules with expect_all, which warns so metrics are still collected, and adds an is_quarantined column computed as NOT(rule1 AND rule2). The rows go into a temporary table partitioned by that flag, and two views split it into valid_trips_data and invalid_trips_data. Valid and invalid records then follow separate downstream paths.

You can do the same thing outside a pipeline with plain SQL. Use WHERE filters to send rows to a Silver table or a quarantine table:

INSERT INTO silver_table
  SELECT * FROM bronze_table
  WHERE event_timestamp <= current_time AND quantity >= 0;

INSERT INTO quarantine_table
  SELECT * FROM bronze_table
  WHERE event_timestamp > current_time OR quantity < 0;

There are two writes here, and that is intentional. Databricks recommends always processing filtered data as a separate write operation, especially with Structured Streaming, and warns that using .foreachBatch to write to multiple tables can lead to inconsistent results. Some bad values are predictable and can be repaired rather than quarantined. If an upstream system writes -1 for a missing weight, a CASE WHEN weight = -1 THEN NULL ELSE weight END transformation fixes it once in Silver, so downstream queries don't each need special logic.

The expect_or_drop action removes records that violate its constraint from the target dataset. So a table whose constraint is the inverted condition, such as the NOT(rule1 AND rule2) expression used for is_quarantined, and that uses expect_or_drop, would keep only the rows the main table rejects. Compare that with the flag-column and WHERE-filter patterns above when you read the exam question below.

Sources134

4.Delta table constraints: enforced versus informational

Expectations belong to pipelines. Delta tables can also enforce rules themselves when data is written, whichever process writes it. All constraints on Databricks require Delta Lake. There are two kinds, and they behave very differently: enforced constraints reject bad data, while informational constraints only describe relationships between tables.

You add or drop NOT NULL with ALTER TABLE ... ALTER COLUMN, and you manage CHECK constraints with ALTER TABLE ADD CONSTRAINT and DROP CONSTRAINT. Before Databricks adds either kind to an existing table, it checks that every existing row already satisfies it. A CHECK expression must be deterministic, and it cannot use user-defined, aggregate, or window functions, or functions that return multiple rows. Adding a constraint also upgrades the table writer protocol if it is below version 3, which can affect external Delta Lake clients.

CHECK is also how you enforce value ranges and text patterns on write. A range rule is a CHECK such as column_name >= 0 AND column_name <= 100. A regex rule uses REGEXP or RLIKE inside a CHECK, as shown below. NOT NULL can be enabled on an existing table only if no existing record in the column is null.

CREATE TABLE table_name (
  column_name STRING CHECK (column_name REGEXP '^[A-Za-z0-9]+$')
);

Informational constraints have version limits too. Primary key and foreign key constraints are available for Unity Catalog and Delta Lake tables in Databricks Runtime 13.3 LTS and above, and GA in 15.2 and above. Unique constraints are in Public Preview, in Databricks SQL and Databricks Runtime 18.2 and above. Two other mechanisms support Silver and Gold quality. Delta schema enforcement ensures that data written to a table adheres to a predefined schema. Databricks data quality monitoring (data profiling) lets you watch the statistical properties and quality of your tables over time.

ALTER TABLE main.default.people_demo ADD CONSTRAINT dateWithinRange  ?  (birthDate > '1900-01-01');

Sources54

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.If a validation table's expect_or_fail fails, any table that reads from it in the same pipeline will not update.Why is that wrong?

   Reading a validation table does not make a dataset wait for the result. To gate downstream work, use separate pipelines coordinated by a job.

   Covered in Validation tables: checks that span datasets

2. 2.Declaring a PRIMARY KEY on a Delta table guarantees that the key is unique.Why is that wrong?

   Primary key, foreign key, and unique constraints are informational only. NOT NULL and CHECK are the constraints that are enforced.

   Covered in Delta table constraints: enforced versus informational

Practise it for real

Drive a pipeline table's drop expectations from a central, tagged rules table

1. 1.Create the rules table with CREATE OR REPLACE TABLE rules AS SELECT col1 AS name, col2 AS constraint, col3 AS tag FROM (VALUES ...), including the website_not_null rule tagged validity.

   Why: This keeps rule definitions separate from pipeline code, so you can change them without editing the pipeline.

   You should see: A rules table with name, constraint, and tag columns.

2. 2.In the pipeline's Python source, define get_rules(tag) so that it reads the rules table, filters on tag, and returns a {name: constraint} dict.

   Why: The expect_all decorators require a dict of description and constraint pairs.

   You should see: Calling get_rules('validity') returns a dict that contains website_not_null.

3. 3.Decorate the raw_farmers_market table with @dp.expect_all_or_drop(get_rules('validity')) and run a pipeline update.

   Why: Drop removes failing records and still records metrics for each rule.

   You should see: Records with a null Website are missing from the table, and the dataset's Data quality tab shows the dropped count for website_not_null.