System Design · medium
How do you ensure data quality in a pipeline?
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
Schema tests, freshness/volume checks, anomaly detection, contracts, alerting (e.g., dbt tests).
How to answer it
The principle
Test the data, not just the code. Code tests tell you the transformation is what you wrote; data tests tell you the input was what you assumed. Pipelines almost always break on the second.
Where checks go
At ingestion schema conformance, required fields, type coercion
reject or quarantine, do not silently coerce
After transform row counts within expected bounds
uniqueness on the primary key
referential integrity against dimensions
accepted values for enums
freshness: is the newest row recent enough
Before publish reconciliation against a source of truth
totals match the finance system, or the run does not shipThe four questions every table should answer
- Is it fresh? When did the newest row arrive, and is that within the SLA?
- Is it complete? Row count against yesterday and against the same day last week.
- Is it unique? No duplicates on the declared key.
- Is it valid? Types, ranges, enums, and no unexpected nulls.
Those four cover most incidents, and dbt tests or Great Expectations express them in a few lines each.
Fail loudly, and in the right place
A quality check that logs a warning nobody reads is not a check. Decide per table whether a failure blocks publication or raises an alert while the stale data stays visible. Blocking is right for finance; for a marketing dashboard, yesterday's numbers may beat none.
Anomaly detection on the metrics themselves catches what schema tests cannot: a column that is still perfectly valid and has silently halved.
Key points
- Test the data as well as the code. Assumptions about inputs are what break.
- Freshness, completeness, uniqueness, validity: the four checks worth having everywhere.
- Quarantine bad rows rather than dropping them, so they can be investigated.
- Reconcile against a source of truth before publishing anything financial.
- A warning nobody reads is not a control. Decide what blocks and what alerts.
FAQ
- Which tool? dbt tests for warehouse models, Great Expectations for richer suites, and an anomaly detection product on top. The discipline matters more than the tool.
- How many tests? Start with the four questions on every important table, then add one for each incident you have.
- Who owns quality? The team that owns the table. A central team can provide tooling, not accountability.
The 30-second answer
The principle is to test the data, not just the code, because pipelines mostly break on inputs that stopped matching what you assumed rather than on logic you wrote wrongly.
I would put checks at three points. At ingestion, schema and required fields, quarantining bad rows rather than silently coercing them. After transform, the four questions every table should answer: is it fresh, is it complete, is the key unique, are the values valid. And before publishing anything financial, a reconciliation against a source of truth.
The part that gets skipped is deciding what happens on failure. A warning nobody reads is not a control, so per table I would decide whether a failure blocks publication or alerts while the previous data stays up. And I would add anomaly detection on the metrics themselves, because a column can stay perfectly valid while quietly halving.
In the interview
- What they are checking: tests at the grain (unique, not null), freshness and volume, and blocking on failure.
- Common mistake: a quality dashboard that reports problems after the CEO has already seen the wrong number.