GovernanceGENERALWAREHOUSE-SPECIFICORG-SPECIFIC

Data Masking, Tokenisation & Encryption

Four different techniques that people call masking. Which joins survive, who can reverse it, and why hashing a low-cardinality field is reversible by anyone with a loop.

What actually happens How to build it Can I trust it?

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.

The question

This column must not be readable, but analysis still needs it — which transformation do you apply, and who can undo it?

Who needs this

An analyst who needs to count distinct users but not contact them, a support tool that must show the last four digits of a card, a fraud model that needs to join a device to an account, and an auditor who wants to know whether the protection is reversible and by whom. Each of those needs a different technique, and giving all of them the same one is the usual mistake.

What one row is

The unit is one column value, transformed on a read path or on a write path. That distinction is the whole design: a value masked on read is still stored in clear and is protected only on the paths that apply the mask, while a value transformed on write is protected everywhere and cannot be recovered by anyone without the mapping or the key.

The obvious build

Hash the sensitive columns. sha256(email) is one function, it is deterministic so joins still work, it is irreversible in the cryptographic sense, and it needs no vault, no key management and no policy engine. This is by a wide margin the most common approach in data platforms, and for high-entropy secrets it is a reasonable one.

Why it breaks

Hashing is only one-way over an unpredictable input. A phone number, a national identifier, a date of birth, a postcode, a card number and an email address from a known domain are all drawn from small, enumerable domains. Anyone holding the hashed column can hash every candidate and build the reverse mapping — no cryptographic weakness required, just a loop (Hashing vs Encryption vs Encoding).

How it breaks with real data

Hashing is only one-way over an unpredictable input. A phone number, a national identifier, a date of birth, a postcode, a card number and an email address from a known domain are all drawn from small, enumerable domains. Anyone holding the hashed column can hash every candidate and build the reverse mapping — no cryptographic weakness required, just a loop (Hashing vs Encryption vs Encoding).
Unsalted deterministic hashes are also joinable across organisations, which is a feature for identity matching and a serious linkage risk otherwise: two datasets from different companies hashing the same identifier the same way link perfectly (Password Storage covers why salting matters here).
Salting fixes enumeration and destroys the join, which was the reason for hashing in the first place. The salt then becomes a secret with a lifecycle, and rotating it re-keys every historical row (Surrogate Keys).
Masking applied in a view protects the view. The base table still holds the value, the underlying files still hold the value, and the backup still holds the value (Row and Column Security).
A deterministic mask preserves equality and grouping, so a masked column can still be grouped by, counted and joined — and in a small group that is enough to identify someone (Data Classification).
Partial masking leaks more than it looks. The last four digits of a card, plus a date and an amount, is frequently unique; the first characters of an email are usually the person's name.
Encryption is applied and the key sits next to the data — in the same account, readable by the same service principal — so the encryption protects against exactly one threat: someone stealing the disk (Key Management and Encryption at Rest).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Masking replaces a value with a fixed or partial form (****, j***@example.com, the last four digits). It is applied on the read path by a policy or a view. The original is unchanged and still present; only the path is protected. It destroys joins unless the mask is deterministic, and if it is deterministic it is a weak tokenisation.
Tokenisation replaces a value with an unrelated surrogate and keeps the mapping in a separately governed vault. It is applied on the write path, usually at ingest. Deterministic tokenisation preserves joins by construction; the token carries no information about the value; reversal requires the vault, which is auditable. This is the technique most data platforms should be using and mostly are not (PII in Pipelines).
Hashing is a one-way function. It preserves joins when unsalted, and its security is a property of the *input domain*, not of the algorithm: hashing a value drawn from a small enumerable set is reversible by anyone, immediately. Salting restores one-wayness and eliminates the join.
Encryption is reversible with a key. It preserves nothing about the value — no joins, no ordering, no grouping — unless a specialised scheme is used, and those schemes trade properties for the exact leakage they preserve. Its real content is key management: who holds the key, where it lives, how it rotates, and what happens when it is destroyed (Symmetric Encryption).
Encryption at rest and in transit are table stakes and are largely solved by infrastructure — storage-level encryption and TLS between components. They protect against stolen media and network interception, and against nothing a legitimate query can do, which is where data-platform exposure actually happens (Encryption at Rest vs in Transit).
The distinction that carries all the weight is who can reverse it, and is that reversal audited. Masking: nobody reverses it, but the original is still there for anyone on another path. Tokenisation: the vault holder, with a log. Hashing: anyone, if the domain is small. Encryption: the key holder, and key access is loggable.

