· DataTamed Team · 9 min read

Healthcare Data De-identification Example in SQL Server

A useful healthcare data de-identification example is not a spreadsheet with names blanked out. It is a repeatable database process that removes or transforms identifying data before a production-derived SQL Server database is ever attached to development or test infrastructure.

That distinction matters. A database can look harmless after obvious columns such as PatientName and NHSNumber are cleared, yet still expose people through dates, locations, free text, account references, or a combination of clinical attributes. For teams that need realistic test data, the job is to preserve the relationships and behaviour that applications depend on while reducing the chance that a real patient can be identified.

What the example needs to achieve

Consider a SQL Server database used by an outpatient provider. It contains patients, appointments, clinicians, referrals, invoices and clinical notes. Developers need it to reproduce scheduling faults, test billing changes and validate reporting queries. QA needs enough volume and variation to catch performance and edge-case failures.

The test copy must retain referential integrity. A patient linked to 18 appointments in production should still be linked to 18 appointments in test. A cancelled appointment must remain cancelled, date ranges should remain plausible, and billing totals should still reconcile where the test requires them to. But nobody in development should be able to identify the person behind that record.

That is a more useful target than simply saying "remove PII". It gives the DBA and test lead concrete acceptance criteria: maintain keys and distributions where needed, change identifying values consistently, and remove high-risk content that cannot safely be transformed.

Healthcare data de-identification example: a SQL Server model

Start with a simplified patient table:

Patient
-------
PatientId             int
NHSNumber             varchar(10)
FirstName             nvarchar(100)
LastName              nvarchar(100)
DateOfBirth           date
Postcode              varchar(10)
EmailAddress          nvarchar(256)
MobileNumber          varchar(30)
GPPracticeCode        varchar(20)
ClinicalNotes         nvarchar(max)

PatientId is used throughout the database as the internal foreign key. It can normally remain unchanged in a controlled test dataset because it has no meaning outside the system. The remaining fields need different treatment.

Direct identifiers: names, contact details, NHS numbers

Names, email addresses and phone numbers can be replaced with deterministic synthetic values. Deterministic means the same source value always becomes the same masked value during an import. If Jane Smith appears in the patient, appointment and invoice tables, every occurrence must map to the same substitute identity. Randomly generating a new name in each table breaks joins and creates test failures that do not exist in production — and someone will spend a morning chasing a bug that only lives in the masking rules.

An NHS number should not be merely hidden in the user interface or replaced with a string of zeroes. Replace it with a generated, format-valid test value, or remove it where the application allows. If downstream validation expects a check digit, generate values that meet that rule without deriving them from the original number.

Randomly generating a new name in each table breaks joins and creates test failures that do not exist in production. Click to share

Indirect identifiers: dates, postcodes, practice codes

Dates of birth, postcodes and GP practice codes require more judgement. A full date of birth combined with a small-area postcode and an unusual condition can identify someone even after names are changed. For many test cases, shifting every patient date consistently by a patient-specific offset protects chronological behaviour. For example, appointments, prescriptions and lab results for one patient might all move 143 days forward. Age-related logic still works, and the interval between events is retained.

Where geographic accuracy is unnecessary, reduce postcodes to a broader district or replace them with synthetic values from the same region. Where an application tests local service allocation, preserve the geographic category rather than the actual address. GP practice codes may need equivalent treatment if they identify a small provider population — a code that maps to a three-partner practice in a village is not much of a disguise.

Free text is the hard part

Clinical notes are usually the highest-risk field. They can contain names, addresses, family details and copied correspondence in free text — including the letter someone pasted in wholesale, signature block and all. Regex replacement can catch predictable patterns, but it will not reliably de-identify narrative notes. The safer operational choice is often to exclude notes from the test import, replace them with representative synthetic text, or use a separately validated redaction process. Keeping real clinical prose because a test screen needs paragraphs is a poor trade-off.

De-identification is not one masking rule

A practical policy separates direct identifiers from indirect identifiers and operational secrets. Direct identifiers include names, contact details, national identifiers and addresses. Indirect identifiers include dates, locations, rare diagnoses and event sequences. Operational secrets include passwords, API keys, SMTP settings, document paths and integration endpoints that may be stored in configuration tables.

