Making quality measurable

Lesson 2 of 4 in Ensuring Data Quality.

"Is the data good?" is not a question a machine can answer. Six narrower questions are, and the whole craft of this module is decomposing the vague one into the specific ones — because a specific question becomes a boolean, and a boolean becomes a gate.

The six dimensions, each stated as the predicate it turns into:

Completeness — is the value present? IsComplete "customer_id", or Completeness "email" > 0.98 when some absence is legitimate.
Uniqueness — is each entity here once? Uniqueness "order_id" > 0.99, and note the threshold: insisting on 1.0 for a feed with a known at-least-once delivery produces a nightly false alarm.
Validity — does the value satisfy its domain? A currency in the ISO list, an age between 0 and 120, a pattern for a postcode.
Consistency — do related fields agree? Does total equal the sum of the lines; does every order.customer_id exist in customer.
Timeliness — is it recent enough? The freshness axis, and the only one whose threshold is a promise to somebody rather than a property of the data.
Accuracy — does it match reality? The hard one, because it is the only dimension that needs a second source to check at all. Its usual practical form is a reconciliation against the system of record.

Which dimensions matter for which columns, and what threshold constitutes a breach, is design work. The rule language is only how you write the answer down.

What you can check, cheapest first — and why each tier needs the one below it

The schema contract — does the shape still hold?
The cheapest check there is, and the one every other tier depends on. Does the column exist, with the name and the type you agreed? ColumnExists, ColumnDataType, ColumnCount, or a registered Avro or Protobuf schema with a compatibility mode.

Nothing above this tier means anything if the shape has changed, because a check on amount is vacuous when amount is now called amt and is entirely null. This is also the tier that catches the single commonest silent defect — the rename.

Cost: effectively zero. It is metadata.
Completeness and uniqueness — is each thing here, exactly once?
One pass over the column, no joins, no second source. Is the value present; is the key unique.

The design decision is the threshold, and it is where most rulesets go wrong in both directions. IsComplete on a primary key is a hard requirement. Completeness "email" > 0.98 is honest about a column that is genuinely optional for 2% of customers, and insisting on 1.0 there pages somebody every night for a fact about the business. The same applies to Uniqueness against a feed with at-least-once delivery.

Run an analyzer for a fortnight before choosing the number. The data knows what normal is; you are guessing.
Validity and range — is each value possible?
Still one pass, now with a domain. Is the currency code in the list; is the quantity positive; is the date inside a plausible window; does the identifier match its pattern.

This tier is where the unit change is caught, and it is worth noticing how: not by a type check, which passes, but by a range. ColumnValues "amount" between 0 and 10000 is a weak-looking rule that catches a hundredfold shift immediately. A dynamic rule is stronger still, comparing against the historical values of the same metric rather than against a constant you chose once.

Cost: one pass. Still cheap.
Consistency and timeliness — do the parts agree, and is it recent?
Now it costs something, because it needs more than one column or more than one table. Does total equal the sum of its lines; does every order.customer_id exist in customer (ReferentialIntegrity); does the row count match a companion table (RowCountMatch).

Timeliness sits here too, as DataFreshness "order_date" <= 24 hours — expressed in hours or days against a date column. One documented behaviour to know: a DataFreshness rule fails for rows whose value is NULL, and the reported failure reason is a percentage of rows passing the threshold, so a column with legitimate nulls needs a compound rule that allows them explicitly.

Cost: a join or a second scan.
Accuracy — does it match reality?
The only dimension that cannot be checked from the data alone, because "correct" is defined somewhere else. Its practical form is a reconciliation: AggregateMatch comparing your sum against the source system's sum for a closed window, or a CustomSql rule against a reference extract.

This is the tier that catches the two defects nothing below it can see — a partial arrival whose rows are all individually perfect, and a semantic change that keeps every value valid. It is also the most expensive, which is why it usually runs on a closed window (yesterday, last month) rather than on the current one.

If you write exactly one rule in this tier, make it a count and sum reconciliation against the system of record for the previous closed period. It is the highest-value rule in this entire module.

Two things about thresholds decide whether a ruleset survives contact with production.

A threshold is a promise, and a promise has a cost on both sides. Set Completeness "email" = 1.0 on a column that is legitimately 97% complete and you have scheduled a nightly page forever; everybody will learn to ignore it, including on the night it means something. Set it at 0.5 and it will never fire. The way out is not judgement, it is measurement: declare analyzers first, collect the statistic for a couple of weeks, read the actual distribution, and then write a rule whose threshold reflects the data. Dynamic rules make this permanent — RowCount > avg(last(5)) * 0.8 compares against the column's own recent history rather than against a number somebody typed in a meeting.

Two rulesets, not one. A single ruleset forces one decision — fail or pass — onto twenty rules of wildly different importance. Split them:

A blocking ruleset with a handful of rules whose failure genuinely means do not publish this: the key is complete and unique, the row count reconciles, the schema holds.
An observational ruleset with everything else, publishing to CloudWatch and a dashboard, reviewed weekly rather than at 03:00.

This is also why the data quality score is a trend and not a gate. Twenty rules, one failure, 95% — and 95% means something completely different depending on whether the failing rule was ColumnLength on a description or IsComplete on the primary key.

The six dimensions, the rule types that express them, and the defect each one actually catches. The last row is the only one that needs a second source.
Dimension	Expressed as	Catches	Cost to evaluate
Completeness	`IsComplete`, `Completeness > t`	a renamed column arriving as nulls, a partially populated feed	one pass over the column
Uniqueness	`Uniqueness > t`, `DistinctValuesCount`	duplicate delivery, a replayed batch, a join key that is not a key	one pass, with a distinct count
Validity	`ColumnValues in [...]`, `ColumnValues between`, `ColumnLength`	a new enum value, an out-of-range quantity, a unit change	one pass over the column
Consistency	`ReferentialIntegrity`, `RowCountMatch`, `CustomSql`	an orphaned key, a child table that did not load with its parent	a join or a second scan
Timeliness	`DataFreshness "col" <= 24 hours`	a stalled feed, a partition that never arrived	one pass — but it fails on NULL rows, so allow them explicitly
Accuracy	`AggregateMatch`, a reconciliation in `CustomSql`	a partial arrival and a semantic change — the two nothing else sees	a second source, usually on a closed window

Key terms: Data quality dimension, DQDL, Analyzer, Data quality score, Freshness check

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.