GDPR & DSAR Pipelines: Right-to-Be-Forgotten in the Warehouse

gdpr data pipeline is the quiet phrase behind the loudest 3 AM war room a senior data engineer will ever join — the one where legal has just received a Data Subject Access Request under Article 17, the 30-day clock is already 12 days in, and nobody on the team knows which of the 30-odd downstream tables in the warehouse still hold the data-subject's email in a hashed column, an event blob, a materialised customer 360 view, or a six-week-old snapshot in the analytics lake. GDPR "right to be forgotten" reads like a one-line clause in the regulation and lands in the data-platform team's backlog as a distributed-systems problem — one identity, many keys, dozens of tables, three storage layers, a backup regime, and a hard SLA measured in calendar days.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "explain how you'd architect a dsar pipeline end-to-end for a Snowflake warehouse plus an S3 lake plus a backup vault, so that the 30-day SLA is a non-event," or "when you delete a right to be forgotten subject, do you delete from backups?" or "what does your gdpr warehouse lineage look like when a data subject access request comes in for someone with three hashed identifiers?" It walks through why identity resolution is the first, hardest step of any deletion pipeline, how dbt / OpenLineage lineage turns into a canonical delete-target list, the per-store deletion mechanics (Snowflake DELETE + PURGE with a time-travel window shrink, BigQuery DELETE with partition expire, S3 lifecycle rules with delete markers, backup retention-policy carve-outs), and the DSAR lifecycle plus per-store attestation log that satisfies both the regulator and the senior-DE interviewer. 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 on the ETL practice library →, and sharpen the propagation-completeness axis with the optimization practice library →.

---

On this page

• Why "delete the user" is a distributed-systems problem
• Identity resolution — one user, many keys
• Lineage-driven delete propagation
• Deletion mechanics per store
• Audit, reporting, and the DSAR SLA
• Cheat sheet — GDPR DSAR recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why "delete the user" is a distributed-systems problem

GDPR Article 17 sounds like a single verb — in the warehouse it decomposes into identity, lineage, propagation, and audit

The one-sentence invariant: a modern data platform stores every user across 30+ places — OLTP primaries, warehouse raw / stg / mart layers, an event-lake in S3, cached feature stores, downstream mart exports, backup snapshots, and streaming logs — and GDPR Article 17 requires you to prove, per store, that the subject's data has been erased within 30 calendar days. The regulation is a compliance clause; the implementation is a distributed-systems problem where identity resolution, lineage discovery, delete propagation, and per-store attestation are all first-class engineering concerns. Every senior interview on data governance in 2026 opens with some variation of "walk me through what happens in your platform between the moment legal receives a DSAR and the moment you can hand them an attestation log."

The four axes interviewers actually probe.

• Identity resolution. One data-subject presents one identifier (usually an email). Behind that email sits an identity graph — 1 email, N user_ids, M hashed variants, K device IDs, and every row in every table keyed on any of them. The first step of any deletion pipeline is turning "[e-mail]" into a canonical set of identity keys. Get this wrong and you either miss rows (compliance fail) or delete another subject's data (confidentiality fail).
• Lineage graph. Which tables actually store any of the resolved identity keys? Not the ones your OKR planning doc listed six months ago — the ones dbt / OpenLineage / the warehouse catalog says, right now, hold columns descended from the source-of-truth identity. Deletion completeness is a function of lineage completeness; interviewers listen for candidates who name their lineage source of truth without hesitation.
• Delete propagation. Every store has its own delete mechanic. Snowflake wants DELETE + PURGE and a time-travel window shrink; BigQuery wants DELETE and partition-expiry; S3 wants lifecycle rules + delete markers; backup vaults want a retention-policy carve-out (or a hard delete with a written policy justification). A senior candidate names the mechanic per store without prompt.
• Audit + attestation. Every store's delete produces a per-store attestation record — timestamp, deleted-row count, actor, request ID, hash of the affected keys. The attestation log survives regulator audits; without it, the compliance argument is verbal, which regulators do not accept.

The 2026 reality — DSAR volume + regulator enforcement.

• SLA clocks. GDPR Article 17 gives you 30 calendar days from the receipt of a valid DSAR; CCPA (California) Section 1798.105 gives you 45 days. Both clocks include weekends and holidays. Regulators have increasingly used the SLA breach as the enforcement lever — the fine is not primarily for the failure to delete, but for the failure to delete within the clock.
• Volume growth. DSAR volumes have grown ~3–5× since 2020 as consumer awareness rose and privacy-focused browsers made the request one click. A mature B2C platform now processes hundreds to thousands of DSARs per month; a manual per-request runbook does not scale.
• Verification obligation. Both regulations require you to verify the requester's identity before deleting; a malicious requester submitting someone else's email is a real attack vector. The DSAR pipeline is a security surface as well as a compliance surface.
• Downstream artefacts. ML training data, feature stores, cached embeddings, and derived aggregates all count as "personal data" if they can be re-identified. Deletion propagation extends further than the raw warehouse rows.

What a right-to-be-forgotten pipeline actually delivers.

• Ingress. A DSAR ticket arrives (email inbox, in-product form, third-party privacy platform). It carries an email + optional evidence-of-identity blob.
• Identity resolution. The pipeline resolves the email through the identity graph to the canonical set of (user_id, hashed_email, device_id, phone_hash, ...) keys.
• Target enumeration. The pipeline queries the lineage graph for every table storing any of the resolved keys.
• Per-store deletion. The pipeline runs the correct delete mechanic per store, waits for the mechanic's completion signal, and captures the attestation.
• Audit + close. The pipeline updates the DSAR ticket with a per-store attestation log, closes the request, and pushes the SLA clock event to legal's dashboard.

What interviewers listen for.

• Do you say "identity resolution is step zero" without prompt? — senior signal.
• Do you name lineage as the source of truth for delete targets rather than a hand-maintained list? — required answer.
• Do you distinguish hard delete, soft delete, and suppression list and pick per store? — senior signal.
• Do you have a written policy for backups rather than hand-waving? — required answer.
• Do you close every DSAR with a per-store attestation log, not a verbal confirmation? — senior signal.

Worked example — the naive DSAR runbook that misses half the tables

Detailed explanation. A common day-one anti-pattern: a startup receives its first DSAR, and the on-call engineer runs DELETE FROM users WHERE email = '[e-mail]' on the OLTP primary. Three months later a regulator complaint reveals that the subject's orders, sessions, events, email_hash_map, ml_feature_store, and mart_customer_360 rows are all still present in the warehouse. The startup argues that they "deleted the account" — the regulator argues that "the account is one row of many hundreds of downstream personal-data rows" and issues a fine.

• The symptom. DSAR marked as closed; downstream tables still hold the data.
• The naive fix. Add more DELETE statements as complaints come in.
• The real bug. No canonical enumeration of every place the subject's data lives.
• The collapse. Regulator audits reveal the miss; the fine is proportional to the number of stores that were skipped.

Question. A B2C SaaS runs Postgres (OLTP), Snowflake (warehouse — raw, stg, mart layers), S3 (event-lake), and a Redis feature store. DSAR arrives for [e-mail]. Enumerate every location that could hold the subject's data, using lineage as the source of truth. Show the SQL that walks the dbt catalog to produce the target list.

Input.

Store	Layers	Identifier columns
Postgres	users, sessions, orders	email, user_id
Snowflake raw	raw_events, raw_orders, raw_users	user_id, hashed_email, device_id
Snowflake stg	stg_events, stg_orders, stg_users	user_id, hashed_email
Snowflake mart	mart_customer_360, mart_lifetime_value	user_id
S3 event-lake	s3://events/YYYY/MM/DD/*.parquet	user_id, device_id in payload
Redis feature store	feat:user:{user_id}	user_id in key

Code.

-- Walk dbt's manifest/catalog to enumerate every model that references an identity column
-- Assumes dbt catalog is exposed as a Snowflake table via `dbt docs generate` + upload
WITH id_cols AS (
  SELECT column_name
  FROM   (VALUES
    ('user_id'),
    ('hashed_email'),
    ('email'),
    ('device_id'),
    ('phone_hash')
  ) AS t(column_name)
),
model_cols AS (
  SELECT node_id,
         database_name,
         schema_name,
         table_name,
         column_name
  FROM   analytics.dbt_catalog_columns
),
targets AS (
  SELECT mc.database_name,
         mc.schema_name,
         mc.table_name,
         mc.column_name,
         mc.node_id
  FROM   model_cols mc
  JOIN   id_cols ic
    ON   LOWER(mc.column_name) = LOWER(ic.column_name)
)
SELECT database_name || '.' || schema_name || '.' || table_name AS fq_target,
       LISTAGG(DISTINCT column_name, ', ') AS identity_columns,
       COUNT(DISTINCT node_id) AS lineage_nodes
FROM   targets
GROUP  BY 1
ORDER  BY 1;

# Enumerate S3 lake partitions that require scan-and-rewrite
import boto3

s3 = boto3.client("s3")
bucket = "events-lake"
paginator = s3.get_paginator("list_objects_v2")

targets = []
for page in paginator.paginate(Bucket=bucket, Prefix="events/"):
    for obj in page.get("Contents", []):
        # Partition path pattern: events/YYYY/MM/DD/HH/part-*.parquet
        targets.append(obj["Key"])

print(f"S3 lake objects to scan: {len(targets)}")

Step-by-step explanation.

1. The dbt catalog walk is the canonical target-list source. Instead of maintaining a hand-written list of "tables that hold personal data," the pipeline queries the catalog for every model that has a column matching any known identity column name. The catalog is refreshed on every dbt build, so newly-added tables are auto-detected the next time a DSAR runs.
2. The id_cols CTE hard-codes the identity column names. In practice this list comes from a PII taxonomy owned by the data-governance team; adding a new identity column is a one-line change plus a re-run.
3. The join surfaces every (fq_target, identity_columns) pair — the target list. Typical output for a mid-size B2C SaaS: 30–80 rows.
4. The S3 enumeration is a separate pass because lake-side files are keyed by partition path, not by column-descending lineage. Every partition potentially contains personal data and must be scanned + rewritten (or filtered via a suppression list overlay at read time).
5. The Redis and OLTP stores use their own enumeration paths (Postgres information_schema.columns, Redis KEYS scan). The point is the same — every store contributes rows to the canonical target list.

Output.

Store	Enumeration mechanism	Typical target count
Snowflake	dbt catalog walk	30–80 fq tables
Postgres	information_schema.columns	5–15 tables
S3 lake	partition path listing	1000s of objects
Redis	KEYS scan on identity-key patterns	10s of key patterns
Total distinct delete targets	lineage-driven	40–100 tables + 1000s of objects

Rule of thumb. Never maintain a hand-written list of "tables that hold personal data." The list drifts on every schema change; lineage tools give you the source of truth for free. Every DSAR pipeline reads from the catalog, never from a stale wiki.

Worked example — the "we deleted from Postgres" fallacy

Detailed explanation. A different framing of the same anti-pattern: the platform team treats OLTP as authoritative and assumes that the CDC pipeline will propagate DELETE events downstream. This is wrong in three ways. First, most CDC tools ship DELETE events as separate messages that downstream consumers may or may not honour. Second, the warehouse raw layer typically stores the history of a row (soft-delete columns, SCD Type 2 dimensions) even after the OLTP row is gone. Third, downstream marts materialise aggregates and joins that survive the source-row deletion. Walk through why "delete on OLTP + CDC" is not sufficient for GDPR compliance.

• The naive model. OLTP is source of truth; CDC propagates all DMLs.
• The reality. CDC ships events; the warehouse chooses how to interpret DELETEs (usually soft-delete with a tombstone).
• The gap. Warehouse raw + stg + mart still hold pre-delete row versions; SCD dimensions preserve historical values by design.

Question. Show the CDC event flow for a DSAR delete and the warehouse-side transformations that do not propagate the deletion. Explain the four warehouse patterns that break CDC-propagated delete.

Input.

Layer	Table pattern	Behaviour on CDC DELETE
Warehouse raw	raw_users_history	Appends a _op = 'D' row; original insert row remains
Warehouse stg	stg_users (SCD Type 2)	Sets is_current = false, valid_to = now(); old values preserved
Warehouse mart	mart_customer_360	Aggregated; delete does not remove the aggregated row
Feature store	feat:user:{id}	TTL-based; may still be present until TTL

Code.

-- The raw layer with the "append-only + op-code" CDC pattern
CREATE TABLE analytics.raw_users_history (
  event_ts       TIMESTAMP_NTZ,
  op             VARCHAR(1),          -- I / U / D
  user_id        BIGINT,
  email          VARCHAR,
  hashed_email   VARCHAR,
  raw_payload    VARIANT
);

-- After DSAR delete: history still contains the original I + subsequent U rows
SELECT op, event_ts, email, hashed_email
FROM   analytics.raw_users_history
WHERE  user_id = 42
ORDER  BY event_ts;
-- I | 2024-01-01 | alice@ex | HASHED_ALICE_V1
-- U | 2024-06-15 | alice@ex | HASHED_ALICE_V2
-- D | 2026-07-04 |          |
-- ↑ regulator sees the pre-delete rows; compliance fail

-- The stg SCD-2 dimension
SELECT user_id, email, is_current, valid_from, valid_to
FROM   analytics.stg_users
WHERE  user_id = 42;
-- 42 | alice@ex | false | 2024-01-01 | 2024-06-15
-- 42 | alice@ex | false | 2024-06-15 | 2026-07-04
-- ↑ still holds the email, valid_to just marks the closure

# The "delete for real" pattern: hard-delete raw + stg + mart
DELETE_STATEMENTS = [
    "DELETE FROM analytics.raw_users_history WHERE user_id = %s",
    "DELETE FROM analytics.stg_users             WHERE user_id = %s",
    "DELETE FROM analytics.mart_customer_360     WHERE user_id = %s",
    "DELETE FROM analytics.mart_lifetime_value   WHERE user_id = %s",
]

def hard_delete(user_id: int, conn) -> dict:
    counts = {}
    for stmt in DELETE_STATEMENTS:
        cur = conn.cursor()
        cur.execute(stmt, (user_id,))
        counts[stmt] = cur.rowcount
    conn.commit()
    return counts

Step-by-step explanation.

1. The raw layer follows the standard CDC append-only pattern — every OLTP DML becomes a new row in the history table with an op column. A CDC-propagated DELETE shows up as a row with op = 'D' and a null payload; every prior I and U row is still present. Regulators reading the table see the subject's email in the historical I row.
2. The stg SCD-2 pattern is designed to preserve history for analytics. A CDC DELETE closes the current row (sets valid_to) but does not remove the row. The subject's email remains in every historical row.
3. The mart layer contains aggregates and joins that do not descend directly from the OLTP row. Even a perfect propagation of the raw DELETE would not remove the mart row; you must explicitly re-materialise or hard-delete the mart.
4. The feature store operates on a TTL. Until the TTL fires (typically 24 hours to 7 days), the subject's features remain queryable. Explicit invalidation on delete is mandatory.
5. The correct pattern is not to lean on CDC. The DSAR pipeline runs explicit DELETE statements against every warehouse table on the canonical target list, waits for each to complete, and records the row count as attestation evidence.

Output.

Layer	Post-CDC state	Post-DSAR-pipeline state
raw_users_history	3 historical rows + 1 D row	0 rows
stg_users (SCD-2)	2 historical rows + closed	0 rows
mart_customer_360	untouched	0 rows
feat:user:42	present until TTL	invalidated

