A Data-Quality Interview Exercise With Missing Identifiers
Design data-quality handling for missing identifiers with visible quarantine, authoritative repair, safe replay and honest completeness reporting.
TL;DR
- In this fictional exercise, shipment records arrive without identifiers needed to associate them with customers.
- Separate validation from disposition and preserve enough evidence to recover or resolve each record.
- Design replay to avoid duplicate effects and make missing or unresolved data visible to downstream consumers.
Decide what the missing identifier prevents
Use this fictional data-quality interview: a partner sends shipment records, and some arrive without the order identifier required to associate them with customers. The pipeline currently drops those rows and reports the rest as successfully processed. The interviewer asks how you would improve it.
Start by identifying the missing relationship and the downstream consequences. Can the record still support a safe aggregate, or would processing it create an incorrect customer update? The answer depends on the use. A missing identifier is not merely a formatting inconvenience when it controls attribution.
Do not fill the field with a guessed order number just to satisfy the schema. Also do not assume silent deletion is acceptable because most records remain valid. The system needs an explicit exception and recovery policy.
Separate validation from disposition
Define the validation rule and the possible outcomes. A record can be valid for processing, temporarily incomplete, permanently invalid under the contract or awaiting investigation. Avoid a single undifferentiated “bad row” category if the recovery actions differ.
| Condition | Candidate disposition | Required evidence |
|---|---|---|
| Missing order identifier | Quarantine for repair | Source record identity and reason |
| Unknown but supplied identifier | Check arrival order or source mismatch | Referenced value and current lookup state |
| Duplicate source record | Apply defined identity rule | Stable source event identity |
| Conflicting identifiers | Escalate rather than guess | Original values and contract version |
The table is a practice design, not a universal data policy. Explain which fields can be retained safely and who is authorised to resolve each exception.
Keep enough evidence to recover
A quarantine record should preserve the original permitted payload or the minimum sufficient representation, the validation reason, source identity and processing context. Do not log sensitive customer information indiscriminately into an unrestricted error stream.
Assign a durable status so operators can distinguish new exceptions, repairs in progress, resolved items and records that will not be processed. A dashboard count alone cannot tell whether the same issue is being retried indefinitely.
Make the source responsible for authoritative corrections where appropriate. If an operator is allowed to repair a field, record the basis and the change. The repair process should not become an unreviewed way to attach shipments to whichever customer seems most likely.
Design replay without double effects
Suppose a corrected record arrives after the original was quarantined. Connect it to the original source identity and process it through the same business rules. Ensure that a retry or repeated repair does not send duplicate customer notifications or create multiple shipment entries.
The recovery path must respect the distinction between a failed validation and an uncertain downstream outcome. If the original record passed validation and a later action timed out, replay may require reconciliation rather than treating the whole record as unprocessed.
Use a clear transaction or workflow boundary for marking the exception resolved. Marking it complete before the business effect is durable can lose work; marking it afterward without idempotent handling can repeat effects after a crash.
Make quality visible to consumers
A batch that processed valid records but quarantined others is partially successful, not necessarily complete. Report accepted, quarantined and rejected counts with consistent definitions. Explain whether downstream totals exclude unresolved records.
Set alerts around the operational risk, such as a sudden change in missing identifiers or exceptions ageing beyond the agreed review window. Avoid an alert on every individual row if that produces noise without improving recovery.
Our system design frameworks guide can help distinguish ingestion, validation, business processing and exception management. A reliable pipeline needs all of those paths to have owners.
Set a policy for unresolved exceptions that cannot be repaired. They should not occupy an active queue forever with no owner, nor disappear without a disposition. Record the approved reason for closure and the effect on downstream completeness. Retain only the evidence needed under the applicable data-handling policy, with access appropriate to its sensitivity.
Rehearse a source-system change
Have a partner reveal that the partner renamed the identifier field without updating the integration contract. Explain how you would stop the growing exception stream, verify the new mapping and replay the affected records safely. Do not silently accept arbitrary field names based on a guess.
Then introduce a record whose order has not arrived yet. That may be a timing dependency rather than malformed data. Give it a bounded recovery policy and preserve the reason so it is not confused with a permanently missing identifier.
Finish with the acceptance rule, quarantine record, correction authority and replay behaviour. Use the mock interview strategy to explain the design through one record's lifecycle. The goal is to prevent silent loss and false attribution while making incomplete data visible and recoverable under a defined process.