Data quality

The classic quality dimensions become questions that a query answers:

• Completeness: did the required field arrive? (amount IS NULL)
• Uniqueness: does the key repeat? (COUNT(*) > 1 per key)
• Validity: is the value in the domain? (known status, positive amount, valid date)
• Referential integrity: does the foreign key exist? (an order from a customer that does not exist)
• Freshness: when is the newest data from?

A rules report, running here

Each rule counts how many rows violate it. Zero is green.

SELECT 'amount filled in' AS rule, COUNT(*) AS violations FROM orders WHERE amount IS NULL
UNION ALL
SELECT 'amount positive', COUNT(*) FROM orders WHERE amount <= 0
UNION ALL
SELECT 'status known', COUNT(*) FROM orders WHERE status NOT IN ('delivered', 'canceled', 'pending')
UNION ALL
SELECT 'customer exists', COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)
UNION ALL
SELECT 'event without repeats', COUNT(*) - COUNT(DISTINCT event_id) FROM events;

rule                  | violations
--------------------- | ----------
amount filled in      | 1
amount positive       | 0
status known          | 0
customer exists       | 1
event without repeats | 1

What to do with whoever violates

The rule needs to state the consequence, and there are three:

• Warn and let it through (record the metric);
• Drop the row (or send it to a quarantine table, so it is not lost);
• Stop the whole load, when the violation means something bigger broke.

In Databricks declarative pipelines, all three exist as *expectations*:

import dlt

@dlt.table
@dlt.expect("amount_filled_in", "amount IS NOT NULL")
@dlt.expect_or_drop("amount_positive", "amount > 0")
@dlt.expect_or_fail("status_known", "status IN ('delivered', 'canceled', 'pending')")
def orders_silver():
    return spark.readStream.table("bronze.orders")

On regular Delta tables, ALTER TABLE … ADD CONSTRAINT … CHECK (amount > 0) makes a write that violates the rule fail.

• Measure the violation rate over time: a rule that always had 0.1% and today has 30% is an incident, even if 30% "passes".
• A quality rule is code: versioned, reviewed and tested like everything else (see CI/CD for data).