Rule of thumb. CDC propagates events, not GDPR-grade deletion. Every DSAR pipeline runs explicit hard-delete against every warehouse table on the target list. Trusting CDC alone is the #1 cause of DSAR compliance failures in warehouses.

Worked example — sizing the DSAR SLA budget across stores

Detailed explanation. GDPR gives you 30 calendar days from receipt to completion. The naive assumption is that the deletion runs in seconds; reality is different. Snowflake DELETE on a large table can take minutes-to-hours; a time-travel window shrink adds more; S3 lake rewrites (scan + filter + rewrite Parquet) can take hours-to-days; backup carve-outs can take weeks. Walk through how the SLA budget partitions across stores and where the pipeline must run asynchronously to hit the clock.

• The budget. 30 days = 720 hours.
• The partitions. Ingress + verification (48 hours), identity resolution + target enumeration (1 hour), per-store deletion (variable), attestation + close (24 hours).
• The variable. Per-store deletion is where the SLA is won or lost; the pipeline must run it asynchronously and track completion.

Question. Partition the 30-day DSAR SLA across the pipeline stages. Show the per-stage budget, the per-store deletion cost distribution, and the point at which the pipeline must escalate to the DBA on-call.

Input.

Stage	Typical duration	Notes
DSAR ticket ingress	seconds	Automated form / email intake
Requester verification	24–72 hours	Manual review of ID evidence
Identity resolution	seconds	Query the identity graph
Target enumeration	seconds	Query the dbt catalog
Snowflake delete (raw + stg + mart)	5 min – 2 hours	Per-table DELETE
Snowflake time-travel shrink	24 hours (default retention)	Wait for TTL to clear or explicit purge
S3 lake rewrite	6 hours – 3 days	Scan + filter + rewrite affected Parquet
Redis invalidation	seconds	DEL on keys
Backup carve-out	7–30 days	Depends on retention policy
Attestation + close	24 hours	Legal review + ticket close

Code.

# SLA budgeting helper
from datetime import timedelta

SLA_BUDGET = timedelta(days=30)

STAGES = [
    ("ingress",                     timedelta(hours=1)),
    ("verification",                timedelta(hours=48)),
    ("identity_resolution",         timedelta(minutes=5)),
    ("target_enumeration",          timedelta(minutes=5)),
    ("snowflake_delete",            timedelta(hours=4)),
    ("snowflake_timetravel_shrink", timedelta(hours=24)),
    ("s3_lake_rewrite",             timedelta(days=3)),
    ("redis_invalidate",            timedelta(minutes=5)),
    ("backup_carveout",             timedelta(days=14)),
    ("attestation_close",           timedelta(hours=24)),
]

def sla_check() -> dict:
    total = sum((d for _, d in STAGES), timedelta())
    slack = SLA_BUDGET - total
    return {"total": total, "budget": SLA_BUDGET, "slack": slack}

print(sla_check())
# {'total': datetime.timedelta(days=17, seconds=79800),
#  'budget': datetime.timedelta(days=30),
#  'slack': datetime.timedelta(days=12, seconds=6600)}

# Pipeline escalation policy — when to page on-call
escalations:
  - stage: verification
    threshold_hours: 72
    action: page-legal-on-call
  - stage: s3_lake_rewrite
    threshold_hours: 96          # 4 days
    action: page-dba-on-call
  - stage: backup_carveout
    threshold_days: 21           # 3 weeks
    action: page-security-on-call
  - stage: total_elapsed
    threshold_days: 25           # 5 days of slack remaining
    action: page-dpo-and-legal

Step-by-step explanation.

1. The 30-day SLA is a hard clock — regulators do not care about internal complexity. The pipeline must budget every stage against the clock and escalate when a stage exceeds its allocated share.
2. Verification (24–72 hours) is the largest human-in-the-loop cost. Most DSAR platforms shorten this by pre-verifying identity at signup (KYC-lite); post-hoc verification of a bare email is the slowest path.
3. The per-store deletion cost is dominated by S3 lake rewrite — the physical cost of reading, filtering, and rewriting Parquet files for every affected partition. Modern lakehouses (Iceberg, Delta) reduce this with row-level deletes, but the naive plan of scanning every partition is measured in days for a large lake.
4. Snowflake time-travel shrink is a common surprise. DELETE from a Snowflake table removes the rows from the current version, but the rows persist in time-travel history for up to 90 days (default retention is 1 day). The DSAR pipeline must either wait for time-travel to expire, explicitly ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0, or run a fail-safe purge.
5. The escalation policy catches stages that overrun. If the S3 rewrite is still going at day 4, page the DBA on-call. If total elapsed hits day 25, page the DPO — you have 5 days of slack; anything less is a compliance risk.

Output.

Stage	Budget	Typical actual	Slack
Ingress + verification	3 days	2 days	1 day
Identity + target enum	5 min	5 min	0
Warehouse deletes	28 hours	24 hours	4 hours
S3 lake rewrite	3 days	2 days	1 day
Backup carve-out	14 days	10 days	4 days
Attestation + close	24 hours	6 hours	18 hours
Total	22–25 days	17 days	5–8 days

Rule of thumb. Budget the SLA per stage; escalate stage-by-stage; never treat the 30 days as a single global counter. The stages that overrun are the ones your pipeline learns about; the ones that quietly run within budget are the ones you never hear from.

Senior interview question on the DSAR problem framing

A senior interviewer often opens with: "You inherit a data platform with Snowflake + S3 lake + Postgres OLTP + Redis feature store, no DSAR pipeline, and legal has just accepted 12 pending DSAR requests. Walk me through what you build in the first week, what the SLA math looks like, and what failure modes you guard against."

Solution Using the four-axis DSAR pipeline plus a per-stage escalation policy

# dsar_pipeline.yaml — the four-axis architecture
version: 1

axes:
  identity_resolution:
    source: identity_graph_service
    inputs:  [email, phone_hash, device_id]
    outputs: [user_id[], hashed_email[], device_id[]]

  lineage_graph:
    source: dbt_catalog
    columns: [user_id, hashed_email, email, device_id, phone_hash]
    outputs: [fq_table[], identity_columns_per_table]

  delete_propagation:
    stores:
      snowflake:
        mechanic: "DELETE + PURGE + time-travel shrink"
        retention_override: 0        # shrink to 0 days for DSAR path
      bigquery:
        mechanic: "DELETE + partition expire"
      postgres:
        mechanic: "DELETE"
      s3_lake:
        mechanic: "lifecycle rule + delete markers + scan/rewrite for affected partitions"
      redis:
        mechanic: "DEL on identity keys"
      backup_vault:
        mechanic: "carve-out with policy-justification log"

  audit:
    attestation_log_table: analytics.dsar_attestation_log
    per_store_fields: [store, deleted_row_count, actor, request_id, keys_hash, ts]
    sla_dashboard: legal.dsar_sla_dashboard

sla:
  gdpr_days: 30
  ccpa_days: 45
  stages:
    ingress:           1h
    verification:      48h
    identity_resolve:  5m
    target_enumerate:  5m
    warehouse_delete:  4h
    timetravel_shrink: 24h
    s3_lake_rewrite:   3d
    redis_invalidate:  5m
    backup_carveout:   14d
    attestation_close: 24h

escalation:
  - stage: verification
    over: 72h
    page: legal_oncall
  - stage: s3_lake_rewrite
    over: 96h
    page: dba_oncall
  - stage: total_elapsed
    over: 25d
    page: dpo_and_legal

Step-by-step trace.

Axis	Delivered artefact	Owned by
Identity resolution	identity-graph query template + service SLO	data-platform
Lineage graph	dbt-catalog walker → canonical target list	data-platform + governance
Delete propagation	per-store delete driver + retention override	data-platform + DBA
Audit + attestation	dsar_attestation_log table + per-store rows	data-platform + legal
SLA orchestration	per-stage budgets + escalation policy	legal-ops + on-call

After the first-week rollout, the 12 pending DSAR requests move from a manual runbook to a driver-based pipeline. Each request finishes in ~17 days on average, well inside the 30-day GDPR window and the 45-day CCPA window. The attestation log gives legal a per-store row-level record that survives regulator scrutiny.

Output:

Metric	Before	After
DSAR runbook	manual, per-request	4-axis driver pipeline
Average time to close	27 days (near-SLA-breach)	17 days
Missed downstream tables per DSAR	6–10	0 (lineage-driven)
Attestation evidence	verbal	per-store row-level log
Escalation policy	none	per-stage + total-elapsed

Why this works — concept by concept:

• Four-axis decomposition — identity, lineage, propagation, audit are orthogonal engineering problems. Trying to solve them together produces spaghetti; solving them separately produces a driver-based pipeline that composes cleanly.
• Lineage as source of truth — dbt catalog / OpenLineage output is the canonical target list. Hand-maintained lists drift on every schema change; the catalog is refreshed on every dbt build.
• Per-store mechanic — Snowflake, BigQuery, S3, Redis, backup each have a different delete verb. A single "delete driver" abstraction with per-store implementations avoids the trap of assuming one mechanic fits all.
• Per-stage SLA budget — the 30-day clock is a total; the pipeline must budget every stage and escalate when a stage overruns. Global counters miss the diagnostic signal that a specific stage is slow.
• Cost — first-week build is roughly 3 senior-DE weeks (identity + lineage + delete driver) plus 1 legal-ops week (attestation schema + dashboard). Ongoing cost is O(DSAR volume) at ~10–30 minutes of automated pipeline runtime per DSAR.

SQL
Topic — sql
SQL DSAR enumeration and delete-target problems

Practice →

ETL Topic — etl ETL problems on GDPR-safe warehouse pipelines

Practice →

---

2. Identity resolution — one user, many keys

One data-subject email fans out to N user_ids, M hashed variants, and K device IDs — resolve identity before you touch a table

The mental model in one line: data subject access request handling starts with an identity-resolution problem — one incoming identifier (usually an email) must fan out to the full set of (user_id, hashed_email, phone_hash, device_id, ...) keys the subject's data was ever written under, because every downstream table is keyed on some subset of that graph. Deletion completeness is bounded by identity-resolution completeness; if you miss a user_id in the graph, you miss every row keyed on it.

The four axes of identity resolution.

• Canonical identifiers. The set of columns that legitimately identify a data subject across the platform. Typically user_id, email (raw and hashed), phone (raw and hashed), device_id, advertising_id, and cookie IDs. The governance team owns the taxonomy; the DSAR pipeline reads it.
• Identity graph. A directed graph where nodes are identifiers and edges are "was observed on the same session / device / login as." Built either in a dedicated node-linking service (Amperity, mParticle) or in-house via SQL / Spark jobs against auth + session data.
• Hashed variants. Modern platforms hash PII for downstream storage (SHA-256 with a salt, SipHash for high-throughput hashing). Every raw identifier has a matching hashed form; deletion must cover both.
• Row-set enumeration. Given the resolved identity keys, the target list of (table, key_column, row_count) triples. Deletion runs per row-set.

Why the resolution matters — three real anti-patterns.

• The single-key trap. A team uses email as the sole key. Users change emails, merge accounts, or sign in with a phone-only flow. The DSAR pipeline deletes by email and misses every row keyed on a different identifier the same subject once used.
• The hashed-variant blind spot. The team hashes email before writing to the warehouse for "privacy." Six months later a DSAR arrives for the raw email. The pipeline queries by email and returns zero rows because the warehouse only stores hashed_email = SHA256(email + salt). Fix: the identity resolution step must compute the hash and query by both.
• The device-ID orphan. A user's device_id is captured in an event stream before login. The events are stored keyed on device_id only. When the user later logs in, the device_id → user_id edge is added to the identity graph, but only if the platform propagates the login event. Missing the propagation leaves anonymous pre-login events orphaned; they survive the DSAR pipeline unless the graph is complete.

Building the identity graph.

• Node types. email, hashed_email, phone, phone_hash, user_id, device_id, advertising_id, cookie_id.
• Edge types. same_session_as (from session logs), logged_in_as (from auth events), hashed_from (deterministic hash relationships), merged_by_admin (from account-merge events).
• Traversal. A DSAR resolution query starts from the incoming identifier node and does a breadth-first traversal, collecting every reachable node.
• Freshness. The graph must be updated at least daily; a stale graph misses recent identity edges and produces incomplete row-sets.

When to buy vs build.

• Buy (Amperity, mParticle, Segment CDP). Managed identity resolution with SLA guarantees, pre-built connectors, and a UI for governance review. Cost is per-record or per-tenant; typical mid-market spend is $50k–500k/year.
• Build in-house. SQL + Spark against auth events and session logs. Ownership stays with the data team; iteration cost is higher; the graph accuracy depends on the team's coverage of every identifier source.
• Hybrid. Buy for cross-platform identity (web + mobile + ads); build for internal-only identity (user-generated warehouse tables).

Common interview probes on identity resolution.

• "How do you handle a user with a raw email and a hashed email?" — the resolution step computes the hash and queries by both.
• "What's your identity-graph freshness SLA?" — daily minimum; hourly for high-volume platforms.
• "How do you audit the identity graph?" — sample-based reviews plus regression tests on known merged accounts.
• "What if the identity graph is wrong?" — the DSAR pipeline has a manual-override step; governance owns the override policy.

Worked example — resolving one email to N keys via identity graph

Detailed explanation. A DSAR arrives for [e-mail]. The identity-graph service is a Snowflake table populated by a nightly Spark job that links (email, user_id, device_id) triples across auth events, session logs, and account-merge events. The resolution query starts from the email node and returns every reachable identifier. The row-set enumeration then joins those keys against the target-table list.

• Input. [e-mail].
• Graph output. user_ids = [42, 89, 137], hashed_emails = [H_ALICE_V1, H_ALICE_V2], device_ids = [D_abc, D_def].
• Row-sets output. ~47 (table, key, row_count) triples across the warehouse.

Question. Write the identity-graph resolution query (Snowflake SQL) that takes an email and returns the full canonical identifier set, plus the row-set enumeration query that produces the (table, key, count) triples.

Input.

Table	Columns
identity_graph.edges	src_id_type, src_id_value, dst_id_type, dst_id_value, edge_type, observed_at
identity_graph.nodes	id_type, id_value, first_seen_at, last_seen_at
governance.dsar_target_tables	fq_table, identity_column_name, key_type

Code.

-- Step 1 — resolve the identity graph starting from an email
WITH RECURSIVE reachable(id_type, id_value, depth) AS (
  -- Seed with the incoming email
  SELECT 'email' AS id_type,
         '[e-mail]' AS id_value,
         0 AS depth
  UNION ALL
  -- Walk outbound edges (bidirectional)
  SELECT e.dst_id_type,
         e.dst_id_value,
         r.depth + 1
  FROM   reachable r
  JOIN   identity_graph.edges e
    ON   (e.src_id_type = r.id_type AND e.src_id_value = r.id_value)
      OR (e.dst_id_type = r.id_type AND e.dst_id_value = r.id_value)
  WHERE  r.depth < 5                         -- cap traversal depth
    AND  e.edge_type IN ('logged_in_as', 'hashed_from', 'same_session_as', 'merged_by_admin')
)
SELECT DISTINCT id_type, id_value
FROM   reachable
ORDER  BY id_type, id_value;
-- email        | [e-mail]
-- hashed_email | H_ALICE_V1
-- hashed_email | H_ALICE_V2
-- user_id      | 42
-- user_id      | 89
-- user_id      | 137
-- device_id    | D_abc
-- device_id    | D_def

