Data Engineering Interview: How Do You Design a Right-to-Erasure Data Deletion Pipeline?

Question and Context

A company stores user data in PostgreSQL, search indexes, caches, an append-only event log, an Iceberg lakehouse, a warehouse, third-party processors, and daily backups. Design a pipeline that executes an approved right-to-erasure request. It must resolve the subject's aliases, handle partial failures, prevent old events or restored backups from recreating deleted data, and produce reliable evidence of completion.

Assume a privacy or legal service has already verified the requester, approved the request, defined its scope, recorded any applicable exception, and supplied a policy deadline. The data platform executes that decision. The interview does not ask the engineer to interpret law.

Keep the policy boundary explicit. GDPR Article 17 defines a right to erasure and also lists conditions and exceptions, so an approved request needs a recorded scope. Article 19 can require communication to recipients. Live systems, backups, and versioned tables also expose different deletion states: backup data may remain until overwrite while being put beyond use, and a deleted Iceberg row can remain in a file referenced by an older snapshot. The workflow must represent those states instead of collapsing them into one database response.

The strongest design treats erasure as a durable, policy-scoped data workflow. It starts from a verified subject identity, derives a versioned target manifest from the data inventory and lineage graph, executes idempotent work per target, blocks resurrection, and verifies every required target before completion.

What the Interviewer Is Evaluating

First, can the candidate define the contract? Account closure, a business-level delete event, access restriction, and approved erasure have different semantics. A deletion service needs the approved scope, effective cutoff, policy deadline, exceptions, and evidence requirements. It should not silently invent them.

Second, can the candidate locate a person across real data models? An email address is rarely enough. The same person may have account IDs, tenant-scoped IDs, device IDs, payment-customer IDs, support identities, and identifiers replaced during account merges. A strong answer introduces a controlled identity-resolution step and considers shared records that belong to more than one subject.

Third, can the candidate reason about heterogeneous deletion? A row store can hard-delete or redact rows. Search and caches require invalidation. Immutable object files may require rewriting. Lakehouse deletes create new snapshots while older snapshots still reference earlier files. Aggregate data requires an anonymization and recomputation decision. Backups and external processors have their own completion semantics.

Fourth, can the workflow survive retries and outages? A synchronous request that fans out to every system will time out and leave ambiguous partial state. The interviewer expects durable state, idempotent target tasks, bounded retries, ownership, deadlines, and a way to distinguish retryable failure, documented retention, exemption, and permanent implementation gaps.

Finally, can the candidate prove that data stays deleted? A green orchestration job is weak evidence. The design must test target absence, retained versions, replay paths, restore procedures, newly discovered stores, and downstream confirmations. It must retain enough non-personal or minimized control evidence to prevent resurrection without preserving the very personal data that was approved for erasure.

Clarifying Questions to Ask

What exactly has been approved? Which subject, jurisdictions, data purposes, time range, products, and legal exceptions are in scope? Who owns the final policy decision?
What is the subject key? Is there a stable internal subject ID? Which historical aliases, merged accounts, tenant IDs, device IDs, and external processor IDs must it resolve?
Which records are shared? Orders, conversations, organization records, fraud evidence, or financial transactions may refer to several people or have an approved retention requirement. Which fields can be erased without corrupting another person's record?
What is the data inventory? Do all stores declare an owner, subject locator, deletion mode, lineage, retention behavior, verifier, and restore procedure? How are unregistered assets detected?
Which stores are mutable? Can the event log and object files be rewritten? Which lakehouse snapshots and warehouse time-travel versions remain queryable after a logical delete?
What counts as completion for backups? Can a backup be selectively rewritten, must it age out, or can it be restricted beyond use? How is an old backup made safe before restored services accept traffic?
Can new data arrive after the cutoff? Does account closure block new activity? Can delayed events, CDC retries, imports, or a recreated account use the old identifier?
Which processors received the data? Is there an API, ticket, or contractual confirmation path? What evidence is required before their target task reaches a terminal state?
What are the deadline and escalation rules? Which failures page an owner, when does a request become overdue, and who may approve a documented exception?
How will verification avoid leaking data? Can probes return counts, snapshot IDs, and salted evidence rather than copying personal values into logs?

