PII Detection & Masking: Tokenization, Format-Preserving Encryption, Dynamic Masking

pii masking is the discipline that keeps senior data engineers out of a regulator's inbox — and the single subsystem most modern data platforms inherit half-built and pretend is finished. A warehouse without a documented masking story is a warehouse with a latent GDPR fine attached; a pipeline without a pii detection pass is a pipeline that leaks emails into free-text comment columns nobody classified. The engineering trade-off lives in which transform you pick per column — deterministic tokenization for join keys, format preserving encryption for typed downstream analytics, dynamic data masking for role-based views over one physical table — not in whether you need any of these at all.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "explain the difference between tokenization and FPE" or "what does a snowflake masking policy look like against a customers.email column consumed by two different roles?" or "how do you run a pii scanner across an S3 landing zone every night without paying for the full DLP scan on every rewrite?" It walks through why data privacy engineering is now an engineering discipline instead of a legal footnote, the three-layer detection stack (DLP + regex + ML classifier), vault-based vs deterministic tokenization, the FF1 / FF3 family of format-preserving encryption schemes with envelope-KMS key management, and the dynamic-masking policy layer that Snowflake, BigQuery, and Databricks all ship natively in 2026. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the pipeline shape on the ETL practice library →, and sharpen the trade-off axis with the design practice library →.

---

On this page

• Why PII handling is an engineering discipline
• PII detection — DLP scanners + regex + ML classifiers
• Tokenization — replace values with opaque tokens
• Format-Preserving Encryption — encrypt without changing shape
• Dynamic masking + Snowflake / BigQuery policy tags
• Cheat sheet — PII masking recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why PII handling is an engineering discipline

The regulatory trifecta forces detect / transform / enforce / audit into the pipeline — pii masking is a first-class column-level transform, not a compliance checkbox

The one-sentence invariant: GDPR, CCPA, and HIPAA together shifted the burden from "the legal team writes a policy" to "the data engineer implements a column-level control that survives every reload, every clone, every downstream materialisation, and every schema change". The 2026 reality is that every serious warehouse (Snowflake, BigQuery, Databricks, Redshift) ships native masking policies, tokenization udfs, or column-level ACLs, and every serious lakehouse ships a DLP scanner as a managed service. The engineering discipline is picking the right transform per column, wiring it through the ingestion and view layer, and monitoring the audit trail — not authoring a policy document.

The four "must-answer" axes interviewers actually probe.

• Detect. How does your pipeline know which columns hold PII? Column names lie (a notes column might have an email); values lie (a phone_e164 column might be empty). Real answers cite a three-layer scanner: a managed DLP service on landing, a regex fallback on ingest, and an ML classifier for free-text fields. The senior signal is naming the false-positive management strategy — confidence thresholds, human review queues, and column-name heuristics — not just the tools.
• Transform. What operation happens to the sensitive value? The transform axis has four dominant options: redact (drop the value entirely, e.g. NULL), mask (replace with a fixed constant, e.g. ***MASKED***), tokenize (replace with an opaque token that can be reversed via a vault), and encrypt (either full encryption or format-preserving encryption). Picking the wrong transform breaks downstream joins, type inference, or reversibility.
• Enforce. Where in the stack does the transform happen? Options: at ingestion (before the value ever lands in the warehouse — the safest, hardest to reverse), in a materialised view (safe but doubles storage), in a dynamic policy at query time (snowflake masking policy, BigQuery policy tags — flexible, role-aware), or in the BI tool (dangerous — the warehouse still holds plaintext).
• Audit. How do you prove the control was applied to every read? The regulator does not care that you wrote a policy; they care that the policy was evaluated on every access. The audit story is Snowflake's ACCESS_HISTORY view, BigQuery's INFORMATION_SCHEMA.DATA_ACCESS, Databricks Unity Catalog audit logs, or a homegrown pgaudit-driven pipeline.

The regulatory trifecta — GDPR / CCPA / HIPAA — and what each demands from an engineer.

• GDPR (EU). Article 5 requires pseudonymisation — replacing identifiers with tokens that cannot be re-linked without additional information kept separately. Article 32 requires appropriate technical measures — read as "actually implement encryption and access controls, don't just document them." Article 17 requires the right to erasure — read as "your architecture must support deleting a customer's data across every downstream copy, materialised view, and backup." The engineering translation is tokenization plus row-level tombstones plus a pii_erasure_events topic that fans out across every consumer.
• CCPA (California). Similar to GDPR but with a stronger emphasis on sale of personal information. The engineering translation is a do_not_sell flag on every customer, propagated through Kafka, materialised into every warehouse, and enforced in every marketing pipeline. The right-to-know request must be answerable within 45 days — read as "have a query that returns everything you know about a customer, indexed by customer_id."
• HIPAA (US healthcare). PHI (Protected Health Information) has a stricter list — 18 identifiers must be either removed or replaced. The Safe Harbor de-identification standard requires removing all 18; the Expert Determination standard allows retaining some if a statistician certifies the re-identification risk. Most warehouses ship a PHI-specific masking policy template — use it.
• The overlap. All three regulations converge on the same engineering primitives: identify sensitive columns, transform them, control who can see the plaintext, log every access. Any masking architecture that solves for one solves for the other two with minor tweaks.

What the 2026 warehouse ships natively — the "batteries included" surface.

• Snowflake. Column-level masking policies (CREATE MASKING POLICY), row access policies (CREATE ROW ACCESS POLICY), tag-based propagation (CREATE TAG + ALTER TABLE ... SET TAG), ACCESS_HISTORY for audit. The masking policy is a SQL function that runs at query time; the result depends on CURRENT_ROLE(). The tag lets you attach a masking policy to a column once and have it automatically apply to every clone, view, and materialised view.
• BigQuery. Policy tags (a hierarchical taxonomy: pii/high, pii/medium, pii/low), column-level ACLs (grant roles/bigquerydatapolicy.maskedReader on a policy tag), Data Catalog integration for discovery. The DLP service scans tables and applies policy tags automatically; the column-level ACL enforces at query time.
• Databricks. Unity Catalog column-level ACLs, dynamic views (CREATE VIEW ... AS SELECT CASE WHEN is_member('admin') THEN email ELSE '***' END), and Delta Live Tables expect clauses for detection. Databricks lags Snowflake on masking policy syntax but the primitives are equivalent.
• Redshift. Column-level access control (via GRANT SELECT (col1, col2) — column-scoped grants) plus row-level security policies. Redshift's masking story is thinner than Snowflake's; teams often layer a materialised-view-based masking pattern on top.
• The lakehouse view. Parquet and Iceberg don't ship masking natively — you enforce at the query engine (Trino, Athena, Spark) or via a data-catalog-driven policy layer (Apache Ranger, Immuta, Privacera).

What interviewers listen for on the "why" question.

• Do you name the four axes — detect, transform, enforce, audit — as the mental model in the first minute? — senior signal.
• Do you distinguish pseudonymisation (reversible with a separately-held key) from anonymisation (irreversible)? — required answer for any GDPR conversation.
• Do you push back on "just mask everything at the BI tool" with the point that the warehouse still holds plaintext and every backup / clone leaks? — required answer.
• Do you cite native warehouse primitives (Snowflake masking policy, BigQuery policy tags) instead of homegrown wrappers? — senior signal.

Worked example — the "detect / transform / enforce / audit" mental model

Detailed explanation. The interviewer opens with "walk me through how you'd design PII handling for a new analytics warehouse from scratch." The senior answer starts with the four-axis mental model, then instantiates each axis against a canonical customers table with name, email, phone, ssn, and a free-text notes column. The junior answer starts with "we'll use Snowflake masking policies" — which is a how without a what.

• Detect. A three-layer scanner runs on landing: managed DLP + regex + ML. The output is a pii_inventory table listing every column tagged with a PII category (email, phone, SSN, free-text-mixed) and a confidence score.
• Transform. Per PII category, pick a transform: SSN → FPE (typed shape preserved); email → deterministic tokenization (joins survive); free-text notes → redact-with-classification (drop the value, keep a "notes contained PII" flag).
• Enforce. Attach a Snowflake tag pii_category = 'email' to each column; a matching masking policy runs at query time based on the caller's role.
• Audit. Snowflake ACCESS_HISTORY logs every read of a tagged column with the caller identity and the masking decision. A nightly job aggregates and stores in a compliance database.

Question. For a customers table with columns (customer_id, name, email, phone, ssn, notes, signup_ts), propose the transform per column, the enforcement mechanism, and the audit signal. Justify each choice against the four axes.

Input.

Column	Category	Free-text?	Downstream use
customer_id	not-PII	no	join key everywhere
name	direct PII	no	display only
email	direct PII	no	join key for cross-source stitching, display
phone	direct PII	no	outbound calling (marketing)
ssn	HIPAA / GDPR high	no	rare — verification only
notes	indirect PII (mixed)	yes	free-text search
signup_ts	not-PII	no	analytics

Code.

-- Snowflake — tag-driven masking policy assignment
CREATE OR REPLACE TAG pii_category
  ALLOWED_VALUES 'email', 'phone', 'ssn', 'name', 'notes-mixed';

-- Attach tags to columns (one-time; propagates to clones + views)
ALTER TABLE customers MODIFY COLUMN name    SET TAG pii_category = 'name';
ALTER TABLE customers MODIFY COLUMN email   SET TAG pii_category = 'email';
ALTER TABLE customers MODIFY COLUMN phone   SET TAG pii_category = 'phone';
ALTER TABLE customers MODIFY COLUMN ssn     SET TAG pii_category = 'ssn';
ALTER TABLE customers MODIFY COLUMN notes   SET TAG pii_category = 'notes-mixed';

-- Masking policy — role-aware
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_ADMIN', 'CS_AGENT')       THEN val
    WHEN CURRENT_ROLE() IN ('ANALYST_TOKENIZED')           THEN SHA2(val, 256)
    ELSE                                                        '***MASKED***'
  END;

-- Attach the masking policy to every column with tag pii_category='email'
ALTER TAG pii_category SET MASKING POLICY email_mask
  ON COLUMN (STRING) WHERE tag_value = 'email';

Step-by-step explanation.

1. The tag pii_category is created once and attached to every sensitive column. Tags propagate through clones, views, and materialised views automatically — a critical property for a warehouse where the same physical table has many downstream consumers.
2. The masking policy email_mask is a SQL function evaluated at query time. It receives the plaintext value and returns a masked, tokenized, or plaintext value based on CURRENT_ROLE(). Three roles are handled: PII_ADMIN and CS_AGENT see clear, ANALYST_TOKENIZED sees a deterministic hash (join-safe), everyone else sees the masked constant.
3. The ALTER TAG ... SET MASKING POLICY binding attaches the policy to every column with tag_value = 'email' — so the same policy applies uniformly across the entire warehouse without hand-coding per-column.
4. Similar policies (with different behaviours per PII category) are created for phone, ssn, name, and notes-mixed. The notes-mixed policy is a redact — the free-text column is replaced with NULL for non-admin roles because inline PII detection at query time is too expensive.
5. The audit signal comes from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY — every query is logged with the columns accessed, the caller identity, and (if the masking policy references CURRENT_ROLE()) the masking decision.

Output.

Column	Category	Transform	Enforcement	Audit
customer_id	not-PII	none	none	none
name	direct	mask constant	Snowflake tag + policy	ACCESS_HISTORY
email	direct	deterministic hash for analyst; clear for CS	Snowflake tag + policy	ACCESS_HISTORY
phone	direct	mask constant for analyst; clear for marketing	Snowflake tag + policy	ACCESS_HISTORY
ssn	high	FPE for verification; redact for analyst	Snowflake tag + FPE UDF	ACCESS_HISTORY
notes	indirect	redact for non-admin	Snowflake tag + policy	ACCESS_HISTORY
signup_ts	not-PII	none	none	none

Rule of thumb. Every masking design starts with a per-column table like the one above. If you can't fill in "Transform / Enforcement / Audit" for every column in five minutes, you don't have a design — you have a wish. The four-axis mental model is the single most useful thing to bring into the interview room.

Worked example — GDPR right-to-erasure across a lakehouse

Detailed explanation. A EU customer files an Article 17 erasure request. Their customer_id = 4242 and every downstream copy of their data must be deleted within 30 days. The naive answer is "run DELETE on the customers table" — which misses (a) materialised views, (b) derived analytics tables, (c) Delta Lake time-travel copies, (d) Parquet backups in S3, (e) BI-tool caches, and (f) the ML feature store. The senior answer is an erasure pipeline that fans out a tombstone event.

• The erasure event. A single Kafka message {"customer_id": 4242, "erasure_ts": "2026-07-04T00:00:00Z"} published to a pii_erasure_events topic.
• The fan-out. Every consumer of the customers table subscribes; each consumer executes a delete or overwrite in its own storage.
• The audit. The compliance database logs one row per consumer per erasure event, timestamped when the delete completed.

Question. Design the erasure pipeline for a lakehouse with (a) a bronze Iceberg customers table, (b) a silver customer_ltv derived table, (c) a gold BI-consumable customer_dashboard mv, and (d) a feature store customer_features in Redis. Cite the guarantee each store gives you and the fallback if the guarantee is missing.

Input.

Store	Kind	Delete supported?	Time-travel?
bronze customers	Iceberg on S3	yes (row-level delete)	yes (30 day retention)
silver customer_ltv	Iceberg	yes	yes
gold customer_dashboard	Snowflake mv	REFRESH required	yes (via cloning)
feature store	Redis	yes (DEL key)	no

Code.

# erasure_pipeline.py — fan-out consumer for pii_erasure_events
from kafka import KafkaConsumer
import json, boto3, redis, snowflake.connector
from pyiceberg.catalog import load_catalog

consumer = KafkaConsumer(
    "pii_erasure_events",
    bootstrap_servers="kafka.internal:9092",
    group_id="erasure-fanout",
    value_deserializer=lambda v: json.loads(v),
)

catalog = load_catalog("s3://lake/warehouse")
sf      = snowflake.connector.connect(user="erasure_bot", account="acme")
r       = redis.Redis(host="features.internal", port=6379)

for msg in consumer:
    cid = msg.value["customer_id"]
    ts  = msg.value["erasure_ts"]

    # 1. Bronze — Iceberg row-level delete
    tbl = catalog.load_table("bronze.customers")
    tbl.delete(row_filter=f"customer_id = {cid}")

    # 2. Silver — Iceberg row-level delete
    tbl = catalog.load_table("silver.customer_ltv")
    tbl.delete(row_filter=f"customer_id = {cid}")

    # 3. Gold — Snowflake DELETE + expire time-travel clones
    sf.cursor().execute(f"DELETE FROM gold.customer_dashboard WHERE customer_id = {cid}")
    sf.cursor().execute(f"ALTER TABLE gold.customer_dashboard SET DATA_RETENTION_TIME_IN_DAYS=0")

    # 4. Feature store — DEL by key
    r.delete(f"features:customer:{cid}")

    # 5. Audit — one row per store per erasure
    log_audit(cid, ts, stores=["bronze", "silver", "gold", "features"])

    consumer.commit()

-- Snowflake — expire time-travel data so old copies of the row are unreachable
ALTER TABLE gold.customer_dashboard  SET DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE gold.customer_dashboard  SET DATA_RETENTION_TIME_IN_DAYS = 7;  -- back to normal

Step-by-step explanation.

1. The consumer subscribes to the pii_erasure_events topic. Every erasure request enters as one Kafka message; each consumer handles one store. Adding a new store means writing a new consumer, not modifying every producer.
2. For Iceberg (bronze + silver), row-level delete is native (Iceberg V2 spec). The delete writes a positional-delete file that masks the row from future reads; a subsequent compaction rewrites the affected data file without the row.
3. For Snowflake gold, a DELETE removes the row from the current version but Snowflake's time-travel retention keeps the pre-delete data reachable via AT (OFFSET => -30 minutes). The two-step ALTER TABLE ... DATA_RETENTION_TIME_IN_DAYS = 0 then back to 7 forces expiration of the pre-delete history for that row.
4. For Redis, DEL is instantaneous — the feature store has no history to worry about. This is easy but audit-critical: log every DEL so you can prove the erasure ran.
5. The audit row is the compliance artifact. Each consumer logs (customer_id, erasure_ts, store, completion_ts); the compliance dashboard aggregates and reports the 30-day compliance window. Missing rows → SLA violation → the regulator gets an unhappy answer.

Output.

Store	Action	Time to delete	Audit event
bronze customers	Iceberg row delete	seconds	1
silver customer_ltv	Iceberg row delete	seconds	1
gold customer_dashboard	Snowflake DELETE + retention flip	minutes	1
feature store	Redis DEL	milliseconds	1

