Masking PII in test data is a two-step job: find the sensitive values, then replace them with realistic, non-sensitive equivalents that keep the formats and relationships your tests depend on. The nuance most teams miss is that structured columns and free-text fields need different detection methods — a column-level scan catches a ssn field, but names and account numbers buried in a notes column slip past it. The hard part is doing the replacement consistently: the same input must map to the same output so referential integrity survives across tables. Done right, PII masking gives you a lower environment that behaves like production without its risk.

Start by finding the PII: sensitivity discovery

You can't mask what you haven't found, so the first move is an inventory of where sensitive data actually lives. In a structured database, that means a sensitivity scan across every column: the scan reads column names, data types, and sample values, then flags the columns that hold personal information. This is the discovery step that everything downstream depends on — a column you never identify is a column that ships to your test environment untouched.

Tonic Structural grounds this step in an automated sensitivity scan, and it's a useful model for what thorough discovery looks like in practice. The scan catches three distinct things:

  • Built-in sensitivity types — names, Social Security numbers, addresses, email addresses, phone numbers, and financial and medical identifiers, detected out of the box across common formats.
  • Custom rules for org-specific data — the internal account formats, member IDs, or proprietary codes that a generic detector won't recognize, matched on column name and data type.
  • Confidence levels and generator recommendations — each detection is marked high, medium, or low confidence so you can review the ambiguous cases, and Structural recommends an appropriate generator for the sensitivity type it found, so discovery flows directly into a masking plan.

Detection quality is where discovery lives or dies, because anything the scan misses becomes a leak you don't know you have. Pattern-based detection combined with LLM-enhanced detection catches roughly 50% more sensitive data than pattern matching alone — the difference between a regex that only knows the shapes you anticipated and a detector that also reads context. Discovery is the opening stage of the broader test data management workflow, and getting it complete is what makes the rest of the pipeline trustworthy. Free text is a separate detection problem that a column-level scan isn't built for, and it needs its own approach.

Masking structured fields without breaking your database

Once you know which columns hold PII, the work shifts to replacing those values without breaking the database around them. For typed columns, several transformation techniques apply: masking replaces a value with a realistic substitute of the same type, tokenization swaps it for a reversible token, and format-preserving encryption keeps the exact shape of a formatted identifier — a 16-digit card number stays a valid-looking 16-digit number — so downstream validation and parsing still work. Which technique fits depends on the field and on whether anything needs to reverse the transformation later.

The property that ties all of this together is consistency, and it's what protects referential integrity. The same input must always map to the same output. When you mask a primary key, every foreign key that references it has to receive the identical transformation, or the relationships that join your tables come apart. Get this wrong and the failure is quiet but expensive: masking a customer_id in one table but not consistently in the tables that reference it produces orphaned rows — orders pointing at customers who no longer exist. Your application then throws errors that look like real defects but are artifacts of broken data, and your team burns hours chasing bugs that don't exist in production.

Tonic Structural handles the replacement step with a library of generators — dozens of algorithms spanning realistic-value masking, format-preserving encryption for keys and formatted identifiers, and consistent, deterministic masking that maps a given input to the same output every time. Because the transformations are schema-aware, they hold relationships together across related tables rather than treating each column in isolation.

The Tonic Advantage: schema-aware masking that respects your keys. Naive column-by-column masking is where referential integrity quietly dies. The Structural Agent groups scan results by sensitivity type and applies transformations with the schema in view: mask a primary key, and the same transformation propagates to every foreign key that references it. The result is a de-identified database whose joins still resolve — no orphaned rows, no broken foreign keys, and no bugs that reflect broken data rather than real defects.

Masking PII in free-text and semi-structured fields

The part teams routinely miss is the free text. A notes, comments, or description column — or a JSON blob storing an API payload — holds names, dates, and account numbers embedded in prose, and a column-level sensitivity scan can only tell you the column might be risky, not which spans inside it are sensitive. Column generators can't help either: there's no single value to swap, just PII scattered through sentences. This is a fundamentally different detection problem, and structured masking isn't built for it.