30-Second Answer Framework

“I would execute an approved erasure request through a durable workflow. A synchronous fan-out leaves ambiguous partial state when one target times out. A controlled identity service resolves the subject to opaque internal and external identifiers. A versioned catalog and lineage graph generate a target manifest, and each adapter performs an idempotent delete, rewrite, restriction, or processor notification. A suppression record stops old events and restored backups from recreating data. Completion requires target-level absence checks, snapshot and backup-retention evidence, processor confirmations, and a replay test. Any newly discovered asset reopens the request.”

Step-by-Step Deep Dive

Step 1: Separate the policy decision from pipeline execution

Create an immutable request envelope after identity verification and scope approval. It should contain a request ID, an opaque subject reference, approved product and purpose scope, cutoff, deadline, policy version, exception references, and the authorizing decision. Keep raw identity documents and free-form legal notes out of the orchestration ledger.

Use explicit states such as received, authorized, planned, executing, verifying, completed, partially completed, rejected, and overdue. State transitions are append-only and attributable. A request can finish only when every item in the approved target manifest has an allowed terminal result: erased, rendered beyond use until a dated expiry, confirmed by a processor, or covered by a documented exception.

This boundary avoids two dangerous shortcuts. Engineers do not decide that every aggregate is anonymous, and the legal service does not mark a request complete merely because a workflow was queued. Each side supplies the decision or evidence it owns.

Step 2: Resolve identity once and preserve history safely

Resolve the verified person to a stable subject key, then expand it through a restricted identity graph. Candidate edges include current and previous account IDs, merged-account IDs, tenant-scoped IDs, device identifiers, payment-customer IDs, CRM contacts, and third-party processor references.

The graph needs effective dates and provenance. Reused email addresses or phone numbers cannot be treated as timeless proof that two accounts belong to one person. Shared objects need field-level rules: deleting one participant should not delete another participant's order or message, but the first participant's direct identifiers may need removal or replacement.

Freeze a request-specific identity version in the plan. If an alias is discovered later, append it and regenerate the affected targets. Store only minimized, access-controlled locators in task payloads. Logs should use request IDs, target IDs, counts, and versions rather than names or raw email addresses.

Step 3: Generate a target manifest from inventory and lineage

Every data asset that may hold subject data should register a deletion contract:

owner and escalation channel;
subject locator and identity namespace;
data purpose and retention class;
upstream and downstream lineage;
deletion action, such as hard delete, field redaction, file rewrite, key destruction, access restriction, or processor notification;
expected snapshot, time-travel, and backup behavior;
an idempotency key and supported retry semantics;
verification query or probe;
evidence schema and terminal-state rules.

The planner joins the approved scope, frozen identity set, catalog, and lineage graph to produce a versioned manifest. The manifest is reviewable before execution and includes targets with zero expected matches; an unexplained omission is more dangerous than an explicit zero.

Catalog coverage needs its own control. Scan storage accounts, warehouses, topics, buckets, schemas, indexes, and processor registries for unregistered assets. Compare runtime access logs and lineage events with the catalog. A newly registered downstream asset that contains in-scope data should create a target task for every open request and may reopen a completed request according to policy.

Step 4: Execute a durable, idempotent workflow

Use a queue or workflow engine with at-least-once delivery. The request state machine schedules one task per target and identity namespace. The target adapter derives its idempotency key from the request, target, subject locator version, and action version. Repeating a completed task returns the same terminal evidence; it does not create a second, ambiguous mutation.

Persist target status independently: pending, running, retryable failure, blocked, erased, restricted until expiry, processor pending, exception, and verification failed. Use bounded exponential backoff for transient errors, a dead-letter or blocked state for exhausted attempts, and an owner-visible deadline. A partial outage must not roll successful deletes back.

The orchestrator should not hold a distributed transaction across all stores. It behaves like a saga whose forward actions converge on the approved end state. Compensation usually means correcting an overbroad redaction from a protected source of truth under separate authorization, not restoring all previously erased data.