Rule of thumb. GDPR erasure is not a DELETE — it's a fan-out pipeline. Design the erasure primitive as a Kafka topic on day one; every store gets its own consumer; the compliance dashboard aggregates the audit trail. Retrofitting this pattern after the first regulator letter is 10× more expensive than shipping it with the first table.

Worked example — the "PII column drift" audit

Detailed explanation. A team ships the customers table with a documented PII inventory. Six months later, a new column preferences_json is added by a downstream team. The column contains free-text {"email_pref": "[e-mail]"} blobs — email addresses inside JSON, undetected by the original inventory. The PII drift is silent: nothing failed, but the warehouse now leaks PII to anyone with SELECT on the column. The senior answer wires a drift detector into the pipeline that re-scans new columns automatically and re-tags them.

• The drift. Any new column in a tagged table can silently contain PII.
• The detector. A daily job that scans every column added since the last audit; runs DLP + regex + ML; applies tags.
• The fail-safe. Any column without a pii_reviewed tag defaults to "restricted" — read access requires an admin role.

Question. Design the drift detector and the fail-safe default. Show the Snowflake tag-based enforcement and the daily audit query.

Input.

Element	Value
Warehouse	Snowflake
Detection cadence	daily
Fail-safe default	RESTRICTED for un-tagged columns
Escalation	Slack alert to #data-privacy

Code.

-- Baseline tag with a review flag; every column defaults to 'unreviewed'
CREATE OR REPLACE TAG pii_reviewed
  ALLOWED_VALUES 'yes', 'no', 'unknown';

-- Query — every column added in last 24h without a pii_reviewed tag
WITH new_cols AS (
  SELECT table_catalog, table_schema, table_name, column_name, data_type
  FROM   snowflake.account_usage.columns
  WHERE  created > DATEADD('day', -1, CURRENT_TIMESTAMP())
),
tagged AS (
  SELECT object_database AS table_catalog,
         object_schema   AS table_schema,
         object_name     AS table_name,
         column_name,
         tag_name,
         tag_value
  FROM   snowflake.account_usage.tag_references
  WHERE  tag_name = 'pii_reviewed'
)
SELECT n.table_catalog, n.table_schema, n.table_name, n.column_name, n.data_type
FROM   new_cols n
LEFT   JOIN tagged t
  ON   n.table_catalog = t.table_catalog
 AND   n.table_schema  = t.table_schema
 AND   n.table_name    = t.table_name
 AND   n.column_name   = t.column_name
WHERE  t.tag_value IS NULL     -- no pii_reviewed tag
   OR  t.tag_value = 'unknown';

# drift_detector.py — daily job
import snowflake.connector, requests

sf = snowflake.connector.connect(user="privacy_bot", account="acme")

cur = sf.cursor()
cur.execute(open("new_untagged_columns.sql").read())
rows = cur.fetchall()

if rows:
    msg = f"PII drift alert — {len(rows)} new columns without pii_reviewed tag:\n"
    for r in rows[:20]:
        msg += f"  {r[0]}.{r[1]}.{r[2]}.{r[3]} ({r[4]})\n"
    requests.post(SLACK_HOOK, json={"channel": "#data-privacy", "text": msg})

# Fail-safe — apply a RESTRICTED masking policy to any column without a review tag
for db, schema, table, col, dtype in rows:
    cur.execute(
        f"ALTER TABLE {db}.{schema}.{table} MODIFY COLUMN {col} SET TAG pii_reviewed = 'unknown'"
    )

Step-by-step explanation.

1. The SQL joins account_usage.columns (every column ever) against account_usage.tag_references (every tag ever applied) on the four-part identifier. The LEFT JOIN + IS NULL finds columns that have never been reviewed.
2. The filter created > DATEADD('day', -1, CURRENT_TIMESTAMP()) limits to columns added in the last 24 hours. The full audit runs weekly against everything, but the daily alert focuses on new drift.
3. The Python job runs the query, formats a Slack message, and (critically) applies the fail-safe: any un-reviewed column gets pii_reviewed = 'unknown'. A separate masking policy attached to pii_reviewed = 'unknown' returns NULL for every non-admin role — the column is read-restricted until a human reviews it.
4. The Slack alert lands in #data-privacy for a human owner. The owner runs the DLP scanner against the column, decides the PII category, and updates the tag to pii_reviewed = 'yes' with the correct pii_category.
5. The fail-safe is the critical part: without it, the drift detector is just noise. With it, the platform cannot silently leak PII from a new column; the worst case is a temporary read-lock until the human review.

Output.

Detection window	Un-reviewed columns	Slack alerts	Fail-safe applied
Day 1	12 (baseline backlog)	1	12
Day 2	3 (new columns overnight)	1	3
Steady state	0-2 per day	1 per busy day	0-2 per day

Rule of thumb. Every new column is a potential PII leak. The daily drift detector plus the "restrict-by-default" fail-safe is the two-line policy that keeps the warehouse safe as the schema evolves. Manual review at ingestion doesn't scale; automation with human-in-the-loop for uncertain cases does.

Senior interview question on the PII engineering discipline

A senior interviewer often opens with: "You inherit a Snowflake warehouse with 400 tables, no masking policies, and a GDPR audit in 60 days. Walk me through what you do on day 1, day 7, day 30, and day 60 — and what you would fight for as the non-negotiables."

Solution Using a four-phase 60-day plan grounded in the detect / transform / enforce / audit axes

-- Day 1 — inventory
CREATE OR REPLACE VIEW privacy.column_inventory AS
SELECT table_catalog, table_schema, table_name, column_name, data_type
FROM   snowflake.account_usage.columns
WHERE  deleted IS NULL;

-- Day 7 — automated DLP scan feeds pii_category tags
CREATE OR REPLACE TAG pii_category  ALLOWED_VALUES 'email','phone','ssn','name','address','notes-mixed';
CREATE OR REPLACE TAG pii_reviewed  ALLOWED_VALUES 'yes','no','unknown';

-- Day 30 — masking policies + tag-driven propagation
CREATE OR REPLACE MASKING POLICY mask_email  AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('PII_ADMIN')       THEN v
       WHEN CURRENT_ROLE() IN ('ANALYST_HASHED')  THEN SHA2(v, 256)
       ELSE                                             '***' END;

ALTER TAG pii_category SET MASKING POLICY mask_email
  ON COLUMN (STRING) WHERE tag_value = 'email';

-- Day 60 — audit + reporting
CREATE OR REPLACE VIEW privacy.access_audit AS
SELECT user_name,
       query_id,
       direct_objects_accessed,
       base_objects_accessed,
       query_start_time
FROM   snowflake.account_usage.access_history
WHERE  ARRAY_SIZE(base_objects_accessed) > 0
  AND  query_start_time > DATEADD('day', -30, CURRENT_TIMESTAMP());

Step-by-step trace.

Phase	Days	Deliverable	Guardrail
Inventory	1–7	column_inventory view; 400 tables catalogued	none — pure read-only discovery
Tag	7–14	DLP scan run; pii_category applied to detected columns	pii_reviewed = 'unknown' fail-safe on unclassified
Enforce	14–30	Masking policies bound to tags	Role-based; PII_ADMIN can override
Audit	30–60	access_audit view; nightly aggregation	Regulator-facing report

The rollout is sequential because tags propagate through clones and views — attaching them to the base table means every downstream consumer inherits the masking without additional work. The regulator gets the audit view; the analyst gets the deterministic hash; the CS agent gets clear text; nobody else sees plaintext. The 60-day plan is deliverable because tag-driven policy propagation is the single lever that makes a 400-table warehouse tractable.

Output:

Metric	Day 0	Day 60
Tables catalogued	0	400
Columns with pii_category tag	0	~800 (typical 2/table)
Masking policies live	0	6 (one per category)
Roles configured	ad-hoc	4 (admin, agent, analyst, restricted)
Access audit lag	none	daily

Why this works — concept by concept:

• Tag-driven policy propagation — attaching a masking policy to a tag rather than a column makes the change O(number of tags) instead of O(number of columns). One ALTER TAG covers 800 columns. Without this, the 60-day plan is impossible.
• Restrict-by-default fail-safe — the pii_reviewed = 'unknown' state defaults to a NULL-returning masking policy. A new column is read-locked until reviewed; drift cannot silently leak PII.
• Role-based enforcement — CURRENT_ROLE() in the masking policy is the enforcement primitive. Three tiers (admin / hashed / masked) cover 95% of use cases; adding a fourth is one CASE branch.
• ACCESS_HISTORY audit — the regulator's proof-of-control artefact. Every read is logged with the caller and the masked/unmasked columns; the nightly view materialises the answer to "who read what."
• Cost — Snowflake masking policies add ~1 ms per row per masked column. On a 100M-row query touching 3 masked columns, that's ~5 minutes of policy CPU — negligible against the query IO. The plan is cheap; the alternative (breach + fine) is not.

SQL
Topic — sql
SQL PII-masking and access-control problems

Practice →

Design Topic — design Design problems on privacy-first data platforms

Practice →

---

2. PII detection — DLP scanners + regex + ML classifiers

Three layers of pii detection in series — DLP for the obvious, regex for the cheap first pass, ML for the free-text hide-outs

The mental model in one line: a production pii scanner is not one thing — it is a three-layer stack where a managed DLP service catches the well-formatted PII in typed columns, a regex + column-name heuristic catches the cheap majority for pennies, and an ML classifier finds email addresses buried inside free-text notes columns that the first two layers miss. Any team that ships only one layer will discover the gap on the day a regulator asks "how do you find PII inside JSON payloads?"

Layer 1 — the managed DLP service.

• GCP Cloud DLP. Scans BigQuery tables, GCS buckets, and DataStore for a preset library of 150+ InfoTypes (EMAIL_ADDRESS, US_SOCIAL_SECURITY_NUMBER, CREDIT_CARD_NUMBER, PERSON_NAME, ...). Returns confidence levels (VERY_LIKELY, LIKELY, POSSIBLE, UNLIKELY, VERY_UNLIKELY). Prices per byte scanned; discovery runs are cheap when scoped to a sample.
• AWS Macie. Scans S3 buckets on a schedule. Detects a smaller but overlapping InfoType list. Integrates with Amazon EventBridge for alerting. Best when your data already lives in S3 in Parquet, CSV, or JSON.
• Azure Purview. Data catalog + DLP. Purview registers your data estate, runs scans, and applies classification labels (Confidential, Highly Confidential). Integrates with Azure Synapse for column-level access control based on Purview labels.
• Snowflake native. Snowflake's SYSTEM$CLASSIFY (2024+) runs a lightweight PII scan inside the warehouse itself; no external service. Best for teams already all-in on Snowflake.
• When to reach for DLP. Every landing zone. It's the highest-recall layer for the well-formatted categories (email, SSN, credit card, phone) and requires no engineering work per column. The trade-off is cost — full-table scans are expensive at scale.

Layer 2 — the regex + column-name heuristic pass.

• The mental model. Cheap first pass that catches the majority for pennies. Column-name matching is O(schema) and free; regex value scanning is O(rows) but blazingly fast in-warehouse.
• Column-name patterns. LOWER(col_name) LIKE '%email%' catches email, email_address, contact_email, user_email. Similar for phone, ssn, zip, dob. High precision, moderate recall — misses contact columns that actually hold emails.
• Value regex patterns. Standard patterns for email, phone, SSN, credit card. Email: [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}. Run against the column values (sampling is fine for cheap discovery).
• False-positive management. A product_id column containing USR-42@BAT-99 matches the email regex. Combine value regex with column-name context: email regex match AND column_name LIKE '%email%' = high confidence; email regex match AND column_name NOT LIKE '%email%' = flag for human review.
• When to reach for regex. Every pipeline. It's the cheapest layer and gives you 80% coverage before DLP ever runs. Ship it in the ingestion job so PII is tagged on write, not discovered on a nightly scan.

Layer 3 — the ML free-text classifier.

