Lesson 3 of 4 · 25 min

Publish data only when its contract passes

Build data-quality gates with actionable failure rows.

Mechanism and reasoning

A pipeline task can succeed while its output is wrong. Process success says the code finished. Data quality says the result meets a contract. Define that contract in terms of grain, identity, completeness, relationships, ranges and business invariants. Start with the failures that would change an important decision.

A useful test returns the records that violate the rule. This makes failure actionable and supports debugging. A single red indicator with no keys forces the operator to rediscover the problem. Keep sensitive fields out of broad alerts; link to controlled evidence instead.

Separate hard publication gates from warnings. A duplicate primary business key may make every downstream aggregation unsafe. A small freshness delay can be tolerable for a daily report but unacceptable for an operational alert. Severity follows the use case, not the convenience of the test tool.

Freshness and completeness differ. A table can contain a recent timestamp while missing most of the expected data. A source can also be legitimately quiet. Use arrival expectations, counts and source-specific schedules. Compare seasonal patterns carefully so a holiday does not trigger a meaningless incident.

When a gate fails, preserve the last known good output if the product permits it and display its age. Do not quietly serve stale data as current. Quarantine invalid records only when the remaining dataset still meets a defined contract; dropping rows can distort totals. In an interview, explain who receives the failure, which evidence they see, and whether readers continue with old, partial or no data.

Convert a business invariant into a publication decision

Start with the grain: one current row per invoice in this example. Then specify the required keys, relationship coverage, amount rules and source completeness. A generic “no nulls anywhere” test can reject legitimate optional fields while missing the business-critical error. Tests should follow the contract of the dataset and its consumers.

sql

1-- Return invoices whose required customer relationship is missing.2SELECT i.invoice_id, i.customer_id3FROM candidate_invoices AS i4LEFT JOIN customers AS c5  ON c.customer_id = i.customer_id6WHERE i.customer_id IS NULL7   OR c.customer_id IS NULL;

This query assumes that the customer table is the authoritative reference and is complete for the same relevant snapshot. If the dimension arrives later by design, the pipeline needs an explicit grace or publication rule. Otherwise, a relationship test may flag timing mismatch rather than invalid source data. Do not weaken the test silently; align its inputs and policy with the intended publication boundary.

Gate	Candidate result	Decision for this report
Unique invoice key	20 duplicate keys	Block
Required customer relationship	2 missing references	Block
Latest source arrival	1 minute old	Freshness passes
Expected source ranges	One range missing	Completeness fails
Optional description present	3% null	Warning only under stated contract

The table shows why one green freshness signal cannot override other failed invariants. It also prevents every anomaly from becoming an equal-severity incident. An optional description may not change revenue, while a missing source range can. The severity choice belongs to the business use of the dataset and should be made before the failure occurs.

A gate must define what readers see. For a daily management report, serving the last validated partition with a visible timestamp may be acceptable. For an operational alert, stale data can be worse than an explicit unavailable state. The system should not label yesterday's output as current merely because the refresh failed. Include the dataset version and freshness state in the published contract.

Quarantine is a policy, not an automatic fix. Removing twenty conflicting invoice rows can lower revenue and hide an upstream defect. If the report requires complete financial coverage, block the partition. If a noncritical enrichment field is invalid and the contract allows a missing value, quarantine that enrichment while preserving the core invoice. The correct unit of exclusion depends on which claims remain valid after the removal.

Failure evidence needs privacy controls. Broad alerts can include counts, rule IDs, partition IDs and a controlled link to failing keys. They should not dump customer details or full financial payloads into every log. Retain enough evidence to reproduce the failure using authorized access. A useful alert states the failed invariant, affected range, publication state and owner, so the next operator does not have to infer the impact.

Completeness checks often require source expectations. A count equal to yesterday's can still hide a missing partition and duplicate rows elsewhere. A recent maximum timestamp can come from one source while another is entirely absent. Use expected partition ranges, source sequence coverage or reconciled totals where available. If no authoritative expected count exists, label the check as an anomaly detector rather than a proof of completeness.

