PII in Pipelines
Where personal data actually ends up in a data platform: raw landing zones, debug logs, error messages carrying rows, notebook extracts, training sets, and the temporary table nobody deleted.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
If a regulator asked you to list every place in your platform where this customer's email address exists, could you produce the list?
The person who has to answer that question, and who is usually a data engineer rather than a lawyer. Also every incident responder scoping a breach, because the scope of an incident is exactly the copy inventory, and an organisation that cannot enumerate its copies has to assume the worst about all of them.
The unit here is the copy: one identifiable value in one location, reachable by one set of principals, under one retention. A person is not one row in your platform. They are a row in raw, an event in the log, a dimension row, several fact rows, a value in a dozen aggregates, a line in an error log, a cell in a notebook checkpoint and a token in a training set.
PII lives in the customer table. We protect the customer table: it is masked, access is restricted, and it is covered by the deletion tooling. Everything else in the warehouse is behavioural data and is therefore lower risk. This is how nearly every platform is designed, and the customer table is genuinely the right place to start.
The raw landing zone holds the unmodified source payload, including every field the operational system sent, including the ones the modelled layer deliberately drops. Raw is by construction the least filtered and the longest retained copy on the platform (The Raw Landing Zone).
- The raw landing zone holds the unmodified source payload, including every field the operational system sent, including the ones the modelled layer deliberately drops. Raw is by construction the least filtered and the longest retained copy on the platform (The Raw Landing Zone).
- A pipeline logs the record it failed on. That is the single most useful thing to log during an incident and it writes a customer's full row into a log store with a different access model, different retention and no classification at all (Secrets in Logs).
- An error message contains a row. A constraint violation, a cast failure or a validation error frequently embeds the offending value, and those messages travel further than logs do — into alerting channels, ticket systems and, increasingly, into an LLM asked to diagnose the failure (Error Handling and Information Leakage).
- An analyst pulled a sample to a notebook eighteen months ago. The notebook checkpoint is in a bucket, the CSV is on a laptop, and neither is in any inventory. This is the most common real leak and the one with the fewest controls (Data Discovery).
- A model was trained on a table that included personal fields, and the parameters now encode them in a form that is neither a row nor deletable (Feature Pipelines).
- A backfill wrote to
tmp_orders_fix_2024_03in a scratch schema, was validated, was published, and the temporary table is still there — unclassified, unmasked, unretained and readable by everyone who can read the schema (Planning a Backfill). - A dead-letter queue holds the messages that could not be processed. They are, by definition, the full original payloads, retained until someone drains it, which is usually never (Ingestion Failure & Recovery).
What is actually happening
- The thesis of the module in one sentence: the moment data is copied out of the operational system, the copy inherits every obligation of the original and none of the mechanisms that enforced them. The application enforced authorization per request, logged access, scoped a session, and deleted on request. The copy has none of that, and nothing tells you it has lost it.
- A data platform is a copy machine, and its value comes from that. Every stage duplicates: ingestion copies to raw, transformation copies to staging, modelling copies to marts, serving copies to extracts and caches, and operations copies to logs, backups and replicas. Counting the deliberate copies understates it by a wide margin (The Fundamental Data Journey).
- Copies fall into two classes with different treatments. Modelled copies are in the catalog, have owners and can be governed by policy. Operational copies — logs, error payloads, dead letters, caches, temp tables, notebook outputs, backups — are produced by machinery rather than by a pipeline definition, and they are the ones with no governance surface.
- The operational copies share a defining property: they exist because something went wrong, and the thing that went wrong is precisely why someone captured the whole record. Debuggability and minimization are in genuine tension, and pretending otherwise produces either an ungoverned log store or an undiagnosable platform.
- Free text and semi-structured payloads defeat column-level controls entirely. A masking policy operates on a column; a JSON blob is one column, and a support transcript is one column, and neither can be governed by a per-column rule (Data Classification).
- Personal data also arrives where nobody looks for it: user-agent strings and IP addresses in access logs, precise timestamps that make behaviour unique, free-text search queries, and file names in uploads.
The copy inventory nobody wrote down
Ask where a customer's email address lives and you will get one answer: the customer dimension. The list below is the answer after an hour of looking, and it is not exhaustive. Each entry is a real location in a real platform, and each has a different owner, a different access model and a different retention.
What makes this list dangerous is that the first three entries are deliberate design decisions that everybody knows about, and the rest are consequences of ordinary engineering practice that nobody decided. Nobody approved the log line. Nobody approved the checkpoint bucket. They exist because a debugging session needed them once.
Read the tree as an audit. For each entry, answer three questions: who can read it, when does it disappear, and would a deletion request reach it. Anywhere the answers are "unclear", "never" and "no" is where the platform's real privacy posture lives.
platform/
├── raw/ full source payloads, longest retention, least filtered
│ ├── users/dt=2026-08-25/*.json every field the source sent, including dropped ones
│ └── _dead_letter/ unparseable payloads, drained by nobody
├── warehouse/
│ ├── dim_customers governed, masked, in the catalog — the copy people think of
│ ├── stg_users SELECT * staging, inherits new columns automatically
│ ├── scratch/tmp_orders_fix_2024_03 a backfill artefact, unmasked, no owner, no expiry
│ └── quarantine/rejected_rows failing rows kept for triage, full payloads
├── logs/
│ ├── pipeline/transform-orders "failed on row: {…}" — the whole record, in a log store
│ └── access/query-history user, timestamp, and the SQL text, which contains predicates
├── alerts + tickets/ error messages copied out of the platform entirely
├── bi/
│ ├── extract-cache/ the BI tool's own materialisation, its own access model
│ └── downloads/ CSVs, on laptops, outside every control
├── notebooks/
│ └── checkpoints/ whatever anyone sampled, retained indefinitely
├── ml/
│ ├── training-sets/v3/ a frozen snapshot, deliberately immutable
│ └── model-artifacts/ parameters that encode what they were trained on
└── backups + snapshots/ a full copy of everything above, separate access modelThe debug log is the leak
A transformation fails on a malformed record. The engineer who wrote it did the right thing and logged enough to diagnose the problem, which means the record. That log line is now in a store with company-wide read access, a retention set by whoever configured the log platform, no classification, no masking policy and no connection to the deletion tooling.
It gets worse in the direction of helpfulness. The error is attached to an alert, the alert goes to a chat channel, someone pastes it into a ticket, and increasingly someone pastes it into an assistant to ask what the error means. Each hop moves the record further from any control and closer to permanent, and every hop is a person trying to fix a problem.
The pattern that resolves the tension is to split the two things the log line was doing. Diagnosis needs to know *which* record failed and *how* — that is an identifier and an error class, and it belongs in the log. Reproduction needs the record itself — that belongs in a quarantine table inside the platform, under the same classification, masking and retention as the dataset it came from, joinable back to the log by the identifier.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| A row fails validation in a transformation. | A full record appears in the pipeline log, and later in an alert and a ticket. | Exception handling serialises the offending object, which is the default behaviour of almost every logging idiom. | Log identifier plus error class; write the payload to a governed quarantine table; add a log-sampling scanner that alerts on the emitting pipeline, not on the line. |
| A database constraint rejects an insert. | The driver's error message embeds the conflicting values and is logged verbatim. | The message is generated by the database, not by your code, so a logging policy in your code does not cover it. | Truncate or redact driver messages at the logging boundary; never forward raw driver errors to systems outside the platform. |
| An ingestion job cannot parse a message. | The message lands in a dead-letter queue and stays there. | Dead-lettering is correct behaviour; draining is a task nobody owns and no alert fires on depth. | Alert on queue depth and oldest-message age, give the queue a retention, and treat it as a classified dataset because it holds full payloads. |
| A backfill needs a staging area. | A full unmasked copy of a modelled dataset exists in a scratch schema, indefinitely. | The scratch schema has no default expiry and no inherited classification, so nothing removes or governs the table. | Default TTL on scratch schemas, classification inherited from the source, and a scheduled report of tables older than the TTL. |
| An analyst needs to share a result. | A CSV in a chat message, then in a spreadsheet, then in a personal drive. | Sharing a query result is easier than granting access to a dataset, so export is the path of least resistance. | Make grants fast for low-classification data and give analysts a governed shared scratch area; log and review export events rather than only blocking them. |
| A pipeline error is pasted into an assistant for diagnosis. | A record leaves the organisation entirely, to a processor nobody assessed. | The error message already contained the record, so the leak happened at the logging step and this is only its final hop. | Fix it at the source by never putting records in messages; the paste is unstoppable and the content of what gets pasted is not. |
On a row-level failure, emit a log line containing the serialised record and the exception: `ERROR transform-orders: failed to parse {"email":"…","address":"…"} — invalid date`. It is one line of code, it is immediately useful, and the on-call engineer can diagnose from the log alone.Emit `ERROR transform-orders: parse_failure order_id=88213 field=ordered_at run_id=…` and write the full record to `quarantine.transform_orders_rejects`, a table in the governed platform inheriting the source's classification, masking and retention. The log line joins to the quarantine table by `order_id` and `run_id`.
The log store and the warehouse have different access models, different retention and different deletion tooling, and a record in the log store is outside all three. Splitting identifier from payload keeps diagnosis in the cheap, widely readable place and keeps the personal data in the place that already has controls — with no loss of debuggability, because the two are joinable.
Deciding what identity is allowed past the boundary
Almost all of the above becomes smaller if the platform does not hold direct identifiers in the first place. Tokenisation at ingest replaces an email or a national identifier with a stable surrogate, keeps the mapping in a separately governed vault, and lets the analytical platform reason about behaviour without ever holding identity (Surrogate Keys).
It is not free and it is not always right. Some analytical work genuinely needs the real value: matching against an external partner list, sending a message to a customer, or reconciling a discrepancy against the source system. Those needs are legitimate, and a design that ignores them produces a vault that everyone has access to, which is the same as no vault.
The decision below is the honest version. Note that the options are not ordered by strength — the right one depends on whether identity is needed for analysis, for output, or not at all, and most platforms end up applying different answers to different fields in the same table.
For a given identifying field, what crosses the ingestion boundary?
when The field is required for analysis or for producing output that reaches the person — a contact address for a campaign, a reference for reconciliation against the source.
cost Every copy in the platform is a copy of identity. The full inventory problem applies and deletion must reach all of it.
when Analysis needs to link records across sources and over time but never needs the value itself. This is the common case for user and customer identifiers.
cost A vault to operate and defend, a lookup on ingest, and a re-identification path that must be audited. The token still distinguishes individuals, so obligations follow it.
when The value is needed rarely, and erasure needs to be provable and cheap — destroying the key destroys every copy at once.
cost Key management becomes the critical path for reads, and a lost key is an unrecoverable dataset. Key-per-subject scales key count with user count (Deletion Requests).
when Nothing downstream needs it and nobody has asked for it. Applies to far more fields than teams expect — most of a source payload is never queried.
cost Irreversible. If a future question needs the field, it cannot be answered for the period during which it was dropped (Keeping Raw History: The Recovery Position and the Liability).
when The analysis needs the attribute but not its precision: a birth year rather than a date, a region rather than a coordinate, an hour rather than a millisecond.
cost Precision is gone permanently, and coarsening enough to prevent re-identification is a judgement that depends on the whole row rather than on the field.
How to build it
Most important first.
- Decide at the platform boundary what identity is allowed past it. Tokenising direct identifiers at ingest — replacing them with a stable surrogate and keeping the mapping in a separately governed vault — means most of the platform never holds them, and most of the copy problem simply disappears (Data Minimization, Surrogate Keys).
- Treat the raw landing zone as the most sensitive store on the platform, not the least. Restrict it to service principals and a small break-glass group, retain it shortest of any layer that can, and never point a BI tool at it (The Raw Landing Zone).
- Log identifiers, never records. A failing row should produce a log line with a primary key and an error class; the row itself belongs in a quarantine table that lives inside the governed platform under the same policy as its source (Structured Logging: Fields a Program Can Read).
- Make error messages boundary-aware. Truncate or hash values in messages that leave the platform, especially those that reach alerting channels and ticket systems, because those systems are outside every control you built (The Error Model: Structure Over Apology).
- Give ad-hoc work a governed destination. A scratch schema with automatic expiry and inherited classification is what stops analysts from exporting; taking away export without providing a scratch space just moves the copies to laptops (The Self-Service Data Platform).
- Put a clock on everything that is not a modelled dataset: temp tables, dead-letter queues, quarantine tables, notebook output prefixes and log stores all need a retention rule, and the rule should be short (Data Retention).
- Maintain the copy inventory as a real artefact — derived from lineage for modelled copies and from configuration for operational ones — and treat "we cannot enumerate the copies" as the incident it is (Data Lineage).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Column masking in the warehouse guarantees that queries through the warehouse against masked columns return masked values. It does not cover the raw layer, the log store, the backup, or any copy already taken.
- Tokenisation at ingest guarantees that the platform holds no direct identifier *for data ingested after it was turned on*. Everything ingested before is unaffected, and this asymmetry is why the boundary decision is expensive to make late.
- A retention rule on a log store guarantees deletion of what it covers on its clock. It does not cover copies of the log exported to a search index, an incident ticket, or a chat message.
- Nothing guarantees the absence of personal data in a free-text column. That is an unbounded claim over user input and no mechanism can make it, which is why free-text columns should be classified by their worst case rather than their intent.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The scanner sweep: sample values from every column in every dataset, plus a sample of log lines, and match against pattern and dictionary detectors for emails, phone numbers, national identifiers and payment-card formats. Report hits in datasets not tagged
personal. It is the only check that finds copies nobody declared. - It misses encoded and hashed values, free text where the identifier is embedded in a sentence, non-Latin and locale-specific formats, columns that were null in the sample, and anything in a store the scanner is not pointed at — which is usually the log store, the ticket system and the notebook bucket.
- It also produces confident false positives on structured references that resemble identifiers, and a team that gets a week of false positives will stop reading its output, which is a worse outcome than not running it.
- The relevant latency is the gap between a copy being created and being discoverable. Modelled copies appear in lineage on the next parse; a temp table appears when someone looks; a notebook export never appears.
- Deletion latency compounds it: an erasure request can only reach copies you can enumerate, so discovery latency is a lower bound on erasure completeness (Deletion Requests).
- Log stores usually have the shortest retention on the platform, which is the one place where poor freshness helps you — an ungoverned copy that expires in days is a bounded problem.
- A source adding a field adds it to raw immediately, whatever the modelled layer does with it. Raw is the layer with the least schema control and therefore the one where new personal data appears first (Schema Evolution).
- A field that changes meaning can become personal without any schema event — a free-text
notescolumn repurposed for customer correspondence is a reclassification with no trigger (Semantic Changes). - Adding a new consumer — a vector store, a training set, an agent's retrieval corpus — creates a new copy class with new mechanisms and usually no governance at all (The LLM Data Pipeline).
- Removing personal data from a copy you found is straightforward and is a rewrite: for immutable file formats that means rewriting the affected files and expiring old snapshots, not issuing a delete (Deletion Requests).
- Removing it from a copy you cannot enumerate is impossible, so recovery here is really discovery. Budget the effort accordingly: the inventory is the deliverable, and the deletions are the easy part.
- Where a copy cannot be rewritten — a trained model, an external system, a partner extract — the honest options are to expire it, to retrain or rebuild it, or to record it as residual risk. Pretending it was cleaned is the failure mode (Risk, Residual Risk and Honest Reporting).
What can go wrong
- The debug log is the leak. It has the longest reach, the weakest access model and the least classification of anything on the platform, and it exists because someone was being a good engineer.
- The dead-letter queue holds full payloads indefinitely, because draining it is never anyone's sprint work.
- A temp table from a backfill outlives the incident it was created for by years, holding a full unmasked copy of a modelled dataset.
- An LLM is handed a failing row to diagnose a pipeline error, sending personal data to a processor nobody assessed (Prompt Injection is the other risk of the same habit).
- The masking policy works perfectly on the warehouse and the same data sits unmasked in the lake files the warehouse reads (The Data Lake).
- A scanner is deployed, produces mostly false positives, is muted, and its existence is cited as coverage.
- Tokenisation is introduced and the vault becomes the highest-value target on the platform, concentrating risk that used to be spread out — a real improvement, but only if the vault is defended accordingly (Secrets Management).
- "We do not store PII, we only store events." Events carry user identifiers, device identifiers, IP addresses and timestamps. A user identifier is personal data under every regime that matters, because the entire point of it is to distinguish a person.
- "The lake is internal so it is fine." Internal is the population most breaches move through, and the lake is where the least filtered copy lives (Least Privilege).
- "Logs are not data." Logs are a dataset with a schema, a grain, a retention and a set of readers. The only thing that distinguishes them is that nobody modelled them, which is an argument for more governance, not less (Security-Safe Logging).
- "We masked the column, so the data is protected." You masked one read path of one copy. Ask where else those bytes are before believing it (Data Masking, Tokenisation & Encryption).
- "An extract is temporary." Nothing is more permanent than a temporary table with a date in its name.
- This lesson is the module's core claim, and it is why classification without a copy inventory is theatre: the label is on the modelled dataset, and the exposure is in the copy that has no label.
- The practical governance deliverable is a maintained, derived inventory of copies — modelled ones from lineage, operational ones from pipeline and infrastructure configuration — with an owner, an access model and a retention per entry.
- Where a copy cannot be governed, the correct response is to stop creating it, not to document it. Documented ungoverned copies accumulate; removed ones do not (Data Minimization).
Operating it
- Count of datasets tagged
personalversus count of datasets where the scanner found identifier patterns. The gap is the ungoverned copy surface, and it is the single most informative governance number a platform can publish. - Log-line sampling for identifier patterns, run continuously with a low sample rate, alerting on the pipeline that emitted them rather than on the line (The Log Bill and What It Is Buying).
- Age and row count of every table in scratch and quarantine schemas, and depth of every dead-letter queue. All three should be near zero and all three are routinely not (The Backlog Arithmetic: Four Levers and a Drain Time).
- Export events from the BI tool and result-download events from the query interface, attributed to a principal. This is the metric that shows whether governance is being routed around (Audit Logs for Privileged Actions).
- At 10x pipelines, operational copies grow faster than modelled ones, because each pipeline brings its own logs, its own dead letters and its own scratch tables.
- At 100x, enumeration must be automated from lineage and infrastructure configuration; a maintained list is stale on the day it is written (Impact Analysis).
- More consumers scales the export surface directly. Every BI user is an export button, and export volume grows with users rather than with data.
- Adding retrieval and training consumers changes the shape rather than the size: those copies are transformed representations that cannot be searched for an email address at all (Embedding Pipelines).
- Retaining raw payloads is the largest storage driver in this area, and it is retained bytes multiplied by retention horizon — the two levers are what you keep and how long (Storage Lifecycle).
- Scanning is sampled reads across columns and log volume, which scales with column count and log rate rather than with data size.
- Tokenisation adds a lookup at ingest and a vault to operate. The lookup cost is per record and constant; the vault cost is operational and permanent.
- The dominant cost of getting this wrong is not storage. It is the cost of an unbounded incident scope, where an organisation that cannot enumerate copies must treat every copy as compromised.
- Tokenising at ingest removes most of the copy problem and costs you the ability to answer questions that need the real identifier — matching against a partner list, contacting a customer, debugging a source discrepancy. Those needs are real, which is why the vault exists and why it must be reachable by an audited path.
- Logging identifiers instead of records makes production debugging harder in exactly the situations where it is hardest already. The quarantine-table pattern recovers most of that, at the cost of building and operating it.
- Short retention on raw reduces exposure and reduces your maximum backfill range. That trade is the subject of the next two lessons and has no universally correct answer (Data Retention).
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe copy inventory — raw, logs, error messages, dead letters, extracts, temp tables, backups, training sets — is a property of how data platforms are built rather than of any stack, and the same list applies whether the platform is a warehouse, a lakehouse or a pile of scheduled scripts.
- ORG-SPECIFICWhat counts as personal data, whether pseudonymised data remains in scope and what an erasure obligation covers are legal determinations that differ by jurisdiction and by the organisation's own commitments; the engineering response is the same but its required completeness is not.
- SCALE-SPECIFICBelow a handful of pipelines the copy inventory can genuinely be a maintained list reviewed quarterly; above a few dozen it must be derived from lineage and infrastructure configuration, because a hand-maintained list of copies is stale within a sprint.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a dead-letter queue exists at all: a consumer that cannot make progress on a message must set it aside or block the partition, and the queue is the durable consequence of that choice.
- — DevOps / Production Engineering owns log pipelines, retention configuration and the alerting path an error message travels down. The controls that stop a record reaching a chat channel are implemented there, not in the data platform.