Tokenisation and pseudonymisation

The written design from session 1 gives the data scientist region, plan and mrr in clear and customer_id masked. Every other cell for that job is empty. A churn model trained on that is fine: it needs behaviour per customer and a segment per customer, and it does not need to know that customer 48213 is a person called Mara in Lisbon.

What the model does need is for customer 48213 to have the same key in the customers table, the events table and the payments table. A masking policy that returns ******** destroys that, because every row now has the same key. A masking policy that returns the last four characters gives a key that collides across thousands of customers. So the masked cell in the design is not asking for a mask at all; it is asking for a substitute that behaves like the real key in a join and like nothing in a lookup.

• Masking - transforming a column value at query time into something that hides it. Fixed strings, partial values, nulls. Session 4 built these.
• Tokenisation - replacing a value with a substitute, the token, that stands in for it consistently. The same input gives the same token, so tokens join.
• Surrogate key - a token used specifically as an identifier for joining. What the modelling view exposes instead of customer_id.
• Pseudonymisation - processing personal data so it can no longer be attributed to a person without additional information, which is kept separately. Still personal data, because somebody holds the additional information.
• Anonymisation - processing so that nobody, including the original holder, can re-link the data to a person. Much harder than it sounds, and the second half of tonight is about why.
• Direct identifier - a column that names a person on its own: name, email, phone, national_id.
• Quasi-identifier - a column that does not identify anybody alone but does in combination: dob, region, plan, a join date.

Every tokenisation scheme is a deterministic function from the real identifier to a substitute, plus some arrangement for who can reverse it. Three shapes cover almost every warehouse.

For a modelling view the keyed hash wins, because a surrogate key has no format to preserve and no downstream system to satisfy. The vault earns its place when values must be reversed for a business reason, such as a collections team that needs to reach the real customer from a flagged surrogate. The format-preserving token is a payments-system control and almost never belongs inside an analytics warehouse.

The tempting shortcut is sha2(national_id) with no key. It looks irreversible, because a hash cannot be computed backwards. It is reversible in practice within minutes, because nobody needs to compute it backwards: they compute it forwards for every possible input and look up the match.

• A national ID space is small. A few hundred million valid values at most for one country, and often far fewer once the check digit and date structure are respected. Hashing all of them is a lunch-break job on a laptop.
• A phone number space is smaller. One country code, a known set of prefixes, seven to nine remaining digits. Hash the whole space once, keep the table forever, reverse any hashed phone number by lookup.
• An email is not much better. Most addresses follow a handful of patterns over a name and a few dozen domains, and the names are already in the table next door.

The secret in a keyed hash is what makes enumeration useless: the attacker would need to hash every candidate with a key they do not have. Take the key away and the hash is a slow way of writing the original value. That is why md5(customer_id) in a modelling view is a real customer_id for bench purposes, and would still count as a leak.

Session 8 scores the finished policy set against 150 concrete requests, and the ladder it climbs ends with tonight's control.

The 3 leaks before this step are all one cell: the data scientist holding a real customer_id, in each of the three regions they cover, where the design said masked. The 1 standing privilege is the same cell counted the other way. A masking policy on customer_id for that role would also clear the leaks and would break the model. The surrogate key clears them and keeps the join, which is why it is the control that finishes the design instead of a tightening that costs somebody their job.

A surrogate key has to be deterministic within one modelling estate, or the joins fail. It does not have to be the same surrogate everywhere.

• One key per purpose. Hashing with a different secret for the churn model and for the fraud team means a token leaked from one cannot be joined to the other. The cost is that the two teams cannot join to each other either, which is often exactly the separation the design wanted.
• One key per period. Rotating the secret annually and re-tokenising means an old export goes stale. Nothing built on it can be joined to fresh data, which limits the life of anything that walked out of the door.
• Never per row. A random salt per row is a random value, not a token, and it destroys the join. This is the mistake people make when they have heard that salting is good and have not asked what it is for.

Decide the granularity before writing the view, because changing it later means re-issuing every surrogate to every consumer at once.

A quasi-identifier is a column that would never be classified as personal on its own and still narrows the population. Date of birth alone matches thousands of people. Region alone matches a third of the table. Plan alone matches a quarter. Together, in a segment with a few hundred customers, they frequently match exactly one.

• The linkage attack is the ordinary case, not the exotic one. The attacker is a colleague, a contractor or a former employee who knows one true fact about one customer: their approximate age, their region, their plan. The table hands them the rest.
• Bucketing helps and does not finish the job. Turning dob into a ten-year band collapses many segments of one into segments of many. Some remain, and the query in the build-along finds them.
• Small regions are the danger. A segment of one in the EU with 60,000 customers is rare. In a country with 400 customers it is the norm, and a regional filter on the view makes the problem worse rather than better for the person reading that region.

The natural response is a rule: no combination of quasi-identifiers may appear fewer than k times, and suppress or generalise the rows that break it. That rule has a name, k-anonymity, and it is a reasonable review check. It is not a guarantee, for two reasons that matter in a warehouse.

• Groups can be uniform. Five people share an age band, region and plan, so the group is safe under k = 5. All five have mrr 1,240 and a support flag for arrears. The attacker learns the sensitive value without needing to know which of the five is their target.
• Tables get joined. A view that is k-anonymous alone stops being so the moment somebody joins it to a second table with a different set of columns. The surrogate key that makes the modelling view useful is exactly what makes that join possible.

The professional conclusion is not to abandon the check. It is that removing names is not anonymisation, that a modelling view is still a view over personal data, and that it therefore still needs the row policy from session 3, a classification review of every column it exposes, and a named owner. The statistics of how much residual risk is acceptable is a separate discipline with its own literature, and the warehouse job is to make sure the question gets asked.

Some columns cannot be made safe by transformation, because their content is not known in advance. support_notes is the example on this table and every estate has one.

• Free text carries whatever an agent typed. Over a year that includes full names, phone numbers read out and written down, card numbers, medical circumstances offered as a reason for late payment, and once in a while a password. No masking policy can find all of it and no hash can pseudonymise it.
• The design already said no. The data scientist's row in the access design has an empty cell for support_notes. The view simply does not select the column, which is the cheapest control there is: a column that is not in the view cannot leak through the view.
• If the model needs the text, the text needs its own pipeline. Redaction, review, and a separate view with a separate grant. Not a column quietly added to the modelling view because the notebook would be more interesting with it.

Two words get used interchangeably in review meetings and describe different things. As a professional distinction, stated without any claim about what a particular law requires:

The modelling view is pseudonymised. The hashing key exists, somebody holds it, and with it every surrogate walks back to a customer_id. That is a deliberate design choice, because a model that produces a churn score has to be able to reach the customer it scored. It also means the view stays inside every control that applies to the base table. What a given jurisdiction requires of pseudonymised data, and when a dataset legally counts as anonymised, are questions for the governance and data protection courses; the warehouse job is to be honest about which of the two words applies.