How do you handle PII in a data pipeline?

Detailed explanation

Interviewers want to see that you treat personal data as a design constraint from the start, not as a filter added at the end. Cover five areas.

1. Minimise

The safest PII is the PII you never ingest. Ask which use case needs each personal field; drop the rest at extraction. Prefer derived values (age band instead of birth date, city instead of street address) when they answer the question.

2. Classify and tag

• Direct identifiers: name, email, phone, national id, account number.
• Quasi-identifiers: birth date, postcode, gender, job title, which identify people in combination.
• Special or sensitive categories: health, biometrics, religion, and similar, which often carry stricter rules.

Tag columns in the catalog (Unity Catalog, Snowflake tags, DataHub) so policies and audits can find them.

3. Protect early

Technique	Reversible?	Joins still work?	Typical use
Drop the column	No	No	Field not needed
Keyed hash (HMAC) with a secret key	No, without the key	Yes, same input gives same token	Counting and joining users
Tokenisation (vault maps value to token)	Yes, through the vault	Yes	Systems that occasionally need the real value
Masking at query time	Data unchanged, view hides it	Yes	Mixed audiences on one table
Encryption of the column	Yes, with the key	No (unless deterministic)	Storing values few people may decrypt
Aggregation, generalisation	No	No	Published statistics

A plain hash of an email is weak: emails are guessable, so anyone can hash a candidate list and match it. Use a keyed hash with the key held outside the warehouse. Under GDPR, pseudonymised data is still personal data; only properly anonymised data falls outside it.

4. Control access

Role-based access to raw and restricted tables, column masking policies or secure views for everyone else, row-level policies where needed, audit logs of who read what, and service accounts with only the grants they use. Keep PII out of logs, error messages, dead-letter payloads and test fixtures.

5. Retention and deletion

Store personal data in as few places as possible, keyed by a stable subject id, with a retention period per dataset. A deletion request must reach every copy: raw files, curated tables, table-format history (time travel, old snapshots need vacuuming), backups according to policy, and downstream extracts. Keeping a mapping from subject id to token in one vault means deleting that mapping can make pseudonymised history unlinkable.

Example: pseudonymise at load, mask for analysts

import hashlib
import hmac

KEY = b"from-a-secret-manager"     # never hard-code a real key

def token(email: str) -> str:
    return hmac.new(KEY, email.strip().lower().encode(), hashlib.sha256).hexdigest()[:20]

print(token("[email protected]") == token(" [email protected]"), len(token("[email protected]")))

True 20

CREATE TABLE customers_restricted (customer_token text PRIMARY KEY, email text NOT NULL, country text);
INSERT INTO customers_restricted VALUES ('t1', '[email protected]', 'DE'), ('t2', '[email protected]', 'FR');

-- Analysts query a view: the token for joins, a masked email for support, no raw value.
CREATE VIEW customers_analytics AS
SELECT customer_token,
       left(email, 1) || '***@' || split_part(email, '@', 2) AS email_masked,
       country
FROM customers_restricted;

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'analyst') THEN CREATE ROLE analyst; END IF;
END $$;
GRANT SELECT ON customers_analytics TO analyst;

SET ROLE analyst;
SELECT * FROM customers_analytics ORDER BY customer_token;
RESET ROLE;

customer_token	email_masked	country
t1	a***@example.com	DE
t2	b***@example.com	FR

SET ROLE analyst;
SELECT email FROM customers_restricted;

The analyst role can use the view but reading the restricted table fails with permission denied. Warehouses offer the same idea as built-in features (Snowflake masking policies, Unity Catalog column masks, BigQuery policy tags).

Trade-offs and pitfalls

• Transforming before load (ETL) is the strongest protection but loses the raw value for future use cases; agree that trade-off with legal and data owners.
• Tokens can still be personal data when combined with quasi-identifiers; small groups in aggregates can re-identify people, so apply minimum group sizes in published reports.
• Rotating the HMAC key changes every token; plan for it, or use a vault.
• Deletion in immutable storage needs compaction or rewrite plus history cleanup; design partitioning so it is feasible.

Common mistakes

1. Copying raw production tables with PII into dev and CI environments.
2. Unsalted hashes presented as anonymisation.
3. PII in logs and dead-letter queues, which nobody includes in deletion processes.
4. Granting broad read access to raw layers “temporarily”.