Data masking

What is data masking?

Data masking replaces sensitive values with realistic but fake ones, so people can work with the shape of the data without seeing the real content. An email becomes jane@example.com, an account number becomes BEXX XXXX XXXX 1234, a real name becomes an invented one. The columns still behave like the originals, but the person behind them is gone.

A lot of work needs the form of the data, not the truth of it. A developer testing a signup screen needs a field that holds a valid email, not a real customer's. Masking gives them something usable while cutting the risk of a leak.

Static masking versus dynamic masking

One decision shapes everything else: whether you rewrite the data permanently or only hide it while someone looks.

Static data masking rewrites the values for good. You copy a production database, run the masking rules over it, and the copy holds fictitious data with no route back. Oracle's Data Masking and Subsetting calls this irreversibly replacing the sensitive data, so the originals cannot be retrieved from the masked copy. That is what makes it safe to hand to developers, testers, or an outside partner: no real data is left to leak.

Dynamic data masking does the opposite. The real values stay in the database and the mask is applied at query time, on the way out. Microsoft's Dynamic Data Masking for SQL Server and Azure SQL is explicit that the data in the database is not changed; it only hides values in the result set from users who should not see them. Snowflake works the same way, through masking policies that mask a column at query runtime while the stored value stays intact.

Dynamic masking is not a security boundary

Because the real data is still sitting underneath, dynamic masking only controls what appears in the result set. Microsoft documents this plainly: Dynamic Data Masking should not be used on its own to fully secure data from users who can run their own queries, because a determined user with query permission can infer the values behind the mask.

Take a salary column that shows as 0 to unprivileged users. The mask hides the number, but it does not stop someone narrowing it down:

SELECT ID, Name, Salary FROM Employees
WHERE Salary > 99999 AND Salary < 100001;

The salary still displays as 0, yet every employee returned earns exactly 100000, and shifting the bounds exposes the rest. Masking policies also do not apply to high-privilege accounts such as db_owner, and a mask does not stop anyone with write access from updating the column. Microsoft positions dynamic masking as complementary to real controls such as row level security, encryption, and auditing: use it to prevent accidental exposure, not to defend against someone who is trying.

Common masking techniques

Most tools mix a few techniques, picked per column:

• Substitution. Swap the real value for one drawn from a lookup list, so a name becomes another plausible name and a city becomes another real city.

• Shuffling. Keep the same set of values but reorder them across rows, so the column's distribution survives while no row keeps its own value.

• Nulling. Blank the value out or replace it with a fixed placeholder. Simple, but it flattens the column, so anything that relied on the spread of values breaks.

• Partial masking. Reveal a few characters and hide the rest, the way a card shows only its last four digits.

• Tokenisation. Replace the value with a meaningless token and keep the mapping in a separate, guarded store. Snowflake calls this external tokenisation: data is tokenised before loading and only detokenised at query time for allowed roles.

Masked test data has to stay usable

Masking is only worth doing if the result still works as data, and two properties decide that. The first is format: a masked email still has to parse as an email and a masked date still has to be a real date, or the application under test rejects it on the way in.

The second, and the one people miss, is referential integrity across tables. If the same customer appears in customers, orders, and invoices, the masked customer ID has to come out identical in all three, or a join that used to link them returns nothing. Static masking handles this with deterministic substitution, where a given input always maps to the same output, and Oracle generates masked keys so primary key and foreign key constraints are not violated. Free text needs its own pass, because PII hides in tickets, notes, and logs, not only in tidy columns; tools like Microsoft Purview help find it first.

Masking, anonymisation, and pseudonymisation

These get mixed up, and the difference is legal as much as technical. Anonymisation and pseudonymisation describe how reversible the result is: pseudonymised data can still be traced back to a person by someone holding the mapping, so under GDPR it stays personal data, while truly anonymised data cannot be linked back at all. Masking is the mechanism, not the guarantee. A static masked copy with no mapping kept can reach anonymisation, while a dynamic mask, or a reversible token whose key still exists, cannot. Differential privacy tackles a related problem for published statistics by adding measured noise.

Synthetic data generates records from scratch that mimic the statistics of the real set without copying any real row, which sidesteps re-identification. A data clean room lets two parties analyse combined data without either side seeing the other's raw records.