Four techniques people call masking

The word "masking" is used for four transformations with different guarantees, different reversal models and different effects on analysis. Choosing between them by strength is the wrong axis; choose by what analysis must still be able to do and by who is allowed to reverse it.

Read the table column by column rather than row by row. The joins survive column tells you what analysis you keep. The who can reverse column is the actual security property, and it is the one that distinguishes a control from a gesture.

The row to sit with is hashing. It is the most-used technique in data platforms, and its guarantee is a property of the data rather than of the algorithm — which means the same sha256 call is strong protection for one column and no protection at all for the next one.

Technique	Applied where	Joins survive?	Who can reverse it	The failure to know about
Masking (static or partial)	Read path — a policy or a view. The stored value is unchanged.	No, unless the mask is deterministic — in which case it is weak tokenisation.	Anyone reaching the data by a path that does not apply the mask.	The base table, the underlying files and the backup all still hold the clear value.
Tokenisation	Write path, usually at ingest. The platform never stores the original.	Yes, when deterministic. That is the reason to choose it.	The vault holder, under audit.	The vault becomes the highest-value target on the platform, and someone will cache it for performance.
Hashing, unsalted	Write or read path. Cheap and stateless.	Yes — and across organisations too, which is a linkage risk as well as a feature.	Anyone, if the input domain is enumerable. Phone numbers, birth dates, postcodes, card numbers, emails from a known list.	It is assumed irreversible because the function is. The function is; the input space is not.
Hashing, salted	Write path, with a salt that is a managed secret.	No — different salts break equality, which was the point of hashing.	Nobody, while the salt is secret and the input has entropy.	Rotating the salt re-keys all history; until the rewrite finishes, one subject appears as two.
Encryption	Write path, with a key in a managed store.	No ordering, no ranges, no pruning. Equality only if deterministic, which leaks the distribution.	The key holder — and key use is loggable, which is the property to design around.	The key stored where the data's own service principal can read it, protecting against stolen media and nothing else.

Why hashing a phone number protects nothing

GENERALThis is an argument about input entropy rather than about any implementation, so it holds identically for SHA-256, SHA-3, BLAKE and every future replacement; changing the hash function changes the cost of the loop by nothing that matters.

This is worth making concrete, because it is the error that survives code review. A cryptographic hash is one-way in the sense that you cannot invert the function. You do not need to invert it. You need only to enumerate the possible inputs, hash each one, and compare — and for the fields data platforms most want to protect, the set of possible inputs is small and completely known.

A national phone number has a bounded numeric space. A date of birth over a plausible range is a five-figure number of possibilities. A postcode is a published list. A payment card number is constrained by issuer prefix and a check digit. An email address is unbounded in principle and, in practice, drawn from a customer list somebody already has. In every one of these cases, building the reverse mapping is a loop, not an attack.

The corollary is the useful part: hashing is a legitimate technique when the input genuinely has entropy — a random identifier, a session token, a generated key — and never when the input is drawn from a domain someone can list. When you need determinism over a low-entropy field, the right tools are a keyed function or tokenisation, both of which put a secret between the attacker and the mapping (Cryptography Fundamentals).

The same function, two completely different guarantees

1-- Column A: a randomly generated session identifier. High entropy, no
2-- enumerable domain. Hashing this is a reasonable protection.
3select sha256(session_token) as session_hash from raw.sessions;
4 
5-- Column B: a national phone number. The domain is a bounded numeric
6-- range, fully known, and generating every candidate is a loop:
7--
8--     for candidate in all_valid_numbers:          -- a bounded, known set
9--         table[sha256(candidate)] = candidate
10--
11-- The result is a complete reverse mapping for the entire column.
12select sha256(phone_number) as phone_hash from raw.customers;   -- protects nothing
13 
14-- The same argument applies to any enumerable domain:
15--   date of birth, postcode, card number (constrained by prefix and
16--   check digit), and any email address on a list somebody holds.
17 
18-- If determinism is required over a low-entropy field, put a secret
19-- between the attacker and the mapping. A keyed function, where the key
20-- lives in a managed secret store and not in the query:
21select hmac_sha256(phone_number, <key from secret store>) as phone_token
22from raw.customers;
23 
24-- Or tokenise at ingest, so the platform never holds the value at all
25-- and the mapping lives in a separately governed, audited vault.

The distinction is not the algorithm and not the column's sensitivity. It is the size of the set of possible inputs — which is a property of the world, is invisible in the schema, and is not something any test in the platform will tell you.