• The problem regex can't solve. A notes column with the value "Customer emailed us at [e-mail] about billing" — the regex catches the email, but a column like "Customer complained about slow service" has no email and no regex match. The problem is any free-text column might contain PII depending on the row.
• The ML approach. A small NER (Named Entity Recognition) model runs on every value in a free-text column. Modern lightweight models (spaCy's en_core_web_sm, HuggingFace dslim/bert-base-NER) tag entities like PERSON, EMAIL, PHONE, LOCATION, ORGANIZATION. Google DLP's custom infoTypes and AWS Comprehend PII Detection wrap this into a managed API.
• The output. Per-row PII flags: {"has_email": true, "has_name": false, "has_phone": true}. Aggregated at the column level: "column notes contains PII in 42% of rows."
• Confidence & sampling. ML classifiers are slow (~10 ms/row for a small model, faster with batching). Sample 10k rows for discovery; run per-row inference only if the sample shows > 5% PII rate and the compliance policy requires per-row masking.
• When to reach for ML. Any free-text column — notes, description, comments, feedback, chat_transcript, email_body. If regex + DLP say "no PII" but the column is text, run ML anyway. This is the most-missed layer.

Confidence & false-positive management — the four buckets.

• VERY_LIKELY. Multiple layers agree; column-name matches; value regex matches; ML tags. Apply the strictest masking policy. No human review needed.
• LIKELY. Two layers agree. Apply masking; queue for human review at low priority.
• POSSIBLE. One layer flagged, others silent. Human review required; masking applied provisionally until reviewed.
• UNLIKELY / VERY_UNLIKELY. Only one weak signal. Log for future audit; no immediate action.

Scanning cadence — three windows.

• On-ingest. Regex + column-name run at write time. Cheapest; catches most known PII immediately.
• Daily. DLP scan of new partitions only. Cost-controlled; catches drift within 24 hours.
• Weekly / monthly. Full-warehouse ML scan of free-text columns. Expensive; catches long-tail cases.

Common interview probes on detection.

• "How would you find PII inside a free-text notes column?" — must mention ML/NER, not just regex.
• "What's the confidence-management strategy?" — must cite multi-layer agreement and human-review queues.
• "How do you scan a 100 TB landing zone without paying $10k/day for DLP?" — sample-first, then targeted full scan on flagged tables.
• "What's a false positive you've seen and how did you handle it?" — real interviewers want a story, not just the theory.

Worked example — three-layer detection over an S3 landing zone

Detailed explanation. A team lands ~2 TB / day of nested JSON into an S3 landing zone. The columns include obvious PII (email, phone), non-obvious (user_agent with device fingerprint), and free-text (support_ticket_body). Design the three-layer scanner, its cost, and the tagging output. The wrong answer is "run Macie on the whole landing zone every night" — that would cost thousands per day.

• Layer 1 — AWS Macie. Scheduled scan of new partitions only. Cost ~$1 / GB scanned; scoping to new data brings this from $2000/day to ~$50/day.
• Layer 2 — regex + column-name in the ingest job. Runs in Glue / Spark on write. Cost is negligible (bundled with the ingest CPU).
• Layer 3 — Comprehend PII on free-text columns only. Runs on a sample of new rows in the free-text columns identified by layers 1 and 2.

Question. Produce the three layers as code (or config), with the cost math for a 2 TB/day landing zone and the tagging output shape.

Input.

Layer	Runs on	Frequency	Cost model	Coverage
1: Macie	new partitions	daily	$1 / GB	typed columns
2: regex + col-name	every row	ingest-time	negligible	obvious patterns
3: Comprehend PII	free-text sample	daily	$0.0001 / unit (100 chars)	free-text

Code.

# ingest_job.py — layer 2 (regex + column-name) runs inline
import re
from pyspark.sql import functions as F

EMAIL_RE = r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"
PHONE_RE = r"(\+?\d{1,3}[- ]?)?\(?\d{3}\)?[- ]?\d{3}[- ]?\d{4}"
SSN_RE   = r"\d{3}-\d{2}-\d{4}"

def col_name_signals(colname: str) -> dict:
    lc = colname.lower()
    return {
        "email_by_name": "email" in lc or "e_mail" in lc,
        "phone_by_name": "phone" in lc or "mobile" in lc or "tel" in lc,
        "ssn_by_name":   "ssn" in lc or "social" in lc,
    }

def value_signals_udf(colname: str):
    def _f(v):
        if v is None: return None
        flags = []
        if re.search(EMAIL_RE, v): flags.append("email")
        if re.search(PHONE_RE, v): flags.append("phone")
        if re.search(SSN_RE, v):   flags.append("ssn")
        return "|".join(flags) if flags else None
    return F.udf(_f)

# Apply to a landing DataFrame
df = spark.read.json("s3://landing/raw/2026/07/04/")
for c in df.columns:
    sig = col_name_signals(c)
    df = df.withColumn(f"_pii_{c}",
        F.when(F.col(c).isNotNull(), value_signals_udf(c)(F.col(c))))
# The _pii_<colname> flags feed the tagging pipeline

# macie_scan.py — layer 1 (managed DLP), scoped to new partitions only
import boto3

macie = boto3.client("macie2", region_name="us-east-1")

response = macie.create_classification_job(
    jobType="ONE_TIME",
    s3JobDefinition={
        "bucketDefinitions": [{"accountId":"123","buckets":["landing-raw"]}],
        "scoping": {
            "includes": {"and":[{"simpleScopeTerm":{
                "comparator":"GT",
                "key":"OBJECT_LAST_MODIFIED_DATE",
                "values":["2026-07-03T00:00:00Z"]
            }}]}
        }
    },
    samplingPercentage=100,        # full new partition
    name=f"pii-scan-{date.today()}",
)

# comprehend_pii.py — layer 3 (ML NER on free-text), 1000-row sample
import boto3

comp = boto3.client("comprehend", region_name="us-east-1")

def scan_free_text_column(rows):
    hits = 0
    for r in rows:
        if not r: continue
        resp = comp.detect_pii_entities(Text=r[:5000], LanguageCode="en")
        if resp["Entities"]:
            hits += 1
    return hits / max(len(rows), 1)

# Sample 1000 rows per free-text column identified by layer 2
sample = df.select("support_ticket_body").sample(0.001).limit(1000).collect()
pii_rate = scan_free_text_column([r["support_ticket_body"] for r in sample])
if pii_rate > 0.05:
    tag_column("support_ticket_body", "notes-mixed", confidence="LIKELY")

Step-by-step explanation.

1. Layer 2 (regex + column-name) runs inline in the Spark ingest job. For each column, col_name_signals sets three boolean flags based on the column name; value_signals_udf runs a per-row regex check and returns a bar-delimited list of matches. The output is a _pii_<colname> sidecar column that downstream steps use to auto-tag.
2. Layer 1 (Macie) runs as a scheduled create_classification_job scoped to new S3 objects only (OBJECT_LAST_MODIFIED_DATE > yesterday). Full-bucket scans would cost $2000/day; scoping to new data cuts this to ~$50/day for a 2 TB/day landing zone.
3. Layer 3 (Comprehend PII) runs only on columns flagged as free-text by layers 1 and 2. Sampling 1000 rows per column is enough to estimate the PII rate; if the rate exceeds 5%, tag the entire column as notes-mixed.
4. The tagging pipeline (not shown) reads the outputs of all three layers, computes the confidence bucket (VERY_LIKELY when all three agree, etc.), and calls ALTER TABLE ... SET TAG in Snowflake or the equivalent in the destination warehouse.
5. Cost math: $50 (Macie) + $0 (regex — bundled) + $20 (Comprehend sample of ~1000 rows × 20 free-text columns) = ~$70/day. The full-scan alternative (Macie on 2 TB × $1/GB) would be $2000/day. The three-layer approach is 30× cheaper.

Output.

Layer	Runtime	Cost / day	Coverage	Confidence contribution
1: Macie	30 min	$50	typed columns	high on obvious PII
2: regex + col-name	inline (2 min)	$0 (bundled)	patterned values	moderate; contextual
3: Comprehend PII	5 min per col	$20	free-text	high on notes
Combined	~40 min	~$70	all columns	multi-signal confidence

Rule of thumb. Never run DLP on the whole warehouse. Always scope Macie / Cloud DLP to new partitions; run regex inline in the ingest job for free; run ML only on free-text columns identified by the other two. The three-layer scoping cuts detection cost by 30× versus naïve full-scan.

Worked example — false positives from a "product_id" column

Detailed explanation. A column product_id contains values like USR-42@BAT-99 — the regex email pattern matches (@ present, plausible characters) but no real email exists. The naive scanner tags the column email, applies the masking policy, and analytics dashboards break because product_id disappears. The senior fix uses column-name context to require both value regex and column-name affinity, plus a per-column confidence score.

• The bad match. USR-42@BAT-99 — matches [A-Z0-9]+@[A-Z0-9-]+\.[A-Z]{2,}? Actually no — no dot. But [A-Z0-9]+@[A-Z0-9.-]+ variants do match. The point stands: the regex has false positives.
• The tell. product_id column name has no email/phone/mail/contact substring. Confidence should be low.
• The fix. Confidence = f(value_match_rate, column_name_match, DLP_agreement). Require ≥ 2 signals for VERY_LIKELY.

Question. Design the confidence-scoring function such that a product_id column with 90% "email-like" values but no column-name match is flagged for review, not auto-tagged as PII.

Input.

Column	Value match rate	Column-name match	DLP tag	Expected confidence
customer_email	99%	yes	LIKELY	VERY_LIKELY
product_id	90%	no	UNLIKELY	POSSIBLE (review)
free_text_notes	5%	no	POSSIBLE	POSSIBLE (review)
description	0%	no	UNLIKELY	UNLIKELY

Code.

# confidence.py — multi-signal PII confidence scoring
from enum import Enum

class Conf(Enum):
    VERY_LIKELY = 4
    LIKELY      = 3
    POSSIBLE    = 2
    UNLIKELY    = 1
    NONE        = 0

DLP_MAP = {
    "VERY_LIKELY": 2,
    "LIKELY":      2,
    "POSSIBLE":    1,
    "UNLIKELY":    0,
    "VERY_UNLIKELY":0,
}

def score(value_match_rate: float, name_match: bool, dlp_tag: str) -> Conf:
    signal_count = 0
    if value_match_rate > 0.5:  signal_count += 1
    if name_match:              signal_count += 1
    signal_count += DLP_MAP.get(dlp_tag, 0)

    if signal_count >= 3:  return Conf.VERY_LIKELY
    if signal_count == 2:  return Conf.LIKELY
    if signal_count == 1:  return Conf.POSSIBLE
    return Conf.NONE

# Route by confidence
def route(colname, conf: Conf):
    if conf == Conf.VERY_LIKELY:
        auto_apply_masking(colname, category="email")
    elif conf == Conf.LIKELY:
        auto_apply_masking(colname, category="email")
        queue_for_review(colname, priority="low")
    elif conf == Conf.POSSIBLE:
        provisional_mask(colname)                # NULL until reviewed
        queue_for_review(colname, priority="high")
    else:
        log_for_audit(colname)                    # no action

# Examples
score(0.99, True,  "LIKELY")    # VERY_LIKELY → auto-apply
score(0.90, False, "UNLIKELY")  # POSSIBLE → review
score(0.05, False, "POSSIBLE")  # POSSIBLE → review
score(0.00, False, "UNLIKELY")  # NONE → log only

Step-by-step explanation.

1. The score function counts three signal sources: (a) value regex match rate above 50%, (b) column-name affinity, (c) DLP tag strength (mapped to 0/1/2). Sum gives a signal count; buckets map to the four confidence levels.
2. For customer_email (99% + name + DLP=LIKELY) → 1 + 1 + 2 = 4 → VERY_LIKELY. Auto-apply masking; no human review.
3. For product_id (90% + no name + DLP=UNLIKELY) → 1 + 0 + 0 = 1 → POSSIBLE. Provisional mask (returns NULL until reviewed), high-priority human review. A real product analyst sees the mask, escalates, and marks the column non-PII within hours.
4. The route function is the action policy: high confidence → auto-apply; medium → apply + review; low → provisional mask + urgent review; none → log only.
5. The critical property: the provisional mask on POSSIBLE means no PII ever leaks even if the confidence is wrong. The cost is a temporary read-lock on some non-PII columns — annoying but recoverable within a review-response SLA.

Output.

Column	signal count	Confidence	Action
customer_email	4	VERY_LIKELY	auto-apply
product_id	1	POSSIBLE	provisional mask + urgent review
free_text_notes	2	LIKELY	apply + low-priority review
description	0	NONE	log only

Rule of thumb. Confidence is a sum, not a single signal. Require multi-layer agreement for auto-apply; use provisional masks + human review for medium confidence. The false-positive story is what separates "we ran the scanner" from "we have a working detection pipeline."

Worked example — ML classifier on free-text with sampling

Detailed explanation. A support_ticket_body column has 500M rows. Running Comprehend PII on every row would cost ~$50k (500M × 100 chars × $0.0001 per 100 chars). Sampling 10k rows costs $1 and answers "does this column contain PII?" with narrow enough confidence intervals to make a masking decision.

• The full scan cost. 500M rows × 5000 chars average × $0.0001 / 100 chars = $250k. Cost-prohibitive.
• The sample cost. 10k rows × 5000 chars × $0.0001 / 100 chars = $5. Trivial.
• The confidence. A 10k-row sample gives 95% confidence intervals of ~±1% on the PII rate. Sufficient for "column contains PII" vs "column does not."

Question. Design the sampling procedure, run the classifier, and produce a per-column PII-rate report with confidence bounds.

Input.

Column	Row count	Sample size	Classifier
support_ticket_body	500M	10k	AWS Comprehend detect_pii_entities
description	20M	5k	AWS Comprehend detect_pii_entities
notes	8M	5k	AWS Comprehend detect_pii_entities

Code.

# free_text_scan.py — sample + classify free-text columns
import boto3, random
from statistics import mean
from math import sqrt

comp = boto3.client("comprehend", region_name="us-east-1")

def classify(text: str) -> dict:
    if not text: return {}
    resp = comp.detect_pii_entities(Text=text[:5000], LanguageCode="en")
    types = {ent["Type"] for ent in resp["Entities"]}
    return types

def sample_and_report(rows, colname: str) -> dict:
    hits, categories = 0, {}
    for r in rows:
        found = classify(r)
        if found:
            hits += 1
            for cat in found:
                categories[cat] = categories.get(cat, 0) + 1
    rate = hits / len(rows)
    # 95% CI (normal approx) for a Bernoulli sample
    se = sqrt(rate * (1 - rate) / len(rows))
    ci_low, ci_hi = max(0, rate - 1.96*se), min(1, rate + 1.96*se)
    return {
        "column": colname,
        "sample_size": len(rows),
        "pii_rate": rate,
        "ci_low": ci_low,
        "ci_hi": ci_hi,
        "categories": categories,
    }

# Example use — pull sample via SQL
sample_sql = """
SELECT support_ticket_body
FROM   tickets
SAMPLE (10000 ROWS)
"""
rows = [r[0] for r in sf.cursor().execute(sample_sql).fetchall()]
report = sample_and_report(rows, "support_ticket_body")

print(report)
# {'column': 'support_ticket_body', 'sample_size': 10000, 'pii_rate': 0.084,
#  'ci_low': 0.079, 'ci_hi': 0.089,
#  'categories': {'EMAIL': 512, 'PHONE': 218, 'NAME': 154, 'ADDRESS': 22}}

Step-by-step explanation.

1. SAMPLE (10000 ROWS) in Snowflake is O(1) — the sample is drawn without a full scan. Every row has an equal probability of selection; the result is a representative sample.
2. For each sampled row, detect_pii_entities returns the PII categories found. A row is a "hit" if any category is detected; the aggregate hit rate estimates the column-level PII rate.
3. The confidence interval uses the normal approximation to the Bernoulli standard error: SE = sqrt(p(1-p)/n). At n=10k, SE < 0.005, giving a ±1% margin — precise enough for policy decisions.
4. The categories dict shows which PII types are present. In this example, 5.1% of rows contain emails, 2.2% contain phones, 1.5% contain names — the column has diverse PII, so a redact policy is warranted (masking individual categories inline is expensive).
5. The report drives the tagging decision: pii_rate > 5% → tag as notes-mixed and apply redact-for-non-admin masking. pii_rate < 1% → tag as low-risk, monitor but no automatic mask.

Output.

Column	Sample	PII rate	95% CI	Categories	Decision
support_ticket_body	10k	8.4%	7.9%–8.9%	email, phone, name, address	mask (notes-mixed)
description	5k	0.4%	0.2%–0.6%	address only	monitor
notes	5k	12.1%	11.2%–13.0%	email, name, phone	mask (notes-mixed)

Rule of thumb. Never run per-row ML on billions of rows for discovery. Sample 5k–10k rows per column, compute the PII rate with a confidence interval, and let the rate drive the tag. Per-row ML is only justified at query time when the masking policy needs value-level decisions.

Senior interview question on PII detection at scale

A senior interviewer might ask: "You inherit a 10 TB Snowflake warehouse with 200 free-text columns and no PII inventory. Walk me through the detection pipeline, cost math, and how you'd stage the rollout so the first result lands within 48 hours."

Solution Using a three-layer scanner with sample-first economics

# detection_pipeline.py — 48-hour rollout
# Hour 0-6  → column-name + regex scan (in-warehouse SQL only)
# Hour 6-24 → Snowflake SYSTEM$CLASSIFY on flagged columns
# Hour 24-48 → Comprehend PII sample on free-text columns

import snowflake.connector, boto3, re
from statistics import mean

sf = snowflake.connector.connect(user="privacy_bot", account="acme")

# --- Hour 0-6 : regex + column-name over ALL columns ---
sql = """
WITH cols AS (
  SELECT table_catalog, table_schema, table_name, column_name, data_type
  FROM   snowflake.account_usage.columns
  WHERE  deleted IS NULL AND data_type IN ('TEXT','VARCHAR','STRING')
)
SELECT c.*,
       CASE WHEN LOWER(column_name) RLIKE '.*(email|e_mail|phone|mobile|ssn|dob|zip).*' THEN 1 ELSE 0 END AS name_hit
FROM   cols c;
"""
name_hits = sf.cursor().execute(sql).fetchall()

# --- Hour 6-24 : SYSTEM$CLASSIFY on flagged tables ---
for db, schema, table, _, _, hit in name_hits:
    if hit:
        classify_sql = f"CALL SYSTEM$CLASSIFY('{db}.{schema}.{table}', {{}})"
        sf.cursor().execute(classify_sql)

# --- Hour 24-48 : Comprehend sample on free-text columns ---
comp = boto3.client("comprehend", region_name="us-east-1")
free_text_cols = [c for c in name_hits if is_free_text(c)]

for c in free_text_cols:
    sample_sql = f"SELECT {c.column_name} FROM {c.full_name} SAMPLE (10000 ROWS)"
    rows       = [r[0] for r in sf.cursor().execute(sample_sql).fetchall() if r[0]]
    pii_rate   = ml_classify_rate(comp, rows)
    if pii_rate > 0.05:
        sf.cursor().execute(
          f"ALTER TABLE {c.full_name} MODIFY COLUMN {c.column_name} "
          f"SET TAG pii_category = 'notes-mixed'"
        )

Step-by-step trace.

Phase	Runs on	Cost	Coverage	Outputs
Regex + column-name	all cols, in-warehouse	~$0	80% of obvious PII	tags: email/phone/ssn/name
SYSTEM$CLASSIFY	flagged tables only	~$100	typed columns w/ high recall	tags refined with confidence
Comprehend PII sample	free-text cols only	~$50	free-text hide-outs	tags: notes-mixed

The 48-hour plan lands the first inventory result within 6 hours (regex + column-name only, in-warehouse, no external cost), full classification within 24 hours, and the free-text sample within 48 hours. Each phase feeds tags into the pii_category taxonomy that the masking policies attach to.

Output:

Phase	Column tags applied	Cost	Confidence
Hour 6	~120 columns (typed)	~$0	POSSIBLE
Hour 24	~180 columns (refined)	~$100	LIKELY
Hour 48	~200 columns (free-text)	~$50	LIKELY on notes-mixed

Why this works — concept by concept:

• Sample-first economics — running ML on 500M rows per column would cost $250k+; running on 10k-row samples costs $5 and answers the "is this column PII?" question with ±1% precision. Sample-first is 50000× cheaper than naïve full-scan.
• Sequential layers, cheapest first — the free layer (regex + column-name) catches 80% of PII before any managed service runs. The medium-cost layer (SYSTEM$CLASSIFY) refines what regex missed. The expensive layer (Comprehend) runs only on free-text.
• Restrict-by-default — every un-tagged column defaults to pii_reviewed = 'unknown' with a NULL-returning masking policy. The 48-hour rollout can miss a column and the platform stays safe.
• Confidence-driven action — VERY_LIKELY auto-applies masking; POSSIBLE queues for review with a provisional mask. Nothing auto-applies on weak signals.
• Cost — $150 total for the 10 TB warehouse detection. The full-scan alternative is $250k+. The three-layer + sample-first design is what makes the 48-hour timeline economically viable.

SQL
Topic — sql
SQL PII-inventory and classification problems

Practice →

ETL Topic — etl ETL problems on landing-zone PII scanning

Practice →

---

3. Tokenization — replace values with opaque tokens

tokenization replaces a plaintext value with an opaque token that has no algorithmic relationship to the original — vault-based or deterministic, always join-safe

The mental model in one line: tokenization replaces [e-mail] with tok_a1b2c3d4 — a random opaque string that reveals nothing about the original value — and stores the mapping either in a vault (looked up by API) or via a deterministic hash (same input always produces the same token). Encryption reveals structure through ciphertext length and keys; tokenization reveals nothing beyond the token itself.

Tokenization vs encryption — the crucial difference.

• Encryption. A cryptographic function transforms plaintext into ciphertext using a key. The ciphertext is derivable from the plaintext with the key; reversible with the key. If the key is compromised, the entire universe of ciphertext is compromised. The ciphertext often reveals length (padding aside) and format.
• Tokenization. A random token is assigned to plaintext and the mapping is stored (in a vault) or generated deterministically (from a keyed hash). The token itself carries no algebraic relationship to the plaintext — even with full access to the tokenization service, an adversary cannot derive plaintext from a stolen token database without the underlying vault or the hash key.
• Practical implication. A tokenized email column that leaks (through a warehouse breach) is worthless to the attacker without vault access. An encrypted column that leaks is worthless until the key leaks — and keys are the hardest thing to keep secret.
• PCI-DSS advantage. PCI-DSS treats a tokenized credit-card number as out of PCI scope (if the vault is properly isolated). An encrypted credit-card number stays in PCI scope (the encryption keys are within the audit boundary).

Vault-based tokenization — the safe default.

• The vault. A separate service (in-house or managed — Skyflow, Very Good Security, Basis Theory) that holds the plaintext ↔ token mapping. The vault is the only place plaintext exists.
• Write path. The application calls vault.tokenize("[e-mail]") → the vault stores plaintext, generates a random tok_a1b2c3d4, returns it. The application stores the token in the warehouse; plaintext never touches the warehouse.
• Read path. Only authorised roles can call vault.detokenize("tok_a1b2c3d4") to retrieve plaintext. All other roles see the token and cannot reverse it.
• The isolation win. The vault has a smaller attack surface (one service, hardened, audited). The warehouse can be replicated widely without leaking plaintext. Backups of the warehouse are safe.
• The cost. Every read of plaintext is an API call to the vault. For high-throughput use cases (per-row detokenization during batch processing), the vault must scale. For low-throughput use cases (CS agent looks up one customer), the vault is trivially cheap.

Deterministic tokenization — same input, same token.

• The property. tokenize("[e-mail]") always returns tok_a1b2c3d4. Two different call sites with the same input produce the same token. Two different inputs produce two different tokens.
• Implementation. HMAC-SHA-256 with a secret key: tok = base64(HMAC-SHA-256(secret, plaintext))[:16]. The secret lives in KMS; the tokenizer runs anywhere.
• The join win. Same email in two different tables produces the same token, so joins on the tokenized column still work: SELECT * FROM orders o JOIN customers c ON o.email_tok = c.email_tok.
• The trade-off vs vault. Deterministic tokens are inferentially weak — an attacker with a candidate email can HMAC it themselves and check whether the token appears in the warehouse (frequency-analysis attack). Vault-based tokens don't have this problem because the token is random.
• When to use. Analytics that need join preservation on a tokenized column; internal environments where the risk of frequency analysis is acceptable.

The join-safety property — why tokenization beats encryption for warehouse analytics.

• Encryption's problem. Standard AES encryption produces different ciphertexts on repeated calls (semantic security via random IVs). Encrypting [e-mail] twice produces two different ciphertexts; the values won't join.
• Deterministic encryption. Some encryption schemes (AES-SIV, deterministic mode) produce the same ciphertext for the same plaintext — but they are weakly semantic and leak equality (an attacker can see which rows have the same email even without decrypting).
• Deterministic tokenization. Same property (equality-preserving), but the leak is bounded by the token space (2^128 for a 16-byte token), and the token is random so no plaintext structure leaks.
• The senior signal. In an interview, describing the join-preservation trade-off across encryption / deterministic encryption / deterministic tokenization is the exact answer senior interviewers listen for.

When to reach for tokenization.

• PCI-DSS scope reduction. Credit-card numbers → tokens; the warehouse leaves PCI scope entirely.
• Cross-source stitching. Same email tokenized identically across CRM + product analytics + marketing → tokens can join without exposing plaintext.
• GDPR pseudonymisation. Tokenized customer_id + vault separately held = Article 25 pseudonymisation.
• When not to use. Very small deployments where the vault operational cost outweighs the compliance benefit; use column-level masking policies instead.

Common interview probes on tokenization.

• "Explain tokenization vs encryption." — must cite the algebraic-relationship distinction and the join-safety property.
• "How does deterministic tokenization affect joins?" — must cite equality preservation.
• "PCI-DSS scope for a tokenized credit-card column?" — out of scope if the vault is properly isolated.
• "What's the frequency-analysis risk of deterministic tokenization?" — an adversary with a candidate value can HMAC it and probe the warehouse.

Worked example — vault-based tokenization of email at ingest

Detailed explanation. A stream of customer signup events lands via Kafka. Each event has an email field. Before the event is persisted to the Snowflake landing table, the ingest job calls the tokenization vault to swap the email for an opaque token. The warehouse never sees plaintext; the vault holds the only mapping. When customer support needs to look up a customer by email, they call the vault with the token to retrieve plaintext.

• The trigger. Customer signup event arrives in Kafka with email: "[e-mail]".
• The tokenize call. Ingest job calls POST /tokenize {"value":"[e-mail]","type":"email"}; vault returns {"token":"tok_a1b2c3d4"}.
• The persistence. Ingest job writes INSERT INTO landing.signups (customer_id, email_tok, ...) VALUES (?, ?, ...); plaintext never touches Snowflake.

Question. Implement the ingest pipeline (Kafka consumer → vault → Snowflake) with a fail-safe on vault errors, and the CS-agent detokenize UI. Cite the failure modes explicitly.

Input.

Component	Value
Kafka topic	signup_events
Vault URL	https://vault.internal/tokenize
Warehouse table	landing.signups (email_tok STRING)
CS agent role	cs_agent (can detokenize)
Analyst role	analyst (cannot detokenize)

Code.

# ingest.py — Kafka consumer with vault tokenization
import json, requests
from kafka import KafkaConsumer
import snowflake.connector

VAULT_URL = "https://vault.internal"
consumer  = KafkaConsumer("signup_events",
                          bootstrap_servers="kafka.internal:9092",
                          value_deserializer=lambda v: json.loads(v))
sf        = snowflake.connector.connect(user="ingest_bot", account="acme")

class VaultError(Exception): pass

def tokenize(value: str, kind: str = "email") -> str:
    resp = requests.post(f"{VAULT_URL}/tokenize",
                         json={"value": value, "type": kind},
                         timeout=1.5)
    if resp.status_code != 200:
        raise VaultError(f"vault error: {resp.status_code}")
    return resp.json()["token"]

for msg in consumer:
    event = msg.value
    try:
        email_tok = tokenize(event["email"], "email")
    except VaultError:
        # Fail-safe — never insert plaintext; DLQ the event
        write_to_dlq(event, reason="vault_unreachable")
        continue

    sf.cursor().execute(
      "INSERT INTO landing.signups (customer_id, email_tok, signup_ts) VALUES (%s, %s, %s)",
      (event["customer_id"], email_tok, event["ts"])
    )
    consumer.commit()

# cs_ui.py — CS agent looks up by email (vault-mediated)
def cs_lookup_by_email(email: str, agent_role: str):
    if agent_role != "cs_agent":
        raise PermissionError("only cs_agent can detokenize")
    email_tok = tokenize(email)                      # deterministic: same input → same token
    row = sf.cursor().execute(
      "SELECT customer_id, signup_ts FROM landing.signups WHERE email_tok = %s",
      (email_tok,)
    ).fetchone()
    return row

-- Verifying no plaintext reaches Snowflake
SELECT * FROM landing.signups LIMIT 3;
-- customer_id | email_tok       | signup_ts
-- 1           | tok_a1b2c3d4    | 2026-07-04 10:00:00
-- 2           | tok_e5f6g7h8    | 2026-07-04 10:01:00
-- 3           | tok_i9j0k1l2    | 2026-07-04 10:02:00

Step-by-step explanation.

1. The Kafka consumer reads each signup event. Before any Snowflake write, the ingest job calls the vault to tokenize the email. This is the critical ordering — plaintext must never appear in the Snowflake write.
2. The vault call is HTTP with a 1.5-second timeout. Vault errors trigger the fail-safe: the event goes to a dead-letter queue with a reason. The DLQ never contains plaintext either — only the original Kafka message which is on a topic with its own retention policy.
3. The Snowflake insert stores only (customer_id, email_tok, signup_ts). The warehouse holds a token, not an email. Even a full-warehouse dump reveals no email addresses.
4. The CS lookup flow uses deterministic tokenization: tokenizing "[e-mail]" always produces tok_a1b2c3d4. So the CS agent can search by plaintext email, receive a token, and query Snowflake for the matching row — without ever needing to detokenize anything.
5. The role check (agent_role != "cs_agent") is enforced before the vault call. This is the least-privilege principle: only authorised roles can tokenize (which reveals whether a given email exists in the warehouse via lookup timing). In production, the vault itself enforces this via JWT-bearer tokens; the Python check is defence in depth.

Output.

Component	Plaintext exposure	Detokenize possible?
Kafka topic	yes (retention 7 days)	n/a
Ingest process memory	yes (transient)	n/a
Snowflake landing	NO	via vault + role
Snowflake analyst view	NO	no
CS agent UI	via vault call	yes
Data warehouse backup	NO	no

Rule of thumb. Tokenize at ingest, not later. Once plaintext lands in the warehouse, every downstream copy — materialised views, backups, replicas, dev clones — inherits the plaintext. Ingest-time tokenization means the warehouse cannot leak PII by construction.

Worked example — deterministic tokenization for join-safe analytics

Detailed explanation. An analyst needs to join orders.customer_email with campaigns.recipient_email to compute campaign attribution. Both columns are tokenized; both used the same deterministic scheme. The join works on the tokens because deterministic tokenization is equality-preserving. The analyst never sees plaintext; the warehouse remains PII-free.

• The requirement. Attribution by matching customer emails across two systems.
• The constraint. Neither system may hold plaintext; analytics must run without a vault call per row.
• The mechanism. HMAC-SHA-256 with a shared secret key stored in KMS; both systems compute email_tok = HMAC(secret, email) at ingest.

Question. Implement the deterministic tokenizer, show the SQL join, and explain the frequency-analysis risk.

Input.

Component	Value
Algorithm	HMAC-SHA-256 truncated to 128 bits, base64
Key store	AWS KMS
Key	arn:aws:kms:us-east-1:...:key/pii-token-hmac
Tables	orders, campaigns
Join key	email (tokenized to email_tok)

Code.

# det_token.py — deterministic tokenizer via HMAC + KMS
import boto3, hmac, hashlib, base64
from functools import lru_cache

kms = boto3.client("kms", region_name="us-east-1")

@lru_cache(maxsize=1)
def load_secret() -> bytes:
    resp = kms.decrypt(CiphertextBlob=open("/etc/pii/hmac.key.enc", "rb").read())
    return resp["Plaintext"]

def det_tokenize(value: str) -> str:
    if not value: return None
    key   = load_secret()
    mac   = hmac.new(key, value.lower().encode("utf-8"), hashlib.sha256).digest()
    return "tok_" + base64.urlsafe_b64encode(mac[:16]).decode("ascii").rstrip("=")

# Same value → same token
det_tokenize("[e-mail]")   # "tok_QjIzYWJjZDEyMzRlZjU2"
det_tokenize("[e-mail]")   # "tok_QjIzYWJjZDEyMzRlZjU2" (case-normalised)
det_tokenize("[e-mail]")     # "tok_XYzabcQjIzYWJjZDEy" (different)

-- Both tables use deterministic tokenization at ingest
-- orders.customer_email_tok  ← det_tokenize(customer_email)
-- campaigns.recipient_email_tok ← det_tokenize(recipient_email)

-- Join works on tokens; no plaintext ever queried
SELECT c.campaign_id,
       COUNT(DISTINCT o.order_id)   AS attributed_orders,
       SUM(o.amount)                AS attributed_revenue
FROM   campaigns c
JOIN   orders o
  ON   o.customer_email_tok = c.recipient_email_tok
 AND   o.order_ts BETWEEN c.sent_ts AND DATEADD('day', 7, c.sent_ts)
GROUP  BY c.campaign_id
ORDER  BY attributed_revenue DESC;

Step-by-step explanation.

1. load_secret fetches the HMAC key from KMS at process start and caches it. The key is stored on disk encrypted with a KMS master key; kms.decrypt unwraps it in memory. The plaintext key never touches disk.
2. det_tokenize HMACs the (lowercase-normalised) input with SHA-256, truncates to 128 bits, base64-encodes. The output is a fixed-length opaque string. Same input always produces the same output; different inputs produce different outputs.
3. At ingest, every orders row has customer_email_tok = det_tokenize(customer_email) and the plaintext is dropped. Similarly for campaigns.recipient_email_tok. The tokenizer runs in the ingest pipeline; the warehouse never sees plaintext.
4. The analyst's join operates entirely on tokens — the SQL is unchanged from what they would have written for plaintext emails. The join is equality-preserving because deterministic tokenization is equality-preserving.
5. The frequency-analysis risk: an adversary with (a) query access to the warehouse and (b) a list of candidate emails can compute the token for each candidate and check whether it appears. Mitigations: rotate the HMAC key annually (breaks all existing tokens; requires a re-tokenize migration), restrict warehouse access, monitor for large batches of email_tok = ... queries.

Output.

Query	Plaintext exposed	Join works
SELECT customer_email FROM orders	NO (dropped at ingest)	n/a
SELECT customer_email_tok FROM orders	yes (the token)	n/a
JOIN orders o ON o.email_tok = c.email_tok	NO	YES
Attribution query	NO	YES

Rule of thumb. Deterministic tokenization is the sweet spot for warehouse analytics: joins work, plaintext never appears, and the frequency-analysis risk is bounded by the ability of an adversary to run arbitrary queries. Rotate the HMAC key annually to bound the exposure window; use vault-based tokenization if the risk is unacceptable.

Worked example — PCI-DSS scope reduction via tokenization

Detailed explanation. A payments team stores credit-card numbers directly in an OLTP database; the entire database is in PCI-DSS scope, which is enormously expensive to audit (annual penetration test, restricted access, hardened everything). Tokenizing the card numbers via a PCI-compliant vault (Skyflow, Very Good Security) removes the warehouse and the OLTP DB from scope; only the vault remains in scope. The audit cost drops by 10×.

• The scope problem. PCI-DSS applies to any system that "stores, processes, or transmits" cardholder data. A warehouse holding card numbers is fully in scope.
• The tokenization win. Replace the card number with a token at ingest. The warehouse now holds tokens, not cardholder data. It leaves PCI scope entirely.
• The vault. A managed service (or self-hosted with FIPS-validated HSM) that holds the plaintext ↔ token mapping. The vault remains in PCI scope but is a much smaller footprint.

Question. Design the tokenization boundary between the OLTP database and the warehouse, cite what stays in scope and what leaves scope, and estimate the audit savings.

Input.

System	Before tokenization	After tokenization
OLTP orders	in PCI scope (holds PAN)	out of scope (holds token)
Warehouse	in PCI scope (holds PAN)	out of scope (holds token)
ETL pipeline	in PCI scope	out of scope (passes tokens)
Vault	did not exist	in PCI scope (small, hardened)
BI tool	in PCI scope	out of scope

Code.

# tokenize_at_gateway.py — payment gateway tokenizes at ingest
import requests
from cryptography.hazmat.primitives.ciphers.aead import AESGCM

VAULT_URL = "https://vault.pci-safe-zone.internal"

def tokenize_card(pan: str, expiry: str, cvv: str) -> dict:
    # Send plaintext PAN to the vault; vault stores it and returns a token
    resp = requests.post(
        f"{VAULT_URL}/tokenize/card",
        json={"pan": pan, "expiry": expiry},   # CVV never stored (PCI 3.2)
        headers={"Authorization": f"Bearer {get_service_token()}"},
        timeout=2.0,
    )
    resp.raise_for_status()
    return resp.json()   # {"token": "tok_card_xyz", "last4": "1234", "brand": "visa"}

# The rest of the pipeline uses the token
def store_order(order_id, customer_id, pan, expiry, cvv, amount):
    tok = tokenize_card(pan, expiry, cvv)
    # OLTP DB stores only the token + last4 + brand — safe for display
    sf.execute(
      "INSERT INTO orders (order_id, customer_id, card_tok, card_last4, card_brand, amount) "
      "VALUES (%s, %s, %s, %s, %s, %s)",
      (order_id, customer_id, tok["token"], tok["last4"], tok["brand"], amount)
    )

-- Warehouse — orders table holds only tokens
CREATE TABLE warehouse.orders (
    order_id     BIGINT      PRIMARY KEY,
    customer_id  BIGINT,
    card_tok     STRING,           -- opaque token; no cardholder data
    card_last4   STRING(4),        -- last-4 for display; not sensitive
    card_brand   STRING(16),
    amount       NUMBER(18,2),
    order_ts     TIMESTAMP
);

-- Analytics query — no cardholder data ever touched
SELECT card_brand,
       COUNT(*)      AS orders,
       SUM(amount)   AS revenue
FROM   warehouse.orders
GROUP  BY card_brand
ORDER  BY revenue DESC;

Step-by-step explanation.

1. At the payment gateway, tokenize_card sends the plaintext PAN and expiry to the vault. The vault is in PCI scope; the gateway calling the vault is briefly in scope but the exposure is bounded to the single API call. CVV is never stored (PCI-DSS 3.2.2 forbids it).
2. The OLTP database receives only card_tok, card_last4, card_brand, and amount. Last-4 is not cardholder data under PCI (safe for display). The token is opaque. The OLTP DB leaves PCI scope entirely.
3. The warehouse inherits the same shape: only tokens + last-4 + brand. Analytics runs on brand, last-4, amounts — everything a BI dashboard needs — without ever touching cardholder data.
4. If a customer needs to view their full card (rare; usually only for CS refunds), the app calls the vault to detokenize with an audit trail. This is the only path that reveals plaintext PAN.
5. The audit savings are enormous: instead of penetration-testing the entire warehouse (100+ tables, dev + staging + prod clones), the audit scope is the vault + the payment gateway. Total PCI-audit hours typically drops from ~800 to ~80 per year — 10× cheaper.

Output.

System	PCI scope before	PCI scope after	Audit hours saved / year
OLTP orders	yes	no	200
Warehouse	yes	no	400
BI + dashboards	yes	no	100
Vault	did not exist	yes	-20 (added)
Net	—	—	~680

Rule of thumb. For any workload that touches credit-card numbers, tokenization is not optional — it's the difference between "PCI compliance is a full-time engineering job" and "PCI compliance is one team's Q1 project." Ship the vault before the warehouse ever sees a PAN.

Senior interview question on tokenization

A senior interviewer might ask: "You're designing the tokenization layer for a new customer-data platform. You need vault-based tokens for credit cards and deterministic tokens for emails so analytics joins work. Walk me through the vault topology, the key management, and the operational monitoring."

Solution Using a two-tier tokenization architecture with KMS envelope encryption

# tokenizer.py — two-tier tokenization service
import boto3, hmac, hashlib, base64, requests, os
from functools import lru_cache

kms  = boto3.client("kms", region_name="us-east-1")
KMS_KEY_ID = os.environ["PII_KMS_KEY_ID"]

@lru_cache(maxsize=1)
def load_hmac_key() -> bytes:
    # Envelope-encrypted HMAC key; kms.decrypt unwraps it in memory
    return kms.decrypt(CiphertextBlob=open("/etc/pii/hmac.key.enc", "rb").read())["Plaintext"]

# --- Tier 1 — deterministic (email, phone) ---
def tokenize_det(value: str, kind: str) -> str:
    key = load_hmac_key()
    mac = hmac.new(key, f"{kind}:{value.lower()}".encode("utf-8"), hashlib.sha256).digest()
    return f"tok_{kind}_{base64.urlsafe_b64encode(mac[:16]).decode().rstrip('=')}"

# --- Tier 2 — vault-based (credit card, SSN) ---
def tokenize_vault(value: str, kind: str) -> dict:
    resp = requests.post(
      "https://vault.internal/tokenize",
      json={"value": value, "type": kind},
      headers={"Authorization": f"Bearer {get_vault_service_token()}"},
      timeout=1.5,
    )
    resp.raise_for_status()
    return resp.json()

# --- Router — pick tier by data class ---
DETERMINISTIC = {"email", "phone", "customer_id_external"}
VAULT_ONLY    = {"credit_card", "ssn", "bank_account", "passport"}

def tokenize(value: str, kind: str):
    if kind in DETERMINISTIC: return tokenize_det(value, kind)
    if kind in VAULT_ONLY:    return tokenize_vault(value, kind)
    raise ValueError(f"unknown tokenization class: {kind}")

# monitoring.yaml — the four operational alerts
alerts:
  - name: vault_latency_p99
    query: histogram_quantile(0.99, rate(vault_request_duration_seconds_bucket[5m]))
    threshold_ms: 50
    page_on_call: true

  - name: vault_error_rate
    query: rate(vault_request_errors_total[5m]) / rate(vault_request_total[5m])
    threshold: 0.01
    page_on_call: true

  - name: kms_decrypt_rate
    query: rate(kms_decrypt_total[5m])
    threshold_min: 0.001
    threshold_max: 100     # alert on absent OR runaway
    page_on_call: warn

  - name: det_token_key_age_days
    query: (time() - det_token_key_created_at_ts) / 86400
    threshold_days: 365
    page_on_call: warn      # rotation reminder

Step-by-step trace.

Data class	Tier	Vault call per row?	Join-safe?	PCI scope?
email	deterministic	no	yes	n/a (not PCI)
phone	deterministic	no	yes	n/a
credit_card	vault	yes (at ingest)	no (random token)	vault only
ssn	vault	yes (at ingest)	no	vault only
customer_id_external	deterministic	no	yes	n/a

The two-tier design balances economy vs join-safety. Deterministic tokens run in-process (no vault round-trip); vault tokens require the API call but give random opaque tokens that leave no frequency-analysis surface. Every data class maps to exactly one tier.

Output:

Component	Latency	Scale limit	Key management
Deterministic tokenizer	~10 μs / call	in-process; per-CPU	KMS envelope encryption
Vault tokenizer	5-15 ms / call	vault-scaled	vault-internal HSM
Monitoring	Prometheus scrape	—	KMS access log
Rotation	annual (det) / monthly (vault)	—	offline migration

Why this works — concept by concept:

• Two-tier routing — deterministic tokens for join-key columns (emails, phones); vault tokens for high-sensitivity columns (cards, SSNs). Each tier gets the right trade-off; the router keeps the surface small.
• KMS envelope encryption — the HMAC key on disk is encrypted with a KMS CMK. Rotating the CMK re-wraps the HMAC key; rotating the HMAC key requires a re-tokenize migration. Two-layer separation of concerns.
• Per-tier operational monitoring — latency + error rate for the vault (customer-facing SLA); rate + age for the deterministic key (rotation reminder). Every alert has a runbook.
• Join-safety only where safe — never use deterministic tokens for high-sensitivity data (frequency-analysis risk). Always use vault tokens for cards and SSNs regardless of the analytics cost.
• Cost — deterministic tokenize is ~10 μs/row (bulk-safe); vault tokenize is ~10 ms/row (batched at ingest, not query time). Envelope encryption adds ~1 ms per KMS unwrap, cached process-wide. The two-tier design is O(1) latency for the deterministic path and O(vault_rtt) only for high-sensitivity classes.

SQL
Topic — sql
SQL join-preserving tokenization problems

Practice →

Design Topic — design Design problems on vault architectures

Practice →

---

4. Format-Preserving Encryption — encrypt without changing shape

format preserving encryption (FPE / FF1 / FF3) encrypts a value into a ciphertext of the same type and length — 16-digit credit card in, 16-digit encrypted number out, analytics stays typed

The mental model in one line: FPE is a NIST-standardised family of encryption schemes (FF1 and FF3-1) that transform a plaintext into a ciphertext with the same character set and the same length — a 16-digit credit-card number encrypts to a 16-digit number, an email to an email-shaped string, a date to a date. Every downstream schema, type inference, and analytics query keeps working; the reversibility with the key preserves the round-trip.

Why FPE exists — the shape-preservation problem.

• The problem with standard encryption. AES encrypts a 16-digit credit card into a 16-byte ciphertext (128 bits) — but the ciphertext is binary, not numeric. Storing it in a CHAR(16) column requires base64 (which expands to 24 characters) or hex (32 characters). Every downstream schema breaks: type checks fail, joins fail, display renders wrong.
• The FPE fix. FPE encrypts a 16-digit input into a 16-digit output. Same length, same character set. The ciphertext still looks like a credit-card number; type checks pass; joins on the encrypted column still work (though on different values); display continues to render correctly.
• The NIST blessing. FF1 (SP 800-38G) and FF3-1 (revised after FF3 was found weak) are the NIST-approved FPE schemes. Both use AES as the underlying block cipher plus a Feistel network for shape preservation.
• The key management. Same as AES — a symmetric key wrapped by KMS. Rotating the key requires re-encrypting all data (unlike tokenization where a vault re-issue works differently).

FF1 vs FF3-1 — the two flavours.

• FF1. Feistel network with a tweakable input (extra context bits like column name); NIST-approved. Slightly slower than FF3 but more flexible. Preferred for new deployments.
• FF3-1. Redesigned after FF3 was found to have a distinguishing attack. Faster than FF1 in some implementations; more restrictive on input length (minimum ~6 characters for the domain to be secure).
• When to pick which. For most warehouse workloads, FF1 is the safer default. FF3-1 wins on throughput for very short strings (SSN, ZIP) if the implementation is optimised.

The key management story — envelope encryption via KMS.

• The data key. A 256-bit AES key that is the actual FPE key. Never stored in plaintext.
• The KMS master key. A CMK in AWS KMS / GCP KMS / Azure Key Vault that wraps the data key. The wrapped data key sits alongside the ciphertext or in a separate metadata store.
• The rotation. Rotating the master key re-wraps the data key without re-encrypting data. Rotating the data key requires re-encrypting all values — expensive at scale.
• The HSM option. For high-compliance workloads (PCI-DSS, HIPAA), the master key lives in a FIPS-140-2 Level 3 HSM. AWS CloudHSM and Azure Dedicated HSM are the managed options.

FPE vs deterministic tokenization — subtle but crucial.

• Both are equality-preserving. Same input → same output under both schemes (given the same key). Joins work.
• FPE is reversible with the key. An adversary with the key can decrypt every value. Deterministic tokenization with a hash is one-way; even with the HMAC key, an adversary must enumerate candidates.
• FPE preserves format. 16-digit card in → 16-digit card out. Deterministic tokenization produces an opaque string of the tokenizer's chosen length (e.g. tok_a1b2c3d4).
• PCI-DSS scope. FPE keeps the data in PCI scope (the key controls the entire cipher universe). Tokenization removes it from scope. Choose FPE only if you need the shape preservation and can accept staying in PCI scope.
• When to pick FPE. Legacy schemas that require the original type/length (a CHAR(16) NUMBER column that cannot be widened). Downstream systems that do arithmetic on the value (rare). Cases where reversibility must be one-round-trip (no vault call).

Common interview probes on FPE.

• "What problem does FPE solve that AES doesn't?" — must cite shape preservation.
• "FF1 vs FF3-1?" — must know both are NIST; FF3-1 is the revised FF3.
• "How does key management work with FPE?" — envelope encryption via KMS.
• "PCI-DSS implications?" — FPE stays in scope; tokenization removes from scope.

Worked example — FPE for a 16-digit credit-card column in Postgres

Detailed explanation. A payments team stores credit-card numbers as CHAR(16) in a Postgres table. They can't change the schema (legacy application depends on the type + length). They need to encrypt at-rest so a warehouse compromise doesn't leak card numbers. FPE with FF1 lets them encrypt in place — the encrypted value still fits the CHAR(16) constraint, so no schema migration is required.

• The schema constraint. card_number CHAR(16) NOT NULL.
• The encryption goal. At-rest ciphertext instead of plaintext.
• The FF1 gain. 16-digit input → 16-digit output. Schema unchanged.

Question. Implement FF1 encryption / decryption for the card_number column using an AES-256 data key wrapped by AWS KMS. Show the SQL flow with the encrypted column and the decryption UDF.

Input.

Field	Value
Column	orders.card_number CHAR(16)
Algorithm	FF1 with radix=10 (digits only)
Key	AES-256, envelope-encrypted with KMS CMK
KMS CMK ARN	arn:aws:kms:us-east-1:...:key/fpe-cc

Code.

# fpe.py — FF1 encryption for numeric strings (radix 10)
import boto3
from pyfpe_ff1 import FF1                 # pip install pyfpe-ff1

kms  = boto3.client("kms", region_name="us-east-1")
KMS_CMK = "arn:aws:kms:us-east-1:...:key/fpe-cc"

def load_data_key() -> bytes:
    wrapped = open("/etc/pii/fpe.key.enc", "rb").read()
    return kms.decrypt(CiphertextBlob=wrapped, KeyId=KMS_CMK)["Plaintext"]

data_key = load_data_key()               # 32 bytes = AES-256
ff1      = FF1(data_key, radix=10)       # numeric alphabet 0-9

def encrypt_pan(pan: str, tweak: bytes = b"cc_v1") -> str:
    assert len(pan) == 16 and pan.isdigit()
    return ff1.encrypt(pan, tweak)

def decrypt_pan(ciphertext: str, tweak: bytes = b"cc_v1") -> str:
    return ff1.decrypt(ciphertext, tweak)

# Round-trip
enc = encrypt_pan("4532112233445566")   # e.g. "8371904266142298" — still 16 digits
dec = decrypt_pan(enc)                  # "4532112233445566"  — reversed with key

-- Postgres — column stays CHAR(16); no schema migration
CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT,
    card_number  CHAR(16) NOT NULL,      -- now holds ciphertext, same shape
    amount       NUMERIC(18,2),
    order_ts     TIMESTAMPTZ
);

