Learn / BigQuery

How to delete PII in BigQuery (and actually prove it).

Running a DELETE on your users table feels like erasure. In BigQuery it usually isn't: the data survives in time travel, in derived tables, and in snapshots, and you are left with no evidence. Here is why, and the crypto-shredding approach that deletes and proves in one call.

BigQuery integrationHow crypto-shredding works →
Why DELETE isn't enough

Four reasons a DML delete falls short.

Time travel keeps a copy

BigQuery retains prior versions of table data for a time-travel window (configurable up to seven days) plus a fail-safe period. A row you DELETE stays recoverable — and readable — for that window.

Downstream copies survive

The DELETE only touches the one table you ran it on. Staging tables, dbt marts, and exports that copied the same personal data are untouched, so the customer's data lives on elsewhere.

It's slow and costly at scale

DML DELETE is billed against the data scanned and can be expensive and slow on large tables, which makes per-user deletions at volume — DSAR batches, breach response — a real bottleneck.

No evidence is produced

A completed query leaves a log line, not an audit artifact. When a regulator asks what was deleted, when, and how completely, a query history does not answer the question.

The approach

Encrypt on write, destroy the key on request.

The reliable pattern is crypto-shredding. Before personal fields land in BigQuery, encrypt each one with a per-user data encryption key. The warehouse — and everything downstream of it — then stores ciphertext. Staging tables, marts, exports, backups, and time-travel windows all inherit that protection automatically, because they only ever held encrypted bytes.

When a deletion request arrives, you do not chase rows. You destroy the per-user key in Cloud KMS. Every copy encrypted under that key becomes mathematically unreadable at the same instant, regardless of which table or snapshot it sits in. One KMS operation replaces the fleet of DELETE statements you would otherwise have to locate, run, and somehow prove.

End to end

Deleting a user's PII in BigQuery with proof.

01 / Ingest encrypted

The pipeline encrypts each PII field with the user's DEK before writing to BigQuery. Plaintext never lands in the warehouse.

02 / Map with a registry

A live registry records which columns and which dbt-derived models hold that user's encrypted data, establishing deletion scope without a manual hunt.

03 / Shred the key

On request, the key vault destroys the DEK in KMS. Every encrypted copy — including copies in time travel and snapshots — becomes unreadable simultaneously.

04 / Issue the certificate

A signed record captures the key fingerprint, the destruction timestamp, and the lineage of covered systems. That is the evidence you hand to an auditor or a DSAR.

Proof record
# deletion proof (decoded)
warehouse: BigQuery
keyFingerprint: sha256:a1b2c3d4e5f6...
shredDate: 2026-06-24T11:03:21Z
covered: raw_users, stg_users, dim_users, mart_customer_metrics
timeTravel: ciphertext_unreadable
status: certificate_issued
FAQ

Common questions

How do I delete a user's PII from BigQuery for GDPR?

You can run DML DELETE statements, but they are slow at scale, leave copies in time travel and downstream tables, and produce no evidence. The reliable approach is crypto-shredding: encrypt PII with a per-user key on write, then destroy that key on request, which renders every encrypted copy unreadable at once and produces a proof certificate.

Does BigQuery time travel keep deleted data?

Yes. BigQuery keeps prior versions of table data in a time-travel window (up to seven days) and a further fail-safe period, so DELETEd rows remain recoverable for a time. Crypto-shredding sidesteps this because the retained versions contain ciphertext that is useless once the key is destroyed.

Why not just DELETE the rows and drop the derived tables?

You would have to enumerate every derived table, export, and backup that copied the data, run a delete against each, wait out every time-travel window, and still have no single artifact proving completeness. Key destruction covers all copies in one operation and emits that proof automatically.

Keep reading