You cannot protect columns you have not found, and PII hides in notes fields and custom_attr_7 far more than in columns named ssn. This playbook layers engine-native discovery with an LLM classification pass over metadata and profiled shapes, producing a PII map and masking plan mapped to DPDP and GDPR obligations.
Tier: Core · Workflow area: Security & Compliance · ~35 minutes
Engine-native classifiers (SQL Server Data Discovery & Classification, Oracle Data Safe, open-source scanners) find the obvious: columns whose names or formats scream email, phone, card number. The residue they miss is where breaches live — free-text fields holding pasted addresses, misused generic columns, legacy imports. An LLM is genuinely good at the residue, because classification from names, comments and value shapes is a language task. The design constraint is that classification must not itself become the leak: the model sees metadata and profiled patterns, not raw rows, unless it is a tenant-approved model cleared for that data.
Run the engine-native pass first — it is free signal.
-- SQL Server: built-in discovery
-- SSMS: Tasks > Data Discovery and Classification > Classify Data
-- or query existing labels:
SELECT schema_name, table_name, column_name,
label, information_type
FROM sys.sensitivity_classifications sc
JOIN sys.columns c ON c.object_id = sc.major_id
AND c.column_id = sc.minor_id
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id;
On Oracle, run Data Safe's sensitive-data discovery job. On Postgres, there is no native equivalent — the profiling in step 2 carries more weight.
Profile shapes, not values, for the LLM pass. Build a metadata-plus-shape extract that describes every column without containing any actual row:
-- Postgres example: shape profile per text column
SELECT c.table_name, c.column_name, c.data_type,
col_description(format('%I.%I', c.table_schema,
c.table_name)::regclass, c.ordinal_position) AS comment,
(SELECT COUNT(DISTINCT x) FROM (SELECT substring(t.col from
'^[A-Za-z0-9@.+-]{0,5}') x FROM tbl t LIMIT 1000) s)
AS prefix_cardinality -- illustrative; use a profiler
FROM information_schema.columns c WHERE c.table_schema = 'public';
In practice use a profiling script that emits, per column: name, type, comment, null rate, distinct rate, min/max length, and a regex-generalized pattern of sampled values (aaa@aaa.aa, +91-ddddd-ddddd, dddd dddd dddd dddd) — shapes that classify without disclosing.
Ask the LLM to classify the residue and challenge the native labels.
Below: (a) engine-native classification results, (b) a shape profile
of every column (name, type, comment, null/distinct rates, length
stats, generalized value patterns). No real values are included.
Produce a PII map:
1. For every column, classify: direct identifier / quasi-identifier /
sensitive-special-category (health, biometrics, financial) /
not personal data / CANNOT DETERMINE. Give a one-line reason
citing the evidence (name, comment, or pattern).
2. Flag columns the native scan missed but the shapes suggest —
especially free-text and generic columns whose patterns contain
email/phone/ID shapes.
3. Flag native labels the shapes contradict.
4. List quasi-identifier COMBINATIONS in the same table that jointly
re-identify (e.g., dob + pincode + gender).
Be conservative: prefer CANNOT DETERMINE over confident guesses.
The combination analysis in point 4 is something native scanners do not attempt and regulators increasingly expect.
Verify the flags with targeted, in-boundary queries. Every CANNOT DETERMINE and every LLM-only flag gets a human check run locally — the raw values stay in the database:
-- Does the free-text notes field actually contain email addresses?
SELECT COUNT(*) FROM support_tickets
WHERE notes ~ '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}';
-- Phone shapes in a generic column?
SELECT COUNT(*) FROM entity_attributes
WHERE attr_key = 'custom_attr_7'
AND attr_value ~ '^\+?[0-9][0-9 -]{8,14}$';
A result like support_tickets.notes: 12,481 rows matching email pattern upgrades a hypothesis into a finding with a number on it.
Draft the masking plan, column by column, technique by technique. With the verified map, have AI draft the plan — then review it as the DBA who must keep referential integrity alive. Each row of the plan: column, classification, environments in scope (prod stays, lower environments mask), technique, and the integrity note. Techniques by class: deterministic tokenization for join-key identifiers (email as key must mask consistently across tables), format-preserving randomization for phones/cards where app validation runs, nulling or fixed literals for free-text, generalization for quasi-identifier combinations (dob → birth year). The classic failure the review must catch: masking customers.email randomly while orders.customer_email masks differently, silently breaking every join the test suite relies on.
Map to obligations and hand the DPO a decision document. Final artifact: the PII map with per-column verification status, the masking plan, and an obligations column drafted by AI (DPDP: personal data — notice + purpose limitation; GDPR Art. 9 if health-related) marked explicitly as draft for DPO review. The DPO rules on the law; you rule on the columns.
sys.sensitivity_classifications (ADD SENSITIVITY CLASSIFICATION) so audit logging picks them up; Dynamic Data Masking can implement parts of the plan but is access-convenience, not security — say so in the plan.The finished PII map is itself a sensitive document — a guided tour of where your most valuable data sits. Store it access-controlled with the security team, version it, and treat "who can read the PII map" as part of the review it documents.
Native scanners find the labeled PII; the LLM pass finds where PII actually hides — free text, generic columns, re-identifying combinations — and it can do so from shapes alone, without touching a single real row. Verify every flag with a local query, mask with join-keys deterministic, and let the DPO own the law.