Step 5: Choose the deletion action for each storage class

Transactional databases. Locate rows by stable subject keys and mapped aliases. Hard-delete rows that are solely owned by the subject. For shared or retained records, redact the approved fields or replace the subject link with a non-identifying value. Respect foreign-key order and verify both primary and secondary tables.

Search, caches, features, and vector indexes. Remove documents and embeddings, invalidate cache keys, and force refresh where indexes are asynchronous. A source-table delete does not prove that an old search document or feature vector disappeared. Verifiers must query the serving surface as well as the source.

Event logs and raw object storage. If records can be rewritten safely, compact affected partitions without the subject. If the source is immutable for a retention window, register an erasure tombstone or suppression token and make every materializer, replay, export, and bootstrap path consult it. The source itself still follows the approved restriction or expiry plan; a tombstone alone does not claim physical erasure.

Lakehouse tables. Apply equality or position deletes where appropriate, or rewrite affected files when stronger physical removal is required. Record the commit and snapshot IDs. A current-snapshot query may show zero rows while older snapshots still reference the files. Apache Iceberg states that data files remain until no retained snapshot references them, so the task must track snapshot expiry and orphan-file cleanup before claiming the corresponding physical-removal stage.

Warehouse tables and materialized views. Delete keyed rows, rebuild affected partitions when necessary, refresh materialized views, and enumerate clones, extracts, and time-travel versions. Product-specific retention is configuration, not a universal constant. For example, BigQuery documents configurable time travel and an additional fail-safe period; the manifest should read actual platform policy and record when older versions become inaccessible.

Third-party processors. Send the scoped request using the processor's supported channel, attach a stable idempotency reference, and retain acknowledgment and completion status. Article 19 of GDPR covers communication to recipients in applicable cases. A sent email is not completion evidence when the contract provides a machine-readable confirmation or ticket state.

Step 6: Handle aggregates, models, and anonymization deliberately

Ask whether an output still permits the subject to be identified or singled out. Pseudonymized data remains linked through a key or token and cannot be called anonymous merely because the email column was removed. Truly anonymous aggregate data may fall outside the erasure target, but the privacy owner must approve that classification.

For keyed or small-cohort aggregates, rebuild the affected partition from allowed source records. Subtraction works only for invertible metrics with enough retained contribution metadata. Percentiles, sketches, trained embeddings, and many model artifacts are not safely corrected by subtracting one row. Choose a documented rebuild, retraining policy, or approved anonymization decision.

Model handling depends on the threat and product contract. Remove subject-level features, examples, retrieval documents, caches, and evaluation fixtures first. Then apply the approved retraining or model-unlearning policy if the model itself is in scope. The deletion pipeline records that policy's decision and evidence; it should not promise that deleting a training row automatically removes its influence from an existing model.

Step 7: Prevent resurrection from races, replay, and restore

Maintain a minimized suppression registry keyed by an opaque subject token and request cutoff. Ingestion and materialization paths check it before writing old events into serving or analytical stores. The registry needs stricter access and retention controls than ordinary logs because it exists specifically to recognize a deleted subject.

Order deletion against concurrent ingestion. Partition work by subject key where possible, or record source watermarks and repeat a closing scan after all writers pass the cutoff. Decide separately how genuinely new, authorized activity after the request is handled. An old event replay is suppressed; a person creating a new account under a valid product purpose may require a new subject epoch rather than permanent global blocking.

Every restore runbook must reapply the deletion ledger and suppression set before the restored service becomes readable or emits downstream data. Test this with restore drills. ICO guidance allows that backup data may remain until overwrite in some circumstances while requiring it to be beyond use; the operational consequence is access restriction plus deletion reapplication, documented expiry, and no normal-purpose processing from the backup.

Step 8: Verify completion with independent evidence

The adapter that performs a mutation can emit execution evidence, but a separate verifier should determine completion. For each target, capture:

target and schema version;
identity-locator version;
action, attempt, and completion timestamps;
rows, documents, objects, or files affected;
current-surface absence probe;
retained snapshot or backup state and expected expiry;
processor confirmation reference;
verifier version and result.