-- Step 2 — enumerate row-sets by joining the resolved keys against target tables
CREATE OR REPLACE TEMP TABLE dsar_resolved_keys AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));   -- from step 1 above

-- Row-set enumeration
WITH targets AS (
  SELECT fq_table, identity_column_name, key_type
  FROM   governance.dsar_target_tables
),
counts AS (
  SELECT t.fq_table,
         t.identity_column_name,
         t.key_type,
         (
           SELECT COUNT(*)
           FROM   IDENTIFIER(t.fq_table) tgt
           WHERE  IDENTIFIER(t.identity_column_name) IN (
             SELECT id_value FROM dsar_resolved_keys WHERE id_type = t.key_type
           )
         ) AS row_count
  FROM   targets t
)
SELECT * FROM counts WHERE row_count > 0
ORDER  BY row_count DESC;

Step-by-step explanation.

1. The recursive CTE walks the identity graph outward from the seed email. Every edge type in the whitelist is traversed; edges to hashed variants, merged accounts, and shared sessions all contribute. The depth < 5 cap prevents runaway traversal on pathological graphs.
2. The distinct output is the resolved key set. In the example: 1 email → 2 hashed_emails → 3 user_ids → 2 device_ids = 8 identifiers. The DSAR pipeline now knows every key the subject's data could have been written under.
3. The row-set enumeration joins the resolved keys against the governance-owned target-table list. Each row of the target list says "table X stores key Y of type Z"; the enumeration counts rows in each (table, key) pair.
4. The IDENTIFIER(...) Snowflake function turns runtime strings into identifiers, letting the query iterate over an arbitrary target list without a code generator. Postgres and BigQuery equivalents use dynamic SQL / EXECUTE IMMEDIATE.
5. The output is the canonical delete plan: a list of (fq_table, identity_column, row_count) triples. Sum of row counts is the total affected rows; each row will be individually attested in the audit log.

Output.

fq_table	identity_column	row_count
analytics.mart_customer_360	user_id	3
analytics.stg_events	user_id	12,847
analytics.stg_orders	user_id	47
analytics.raw_events	device_id	25,341
analytics.stg_users	user_id	3
analytics.stg_sessions	user_id	213
analytics.raw_email_hash_map	hashed_email	2

Rule of thumb. The identity-graph query is the foundation of every DSAR pipeline. Get it wrong and every downstream deletion is incomplete. Version the graph, monitor its freshness, and treat regressions as compliance incidents.

Worked example — the hashed-email blind spot

Detailed explanation. A common bug: the platform hashes emails before writing to the warehouse — SHA-256 with a per-tenant salt. The identity-resolution step forgets to compute the hash, queries only by the raw email, and returns rows only from the OLTP layer (which stores raw email). The warehouse layer (which stores only hashed) returns zero rows and the DSAR pipeline reports "no downstream data" — a compliance fail that surfaces months later when a regulator audits.

• The setup. OLTP stores email (raw); warehouse stores hashed_email = SHA256(email || salt).
• The bug. The pipeline queries by email on both.
• The gap. Warehouse deletion count is 0; regulator later finds thousands of hashed_email rows for the subject.

Question. Show the correct identity-resolution query that resolves an incoming raw email to both the raw form and the deterministic hashed form, so that the warehouse tables (which key on the hash) are correctly enumerated.

Input.

Salt storage	Value
Tenant salt (per-tenant secret)	secrets.hashing_salt_for_tenant
Hash algorithm	SHA-256 (single round)
Hash format	hex lowercase

Code.

-- The identity resolution step: raw email in, raw + hashed out
WITH input AS (
  SELECT '[e-mail]' AS raw_email
),
hashed AS (
  SELECT raw_email,
         LOWER(SHA2(raw_email || (SELECT salt_value FROM secrets.tenant_salt WHERE tenant_id = 1), 256)) AS hashed_email
  FROM   input
),
identifiers AS (
  SELECT 'email'        AS id_type, raw_email     AS id_value FROM hashed
  UNION ALL
  SELECT 'hashed_email' AS id_type, hashed_email  AS id_value FROM hashed
)
SELECT * FROM identifiers;
-- email        | [e-mail]
-- hashed_email | b1946ac92492d2347c6235b4d2611184

-- Now feed both id types into the identity-graph walker
WITH RECURSIVE reachable(id_type, id_value, depth) AS (
  SELECT id_type, id_value, 0 FROM identifiers
  UNION ALL
  SELECT e.dst_id_type, e.dst_id_value, r.depth + 1
  FROM   reachable r
  JOIN   identity_graph.edges e
    ON   e.src_id_type = r.id_type AND e.src_id_value = r.id_value
  WHERE  r.depth < 5
)
SELECT DISTINCT id_type, id_value FROM reachable;

Step-by-step explanation.

1. The resolution step computes the deterministic hash of the raw email using the tenant salt. The salt is fetched from a governance-owned secrets table (never hard-coded in the pipeline).
2. The identifiers CTE emits both the raw email and the hashed form as separate rows. Downstream tables that key on either form now have a matching identifier in the resolved set.
3. The graph walker then explores from both seeds. Edges from email → user_id and hashed_email → user_id are both traversed; the final reachable set is the union.
4. The hash function must match exactly the one used by the ingestion pipeline — same algorithm, same salt, same normalisation (usually lowercase + trim). Any drift breaks the resolution and produces silent under-deletion.
5. The salt is per-tenant to prevent cross-tenant identifier leakage. A single global salt would let a hashed identifier from tenant A match the same email in tenant B; a per-tenant salt keeps tenants isolated.

Output.

id_type	id_value	seeded from
email	[e-mail]	raw input
hashed_email	b1946ac92492...	computed
user_id	42	graph walk
user_id	89	graph walk
device_id	D_abc	graph walk

Rule of thumb. If any downstream store hashes PII, the identity-resolution step must compute the same hash. A single unhashed lookup is not sufficient; the hashed form is a first-class identifier in the resolution query.

Worked example — the merged-account graph edge

Detailed explanation. Two users merge accounts (typical customer-support workflow — user emailed support saying "please merge my old account into my new one"). The account-merge event writes an edge user_id=89 merged_into user_id=42 in the identity graph. Six months later a DSAR arrives for [e-mail] (which is linked to user_id=42). Without the merge edge, the pipeline finds user_id=42's data and misses user_id=89's pre-merge data. The merge edge is what closes the gap.

• The setup. Two user_ids exist for one subject; a merge event links them.
• The graph edge. merged_by_admin with metadata {source: 89, target: 42, merged_at: ...}.
• The requirement. The DSAR resolution step must traverse merge edges bidirectionally.

Question. Show the graph-edge representation for account merges, and demonstrate the resolution query correctly returning both pre-merge and post-merge user_ids.

Input.

Event	Source user_id	Target user_id	Timestamp
Account merge	89	42	2024-05-15

Code.

-- The merge edge in the identity graph
INSERT INTO identity_graph.edges
  (src_id_type, src_id_value, dst_id_type, dst_id_value, edge_type, observed_at)
VALUES
  ('user_id', '89', 'user_id', '42', 'merged_by_admin', '2024-05-15 09:15:22'),
  ('user_id', '42', 'user_id', '89', 'merged_by_admin', '2024-05-15 09:15:22');  -- bidirectional

-- Resolution starting from [e-mail] (which is linked to user_id=42)
WITH RECURSIVE reachable(id_type, id_value, depth) AS (
  SELECT 'email' AS id_type, '[e-mail]' AS id_value, 0
  UNION ALL
  SELECT e.dst_id_type, e.dst_id_value, r.depth + 1
  FROM   reachable r
  JOIN   identity_graph.edges e
    ON   e.src_id_type = r.id_type AND e.src_id_value = r.id_value
  WHERE  r.depth < 5
    AND  e.edge_type IN (
           'logged_in_as',
           'hashed_from',
           'same_session_as',
           'merged_by_admin'                 -- critical for merged accounts
         )
)
SELECT DISTINCT id_type, id_value FROM reachable ORDER BY id_type, id_value;
-- email        | [e-mail]
-- user_id      | 42
-- user_id      | 89                          -- merged in — pipeline would miss this without the edge

Step-by-step explanation.

1. The account-merge event writes two edges in the identity graph — one from source to target and one from target to source. Bidirectional edges let the resolution walk in either direction; a unidirectional edge would break resolution starting from the "wrong" side.
2. The resolution query includes merged_by_admin in the edge-type whitelist. Omitting it means merged accounts are invisible to the pipeline — every regulator audit report on a merged subject would find pre-merge data still present.
3. The example starts from [e-mail], which is linked to user_id=42. The walker discovers the merge edge and adds user_id=89 to the reachable set. Now the row-set enumeration includes tables keyed on 89 — the pre-merge data.
4. The pipeline treats both user_ids equally in the delete phase. There's no "primary" and "merged" distinction at deletion time — both must be scrubbed.
5. The audit log records both user_ids and their source (merged_by_admin), so a regulator can see the reasoning trail: "the pipeline discovered user_id=89 via a merge edge dated 2024-05-15."

Output.

Step	Result
Seed	email = [e-mail]
Graph walk depth 1	+ user_id = 42 (logged_in_as)
Graph walk depth 2	+ user_id = 89 (merged_by_admin)
Final resolved set	3 identifiers, covers both pre-merge and post-merge

Rule of thumb. Account-merge edges are the subtle identity-graph feature every DSAR pipeline needs. Without them, merged subjects have pre-merge data that survives every DSAR; regulator audits find it and the fine is proportional to the merge-edge coverage gap.

Senior interview question on identity resolution

A senior interviewer might ask: "Design the identity-resolution component of your DSAR pipeline. Explain the graph model, the traversal, the hashed-variant handling, the merged-account handling, and the SLO you'd set on graph freshness."

Solution Using a bidirectional identity graph with SHA-256 hashed variants and daily refresh

-- Full identity-resolution helper — takes a raw email, returns all resolved keys
CREATE OR REPLACE PROCEDURE governance.resolve_dsar_identity(input_email STRING)
RETURNS TABLE(id_type STRING, id_value STRING)
LANGUAGE SQL
AS
$$
DECLARE
    tenant_salt STRING;
BEGIN
    SELECT salt_value INTO :tenant_salt FROM secrets.tenant_salt WHERE tenant_id = 1;

    LET res RESULTSET := (
      WITH input_ids AS (
        SELECT 'email'        AS id_type, :input_email AS id_value
        UNION ALL
        SELECT 'hashed_email',
               LOWER(SHA2(:input_email || :tenant_salt, 256))
      ),
      RECURSIVE reachable(id_type, id_value, depth) AS (
        SELECT id_type, id_value, 0 FROM input_ids
        UNION ALL
        SELECT e.dst_id_type, e.dst_id_value, r.depth + 1
        FROM   reachable r
        JOIN   identity_graph.edges e
          ON   e.src_id_type = r.id_type AND e.src_id_value = r.id_value
        WHERE  r.depth < 5
          AND  e.edge_type IN (
                 'logged_in_as',
                 'hashed_from',
                 'same_session_as',
                 'merged_by_admin'
               )
      )
      SELECT DISTINCT id_type, id_value FROM reachable
    );
    RETURN TABLE(res);
END;
$$;

-- Nightly graph freshness check
CREATE OR REPLACE VIEW governance.identity_graph_freshness AS
SELECT MAX(observed_at)                              AS latest_edge_ts,
       DATEDIFF('hour', MAX(observed_at), CURRENT_TIMESTAMP()) AS age_hours,
       CASE WHEN DATEDIFF('hour', MAX(observed_at), CURRENT_TIMESTAMP()) > 30
            THEN 'BREACH' ELSE 'OK' END               AS slo_status
FROM   identity_graph.edges;

-- Alert: identity_graph_freshness.slo_status = 'BREACH' → page data-governance-oncall

Step-by-step trace.

Step	Result	Time
Input	[e-mail]	t+0
Compute hashed_email	b1946ac92492...	t+50ms
Graph walk (depth ≤ 5)	8 identifiers reached	t+300ms
Return resolved set	(email, hashed_email × 2, user_id × 3, device_id × 2)	t+320ms
Row-set enumeration	47 (table, key) pairs	t+2s
Total identity-resolution latency		~2 seconds

The stored procedure encapsulates the resolution behind a single call. The DSAR pipeline invokes it per request; the graph is refreshed nightly (with an alert on 30-hour staleness), and the average resolution completes in ~2 seconds. Row-set enumeration adds a second pass but stays under 10 seconds for the entire pipeline.

Output:

Metric	Result
Identifiers resolved per DSAR	5–15
Row-sets enumerated per DSAR	30–80 tables
Identity resolution latency p99	2 seconds
Graph freshness SLO	24 hours (alert at 30 hours)
Merged-account coverage	100% via bidirectional merge edges

Why this works — concept by concept:

• Both raw and hashed seeds — the resolution starts with both the raw email and the deterministic hashed form. Downstream tables that key on either form are covered by construction.
• Bidirectional graph edges — merge edges are written in both directions, so resolution from either side discovers the other. Unidirectional edges are a silent bug in every DSAR pipeline that has ever shipped them.
• Depth-bounded traversal — depth < 5 caps the graph walk. Real identity graphs have small diameter; a depth of 5 covers the legitimate cases without runaway traversal on pathological subgraphs.
• Freshness SLO — 24-hour minimum, alert at 30 hours. A stale graph misses recent merges and logins; the SLO is a compliance surface.
• Cost — O(edges reachable) per resolution. Typical identity graph has 10s of edges per subject; resolution is milliseconds. The dominant cost is the graph refresh job (nightly Spark), not per-DSAR resolution.

SQL
Topic — sql
SQL identity-graph and recursive-CTE problems

Practice →

ETL Topic — etl ETL problems on identity resolution and merge handling

Practice →

---

3. Lineage-driven delete propagation

Use the lineage graph as the delete-target list — and pick hard delete, soft delete, or suppression per table

The mental model in one line: the lineage graph produced by dbt, OpenLineage, or the warehouse catalog is the canonical delete-target list, and the delete strategy per target (hard delete, soft delete + tombstone, or suppression list overlay) is chosen based on the table's role (raw, stg, mart, export) and its retention posture. Trying to enumerate delete targets by hand is a losing race against schema evolution; trying to apply a single delete strategy everywhere ignores the different constraints of raw vs mart vs export tables.

The four axes of delete propagation.

• Lineage source of truth. dbt's manifest.json + catalog.json (built by dbt docs generate), OpenLineage events, or the warehouse's built-in catalog (Snowflake INFORMATION_SCHEMA, BigQuery INFORMATION_SCHEMA). All three converge on the same graph: nodes are datasets, edges are transformations.
• Column-level lineage. Table-level lineage says "table X depends on table Y"; column-level lineage says "column X.a depends on column Y.b." For DSAR, column-level is what you want — knowing that mart_customer_360.hashed_email descends from raw_users.email is what lets the pipeline scrub the mart.
• Delete strategy per node. Hard delete for GDPR-required tables; soft delete with a tombstone for tables where downstream systems expect a row to exist; suppression list for tables where physical deletion is prohibitive (streaming logs, archived aggregates).
• Propagation order. Delete from the leaves in (marts, exports) before deleting from roots (raw), so downstream re-materialisation doesn't accidentally re-hydrate the mart from a still-present raw row.

