How Do You Ensure Data Quality?

Interviewers ask this one open ended on purpose. They want to hear whether you reach for a structure before you reach for a tool. The structure that holds up is 3 layers of checks, each placed where a different kind of failure originates. The first layer sits at ingestion and tests the raw feed against its contract: the columns the producer promised, their types, and a primary key that is present and unique. The second layer runs after transformation and tests the pipeline's own work: row counts reconcile with the input, joins did not fan out, every foreign key resolves. The third layer runs on the published output and tests business invariants: order totals are never negative, every order has a customer, and yesterday's revenue lands within a plausible band of the day before.

One check at the end tells you something broke; 3 checks tell you where. If the contract layer passed and the transformation layer failed, the bug lives in your code, and the on-call engineer skips the hour spent asking the producer whether they changed anything. Say that sentence in the interview, because it shows the layers exist to shorten diagnosis rather than to look thorough.

Blocking versus warning

The decision this question turns on is what the pipeline does when a check fails. A warning writes a log line and lets the load continue to the table consumers read. A blocking check halts the run before that write happens. Candidates often default to warnings because blocking looks expensive: the dashboard shows yesterday's numbers until someone fixes the feed. The senior position is the reverse, and it rests on one asymmetry. Stale and correct is recoverable, because the data arrives late and nothing downstream has acted on it. Fresh and wrong is unrecoverable, because a finance analyst who reconciled against the wrong figure at 8 a.m. has already sent the email. You can replay a load; you cannot replay a decision.

Block the publish

• Primary key null or duplicated
• Row count outside the reconciliation tolerance
• A required column missing from the source
• Totals negative or outside the business band

Warn and continue

• Optional column null rate rises from 2% to 4%
• A new column appears that no consumer reads yet
• Free-text field length distribution shifts
• Row count 3% above the median on a known sale day

A blocking check is a query that returns a count, and a pipeline step that refuses to proceed unless that count is 0. The query below runs against the day's partition after the transform step and before the publish step.

SELECT
  COUNT(*) AS violations
FROM silver.orders
WHERE dt = DATE '2026-10-20'
  AND (
    customer_id IS NULL
    OR order_total < 0
    OR currency NOT IN ('USD', 'EUR', 'GBP')
  )

Reconciliation as the layer 2 check

SELECT
  raw.row_count AS raw_distinct_orders,
  curated.row_count AS curated_rows,
  raw.row_count - curated.row_count AS dropped_rows
FROM (
  SELECT
    COUNT(DISTINCT order_id) AS row_count
  FROM bronze.orders
  WHERE dt = DATE '2026-10-20'
) raw
CROSS JOIN (
  SELECT
    COUNT(*) AS row_count
  FROM silver.orders
  WHERE dt = DATE '2026-10-20'
) curated

The tolerance belongs next to the check, in writing, with every reason rows leave on purpose. This transform removes 2 kinds: the duplicate versions that deduplication collapses on the way into silver, which section 2 builds, and internal test orders. Counting distinct order_id values in bronze takes the duplicates out of the arithmetic, so on a 20 million row day dropped_rows of 1,200 test orders is healthy and the threshold reads 0.5% rather than 0. A tolerance nobody wrote down becomes a 3 a.m. argument about whether 1,200 is a bug.

What blocking costs, in minutes

Every check adds runtime. On a 20 million row daily partition, the 3 layers together add about 4 minutes to a 40 minute load, a 10% tax that buys the ability to trust the number. Blocking adds a second cost only when a check fails: the data sits unpublished while a human decides, and that decision needs an owner. Name both costs in your answer and name the mitigations with them: run the layer 1 checks in parallel with the transform so they cost no wall-clock time, and attach a named owner and a paging rule to each blocking check so a failure at 3 a.m. wakes one specific person rather than a channel.

What the interviewer hears as the senior signal

• Each layer is tied to the failure it catches, so the design shortens diagnosis
• Blocking is the default for keys, counts and invariants, with the asymmetry stated as the reason
• Every tolerance and every blocking check has a written number and a named owner

Do

• Place checks at the 3 layers and say which failure each one catches
• Block on key, count and invariant failures before the publish step
• Write the tolerance beside the check with the reason for it
• Give every blocking check a named owner

Avoid

• Run one check at the end and call the pipeline validated
• Warn on a null primary key because blocking feels disruptive
• Set tolerance to 0 on a transform that filters rows on purpose
• Page a channel with no owner attached

About this lesson

This section is part of the Make It Reliable: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.