You Can't Protect What You Can't Find: Detecting and Classifying PII in Data Pipelines

It's 4:47 PM on a Friday. A stakeholder pings you: "Hey, can we get the support tickets table into the warehouse? The CS team wants to chart ticket volume by region."

Sounds harmless. One table, one SELECT *, done before standup Monday.

You glance at the schema. Eighteen columns of pure innocence - ticket_id, status, created_at - and then column nineteen: support_notes. Free text. Years of customers writing things like:

Hi, this is Jane Doe, my order #4311 never arrived. I'm at ja******@example.com, or call me on 555-010-8899. Billing zip is 94110. Also my card ending in 4242 keeps failing??

Congratulations: your "harmless analytics table" is now one of the most sensitive assets in the company.

The Part Every PII Article Skips

Google "how to handle PII as a data engineer" and you'll get the same tidy list everywhere: masking, tokenization, hashing, encryption, anonymization. All correct. All useful. And all of them answer a question you can only ask after you know where your PII lives.

MASK THE EMAIL COLUMN is a one-liner. Knowing that contact_ref is full of emails, that the metadata JSON blob hides shipping addresses, and that support_notes contains entire identities - that's the actual job.

How PII Escapes

PII is a gas. It expands to fill whatever container you give it, and it escapes through the same few cracks every time:

• Cryptic names - x_field_7, courtesy of an upstream team you've never met
• Lying names - a column called contact_ref that is, in fact, emails
• Free text - customers volunteering their whole identity into a textbox
• Schema drift - a vendor feed that grew three columns overnight, silently. Nobody opens a ticket titled "FYI: new column of passport numbers."

So this series spends its first article on the unfashionable part: finding and classifying PII.

• Part 2 will pick the protection technique (mask vs hash vs tokenize vs encrypt - with the trade-offs that actually decide it)
• Part 3 puts the whole thing into a runnable pipeline

Parts 2 and 3 build on the two stages this post covers - the ones everyone else skips.

The Toy Dataset

Everything below runs against a deliberately messy customers.csv that ships with the companion repo. It's generated with Faker, so it contains zero real people - the only thing harmed in writing this article was the CSV's dignity:

Setup is two commands, no warehouse, no Docker, no cloud account:

git clone --depth 1 --sparse --filter=blob:none https://github.com/nbaubek/devto-articles-repo
cd devto-articles-repo
git sparse-checkout set pii-pipeline
cd pii-pipeline
uv sync

Now let's climb.

Rung 1 - Trust Column Names (a Little)

The cheapest detection you'll ever write: regex over schema names.

# src/pii/heuristics.py
import re

PATTERNS = {
    "email": r"e[-_]?mail",
    "phone": r"\bphone\b|\bmobile\b|\btel\b",
    "ssn": r"\bssn\b|social",
    "dob": r"\bdob\b|birth",
    "name": r"first[-_ ]?name|last[-_ ]?name|full[-_ ]?name",
}

def scan_column(name: str) -> list[str]:
    return [label for label, pattern in PATTERNS.items() if re.search(pattern, name, flags=re.IGNORECASE)]

$ uv run python -m pii.heuristics
customer_id → []
email → ['email']
phone → ['phone']
first_name → ['name']
last_name → ['name']
dob → ['dob']
contact_ref → []  # emails live here. Rung 1 is blind.
support_notes → []  # ...and here.

It catches every honestly-labeled column in about twenty lines, and it's fast enough to run in CI on every schema change.

Column-name heuristics are like checking luggage by reading the name tags: quick, cheap, and completely defeated by anyone who labels their suitcase "definitely not bombs."

Rung 2 - Profile the Actual Content

Fine. Don't ask the schema - ask the data. For each column, compute the fraction of non-null values that look like PII:

-- src/pii/profile_scan.sql (single-column version)
SELECT round(
    avg(
        CASE WHEN regexp_matches(contact_ref, '^[ \w.+-]+@[ \w-]+\.[ \w.]+$') THEN 1.0 ELSE 0.0 END
    ), 2
) AS email_ratio
FROM 'data/customers.csv'
WHERE contact_ref IS NOT NULL;

The repo loops this over every column and pattern (email, phone, SSN, credit card). The verdicts from our CSV:

contact_ref finally gets caught, because content doesn't lie. But support_notes walks free: only some rows contain emails, they're buried mid-sentence, and a threshold-based regex over prose is brittle by nature.

Two honest caveats. First, profiling means actually reading the data - run it as a controlled job with proper access, not as an ad-hoc query in the BI tool (that would be detecting PII by leaking PII, a bold strategy). Second, regexes only recognize formatted identifiers. "Jane Doe of 94110" contains no @ and no dashes. For that, you need something that reads.

Rung 3 - Read the Free Text

Presidio is Microsoft's open-source PII detector: named-entity recognition plus pattern recognizers, scoring each hit:

# src/pii/presidio_scan.py (trimmed)
from presidio_analyzer import AnalyzerEngine
from presidio_analyzer.nlp_engine import NlpEngineProvider

def get_analyzer() -> AnalyzerEngine:
    # Presidio's default wants the ~800 MB spacy model; we wire the small
    # one so `uv sync` is all the setup a reader needs.
    provider = NlpEngineProvider(nlp_configuration={
        "nlp_engine_name": "spacy",
        "models": [{"lang_code": "en", "model_name": "en_core_web_sm"}],
    })
    return AnalyzerEngine(nlp_engine=provider.create_engine())

analyzer = get_analyzer()