-- pgcrypto-based encrypt/decrypt would break the shape; a plpython3u UDF calling FF1 preserves it
-- Example UDF wrapping the Python code above (simplified)
CREATE OR REPLACE FUNCTION fpe_encrypt_pan(pan TEXT) RETURNS TEXT
LANGUAGE plpython3u AS $$
    import ff1_helper                     -- module installed on the DB host
    return ff1_helper.encrypt_pan(pan)
$$ SECURITY DEFINER;

-- INSERT flow — plaintext PAN enters via app; UDF encrypts in Postgres
INSERT INTO orders (order_id, customer_id, card_number, amount, order_ts)
VALUES (1001, 42, fpe_encrypt_pan('4532112233445566'), 199.99, NOW());

-- The row on disk holds "8371904266142298" — 16-digit ciphertext
SELECT card_number FROM orders WHERE order_id = 1001;
-- card_number
-- ----------------
-- 8371904266142298

Step-by-step explanation.

1. load_data_key reads the AES-256 data key (encrypted on disk) and unwraps it with KMS. The unwrapped key lives in process memory only; a process restart re-unwraps.
2. FF1(data_key, radix=10) constructs an FF1 cipher over the numeric alphabet (digits 0-9). The tweak is a public string that binds the ciphertext to a purpose (b"cc_v1" for credit card version 1). Different tweaks produce different ciphertexts even with the same key + plaintext.
3. encrypt_pan accepts a 16-digit string and returns a 16-digit ciphertext. The output is deterministic (same input + same tweak → same ciphertext), preserves length, and stays in the digit alphabet. The application can INSERT into the existing CHAR(16) column with no schema change.
4. decrypt_pan reverses. Only processes that hold the data key can decrypt. In a compromised warehouse scenario, the ciphertext is useless without the key.
5. The Postgres UDF wraps the Python code (via plpython3u) so encryption happens at INSERT time inside the database. This ensures the plaintext never persists — the row on disk holds only ciphertext. Alternative: encrypt in the application layer before the INSERT. Either way, at-rest exposure is zero.

