Data Engineering Interview: How would you design an auditable data-quality quarantine pipeline?

Problem and Context

An upstream system sends 200 million order events per day, and about 0.2% may have missing required fields, parse failures, or business-rule violations. Valid events must continue into fact tables and downstream metrics. Invalid events cannot disappear silently; after repair they must be replayable from original input without counting the same event twice.

Assume a batch-and-stream design with a stable event_id per input, an immutable raw copy, schema-versioned rules, and quarantine records that retain failure reasons, rule versions, and retry state. The interview problem is about data-engineering routing, observability, and recovery rather than a single Spark option.

What the Interviewer Evaluates

Separating parse failures, missing fields, and business validation failures into actionable classes.
Preserving raw evidence, rule versions, and lineage for audit and replay.
Choosing among fail-fast, drop, and redirect/quarantine based on blast radius.
Using idempotency keys, deduplication state, and output versions to prevent double counting.
Designing quality metrics, alert thresholds, repair workflows, and replay gates.
Addressing toxic data, PII, retention, and access control in the quarantine area.

Clarifications to Ask First

Is 0.2% an accepted defect budget, or must any breach block release? This determines threshold versus zero-tolerance gating.
Can events be retried, arrive out of order, or be duplicated? If so, event_id must define the idempotency boundary.
Can rule failures be auto-repaired, or is human approval required? This changes the replay queue and controls.
Can downstream metrics tolerate delay or correction? If not, repaired data needs compensating partitions and versioned reports.
Does the raw payload contain personal data? That changes encryption, masking, access, and deletion requirements.

30-Second Answer Framework

I split the pipeline into immutable raw, parsing, rule-validation, valid, and quarantine stages. Parse and rule failures produce a quarantine record containing the event_id, schema and rule versions, reason codes, and a raw reference; valid events enter the fact table through an idempotent write. The quarantine area provides repair, approval, and replay queues. Replay uses the same event_id and deduplicates at the target boundary. I monitor valid rate, reason-code ratios, quarantine age, and replay success, then choose alert or blocking gates by business SLO and severity.

Step-by-Step Deep Dive

1. Preserve Raw Evidence First

Partition immutable objects or logs by batch, source, and receive time, and store a checksum. Processing jobs append status instead of overwriting the raw payload. This makes rule upgrades, parser fixes, and supplier disputes reproducible from identical input. Separate permissions for raw and quarantine layers prevent support users from editing facts directly.

2. Classify Failures and Keep Reasons

Run byte/format parsing first, schema type and required-field checks second, and cross-field business rules third. Store structured reason_code values such as MALFORMED_JSON, MISSING_ORDER_ID, or INVALID_CURRENCY. A record may have multiple reasons, but preserve the first failing stage and rule version so later repairs remain explainable.

text

quarantine_record = {
  event_id, source_batch, raw_uri, payload_hash,
  schema_version, rule_version, failed_stage,
  reason_codes, first_seen_at, status
}

Spark file options can record bad files or ignore corrupt files, but continuing the job is not the same as safely preserving business records. The design must explicitly route recoverable records to quarantine rather than merely enabling an ignore switch.

3. Choose Fail, Drop, or Quarantine

Unreadable infrastructure input, untrusted signatures, or corruption that could contaminate a whole batch should fail the batch and retain an alert. A single-record error that can be isolated without affecting other events should be quarantined so valid data continues. Drop is acceptable only when the record is unrecoverable, the business owner accepts the loss, and an audit trail is required; every drop must remain countable and traceable.

4. Define the Idempotent Valid Write

Use event_id plus source version as a unique key, and write the fact table through an idempotent upsert or commit log. Before replaying quarantine, check whether the target already accepted the event, then choose skip, update, or a compensating version. For correctable facts such as order amount, do not silently overwrite history; emit a correction event and let downstream reports recompute by version or effective time.

5. Repair, Approve, and Replay

