Prompt and scope
This is a common data-platform and analytics-engineering design question. The goal is to decompose “reliable data” into measurable dimensions and connect them to consumer use and remediation, rather than listing a few checks.
What the interviewer is evaluating
- Whether you distinguish freshness, completeness, validity, accuracy, consistency, and uniqueness.
- Whether targets reflect each data product and consumer rather than one global threshold.
- Whether detection, severity-based alerting, downstream blocking, and recovery are designed together.
- Whether quality evidence, versions, and lineage are retained so alerts remain actionable.
Clarifying questions to ask first
Confirm batch versus streaming input, event time versus arrival time, the tables or fields used by customers, finance, or models, tolerated delay, late partitions and time zones, and whether consumers should block, degrade, or show the last trusted snapshot during an incident.
A 30-second answer structure
I would define a data contract by consumer use, then configure freshness, volume, completeness, and validity checks for each dataset. The pipeline stores quality results and lineage, routes warnings and blocking failures to owners, and lets downstream jobs read quality state. For a blocked release, consumers get a timestamped trusted snapshot; recovery uses backfill, recomputation, and reconciliation to close the event.
Deep-dive answer
1. Tie quality dimensions to use
Freshness asks when the latest usable data was produced; completeness asks whether required fields or partitions arrived; volume checks expected rows or bytes; validity checks formats and ranges; consistency checks relationships across tables; uniqueness checks duplicates. Thresholds should differ for settlement, operational dashboards, and offline training.
2. Define executable SLAs and SLOs
Record a business deadline, maximum delay, tolerated missing ratio, blocking condition, owner, and escalation time for each dataset. Track “data arrived but failed validation” separately from “the upstream produced no new data.” Give late data a bounded backfill window; after it expires, mark the partition unavailable instead of waiting forever.
3. Store versioned evidence
Persist the rule version, batch or partition, observed value, threshold, run time, input and output lineage, and result. Expectations or another declarative system can define checks, while a queryable quality ledger makes failures auditable. Evaluate a new threshold against historical baselines so a configuration change cannot hide a regression.
4. Design alerting, blocking, and degradation
Route alerts by impact and severity to data owners, platform on-call, and business consumers. A recoverable missing partition can be marked delayed while a trusted snapshot remains available; a failure that could contaminate finance or a model should block publication. Every block needs escalation, approval, and release conditions so consumers cannot simply bypass it.
5. Close the loop with backfill and reconciliation
After fixing the upstream, backfill by partition or business time, rerun the same transformation and rule versions, and record a repair batch to prevent duplicate writes. Reconcile input rows, output rows, rejects, late records, and downstream consumption. Close the event only after metrics return to baseline, then adjust rules, thresholds, or ownership in the review.
Example of a strong answer
I would define the contract by consumer. A settlement table must arrive within one hour of its daily deadline with a required completeness threshold, while an exploratory dashboard can tolerate more delay. Each dataset gets freshness, partition-count, required-field, range, and cross-table checks; every result stores rule version, observation, batch, lineage, and owner. A freshness timeout warns first, but a completeness failure on finance data blocks publication and serves a timestamped trusted snapshot. After the upstream fix, I backfill by business time, reuse the same transformation and checks, reconcile input, output, rejects, and consumer counts, and close the event only when metrics recover.
Common mistakes
- Monitoring job success without checking whether data is fresh, complete, and valid.
- Applying one global threshold to datasets with different consumers and deadlines.
- Writing quality results only to logs, with no batch, rule-version, or lineage link.
- Blocking every downstream consumer for a failure confined to one dataset or partition.
- Overwriting history during repair without a backfill batch, reconciliation, or deduplication guard.
- Reporting one score that does not tell consumers which dimension is unavailable.
Follow-up questions
What if the upstream job succeeds but no new data arrives?
Check arrival time and business time separately, then compare the last trusted partition with the expected update frequency. A successful job proves execution, not SLA compliance; a timeout should create a quality incident.
How do you handle false positives in quality rules?
Keep observations, thresholds, and rule versions; observe a baseline in shadow mode before enforcing. Use tiered thresholds and anomaly ratios, record consumer impact, and review changes rather than silently disabling alerts.
Should every failure block all downstream systems?
Scope the response by lineage and use. A failure that could contaminate settlement or a model can block related publication, while an independent exploratory dataset may use a warned previous version. Both the scope and release condition must be auditable.
How do you prove recovery did not lose rows?
Reconcile batches, partitions, input and output counts, rejects, late records, and downstream consumption, then sample primary-key sets. Repair batches must be replayable and traceable, and repeated execution should produce the same result.