Output.

Value	Plaintext	Ciphertext (FF1)	Schema-compatible?
card_number	4532112233445566	8371904266142298	yes (16 digits)
Type check	passes	passes	—
Storage size	16 bytes	16 bytes	—
Backup exposure	plaintext	ciphertext only	—

Rule of thumb. Reach for FPE when the schema cannot change and you need at-rest encryption. Reach for tokenization when the schema can change and you need to leave PCI scope. Reach for both together when the compliance requirement is highest and the operational budget allows.

Worked example — FPE with tweaks per column to prevent cross-column swaps

Detailed explanation. An attacker with database access could copy an encrypted card_number from customer A into customer B's row — a swap attack. If the FPE tweak is the same for both rows, the ciphertext moves cleanly; if the tweak is per-row or per-column, the swapped value fails to decrypt. Tweak-per-row is the strongest but expensive; tweak-per-column is the common compromise.

• The swap risk. Ciphertext + key = valid PAN. Moving the ciphertext to a different row transfers the PAN.
• The tweak defence. FF1 binds ciphertext to the tweak; decrypting with a different tweak returns garbage. Tweak per column ensures ciphertext from column A won't decrypt as PAN in column B; tweak per row ensures ciphertext from row A won't decrypt in row B.
• The trade-off. Per-row tweak = per-row storage overhead (the tweak or a row-key must be stored). Per-column = O(1) storage.