Delete strategy — hard, soft, or suppression.

• Hard delete. Physical DELETE FROM ... WHERE identity_column IN (...). Rows are gone; the storage engine reclaims space (eventually, after time-travel / vacuum). Preferred for warehouse tables under GDPR.
• Soft delete with tombstone. UPDATE ... SET is_deleted = true, deleted_at = now(), personal_data_columns = NULL. Row exists but personal data is nulled. Useful for tables where downstream joins expect the row primary key to remain (foreign-key integrity, audit trails).
• Suppression list overlay. A suppressed_ids table lists identifiers whose data must be filtered from every downstream read. The physical rows persist; every query joins to the suppression list. Used for streaming logs, append-only event tables, and lakehouse tables where physical rewrite is prohibitively expensive.

Propagation order — leaves before roots.

• The reason. If you delete from raw_users first, an incremental dbt build could re-materialise stg_users and mart_customer_360 from the raw source. If the raw is already deleted, the incremental build sees no changes and leaves the stg / mart rows in place — but a full-refresh build would correctly drop them.
• The correct order. Compute the topological sort of the lineage graph, delete from the leaves (exports, marts) first, then stg, then raw. Halt any incremental builds during the delete window.
• Alternative. Freeze dbt builds during the delete window; delete in any order; unfreeze. Simpler but breaks other scheduled builds.

Column-level lineage — the accuracy dividend.

• The naive approach. "Any table with a user_id column is a target." Coarse but effective for most cases.
• The column-level approach. "Any column that column-level lineage traces back to the source identity column is a target." Catches derived columns that don't literally contain the identifier but descend from it (e.g. hashes-of-hashes).
• The tools. dbt-column-lineage, OpenLineage column-level facets, Marquez, DataHub, Atlan. Modern lineage tools output column-level graphs; older tools output table-level only.

Common interview probes on delete propagation.

• "What's your lineage source of truth?" — dbt catalog + column-level lineage.
• "Why leaves before roots?" — prevent re-hydration during incremental builds.
• "When do you soft-delete instead of hard-delete?" — foreign-key integrity, audit trails, append-only tables.
• "When do you use a suppression list?" — streaming logs, immutable event lakes, large lakehouse Parquet.

Worked example — dbt catalog walk to produce the target list

Detailed explanation. The core of a lineage-driven delete pipeline is a query against dbt's exported catalog. Every dbt run produces manifest.json (the DAG) and catalog.json (the actual column-level metadata from the warehouse). Uploading both to a governance table lets a SQL query produce the canonical target list — every table + column that stores a known identity type.

• Input. dbt catalog.json uploaded to governance.dbt_catalog_columns.
• Input. Identity taxonomy hard-coded (or read from governance.pii_taxonomy).
• Output. (fq_table, identity_column, key_type) target list.

Question. Show the SQL that walks the dbt catalog for every column matching the identity taxonomy and produces the target list. Include column-level lineage using the manifest's columns and depends_on metadata.

Input.

Governance table	Columns
governance.dbt_catalog_columns	node_id, database_name, schema_name, table_name, column_name, data_type
governance.dbt_manifest_columns	node_id, column_name, depends_on_column_id[]
governance.pii_taxonomy	column_name_pattern, key_type

Code.

-- Step 1 — expand the PII taxonomy to concrete column names
WITH pii_patterns AS (
  SELECT column_name_pattern, key_type
  FROM   governance.pii_taxonomy
),
-- Step 2 — every catalog column matching any taxonomy pattern
direct_targets AS (
  SELECT c.node_id,
         c.database_name || '.' || c.schema_name || '.' || c.table_name AS fq_table,
         c.column_name,
         p.key_type
  FROM   governance.dbt_catalog_columns c
  JOIN   pii_patterns p
    ON   REGEXP_LIKE(LOWER(c.column_name), p.column_name_pattern)
),
-- Step 3 — column-level lineage: any column that descends from a direct target
RECURSIVE descendant_targets(node_id, column_name, key_type) AS (
  SELECT node_id, column_name, key_type FROM direct_targets
  UNION ALL
  SELECT m.node_id,
         m.column_name,
         d.key_type
  FROM   descendant_targets d
  JOIN   governance.dbt_manifest_columns m
    ON   ARRAY_CONTAINS(d.node_id || '.' || d.column_name, m.depends_on_column_id)
),
enriched AS (
  SELECT c.database_name || '.' || c.schema_name || '.' || c.table_name AS fq_table,
         c.column_name,
         d.key_type
  FROM   descendant_targets d
  JOIN   governance.dbt_catalog_columns c ON c.node_id = d.node_id AND c.column_name = d.column_name
)
SELECT DISTINCT fq_table, column_name, key_type
FROM   enriched
ORDER  BY fq_table, column_name;

Step-by-step explanation.

1. The PII taxonomy is a table of (column_name_pattern, key_type) rows — ('user_id', 'user_id'), ('.*email.*', 'email'), ('.*device_id.*', 'device_id'), etc. Governance owns this list; adding a new PII column type is a one-row insert.
2. The direct_targets CTE joins every column in the dbt catalog against the taxonomy, using regex match on the column name. This produces the direct hits — every column that literally matches a PII pattern.
3. The recursive descendant_targets CTE follows column-level dependencies in the dbt manifest. If mart_customer_360.email_domain depends on stg_users.email, and stg_users.email is a direct target, then mart_customer_360.email_domain is a descendant target — it stores derived personal data.
4. The recursion terminates when no new descendants are found. Modern dbt projects have column-level lineage in the manifest; older projects have table-level only, in which case the descendant step falls back to "any column in a downstream table."
5. The output is the enriched target list, deduplicated. Typical B2C SaaS produces 50–150 target rows: 30–80 direct + 20–70 descendant.

Output.

fq_table	column_name	key_type	source
analytics.raw_users	email	email	direct
analytics.raw_users	user_id	user_id	direct
analytics.stg_users	hashed_email	email	direct
analytics.mart_customer_360	user_id	user_id	direct
analytics.mart_customer_360	email_domain	email	descendant
analytics.mart_lifetime_value	user_id	user_id	descendant

Rule of thumb. Column-level lineage catches the 20–30% of PII columns that don't literally match a PII pattern but descend from one (email domains, hashed-of-hashed, derived features). Table-level lineage misses these; regulator audits find them.

Worked example — leaves-before-roots propagation order

Detailed explanation. The classic bug: the pipeline deletes from raw_users first, then the nightly incremental dbt build runs, sees no new source changes, and leaves the mart rows in place. Regulator audits find the mart rows six months later. The fix is to compute the topological sort of the lineage DAG and delete from the leaves inward. A simpler alternative — freeze dbt builds during the delete window — is often the pragmatic choice.

• The DAG. raw_users → stg_users → mart_customer_360 → export_customer_360.
• The wrong order. raw → stg → mart → export.
• The right order. export → mart → stg → raw.

Question. Show the topological sort SQL and the resulting delete order for a mid-size lineage graph.

Input.

Node	Depends on
export_customer_360	mart_customer_360
mart_customer_360	stg_users, stg_orders
mart_lifetime_value	mart_customer_360
stg_users	raw_users
stg_orders	raw_orders
raw_users	(source)
raw_orders	(source)

Code.

-- Compute topological order: leaves (highest depth) first
WITH RECURSIVE depth(node_id, depth) AS (
  -- Roots: nodes that depend on nothing (or on external sources)
  SELECT node_id, 0
  FROM   governance.dbt_manifest_nodes
  WHERE  NOT EXISTS (
    SELECT 1 FROM governance.dbt_manifest_deps d WHERE d.node_id = governance.dbt_manifest_nodes.node_id
  )
  UNION ALL
  SELECT d.node_id, p.depth + 1
  FROM   depth p
  JOIN   governance.dbt_manifest_deps d ON d.depends_on = p.node_id
),
max_depth AS (
  SELECT node_id, MAX(depth) AS d FROM depth GROUP BY node_id
)
SELECT node_id, d AS depth
FROM   max_depth
ORDER  BY d DESC;                             -- leaves first

# Delete driver — leaves first
def delete_in_topological_order(pipeline, resolved_keys, target_list):
    # target_list already carries (fq_table, column, depth) triples
    ordered = sorted(target_list, key=lambda t: -t["depth"])   # leaves first
    for target in ordered:
        counts = pipeline.hard_delete(
            fq_table=target["fq_table"],
            column=target["column"],
            keys=resolved_keys[target["key_type"]],
        )
        pipeline.attest(
            store="snowflake",
            fq_table=target["fq_table"],
            deleted_row_count=counts,
        )

Step-by-step explanation.

1. The topological-sort recursive CTE assigns a depth to every node based on distance from the source. Root nodes (no dependencies) get depth 0; downstream nodes inherit depth = 1 + max(depth of parents).
2. Sorting by depth DESC gives leaves-first order. export_customer_360 (depth 3) is deleted first, then mart_lifetime_value (depth 2), then mart_customer_360 (depth 2), then stg_users / stg_orders (depth 1), then raw_users / raw_orders (depth 0).
3. This order prevents re-hydration by incremental dbt builds. Even if a build runs mid-delete, the raw is still present when the mart delete runs, so the mart delete sees the same source and completes normally. The subsequent raw delete removes the source; a future incremental build finds nothing new and correctly propagates the deletion.
4. The alternative — freeze dbt builds — is often simpler. Set dbt run to skip scheduled runs while a dsar_in_progress flag is set. Delete in any order. Unfreeze at the end. The trade-off is a delay in other scheduled builds; for most teams this is acceptable during a DSAR window.
5. The attest step captures the per-table row count. This is the audit evidence the regulator wants: a per-table timestamped record of "deleted N rows keyed on these identifiers at time T."

Output.

Order	fq_table	depth	Rationale
1	export_customer_360	3	leaf export
2	mart_lifetime_value	2	leaf mart
3	mart_customer_360	2	mart
4	stg_users	1	stg
5	stg_orders	1	stg
6	raw_users	0	raw source
7	raw_orders	0	raw source

Rule of thumb. Delete from leaves inward, or freeze incremental builds during the DSAR window. Either strategy prevents mid-pipeline re-hydration; picking the wrong order is a silent under-deletion bug.

Worked example — suppression list overlay for immutable event lakes

Detailed explanation. Some stores cannot be hard-deleted without an expensive rewrite. Streaming logs (Kafka, Kinesis) are strictly append-only; S3 lake Parquet files require reading, filtering, and rewriting the entire file to remove a single row; long-tail archived tables sit on cold storage where deletion is not cost-effective. For these stores, the industry pattern is a suppression list — a governance-owned table listing every identifier that must be filtered out of every read. The physical rows persist; the read layer enforces the deletion.

• The store. S3 lake s3://events/YYYY/MM/DD/*.parquet — thousands of files, each 100+ MB.
• The physical delete cost. Rewriting every file is measured in hours-to-days and CPU-dollars.
• The suppression alternative. A governance.suppressed_ids table joined into every read.

Question. Show the suppression-list table schema, the enforcement pattern in a query layer (dbt macro / Trino filter), and the annual-compaction schedule that eventually removes suppressed rows physically.

Input.

Component	Value
Lake format	Parquet
Query engine	Trino (or Athena)
Rewrite budget	quarterly (annual for the largest partitions)

Code.

-- Suppression list table
CREATE TABLE governance.suppressed_ids (
  id_type       VARCHAR NOT NULL,           -- user_id / hashed_email / device_id
  id_value      VARCHAR NOT NULL,
  dsar_request_id VARCHAR NOT NULL,
  suppressed_at TIMESTAMP NOT NULL,
  reason        VARCHAR,                    -- 'gdpr_article_17' / 'ccpa_section_1798_105'
  PRIMARY KEY (id_type, id_value)
);

-- On DSAR: insert every resolved key into the suppression list
INSERT INTO governance.suppressed_ids (id_type, id_value, dsar_request_id, suppressed_at, reason)
SELECT id_type, id_value, 'DSAR-2026-07-04-alice', CURRENT_TIMESTAMP, 'gdpr_article_17'
FROM   governance.resolve_dsar_identity('[e-mail]');

-- Enforcement — every read against the lake joins to the suppression list
CREATE OR REPLACE VIEW analytics.events_gdpr_safe AS
SELECT e.*
FROM   analytics.events e
LEFT JOIN governance.suppressed_ids s
       ON   (s.id_type = 'user_id'      AND s.id_value = CAST(e.user_id AS VARCHAR))
         OR (s.id_type = 'device_id'    AND s.id_value = e.device_id)
         OR (s.id_type = 'hashed_email' AND s.id_value = e.hashed_email)
WHERE  s.id_value IS NULL;                  -- exclude suppressed rows

# Compaction schedule — physical delete during quarterly rewrites
compaction:
  cadence: quarterly
  scope: hot partitions (last 90 days)
  operation: |
    Read every partition, filter out any row where any identity_column value
    matches governance.suppressed_ids, write back as new Parquet files, delete
    old files. Update the lake catalog.
  annual_scope: cold partitions (older than 12 months)

Step-by-step explanation.

1. The suppression list is a compact governance-owned table — one row per suppressed (id_type, id_value) pair. On a DSAR, every resolved key is inserted; the physical lake data is not immediately modified.
2. Every read against the lake goes through the events_gdpr_safe view (or an equivalent Trino filter). The view left-joins the suppression list and excludes any row that matches; readers see the suppressed data as if it were deleted.
3. The compaction schedule catches up with the physical delete on a quarterly cadence. Every 90 days, the hot partitions are rewritten; suppressed rows are physically removed. Cold partitions (older than 12 months) are rewritten annually.
4. The trade-off: reads pay a small join cost (the suppression list is tiny, typically < 100k rows, and easily fits in memory); the physical delete happens on a batch schedule instead of per-request.
5. GDPR permits this pattern as long as (a) the suppression is effective at read time (which the view enforces), (b) the physical delete happens within a documented policy window (quarterly, annual for cold), and (c) the DSAR audit log records the suppression event. The pattern is common at scale — Netflix, Uber, and Meta all describe variants of it publicly.

Output.

Path	Cost	Latency to effective delete
Hard delete every partition	6h – 3 days	measured in days
Suppression + quarterly compaction	seconds per DSAR	quarterly compaction cadence
Suppression + annual compaction (cold)	seconds per DSAR	annual compaction cadence

Rule of thumb. Use suppression list overlay for immutable event lakes and streaming logs; document the physical-delete cadence in the DSAR policy; regulators accept the pattern when it's documented and effective at read time. Never let a suppression list become a permanent excuse to skip physical delete.

Senior interview question on delete propagation

A senior interviewer might ask: "You have a Snowflake warehouse with raw/stg/mart layers, an S3 event lake, and a Kafka log. Walk me through how you'd propagate a DSAR delete across all four, including the lineage source of truth, the propagation order, and the strategy per store."

Solution Using leaves-first hard delete for warehouse + suppression list for lake and log

# Full delete-propagation driver
from typing import Dict, List

