Lesson 1: The PII Detection Problem at Scale

Course: PySpark PII Detection | Duration: ~20 min | Lesson: 1 of 7

A regulator emails TheWorldShop with one question: "Where is your customer PII stored?" Dev, the data engineer who drew the short straw, opens the warehouse. Four thousand tables. Twelve years of data. A raw zone full of JSON blobs nobody has looked at since the engineer who wrote them left. He has two weeks to answer.

His first instinct is a grep for column names like email and ssn. It finds a few hundred. Then he opens a table called support_tickets with a single column, body, and reads a message where a customer pasted their full credit card number to "verify their identity." No column called card. No schema hint. Just PII sitting in free text, invisible to any search that trusts column names.

Dev's grep was never going to work. The PII isn't where the schema says it is. This course is about finding it anyway, across terabytes, where eyeballing is impossible and column names lie.

2. Concept Explanation

Finding PII in a small, well-labeled table is easy. Finding it across a real warehouse is a genuinely hard problem, and the reasons are worth naming before we reach for tools.

Why column names aren't enough

• PII hides in free text. A comments, body, notes, or description column can hold names, cards, SSNs, addresses, anything a human typed. The column name tells you nothing.
• PII hides in semi-structured blobs. A payload JSON column or a properties map can carry email addresses three keys deep, in fields nobody documented.
• Schemas lie and drift. A column called user_ref might hold raw emails. A column called notes might hold government IDs. Names reflect intent, not contents.

Why a single regex pass isn't enough either

Regex is the right first tool (next lesson), but it has two failure modes at scale:

• Context-dependent PII. A nine-digit number could be an SSN, an order ID, or a part number. A name like "Brooklyn" could be a person or a borough. Regex matches shape, not meaning, so it can't tell a person's name from a place without context. Names, in particular, have no fixed pattern at all.
• Recall vs precision. Loosen a regex to catch more (higher recall) and it floods you with false positives. Tighten it (higher precision) and it misses real PII. There's no single threshold that's right for both ssn columns and free-text body columns.

The hybrid approach

Production PII detection combines two layers:

• Regex / pattern matching for structured PII with a reliable shape: emails, SSNs, credit cards (Luhn-checkable), phone numbers, IBANs. Fast, cheap, high precision when the pattern is distinctive.
• NLP / named-entity recognition (NER) for context-dependent PII: person names, locations, organizations in free text. This is where Microsoft Presidio (later lessons) earns its place, it runs a language model that recognizes "Priya Shah" as a person from sentence context, something no regex can do.

You run both, combine their signals, and attach a confidence score so a human can triage the uncertain hits (a whole lesson later). Neither layer alone is enough: regex misses the names, NLP is too slow and noisy for the structured stuff.

Why this needs Spark

The detection logic isn't the hard part. The scale is. A real warehouse is terabytes across thousands of columns, and you re-scan as data lands. That's an embarrassingly parallel problem, scan each partition independently, which is exactly what Spark is for. The rest of this course is detection logic plus the Spark engineering to run it over the whole warehouse without it taking two weeks or falling over.

3. Worked Example

Here's the shape of the problem on three TheWorldShop columns, showing why one tool can't cover them.

# Column 1: `email` (structured). Regex nails it, high precision.
"priya.shah@example.com"   ->  EMAIL via regex, confidence 0.99

# Column 2: `order_note` (free text). Regex finds the card; NLP finds the name.
"Customer Priya called, please refund card 4111 1111 1111 4019"
   ->  regex:  CREDIT_CARD "4111111111114019" (Luhn-valid), confidence 0.95
   ->  NLP:    PERSON "Priya", confidence 0.80
   ->  regex alone MISSES the name; NLP alone is slow and misses the card shape

# Column 3: `external_ref` (looks structured, lies). Shape collides with PII.
"123-45-6789"
   ->  regex:  SSN pattern matches!  confidence 0.40 (LOW)
   ->  reality: it's an internal reference number, NOT an SSN -> false positive

The third case is the whole challenge in miniature. The string looks exactly like an SSN. A naive regex scan flags it, you "discover" PII that isn't there, and now your report cries wolf. Lower the confidence (the - separators help, but the column name external_ref and the surrounding values argue against SSN) and a human reviews it. The detector's job isn't a yes/no; it's a scored hit that routes to the right place.

Across the warehouse, you run this over every column of every table as a Spark job: pattern-match the structured stuff, NER the free text, score every hit, and emit a per-column report (% rows matching, entity types found, confidence distribution). That report is the answer to the regulator's question, and the rest of the course builds it.

4. Your Turn

Exercise: TheWorldShop hands you a table events.raw_payloads with three columns: event_id (UUID), event_type (string like "login", "checkout"), and payload (a JSON string that varies by event type). You're asked to assess its PII risk.

1. Which column is the highest PII risk and why, even though none of the three column names mentions PII?
2. Explain why a regex-only scan of payload would both miss real PII and produce false positives. Give one concrete example of each.
3. Describe the two-layer (regex + NLP) approach you'd apply to payload, and what a single combined "hit" record should contain so a human can triage it.

5. Real-World Application

Every large data organization eventually faces Dev's two-week question, usually triggered by a GDPR Subject Access Request, a CCPA audit, or a breach. The teams that answer it in hours rather than weeks have a standing PII scan running over the warehouse, not a frantic grep. AWS Macie, Google Cloud DLP, and Microsoft Purview are the managed versions of exactly the job this course builds: they combine pattern matching with ML classification and emit per-column PII findings. The open-source path, PySpark plus Microsoft Presidio, is what teams reach for when the data can't leave their VPC or the managed tools cost more than the engineering.

The free-text problem is not hypothetical. The single most common source of "PII we didn't know we had" is customer-support and free-text fields, where users and agents paste card numbers, full names, and government IDs into columns that were never meant to hold them. The 2017 Equifax breach and countless smaller incidents involved sensitive data sitting in places the schema didn't advertise. This is also why "we masked the ssn column" is a dangerously incomplete statement: the unmasked SSN is in the notes column, and only a content scan finds it.

The scale framing matters for your job specifically. Detection logic you can prototype in pandas on a laptop; running it daily over a petabyte warehouse, incrementally, without melting the cluster, is the actual engineering. That's why this course is PySpark-first: the detector is the easy half, and the distributed scan job is the half that makes it real.

6. Recap + Bridge

PII detection at scale is hard because the PII isn't where the schema says it is: it hides in free-text and JSON columns, and column names lie. No single tool covers it. Regex matches shape (great for structured PII like emails and cards, blind to names and to meaning), and NLP/NER reads context (great for names in free text, too slow and noisy for the structured stuff). The production answer is a hybrid: run both, score every hit, route the uncertain ones to a human, and do it all as a parallel Spark job over the whole warehouse.

Next lesson starts on the structured layer: regex patterns for PII. You'll write production-grade patterns for SSNs, emails, phone numbers, and credit cards (with the Luhn check that separates a real card from any 16 digits), and you'll see how to compile them once and run them across a billion rows without paying the compilation cost on every one.