Question. Design a per-column tweak scheme and a per-row scheme, cite the trade-offs, and recommend which fits a warehouse workload.

Input.

Scheme	Tweak	Storage per row	Prevents
Per-column tweak	column name (e.g. b"cc_v1")	0	cross-column swap
Per-row tweak	column name + primary key	0 (derived)	cross-column + cross-row swap
Per-value tweak	random per-INSERT	~16 bytes	any swap

Code.

# tweak_schemes.py — three tweak strategies
from pyfpe_ff1 import FF1

ff1 = FF1(data_key, radix=10)

# --- Per-column tweak ---
def enc_col(pan: str, column: str = "card_number") -> str:
    return ff1.encrypt(pan, column.encode("utf-8")[:8].ljust(8, b"_"))

# --- Per-row tweak ---
def enc_row(pan: str, column: str, order_id: int) -> str:
    tweak = f"{column}:{order_id}".encode("utf-8")[:8].ljust(8, b"_")
    return ff1.encrypt(pan, tweak)

# --- Per-value tweak (with stored tweak) ---
import os
def enc_val(pan: str) -> tuple[str, bytes]:
    tweak = os.urandom(8)
    return ff1.encrypt(pan, tweak), tweak
def dec_val(ciphertext: str, tweak: bytes) -> str:
    return ff1.decrypt(ciphertext, tweak)

# Round-trip demonstrating swap defence
enc_a = enc_row("4111111111111111", "card_number", 1001)   # bound to (col=card_number, id=1001)
enc_b = enc_row("5222222222222222", "card_number", 1002)   # bound to (col=card_number, id=1002)

# If attacker copies enc_a into row 1002 and tries to decrypt with row 1002's tweak:
def dec_row(cipher: str, column: str, order_id: int) -> str:
    tweak = f"{column}:{order_id}".encode("utf-8")[:8].ljust(8, b"_")
    return ff1.decrypt(cipher, tweak)

# dec_row(enc_a, "card_number", 1002) → garbage (16 digits but not the original PAN)
# The tweak mismatch causes the Feistel network to produce a different plaintext.

-- Storage — per-column and per-row schemes need no extra columns
-- Per-value scheme needs a tweak column
CREATE TABLE orders_v3 (
    order_id     BIGINT PRIMARY KEY,
    card_number  CHAR(16) NOT NULL,
    -- Per-value scheme would add:
    -- card_tweak   BYTEA NOT NULL,
    amount       NUMERIC(18,2),
    order_ts     TIMESTAMPTZ
);

Step-by-step explanation.

1. enc_col uses a fixed per-column tweak. Every card in the card_number column shares the same tweak. Prevents cross-column swap (moving ciphertext to a different column with a different tweak) but not cross-row swap.
2. enc_row derives the tweak from (column, primary_key). Every row has its own tweak; ciphertext bound to row 1001 will not decrypt cleanly if placed in row 1002. Storage cost is zero because the tweak is derived from existing columns.
3. enc_val uses a random tweak per INSERT. Strongest defence but requires storing the tweak alongside the ciphertext (adds an extra column). Useful when the primary key is not stable (e.g. immutable log entries where the row key changes).
4. The swap defence: if an attacker moves enc_a (bound to card_number:1001) into order_id = 1002, decrypting with the correct card_number:1002 tweak produces a different 16-digit value — garbage from the customer's perspective but still 16 digits (the shape is preserved even under decryption of the wrong tweak). This is a real property of FF1 that has both good (undetectable garbage) and bad (attacker can't trivially detect the swap) implications.
5. For a warehouse workload with stable primary keys, enc_row is the sweet spot: strong swap defence, zero storage overhead. For an event-log workload where rows are re-keyed, enc_val is worth the extra column.

Output.

Scheme	Cross-column swap	Cross-row swap	Storage overhead
Per-column	prevented	not prevented	0
Per-row	prevented	prevented	0
Per-value	prevented	prevented	8 bytes per row

Rule of thumb. For warehouse workloads with stable primary keys, the per-row tweak (derived from column + PK) is the right default: strong swap defence, no schema change. Reach for per-value tweaks only when the primary key is not stable or the compliance target is the highest.

Worked example — FPE key rotation without downtime

Detailed explanation. Annual key rotation is a common compliance requirement (PCI-DSS 3.6.4). Naively rotating an FPE key requires re-encrypting every row — expensive at scale and downtime-inducing if done inline. The senior approach uses a dual-key window: for a period, both the old and new keys are online; new INSERTs use the new key; a background job re-encrypts old rows.

• The rotation goal. Retire key v1; introduce key v2; every row eventually encrypted under v2.
• The dual-key window. For (say) 90 days, both keys are online. New data uses v2; old data can be decrypted with v1.
• The migration job. Reads each row encrypted under v1, decrypts, re-encrypts under v2, updates.

Question. Implement the dual-key window with a key_version column and a background migration. Show the encrypt/decrypt logic and the migration loop.

Input.

Field	Value
Old key	v1 (retiring)
New key	v2 (active)
Migration window	90 days
Rows to migrate	500M
Migration throughput	5k rows/second

Code.

# fpe_rotation.py — dual-key window
from pyfpe_ff1 import FF1

data_key_v1 = load_key_from_kms("fpe-cc-v1")
data_key_v2 = load_key_from_kms("fpe-cc-v2")
ff1_v1 = FF1(data_key_v1, radix=10)
ff1_v2 = FF1(data_key_v2, radix=10)

def encrypt_current(pan: str, tweak: bytes) -> tuple[str, int]:
    """Always encrypt with the current (v2) key; return (ciphertext, key_version)."""
    return ff1_v2.encrypt(pan, tweak), 2

def decrypt_versioned(ciphertext: str, key_version: int, tweak: bytes) -> str:
    if key_version == 1: return ff1_v1.decrypt(ciphertext, tweak)
    if key_version == 2: return ff1_v2.decrypt(ciphertext, tweak)
    raise ValueError(f"unknown key version {key_version}")

def migrate_row(row):
    pan_v1 = decrypt_versioned(row["card_number"], row["key_version"], make_tweak(row))
    pan_v2, kv2 = encrypt_current(pan_v1, make_tweak(row))
    return pan_v2, kv2

-- Schema — add key_version column
ALTER TABLE orders ADD COLUMN key_version INT NOT NULL DEFAULT 1;

-- Background migration — throttled, resumable
CREATE OR REPLACE PROCEDURE migrate_fpe_batch(batch_size INT)
LANGUAGE plpgsql AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT order_id, card_number, key_version
    FROM   orders
    WHERE  key_version = 1
    ORDER  BY order_id
    LIMIT  batch_size
    FOR    UPDATE SKIP LOCKED
  LOOP
    UPDATE orders
    SET    card_number = fpe_migrate_pan(rec.card_number, rec.key_version, rec.order_id),
           key_version = 2
    WHERE  order_id    = rec.order_id;
  END LOOP;
  COMMIT;
END;
$$;

-- Run in a loop, throttled to avoid load
DO $$
BEGIN
  LOOP
    CALL migrate_fpe_batch(5000);
    PERFORM pg_sleep(1);                -- rate limit
    EXIT WHEN NOT EXISTS (SELECT 1 FROM orders WHERE key_version = 1);
  END LOOP;
END $$;

Step-by-step explanation.

1. The schema gets a key_version column with a default of 1 (existing rows). New INSERTs from now on use encrypt_current which always returns (ciphertext_v2, 2) — the new default.
2. decrypt_versioned reads the row's key_version and picks the correct FF1 cipher. Reads work throughout the migration; the application never sees a stale-key error.
3. The migration procedure processes batch_size rows at a time under FOR UPDATE SKIP LOCKED — safe under concurrent writes. Each row is decrypted with v1, re-encrypted with v2, and updated with the new key_version = 2.
4. The loop runs continuously with a 1-second sleep between batches to avoid pool saturation. At 5000 rows / batch × 1 batch / second, 500M rows migrate in about 28 hours of migration time — well within the 90-day window.
5. Once all rows have key_version = 2, the v1 key can be retired: destroy the KMS wrapped key file, revoke the KMS CMK grant, delete the v1 FF1 cipher from application code. The dual-key window closes; the rotation is complete.

Output.

Time	key_version distribution	Rows to migrate	Downtime
Day 0	100% v1	500M	0
Day 1 (start)	99.9% v1	499.5M	0
Day 15	50% v1	250M	0
Day 30 (end)	0% v1	0	0

Rule of thumb. Never rotate an encryption key with an inline re-encrypt — you'll take downtime you didn't budget for. Always use a dual-key window with a background migration and a version column. Ship the key_version column on the first FPE deployment even if you don't need rotation yet — retrofitting is expensive.

Senior interview question on FPE

A senior interviewer might ask: "You're introducing FPE for a credit_card column in a warehouse used by 20 downstream analytics jobs. Walk me through the algorithm choice, the key management, how you'd handle a query that needs to decrypt for a specific role, and how you'd stage the rollout."

Solution Using FF1 with envelope-KMS, per-row tweaks, and a role-aware decrypt UDF

-- Snowflake — external function calls a Lambda that owns the FF1 cipher
CREATE OR REPLACE EXTERNAL FUNCTION fpe_decrypt_cc(cipher STRING, tweak STRING)
  RETURNS STRING
  API_INTEGRATION = fpe_api
  AS 'https://fpe.internal.acme.io/decrypt';