Repair tools create a new payload or patch and never modify the raw layer. Record the operator, reason, input hash, and rule version, then route the change through approval. A replay worker reads quarantine state and reruns the complete validation chain. On success it atomically moves QUARANTINED to REPLAYED; on failure it increments attempts and schedules the next time. Leases or database locks prevent two workers from applying the same event concurrently.

6. Quality Metrics and Release Gates

Monitor total intake, valid rate, each reason_code ratio, quarantine-age percentiles, replay success, duplicate events, and downstream corrections. Gate by severity: a signature failure may be zero tolerance while a missing optional field only alerts. Even 0.2% should be compared with historical baselines, source mix, and business loss rather than declared normal. When a gate trips, freeze downstream publication or roll back to the previous rule version and record any manual release.

7. Retention, Privacy, and Recovery

Keep only the minimum raw fields needed for repair, encrypt sensitive payloads, and restrict access. Retention and deletion requests must map from event_id to raw objects, quarantine rows, and derived indexes. Back up queues, metadata tables, and object storage separately. If the target write succeeds but the status update fails, retry by unique key; if replay is marked but the write is uncertain, recover from a commit log or target-table check instead of inferring success from a worker response.

High-Quality Model Answer

I would first confirm the acceptable defect budget, duplicate or late delivery, and whether order corrections may wait for reporting. The pipeline keeps an immutable raw layer, then runs parsing, schema, and business rules in stages. Recoverable record-level errors go to quarantine; infrastructure or security failures block the batch. A quarantine row stores the idempotency key, raw reference, rule version, reason code, and state. Valid events write idempotently to the fact table. Repair creates a new payload and requires approval; replay reruns the full validation chain and atomically marks success. Metrics cover valid rate, reasons, quarantine age, replay rate, and duplicate effects, with severity-based gates. This keeps a small bad slice from blocking the batch without hiding anomalies as healthy data.

Common Mistakes

Enabling ignoreCorruptFiles and declaring success → the job continues but records may vanish → route recoverable records to reason-coded quarantine.
Storing failures in an editable table → raw evidence can be changed → keep raw immutable and create new repair versions.
Re-inserting replayed events directly → downstream metrics double count → use an event_id uniqueness boundary and commit log.
Blocking the whole batch for every error → a small bad slice destroys freshness → choose fail or quarantine by stage and severity.
Counting only total failures → source and rule regressions stay hidden → break metrics down by reason, schema version, source, and time.
Skipping validation after repair → the patch can introduce a second defect → replay must execute the complete validation chain.

Follow-ups and Responses

The quarantine rate jumps from 0.2% to 8%. Do you keep publishing?

Split by reason and source first. If one supplier has a recoverable missing field, pause that source and continue others. If parsing or signature validation fails, freeze downstream publication and roll back the rule version. Thresholds should reflect business loss and historical baselines, not only the absolute percentage.

How do you prevent replay from changing a settled order?

Separate original and correction events, and retain version or effective-time columns in the fact table. A settlement snapshot pins its input generation. A repaired event goes through a compensation workflow where financial rules decide whether to create an adjustment rather than silently overwrite the settled amount.

The quarantine area contains PII. How can support investigate?

Show masked fields and reason codes by default. Grant short-lived authorized access to the raw payload and record every audit event. Deletion requests follow event_id across raw objects, quarantine rows, and derived indexes, retaining only an irreversible audit digest afterward.

A worker crashes after the target write but before changing quarantine state. What happens?

On retry, check the target table and commit log by idempotency key. If present, only repair the status; do not repeat the side effect. If absent, submit again. Use conditional, retryable state transitions so an unknown result is never guessed to be success or failure.

When should a record be dropped instead of retained?

Only when it is unrecoverable, no compliance rule requires retention, and the business owner explicitly accepts the loss. Even then, retain an auditable count, reason, batch, and policy version, and expose the drop in quality reports.