def scan_text(text: str) -> list[tuple[str, str, float]]:
    return [
        (hit.entity_type, text[hit.start:hit.end], round(hit.score, 2))
        for hit in analyzer.analyze(text=text, language="en")
    ]

Run it over a real row of our support_notes and the buried identity surfaces:

$ uv run python -m pii.presidio_scan
Hi, this is James Santos - order #3615 never arrived. I'm at williamjohnson@...

EMAIL_ADDRESS  wil***********@example.com  1.0
PERSON         James Santos                0.85
PHONE_NUMBER   555-658-7873                0.4
UK_NHS         555-658-7873                1.0  <- same digits, confidently wrong

The whole identity in one paragraph, found - and the output above is real, which is exactly why you should read it twice. The email is found at 1.0. The person at 0.85. But the phone number limps in at 0.4, and the same digits are simultaneously reported as a UK NHS number at 1.0.

These scores are a model's opinion, not a fact: recognizers want tuning for your domain, and this is by far the most expensive rung. In practice you don't Presidio-scan every row of every table nightly. You scan new tables, schema changes, and a sample of free-text columns, on a schedule.

Each rung catches what escaped the previous one - and all of them react after the fact.

Finding It ≠ Knowing How Dangerous It Is

So the ladder tells you where PII lives. Classification decides what it means - and this is the step that actually determines your protection strategy later.

Four tiers:

• Tier 0 - not PII. customer_id, timestamps. Breathe.
• Tier 1 - direct identifiers. Email, SSN, phone, name. Each one identifies a person on its own. These get the heavy treatment in Part 2.
• Tier 2 - quasi-identifiers. ZIP, birth date, gender. Each is boring alone; together they're a fingerprint. Latanya Sweeney's classic result: ZIP + birth date + gender uniquely identify about 87% of Americans.
• Tier 3 - sensitive attributes. Health conditions, salary, credentials. Not identifiers, but the payload - the thing you're afraid of revealing about whoever you re-identify.

Our toy customers.csv doesn't happen to carry one (support tickets rarely do), but the moment a plan_tier column turns into annual_income or a notes field turns into diagnosis, the classification step is identical - it's the protection strategy in Part 2 that changes, because a salary figure has no format to mask and nothing to tokenize; it mostly wants access control and aggregation limits, not a transformation.

Tier 2 deserves a diagram, because this is the one that bites teams who think they're done: "We stripped all names and emails" is not the same sentence as "nobody can be identified."

Two uncomfortable truths fall out of this, and both shape the rest of the series:

1. Stripping direct identifiers from a table does not make it anonymous
2. Even a hashed email is still personal data under GDPR (preview for Part 2)

The output of all our detection work is just this file:

# src/pii/classifications.yaml
version: 1
columns:
  customer_id: { tier: non_pii }
  email: { tier: direct }
  phone: { tier: direct }
  first_name: { tier: direct }
  last_name: { tier: direct }
  zip: { tier: quasi }   # dangerous in combination
  dob: { tier: quasi }
  contact_ref: { tier: direct }  # caught by profiling, not by its name
  support_notes: { tier: free_text, action: redact_on_read }

Detection produced a map; this file turns the map into policy. Part 3's pipeline will consume it directly. Stop detecting. Start declaring.

Data Contracts: Shift Left

One last twist - and the fourth escape route from the top of this article, the one the ladder itself never catches. Rungs 1 through 3 handle cryptic names, lying names, and free text; schema drift needs a different kind of defense, because a scanner only tells you about columns that already exist.

Everything on the ladder is also reactive - PII gets found after it arrives, maybe months after.

Data contracts flip the burden: the producer declares PII tiers as part of the schema, and your pipeline enforces the declaration.

# contracts/customers.v1.yaml - owned by the producing team
dataset: raw.customers
version: 1
owner: cs-analytics
columns:
  email: { pii_tier: direct }
  phone: { pii_tier: direct }
  first_name: { pii_tier: direct }
  last_name: { pii_tier: direct }
  zip: { pii_tier: quasi }
  dob: { pii_tier: quasi }
  contact_ref: { pii_tier: direct }
  support_notes: { pii_tier: free_text, redact: true }

A small validator (validate_contract.py in the repo) reconciles declaration against reality and has exactly three outcomes:

1. Declared and detected - fine, proceed
2. Declared but not detected - for direct identifiers, that's a stale (or optimistic) contract; warn loudly. For quasi and free_text tiers, the declaration is the control - the scanner can't check it for you
3. Detected but not declared - someone added a PII column without declaring it. Fail the build

That flips the conversation from "Legal found customer emails in the BI tool" (a genuinely terrible Friday) to "Your PR adds an undeclared column that's 97% emails" (a merely awkward Friday, and it happens at review time, not breach time).

Takeaways

• Protection is a solved problem once you know where PII lives. Detection is where pipelines actually fail
• Climb the ladder in order - names, then content, then language - because each rung's cost grows by an order of magnitude
• Classify into tiers, because "direct identifier" and "quasi-identifier" demand completely different handling
• Stripping names is not anonymization
• Contracts turn detection from an audit into an enforcement point. Shift it left

The full code for this post - messy CSV included - is in the companion repo.

Next in the series: You have a tier-tagged map of your PII. Now, which weapon? Masking, tokenization, hashing, or encryption - the join-key dilemma (why analytics-friendly hashes are exactly the brute-forceable ones), why your salt doesn't save you, and why a hashed email is still personal data under GDPR.