This classification prevents a common failure mode: a masking exercise that protects the patient table but leaves identifiers in audit data, scanned-document metadata, error logs or ETL staging tables. SQL Server estates often have several databases and integration jobs around the clinical application, and the staging table nobody has opened since 2019 still has a full name column in it. The scope must cover every object that moves into non-production, not only the tables people see in the application.

It also avoids treating pseudonymisation and anonymisation as interchangeable. Replacing a name with a token is pseudonymisation if a mapping exists or the person can reasonably be re-identified from other fields. Under UK GDPR, health information is special category personal data. Whether a dataset is sufficiently anonymous depends on the data, the recipients, the available auxiliary information and the realistic likelihood of re-identification. Your data protection, clinical governance and security teams should set the required standard for the use case.

Apply protection before cloning

The safest point to mask is during the controlled import from production, before any clone is created. If teams first restore a live backup to a development server and run masking scripts afterwards, there is a window in which production data exists in a less controlled environment. A failed script, a missed table or an unauthorised database attachment can turn that window into an incident.

An import-stage approach changes the sequence. Production data is read from a backup or approved source, masking rules are applied, and only the protected database image becomes available for development and QA. Every subsequent clone inherits the same protected baseline.

This is where copy-on-write cloning is useful operationally. Instead of restoring a large masked database for each team, create one sanitised base image and provision lightweight writable clones. The virtual disk holds the shared baseline; each clone stores only its changes. A developer can reset a damaged test database rather than asking for another full restore, and QA can refresh from the same approved state before a regression run.

For SQL Server teams, the clone should attach as a real writable database on the registered SQL Server instance. Existing connection strings, migration tools and test frameworks can use it normally. The masking controls protect the source data; the cloning controls make the protected version fast enough that teams do not work around the process.

Preserve the behaviours tests actually need

The right masking method depends on the test. A performance test may need production-like row counts, index selectivity, data skew and large attachments. A UI test might need only a modest dataset with believable names and dates. A finance reconciliation test may require exact monetary values, while a developer investigating a validation rule may not.

Write those requirements down before creating rules. Otherwise, masking tends to become either too weak, leaving unnecessary data exposed, or too destructive, leading teams to request unmasked copies "just this once".

A strong rule set usually protects these properties:

  • Stable foreign-key relationships and repeatable masked identities
  • Date and event ordering where workflow logic depends on it
  • Realistic value distributions for query plans and reports
  • Removed or safely substituted free text, attachments and document metadata
  • Disabled outbound integrations, user credentials and notification destinations

The final point is easy to overlook. A de-identified copy can still cause harm if it sends appointment reminders, posts to a live API or emails real addresses retained in a queue table. Post-clone scripts should disable agents, clear message queues, redirect endpoints and remove credentials before the database is handed over. The test environment that texts 400 real patients at 2am about an appointment that does not exist is a story every healthcare DBA has heard a version of.

A de-identified copy can still cause harm if it sends appointment reminders to real addresses left in a queue table. Click to share

Make the process auditable

De-identification should be a controlled release process, not a one-off SQL script on a shared drive. Record the source approval, rule-set version, import date, operator, target environment and validation result. Restrict who can create imports, who can provision clones and who can attach them to SQL Server instances.

Validation needs both technical and operational checks. Technical checks can search for known test patient identifiers, inspect row counts, verify that no production email domains remain, and confirm foreign keys and key reports still work. Operational checks confirm that the clone is on an approved host, non-production users have the right access, and outbound jobs are disabled.

DataTamed supports this pattern by applying masking during import, then creating SQL Server clones from the protected image. Role-based access and an exportable audit report — every clone, mask and backup event as a row you can hand to Word, Excel, PDF or CSV — help keep the process controlled, including in air-gapped environments where cloud-based test-data services are not an option. The server runs inside your network, so production-grade patient data never crosses the boundary in the first place.

The goal is not to make a test database look vaguely safe. It is to make protected, production-realistic data the quickest database your teams can get. When a clean clone is available in seconds rather than a restore in hours, the secure route becomes the route people actually use.

]]>