def dsar_delete_propagation(dsar_request_id: str, resolved_keys: Dict[str, List[str]]):
    """
    resolved_keys = {'user_id': [...], 'hashed_email': [...], 'device_id': [...]}
    """
    # Step 1 — enumerate targets via lineage
    targets = enumerate_targets_from_dbt_catalog()   # returns [(fq_table, col, key_type, depth), ...]

    # Step 2 — freeze incremental dbt builds
    set_dbt_dsar_freeze(active=True, dsar_request_id=dsar_request_id)

    # Step 3 — hard-delete warehouse targets in leaves-first order
    ordered = sorted(targets, key=lambda t: -t["depth"])
    for target in ordered:
        keys_for_type = resolved_keys[target["key_type"]]
        deleted_count = snowflake_hard_delete(target["fq_table"], target["col"], keys_for_type)
        attest_delete(
            dsar_request_id=dsar_request_id,
            store="snowflake",
            fq_table=target["fq_table"],
            deleted_row_count=deleted_count,
        )

    # Step 4 — Snowflake time-travel shrink
    for target in ordered:
        snowflake_shrink_time_travel(target["fq_table"])
    attest_delete(dsar_request_id, "snowflake_time_travel", "shrunk", 0)

    # Step 5 — suppression-list overlay for S3 lake + Kafka log
    suppression_rows = 0
    for id_type, ids in resolved_keys.items():
        suppression_rows += insert_suppression_list(id_type, ids, dsar_request_id)
    attest_delete(dsar_request_id, "s3_lake_suppression", "inserted", suppression_rows)
    attest_delete(dsar_request_id, "kafka_log_suppression", "inserted", suppression_rows)

    # Step 6 — unfreeze
    set_dbt_dsar_freeze(active=False, dsar_request_id=dsar_request_id)

    # Step 7 — return attestation summary
    return read_attestation_log(dsar_request_id)

Step-by-step trace.

Step	Store	Action	Attested row count
1	governance	enumerate targets	47 (fq_table, col) pairs
2	dbt	freeze incremental builds	—
3	Snowflake	hard-delete leaves-first	38,721 rows across 47 tables
4	Snowflake	time-travel shrink	—
5a	S3 lake	insert suppression list	8 identifiers
5b	Kafka log	insert suppression list	8 identifiers
6	dbt	unfreeze	—
7	audit	return attestation summary	47 rows across 6 stores

The pipeline runs end-to-end in ~4 hours for a mid-sized subject (~40k rows across the warehouse). The suppression list handles the S3 lake and Kafka log without an expensive rewrite; the quarterly compaction eventually completes the physical delete.

Output:

Store	Mechanism	DSAR-time cost	Physical-delete latency
Snowflake raw / stg / mart	hard delete + time-travel shrink	3-4 hours	immediate + 1 day for time-travel
S3 event lake	suppression list overlay	seconds	quarterly compaction
Kafka log	suppression list overlay	seconds	7-day retention TTL
Postgres OLTP	hard delete	seconds	immediate
Redis feature store	DEL on keys	seconds	immediate

Why this works — concept by concept:

• Lineage as source of truth — dbt catalog walk produces the target list. Newly-added tables auto-enrol; retired tables auto-drop. The list is never stale.
• Leaves-first order — topological sort prevents re-hydration by incremental builds. The freeze flag is a belt-and-braces safety net for teams whose builds run at unpredictable times.
• Per-store mechanic — warehouse tables get hard delete + time-travel shrink; lake and log get suppression + scheduled compaction. Matching mechanic to store is the difference between a compliant pipeline and a hand-waved one.
• Attestation per step — every action writes a row to the attestation log. The log is the regulator-facing evidence; without it, the compliance argument is verbal.
• Cost — O(target list × avg rows per target) for the warehouse hard delete; O(suppressed identifiers) for the lake / log overlay. Total DSAR cost typically 3–5 hours of pipeline runtime plus quarterly compaction amortised across all DSARs.

ETL
Topic — etl
ETL delete-propagation and lineage problems

Practice →

Optimization Topic — optimization Optimization problems on delete-order and suppression

Practice →

---

4. Deletion mechanics per store

One recipe per store — Snowflake DELETE + PURGE, BigQuery DELETE + partition expire, S3 lifecycle + delete markers, backup retention policy

The mental model in one line: each storage engine exposes a different delete verb, retention model, and completion signal — the DSAR pipeline runs the correct mechanic per store, waits for the correct completion signal, and produces a per-store attestation record. Getting the mechanic wrong is one of the two most common causes of a DSAR compliance failure (the other is missing tables from the target list).

The four axes of per-store deletion.

• Delete verb. SQL DELETE, S3 DeleteObject, Kafka log retention, Redis DEL, backup vault carve-out — the store's own delete primitive.
• Retention window. Time-travel (Snowflake), snapshot history (BigQuery), object versioning (S3), retention policy (Kafka, backup vault) — the store's own "how long is the deleted data still recoverable" window. Under GDPR, this window must be respected or overridden.
• Completion signal. The store's confirmation that deletion is complete and the retention window has cleared. Snowflake time-travel shrink, BigQuery partition-expire timer, S3 lifecycle transition — each different.
• Attestation output. The per-store audit record: timestamp, deleted-row count, actor, request ID, keys hash. The attestation is the regulator-facing evidence.

Snowflake — DELETE + PURGE with time-travel shrink.

• The delete verb. DELETE FROM table WHERE identity_column IN (...).
• The retention window. Time-travel (default 1 day, up to 90 for Enterprise Edition) plus Fail-safe (7 additional days, Enterprise+ only).
• The DSAR override. ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 before the delete, then ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 1 after. Fail-safe cannot be shrunk; it's a 7-day fixed window managed by Snowflake.
• The completion signal. Time-travel window elapses. For strict compliance, the DSAR pipeline waits the full retention window (0 days if overridden) + Fail-safe (7 days) before marking the store attestation "complete."
• The attestation. Row count from the DELETE return, plus a timestamp of when Fail-safe would clear.

BigQuery — DELETE + partition expire.

• The delete verb. DELETE FROM table WHERE identity_column IN (...).
• The retention window. Time-travel (7 days, non-configurable in most tiers). Table-level snapshot history depends on the project's snapshot policy.
• The DSAR override. BigQuery time-travel cannot be disabled at the table level. For DSAR, the pipeline waits the 7-day window, then can optionally recreate the table from a fresh CTAS to purge time-travel history early.
• Partition expire. For time-partitioned tables, ALTER TABLE ... SET OPTIONS (partition_expiration_days = N) sets a physical delete cadence. Useful for retention policies where old data is auto-purged.
• The attestation. Row count from DELETE, plus a note on the 7-day time-travel window clearing.

S3 lake — lifecycle rules + delete markers.

• The delete verb. For versioned buckets, DeleteObject writes a delete marker; the original version remains until versioning expiry. For unversioned buckets, DeleteObject is immediate.
• The retention window. Versioning lifecycle rules control when non-current versions expire. Typical policy: expire non-current versions after 30 days.
• The DSAR override. For versioned buckets, set a lifecycle rule that expires all versions of the deleted object within 24 hours for DSAR-affected paths. Alternative: mark the bucket unversioned for the DSAR window (usually not acceptable operationally).
• The completion signal. Lifecycle rule fires; S3 removes the physical bytes.
• The attestation. Object list before delete, object list after delete, lifecycle-rule confirmation.

Backup vault — retention policy carve-out.

• The delete verb. Depends on the backup product. AWS Backup: DeleteRecoveryPoint. Postgres pg_dump snapshots on S3: DeleteObject on the snapshot. Snowflake Time-Travel + Fail-safe: cannot be individually purged.
• The retention window. Set by policy — typical retention is 7-30 days for hot backups, 90 days to 7 years for compliance archives.
• The DSAR strategy — two options. (1) Hard delete from backups: physically remove the backup that contains the subject's data. Only feasible if backups are small and per-tenant. (2) Documented retention window: leave the backup in place, document that the subject's data will be physically purged when the backup naturally ages out. GDPR permits option 2 with a written policy justification.
• The attestation. Either the deletion record (option 1) or the policy justification and expected purge date (option 2).

Streaming logs (Kafka, Kinesis) — retention policy + suppression overlay.

• The delete verb. Kafka: kafka-delete-records.sh to advance the log start offset. Kinesis: no per-record delete; wait for retention.
• The retention window. Kafka: retention.ms (default 7 days). Kinesis: 24 hours to 365 days depending on tier.
• The DSAR strategy. For short-retention logs (7-day Kafka), the pipeline inserts a suppression-list entry and lets the 7-day retention naturally purge. For longer-retention streams, use compaction or per-record delete where available.
• The attestation. Suppression-list insertion timestamp plus expected retention-clear date.

Common interview probes on per-store deletion.

• "How do you handle Snowflake time-travel for DSAR?" — SET DATA_RETENTION_TIME_IN_DAYS = 0 for the DSAR window, then restore.
• "Do you delete from backups?" — either hard delete (small backups) or documented retention window with policy justification.
• "How do you handle S3 versioning?" — lifecycle rule that expires versions within 24 hours for DSAR-affected paths.
• "What's the Snowflake Fail-safe window?" — 7 days, cannot be shrunk, must be documented.

Worked example — Snowflake DELETE + time-travel shrink

Detailed explanation. Snowflake DELETE removes rows from the current version but leaves them recoverable via time-travel for up to 90 days (Enterprise Edition) or 1 day (Standard Edition). For GDPR compliance the time-travel window must be either shrunk or waited out. Additionally, Fail-safe (Enterprise+) provides an extra 7 days of recovery managed by Snowflake support; it cannot be shrunk. Walk through the full delete + shrink pattern with attestation capture.

• The setup. Snowflake Enterprise, DATA_RETENTION_TIME_IN_DAYS = 7 on target tables.
• The pattern. Shrink → delete → wait for Fail-safe → attest.

Question. Show the full DDL + DML for a compliant Snowflake DSAR delete, with attestation capture and Fail-safe documentation.

Input.

Table	Current retention	Post-DSAR retention
analytics.stg_users	7 days	0 days (temporary) → 7 days (restored)
analytics.mart_customer_360	7 days	0 days (temporary) → 7 days (restored)

Code.

-- Step 1 — shrink time-travel to 0 for the target tables
ALTER TABLE analytics.stg_users             SET DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE analytics.mart_customer_360     SET DATA_RETENTION_TIME_IN_DAYS = 0;

-- Step 2 — hard delete
BEGIN TRANSACTION;

DELETE FROM analytics.stg_users
WHERE  user_id IN (42, 89, 137);
-- Capture the affected row count for attestation
SET stg_users_deleted := SQLROWCOUNT;

DELETE FROM analytics.mart_customer_360
WHERE  user_id IN (42, 89, 137);
SET mart_customer_360_deleted := SQLROWCOUNT;

COMMIT;

-- Step 3 — write attestation
INSERT INTO governance.dsar_attestation_log
  (dsar_request_id, store, fq_table, deleted_row_count, actor, keys_hash, attested_at)
VALUES
  ('DSAR-2026-07-04-alice',
   'snowflake',
   'analytics.stg_users',
   $stg_users_deleted,
   CURRENT_USER(),
   SHA2('42|89|137', 256),
   CURRENT_TIMESTAMP()),
  ('DSAR-2026-07-04-alice',
   'snowflake',
   'analytics.mart_customer_360',
   $mart_customer_360_deleted,
   CURRENT_USER(),
   SHA2('42|89|137', 256),
   CURRENT_TIMESTAMP());

-- Step 4 — restore normal retention (after Fail-safe window)
-- schedule for T + 7 days
ALTER TABLE analytics.stg_users             SET DATA_RETENTION_TIME_IN_DAYS = 7;
ALTER TABLE analytics.mart_customer_360     SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Step 5 — document Fail-safe (Enterprise+ only)
INSERT INTO governance.dsar_failsafe_documentation
  (dsar_request_id, store, note, expected_purge_at)
VALUES
  ('DSAR-2026-07-04-alice',
   'snowflake',
   'Fail-safe retention is 7 days managed by Snowflake and cannot be shrunk. Physical purge complete by T+7.',
   DATEADD('day', 7, CURRENT_TIMESTAMP()));

Step-by-step explanation.

1. ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 shrinks time-travel to zero for the target tables only. Setting this globally is disruptive to other operations; the DSAR pipeline shrinks per-table and restores after.
2. The DELETE runs inside a single transaction so partial failure rolls back cleanly. SQLROWCOUNT captures the affected row count for attestation; this is the regulator-facing evidence of "N rows deleted."
3. The attestation log gets one row per deleted table, with the row count, the actor, a hash of the affected keys (never the keys themselves — the log is queried by many teams), and a timestamp.
4. The retention restoration is scheduled for T+7 days (after Fail-safe naturally clears). During the 7 days, the table is unrecoverable via time-travel; production tolerance for this depends on the team's policies.
5. Fail-safe (Enterprise+) is a Snowflake-managed 7-day window for disaster recovery. It cannot be shrunk or accessed by users. GDPR compliance requires documenting the window; regulators accept documented policy limitations of the underlying storage engine.

Output.

Step	Result
Time-travel shrink	0 days per target table
DELETE (stg_users)	3 rows
DELETE (mart_customer_360)	3 rows
Attestation log rows	2 (one per table)
Fail-safe purge	scheduled at T+7
Retention restore	scheduled at T+7

Rule of thumb. Snowflake DSAR = shrink time-travel + delete + attest + document Fail-safe. Fail-safe is the surprise — Enterprise+ customers must document it; Standard Edition customers don't have Fail-safe and can complete DSAR in one step.

Worked example — S3 lifecycle rule + delete marker

Detailed explanation. S3 versioned buckets require a special DSAR pattern. DeleteObject on a versioned bucket writes a delete marker — the current version becomes "deleted," but the previous versions remain in the bucket. Full deletion requires either explicit DeleteObject on every version or a lifecycle rule that expires non-current versions. For DSAR compliance the pipeline sets a targeted lifecycle rule that expires all versions of the affected object within 24 hours.

• The setup. Versioned S3 bucket for lake data.
• The pattern. Identify affected objects, put a targeted lifecycle rule, wait for lifecycle transition, attest.

Question. Show the boto3 code that identifies affected objects for a DSAR, puts a lifecycle rule scoped to the affected prefixes, and captures the attestation.

Input.

Component	Value
Bucket	events-lake (versioned)
Affected prefix	events/2026/07/04/
DSAR request ID	DSAR-2026-07-04-alice

Code.

import boto3
from datetime import datetime, timedelta

s3 = boto3.client("s3")

BUCKET = "events-lake"
DSAR_ID = "DSAR-2026-07-04-alice"

# Step 1 — enumerate affected object versions
def enumerate_affected_objects(prefix: str) -> list:
    """List every object + version that matches the affected prefix."""
    paginator = s3.get_paginator("list_object_versions")
    affected = []
    for page in paginator.paginate(Bucket=BUCKET, Prefix=prefix):
        for v in page.get("Versions", []):
            affected.append({"Key": v["Key"], "VersionId": v["VersionId"]})
        for m in page.get("DeleteMarkers", []):
            affected.append({"Key": m["Key"], "VersionId": m["VersionId"]})
    return affected

affected = enumerate_affected_objects("events/2026/07/04/")

# Step 2 — put a lifecycle rule scoped to the DSAR prefix that expires everything
lifecycle_rule = {
    "Rules": [
        {
            "ID": f"dsar-{DSAR_ID}",
            "Status": "Enabled",
            "Filter": {"Prefix": "events/2026/07/04/"},
            "Expiration": {"Days": 1},
            "NoncurrentVersionExpiration": {"NoncurrentDays": 1},
            "AbortIncompleteMultipartUpload": {"DaysAfterInitiation": 1},
        }
    ]
}

