Before you can encrypt, mask, or delete personal data, you need to know where it lives. That sounds obvious until someone asks “which of our 400 tables hold card numbers?” and the honest answer is a shrug. GDPR Article 30 expects a controller to keep a record of processing that describes “the categories of personal data”, and you cannot write that record from memory.
That is the job of sensitive data discovery: scan the stores you own, find columns that hold PII, and label them. The usual first attempt is a folder of regexes. It finds everything, including order numbers, timestamps, ZIP+4 codes and SKUs, and after a week nobody reads its report.
In this post we build a small scanner in Python that does the boring parts properly:
Walks a SQLite database and a folder of CSV exports.
Samples each column with reservoir sampling (bounded memory, reproducible).
Finds candidates with regexes, then validates them: Luhn and network prefixes for cards, ISO 7064 mod-97 for IBANs, SSA rules for SSNs, libphonenumber for phones.
Uses the column name as a second signal and computes a confidence score per column.
Writes a data inventory in JSON and Markdown with classification labels and masked examples.
Compares the result with Microsoft Presidio on the same samples.
Versions: Python 3.13, Faker 40.44.0, phonenumbers 9.0.42, pytest 9.2.0, and for the comparison presidio-analyzer 2.2.366 with spaCy 3.8.18 and en_core_web_sm 3.8.0. All test data is synthetic (seeded Faker), and every output block below is copied from a real run.
What a column-level scanner needs
Discovery for analytics, as the PixelPlex guide describes it, is about finding patterns and insights in data. Discovery for privacy asks a narrower question about each column: “does this hold a data type that law, contracts or policy say we must protect?” Answering it per column, not per value, is the key design decision. A single cell can look like anything. Two hundred cells from the same column rarely lie.
| Stage | What it does | Why it matters |
|---|---|---|
| Walk | List tables/files and columns | Coverage: you only classify what you enumerate |
| Sample | Up to 200 non-empty values per column | Bounded cost per column, no full-table scans |
| Detect | Cheap regex finds candidates | Fast, high recall |
| Validate | Checksums and numbering rules | Removes look-alikes |
| Score | Hit ratio + column-name signal | A column decision, not a cell decision |
| Report | Labels, confidence, masked examples | Something a human can review and act on |
Step 1. Synthetic data with traps
make_data.py builds shop.db with three tables and two CSV exports. Real PII is mixed with values that a lazy regex will misread:
| Column | Real content | Looks like |
|---|---|---|
| orders.order_number | 16 random digits | a card number |
| app_events.device_serial | 16 random digits | a card number |
| orders.sku | US48XKQ93821PZ7A style codes | an IBAN |
| payments.batch_ref | 000-, 666-, 9xx- codes | an SSN |
| customers.created_at, zip, app_events.app_version | dates, ZIP+4, versions | phone numbers |
| orders.card_masked | **** **** **** 1234 | a card column by name only |
| customers.notes | free text, some with an email or phone | mostly harmless text |
| leads.contact | half emails, half phones | two entity types in one column |
"""Generate a synthetic SQLite database and CSV exports for the scanner demo. Every value comes from Faker (seeded) or random.Random (seeded). No real PII. """ import csv import random import sqlite3 from datetime import datetime from pathlib import Path from faker import Faker SEED = 42 # Fixed ranges: relative ones like "-30d" would change the data every day. START, END = datetime(2024, 1, 1), datetime(2026, 1, 1) DATA = Path(__file__).parent / "data" def digits(rng: random.Random, n: int) -> str: return "".join(rng.choice("0123456789") for _ in range(n)) def main() -> None: fake = Faker("en_US") Faker.seed(SEED) rng = random.Random(SEED) DATA.mkdir(exist_ok=True) (DATA / "exports").mkdir(exist_ok=True) db_path = DATA / "shop.db" db_path.unlink(missing_ok=True) con = sqlite3.connect(db_path) con.executescript( """ CREATE TABLE customers (id INTEGER PRIMARY KEY, full_name TEXT, email TEXT, phone TEXT, ssn TEXT, zip TEXT, created_at TEXT, notes TEXT); CREATE TABLE orders (id INTEGER PRIMARY KEY, order_number TEXT, customer_id INTEGER, amount REAL, sku TEXT, card_masked TEXT, placed_at TEXT); CREATE TABLE payments (id INTEGER PRIMARY KEY, customer_id INTEGER, card_number TEXT, iban TEXT, batch_ref TEXT, company_code TEXT); """ ) customers = [] for i in range(1, 401): note = fake.sentence(nb_words=10) roll = rng.random() if roll < 0.08: note += f" Prefers email: {fake.free_email()}" elif roll < 0.16: note += f" Call back on {fake.phone_number()}" customers.append((i, fake.name(), fake.email(), fake.phone_number(), fake.ssn(), fake.zipcode_plus4(), fake.date_between(START, END).isoformat(), note)) con.executemany("INSERT INTO customers VALUES (?,?,?,?,?,?,?,?)", customers) orders = [] for i in range(1, 1201): sku = (rng.choice(["US", "DE", "FR", "GB"]) + digits(rng, 2) + "".join(rng.choice("ABCDEFGHJKLMNPQRSTUVWXYZ0123456789") for _ in range(12))) orders.append((i, digits(rng, 16), rng.randint(1, 400), round(rng.uniform(5, 900), 2), sku, "**** **** **** " + digits(rng, 4), fake.date_time_between(START, END).isoformat(timespec="seconds"))) con.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?,?)", orders) payments = [] for i in range(1, 401): batch_ref = f"{rng.choice(['000', '666', str(rng.randint(900, 999))])}-{digits(rng, 2)}-{digits(rng, 4)}" payments.append((i, rng.randint(1, 400), fake.credit_card_number(), fake.iban(), batch_ref, "CP-" + digits(rng, 6))) con.executemany("INSERT INTO payments VALUES (?,?,?,?,?,?)", payments) con.commit() con.close() with open(DATA / "exports" / "leads.csv", "w", newline="") as fh: w = csv.writer(fh) w.writerow(["lead_id", "name", "contact", "company", "bank_acct"]) for i in range(1, 301): contact = fake.company_email() if rng.random() < 0.5 else fake.phone_number() w.writerow([i, fake.name(), contact, fake.company(), fake.iban()]) with open(DATA / "exports" / "app_events.csv", "w", newline="") as fh: w = csv.writer(fh) w.writerow(["event_id", "device_serial", "app_version", "event", "ts"]) for i in range(1, 601): w.writerow([fake.uuid4(), digits(rng, 16), f"{rng.randint(1, 12)}.{rng.randint(0, 9)}.{rng.randint(0, 99)}.{rng.randint(1000, 9999)}", rng.choice(["open", "tap", "purchase", "close"]), fake.date_time_between(datetime(2025, 12, 1), END).isoformat(timespec="seconds")]) print(f"wrote {db_path.relative_to(DATA.parent)}: customers=400 orders=1200 payments=400") print("wrote data/exports/leads.csv (300 rows), data/exports/app_events.csv (600 rows)") if __name__ == "__main__": main() # Output: # wrote data/shop.db: customers=400 orders=1200 payments=400 # wrote data/exports/leads.csv (300 rows), data/exports/app_events.csv (600 rows) Faker is seeded, but its docs warn that results are not guaranteed to be stable across patch versions, so the version is pinned to the patch level in requirements.txt.
Step 2. The “before”: regex only
The scanner has a --naive flag that keeps the regexes and turns off validators and the name signal. This is what many homegrown PII detection scripts look like:
python -m pii_scan --db data/shop.db --csv data/exports --naive [naive] columns=31 findings=31 confirmed=23 review=8 app_events.event_id CREDIT_CARD review conf=0.01 valid=1% regex=1% app_events.device_serial CREDIT_CARD confirmed conf=1.00 valid=100% regex=100% app_events.device_serial PHONE confirmed conf=1.00 valid=100% regex=100% app_events.app_version PHONE confirmed conf=1.00 valid=100% regex=100% app_events.ts PHONE confirmed conf=1.00 valid=100% regex=100% leads.contact CREDIT_CARD review conf=0.01 valid=0% regex=0% leads.contact EMAIL confirmed conf=0.53 valid=53% regex=53% leads.contact PHONE review conf=0.47 valid=47% regex=47% leads.bank_acct IBAN confirmed conf=1.00 valid=100% regex=100% leads.bank_acct PHONE confirmed conf=1.00 valid=100% regex=100% customers.email EMAIL confirmed conf=1.00 valid=100% regex=100% customers.phone CREDIT_CARD review conf=0.05 valid=5% regex=5% customers.phone PHONE confirmed conf=1.00 valid=100% regex=100% customers.ssn US_SSN confirmed conf=1.00 valid=100% regex=100% customers.ssn PHONE confirmed conf=1.00 valid=100% regex=100% customers.zip PHONE confirmed conf=1.00 valid=100% regex=100% customers.created_at PHONE confirmed conf=1.00 valid=100% regex=100% customers.notes CREDIT_CARD review conf=0.01 valid=0% regex=0% customers.notes EMAIL review conf=0.09 valid=9% regex=9% customers.notes PHONE review conf=0.07 valid=8% regex=8% orders.order_number CREDIT_CARD confirmed conf=1.00 valid=100% regex=100% orders.order_number PHONE confirmed conf=1.00 valid=100% regex=100% orders.sku IBAN confirmed conf=1.00 valid=100% regex=100% orders.placed_at PHONE confirmed conf=1.00 valid=100% regex=100% payments.card_number CREDIT_CARD confirmed conf=1.00 valid=100% regex=100% payments.card_number PHONE confirmed conf=1.00 valid=100% regex=100% payments.iban IBAN confirmed conf=1.00 valid=100% regex=100% payments.iban PHONE confirmed conf=1.00 valid=100% regex=100% payments.batch_ref US_SSN confirmed conf=1.00 valid=100% regex=100% payments.batch_ref PHONE confirmed conf=1.00 valid=100% regex=100% 23 columns “confirmed”, and only 7 of them really hold PII. The phone regex fires on timestamps, ZIP+4 codes, app versions, SSNs, IBANs and card numbers. The card regex is certain that order numbers and device serials are payment cards. orders.sku is “100% IBAN”. A report like this gets ignored, and then the one real finding in it gets ignored too.
Step 3. Validators
Each detector is a pair: a regex that finds candidates and a function that rejects look-alikes. Here is the whole module:
"""PII detectors: a candidate regex plus a validator that rejects look-alikes.""" from __future__ import annotations import re from dataclasses import dataclass from typing import Callable import phonenumbers # ---------- validators ---------- def luhn_ok(number: str) -> bool: """Luhn mod-10 check over a string of digits.""" total = 0 for i, ch in enumerate(reversed(number)): d = int(ch) if i % 2 == 1: d *= 2 if d > 9: d -= 9 total += d return total % 10 == 0 def card_ok(raw: str) -> bool: num = re.sub(r"[ -]", "", raw) if not num.isdigit() or not 12 <= len(num) <= 19: # Maestro goes down to 12 digits return False if len(set(num)) == 1: # 0000000000000000 passes Luhn, it is not a card return False # Major networks start with 2-6; 1800/2131 are 15-digit JCB prefixes that Faker generates. if num[0] not in "23456" and not num.startswith(("1800", "2131")): return False return luhn_ok(num) def iban_ok(raw: str) -> bool: s = raw.replace(" ", "").upper() if not re.fullmatch(r"[A-Z]{2}\d{2}[A-Z0-9]{11,30}", s): return False rearranged = s[4:] + s[:4] as_int = "".join(str(int(c, 36)) for c in rearranged) # A=10 ... Z=35 return int(as_int) % 97 == 1 SSN_BLOCKLIST = {"078-05-1120", "219-09-9999"} # numbers the SSA lists as misused/advertising def ssn_ok(raw: str) -> bool: m = re.fullmatch(r"(\d{3})-(\d{2})-(\d{4})", raw) if not m: return False area, group, serial = m.groups() if area in ("000", "666") or area.startswith("9"): return False if group == "00" or serial == "0000": return False return raw not in SSN_BLOCKLIST def email_ok(raw: str) -> bool: local, _, domain = raw.rpartition("@") if not local or local.startswith(".") or local.endswith(".") or ".." in local: return False labels = domain.split(".") if len(labels) < 2 or not labels[-1].isalpha(): return False return all(lbl and not lbl.startswith("-") and not lbl.endswith("-") for lbl in labels) def phone_matches(text: str, region: str = "US", leniency=phonenumbers.Leniency.VALID) -> list[str]: """libphonenumber's matcher instead of a regex. VALID = real ranges, POSSIBLE = right length.""" return [m.raw_string for m in phonenumbers.PhoneNumberMatcher(text, region, leniency=leniency)] def phone_weight(text: str) -> float: if phone_matches(text): return 1.0 if phone_matches(text, leniency=phonenumbers.Leniency.POSSIBLE): return 0.5 # right length, but not in an assigned range: half credit return 0.0 # ---------- detector table ---------- @dataclass(frozen=True) class Detector: entity: str label: str # classification label for the inventory pattern: re.Pattern # cheap candidate finder (the "naive" scanner uses only this) validate: Callable[[str], bool] name_hints: frozenset[str] # column-name tokens that count as a signal DETECTORS = [ Detector("CREDIT_CARD", "Restricted", re.compile(r"\b(?:\d[ -]?){11,18}\d\b"), card_ok, frozenset({"card", "cc", "pan", "ccnum", "credit"})), Detector("US_SSN", "Restricted", re.compile(r"\b\d{3}-\d{2}-\d{4}\b"), ssn_ok, frozenset({"ssn", "social", "tin", "taxid"Source：https://dev.to/audrey_xu/sensitive-data-discovery-in-python-build-a-pii-scanner-that-validates-not-just-matches-gn7Related posts
Wie man mit osFoundry Custom App kleine interne Apps baut und hostet
ODF vs. Managed Block Storage on IBM Cloud: How I Model the Tradeoff for Stateful Workloads
AWS KMS Just Shipped ML-KEM. Post-Quantum Crypto Stopped Being a Side Project
Why I built a bot that remembers what people delete — and what it taught me
An MCP server in pure Python, under 100 lines, where tools can only propose
