Skip to main content

PII Scrubbing Funnels Before Training

Level 11: Data Engineering for AImedium27 minPII redaction funnelsregex vs NER detectorsseverity-based dispositionbenchmark decontaminationanti-join blind-spot detectionCASEconditional aggregation

Drive a redact-or-drop decision from a two-detector PII scan table, find the entities one detector misses, and place benchmark decontamination as the stage that runs right after.

Two detectors, because one kind of PII has a shape and the other does not

The PII stage of a curation pipeline is the point where somebody's phone number stops being a data-quality curiosity and starts being a legal problem, because a model that memorized it will happily repeat it. NVIDIA's NeMo Curator runs the stage as regex plus NER in series, and the reason there are two detectors is the reason the whole stage is interesting:

Table
The two-detector design in one table. Regex cannot recognize a name; a model can, and a model is also expensive and occasionally wrong about a number it could have matched exactly.
detectorwhat it is good atwhytypical entity types
regexstructured PIIthe value has a fixed shape you can write down as a patternSSN, credit card, email, most phone formats
nerunstructured PIIthe value has no shape, only a role in a sentenceperson name, organization, address, location
both, in seriesthe union of the twoeach one is blind to what the other seesthe full entity list, with overlap on the shaped types
The two-detector design in one table. Regex cannot recognize a name; a model can, and a model is also expensive and occasionally wrong about a number it could have matched exactly.

A social security number is 123-45-6789. That is a pattern, and a regex matches it exactly, every time, for free. A person's name is "Dana Whitfield", which is not a pattern at all: it is a sequence of tokens that a named-entity-recognition model classifies from context. No regex catches it, and no amount of regex tuning ever will.

They overlap in the middle. A good NER model will also flag an email or an SSN, because those entity types are in its label set too. That overlap is worth having, and the gaps in it are worth querying, which is what the practice does.

Data engineers own the funnel even when a machine-learning team owns the models

The detectors themselves are somebody else's problem. The pipeline around them is yours. What a data engineer owns here is the funnel:

  1. Scan. Each detector runs over each document and writes a row per entity type it found, with a count. That table is the artifact you query, and it is the seed below.
  2. Decide. Every scanned document gets a disposition, and the disposition comes from severity, not from volume.
  3. Act. Redacted documents continue down the funnel with their spans masked. Dropped documents leave the corpus entirely and their doc_ids go on a list somebody can audit.

Severity is the part with real judgment in it, and the industry line is roughly this:

  • A social security number or a credit card number anywhere in the document means drop the whole document. These values are unambiguously sensitive, redacting them still leaves the surrounding context that identified the person, and a corpus is cheap compared to that risk.
  • Emails and person names mean redact and keep. They are common enough that dropping every document containing one would take a large bite out of the corpus for very little safety gained.

Notice that this rule is about entity type, never about match count. A document with one SSN is dropped. A document with twenty person names is redacted and kept. Any query that ranks documents by total matches and drops the top of the list is implementing a rule nobody agreed to.

The stage right after this one is why benchmarks lie

Decontamination follows PII redaction in the NeMo Curator order, and it deserves a sentence because it explains a headline you have read. Decontamination searches the training corpus for n-gram overlap with published evaluation sets and removes what it finds. Without it, the eval questions are sitting inside the training data, the model has effectively seen the answer key, and "our model aced the benchmark" is a data bug rather than a result. It is the same failure mode as a leaked label in a machine-learning feature table, and it is data-engineering work: a join between two corpora on shingles of text.

Common mistake: treating the absence of a scan row as "the detector ran and found nothing" when it actually means "there is no hit here". A scan-results table only records hits, so a document that is genuinely clean has zero rows, not a row with a count of zero. That is why finding the clean documents is an anti-join against the corpus table and not a WHERE match_count = 0.

Interview nuance: the one-sentence version that lands is "regex catches structured PII like SSNs and emails, NER catches unstructured PII like names, and you run both because each is blind to the other's category." Follow it with the disposition rule, drop on the unambiguous identifiers and redact the rest, and you have said everything a junior is expected to know about this stage.