s3.put_bucket_lifecycle_configuration(
    Bucket=BUCKET,
    LifecycleConfiguration=lifecycle_rule,
)

# Step 3 — attest
attestation = {
    "dsar_request_id": DSAR_ID,
    "store": "s3_lake",
    "bucket": BUCKET,
    "prefix": "events/2026/07/04/",
    "affected_object_count": len(affected),
    "lifecycle_rule_id": f"dsar-{DSAR_ID}",
    "expected_purge_at": (datetime.utcnow() + timedelta(days=2)).isoformat(),
    "actor": boto3.client("sts").get_caller_identity()["Arn"],
    "attested_at": datetime.utcnow().isoformat(),
}
write_attestation(attestation)   # to governance.dsar_attestation_log

Step-by-step explanation.

1. The pipeline enumerates every affected object version — including delete markers — using list_object_versions. Versioned buckets can have many versions per key; a naive list_objects misses them all.
2. The lifecycle rule is scoped to the DSAR-affected prefix using Filter.Prefix. The rule expires the current version (Expiration.Days = 1) and every non-current version (NoncurrentVersionExpiration.NoncurrentDays = 1) within 24 hours.
3. Lifecycle rules take up to 24 hours to fire from the moment they're applied. The DSAR pipeline records the expected purge time as T + 48 hours for safety and monitors S3 to confirm the purge.
4. The attestation captures the affected object count, the lifecycle rule ID, and the expected purge time. On the follow-up run (T+48h), the pipeline re-lists the prefix and confirms zero objects; that second attestation is the physical-delete confirmation.
5. For unversioned buckets, the pattern is simpler — a direct DeleteObject on every affected key. For versioned buckets, the lifecycle rule is the canonical approach; without it, a DeleteObject just writes a delete marker and leaves the version data recoverable.

Output.

Step	Result
Affected objects enumerated	3,241 objects across 12 partitions
Lifecycle rule applied	dsar-DSAR-2026-07-04-alice
Expected purge at	T+48 hours
Attestation record	1 row in governance.dsar_attestation_log
Follow-up confirmation	zero objects at T+48 → second attestation row

Rule of thumb. S3 versioned buckets require the lifecycle-rule + delete-marker pattern. A naive DeleteObject leaves non-current versions recoverable; regulator audits find them. Always follow up at T+48 hours to confirm physical delete.

Worked example — backup carve-out with retention justification

Detailed explanation. Backups are the most subtle GDPR corner. Physically deleting the subject's data from every backup snapshot is technically possible but operationally expensive — most backup formats are opaque blobs, and restoring, filtering, and re-writing every snapshot is prohibitive at scale. GDPR permits a "documented retention window" pattern where the deletion is deferred until the backup naturally ages out, provided the policy is written, effective at restore time (backups are never used to restore deleted subjects' data), and audited.

• The setup. AWS Backup vault holding Postgres snapshots and Snowflake failure exports. Retention is 30 days.
• The pattern. Log a policy justification; add the subject to a restoration-block list; wait for natural aging.

Question. Show the policy record, the restoration-block list, and the audit query that proves the backup carve-out is effective.

Input.

Component	Value
Backup vault	analytics-backups
Retention	30 days
Restoration block table	governance.backup_restoration_blocks

Code.

-- Restoration block: on any backup restore, the pipeline reads this table
-- and refuses to restore data associated with any blocked identifier
CREATE TABLE governance.backup_restoration_blocks (
  id_type          VARCHAR NOT NULL,
  id_value         VARCHAR NOT NULL,
  dsar_request_id  VARCHAR NOT NULL,
  blocked_at       TIMESTAMP NOT NULL,
  expected_natural_purge_at TIMESTAMP NOT NULL,
  reason           VARCHAR,
  PRIMARY KEY (id_type, id_value)
);

-- On DSAR: insert every resolved key into the restoration-block list
INSERT INTO governance.backup_restoration_blocks
  (id_type, id_value, dsar_request_id, blocked_at, expected_natural_purge_at, reason)
SELECT id_type,
       id_value,
       'DSAR-2026-07-04-alice',
       CURRENT_TIMESTAMP,
       DATEADD('day', 30, CURRENT_TIMESTAMP),          -- backup retention window
       'gdpr_article_17_documented_retention_window'
FROM   governance.resolve_dsar_identity('[e-mail]');

# The backup restore procedure — every restore consults the block list
restore_procedure:
  step_1_read_block_list:
    query: SELECT id_type, id_value FROM governance.backup_restoration_blocks
  step_2_filter_restored_data:
    for_each_table_restored:
      DELETE FROM restored.<table> WHERE <identity_col> IN (<blocked_values>)
  step_3_attest:
    write_to: governance.dsar_attestation_log
    fields:
      - dsar_request_id
      - store: backup_restore
      - action: filtered_on_restore
      - filtered_row_count

# Audit — confirm every blocked identifier has an expected natural purge date
def audit_backup_blocks() -> dict:
    blocked = query("SELECT id_type, id_value, expected_natural_purge_at FROM governance.backup_restoration_blocks WHERE expected_natural_purge_at > CURRENT_TIMESTAMP")
    total = len(blocked)
    latest_purge = max((b["expected_natural_purge_at"] for b in blocked), default=None)
    return {
        "blocked_count": total,
        "latest_expected_purge_at": latest_purge,
        "policy": "documented_retention_window",
    }

Step-by-step explanation.

1. The restoration-block table lists every identifier that must be filtered out if a backup is ever restored. This is the mechanism that makes the "documented retention window" pattern effective — even if the physical backup contains the subject's data, no restore can bring it back into production.
2. Every backup restore procedure consults the block list before returning the restored data to production. A simple DELETE FROM restored.<table> WHERE identity IN (blocked) runs immediately after restore; the subject's data never re-enters production.
3. The expected natural purge date is computed from the backup retention (30 days). After 30 days, the backup itself is deleted by the retention policy, and the block entry can be removed — the physical purge is complete.
4. The audit query confirms every blocked identifier has a bounded purge date. Regulator asks "when will [e-mail]'s data be physically deleted from backups?" — the answer is the expected_natural_purge_at timestamp.
5. GDPR accepts this pattern because (a) the block list makes the deletion effective at restore time, (b) the physical delete happens within a bounded and documented window, and (c) the pattern is auditable end-to-end. The alternative — physically deleting every backup snapshot — is orders of magnitude more expensive without additional compliance benefit.

Output.

Field	Value
Blocked identifiers	8
Backup retention	30 days
Expected natural purge at	T + 30 days
Block list effective at restore	yes (procedure-enforced)
Attestation record	1 row per DSAR + expected purge date

Rule of thumb. Backups are the GDPR corner case. Use documented retention windows with a restoration-block list unless backups are small and per-tenant. Regulators accept the pattern when the policy is written and the block list is procedure-enforced.

Senior interview question on per-store delete mechanics

A senior interviewer might ask: "You have Snowflake (raw + stg + mart), S3 versioned lake, AWS Backup vault with 30-day retention, and Redis feature store. Walk me through the delete mechanic for each, the retention window, the completion signal, and the attestation."

Solution Using per-store drivers with matched completion signals

# Full per-store delete driver
class DsarDeleteDriver:
    def snowflake(self, fq_table: str, keys: list) -> dict:
        conn = snowflake_conn()
        cur = conn.cursor()
        cur.execute(f"ALTER TABLE {fq_table} SET DATA_RETENTION_TIME_IN_DAYS = 0")
        cur.execute(f"DELETE FROM {fq_table} WHERE user_id IN %s", (tuple(keys),))
        deleted = cur.rowcount
        cur.execute(f"ALTER TABLE {fq_table} SET DATA_RETENTION_TIME_IN_DAYS = 1")
        return {
            "store": "snowflake",
            "fq_table": fq_table,
            "deleted_row_count": deleted,
            "fail_safe_purge_at": now() + timedelta(days=7),
        }

    def bigquery(self, fq_table: str, keys: list) -> dict:
        bq = bigquery_client()
        job = bq.query(
            f"DELETE FROM `{fq_table}` WHERE user_id IN UNNEST(@keys)",
            job_config=QueryJobConfig(query_parameters=[ArrayQueryParameter("keys", "INT64", keys)]),
        )
        job.result()
        deleted = job.num_dml_affected_rows
        return {
            "store": "bigquery",
            "fq_table": fq_table,
            "deleted_row_count": deleted,
            "time_travel_purge_at": now() + timedelta(days=7),
        }

    def s3_versioned(self, bucket: str, prefix: str, dsar_id: str) -> dict:
        s3 = boto3.client("s3")
        # Put lifecycle rule
        s3.put_bucket_lifecycle_configuration(
            Bucket=bucket,
            LifecycleConfiguration={
                "Rules": [{
                    "ID": f"dsar-{dsar_id}",
                    "Status": "Enabled",
                    "Filter": {"Prefix": prefix},
                    "Expiration": {"Days": 1},
                    "NoncurrentVersionExpiration": {"NoncurrentDays": 1},
                }]
            },
        )
        return {
            "store": "s3_lake",
            "bucket": bucket,
            "prefix": prefix,
            "physical_purge_at": now() + timedelta(hours=48),
        }

    def redis(self, keys: list) -> dict:
        r = redis.Redis()
        deleted = 0
        for k in keys:
            deleted += r.delete(f"feat:user:{k}")
        return {"store": "redis", "deleted_key_count": deleted}

    def backup_carveout(self, resolved_keys: dict, dsar_id: str) -> dict:
        # insert into governance.backup_restoration_blocks
        rows_inserted = insert_backup_blocks(resolved_keys, dsar_id)
        return {
            "store": "backup_vault",
            "blocked_identifier_count": rows_inserted,
            "expected_natural_purge_at": now() + timedelta(days=30),
            "policy": "documented_retention_window",
        }

Step-by-step trace.

Store	Verb	Retention window	Physical purge signal
Snowflake	DELETE + shrink DATA_RETENTION_TIME_IN_DAYS	0d time-travel + 7d Fail-safe	T+7 days
BigQuery	DELETE	7d time-travel	T+7 days
S3 versioned	put_bucket_lifecycle_configuration	1d version expiry	T+48 hours
Redis	DEL	none	immediate
Backup vault	restoration-block list	policy retention (30d)	T+30 days

Every store returns a structured attestation record. The DSAR pipeline aggregates these into a per-DSAR row set in governance.dsar_attestation_log; the legal team reads the log to close the DSAR ticket.

Output:

Store	Attestation content	Regulator-facing evidence
Snowflake	(fq_table, deleted_row_count, Fail-safe date)	per-table delete count
BigQuery	(fq_table, deleted_row_count, time-travel date)	per-table delete count
S3 versioned	(bucket, prefix, expected purge date)	lifecycle rule ID + expected purge
Redis	(deleted_key_count)	key count
Backup vault	(blocked count, natural purge date, policy)	policy justification + block list

Why this works — concept by concept:

• Per-store mechanic — each store has a native delete verb and retention model. Matching the pipeline call to the store's semantics is the difference between "compliance theatre" and actual deletion.
• Completion signal per store — Snowflake Fail-safe, BigQuery time-travel, S3 lifecycle transition each have their own timeline. The pipeline records the expected purge time and follows up to confirm.
• Attestation as regulator-facing evidence — every store returns a structured record. The aggregated attestation log is the artefact a regulator asks for; without it, the compliance argument is verbal.
• Documented retention windows — backups (and long-retention time-travel) use documented retention windows plus restoration-block lists. GDPR accepts the pattern when it's written and effective at restore time.
• Cost — O(target rows) for warehouse deletes; O(objects) for lake deletes; O(1) for suppression/block lists. Total per-DSAR cost is dominated by the S3 lake rewrite (or amortised via quarterly compaction).

SQL
Topic — sql
SQL per-store delete and retention problems

Practice →

Optimization Topic — optimization Optimization problems on Snowflake time-travel + S3 lifecycle

Practice →

---

5. Audit, reporting, and the DSAR SLA

Every DSAR closes with a per-store attestation log + a 30-day SLA dashboard — that's what makes it audit-safe

The mental model in one line: a DSAR pipeline is not done when the last DELETE returns — it is done when the per-store attestation log is written, the SLA clock event is closed, and legal has a monthly reporting dashboard that proves the pipeline's completeness across every request. The senior interview signal is talking about the audit trail as a first-class engineering surface, not a compliance afterthought.

The four axes of DSAR audit + reporting.

• DSAR ticket lifecycle. The five states — receive → verify → resolve → propagate → attest → close — with per-state timestamps and per-state actors. Every transition writes a row to the ticket-history table.
• SLA clock. 30 days for GDPR Article 17, 45 days for CCPA Section 1798.105. The clock starts at received_at and ends at closed_at. Every DSAR carries a per-request SLA budget and elapsed timer.
• Per-store attestation log. One row per (DSAR, store, table) triple, capturing the delete verb, row count, actor, key hash, and physical-purge timestamp.
• Monthly reporting to legal. Aggregate metrics — DSARs received, DSARs closed, average time-to-close, SLA-breach count, per-store attestation completeness. Legal reads the dashboard; the pipeline surfaces the numbers.

The five-state ticket lifecycle.

• Receive. DSAR arrives (in-product form, email, third-party privacy platform). Ticket ID assigned; SLA clock starts.
• Verify. Requester's identity is verified. Usually a manual review of ID evidence; some platforms auto-verify against KYC data. Ticket transitions to verified or rejected.
• Resolve. Identity graph query runs; canonical key set produced.
• Propagate. Per-store deletion drivers run; attestation rows written.
• Attest + close. Attestation summary is written to the ticket; legal reviews and closes.

The SLA clock — three counters.

• Elapsed. now() - received_at. Compared against 30 (GDPR) or 45 (CCPA) days.
• Slack. budget - elapsed. Number of days before SLA breach.
• Stage timing. Per-stage elapsed, for identifying which stage is slow.

The attestation log schema.

• Primary key. (dsar_request_id, store, fq_table).
• Columns. dsar_request_id, store, fq_table, deleted_row_count, actor, keys_hash (SHA-256 of the affected identifier list, never the raw identifiers), attested_at, expected_physical_purge_at, verb.
• Retention. The attestation log is itself retained for at least 6 years (typical regulator audit window). It is not deletable by end users; only the DPO can access it directly.
• Query patterns. "All attestations for DSAR X" (regulator audit); "count of DSARs closed within SLA in month Y" (legal reporting); "attestations missing for DSAR X" (pipeline completeness check).

Monthly legal reporting.

• Volume. DSARs received this month; running trend; per-source breakdown (email / form / third-party).
• Throughput. Time-to-close distribution; median, p95, p99; per-stage breakdown.
• Compliance. SLA-breach count; days-past-SLA for any open requests; attestation completeness (fraction of expected attestations that were written).
• Per-store health. Attestations written per store; row counts per store; expected physical-purge dates for the store's retention window.

Common interview probes on audit + reporting.

• "What's in your attestation log schema?" — primary key + deleted row count + actor + expected physical purge.
• "How do you know the pipeline was complete?" — count of expected attestations vs written attestations must be equal per DSAR.
• "How do you report to legal?" — monthly dashboard with volume, throughput, SLA-breach count, per-store health.
• "How long do you retain the attestation log?" — 6+ years (regulator audit window).

Worked example — the attestation log schema + audit query

Detailed explanation. The attestation log is the regulator-facing artefact — a per-store row-level record of every deletion the pipeline performed. Design it as an append-only append-only table with a primary key of (dsar_request_id, store, fq_table) and a strict schema. The most common audit query is "prove that DSAR X was completed" — the query returns every attestation row for the DSAR ordered by store and table.

