Which Column Gets Masked? How the Masking Decision Is Made

Nobody argues about whether production data should be masked. The argument starts one step earlier, and this is where most organisations get stuck: who decides which column in a query result gets masked, and on what basis?

The Real Question Is Knowing, Not Masking

Masking is technically easy: you replace the value with asterisks. The hard part is deciding which value to replace. Both common approaches fall short on their own.

The first is a manual list: the names of sensitive columns are written down somewhere and that list is applied. It fails when a query is written with SELECT *, or when a column comes back under a different name through an alias. It also fails when a new table appears and nobody remembers to update the list.

The second is masking everything. It is safe but makes the result useless, so teams start looking for ways to switch masking off entirely. A control that is too strict turns into a control that is not applied.

Two Axes: The Column Name and What Is Inside It

The model that works combines two independent signals. The first is a classification catalog: does the column name match a known sensitive field (national id, IBAN, phone, email, first name, surname). The second is a data pattern: do the values inside the column match a sensitive shape.

Each is wrong on its own, and each is wrong in a different direction. The name lies: a column may be called col1 or data3, or conversely a column named customer_name may hold a product name. The content lies too: the column may be empty, or the rows sampled that day may happen to be synthetic.

Using both signals means that where one is wrong the other catches it. When the name is unfamiliar the content decides; when the content is inconclusive the name does.

A Pattern Is Not Enough, a Validator Is

Building the data pattern from a regular expression alone produces a great many false positives. Not every eleven digit number is a national id; it could be an order number or a barcode. Not every sixteen digit number is a card number.

The answer is to put a validator on top of the pattern: computing the check digit the value carries within itself. That computation is mathematical and leaves no room for guessing.

Some fields have no validator: names, addresses, diagnosis codes. For these the pattern and the column name work together and there is no mathematical certainty. Knowing that boundary matters: what the system knows for certain must be separated from what it infers.

Sampling and Threshold: How Many Rows to Look At

Content checking does not scan every row; if it did, delivery time on large result sets would be unacceptable. Instead a sample is taken and a threshold is applied.

In practice there are three settings. Sample size says how many rows to look at; a thousand is a sensible starting point. Threshold percentage says how much of the sample must match the pattern for the column to count as sensitive; twenty per cent is a balanced start. Sampling mode decides whether the first N rows or random rows are taken.

Lowering the threshold masks more columns and raises false positives; raising it increases the risk of missing a genuinely sensitive column. The right value depends on your data, but one rule helps: when tuning the threshold, choose the direction of your errors. On production data, masking too much costs less than masking too little.

Masking Is a Property of the Data, Not the Flow

This distinction is the most important design decision. If the masking rule is attached to the approval flow, then when one request targets a production server and a test server together, a single decision is made and it is wrong for both environments.

The right place is the server definition. Each server carries its own masking regime and the decision is made per server. Two regimes are enough in practice:

• Strict: the classification catalog and the data patterns work together. This is the default for production.
• Content only: column names are ignored and only data patterns are considered. In environments holding synthetic data this ends the noise.

The content only regime has a useful property: it verifies itself. If real data is ever loaded into that test server, the patterns with validators fire and masking returns on its own. Nobody has to remember a setting. The accepted loss is explicit and should be written down: fields with no pattern, such as names, addresses and diagnoses, go out in the clear in that environment.

Every regime other than strict should require a written justification. The answer to why it was relaxed on this server has to enter the record at the moment it is relaxed; it cannot be reconstructed from memory during an audit.

Asking for It Unmasked: Reason and a Second Approval

Masked data is not always enough. Investigating a customer complaint may require seeing the real IBAN. A system that denies this need pushes the team into bypassing masking altogether.

The right design does not forbid the exception; it makes it expensive and visible. Whoever asks for an unmasked column writes a justification, the request passes a separate approval, and which column went out unmasked under whose approval enters the record. The exception becomes a countable event rather than a workaround. How often unmasked data leaves the organisation turns into a number you can measure.

One more detail: the regime at the moment of delivery should be frozen onto the record. Even if the server is later switched to strict, how the data was delivered that day must not change. Otherwise the record of a past delivery gets reinterpreted through today's setting and stops being evidence.

Frequently Asked Questions

Should we use full masking or partial masking?

It depends on the purpose. Partial masking leaves the last few characters and allows record matching: a support team can find the record from the customer saying the last four digits of their card. Full masking is safer but makes results indistinguishable. Full masking for identity and diagnosis fields, partial for card and phone numbers, is a common balance.

Does masking miss a column returned under an alias?

In a system that looks only at column names it does, and this is the most common gap. Content checking closes it: whatever name the value comes back under, its pattern and its check digit are the same. This is the most concrete benefit of using both axes.

Does masking slow the query result down?

Because content checking runs on a sample, its cost is proportional to the sample size rather than to the whole result set. Defining a time budget also helps: if the check exceeds the budget, the safe side is taken and the column is treated as sensitive. Performance concern should not become the reason for switching masking off.

Can we know which columns will come back before running the query?

It can. The result columns of a query can be read from the server without running the query and without reading a single row of data. This lets the person opening the request see which columns will be masked at the moment they save it. The surprise disappears before saving rather than after delivery.