Probe with counts and opaque keys. Avoid copying deleted values into the evidence store. Sample-based checks can supplement but cannot replace deterministic checks for the request being completed.

Run end-to-end controls: submit the same request twice; interrupt each target after mutation but before acknowledgment; replay an old event; restore an old backup into isolation; merge and split identities; recreate an account; add an unregistered downstream table; hold one processor offline; and exercise a shared record plus a documented exception. Completion should fail closed when a required target has no verifier or unknown status.

Step 9: Operate the system with measurable obligations

Track request completion time against the policy deadline, overdue and partially completed requests, target success and retry rates, manifest coverage, unknown assets, processor confirmation latency, snapshot and backup expiry backlog, replay-resurrection incidents, and restore reapplication success.

Alert on workflow state, not only job exceptions. A request waiting indefinitely for a processor or snapshot expiry can have no running failures and still be overdue. Give each blocked target an owner and an escalation path. Review exception use and zero-match targets for suspicious patterns.

The cost model also matters. Orchestration is roughly proportional to the number of targets, while physical work depends on matched rows and the bytes in affected files or partitions. Batch file rewrites and snapshot maintenance without hiding per-request traceability. A request should still show which shared maintenance job supplied its evidence.

High-Quality Sample Answer

“I would receive only an authorized request envelope: request ID, opaque subject reference, approved scope, cutoff, deadline, policy version, and exception references. A restricted identity service expands that subject into versioned internal, historical, tenant, device, and processor IDs. It does not use an email address as a timeless primary key.

Next I would generate a versioned target manifest from the catalog and lineage graph. Every target declares its owner, locator, action, retention behavior, verifier, and evidence contract. The catalog includes live databases, indexes, caches, raw events, object storage, lakehouse and warehouse tables, materialized outputs, exports, backups, and processors. Infrastructure discovery and runtime lineage compare actual assets with that catalog, so an unknown store blocks or reopens completion.

Execution is an asynchronous state machine with at-least-once delivery and idempotent per-target tasks. A retry uses the request, target, identity version, and action version as its key. Row stores hard-delete solely owned rows and redact approved fields in shared or retained records. Serving indexes, caches, feature stores, and vector stores are deleted and independently queried. Raw immutable logs receive a suppression record and follow their approved restriction or expiry plan; every replay path must consult that suppression record.

For Iceberg, I would apply row deletes or rewrite affected files, record the commit and snapshot, and track old snapshot expiry because a zero-row current query does not mean the underlying file has been removed. Warehouses need the same treatment for clones, materialized views, extracts, and time-travel versions. Keyed or small-cohort aggregates are rebuilt from allowed inputs. Truly anonymous aggregates may be retained only after the privacy owner approves that classification.

Backups have an explicit terminal state. If selective rewriting is unavailable, the request records restricted-beyond-use status and the scheduled overwrite date. A restore cannot serve traffic until the deletion ledger and suppression registry have been reapplied. Third-party processors get idempotent tasks and remain pending until the required confirmation arrives.

To prevent races, I would record source watermarks and run a closing scan after writers cross the cutoff. Delayed and replayed old events are suppressed. New authorized activity receives a new subject epoch when policy permits it, so replay protection does not silently become a lifetime ban.

A separate verifier checks each serving surface, current table state, retained snapshots, backup status, and processor evidence. Only when every target has an allowed terminal state can the request complete. I would test duplicate delivery, crashes between mutation and acknowledgment, offline targets, replay, isolated restore, account recreation, identity merges, shared records, and newly discovered assets.

My operating metrics would include deadline compliance, partial and overdue counts, catalog coverage, unknown targets, replay resurrection, snapshot and backup expiry backlog, processor latency, and restore reapplication success. This design gives the company a durable proof trail while keeping personal values out of ordinary logs and keeping legal scope decisions outside the data pipeline.”

Common Mistakes

Deleting only the primary account row → Copies remain in indexes, analytical tables, exports, and processors → Generate a lineage-backed target manifest and verify every required serving and storage surface.
Using email as the universal subject key → Emails change, can be reused, and miss merged or external identities → Resolve a verified subject through a versioned identity graph with provenance.
Calling a synchronous fan-out from the request API → One timeout creates unknown partial state and unsafe retries → Use durable per-target tasks, explicit states, idempotency, and owner escalation.
Treating soft delete as completed erasure → Data remains readable to privileged queries, exports, replay, or restores → Use soft delete only as an approved restriction phase and track the final action or expiry.
Marking an Iceberg delete complete after the current query returns zero → Retained snapshots may still reference old files → Record snapshot state and wait for the approved expiry and cleanup stage.
Assuming every aggregate is anonymous → Small cohorts, join keys, and pseudonyms can still identify or single out a person → Obtain an explicit anonymization decision and rebuild affected outputs when needed.
Ignoring replay and restore → A later backfill or backup recovery can recreate the data everywhere → Maintain a minimized suppression registry and reapply the deletion ledger before restored data is served.
Logging raw identity values as evidence → The audit trail becomes a new uncontrolled copy of the deleted data → Store opaque references, counts, versions, state transitions, and restricted evidence.
Treating “request sent to processor” as completion → Delivery does not prove the processor acted → Track acknowledgment, terminal confirmation, deadline, and escalation according to the contract.
Letting the mutation job verify itself → A successful call may miss stale indexes, snapshots, or silent no-ops → Run independent target probes and end-to-end replay and restore tests.

Follow-Up Questions and Responses

Follow-up 1: How would you handle a deletion request that affects aggregate tables?

Classify each output first. A genuinely anonymous aggregate may be retained if the responsible privacy owner approves that conclusion. Pseudonymized, keyed, or small-cohort outputs remain candidates for action. Rebuild affected partitions from allowed source records. Simple subtraction is safe only for invertible aggregates with reliable contribution metadata; percentiles, sketches, and many learned artifacts require recomputation or a separate approved policy.

Follow-up 2: How do you stop Kafka replay from recreating deleted rows?

Write a durable suppression record before derived systems declare completion. Every consumer, backfill, materializer, and bootstrap path checks an opaque subject token and event cutoff. Record a source watermark and run a closing scan after current writers pass it. Test by replaying pre-cutoff events into an isolated environment and proving that no target becomes readable again.

Follow-up 3: What happens when an old backup is restored?

Restore into an isolated environment. Before opening traffic or downstream emission, apply every relevant deletion request recorded after the backup was created and before the restore, refresh the suppression registry, and run target verifiers. Record the highest deletion-ledger position applied to the restored service. A backup that remains until scheduled overwrite stays access-restricted and cannot be used for ordinary processing.

Follow-up 4: What if a required data store is unavailable near the deadline?

Keep the request partially completed or overdue, retain successful target results, and retry the unavailable target idempotently. Escalate to its named owner and the privacy operations owner before the policy deadline. Only an authorized exception can change the target's required terminal state. The orchestrator must never convert an exhausted retry into silent success.

Follow-up 5: How would you support account recreation after deletion?

Separate historical replay protection from future authorized activity. Give the recreated account a new subject epoch and new internal IDs. The suppression registry continues to reject old identifiers and events at or before the approved cutoff, while policy controls decide whether new data may be collected. Identity resolution must prevent the new epoch from accidentally reattaching old derived records.

Follow-up 6: Can cryptographic erasure replace physical deletion?

Only under a verified storage design. If all relevant copies are encrypted with a unique subject-scoped key, destroying that key can make the data inaccessible. Shared keys, plaintext indexes, logs, caches, exports, backups, or retained key copies break the claim. Treat key destruction as one target action with its own inventory and verifier, not as a universal shortcut.

Follow-up 7: How do you know the catalog is complete?

Continuously compare declared assets with cloud inventory, warehouse metadata, topic and bucket listings, processor registries, and runtime lineage or access events. Require a deletion contract before a new asset may process subject data. Unknown assets create a coverage alert and block or reopen affected requests. Periodic restore and replay drills expose paths that static lineage often misses.