On a real platform this differs. The table below is a scan output; the scan itself is a service. Microsoft Presidio is the common open-source implementation of exactly this two-detector design, and the managed clouds sell the same shape (Amazon Comprehend PII detection, Google Cloud DLP). Governance pressure is why this stage keeps getting more attention rather than less: Gartner projects 70 percent of organizations will have adopted modern data-quality solutions by 2027, driven substantially by AI data requirements. Whichever detector you run, the artifact it hands you is a table of hits, and the funnel over it is the query you just wrote.

Sample data for this example
CREATE TABLE corpus_docs (
  doc_id     TEXT,
  word_count INTEGER
);
INSERT INTO corpus_docs (doc_id, word_count) VALUES
  ('doc_101', 3120),
  ('doc_102',  890),
  ('doc_103', 5640),
  ('doc_104', 2210),
  ('doc_105',  760),
  ('doc_106', 4480),
  ('doc_107', 1530),
  ('doc_108', 6910),
  ('doc_109', 2740),
  ('doc_110',  980),
  ('doc_111', 8350);

CREATE TABLE pii_scan_results (
  doc_id      TEXT,
  detector    TEXT,     -- regex | ner, the two detectors the stage runs in series
  entity_type TEXT,     -- EMAIL | PHONE | SSN | PERSON_NAME
  match_count INTEGER   -- spans this detector found of this type; a row exists only on a hit
);
INSERT INTO pii_scan_results (doc_id, detector, entity_type, match_count) VALUES
  ('doc_101', 'regex', 'SSN',          2),
  ('doc_101', 'regex', 'EMAIL',        5),
  ('doc_101', 'ner',   'PERSON_NAME',  7),
  ('doc_102', 'regex', 'EMAIL',        9),
  ('doc_102', 'ner',   'PERSON_NAME', 12),
  ('doc_103', 'regex', 'EMAIL',        3),
  ('doc_103', 'ner',   'PHONE',        4),
  ('doc_103', 'ner',   'PERSON_NAME',  6),
  ('doc_104', 'regex', 'EMAIL',        4),
  ('doc_104', 'ner',   'EMAIL',        4),
  ('doc_104', 'ner',   'PERSON_NAME',  2),
  ('doc_105', 'regex', 'PHONE',        2),
  ('doc_105', 'ner',   'SSN',          1),
  ('doc_106', 'regex', 'PHONE',        8),
  ('doc_107', 'regex', 'SSN',          1),
  ('doc_107', 'regex', 'EMAIL',        1),
  ('doc_107', 'ner',   'PERSON_NAME',  3),
  ('doc_108', 'ner',   'PERSON_NAME',  4);
Worked example (SQL)
-- What each detector actually contributed. The zero in the regex column for PERSON_NAME is the
-- whole argument for running two detectors, and the EMAIL row shows where they overlap.
SELECT entity_type,
       SUM(CASE WHEN detector = 'regex' THEN match_count ELSE 0 END) AS regex_matches,
       SUM(CASE WHEN detector = 'ner'   THEN match_count ELSE 0 END) AS ner_matches,
       COUNT(DISTINCT doc_id) AS docs_flagged
FROM pii_scan_results
GROUP BY entity_type
ORDER BY entity_type;

Apply

Your turn

The task this lesson builds to.

Write a query that returns each scanned document's disposition, as (doc_id, total_matches, disposition), most matches first and then by doc_id, over pii_scan_results(doc_id, detector, entity_type, match_count).

total_matches is the document's summed match_count across every detector and entity type. disposition is 'drop' when the document has any 'SSN' match at all and 'redact' otherwise. Alias every column exactly.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a query that returns the detector blind spots, as (doc_id, entity_type), sorted by doc_id and then entity_type, over pii_scan_results.

A blind spot is an entity type the 'ner' detector found in a document where the 'regex' detector recorded no match of that same type in that same document. Return one row per blind spot.

2 hints and 1 automated check are waiting in the workspace.