From Detection to Production: A PII-Safe Pipeline in Python and DuckDB

Part 1 gave you a map: every PII-bearing column, tier-tagged. Part 2 armed you: HMAC for direct identifiers, tokens for what must be reversible, masking for display, generalization for quasi-identifiers. Now the part where articles usually wave their hands and your actual weekend disappears: putting it together.

Everything in this post runs from the companion repo with uv sync — no warehouse, no Docker, no cloud account. DuckDB stands in for your storage layers so the mechanics survive the translation; the article flags every spot where production wants something heavier.

The architecture: three zones and a key

Zone	File	What lives there	Access story
Raw	raw.duckdb	everything, unmasked, as landed	restricted by access, short-lived by policy
Curated	curated.duckdb	pseudonymized join keys, masked phones, generalized quasi-identifiers	the analytics surface
Vault	vault.duckdb	token → identity, for authorized reversal	the crown jewels; audit everything

And floating beside them: PII_HMAC_KEY, from your secrets manager to the pipeline's environment. It never touches disk in the repo.

The raw zone argument

First, the uncomfortable design decision. Can't you just protect PII before it lands anywhere — mask at ingestion, skip the raw zone entirely? In a perfect world, yes. In this one: sources drift, transforms have bugs, and the day you need to reprocess last month you'll want the original bytes. Most pipelines keep a raw zone.

The point this series argues for is smaller and sharper: once raw PII lands in cheap, copyable storage, you've inherited a governance problem — so treat the raw zone like the liability it is. Concretely, in the repo: it's a separate database file, separate from anything an analyst or BI tool touches, and it's the one zone where you'd set a retention timer (the demo skips implementing it; your warehouse's lifecycle policies shouldn't). Protection then happens at the first boundary out of raw — not "eventually, in some dashboard."

The transform: policy-driven, not vibes-driven

The pipeline reads Part 1's classifications.yaml and applies Part 2's decision per tier — direct → pseudonymize, quasi → generalize, free_text → keep-restricted, non_pii → pass-through. The heart of it is ~15 lines:

# src/pii/pipeline.py (trimmed)
curated_rows = [
    (
        row["customer_id"],
        pseudonymize(row["email"], key),        # direct -> HMAC join key
        pseudonymize(row["contact_ref"], key),  # direct -> same key for the same person
        mask_phone(row["phone"]),               # direct -> display mask
        row["zip"][:3],                         # quasi   -> generalize
        int(row["dob"][:4]),                    # quasi   -> generalize
        row["support_notes"],                   # free_text -> kept, restricted at serving
    )
    for row in rows
]

$ uv run python -m pii.pipeline
warning: PII_HMAC_KEY not set — using the dev-only key. Fine for the
warning: demo, a firing offence in production.
raw      : 300 rows landed (restricted zone, add retention)
curated  : 300 rows, 291 with user_pseudo_id = contact_token
vault    : 300 reversible tokens (guard this file)
joins survive pseudonymization: email and contact_ref -> same token

That 291 is the Part 2 payoff, measured: 97% of contact_ref values are the same email as the email column (Part 1 planted that lie), and HMAC's determinism means both columns collapse onto one join key per person. Names never make it into curated at all — they live only in the vault.

The serving layer: roles, not tables

DuckDB has no native masking policies — it's an embedded database, roles aren't its job. So the repo simulates the pattern with views and says so honestly:

-- src/pii/serve.py: what analysts get
CREATE OR REPLACE VIEW v_customers_analyst AS
SELECT
    customer_id,
    user_pseudo_id,
    phone_masked,
    zip3,
    birth_year,
    NULL AS support_notes      -- redact_on_read, from classifications.yaml
FROM curated_customers;

$ uv run python -m pii.serve
analyst sees:
  ('0822e8f3-...', '0138f3a7b71bff72', '***-***-9935', '044', 1942, None)

support sees:
  ('47378190-...', '6814444dac9bd257', '***-***-7873',
   "Hi, this is James Santos — order #3615 never arrived. I'm at williamjohnson@...")

In production this exact shape becomes a warehouse-native policy attached to the column, evaluated per query:

-- the Snowflake version of the same idea
CREATE MASKING POLICY mask_notes AS (val VARCHAR) RETURNS VARCHAR ->
  CASE WHEN CURRENT_ROLE() = 'SUPPORT' THEN val ELSE NULL END;

