Why it matters
Analytics pipelines copy data liberally: raw landing, staging, modelled tables, extracts and backups. Each copy multiplies the places a personal field can leak from and the work required to honour a deletion request. Because transforms are written by people who did not see the source contract, classification has to travel with the data. Otherwise the only reliable map of personal data is an incident report.
Mental model
Personal data in a pipeline is like water in a building: it spreads to every room the pipes reach, and knowing where the main valve is does not tell you which walls are wet. The metaphor breaks because water damage is visible, while a copied column in a downstream table looks exactly like any other column.
How it works
- 1Classify each source column at ingestion and store the classification as metadata beside the table, not in a wiki.
- 2Decide per field whether it is needed at all, and drop or aggregate fields that no query requires.
- 3Replace direct identifiers with tokens or hashes at the boundary, keeping the mapping in a separate controlled store.
- 4Restrict raw schemas to a small role and give analysts access only to modelled views.
- 5Propagate classification through transforms so derived tables inherit the sensitivity of their inputs.
- 6Build a deletion path that covers live tables, snapshots, exports and backups, and test it on one subject.
Step 1 of 6
Classify each source column at ingestion and store the classification as metadata beside the table, not in a wiki.
Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.
What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.
Text version
The page's steps: 1. Classify each source column at ingestion and store the classification as metadata beside … 2. Decide per field whether it is needed at all, and drop or aggregate fields that no query … 3. Replace direct identifiers with tokens or hashes at the boundary, keeping the mapping in … 4. Restrict raw schemas to a small role and give analysts access only to modelled views. 5. Propagate classification through transforms so derived tables inherit the sensitivity of … 6. Build a deletion path that covers live tables, snapshots, exports and backups, and test i…
- Classify each source column at ingestion and store the classification as metadata beside the table, not in a wiki.
- Decide per field whether it is needed at all, and drop or aggregate fields that no query requires.
- Replace direct identifiers with tokens or hashes at the boundary, keeping the mapping in a separate controlled store.
- Restrict raw schemas to a small role and give analysts access only to modelled views.
- Propagate classification through transforms so derived tables inherit the sensitivity of their inputs.
- Build a deletion path that covers live tables, snapshots, exports and backups, and test it on one subject.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| Drop fields at ingestion | Smallest exposure surface | Some future analysis is impossible | The field is needed for a legal or billing requirement |
| Tokenise identifiers | Joins without exposing raw values | A mapping store to secure and rotate | The token is deterministic and reversible by dictionary attack |
| Hash with a salt | Cheap pseudonymisation | Salt management and no reversibility | Downstream needs the original value for a legitimate purpose |
| Keep raw with strict access | Full reprocessing ability | Deletion and audit burden | Retention exceeds the stated purpose |
Good for
Smallest exposure surface
Cost
Some future analysis is impossible
Fails when
The field is needed for a legal or billing requirement
- The field is needed for a legal or billing requirement
- The token is deterministic and reversible by dictionary attack
- Downstream needs the original value for a legitimate purpose
- Retention exceeds the stated purpose
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Drop fields at ingestion is good for Smallest exposure surface, costs Some future analysis is impossible, and fails when The field is needed for a legal or billing requirement. Tokenise identifiers is good for Joins without exposing raw values, costs A mapping store to secure and rotate, and fails when The token is deterministic and reversible by dictionary attack. Hash with a salt is good for Cheap pseudonymisation, costs Salt management and no reversibility, and fails when Downstream needs the original value for a legitimate purpose. Keep raw with strict access is good for Full reprocessing ability, costs Deletion and audit burden, and fails when Retention exceeds the stated purpose
Failure modes
- Unlabelled copy. A personal column appears in a modelled table nobody classified. Debug: scan column names and sample values against the classification list, including derived tables.
- Deletion gap. A subject is removed from the main table but remains in snapshots and exports. Debug: search every snapshot and export for the subject key and count matches.
- Reversible token. A hash of a low-entropy field is reversed by enumerating candidates. Debug: attempt a dictionary match against a sample of tokens.
In production
Keep a register of personal fields with owner, purpose and retention, and reconcile it against actual schemas monthly. Alert on new columns in sensitive schemas and rehearse one deletion end to end.
Common misconceptions
People think: Pseudonymisation makes data non-personal.
Actually: It reduces direct identifiability; if the mapping or a low-entropy input exists, the data can still be re-identified.
People think: Deleting from the main table satisfies erasure.
Actually: Snapshots, exports, caches and backups are copies, and each needs a defined path or an expiry that bounds it.
Interview angle
Show that you can find personal data, limit its spread and delete it, with the metadata and access controls that make each step possible. Mention derived tables and snapshots. Weak answers say they would encrypt the database and stop there.
Sources
- NIST SP 800-122, Guide to Protecting the Confidentiality of PII — PII identification, minimisation and safeguards.
- GDPR, Article 4 definitions — the legal definition of personal data and processing.
- PostgreSQL documentation, Row Security Policies — enforcing access boundaries inside the database.