Encryption, and the part that actually matters

Encryption at rest and in transit is the part of this area that is largely solved and largely misunderstood. Storage-level encryption protects against someone obtaining the physical media or the raw storage object; transport encryption protects against interception between components. Both are table stakes, both are usually a configuration flag, and neither addresses a single exposure path in this module — because every legitimate query decrypts transparently (Encryption at Rest vs in Transit).

What separates encryption that means something from encryption that is a checkbox is key management. Who holds the key, whether it is separable from the data's own service principal, whether its use is logged, how it rotates, and what happens when it is destroyed. A key that the pipeline identity can read is a key that anyone who can modify a pipeline can use (Key Management and Encryption at Rest, Privilege Separation).

Key granularity is the design decision with the most consequences downstream. One key for the platform is simple and gives you nothing beyond media protection. A key per dataset gives separation. A key per subject is expensive and buys the property that makes erasure tractable: destroy the key and every copy of that subject's encrypted data — in raw, in backups, in snapshots, in files nobody can rewrite — becomes unreadable at once (Deletion Requests).

A value protected from ingest to query, and what each stage promises

1
In transit from the source
TLS between the source system and the ingestion process.
guarantees Confidentiality and integrity against a network observer, given verified certificates. Nothing against either endpoint.
fails by Certificate verification disabled to make a connection work during an incident, and never re-enabled (TLS as a Security Boundary).
2
Transform at the boundary
Tokenises or encrypts the identifying column before anything is written.
guarantees The platform stores no recoverable original from this point forward — for data arriving after this was turned on.
fails by Being added later, leaving every historical record in clear and every backup of it too.
3
At rest in object storage
Storage-level encryption with a managed key.
guarantees Confidentiality against someone obtaining the storage object or the media. Nothing against an authorised read.
fails by Being cited as the answer to "is the PII protected", which it is not.
4
Key access
A key management service issues decrypt operations to permitted principals and logs them.
guarantees That decryption is attributable and revocable — which is the actual security property, and the only one that survives a legitimate query.
fails by A key readable by the same broad service principal as the data, collapsing two controls into one.
5
Read path
A policy returns the masked or tokenised form to principals without the entitlement.
guarantees Protection on paths that evaluate the policy. Not on direct file reads or a second engine.
fails by A federated engine pointed at the same files, which honours none of it (Federated Query).
6
Reversal
An audited vault lookup or decrypt operation for a stated purpose.
guarantees That every recovery of a real value is attributable to a principal and a purpose.
fails by Being undesigned, so it happens through a shared credential and is invisible.

Only two stages here protect against anything a legitimate query can do: the write-path transformation and key access control. The rest protect against media theft and network interception, which are not how data platforms leak.

Product detail — verify current documentation

Key management service capabilities — customer-managed keys, key granularity, automatic rotation, and which storage and warehouse services can use them — differ by provider and change over time. Verify current documentation before designing key-per-subject schemes around a specific service.

How to build it

Most important first.

Choose by what analysis needs, not by what sounds strongest. Needs to link records but never the value: tokenise. Needs the value rarely and under audit: encrypt with a managed key. Needs neither: drop it at the boundary (Data Minimization).
Transform on the write path wherever possible. A read-path mask protects the paths you configured; a write-path transformation protects the bytes, which means it also protects the backup, the file and the copy (The Raw Landing Zone).
Never hash a low-cardinality or enumerable field and call it protected. If the domain can be enumerated, the hash is a lookup table waiting to be built, and the honest options are tokenisation or encryption.
Where a deterministic transformation is required for joins, use a keyed function — a keyed hash or deterministic tokenisation — so that reversal requires a secret rather than a wordlist (Cryptography Fundamentals).
Manage keys and salts as first-class secrets with rotation, separation of duties and audit, and keep them in a system the data platform's service account cannot read wholesale (Secrets Management, The Secret Lifecycle).
Bind masking policies to classification tags rather than to column names so new columns inherit protection instead of arriving in clear (Data Classification).
Apply a group-size threshold before publishing anything aggregated from a masked or tokenised column. Distinct counts over small groups re-identify regardless of the transformation applied.

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.