-- Role-aware masking policy — decrypt only for PII_ADMIN
CREATE OR REPLACE MASKING POLICY cc_reveal AS (v STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'PII_ADMIN'
      THEN fpe_decrypt_cc(v, CONCAT('cc:', CURRENT_ROW_ID()))
    WHEN CURRENT_ROLE() IN ('CS_AGENT')
      THEN CONCAT('****-****-****-', SUBSTRING(v, 13, 4))    -- last-4 only
    ELSE                    '****-****-****-****'
  END;

ALTER TABLE orders MODIFY COLUMN card_number
  SET MASKING POLICY cc_reveal;

-- Query as an analyst
SELECT card_number FROM orders WHERE order_id = 1001;
-- ****-****-****-****
-- (masking policy returned the full-mask branch)

-- Query as a CS agent
SELECT card_number FROM orders WHERE order_id = 1001;
-- ****-****-****-5566
-- (masking policy returned last-4)

-- Query as PII_ADMIN
SELECT card_number FROM orders WHERE order_id = 1001;
-- 4532112233445566
-- (masking policy called fpe_decrypt_cc via external function)

# Lambda side — owns the FF1 key, called by Snowflake external function
def lambda_handler(event, ctx):
    rows = event["data"]                    # [[row_id, cipher, tweak], ...]
    results = []
    for row in rows:
        idx, cipher, tweak = row[0], row[1], row[2].encode("utf-8")[:8].ljust(8, b"_")
        plaintext = ff1.decrypt(cipher, tweak)
        results.append([idx, plaintext])
    return {"data": results}

Step-by-step trace.

Role	Query	Masking branch	Downstream call
PII_ADMIN	SELECT card_number	fpe_decrypt_cc	Lambda decrypt call
CS_AGENT	SELECT card_number	last-4 only	none
ANALYST	SELECT card_number	full mask	none
INGEST_BOT	INSERT card_number	encrypt in app	none (Lambda encrypt)

The rollout stages: (1) provision the KMS keys + Lambda; (2) create the external function and masking policy; (3) migrate existing rows via dual-key window; (4) enforce the masking policy on the column. The 20 downstream jobs see either last-4, a full mask, or (rare) plaintext depending on role — no code change needed in the jobs themselves.

Output:

Layer	Behaviour
Warehouse at-rest	ciphertext only
Analyst query	full-mask constant
CS agent query	last-4 for display
Admin query	plaintext via Lambda
Backup exposure	ciphertext only
Downstream ETL	operates on ciphertext (same schema)

Why this works — concept by concept:

• FF1 with per-row tweaks — shape-preserving encryption + swap defence. Ciphertext bound to (column, primary key) can't be moved without failing decrypt.
• Envelope KMS + Lambda — the FF1 data key lives in a Lambda VPC that Snowflake can call via external function. The warehouse never sees the raw key; the Lambda is the only place decryption happens.
• Role-aware masking policy — one column, three views: full mask for analysts, last-4 for CS, plaintext for PII_ADMIN. Every read is logged; the audit shows who decrypted.
• Schema-compatible — the CHAR(16) column is unchanged; ingest, ETL, and downstream jobs see 16-digit strings whether encrypted or plain. No migration surface.
• Cost — FF1 encrypt/decrypt is ~50 μs / row in-process; Lambda round-trip is ~10 ms / batch of 100 rows. Analysts and CS agents don't trigger decrypt; only PII_ADMIN queries incur the Lambda cost. In practice the cost is O(rows read by admins), which is small.

SQL
Topic — sql
SQL format-preserving encryption problems

Practice →

Design Topic — design Design problems on key-management topologies

Practice →

---

5. Dynamic masking + Snowflake / BigQuery policy tags

dynamic data masking = one physical table, many role-based views — the snowflake masking policy and BigQuery policy tag are the 2026 primitives

The mental model in one line: dynamic masking evaluates a masking function at query time based on the caller's role — the same physical row returns clear text to a CS agent, a tokenized hash to an analyst, and a fully masked constant to everyone else, without ever duplicating the data. This is the primitive that makes role-based PII access sustainable at scale; the alternative (per-role materialised views) doubles or triples storage and creates a synchronisation nightmare.

Dynamic vs static masking — the storage-vs-flexibility trade-off.

• Static masking. The masked value is written once to a separate table or view. A customers_masked table holds hashed emails; a customers_pii table holds plaintext. Roles are granted SELECT on one or the other.
• Dynamic masking. The masking function runs at query time against the base table. CURRENT_ROLE() inside the function chooses the branch. One table serves all consumers; roles get different views of the same rows.
• Storage. Static duplicates data per role (2×, 3× cost); dynamic uses one copy.
• Freshness. Static requires a refresh pipeline; dynamic is always current.
• Complexity. Static is more infra to maintain (extra tables + pipelines); dynamic is a policy attached to a column.
• When each wins. Dynamic for most workloads. Static for very large tables where the per-query masking cost is problematic and the roles are few and stable.

Snowflake masking policies — the canonical form.

• Create a policy. CREATE MASKING POLICY <name> AS (val <type>) RETURNS <type> -> CASE ... END. The function receives the plaintext and returns the masked value.
• Attach to a column. ALTER TABLE t MODIFY COLUMN c SET MASKING POLICY p. Applies to every SELECT of that column from any role.
• Role-aware. Inside the CASE, use CURRENT_ROLE(), IS_ROLE_IN_SESSION('...'), or INVOKER_ROLE() (for consuming views). Different roles → different branches.
• Tag-driven. Attach the policy to a tag instead of a column; every column with that tag inherits the policy. ALTER TAG pii_category SET MASKING POLICY email_mask ON COLUMN (STRING) WHERE tag_value = 'email' — one policy covers hundreds of columns.
• Composable. Combine with row access policies (CREATE ROW ACCESS POLICY) to filter rows and mask columns in one query.

BigQuery policy tags — the same idea, different mechanism.

• Policy tag taxonomy. A hierarchy of labels: pii/high, pii/medium, pii/low. Assigned in the Data Catalog.
• Column-level ACL. Grant roles/bigquerydatapolicy.maskedReader on a policy tag to a role. When the role queries a column with that policy tag, they see a masked value (currently: hash, nullify, or last-4).
• Data policy. BigQuery Data Policies (2023+) let you define per-column masking rules — hash, default constant, custom UDF — attached to a policy tag.
• DLP integration. Cloud DLP can auto-classify columns and apply policy tags. The classification + enforcement loop closes without human intervention for common PII categories.
• When BigQuery wins. GCP-native shops; teams already using Data Catalog; workloads that need the hierarchical taxonomy (pii/high ⊂ pii).

Databricks Unity Catalog — column-level ACLs + dynamic views.

• Column-level GRANT. GRANT SELECT ON TABLE t TO role grants full access; GRANT SELECT (col1, col2) ON TABLE t TO role grants column-scoped access. Missing columns are inaccessible.
• Dynamic views. CREATE VIEW v AS SELECT CASE WHEN is_member('admin') THEN email ELSE '***' END AS email, ... — the SQL branch on role membership.
• The gap. Databricks lags Snowflake on masking-policy syntax — you write the CASE in every view instead of attaching a reusable policy. Practical for smaller deployments; painful at scale.

The senior interview signals — reversibility, join preservation, role-based access.

• Reversibility. Which masking outcomes are one-way (hash, redact) vs two-way (tokenized-with-vault, FPE-with-key)? Interviewers listen for the trade-off.
• Join preservation. Does the masked value join with the plaintext value in another column? Deterministic hash: yes (equality-preserving). Random token: no. Random hash: no.
• Role-based access. How do you attach roles to policies? Snowflake CURRENT_ROLE(); BigQuery IAM on policy tag; Databricks is_member. Every warehouse ships the primitive; the design is picking the axis.

Common interview probes on dynamic masking.

• "Show me a Snowflake masking policy that returns clear text for admin and hash for analyst." — must write the CASE.
• "How do you propagate a masking policy across 100 tables?" — tag-driven policy binding.
• "What's the join-preservation property when the masking policy hashes an email?" — must cite deterministic vs random hashing.
• "How does BigQuery differ from Snowflake for masking?" — policy tags vs masking policies; hierarchical taxonomy vs SQL functions.

Worked example — Snowflake masking policy with three roles

Detailed explanation. Design a masking policy for customers.email that returns (a) clear text to PII_ADMIN, (b) deterministic hash (join-safe) to ANALYST_HASHED, (c) full-mask constant to everyone else. Attach the policy via the pii_category tag so every column tagged email inherits it.

• Roles. PII_ADMIN, ANALYST_HASHED, and a catch-all default.
• Behaviour per role. Clear, hash, mask.
• Propagation. Tag-driven — one policy covers every column with pii_category = 'email'.

Question. Implement the policy, the tag binding, and demonstrate the query results for each of the three roles.

Input.

Component	Value
Warehouse	Snowflake
Table	customers.email STRING
Roles	PII_ADMIN, ANALYST_HASHED, ANALYST_MASKED (default)
Tag	pii_category = 'email'

Code.

-- 1. Tag
CREATE OR REPLACE TAG pii_category
  ALLOWED_VALUES 'email', 'phone', 'ssn', 'name', 'notes-mixed';

-- 2. Attach tag to the column
ALTER TABLE customers MODIFY COLUMN email SET TAG pii_category = 'email';

-- 3. Masking policy
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'PII_ADMIN'          THEN val
    WHEN CURRENT_ROLE() = 'ANALYST_HASHED'     THEN SHA2(val, 256)
    ELSE                                             '***MASKED***'
  END;

-- 4. Tag-driven binding — one policy covers every column with pii_category='email'
ALTER TAG pii_category SET MASKING POLICY email_mask
  ON COLUMN (STRING) WHERE tag_value = 'email';

-- 5. Query as PII_ADMIN
USE ROLE PII_ADMIN;
SELECT customer_id, email FROM customers LIMIT 3;
-- customer_id | email
-- 1           | [e-mail]
-- 2           | [e-mail]
-- 3           | [e-mail]

-- 6. Query as ANALYST_HASHED
USE ROLE ANALYST_HASHED;
SELECT customer_id, email FROM customers LIMIT 3;
-- customer_id | email
-- 1           | e3b0c44298fc1c14...  (SHA-256 of [e-mail])
-- 2           | 8f1a2b3c4d5e6f70...  (SHA-256 of [e-mail])
-- 3           | 9a1b2c3d4e5f6a70...  (SHA-256 of [e-mail])

-- 7. Query as ANALYST_MASKED (catch-all)
USE ROLE ANALYST_MASKED;
SELECT customer_id, email FROM customers LIMIT 3;
-- customer_id | email
-- 1           | ***MASKED***
-- 2           | ***MASKED***
-- 3           | ***MASKED***

Step-by-step explanation.

1. The tag pii_category is the propagation vehicle. Attaching it to a column marks the column as email-category PII. Tags survive clones, views, and materialised views.
2. The masking policy email_mask receives the plaintext value and returns clear, hashed, or masked based on CURRENT_ROLE(). Snowflake evaluates the CASE at query time; the return value replaces the plaintext in the result set.
3. The ALTER TAG ... SET MASKING POLICY binding is the powerhouse — one statement attaches the policy to every current and future column with the tag. No per-column ALTER; no per-column enforcement risk.
4. The three role-scoped queries return different values from the same physical row. The customers.email column stores plaintext; the query result is what the masking policy returns. Access is logged in ACCESS_HISTORY regardless.
5. The join-preservation property: two rows with the same email produce the same SHA-256 hash under ANALYST_HASHED. Analytics that joins on SHA2(customers.email) still works. Under ANALYST_MASKED, all rows produce the same ***MASKED*** value — joins collapse; this is intentional (analysts who need join must escalate to ANALYST_HASHED).

Output.

Role	Value returned	Join-preserving?	Reversible?
PII_ADMIN	plaintext	yes (trivially)	n/a
ANALYST_HASHED	SHA-256 deterministic	yes (equality-preserving)	no (one-way)
ANALYST_MASKED	***MASKED*** constant	no (all rows equal)	no

Rule of thumb. Tag-driven policy propagation is the single lever that scales masking to a 400-table warehouse. Write the policy once, tag the columns, and let Snowflake propagate. Never enforce masking column-by-column at scale — the ALTER-per-column drift kills the rollout.

Worked example — row access policy + masking policy together

Detailed explanation. A transactions table holds financial data for all EU tenants. A German analyst may only see rows for their tenant; even within their tenant, the amount column is masked below a certain role. The combination requires both a row access policy (row-level filter) and a masking policy (column-level transform). Snowflake evaluates both on every query.

• The row access. Analyst sees only rows for their tenant (WHERE tenant_id = CURRENT_USER_TENANT()).
• The column mask. For analysts, the amount column is bucketed to the nearest 100 (privacy-preserving aggregation); for admins, the exact value.
• The order. Snowflake applies row access first (filter), then masking (transform).

Question. Implement both policies and demonstrate the query behaviour for a German analyst and a global admin.

Input.

Table	transactions (tenant_id STRING, amount NUMBER, ...)
Row access policy	tenant_isolation
Masking policy	amount_bucket
Roles	DE_ANALYST (tenant=DE), GLOBAL_ADMIN

Code.

-- 1. Row access policy — analyst sees only rows for their tenant
CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation AS (tenant_id STRING) RETURNS BOOLEAN ->
  CASE
    WHEN CURRENT_ROLE() = 'GLOBAL_ADMIN'    THEN TRUE
    WHEN CURRENT_ROLE() = 'DE_ANALYST'      THEN tenant_id = 'DE'
    WHEN CURRENT_ROLE() = 'FR_ANALYST'      THEN tenant_id = 'FR'
    ELSE                                          FALSE
  END;

ALTER TABLE transactions ADD ROW ACCESS POLICY tenant_isolation ON (tenant_id);

-- 2. Masking policy — bucket the amount for analysts
CREATE OR REPLACE MASKING POLICY amount_bucket AS (val NUMBER) RETURNS NUMBER ->
  CASE
    WHEN CURRENT_ROLE() = 'GLOBAL_ADMIN'    THEN val
    WHEN CURRENT_ROLE() LIKE '%_ANALYST'    THEN ROUND(val, -2)   -- round to nearest 100
    ELSE                                          NULL
  END;

ALTER TABLE transactions MODIFY COLUMN amount SET MASKING POLICY amount_bucket;

-- 3. Query as DE_ANALYST
USE ROLE DE_ANALYST;
SELECT tenant_id, txn_id, amount FROM transactions LIMIT 3;
-- tenant_id | txn_id | amount
-- DE        | 1001   | 200
-- DE        | 1002   | 500
-- DE        | 1003   | 100
-- (rows filtered to tenant_id='DE'; amount rounded to nearest 100)

-- 4. Query as GLOBAL_ADMIN
USE ROLE GLOBAL_ADMIN;
SELECT tenant_id, txn_id, amount FROM transactions LIMIT 3;
-- tenant_id | txn_id | amount
-- DE        | 1001   | 214.55
-- FR        | 2001   | 512.42
-- IT        | 3001   | 88.11
-- (all rows visible; exact amounts)

Step-by-step explanation.

1. The row access policy tenant_isolation returns TRUE / FALSE per row based on the tenant_id column and CURRENT_ROLE(). Snowflake evaluates it for every row of every SELECT against the table; rows returning FALSE are filtered out of the result.
2. ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (tenant_id) attaches the policy. From this point, every SELECT against transactions is filtered.
3. The masking policy amount_bucket returns the exact amount for admin and a rounded-to-100 value for analyst. ROUND(val, -2) rounds to the nearest hundred (privacy-preserving bucketing).
4. When the DE_ANALYST runs the query, Snowflake first applies row access (filter to tenant_id='DE'), then applies masking to the amount column of the surviving rows. The result is bucketed amounts for DE-only rows.
5. When GLOBAL_ADMIN runs the same query, both policies allow through: all rows visible; exact amounts. The same physical table serves both roles; the policies do the composition at query time.

Output.

Role	Rows returned	amount column	Enforcement layers
DE_ANALYST	tenant='DE' only	rounded to 100	row access + mask
FR_ANALYST	tenant='FR' only	rounded to 100	row access + mask
GLOBAL_ADMIN	all rows	exact	pass-through
Unknown role	0 rows	NULL	both deny

Rule of thumb. Row access + masking is the two-axis privacy primitive: filter which rows a role can see, transform what they see within the surviving rows. Ship both from day one on any multi-tenant analytics table.

Worked example — BigQuery policy tag + column-level ACL

Detailed explanation. A BigQuery warehouse serves both a BI dashboard team and a data-science team. Both need access to the orders table but only the BI team should see email addresses. The email column is annotated with a pii/high policy tag; the BI team is granted roles/bigquerydatapolicy.fineGrainedReader (unmasked read) on the tag; the DS team is granted roles/bigquerydatapolicy.maskedReader (masked read) on the tag. BigQuery enforces column-level access based on the role.

• The tag. pii/high policy tag applied to orders.email.
• The BI role. roles/bigquerydatapolicy.fineGrainedReader on pii/high → unmasked reads.
• The DS role. roles/bigquerydatapolicy.maskedReader on pii/high → masked reads (BigQuery's built-in mask).

Question. Configure the taxonomy, apply the tag, grant the roles, and show the query behaviour for both teams.

Input.

Component	Value
Project	acme-analytics
Dataset.table	warehouse.orders
Column	email STRING
Policy tag	acme-pii-taxonomy/pii/high
BI role	[e-mail]
DS role	[e-mail]

Code.

# gcloud — create the taxonomy and policy tag
gcloud data-catalog taxonomies create acme-pii-taxonomy \
  --location=us --display-name="ACME PII" \
  --description="PII classification for warehouse columns"

gcloud data-catalog taxonomies policy-tags create \
  --taxonomy=acme-pii-taxonomy --location=us \
  --display-name="pii/high" --description="High-sensitivity PII"

# Grant fine-grained reader (unmasked) to BI SA on the tag
gcloud data-catalog taxonomies policy-tags add-iam-policy-binding \
  <policy-tag-resource> \
  --member="serviceAccount:[e-mail]" \
  --role="roles/bigquerydatapolicy.fineGrainedReader"

# Grant masked reader to DS SA on the tag
gcloud data-catalog taxonomies policy-tags add-iam-policy-binding \
  <policy-tag-resource> \
  --member="serviceAccount:[e-mail]" \
  --role="roles/bigquerydatapolicy.maskedReader"

-- BigQuery — apply the tag to the email column
ALTER TABLE `acme-analytics.warehouse.orders`
  ALTER COLUMN email SET OPTIONS (
    policy_tags = ["projects/acme-analytics/locations/us/taxonomies/.../policyTags/..."]
  );

-- Query as BI SA (fine-grained reader)
SELECT customer_id, email FROM `acme-analytics.warehouse.orders` LIMIT 3;
-- customer_id | email
-- 1           | [e-mail]
-- 2           | [e-mail]
-- 3           | [e-mail]

-- Query as DS SA (masked reader) — BigQuery returns a hash by default
SELECT customer_id, email FROM `acme-analytics.warehouse.orders` LIMIT 3;
-- customer_id | email
-- 1           | 1a2b3c4d...  (SHA-256)
-- 2           | 5e6f7a8b...
-- 3           | 9c0d1e2f...

# terraform — declarative BigQuery data policy for a custom mask
resource "google_bigquery_datapolicy_data_policy" "email_mask" {
  location         = "us"
  data_policy_id   = "email_last_char_mask"
  policy_tag       = google_data_catalog_policy_tag.pii_high.name
  data_policy_type = "COLUMN_LEVEL_SECURITY_POLICY"
  data_masking_policy {
    predefined_expression = "SHA256"
  }
}

Step-by-step explanation.

1. The taxonomy acme-pii-taxonomy and the tag pii/high are created once in the BigQuery Data Catalog. Tags are hierarchical: pii/high, pii/medium, pii/low under the same root.
2. IAM roles are granted on the policy tag, not on the column directly. This is the propagation vehicle — every column with the same tag inherits the same access model.
3. ALTER TABLE ... ALTER COLUMN email SET OPTIONS (policy_tags = [...]) attaches the tag to the column. BigQuery enforces the ACL at query time; the SA's granted role determines the branch.
4. BI SA queries return plaintext (fine-grained reader = unmasked). DS SA queries return the default mask (SHA-256 hash under BigQuery's built-in maskedReader role).
5. Custom masks (e.g. last-4 for a card number, custom UDF for a phone) are defined as data_masking_policy resources attached to the tag. The Terraform snippet shows the declarative form; BigQuery evaluates the custom mask at query time.

Output.

Role	Access model	email column result	Enforcement point
BI SA (fine-grained)	fineGrainedReader on pii/high	plaintext	policy tag ACL
DS SA (masked)	maskedReader on pii/high	SHA-256 hash	policy tag ACL
Unknown SA	no role on pii/high	(query fails on column)	policy tag ACL

Rule of thumb. BigQuery policy tags shine when the classification is hierarchical (pii/high, pii/medium) and the enforcement is via IAM. Snowflake masking policies shine when the transform is a custom SQL function (arbitrary CASE branches). Pick the warehouse-native primitive; don't build your own.

Senior interview question on dynamic masking

A senior interviewer might ask: "You need to enforce role-based access on 400 Snowflake tables with a mix of email, phone, SSN, and free-text-mixed columns. Walk me through the tag taxonomy, the masking policies, the row access policies, and how you'd validate the enforcement across the full estate."

Solution Using a tag-driven policy layer with a nightly validator

-- 1. Tag taxonomy
CREATE OR REPLACE TAG pii_category
  ALLOWED_VALUES 'email','phone','ssn','name','address','notes-mixed';

CREATE OR REPLACE TAG pii_sensitivity
  ALLOWED_VALUES 'low','medium','high';

-- 2. Masking policies — one per category
CREATE OR REPLACE MASKING POLICY mask_email AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'PII_ADMIN'          THEN v
       WHEN CURRENT_ROLE() = 'CS_AGENT'           THEN v
       WHEN CURRENT_ROLE() = 'ANALYST_HASHED'     THEN SHA2(v, 256)
       ELSE                                             '***' END;

CREATE OR REPLACE MASKING POLICY mask_phone AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('PII_ADMIN','CS_AGENT')  THEN v
       WHEN CURRENT_ROLE() = 'ANALYST_HASHED'           THEN SHA2(v, 256)
       ELSE                                                   '***-***-' || RIGHT(v, 4) END;

CREATE OR REPLACE MASKING POLICY mask_ssn AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'PII_ADMIN'   THEN v
       ELSE                                        '***-**-****' END;

CREATE OR REPLACE MASKING POLICY mask_notes_mixed AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'PII_ADMIN'   THEN v
       ELSE                                        NULL END;

-- 3. Tag-driven binding — one ALTER covers every column with the matching tag_value
ALTER TAG pii_category SET MASKING POLICY mask_email  ON COLUMN (STRING) WHERE tag_value = 'email';
ALTER TAG pii_category SET MASKING POLICY mask_phone  ON COLUMN (STRING) WHERE tag_value = 'phone';
ALTER TAG pii_category SET MASKING POLICY mask_ssn    ON COLUMN (STRING) WHERE tag_value = 'ssn';
ALTER TAG pii_category SET MASKING POLICY mask_notes_mixed ON COLUMN (STRING) WHERE tag_value = 'notes-mixed';

-- 4. Row access for multi-tenant tables
CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation AS (tenant_id STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'GLOBAL_ADMIN'
  OR tenant_id = CURRENT_USER_TENANT();

-- 5. Nightly validator — every tagged column must have a policy attached
CREATE OR REPLACE VIEW privacy.policy_coverage AS
WITH tagged AS (
  SELECT object_database, object_schema, object_name, column_name, tag_value
  FROM   snowflake.account_usage.tag_references
  WHERE  tag_name = 'pii_category'
),
policied AS (
  SELECT ref_database_name, ref_schema_name, ref_entity_name, ref_column_name
  FROM   snowflake.account_usage.policy_references
  WHERE  policy_kind = 'MASKING_POLICY'
)
SELECT t.*,
       CASE WHEN p.ref_entity_name IS NULL THEN 'MISSING_POLICY' ELSE 'OK' END AS status
FROM   tagged t
LEFT   JOIN policied p
  ON   t.object_database = p.ref_database_name
 AND   t.object_schema   = p.ref_schema_name
 AND   t.object_name     = p.ref_entity_name
 AND   t.column_name     = p.ref_column_name;

Step-by-step trace.

Step	Component	Coverage
Tag taxonomy	6 category values × 3 sensitivity values	flexible per-column classification
Masking policies	4 policies (email/phone/ssn/notes-mixed)	one per category; role-aware branches
Tag-driven binding	4 ALTER TAG statements	all current + future columns with matching tag
Row access policy	1 policy on multi-tenant tables	filter rows by tenant
Nightly validator	policy_coverage view	detect drift (tag without policy)

The rollout is one-time: create the tags, write the policies, bind via tag, add row access on multi-tenant tables. From that point, every new column tagged pii_category = 'email' inherits the mask without additional ALTER. The validator catches any drift where a column is tagged but the policy is missing.

Output:

Metric	Manual per-column	Tag-driven
ALTER statements per new column	1 per column	0 (inherited)
Total ALTERs for 800 columns	800	4
Drift risk	high (any missed column leaks)	low (validator catches)
Onboard new PII category	new policy + ALTER 100 columns	new policy + 1 ALTER TAG
Coverage report	manual audit	SQL view

Why this works — concept by concept:

• Tag taxonomy as the propagation vehicle — one ALTER TAG covers every column with the matching value, current and future. Manual per-column ALTER is intractable at 800 columns; tag propagation makes it O(number of tag values).
• Row access + masking composition — Snowflake applies row access first (filter), then masking (transform). Multi-tenant tables need both; the composition is native and evaluated at query time.
• Nightly validator — the policy_coverage view joins tagged columns against policy references and flags gaps. A status = 'MISSING_POLICY' row is a paged alert. The validator is the guardrail that catches drift automatically.
• Role-per-purpose — PII_ADMIN, CS_AGENT, ANALYST_HASHED, and defaults cover 95% of use cases. Adding a new role means updating the CASE in each policy — one line each.
• Cost — masking policies add ~1 ms per row per masked column of policy CPU. On a 100M-row query touching 3 masked columns, that's ~5 minutes of policy evaluation — negligible against typical query IO. Row access policies add filter cost equivalent to a normal WHERE clause. The design is cheap; the drift protection is priceless.

SQL
Topic — sql
SQL dynamic-masking and row-access policy problems

Practice →

Design
Topic — design
Design problems on tag-driven privacy policies

Practice →

---

Cheat sheet — PII masking recipes

• Snowflake masking policy — canonical create + tag-driven apply.

CREATE OR REPLACE MASKING POLICY email_mask AS (v STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'PII_ADMIN'      THEN v
       WHEN CURRENT_ROLE() = 'ANALYST_HASHED' THEN SHA2(v, 256)
       ELSE                                        '***MASKED***' END;

CREATE OR REPLACE TAG pii_category ALLOWED_VALUES 'email','phone','ssn','name','notes-mixed';
ALTER TABLE customers MODIFY COLUMN email SET TAG pii_category = 'email';
ALTER TAG pii_category SET MASKING POLICY email_mask ON COLUMN (STRING) WHERE tag_value = 'email';

• BigQuery policy tag + column-level ACL.

resource "google_data_catalog_policy_tag" "pii_high" {
  taxonomy   = google_data_catalog_taxonomy.pii.name
  display_name = "pii/high"
}

resource "google_bigquery_datapolicy_data_policy" "email_mask" {
  location         = "us"
  data_policy_id   = "email_mask_hash"
  policy_tag       = google_data_catalog_policy_tag.pii_high.name
  data_policy_type = "COLUMN_LEVEL_SECURITY_POLICY"
  data_masking_policy { predefined_expression = "SHA256" }
}

# Column tagged via SQL
# ALTER TABLE dataset.orders ALTER COLUMN email
#   SET OPTIONS (policy_tags = ["projects/.../policyTags/pii-high"]);

• Format-Preserving Encryption (FF1) credit-card skeleton.

from pyfpe_ff1 import FF1
ff1 = FF1(data_key, radix=10)                # AES-256 key wrapped by KMS

def encrypt_pan(pan: str, order_id: int) -> str:
    tweak = f"cc:{order_id}".encode()[:8].ljust(8, b"_")
    return ff1.encrypt(pan, tweak)           # 16-digit in → 16-digit out

def decrypt_pan(ciphertext: str, order_id: int) -> str:
    tweak = f"cc:{order_id}".encode()[:8].ljust(8, b"_")
    return ff1.decrypt(ciphertext, tweak)

• Vault-based tokenization API call sketch.

import requests
def tokenize(value: str, kind: str) -> str:
    resp = requests.post("https://vault.internal/tokenize",
                         json={"value": value, "type": kind},
                         headers={"Authorization": f"Bearer {get_service_token()}"},
                         timeout=1.5)
    resp.raise_for_status()
    return resp.json()["token"]                # e.g. "tok_a1b2c3d4"

• Deterministic tokenizer (join-safe, KMS-wrapped HMAC).

import hmac, hashlib, base64
def det_tokenize(value: str, kind: str, secret: bytes) -> str:
    mac = hmac.new(secret, f"{kind}:{value.lower()}".encode(), hashlib.sha256).digest()
    return f"tok_{kind}_{base64.urlsafe_b64encode(mac[:16]).decode().rstrip('=')}"

• DLP scanner scheduling template (AWS Macie, new-partition-only).

import boto3
from datetime import datetime, timedelta
macie = boto3.client("macie2")
macie.create_classification_job(
    jobType="ONE_TIME",
    s3JobDefinition={
        "bucketDefinitions":[{"accountId":"123","buckets":["landing-raw"]}],
        "scoping":{"includes":{"and":[{"simpleScopeTerm":{
            "comparator":"GT","key":"OBJECT_LAST_MODIFIED_DATE",
            "values":[(datetime.utcnow()-timedelta(days=1)).isoformat()+"Z"],
        }}]}}
    },
    samplingPercentage=100,
    name=f"pii-daily-{datetime.utcnow():%Y%m%d}",
)

• Three-layer detection pipeline order. Regex + column-name (in-warehouse, ~$0) → managed DLP (Macie / Cloud DLP / SYSTEM$CLASSIFY, $10s/day scoped to new partitions) → ML NER on free-text (Comprehend / spaCy / HuggingFace, sample 5k–10k rows per column, ~$1 per column). Route by confidence: multi-layer agreement → auto-apply; single-layer → provisional mask + review.
• Restrict-by-default drift guard. Every un-classified column gets pii_reviewed = 'unknown'; the matching masking policy returns NULL for non-admin roles. Human review flips to pii_reviewed = 'yes' with the correct pii_category. Guarantees no new column leaks PII before review.
• GDPR erasure fan-out. Kafka topic pii_erasure_events; one consumer per store (bronze / silver / gold / feature store / BI cache); each consumer executes its own delete + logs an audit row. The compliance dashboard aggregates.
• PCI-DSS scope reduction. Tokenize at ingest via a vault; warehouse holds tokens + last-4 + brand; the vault is the only PCI-scoped system. Audit hours drop 10× vs a warehouse-in-scope design.
• FPE rotation via dual-key window. Add a key_version column defaulted to 1; introduce v2 as the new encryption default; background procedure re-encrypts rows in batches under FOR UPDATE SKIP LOCKED; retire v1 when all rows have key_version = 2.
• Snowflake row access + masking composition. CREATE ROW ACCESS POLICY filters rows by tenant; CREATE MASKING POLICY transforms surviving values by column. Snowflake evaluates row access first, then masking — both attach to the base table; every consumer inherits.
• Nightly policy-coverage validator. SQL view joins TAG_REFERENCES (every tagged column) LEFT JOIN POLICY_REFERENCES (every column with a policy attached). Rows with NULL on the policy side = drift; page on-call. Ensures the tag propagation is intact.

Frequently asked questions

Tokenization vs encryption — what's the practical difference for a data engineer?

Tokenization replaces a plaintext value with a random opaque token; the mapping lives in a vault (external service) or is derived from a keyed hash (deterministic). The token has no algorithmic relationship to the plaintext — even with full read access to the token database, an adversary cannot derive the plaintext without vault access or a candidate-value enumeration. Encryption transforms plaintext into ciphertext using a key; the ciphertext is derivable from the plaintext and reversible with the key. If the key leaks, the entire universe of ciphertext is compromised. The engineering trade-off: tokenization removes the warehouse from PCI-DSS scope (the vault holds the only PII); encryption keeps the warehouse in scope but preserves shape (with FPE) or supports arbitrary values (with AES). The 2026 default for join-preserving analytics is deterministic tokenization: HMAC-SHA-256 with a KMS-wrapped key. The 2026 default for high-sensitivity columns (credit cards, SSNs) is vault-based tokenization + a role-aware detokenize UDF. Encryption (FPE) is the escape hatch when the schema cannot change and reversibility must be one-round-trip.

What is format-preserving encryption (FPE) and when do I need it?

FPE is a family of encryption schemes (NIST FF1 and FF3-1) that transform plaintext into ciphertext with the same character set and same length — a 16-digit credit-card number encrypts to a 16-digit number, an email-shaped string to an email-shaped string. Standard AES produces binary ciphertext that requires base64/hex expansion; FPE produces ciphertext that fits the original column's type constraints without schema changes. When you need FPE: legacy schemas with fixed types (CHAR(16) credit-card columns), downstream systems that do format checks on the value, cases where reversibility must be one-round-trip without a vault call. When you don't: any workload where you can add a new column for the ciphertext or migrate to a tokenization vault. The trade-off vs tokenization: FPE stays in PCI-DSS scope (the key controls the entire cipher universe); tokenization removes it from scope. Key management: envelope encryption via KMS; the FF1 data key is unwrapped in memory at process start. Rotation: dual-key window with a background re-encrypt migration and a key_version column — never inline.

Dynamic masking vs static masking — which should I ship?

Ship dynamic masking for 95% of workloads. The masking policy runs at query time based on the caller's role; one physical table serves all consumers; the same row returns clear text to a CS agent, a deterministic hash to an analyst, and a full-mask constant to everyone else. Storage is not duplicated; the data is always fresh; onboarding a new role is one CASE branch. Ship static masking only when (a) the tables are enormous and the per-query masking cost dominates, (b) the roles are few and stable, and (c) the freshness lag from a nightly refresh is acceptable. In Snowflake: CREATE MASKING POLICY + ALTER TABLE ... SET MASKING POLICY for dynamic; a separate table + a REFRESH job for static. In BigQuery: policy tags + column-level ACL for dynamic; scheduled queries writing to a masked table for static. The senior signal: citing tag-driven propagation as the mechanism that makes dynamic masking scale to hundreds of tables — attaching the policy to a tag instead of each column reduces N ALTER statements to 1.

How do I detect PII at scale without paying $10k/day for the DLP scanner?

Three layers, sequenced cheapest first. Layer 1 — regex + column-name heuristics run in-warehouse against every column (SQL only, ~$0). Catches obvious patterns: LOWER(col_name) LIKE '%email%' finds email, contact_email, user_email; a value-regex catches [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}. Coverage: ~80% of typed PII for pennies. Layer 2 — managed DLP (AWS Macie, GCP Cloud DLP, Snowflake SYSTEM$CLASSIFY) runs only on tables flagged by layer 1, scoped to new partitions with OBJECT_LAST_MODIFIED_DATE > yesterday. Full-bucket scan of a 2 TB landing zone costs $2000/day; scoping to new data drops it to ~$50/day. Layer 3 — ML NER classifier (AWS Comprehend PII, spaCy NER) runs on a 5k–10k row sample per free-text column identified by layers 1 and 2. Sample cost is ~$1 per column; full-scan cost would be $50k+ for a 500M-row column. Confidence aggregation: VERY_LIKELY when all three layers agree (auto-apply); LIKELY when two agree (apply + review); POSSIBLE when one flags (provisional mask + urgent review); NONE otherwise (log only). Total cost for a 10 TB warehouse: ~$150 for the 48-hour initial pass, ~$70/day steady state.

How does a Snowflake masking policy actually work under the hood?

A Snowflake masking policy is a SQL function attached to a column that Snowflake evaluates at query time on every SELECT. The function receives the plaintext value from the base table and returns a masked value; the CASE inside typically branches on CURRENT_ROLE(), IS_ROLE_IN_SESSION('...'), or INVOKER_ROLE() (for consuming views). The evaluation model: for each row of the result set, Snowflake calls the function with the plaintext; the return value replaces the plaintext in the response. Row access policies (which filter rows) evaluate before masking policies (which transform surviving values); the composition is native. Tag-driven propagation is the operational lever: attaching a policy to a tag (ALTER TAG pii_category SET MASKING POLICY email_mask ON COLUMN (STRING) WHERE tag_value = 'email') applies the policy to every current and future column with that tag, across clones, views, and materialised views — one ALTER for hundreds of columns. The audit story: SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY logs every query with the columns accessed and the caller identity; the nightly view materialises the answer to "who read what under what mask." The performance cost: ~1 ms per row per masked column of policy CPU; negligible against typical query IO.

Do I still need to encrypt PII at-rest if I've tokenized or masked it?

Yes — the layers are complementary, not alternatives. At-rest encryption (Snowflake's native, S3 SSE, TDE for OLTP databases) protects against physical theft of storage media or unauthorized access to the underlying bytes; it's a compliance requirement under HIPAA (§164.312) and a best-practice under GDPR Article 32. It runs at the storage layer and is transparent to the query engine. Tokenization / FPE / masking are column-level controls that determine what the authorised query engine returns to a caller based on role — they protect against insider access, mis-scoped roles, and BI-tool leaks even after the storage-layer decryption has happened. The layers together: the warehouse encrypts every block at rest (protects the disk); the column-level policy transforms the value at query time (protects the read path); the vault or KMS holds the reversibility key (protects the reversal path). The engineering translation: enable at-rest encryption on day one (it's usually a checkbox), then add tokenization / FPE / masking as the column-level story matures. A common mistake: thinking that at-rest encryption is enough — it is not; a compromised warehouse role with SELECT can read every plaintext column regardless of the storage-layer encryption. The layers stack; every serious PII deployment ships both.

Practice on PipeCode

• Drill the SQL practice library → for the Snowflake masking policy, row access policy, tokenization-join, and FPE UDF problems senior interviewers love.
• Rehearse the pipeline shape on the ETL practice library → for the landing-zone DLP scan, ingest-time tokenization, and dual-key rotation exercises that motivate the masking primitives.
• Sharpen the trade-off axis with the design practice library → for the tokenization-vault topology, PCI-DSS scope-reduction, and GDPR-erasure fan-out problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the detect / transform / enforce / audit mental model against real graded inputs.

Lock in privacy-engineering muscle memory

Regulations tell you what. Vendor docs tell you how. PipeCode drills tell you the decision — when tokenization removes PCI scope, when FPE beats schema migration, when a Snowflake masking policy replaces three materialised views. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →