How do you design a warehouse so that deletion requests are practical?

Keep personal attributes in one restricted identity table, use pseudonymous keys in facts and marts, tag personal columns and track lineage, automate deletion jobs that propagate to dependent tables, and set retention for snapshots, exports and backups.

What is the difference between pseudonymisation and anonymisation?

Pseudonymisation replaces identifiers with keys but can be reversed with extra information (the mapping or secret), so the data remains personal data. Anonymisation makes re-identification impractical, so the data is no longer personal data.

Why is a keyed hash (HMAC) better than a plain hash for pseudonymous keys?

Plain hashes of emails or phone numbers can be reversed by hashing candidate values (dictionary attacks). An HMAC with a secret key can't be recomputed without the secret, which can also be rotated or destroyed.

Personal Data, Privacy and Retention — ETL and Data Pipelines

Scenario

Example company: a customer asks the company to delete their personal data under the GDPR's right to erasure. The production database deletes them in minutes. The data team then finds the customer's email and address in raw tables, staging tables, three marts, two snapshots, exported CSVs and a backup.

What you see in production

Each deletion request takes days of manual searching.
Some copies are missed; a marketing export months later still includes the person.
Analysts have read access to raw email addresses they never need.

The naive code

-- raw.customers copied as-is into every downstream model
SELECT * FROM raw.customers;      -- email, phone, address flow everywhere

Reproduce it

The simulation propagates a deletion through tables that copied raw personal data, then through a design that stores a pseudonymous key everywhere and personal data in one place.

Deleting a person: copies everywhere vs one identity tableTry it yourself

import hashlib
import hmac

SECRET = b"rotate-me-and-keep-in-a-secrets-manager"

def pseudonym(email):
    return hmac.new(SECRET, email.lower().encode(), hashlib.sha256).hexdigest()[:12]

email = "[email protected]"

# Design A: personal data copied into every table
tables_a = {name: [{"email": email, "amount": 50}] for name in
            ["raw.orders", "stg_orders", "fct_orders", "mart_marketing", "snapshot_customers"]}
deleted_a = [name for name in tables_a if name.startswith("raw")]     # the team found only the raw copy
leftover = [name for name in tables_a if name not in deleted_a]
print("design A: copies still holding the email:", leftover)

# Design B: one identity table holds personal data; everything else uses a pseudonymous key
key = pseudonym(email)
identity = {key: {"email": email}}
facts = {"fct_orders": [{"customer_key": key, "amount": 50}], "mart_marketing": [{"customer_key": key}]}
del identity[key]                                   # one delete removes the link to the person
print("design B: pseudonymous key", key, "| identity rows left:", len(identity))
print("design B: facts keep aggregates without personal data:", facts["fct_orders"])

import hashlib
import hmac

SECRET = b"rotate-me-and-keep-in-a-secrets-manager"

def pseudonym(email):
    return hmac.new(SECRET, email.lower().encode(), hashlib.sha256).hexdigest()[:12]

email = "ada@example.com"

# Design A: personal data copied into every table
tables_a = {name: [{"email": email, "amount": 50}] for name in
            ["raw.orders", "stg_orders", "fct_orders", "mart_marketing", "snapshot_customers"]}
deleted_a = [name for name in tables_a if name.startswith("raw")]     # the team found only the raw copy
leftover = [name for name in tables_a if name not in deleted_a]
print("design A: copies still holding the email:", leftover)

# Design B: one identity table holds personal data; everything else uses a pseudonymous key
key = pseudonym(email)
identity = {key: {"email": email}}
facts = {"fct_orders": [{"customer_key": key, "amount": 50}], "mart_marketing": [{"customer_key": key}]}
del identity[key]                                   # one delete removes the link to the person
print("design B: pseudonymous key", key, "| identity rows left:", len(identity))
print("design B: facts keep aggregates without personal data:", facts["fct_orders"])

Output

design A: copies still holding the email: ['stg_orders', 'fct_orders', 'mart_marketing', 'snapshot_customers']
design B: pseudonymous key 204d3985b5d8 | identity rows left: 0
design B: facts keep aggregates without personal data: [{'customer_key': '204d3985b5d8', 'amount': 50}]

Root cause

Personal data was copied by default into every layer, without an inventory of where it lived or who needed it. Deletion then meant finding every copy by hand.

The fix

Minimise: load only the personal fields you need. Drop or mask the rest at extraction (one of the few good reasons to transform before loading).
Separate identity from activity: keep personal attributes in one restricted identity table; use a pseudonymous key (a random ID, or a keyed hash such as HMAC with a secret) in facts and marts. Deleting or anonymising the identity row breaks the link to the person.
Classify and tag columns containing personal data, and use lineage to find every downstream copy.
Column-level access control and masking so most users never see raw identifiers.
Automate deletion: a deletion request becomes a job that deletes or anonymises the identity row, propagates to dependent tables, and records proof. Make sure snapshots, exports and backups have defined retention periods so copies expire.
Note that pseudonymised data is generally still personal data under the GDPR; anonymisation must make re-identification impractical. Involve your privacy or legal team in the design.

Trade-offs

Pseudonymisation adds a join for the few use cases that need contact details.
Short retention periods limit long-term analyses; aggregate data that no longer identifies people can usually be kept longer.

Detect it in production

Scan tables regularly for columns that look like personal data (emails, phone numbers) but aren't tagged.
Track deletion-request completion time and verify deletion across all registered tables.

Key points

Copying personal data everywhere makes deletion and access control unmanageable.
Minimise, keep identity in one restricted table, and use pseudonymous keys elsewhere.
Tag personal data, automate deletion requests, and set retention for snapshots, exports and backups.