Masking guarantees the masked expression is returned on the paths where the policy is evaluated. It guarantees nothing about the stored value, which is unchanged.
Tokenisation guarantees the platform holds no recoverable original without the vault. It does not make the data non-personal: a stable token still distinguishes and links individuals, so obligations follow it (Data Minimization).
Hashing guarantees one-wayness only when the input has enough entropy to resist enumeration. For identifiers drawn from known domains it guarantees essentially nothing, which is the single most important sentence in this lesson.
Encryption guarantees confidentiality against anyone without the key, and transfers the entire problem to key management. A key readable by the same principal that reads the data guarantees very little (Key Management and Encryption at Rest).
None of these guarantee non-inference. Group sizes, distributions, ordering and correlation with unmasked columns leak, and a determined analysis over a masked column recovers more than the mask suggests.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The exposure test: for each column tagged personal, assert that reading it through each supported path returns the protected form. Run it as a policy test across the warehouse, the lake path and any federated engine, using a principal that should not see the clear value.
It misses inference entirely — that the masked column is still groupable, that a partial mask plus two other columns is unique, that a hash of an enumerable domain is a lookup away — because those are not read failures and no assertion about a returned value catches them.
A second, blunter check is worth running: a scanner over the protected column's values looking for the *unprotected* format. A phone-shaped value in a column that should hold tokens means a write path bypassed the transformation (Data Tests).

Freshness

Read-path masking has no effect on pipeline freshness and adds a small per-row expression cost on masked columns only.
Write-path tokenisation adds a vault lookup at ingest, which puts an external dependency on the ingestion critical path — its availability becomes your ingestion availability (Ingestion Failure & Recovery).
Rotating a salt or a tokenisation key is a re-keying of history: every affected row must be rewritten before joins across the boundary work again, and until they do, the same subject appears as two subjects (Backfills).

When the schema or meaning changes

A new column arrives and inherits no mask unless policy binds to tags. This is the same failure as everywhere else in the module and has the same fix (Schema Evolution).
Changing the transformation — new salt, new key, hash to token — splits history at the change date. Records before and after no longer join, and the metric that counts distinct subjects across the boundary doubles (Semantic Changes).
A field whose cardinality falls can become enumerable without any change to it. A hashed identifier is protected in proportion to the size of its domain, and that domain is a property of the world rather than of your schema.

How to re-run this safely

A column masked on read but stored in clear is recoverable by simply reading it, which is the point: recovery is easy and so is exposure. Whether that is acceptable depends on how many paths exist.
A tokenised value is recovered through the vault, under audit. Design that path deliberately — who may use it, for what purpose, with what logging — because it will be used and an undesigned reversal path becomes an undocumented one (Audit Logs for Privileged Actions).
A hashed value is not recoverable from your platform and may be recoverable by anyone else with a candidate list. Plan on the basis of the domain size, not of the algorithm.
An encrypted value with a destroyed key is unrecoverable, which is a failure mode and a feature — it is the mechanism behind crypto-shredding (Deletion Requests).

What can go wrong

Failure modes

A low-cardinality field is hashed, and the reverse mapping is trivially reconstructed by anyone holding the column.
Masking is applied in a view and the base table, the lake files and the backups hold the clear value (Row and Column Security).
Salting is introduced to fix enumeration and silently breaks every join that depended on the deterministic hash, splitting one subject into two across the change date.
The tokenisation vault is cached in the warehouse for performance, recreating exactly the mapping the design existed to avoid (Caching Patterns).
Encryption is enabled and the key is readable by the same service principal that reads the data, so the control covers stolen media and nothing else (Key Management and Encryption at Rest).
A partial mask leaves enough to identify — last four digits plus timestamp plus amount — and the protection is assumed rather than tested.
A masked column is used as a GROUP BY key in a published aggregate, and a group of size one is a disclosure.
The transformation is applied in the modelled layer, and the raw layer holds every clear value with a longer retention (Data Retention).

Misreads

"Hashing is irreversible." Hashing is irreversible for high-entropy inputs. For a phone number, a birth date, a postcode or an email from a known domain, the input space is enumerable and the mapping is rebuilt with a loop. This is the single most common security error in data platforms (Hashing vs Encryption vs Encoding).
"Hashed data is not personal data." It still distinguishes individuals and still links records, which is what the obligations attach to. It is a risk reduction, not a reclassification (Data Classification).
"Masking and encryption are the same thing with different strength." They differ in *where* they apply and *who can reverse*: masking leaves the original in place and protects one path; encryption transforms the stored bytes and moves the whole problem to key management.
"Encryption at rest protects our data." It protects against someone taking the storage. Every legitimate query decrypts transparently, which covers none of the exposure paths in this module (Encryption at Rest vs in Transit).
"We mask in production, so lower environments are fine." Lower environments are usually loaded from production copies with the masking applied last, or not at all, and they have weaker access control by design.
"Tokenised means anonymous." It means the platform cannot resolve it. The vault can, and the token still links every record about that person (Data Minimization).