• Setup. Snowflake or BigQuery attestation log table.
• Access. Read-only for legal, read-write for the DSAR pipeline, no delete permission for anyone.
• Retention. 6 years minimum.

Question. Design the attestation log schema and write the three canonical audit queries — per-DSAR completeness, monthly volume, per-store attestation coverage.

Input.

Component	Value
Table name	governance.dsar_attestation_log
Retention	6 years
Access	pipeline (write), legal (read), DPO (query direct)

Code.

-- Attestation log schema
CREATE TABLE governance.dsar_attestation_log (
  dsar_request_id            VARCHAR NOT NULL,
  store                      VARCHAR NOT NULL,         -- snowflake / bigquery / s3_lake / redis / backup_vault / kafka
  fq_table                   VARCHAR,                  -- fully-qualified table name, or bucket+prefix, or key pattern
  verb                       VARCHAR NOT NULL,         -- DELETE / SUPPRESS / LIFECYCLE_RULE / DEL / BLOCK
  deleted_row_count          INTEGER,                  -- rows deleted (nullable for suppression / block)
  suppressed_id_count        INTEGER,                  -- for suppression list inserts
  blocked_id_count           INTEGER,                  -- for restoration-block inserts
  actor                      VARCHAR NOT NULL,         -- IAM role / user
  keys_hash                  VARCHAR NOT NULL,         -- SHA-256 of the affected identifier list
  attested_at                TIMESTAMP NOT NULL,
  expected_physical_purge_at TIMESTAMP,
  metadata                   VARIANT,                  -- structured extras
  PRIMARY KEY (dsar_request_id, store, fq_table, verb, attested_at)
);

-- Audit query 1 — per-DSAR completeness
SELECT dsar_request_id,
       COUNT(DISTINCT store)                   AS stores_attested,
       COUNT(*)                                AS total_attestations,
       SUM(deleted_row_count)                  AS total_deleted_rows,
       SUM(suppressed_id_count)                AS total_suppressed,
       SUM(blocked_id_count)                   AS total_blocked,
       MIN(attested_at)                        AS first_attested_at,
       MAX(attested_at)                        AS last_attested_at,
       MAX(expected_physical_purge_at)         AS latest_expected_purge
FROM   governance.dsar_attestation_log
WHERE  dsar_request_id = 'DSAR-2026-07-04-alice'
GROUP  BY 1;

-- Audit query 2 — monthly volume + SLA compliance
SELECT DATE_TRUNC('month', t.received_at)                           AS month,
       COUNT(DISTINCT t.dsar_request_id)                            AS dsars_received,
       COUNT(DISTINCT CASE WHEN t.closed_at IS NOT NULL THEN t.dsar_request_id END) AS dsars_closed,
       AVG(DATEDIFF('day', t.received_at, t.closed_at))             AS avg_days_to_close,
       COUNT(DISTINCT CASE WHEN DATEDIFF('day', t.received_at, t.closed_at) > 30 THEN t.dsar_request_id END) AS gdpr_sla_breaches,
       COUNT(DISTINCT CASE WHEN DATEDIFF('day', t.received_at, t.closed_at) > 45 THEN t.dsar_request_id END) AS ccpa_sla_breaches
FROM   governance.dsar_ticket t
GROUP  BY 1
ORDER  BY 1 DESC;

-- Audit query 3 — per-store attestation coverage
SELECT store,
       COUNT(DISTINCT dsar_request_id)         AS dsars_covered,
       SUM(deleted_row_count)                  AS total_deleted,
       AVG(deleted_row_count)                  AS avg_deleted_per_dsar
FROM   governance.dsar_attestation_log
WHERE  attested_at >= DATEADD('month', -1, CURRENT_TIMESTAMP)
GROUP  BY 1
ORDER  BY 2 DESC;

Step-by-step explanation.

1. The attestation log schema uses a composite primary key (dsar_request_id, store, fq_table, verb, attested_at) so each attestation event is uniquely identifiable. The attested_at in the key allows multiple attestations per store+table (e.g. initial delete + physical-purge confirmation).
2. Query 1 answers "was DSAR X complete?" — counts distinct stores attested, total attestations, and the range of attestation timestamps. The DSAR pipeline uses the expected-attestation count from the target-list enumeration and asserts equality; missing attestations are pipeline bugs.
3. Query 2 is the legal-facing monthly report. Volume + throughput + SLA breach counts across GDPR (30d) and CCPA (45d) windows. A single SLA breach in a month triggers a legal review; the report is the running dashboard.
4. Query 3 is the per-store coverage report. It answers "is every store contributing attestations?" — a store with zero attestations means the pipeline is silently skipping that store.
5. The log is append-only. Users can query but never modify. Pipeline updates are additive; corrections come as follow-up attestation rows with an updated expected_physical_purge_at or an explanatory metadata.correction note.

Output.

Query	Purpose	Consumer
Per-DSAR completeness	Prove DSAR X was complete	Regulator + legal
Monthly volume + SLA	Compliance trend	Legal + DPO
Per-store coverage	Pipeline completeness	Data platform team

Rule of thumb. The attestation log is the only artefact a regulator will ask for. Design it as append-only with a strict schema, retain for 6+ years, and treat missing rows as pipeline incidents. If the log is complete, the compliance argument writes itself.

Worked example — the 30-day SLA clock with per-stage timing

Detailed explanation. The 30-day SLA is a single number but the compliance signal is a per-stage timing. When a DSAR breaches SLA, the retro question is "which stage overran?" — the pipeline must record per-stage timestamps so the answer is one query away. Additionally, the SLA clock is a calendar clock, not a business-day clock; weekends and holidays count.

• The setup. DSAR ticket table with per-stage timestamps.
• The metric. Per-stage elapsed, plus total elapsed vs SLA budget.

Question. Show the ticket schema with per-stage timestamps, the SLA calculation query, and the alerting policy that catches at-risk DSARs before breach.

Input.

Stage	Column
received_at	Timestamp
verified_at	Timestamp
resolved_at	Timestamp
propagated_at	Timestamp
attested_at	Timestamp
closed_at	Timestamp

Code.

-- Ticket schema
CREATE TABLE governance.dsar_ticket (
  dsar_request_id VARCHAR PRIMARY KEY,
  subject_email   VARCHAR,
  jurisdiction    VARCHAR,                       -- 'GDPR' / 'CCPA' / 'both'
  received_at     TIMESTAMP NOT NULL,
  verified_at     TIMESTAMP,
  resolved_at     TIMESTAMP,
  propagated_at   TIMESTAMP,
  attested_at     TIMESTAMP,
  closed_at       TIMESTAMP,
  status          VARCHAR NOT NULL DEFAULT 'received'   -- received / verified / resolved / propagated / attested / closed / rejected
);

-- SLA calculation with per-stage elapsed
WITH stage_timing AS (
  SELECT dsar_request_id,
         jurisdiction,
         received_at,
         verified_at,
         resolved_at,
         propagated_at,
         attested_at,
         closed_at,
         DATEDIFF('hour', received_at,   verified_at)  AS verify_hours,
         DATEDIFF('hour', verified_at,   resolved_at)  AS resolve_hours,
         DATEDIFF('hour', resolved_at,   propagated_at) AS propagate_hours,
         DATEDIFF('hour', propagated_at, attested_at)  AS attest_hours,
         DATEDIFF('hour', attested_at,   closed_at)    AS close_hours,
         DATEDIFF('day',  received_at,   COALESCE(closed_at, CURRENT_TIMESTAMP)) AS elapsed_days
  FROM   governance.dsar_ticket
)
SELECT dsar_request_id,
       jurisdiction,
       elapsed_days,
       CASE jurisdiction
         WHEN 'GDPR' THEN 30
         WHEN 'CCPA' THEN 45
         WHEN 'both' THEN 30
       END AS sla_days,
       CASE jurisdiction
         WHEN 'GDPR' THEN 30 - elapsed_days
         WHEN 'CCPA' THEN 45 - elapsed_days
         WHEN 'both' THEN 30 - elapsed_days
       END AS slack_days,
       verify_hours,
       resolve_hours,
       propagate_hours,
       attest_hours,
       close_hours,
       CASE WHEN closed_at IS NULL AND
                 CASE jurisdiction
                   WHEN 'GDPR' THEN 30 - elapsed_days
                   WHEN 'CCPA' THEN 45 - elapsed_days
                   WHEN 'both' THEN 30 - elapsed_days
                 END < 5
            THEN 'AT_RISK'
            WHEN closed_at IS NULL AND elapsed_days > 30 THEN 'BREACHED'
            ELSE 'OK'
       END AS sla_status
FROM   stage_timing;

# Alert policy on sla_status
alerts:
  - name: dsar_sla_at_risk
    query: |
      SELECT dsar_request_id FROM dsar_sla_view WHERE sla_status = 'AT_RISK'
    page: dpo_and_legal
    interval: 6h
  - name: dsar_sla_breached
    query: |
      SELECT dsar_request_id FROM dsar_sla_view WHERE sla_status = 'BREACHED'
    page: dpo_and_ciso
    interval: 15m

Step-by-step explanation.

1. Every stage transition writes a timestamp to the ticket. The pipeline writes resolved_at when the identity graph query returns; propagated_at when the delete driver finishes; attested_at when the attestation log rows are written; closed_at when legal marks the ticket closed.
2. The SLA-calculation view derives per-stage elapsed times using DATEDIFF('hour', ...). This surfaces which stage is slow — verify_hours large means human review is bottleneck; propagate_hours large means the delete drivers are slow.
3. The slack_days column is SLA_budget - elapsed. When slack drops below 5 days on an open ticket, the status flips to AT_RISK and the alert fires. This gives DPO + legal a 5-day runway to escalate.
4. BREACHED status fires the moment elapsed exceeds SLA on an open ticket. Regulator-facing communication may be required within hours of breach; the alert is at 15-minute intervals for high-urgency response.
5. The dashboard shows the sla_status distribution per month — the primary compliance metric legal reports upward. A single BREACHED status in a month is a regulator-notifiable event in most jurisdictions.

Output.

DSAR ID	Elapsed	Slack	Status	Slow stage
DSAR-2026-07-04-alice	17 d	13 d	OK	—
DSAR-2026-07-01-bob	26 d	4 d	AT_RISK	verify_hours = 72h
DSAR-2026-06-01-carol	34 d	-4 d	BREACHED	propagate_hours = 300h

Rule of thumb. SLA is a per-stage metric, not a global counter. When a DSAR is at risk, the retrospective must identify the slow stage. Design the ticket schema with per-stage timestamps from day one; retrofitting them after a breach is expensive.

Worked example — monthly reporting to legal

Detailed explanation. The monthly report is the artefact legal takes to the DPO / CISO and eventually to the regulator on request. It aggregates DSAR volume, throughput, SLA compliance, and per-store attestation coverage into a single dashboard. Design the report as a SQL query set that runs on the 1st of every month against the previous month's window.

• Consumer. Legal + DPO + CISO.
• Cadence. Monthly.
• Retention. 6+ years (regulator audit).

Question. Write the SQL query set that produces the monthly report, with sample output structure and a section-by-section walkthrough of what each panel shows.

Input.

Component	Value
Report window	previous calendar month
Report cadence	1st of every month
Recipients	legal-ops, DPO, CISO

Code.

-- Panel 1 — Volume
WITH last_month AS (
  SELECT DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE)) AS month_start,
         LAST_DAY(DATEADD('month', -1, CURRENT_DATE))            AS month_end
)
SELECT jurisdiction,
       COUNT(*) AS dsars_received
FROM   governance.dsar_ticket, last_month
WHERE  received_at >= month_start
  AND  received_at <= month_end
GROUP  BY jurisdiction;

-- Panel 2 — Throughput
SELECT jurisdiction,
       PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY DATEDIFF('day', received_at, closed_at)) AS median_days,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DATEDIFF('day', received_at, closed_at)) AS p95_days,
       PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY DATEDIFF('day', received_at, closed_at)) AS p99_days,
       AVG(DATEDIFF('day', received_at, closed_at)) AS avg_days
FROM   governance.dsar_ticket
WHERE  closed_at BETWEEN (SELECT month_start FROM last_month) AND (SELECT month_end FROM last_month)
GROUP  BY jurisdiction;

-- Panel 3 — SLA compliance
SELECT jurisdiction,
       COUNT(*)                                                            AS closed_in_month,
       COUNT(CASE WHEN
                    DATEDIFF('day', received_at, closed_at) <=
                    CASE jurisdiction WHEN 'GDPR' THEN 30 WHEN 'CCPA' THEN 45 END
                  THEN 1 END)                                              AS within_sla,
       COUNT(CASE WHEN
                    DATEDIFF('day', received_at, closed_at) >
                    CASE jurisdiction WHEN 'GDPR' THEN 30 WHEN 'CCPA' THEN 45 END
                  THEN 1 END)                                              AS breached
FROM   governance.dsar_ticket
WHERE  closed_at BETWEEN (SELECT month_start FROM last_month) AND (SELECT month_end FROM last_month)
GROUP  BY jurisdiction;

-- Panel 4 — Per-store attestation coverage
SELECT a.store,
       COUNT(DISTINCT a.dsar_request_id) AS dsars_covered_by_store,
       SUM(a.deleted_row_count)          AS total_deleted_rows
FROM   governance.dsar_attestation_log a
WHERE  a.attested_at BETWEEN (SELECT month_start FROM last_month) AND (SELECT month_end FROM last_month)
GROUP  BY a.store
ORDER  BY 2 DESC;

Step-by-step explanation.

1. Panel 1 — Volume. How many DSARs arrived last month, split by jurisdiction. Trend context (this-month vs prior-month) is often added as a second query.
2. Panel 2 — Throughput. Median / p95 / p99 time-to-close. Legal cares about median (typical experience); DPO cares about p99 (worst case); CISO cares about the delta between the two (variance is compliance risk).
3. Panel 3 — SLA compliance. Split closed DSARs into within-SLA and breached. Each breach is a regulator-notifiable event in most jurisdictions; a single row here triggers a legal follow-up.
4. Panel 4 — Per-store attestation coverage. Which stores contributed attestations, how many DSARs each store served, total row counts. A store that's not appearing means the pipeline is silently skipping it.
5. The report runs on the 1st of every month, is emailed to the legal-ops distribution list, and is archived to governance.monthly_reports for the 6-year retention window.

Output.

Panel	Metric	Sample value
Volume	GDPR DSARs received	47
Volume	CCPA DSARs received	32
Throughput	Median days-to-close (GDPR)	17
Throughput	p99 days-to-close (GDPR)	27
SLA compliance	GDPR breaches	0
SLA compliance	CCPA breaches	0
Store coverage	Snowflake attestations	79 DSARs, 3.2M rows deleted
Store coverage	S3 lake attestations	79 DSARs, 128k objects

Rule of thumb. Monthly reporting is the artefact that turns pipeline output into legal-facing evidence. Build it once, run it forever; regulator audits typically request "the last 24 months of DSAR reports" — having them archived saves days of legal-ops work.

Senior interview question on audit + SLA reporting

A senior interviewer might ask: "Design the audit and reporting layer for your DSAR pipeline. What tables do you need, what queries do you run, how do you catch SLA breaches before they happen, and how do you satisfy a regulator asking for the last 12 months of evidence?"