Solving it takes named entity recognition — the task of locating and classifying spans of text such as names, dates, locations, and identifiers inside unstructured content. Tonic Textual is built for exactly this half of the database. It uses proprietary NER models to find sensitive entities inside free text, then either redacts them or replaces them with realistic synthetic substitutes, while preserving the surrounding context and the format of the text. For semi-structured formats like JSON, XML, and HTML, it transforms only the sensitive text values and leaves the structure intact, so the payload still parses. It also supports custom entity types for organization-specific data and detection across multiple languages.

Tonic Structural and Textual address two halves of the same database rather than competing for the same job: Structural masks the relational columns, and Textual handles the unstructured and free-text fields those columns sit alongside. A support-ticket table is a good example — Structural masks the customer_id and email columns, and Textual de-identifies the names and phone numbers written into the ticket body, so the whole record ends up safe.

The Tonic Advantage: synthesize, don't just redact. Blacking out every sensitive span leaves free text riddled with placeholders, and a model or a test that reads it learns from noise. Textual's synthesis path swaps each detected entity for a realistic fake of the same type — a name becomes a different plausible name, a date becomes a different plausible date — so the sentence still reads naturally and its statistical properties survive. The free-text column stays useful for testing instead of turning into redaction static.

How to mask PII in a database, end to end

Pulling the pieces together, masking a database for a lower environment is a repeatable procedure you can run the same way every time and automate once it's stable:

  1. Scan to inventory PII across both structured columns and free-text fields, so you know every place sensitive data lives before you transform anything.
  2. Assign generators to structured columns — format-preserving encryption for keys and formatted identifiers, consistent masking elsewhere — matched to the sensitivity type each column holds.
  3. Route free-text columns through NER detection, then redact or synthesize the entities found inside the prose so nothing sensitive survives in the text.
  4. Verify referential integrity holds across tables, confirming that keys transformed consistently and no relationships were orphaned in the process.
  5. Validate with a privacy report before release, checking that every sensitive column carries a real generator and nothing slipped through as passthrough.
  6. Provision to lower environments and automate the job in CI, so every refresh re-runs the same safe transformation instead of depending on someone remembering to.

This is the same shape whether you're doing it by hand or with tooling, and it's what a guide to how to mask a database walks through in more depth; the specific masking techniques you choose at step 2 depend on your fields and constraints. Automating the full sequence is where the payoff shows up. Patterson cut test data provisioning time by 75% after automating this workflow, provisioning data to seven development teams across an organization of more than 7,000 employees while keeping PHI out of developer workflows entirely — the discovery, masking, and provisioning steps running as one pipeline rather than a manual project each time.

Keeping masked test data compliant

The reason coverage across both structured and free-text data matters so much is that a single missed value undoes everything else. One name left in a notes column leaks the identity that every other transformation in the database was designed to protect — the masked keys and encrypted identifiers stop mattering the moment the free text gives the person away. Compliance depends on the weakest column, not the strongest, which is why discovery quality across the whole dataset is the real determinant of whether a lower environment is safe.

Detecting and de-identifying PII across both the relational and unstructured layers is what shrinks the sensitive-data footprint in your test environments and supports compliance with regulations like HIPAA, GDPR, PCI DSS, and SOC 2. Masking doesn't achieve compliance on its own — that's a property of your whole program — but keeping production PII out of environments that don't need it removes one of the largest and least-controlled sources of exposure. Lower environments tend to have looser access controls, more copies, and more people touching them than production does, so the data that reaches them should carry the least sensitive information possible.

The through-line is that good masking is really good detection followed by consistent replacement. Find every sensitive value across both halves of the database, replace each one in a way that preserves formats and relationships, and validate before you ship. That is what turns a risky production copy into a test environment your team can actually build against, and it's the foundation the rest of test data compliance is built on.