Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 15 of 17

PII in pipelines

0 of 17 complete

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

Mechanism, step by stepInteractive
  1. 1Classify each source column at ingestion and store the classification as metadata beside the table, not in a wiki.
  2. 2Decide per field whether it is needed at all, and drop or aggregate fields that no query requires.
  3. 3Replace direct identifiers with tokens or hashes at the boundary, keeping the mapping in a separate controlled store.
  4. 4Restrict raw schemas to a small role and give analysts access only to modelled views.
  5. 5Propagate classification through transforms so derived tables inherit the sensitivity of their inputs.
  6. 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.

Step 1/6 · step 1

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…

  1. Classify each source column at ingestion and store the classification as metadata beside the table, not in a wiki.
  2. Decide per field whether it is needed at all, and drop or aggregate fields that no query requires.
  3. Replace direct identifiers with tokens or hashes at the boundary, keeping the mapping in a separate controlled store.
  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 their inputs.
  6. Build a deletion path that covers live tables, snapshots, exports and backups, and test it on one subject.

Trade-offs

ChoiceGood forCostFails when
Drop fields at ingestionSmallest exposure surfaceSome future analysis is impossibleThe field is needed for a legal or billing requirement
Tokenise identifiersJoins without exposing raw valuesA mapping store to secure and rotateThe token is deterministic and reversible by dictionary attack
Hash with a saltCheap pseudonymisationSalt management and no reversibilityDownstream needs the original value for a legitimate purpose
Keep raw with strict accessFull reprocessing abilityDeletion and audit burdenRetention exceeds the stated purpose
Trade-off mapInteractive

Good for

Smallest exposure surface

Cost

Some future analysis is impossible

Fails when

The field is needed for a legal or billing requirement

  1. The field is needed for a legal or billing requirement
  2. The token is deterministic and reversible by dictionary attack
  3. Downstream needs the original value for a legitimate purpose
  4. 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