Privacy, retention and access

Masking is the mechanism that consumes classification, so its coverage is bounded by classification coverage: an unclassified personal column is an unmasked one, always (Data Classification).
The reversal path is the governed artefact, not the transformation. Who may reverse, for what purpose, with what logging, is the question an auditor asks and the question most implementations have not answered.
Key and salt management is where this becomes real. A key held by the same principal as the data, or a salt in a repository, converts a strong control into a compliance artefact (Secrets Management).
Crypto-shredding — encrypting per subject so that destroying a key erases every copy at once — is the one technique that makes erasure tractable across immutable storage, and it is designed here or not at all (Deletion Requests).

Operating it

How you see it in production

Count of columns tagged personal that are not covered by a masking or tokenisation policy — the direct coverage measure, and the number that should be published (The Data Catalog).
Vault reversal rate, per principal and per stated purpose. A rising line means the tokenisation boundary is being routed around and the platform is drifting back to holding identity (Audit Logs for Privileged Actions).
Format scanning over protected columns, alerting on values that match the unprotected pattern — the detector for a write path that skipped the transformation.
Key and salt age against the rotation policy, and the count of rows still encrypted under a retired key (The Secret Lifecycle).

What changes at 10x and 100x

At 10x volume, read-path masking scales with rows projected and tokenisation scales with distinct values, which is usually much slower-growing — an advantage that widens with scale.
At 100x distinct subjects, the vault becomes a substantial system in its own right, with its own partitioning, caching and availability story (Partitioning and Sharding).
High-cardinality columns are the ones where tokenisation is cheapest per row and hashing is safest; low-cardinality columns are the reverse on both counts, which is exactly the intuition most people have backwards.
More engines multiplies the number of places a read-path mask must be implemented, which is a strong argument for transforming on write instead (Query Engines).

What drives cost here

Read-path masking costs a per-row expression on masked columns that a query actually projects, which is another reason a wide SELECT * is expensive in analytics (Projection Pushdown).
Tokenisation costs a lookup per distinct value at ingest — usually cached and therefore cheap in steady state — plus the permanent operational cost of a vault with high availability and strict audit.
Encryption costs compute per value on write and read and, more significantly, forecloses predicate pushdown and pruning on that column: an encrypted column cannot be range-scanned or partition-pruned, so queries filtering on it read everything (Predicate Pushdown).
Re-keying is a full rewrite of the affected columns across all history, priced like a backfill, and it is the cost that makes teams avoid rotation (Planning a Backfill).

What this approach costs

Deterministic transformations preserve joins and preserve linkability. You cannot have analysis that links records without also having a mechanism that links records, and every design that claims both is hiding the trade somewhere.
Tokenisation is the best general answer and introduces a vault: a new critical dependency, a concentrated target, and an operational burden that never ends.
Encryption is the strongest confidentiality and the worst analytical citizen: no pruning, no ranges, no grouping without decryption, so an encrypted column is effectively opaque to the query planner (Query Optimizers).
Read-path masking is cheap, portable and reversible by anyone who finds another path. It is the right choice for reducing casual exposure and the wrong choice for protecting against a determined one.

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 four-technique distinction and the enumeration argument are mathematical rather than technological: hashing a value drawn from a small known domain is reversible on any platform, in any language, with any algorithm.
WAREHOUSE-SPECIFICNative masking policies, whether they bind to tags, and which hash and encryption functions are available differ per warehouse; a policy written for one engine typically becomes a view or a write-path transformation on another, which changes the guarantee and not only the syntax.
ORG-SPECIFICWhether pseudonymised data stays in regulatory scope, and whether a given partial mask counts as protection, are legal determinations rather than engineering ones, and they differ enough between regimes to change which technique is acceptable for the same column.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Backendbackend-secrets

Securityhashing-encryption-encoding password-storage symmetric-encryption asymmetric-cryptography encryption-rest-transit cryptography-fundamentals secrets-management secret-lifecycle privilege-separation

Cloudkms-and-encryption cloud-secrets-management

DSAhash-table

Domains that do not exist yet

— Security Engineering owns the cryptographic depth — what a hash function guarantees, why salting works, how key hierarchies are constructed. This lesson borrows those results and applies them to columns in a warehouse; the reasoning behind them lives there.
— DevOps / Production Engineering owns key rotation as an operational process, including the re-keying backfill that rotation implies for already-written data.