Solution Using a three-table audit layer + monthly automated reporting

-- Three-table audit layer
-- 1. dsar_ticket           — per-request lifecycle + timestamps
-- 2. dsar_attestation_log  — per-store row-level deletion evidence
-- 3. monthly_reports       — legal-facing aggregates

CREATE TABLE governance.monthly_reports (
  report_month             DATE PRIMARY KEY,
  gdpr_received            INTEGER,
  gdpr_closed              INTEGER,
  gdpr_breached            INTEGER,
  ccpa_received            INTEGER,
  ccpa_closed              INTEGER,
  ccpa_breached            INTEGER,
  median_days_to_close     NUMERIC,
  p99_days_to_close        NUMERIC,
  stores_attested          ARRAY,                    -- ['snowflake', 's3_lake', ...]
  generated_at             TIMESTAMP NOT NULL,
  generated_by             VARCHAR NOT NULL
);

-- Automated monthly job (runs on the 1st of every month)
CREATE OR REPLACE PROCEDURE governance.generate_monthly_dsar_report()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  report_month DATE;
BEGIN
  report_month := DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE));

  INSERT INTO governance.monthly_reports
    (report_month, gdpr_received, gdpr_closed, gdpr_breached,
     ccpa_received, ccpa_closed, ccpa_breached,
     median_days_to_close, p99_days_to_close,
     stores_attested, generated_at, generated_by)
  WITH volume AS (
    SELECT jurisdiction, COUNT(*) AS received
    FROM   governance.dsar_ticket
    WHERE  DATE_TRUNC('month', received_at) = :report_month
    GROUP  BY jurisdiction
  ),
  closes AS (
    SELECT jurisdiction,
           COUNT(*) AS closed,
           COUNT(CASE WHEN
                        DATEDIFF('day', received_at, closed_at) >
                        CASE jurisdiction WHEN 'GDPR' THEN 30 WHEN 'CCPA' THEN 45 END
                      THEN 1 END) AS breached,
           PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY DATEDIFF('day', received_at, closed_at)) AS median_d,
           PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY DATEDIFF('day', received_at, closed_at)) AS p99_d
    FROM   governance.dsar_ticket
    WHERE  DATE_TRUNC('month', closed_at) = :report_month
    GROUP  BY jurisdiction
  ),
  stores AS (
    SELECT ARRAY_AGG(DISTINCT store) AS stores
    FROM   governance.dsar_attestation_log
    WHERE  DATE_TRUNC('month', attested_at) = :report_month
  )
  SELECT :report_month,
         (SELECT received FROM volume WHERE jurisdiction = 'GDPR'),
         (SELECT closed   FROM closes WHERE jurisdiction = 'GDPR'),
         (SELECT breached FROM closes WHERE jurisdiction = 'GDPR'),
         (SELECT received FROM volume WHERE jurisdiction = 'CCPA'),
         (SELECT closed   FROM closes WHERE jurisdiction = 'CCPA'),
         (SELECT breached FROM closes WHERE jurisdiction = 'CCPA'),
         (SELECT AVG(median_d) FROM closes),
         (SELECT AVG(p99_d)    FROM closes),
         (SELECT stores FROM stores),
         CURRENT_TIMESTAMP(),
         CURRENT_USER();

  RETURN 'ok';
END;
$$;

Step-by-step trace.

Table	Rows written per DSAR	Query pattern
dsar_ticket	1 row per DSAR (updated per stage)	per-request lifecycle
dsar_attestation_log	N rows per DSAR (1 per store+table)	regulator audit + completeness check
monthly_reports	1 row per month	legal reporting dashboard

The three tables cover the full audit surface. A regulator asking for "evidence of DSAR X" gets the dsar_ticket row + all dsar_attestation_log rows for that request. A regulator asking for "the last 12 months of compliance" gets the monthly_reports rows. A DPO checking pipeline health runs the per-store coverage query. Every question has a direct answer in one of the three tables.

Output:

Layer	Consumer	Query frequency
dsar_ticket	pipeline + on-call	continuous
dsar_attestation_log	regulator + DPO	ad-hoc
monthly_reports	legal + CISO	monthly
SLA at-risk alert	DPO on-call	every 6 hours
SLA breached alert	DPO + CISO	every 15 minutes

Why this works — concept by concept:

• Three tables, three consumers — each table serves a distinct audience with a distinct query pattern. Mixing them into one wide table makes every query expensive and every audit slow.
• Per-stage timing — the ticket schema captures a timestamp per stage. Retrospectives on breaches identify the slow stage immediately; without per-stage timestamps the retro is a manual reconstruction.
• Append-only attestation — the attestation log is never modified. Corrections come as follow-up rows with an explanatory metadata note. This preserves the audit trail and is the pattern regulators expect.
• Automated monthly reports — the stored procedure runs on the 1st of every month and writes an aggregate row. Legal reads the row; the underlying detail is available on demand. Manual reports drift; automated reports don't.
• Cost — three tables, one stored procedure, two alert queries. Storage cost is negligible (attestation log grows ~1 MB per 1000 DSARs). Ongoing operational cost is ~1 hour per quarter for schema evolution and query tuning.

SQL
Topic — sql
SQL audit-log and SLA-clock problems

Practice →

ETL
Topic — etl
ETL problems on compliance reporting pipelines

Practice →

---

Cheat sheet — GDPR DSAR recipes

• Identity-graph resolution query template. Recursive CTE starting from (email, hashed_email) seeds, walking edges of type logged_in_as, hashed_from, same_session_as, merged_by_admin, bounded at depth 5. Bidirectional edges for merges; hashed variants seeded alongside raw. Wrap as a stored procedure governance.resolve_dsar_identity(input_email) returning (id_type, id_value) rows.
• Lineage-driven delete-target enumeration. SQL query against governance.dbt_catalog_columns joined to governance.pii_taxonomy regex patterns. Follow with a recursive walk over governance.dbt_manifest_columns to include column-level descendants. Output (fq_table, column_name, key_type, depth) triples; sort by depth DESC for leaves-first delete order.
• Snowflake hard-delete recipe. ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 → DELETE FROM ... WHERE identity_column IN (...) (capture SQLROWCOUNT) → write attestation row → ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = <original> → document 7-day Fail-safe. Enterprise+ tenants document Fail-safe; Standard tenants skip that step.
• BigQuery hard-delete recipe. DELETE FROM ... with parameterised UNNEST array — capture num_dml_affected_rows. Time-travel is a fixed 7-day window; document the expected physical purge date. For time-partitioned tables consider partition_expiration_days for retention-based purging.
• S3 versioned lake lifecycle rule. list_object_versions on the affected prefix; put_bucket_lifecycle_configuration with Filter.Prefix, Expiration.Days = 1, NoncurrentVersionExpiration.NoncurrentDays = 1. Follow up at T+48h to confirm the prefix is empty; write a second attestation row.
• Kafka/Kinesis suppression recipe. Insert every resolved key into governance.suppressed_ids on receipt; every consumer joins to the suppression list on read. Physical purge happens at the natural retention window (7 days for Kafka default, 24h–365d for Kinesis). Log the expected physical purge date in the attestation.
• Backup carve-out policy. Insert every resolved key into governance.backup_restoration_blocks with expected_natural_purge_at = now + backup_retention. Every restore procedure filters the restored data against the block list before releasing to production. Document as gdpr_article_17_documented_retention_window in the attestation log.
• DSAR attestation log schema. Primary key (dsar_request_id, store, fq_table, verb, attested_at). Columns: deleted_row_count, suppressed_id_count, blocked_id_count, actor, keys_hash (SHA-256 — never raw identifiers), attested_at, expected_physical_purge_at, metadata (variant). Retention 6+ years. Append-only; no update permission.
• Five-stage DSAR ticket lifecycle. received → verified → resolved → propagated → attested → closed. One timestamp per stage; SLA computed as now - received_at. Alert AT_RISK at slack < 5 days, BREACHED at elapsed > SLA budget (30d GDPR, 45d CCPA).
• SLA breach retro. Query per-stage elapsed for the breached DSAR. Slow stage identifies the fix — verify_hours large → speed up verification; propagate_hours large → tune delete drivers; attest_hours large → automate attestation writes.
• Monthly legal report. Volume by jurisdiction, throughput percentiles (median / p95 / p99), SLA compliance count, per-store attestation coverage. Automated stored procedure on the 1st of every month; archived to governance.monthly_reports. Emailed to legal-ops + DPO.
• Identity-graph freshness SLO. Nightly refresh minimum; alert on 30-hour staleness. Graph freshness bugs are compliance bugs — a merged account whose edge hasn't been written misses its pre-merge data in every DSAR run.
• Column-level lineage caveat. dbt manifest column dependencies catch descendant columns (email domain, hashed-of-hashed). Without column-level lineage, table-level fallback misses 20–30% of the target list. Prioritise column-level lineage tooling for any compliance-first data platform.
• The one rule you cannot forget. Never invent a pgdelete — CDC propagates events, not GDPR-grade deletion. Every DSAR runs explicit hard-delete against every warehouse table on the lineage-driven target list. Trusting CDC alone is the single most common cause of a downstream compliance failure.

Frequently asked questions

What is a DSAR (Data Subject Access Request) under GDPR and CCPA?

A Data Subject Access Request (DSAR) is the mechanism a data subject uses to exercise their statutory rights over their personal data. Under GDPR (Article 15 for access, Article 17 for erasure a.k.a. "right to be forgotten"), an EU/EEA resident can demand that a controller (a) disclose every category of personal data the controller processes about them, (b) provide a copy of that data, and (c) erase the data on request unless a lawful basis to retain applies. Under CCPA (California Consumer Privacy Act, Section 1798.100 for access, Section 1798.105 for deletion), a California resident has an analogous right to know and delete. Both regulations require a verified identity — the platform must confirm the requester is the subject before responding. In practical warehouse terms, the DSAR translates to (1) an identity-resolution query returning every key the subject's data was ever written under, (2) a lineage-driven enumeration of every table storing any of those keys, (3) a per-store deletion (or suppression) mechanic, and (4) a per-store attestation log that survives regulator audit. Both regulations impose hard SLA clocks — 30 calendar days for GDPR, 45 for CCPA — measured from the moment a valid DSAR is received.

What is the GDPR delete SLA under Article 17 — is it 30 days or 30 business days?

The GDPR delete SLA under Article 17 is 30 calendar days — weekends and public holidays count against the clock. Article 12(3) permits a one-time extension of up to 60 additional days where the request is "complex" or the controller has received a large number of requests, but the controller must notify the data subject of the extension and the reason within the original 30-day window. Regulators (ICO in the UK, CNIL in France, the various EU DPAs) have increasingly used the SLA clock as the enforcement lever — the fine is often larger for a late deletion than for a missed deletion, because the missed deletion suggests a pipeline bug (which the regulator understands) while the late deletion suggests an operational failure (which the regulator does not tolerate). CCPA under Section 1798.105 gives 45 calendar days plus a permitted extension. Design your DSAR pipeline against the shorter clock (30 days) and treat the extension as an emergency escape hatch, never as the plan.

How do I hard-delete a row from a Snowflake table — is DELETE enough for GDPR?

DELETE FROM table WHERE identity_column IN (...) removes the row from the current version, but Snowflake keeps the pre-delete row in time-travel (default 1 day, up to 90 days on Enterprise Edition) and Fail-safe (7 additional days on Enterprise+, non-configurable). For strict GDPR compliance you must either wait the full time-travel + Fail-safe window before marking the DSAR complete, or shrink time-travel with ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 for the DSAR run and restore the normal retention after. Fail-safe cannot be shrunk on Enterprise+ — it's a 7-day disaster-recovery window managed by Snowflake support — so the DSAR attestation must document the expected Fail-safe purge date (T+7). Standard Edition does not have Fail-safe, so the DSAR completes as soon as time-travel is shrunk and the DELETE returns. The full recipe: ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 → DELETE FROM ... WHERE user_id IN (...) → capture SQLROWCOUNT for attestation → ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = <original> → schedule Fail-safe purge documentation for T+7.

Do I need to delete from backups too under GDPR?

Under strict reading of Article 17, yes — personal data must be erased from every location, including backups. In practice, regulators (and the EDPB's guidelines) accept a documented retention window pattern: leave the backup in place, add the subject to a restoration-block list, wait for the backup to naturally age out under the normal retention policy (typically 30–90 days), and document the pattern in your privacy policy. The restoration-block list is the mechanism that makes the pattern effective — every backup restore procedure must consult the block list and filter any blocked identifier from the restored data before releasing to production. Regulators accept this pattern when (a) the backup retention window is short and documented, (b) the block list is procedure-enforced at restore time, and (c) the DSAR attestation log records the expected natural-purge date. The alternative — physically restoring, filtering, and re-writing every backup — is orders of magnitude more expensive without additional compliance benefit and is not standard practice at scale.

How do I handle hashed identifiers when the DSAR arrives with the raw email?

The identity-resolution step must compute the same hash the ingestion pipeline used. If your warehouse stores hashed_email = SHA256(email || tenant_salt), then the resolution query must lower-case + trim the input email, apply the tenant salt, run SHA-256, and lower-case the hex output — matching every normalisation step the ingestion pipeline applied. Seed the identity graph with both the raw email and the computed hash so downstream tables that key on either form are covered. Same pattern for phone numbers, device IDs, or any other hashed identifier. The most common bug is a subtle mismatch — different salt, different case-folding, different trim behaviour — that causes the hash to not match the stored value and the warehouse deletion count to be zero. Always run a smoke test on a known subject: compute the hash, query the warehouse for the hash, confirm the row count matches the expected value. If it's zero, your hashing recipe drifted; fix before shipping.

What about downstream ML training data and feature stores — do those count as personal data?

Yes — any dataset that can be re-identified to the data subject counts as personal data under GDPR Article 4(1), including derived features, aggregated statistics keyed on user_id, embeddings that identify individual users, and cached ML predictions. The DSAR pipeline must extend to the feature store (Redis, Feast, Tecton), the training-data warehouse (S3 Parquet, Delta, Iceberg), and any cached prediction store (application-side cache, model-serving cache). For the feature store, DEL on the per-user keys is usually sufficient. For training data, the same lineage-driven enumeration applies — the target list includes every training table that keys on a resolved identifier. For cached predictions, invalidation on delete (either at DSAR time or via TTL) is required. Aggregates and models trained on the subject's data are a subtler case — a model already trained cannot "un-learn" a specific subject, but the training-time snapshot must be updated so future re-training does not re-include the subject. Regulators accept this pragmatic split; the compliance argument is that the specific subject's data is deleted from every queryable store, and the model retraining cadence brings the model in line over time.

Practice on PipeCode

• Drill the SQL practice library → for the identity-graph resolution, lineage-driven enumeration, and per-store delete SQL that senior interviewers love.
• Rehearse on the ETL practice library → for the DSAR pipeline architecture, delete-propagation ordering, and warehouse-scale compliance flows.
• Sharpen the propagation-completeness axis with the optimization practice library → for the Snowflake time-travel shrink, S3 lifecycle rule, and attestation-log design problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor GDPR + DSAR intuition against real graded inputs.

Lock in DSAR pipeline muscle memory

Regulation docs explain the clause. PipeCode drills explain the decision — when to hard-delete vs suppress, how to size the SLA budget, how to write an attestation log a regulator will accept. 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 ETL problems →