To verify the gate, construct a fixture with one duplicate, one missing foreign key, one stale partition and one legitimate null optional field. Confirm which cases block and which warn. Then test that a failed candidate cannot replace the published generation. This last check turns a collection of data tests into an actual release boundary. A test that fails but is ignored by the publication process is only a diagnostic message.

Worked example

Teaching table expects one row per invoice. A quality query finds duplicate invoice i9 and missing customer references for i12 and i13:

sql

1SELECT invoice_id, COUNT(*) AS copies2FROM invoices3GROUP BY invoice_id4HAVING COUNT(*) <> 1;

The duplicate gate blocks publication because summing revenue can double-count. A separate relationship test returns i12 and i13 for investigation. The task's successful exit does not override either gate. Preserve the previous validated partition and mark its timestamp while the source owner resolves the defects.

Exercise

A load has 1,000 rows, a timestamp from this minute and twenty duplicate business keys. Yesterday's valid load had 1,000 unique keys. Decide whether freshness alone permits publication and give two checks.

Model solution and rubric

No. Recent arrival does not establish uniqueness or completeness. Check distinct business keys and compare expected source coverage by partition or source sequence. Return the duplicate keys so the operator can trace them. If the prior output remains in use, expose its age and reason. Do not simply remove arbitrary duplicates when the conflicting rows can differ in value or version.

Score out of four: one point for the correct result, one for showing the intermediate reasoning, one for identifying the stated failure case, and one for a verification that could disprove the answer. Do not award the reasoning point for a tool name alone.

Failure modes and misconceptions

“Deduplicate by choosing any row and the contract is repaired.” Conflicting values or versions require a deterministic business rule. Arbitrary removal can change totals.

“A warning is harmless because the pipeline stayed green.” The publication decision must reflect the declared severity and affected consumer, not the orchestration status.

Interview probe

Evidence class: recommended. Original practice.

Which data tests would you add first to a revenue pipeline?

Strong answer: I would pin the grain, test unique invoice or transaction keys, validate required relationships and reconcile totals against the source by period. Then I would define freshness and correction rules with the consumers.

Follow-up: When is quarantining bad rows safer than blocking the entire partition?

Weak answer indicators: Treating a green orchestration run as valid data; using freshness as completeness; dropping duplicates without a deterministic rule.

Sources

Technical references: dbt data tests; Airflow best practices. Sources support the documented mechanisms. The numbers, decisions, rubrics and interview prompts in this lesson are original teaching examples, not measurements or employer question claims.

docsdbt data testsdocs.getdbt.com docsAirflow best practicesairflow.apache.org

Checkpoint

A recent table has expected row count but one source partition is missing and another duplicated. Which check catches the gap?

AExpected source-range and business-key coverageBMaximum timestamp onlyCTask exit code onlyDTotal row count only

Checkpoint

A duplicate invoice gate fails but the job exits successfully. What controls publication?

AThe process exit code aloneBThe declared data contract and gate severityCPublish after deleting one duplicate arbitrarilyDChange the hard gate to a warning after seeing the failure

Checkpoint

A relationship test flags facts because its dimension snapshot is incomplete. What should be verified first?

AWhether reference data and fact scope align at the publication boundaryBWhether all null fields can be ignored globallyCWhether every missing key should be assigned one default customerDWhether the test should become a warning to keep the schedule

Checkpoint

When can quarantining bad enrichment be acceptable?

AWhenever it reduces error countBWhenever the orchestration tool supports itCWhenever the remaining rows look recentDWhen the remaining published dataset still meets an explicit consumer contract

Checkpoint

The previous valid partition remains visible after a failed refresh. What should readers receive?

AIts actual age and failed-refresh stateBThe current time as its freshness labelCNo indication because values are validDOnly the latest task start time

Explain how you would build data-quality gates with actionable failure rows without reading the solution. State one assumption that could change your answer, and one observation that would make you revise it.

Not yetGetting thereConfident

Wrap-up

Make tests return useful evidence. Publication behavior must follow the data contract, including what readers see during failure.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free