ALTER TABLE curated_customers
  MODIFY COLUMN support_notes SET MASKING POLICY mask_notes;

The lesson isn't "use views" — it's that redaction lives at the serving boundary, declared once, instead of being remembered separately by every downstream consumer.

Erasure: "delete the user" is three deletes

A GDPR erasure request arrives for one customer. Where do they live? Everywhere (Part 2 explained why pseudonyms don't exempt you):

$ uv run python -m pii.erasure [e-mail]
vault    deleted 1 row(s)
curated  deleted 1 row(s)
raw      deleted 1 row(s)

backups: not covered here — that's what crypto-shredding is for.

The trick worth stealing: you don't need the vault to find the person in curated — hmac(key, email) recomputes the pseudonym deterministically, so erasure fans out to every zone from the request itself. And the honest footnote the script prints: DELETE doesn't touch backups and snapshots. That's why erasure-ready pipelines either keep backup windows short or encrypt per-customer and destroy keys on request (crypto-shredding).

The guardrail: fail the PR, not the incident

Everything above is one refactor away from silently leaking PII again. So the repo's five tests do the remembering for you:

# tests/test_no_leaked_pii.py (trimmed)
def test_curated_zone_has_no_direct_pii(tmp_path):
    # run the pipeline into temp DBs, then:
    for column in columns:                    # every curated column...
        for pattern in (EMAIL_RE, PHONE_RE):  # ...must match zero PII patterns
            hits = con.execute(
                f"SELECT count(*) FROM curated_customers "
                f"WHERE regexp_matches(CAST({column} AS VARCHAR), '{pattern}')"
            ).fetchone()[0]
            assert hits == 0, f"{column} matches PII pattern {pattern} — leak!"

Worth a pause on that f-string, given the subject matter: column and pattern are interpolated straight into the SQL. That's safe here specifically because both come from a closed, hardcoded list defined in the test file, not from anything a user or upstream system controls — there's no injection surface. But copy this pattern into anything where column could originate from a config file, an API response, or a contract someone else edits, and you've built the exact kind of hole this series spends three articles closing. Parameterize or validate against an allow-list the moment that input stops being something you typed yourself.

$ uv run pytest -v
tests/test_no_leaked_pii.py::test_curated_zone_has_no_direct_pii PASSED [ 20%]
tests/test_no_leaked_pii.py::test_analyst_view_exposes_no_free_text PASSED [ 40%]
tests/test_no_leaked_pii.py::test_pseudonymization_preserves_joins PASSED [ 60%]
tests/test_no_leaked_pii.py::test_every_column_is_classified PASSED      [ 80%]
tests/test_no_leaked_pii.py::test_validate_contract_passes PASSED        [100%]

5 passed in 5.40s

Read those test names again — each one is a failure mode from Parts 1 and 3 that now cannot happen silently: PII leaking into curated, free text reaching analysts, join keys breaking, a new column appearing without classification, a contract drifting from reality. This is the whole series in one command: detection became a pipeline stage, not an audit.

(If the unit/integration split and the mocking pattern above look familiar, they're the same ones from my pytest crash course for data pipelines — this test suite is that article's advice applied to a real problem instead of a toy one.)

What this repo deliberately doesn't do

Honesty section, no charge:

• No orchestration — nothing schedules detection scans or key rotation. Put Presidio on a nightly sample, not in CI (too slow) and not never (that's how drift wins).
• No catalog or lineage — tier tags belong in your data catalog too, so governance sees what engineering sees.
• No access auditing — the vault should log every reversal; DuckDB won't do that for you.
• DuckDB views ≠ policies — production wants Snowflake masking policies or BigQuery column-level security, where the database enforces what the view merely promises.
• Single key, single rotation story, unimplemented — versioned tokens (v2:...) are designed for, not built.

The series, in one diagram

Part 1 argued detection is a pipeline stage, not an audit. Part 2 argued technique choice follows from what the data must still do. Part 3's argument is the last one: protection is architecture — zones, boundaries, and a guardrail that fails the build — because any PII decision that lives only in someone's memory is a future incident.

The full repo is here — clone it, break it, add a column of undeclared emails to the CSV and watch the contract test fail. That's the fastest way to make this series yours.