Column Encryption & Tokenization Vaults: Envelope Encryption, KMS, BYOK/HYOK for Warehouses

column encryption is the control that decides whether a stolen warehouse snapshot, a leaked backup, or an over-privileged analyst hands an attacker a customer's raw national ID and card number — or hands them nothing but ciphertext they can never unwind. Role grants and masking decide who can query what, but the moment a disk image walks out the door, an S3 bucket is left public, or a support engineer runs SELECT * against a table they were never meant to open, the only thing standing between the raw value and the breach headline is whether that column was encrypted at the value level and whether the key lived somewhere the attacker could not reach. The engineering decision is not "should we encrypt" — every regulated column must be protected — but how: encrypt the value in the application before it ever lands, tokenization that swaps it for a meaningless surrogate, or warehouse-native masking that only redacts at read time and leaves the raw bytes on disk.

This guide is the senior-data-engineering walkthrough for protecting sensitive columns end to end: envelope encryption with a two-level key hierarchy wrapped by a cloud KMS, application-side column encryption in deterministic and randomized modes, format-preserving encryption that keeps a 16-digit card number 16 digits, tokenization vaults that pull the warehouse out of PCI scope, and the BYOK/HYOK custody models that let a customer hold their own key — with crypto-shredding as the right-to-erasure lever. It covers warehouse security from the angle interviewers actually probe: where the data encryption key lives versus the key that wraps it, when deterministic encryption buys you a join and what it leaks, and how deleting one key can erase a tenant's data forever. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the modelling reps on the design practice library →, and stress-test the schema fundamentals on the database practice library →.

---

On this page

• Why column encryption and tokenization is the pick-one decision
• Envelope encryption and KMS — the DEK/KEK hierarchy
• Application-side column encryption — deterministic, randomized, format-preserving
• Tokenization vaults — vault-based vs vaultless
• BYOK / HYOK for warehouses — who owns the key
• Cheat sheet — column encryption and tokenization recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why column encryption and tokenization is the pick-one decision

Three controls, four axes — the choice binds your breach blast radius for years

The one-sentence invariant: column-level protection is a picking exercise between encrypting the value in the application before it lands (column encryption), swapping the value for a meaningless surrogate whose mapping lives elsewhere (tokenization), or leaving the raw bytes on disk and only redacting them at read time (warehouse-native masking) — and each choice trades where the key lives against what actually leaks when a snapshot is stolen, whether you can still join or range-scan the column, and how much latency you pay on every write and read. The control you pick in month one is the control your entire ingestion pipeline, every downstream join, and every compliance auditor's report hard-codes assumptions about — whether a leaked backup is a catastrophe or a non-event, whether a WHERE email = ? lookup still works, whether "delete this customer's data" is a DELETE or a key-shred.

The four axes interviewers actually probe.

• Where the key lives. Warehouse-native masking keeps the plaintext on disk and enforces nothing cryptographic — a stolen file is a stolen secret. Application-side column encryption keeps the ciphertext on disk and the key in a KMS the warehouse can't read. Tokenization keeps neither the value nor a derivable key near the data — only a surrogate. Interviewers open here because "the data is encrypted at rest" is the answer that fails: provider-managed at-rest encryption protects against a stolen physical disk, not against a leaked query result or an over-privileged role.
• What leaks in a breach. Model the adversary who exfiltrates a full table snapshot. With masking: everything, in the clear. With randomized column encryption: nothing but ciphertext and (with envelope encryption) a wrapped DEK they cannot unwrap. With deterministic encryption: ciphertext plus the frequency distribution (equal plaintexts share a ciphertext). With tokenization: tokens that are worthless without the vault. Naming exactly what leaks under each is the senior signal.
• Queryability and format preserved. Randomized encryption destroys equality and ordering — no joins, no WHERE col = ?, no range scans. Deterministic encryption restores equality and joins (at the cost of frequency leakage). Format-preserving encryption keeps the type and length so a 16-digit card stays a 16-digit CHAR(16) and validators/formats don't break. Masking preserves everything because the plaintext is still there. This axis is where most designs actually get decided.
• Latency and throughput cost. Every encrypted write needs a key; a naive design calls the KMS per row and dies at scale. Envelope encryption amortises this — one KMS call issues a data encryption key that encrypts millions of rows locally. Tokenization adds a network hop to a vault on write and on detokenize. Masking is free on write and cheap on read. Quantifying the per-row cost is what separates a shipped design from a whiteboard one.

The 2026 reality — envelope encryption is the substrate; the mode and custody are the decisions.

• Envelope encryption + KMS is the default key hierarchy under every serious column-encryption design. A short-lived data encryption key (DEK) encrypts the data; a long-lived key-encryption-key (KEK) in AWS KMS / GCP KMS / Azure Key Vault wraps the DEK. You store the wrapped DEK next to the ciphertext and rotate the KEK without ever re-encrypting the data.
• Application-side column encryption protects the value before it reaches the warehouse. The mode (AES-GCM randomized vs AES-SIV deterministic vs FF3-1 format-preserving) is chosen per column based on whether you must still join, range-scan, or fit an existing schema.
• Tokenization is the PCI-descoping tool: replace the card number with a token so the warehouse is never in scope for PCI-DSS at all. Vault-based tokenization stores a token↔value map; vaultless derives the token cryptographically with no stored map.
• BYOK / HYOK answer "who owns the key" for regulated or multi-tenant deployments. BYOK imports customer key material into the provider KMS; HYOK keeps the key inside the customer's own HSM and never releases it. Both enable crypto-shredding — destroy the key, and the ciphertext is unrecoverable, which is the cheapest possible GDPR right-to-erasure.

What interviewers listen for.

• Do you say "provider at-rest encryption does not protect against a leaked query result or an over-privileged role" when someone says "it's already encrypted"? — required answer.
• Do you name envelope encryption (DEK wrapped by a KEK) rather than "we encrypt with a key"? — senior signal.
• Do you distinguish deterministic (joinable, leaks frequency) from randomized (secret, no join) without prompting? — senior signal.
• Do you name tokenization as the PCI-descoping move, not as "another way to encrypt"? — senior signal.
• Do you reach for crypto-shredding as the right-to-erasure mechanism instead of a DELETE across cold storage and backups? — senior signal.

Worked example — the control-versus-axis comparison table

Detailed explanation. The single most useful artifact for a column-encryption interview is a memorised control × axis table. Every senior data-protection discussion converges on it within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a customers table that must protect an email (needed for joins) and a national_id (never queried) in a Snowflake warehouse.

• The table. customers (id, email, national_id, region, ltv_cents).
• The columns. email — must still JOIN to an events table; national_id — only ever read by a compliance export, never joined or filtered.
• The adversary. Someone who steals a full table snapshot (a leaked backup, a public bucket, an over-privileged role running SELECT *).

Question. Build the four-axis comparison across the three controls and pick the control each column should use.

Input.

Control	Where the key lives	Leaks on snapshot theft	Joinable?	Write cost
Native masking	nowhere (plaintext on disk)	everything, clear	yes	~0
Randomized encryption	KMS (via envelope)	ciphertext only	no	1 local AES op
Deterministic encryption	KMS (via envelope)	ciphertext + frequency	yes (equality)	1 local AES op
Tokenization (vault)	vault, off-warehouse	tokens only	equality via token	1 vault call

Code.

-- The physical column choices fall straight out of the grid:
--   email        -> deterministic column encryption (must JOIN)
--   national_id  -> randomized column encryption OR tokenization (never joined)

CREATE TABLE analytics.customers (
    id            BIGINT       NOT NULL,
    -- deterministic ciphertext: same email -> same bytes, so JOIN still works
    email_det     BINARY       NOT NULL,   -- AES-SIV output
    -- randomized ciphertext: maximal secrecy, never needs equality
    national_id_c BINARY,                  -- AES-GCM output (nonce||ct||tag)
    -- wrapped DEK stored beside the ciphertext (envelope encryption)
    dek_wrapped   BINARY       NOT NULL,
    region        VARCHAR      NOT NULL,    -- not sensitive; plaintext
    ltv_cents     BIGINT
);

Step-by-step explanation.

1. The email column must still JOIN against an events table keyed on email, so randomized encryption is off the table (it would make every ciphertext unique and joins impossible). Deterministic encryption keeps equal plaintexts equal, so JOIN ON a.email_det = b.email_det works — at the cost of leaking which emails are most frequent.
2. The national_id column is never joined or filtered — a compliance export reads it in bulk and decrypts in a trusted service. That means randomized AES-GCM is the correct choice: maximum secrecy, no frequency leakage, and the loss of queryability costs nothing because nothing queries it.
3. Both columns share one envelope: a single data encryption key encrypts every value in the row (or the table), and the KMS-wrapped DEK is stored in dek_wrapped next to the ciphertext. Nothing calls the KMS per row.
4. region and ltv_cents are not regulated, so they stay plaintext — encrypting non-sensitive columns just burns CPU and blocks legitimate analytics. Encrypt only what the classification says is sensitive.
5. If PCI-DSS scope reduction were the goal for national_id (or a card number), tokenization would replace step 2 — the warehouse would store a token and never touch the raw value, pulling it out of audit scope entirely.

Output.

Column	Chosen control	Why
email	deterministic column encryption	must JOIN; accept frequency leak
national_id	randomized column encryption	never queried; maximal secrecy
card_number (if present)	tokenization vault	descope the warehouse from PCI
region, ltv_cents	plaintext	not sensitive; keep analytics fast

Rule of thumb. Never pick a protection control by reputation. Pick it per column from (key location × breach leak × queryability × write cost). Write the grid on a whiteboard first; the control falls out of the column's classification and its query pattern.

Worked example — what interviewers actually probe

Detailed explanation. The senior data-protection interview has a predictable arc: an ambiguous opener ("how would you protect PII in our warehouse?"), then progressive narrowing to test whether you know the axes. Candidates who say "we turn on at-rest encryption" score lowest; candidates who name envelope encryption, pick a mode per column, and reach for crypto-shredding score highest. Walk through the grading rubric.

• Ambiguous opener. "How do you protect the SSN and card columns in the warehouse?" — invites the control choice.
• Follow-up 1. "The warehouse already encrypts at rest — isn't that enough?" — probes the threat model.
• Follow-up 2. "You need to join on the email — now what?" — probes deterministic vs randomized.
• Follow-up 3. "Where does the key live and who can reach it?" — probes envelope + KMS + custody.
• Follow-up 4. "How do you honour a delete-my-data request across all backups?" — probes crypto-shredding.

Question. Draft a five-minute senior answer that covers threat model, control choice, key custody, and erasure without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Threat model	"it's encrypted at rest"	"at-rest guards a stolen disk, not a leaked query result"
Control	"we hash it"	"envelope-encrypted columns; deterministic where we must join"
Key custody	"in a config file"	"DEK from KMS-issued key; KEK in KMS, never in the app"
Erasure	"DELETE the rows"	"crypto-shred: destroy the per-tenant key"
Descoping	"encrypt the card"	"tokenize the card so the warehouse leaves PCI scope"

Code.

Senior column-protection answer template (5 minutes)
====================================================

Minute 1 — threat model first
  "Provider at-rest encryption protects a stolen physical disk. It does
   NOT protect a leaked query result, a public backup, or an
   over-privileged role. We encrypt sensitive VALUES so the plaintext
   never exists on disk or in a query result."

Minute 2 — control per column
  "Randomized AES-GCM for columns we never query (SSN, national ID).
   Deterministic AES-SIV for columns we must still JOIN on (email,
   customer key). Format-preserving FF3-1 when the ciphertext must keep
   the column's type and length. Tokenization for card data so the
   warehouse is out of PCI scope entirely."

Minute 3 — envelope encryption + KMS
  "One data encryption key (DEK) encrypts the values locally. The DEK
   is wrapped by a key-encryption-key (KEK) that lives in KMS and never
   enters the app. We store the wrapped DEK beside the ciphertext.
   Rotating the KEK re-wraps the DEK; it never re-encrypts terabytes."

Minute 4 — custody (BYOK / HYOK)
  "For regulated tenants: BYOK imports the customer's key material into
   our KMS; HYOK keeps the key in the customer's HSM and we call out to
   unwrap. Revoking the key revokes access to the data."

Minute 5 — erasure via crypto-shredding
  "Right-to-erasure is a key-shred, not a DELETE. Per-tenant DEK: to
   erase a tenant we destroy their key. The ciphertext in every backup
   and cold copy becomes permanently unrecoverable, no scanning
   required."

Step-by-step explanation.

1. Minute 1 is the framing that scores. Correcting "encrypted at rest is enough" by naming the actual threat model — leaked query results, public backups, over-privileged roles — immediately signals you understand what column encryption is for.
2. Minute 2 picks a mode per column instead of one blanket answer. Naming randomized vs deterministic vs format-preserving vs tokenization, and tying each to a query pattern, is the differentiator.
3. Minute 3 names envelope encryption explicitly. Weak candidates say "we encrypt with a key" and can't answer "where does the key live?"; strong candidates describe the DEK/KEK split and why it makes rotation cheap.
4. Minute 4 addresses custody before being asked. BYOK and HYOK are the words that show you've dealt with regulated or multi-tenant customers who demand their own key.
5. Minute 5 reframes erasure as crypto-shredding. "DELETE the rows" fails the follow-up about backups and cold storage; "destroy the per-tenant key" is O(1) and provably complete.

Output.

Grading criterion	Weak score	Senior score
Corrects the at-rest threat model	rare	mandatory
Picks a mode per column	rare	required
Names envelope encryption + KMS	rare	senior signal
Names BYOK/HYOK custody	rare	senior signal
Reframes erasure as crypto-shred	rare	senior signal

Rule of thumb. The senior answer is a five-minute monologue: correct the threat model, pick a mode per column, envelope-encrypt with a KMS-held KEK, name the custody model, and erase by shredding the key. Rehearse it once; deploy it every interview.

Worked example — the "pick the control" decision tree

Detailed explanation. Given a new sensitive column, the senior architect runs a short decision tree in their head. Codifying it makes the interview answer reproducible: any interviewer can hand you a column and you can walk the tree out loud. Walk the tree with three canonical columns: a national ID that is never queried, an email that must join, and a card number under PCI.

• Q1. Is this column ever joined, filtered by equality, or range-scanned? → no = go to Q2; yes (equality) = go to Q3; yes (range) = consider order-preserving or a blind index.
• Q2. Not queried → randomized AES-GCM (maximum secrecy). Done.
• Q3. Equality-only and PCI/card data? → yes = tokenization (descope); no = deterministic AES-SIV.
• Q4. Must the ciphertext keep the column's type/length (legacy schema, downstream validators)? → yes = format-preserving FF3-1; no = binary ciphertext is fine.
• Q5 (parallel). Does a regulator/tenant demand to own the key or to erase on demand? → yes = BYOK/HYOK + per-tenant DEK for crypto-shredding.

Question. Walk the decision tree for the three columns and record the control each ends up with.

Input.

Column	Q1 (queried?)	Q3 (PCI?)	Q4 (keep format?)	Q5 (custody?)
national_id	no	—	—	tenant erasure
email	equality (join)	no	no	—
card_number	equality	yes	yes	—

Code.

# Decision-tree helper (illustrative)
def pick_column_control(queried: str,      # 'no' | 'equality' | 'range'
                        is_pci: bool,
                        keep_format: bool,
                        needs_custody: bool) -> list[str]:
    """Return the protection control(s) for one sensitive column."""
    controls = []

    if queried == "no":
        controls.append("randomized AES-GCM")
    elif queried == "equality":
        if is_pci:
            controls.append("tokenization vault")
        elif keep_format:
            controls.append("format-preserving FF3-1 (deterministic)")
        else:
            controls.append("deterministic AES-SIV")
    else:  # range
        controls.append("order-preserving / blind index (evaluate carefully)")

    if needs_custody:
        controls.append("BYOK/HYOK + per-tenant DEK (crypto-shred)")

    return controls


print(pick_column_control("no",       False, False, True))
# -> ['randomized AES-GCM', 'BYOK/HYOK + per-tenant DEK (crypto-shred)']

print(pick_column_control("equality", False, False, False))
# -> ['deterministic AES-SIV']

print(pick_column_control("equality", True,  True,  False))
# -> ['tokenization vault']

Step-by-step explanation.

1. national_id is never queried, so Q1 short-circuits to randomized AES-GCM — the strongest, frequency-hiding choice. Because it also needs tenant-level erasure, Q5 adds a per-tenant DEK so the tenant can be crypto-shredded later.
2. email is joined by equality with no PCI constraint and no format requirement, so the tree lands on deterministic AES-SIV — equal emails produce equal ciphertext, joins work, and the only cost is frequency leakage.
3. card_number is equality-queried and PCI, so Q3 routes to tokenization: the warehouse stores a token, the raw PAN never lands, and the warehouse is out of PCI-DSS scope. (If tokenization weren't available, the keep_format branch would pick FF3-1 so the ciphertext stays a 16-digit string.)
4. The Q5 branch is orthogonal to the mode — you can layer per-tenant keys and BYOK/HYOK custody on top of any column control. Crypto-shredding requires only that the key be per-tenant and destroyable.
5. If none of Q1–Q4 give a clean answer (a column that must be range-scanned, e.g. salary), the honest answer is "order-preserving encryption leaks ordering and is often unsafe; prefer keeping that column in a separate trusted store rather than the shared warehouse." Refuse to force a bad mode onto a range-query column.

Output.

Column	Primary control	Add-on
national_id	randomized AES-GCM	per-tenant DEK (crypto-shred)
email	deterministic AES-SIV	—
card_number	tokenization vault	—

Rule of thumb. The five-question decision tree is a whiteboard-friendly answer. Practice walking it end to end so an interviewer can hand you any column — SSN, email, salary, card — and get a control name in under 60 seconds.

Senior interview question on choosing a protection control

A senior interviewer often opens with: "You inherit a Postgres → Snowflake pipeline that lands customers with a plaintext email, national_id, and card_number. Security wants the warehouse out of PCI scope, analysts still need to join on email, and legal wants a working right-to-erasure. Walk me through the control you'd apply to each column, where the keys live, and how you'd honour a deletion across every backup."

Solution Using envelope encryption per column mode plus tokenization and per-tenant crypto-shredding

# Ingestion transform — apply the right control per column before load
import boto3
from cryptography.hazmat.primitives.ciphers.aead import AESGCM, AESSIV
import os

kms = boto3.client("kms")
KEK_ID = "arn:aws:kms:us-east-1:acct:key/tenant-<id>-kek"

def new_dek():
    """Envelope: one KMS call issues a plaintext DEK + its wrapped form."""
    resp = kms.generate_data_key(KeyId=KEK_ID, KeySpec="AES_256")
    return resp["Plaintext"], resp["CiphertextBlob"]   # (dek, wrapped_dek)

def protect_row(row, dek, card_tokenizer):
    # national_id: never queried -> randomized AES-GCM
    nonce = os.urandom(12)
    nid_ct = nonce + AESGCM(dek).encrypt(nonce, row["national_id"].encode(), None)

    # email: must JOIN -> deterministic AES-SIV (no nonce; equal in -> equal out)
    email_ct = AESSIV(dek + dek).encrypt([row["email"].encode()])  # SIV needs 512-bit key

    # card_number: PCI -> tokenize; the raw PAN never enters the warehouse
    card_tok = card_tokenizer.tokenize(row["card_number"])

    return {
        "id": row["id"],
        "email_det": email_ct,      # joinable ciphertext
        "national_id_c": nid_ct,    # opaque ciphertext
        "card_token": card_tok,     # surrogate; PCI descoped
        "region": row["region"],    # plaintext (not sensitive)
    }

-- Per-tenant key catalogue enables O(1) crypto-shredding on erasure
CREATE TABLE governance.tenant_keys (
    tenant_id     BIGINT       PRIMARY KEY,
    kek_arn       TEXT         NOT NULL,        -- KMS KEK per tenant
    wrapped_dek   BYTEA        NOT NULL,        -- DEK wrapped by that KEK
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    shredded_at   TIMESTAMPTZ                   -- set when the tenant is erased
);

-- Right-to-erasure = destroy the tenant's key material, not the rows
-- 1. schedule KMS key deletion (irreversible after the waiting period)
--    aws kms schedule-key-deletion --key-id <tenant kek> --pending-window-in-days 7
-- 2. forget the wrapped DEK
UPDATE governance.tenant_keys
SET    wrapped_dek = NULL,
       shredded_at = now()
WHERE  tenant_id = :tenant_id;
-- After this, every ciphertext for that tenant -- in the warehouse,
-- in cold storage, in every backup -- is permanently undecryptable.

Step-by-step trace.

Column	Control applied	On snapshot theft	Query still works?
email	deterministic AES-SIV	ciphertext + frequency	JOIN on email_det
national_id	randomized AES-GCM	ciphertext only	no (by design)
card_number	tokenization vault	token only	equality on token
region	plaintext	region only (not sensitive)	yes
(keys)	KMS KEK per tenant	wrapped DEK, unusable	n/a

After deployment, the warehouse holds only ciphertext and tokens for the three sensitive columns; a leaked snapshot yields nothing decryptable because the KEKs live in KMS and never in the pipeline. Analysts join on email_det because equal emails encrypt to equal bytes. The card column is a token, so the warehouse is out of PCI-DSS audit scope. When legal receives an erasure request, the platform schedules deletion of that tenant's KEK and nulls the wrapped DEK — every backup copy of the tenant's data becomes permanently unrecoverable with a single key operation.

Output:

Metric	Before (plaintext columns)	After (envelope + tokenize)
Breach exposure (stolen snapshot)	full PII in clear	ciphertext + tokens only
Email joinability	yes	yes (deterministic)
PCI scope of warehouse	in scope	descoped (tokens only)
Right-to-erasure across backups	scan + DELETE everywhere	O(1) key-shred
KMS calls per row	n/a	0 (envelope amortises)

Why this works — concept by concept:

• Envelope encryption — one generate_data_key KMS call issues a plaintext DEK plus its wrapped form; the DEK encrypts millions of values locally and the wrapped DEK is stored beside the ciphertext. The KEK never leaves KMS, so a pipeline compromise leaks only wrapped keys.
• Mode per column — randomized AES-GCM for the never-queried national_id (frequency-hiding), deterministic AES-SIV for the joinable email (equal-in-equal-out), tokenization for the PCI card_number (the raw PAN never lands). The query pattern chooses the mode, not habit.
• Tokenization for PCI descoping — because the warehouse only ever stores a surrogate token, it is not "storing cardholder data" and drops out of PCI-DSS scope, shrinking the audit surface to the tokenization service alone.
• Per-tenant key + crypto-shredding — one DEK per tenant means erasure is a key-destroy, not a data-scan. Deleting the tenant's KEK renders every ciphertext copy — warehouse, cold storage, backups — permanently unrecoverable, satisfying right-to-erasure at O(1).
• Cost — one KMS call per DEK (not per row), one local AES op per value, one vault call per card, and zero re-encryption on key rotation or erasure. Compared to plaintext columns the marginal cost is microseconds per value; compared to scanning every backup for a deletion it is O(1) versus O(all copies).

SQL
Topic — sql
SQL access-control and sensitive-column problems

Practice →

Design Topic — design Design problems on data-protection systems

Practice →

---

2. Envelope encryption and KMS — the DEK/KEK hierarchy

envelope encryption uses a short-lived data encryption key wrapped by a KMS-held key-encryption-key — so you rotate the outer key without re-encrypting terabytes

The mental model in one line: envelope encryption is the pattern where a locally-generated data encryption key (DEK) encrypts the actual data, a long-lived key-encryption-key (KEK) that lives in a KMS wraps (encrypts) that DEK, and you store the wrapped DEK next to the ciphertext — this lets you encrypt millions of rows with a single KMS call, keep the master key material inside the KMS where the application can never read it, and rotate the KEK by simply re-wrapping the DEK instead of re-encrypting the underlying data. Every serious column-encryption design on AWS, GCP, or Azure sits on top of this two-level hierarchy; understanding why there are two levels is the difference between a design that scales and one that melts the KMS rate limit.

The four axes for envelope encryption.

• Where the key lives. The KEK lives inside the KMS/HSM and is never exported — the application can ask the KMS to Encrypt/Decrypt (wrap/unwrap) a DEK, but it never sees the KEK bytes. The DEK exists in application memory only transiently while encrypting a batch, then is discarded; only the wrapped DEK is persisted. What lands on disk is ciphertext + wrapped DEK, never a usable key.
• Latency and throughput. A naive "encrypt each row with a KMS call" design hits KMS request limits (a few thousand requests/second) almost immediately and adds a network round-trip per row. Envelope encryption makes one GenerateDataKey call to mint a DEK, then encrypts an entire batch/partition/table locally with that DEK. KMS is touched once per DEK, not once per row.
• Rotation. Because the KEK only ever encrypts DEKs (kilobytes), rotating the KEK means re-wrapping each DEK — cheap and fast — with no need to re-encrypt the terabytes of data the DEK protects. This decoupling is the entire point of the two-level design.
• Blast radius. A leaked DEK exposes only the data that one DEK encrypted (one tenant, one partition, one column — however you scoped it). A leaked KEK would be catastrophic, which is exactly why it never leaves the KMS. Scope the DEK narrowly (per tenant, per table) to shrink the blast radius of any single key compromise.

KMS anatomy — the three calls that matter.

• GenerateDataKey. Returns a fresh DEK in both forms in one call: Plaintext (use it to encrypt, then wipe from memory) and CiphertextBlob (the wrapped DEK — persist this). This is the write-path primitive.
• Decrypt (unwrap). Give the KMS the wrapped DEK; it returns the plaintext DEK if the caller's IAM principal is allowed by the key policy. This is the read-path primitive — you unwrap the DEK, decrypt the data, wipe the DEK.
• Key policy + grants. The KEK's key policy is the real access boundary: it decides which principals may Decrypt. Column encryption's security ultimately rests on this policy, not on the warehouse's grants. Lock it to the specific ingestion and read services.

The wrapped-DEK storage pattern.

• Store the wrapped DEK with the ciphertext. A per-row wrapped DEK, a per-partition sidecar, or a per-table key catalogue — the wrapped DEK travels with the data so any reader who is allowed to Decrypt can unwrap and read.
• Cache the unwrapped DEK carefully. Unwrapping on every read re-hits the KMS. A short-lived in-memory DEK cache (seconds to minutes) cuts KMS calls dramatically — but a DEK cached too long widens the window where a memory dump leaks a live key. Bound the cache TTL.
• Never log the plaintext DEK. The most common real-world envelope-encryption incident is a DEK printed into a debug log or an exception trace. Treat the plaintext DEK like a password: never log it, never serialise it, wipe it after use.

Common interview probes on envelope encryption.

• "Why two levels of keys instead of one?" — required answer: rotate the KEK without re-encrypting data, and keep the master key inside the KMS.
• "How do you encrypt a billion rows without hitting the KMS rate limit?" — one GenerateDataKey per batch, encrypt locally.
• "What do you store next to the ciphertext?" — the wrapped DEK (and a nonce/IV).
• "What happens if you lose the KEK?" — the wrapped DEK can never be unwrapped; the data is gone. (This is a feature for crypto-shredding, a disaster otherwise.)

Worked example — envelope-encrypt a column with a KMS-issued DEK

Detailed explanation. The canonical envelope write path: call GenerateDataKey once, use the plaintext DEK to AES-GCM-encrypt every value in a batch, persist the ciphertext plus the wrapped DEK, and wipe the plaintext DEK. Then the read path unwraps the DEK once and decrypts the batch. Build both paths.

• Write. One KMS GenerateDataKey; encrypt N values locally; store ciphertext + wrapped DEK.
• Read. One KMS Decrypt to unwrap; decrypt N values locally; wipe DEK.
• Scope. One DEK per batch (or per tenant) to bound blast radius.

Question. Implement the envelope write and read for a batch of national_id values, storing one wrapped DEK per batch.

Input.

Parameter	Value
KEK	KMS key alias/pii-kek
DEK spec	AES-256
Cipher	AES-GCM (randomized)
DEK scope	one per batch
Stored per row	nonce + ciphertext

Code.

import boto3, os
from cryptography.hazmat.primitives.ciphers.aead import AESGCM

kms = boto3.client("kms")

def envelope_encrypt_batch(values: list[str], kek_alias="alias/pii-kek"):
    # 1. ONE KMS call mints a DEK in plaintext + wrapped form
    resp = kms.generate_data_key(KeyId=kek_alias, KeySpec="AES_256")
    dek, wrapped_dek = resp["Plaintext"], resp["CiphertextBlob"]

    aead = AESGCM(dek)
    rows = []
    try:
        # 2. Encrypt every value LOCALLY with the one DEK (no KMS per row)
        for v in values:
            nonce = os.urandom(12)
            ct = aead.encrypt(nonce, v.encode(), None)   # ct includes GCM tag
            rows.append(nonce + ct)                       # store nonce||ct
    finally:
        # 3. Wipe the plaintext DEK from memory as soon as we are done
        dek = b"\x00" * len(dek)
    return rows, wrapped_dek          # persist rows + ONE wrapped_dek per batch


def envelope_decrypt_batch(rows: list[bytes], wrapped_dek: bytes):
    # 1. ONE KMS call unwraps the DEK for the whole batch
    dek = kms.decrypt(CiphertextBlob=wrapped_dek)["Plaintext"]
    aead = AESGCM(dek)
    out = []
    try:
        for blob in rows:
            nonce, ct = blob[:12], blob[12:]
            out.append(aead.decrypt(nonce, ct, None).decode())
    finally:
        dek = b"\x00" * len(dek)
    return out

Step-by-step explanation.

1. generate_data_key is the single KMS round-trip for the entire batch. It returns the DEK twice: Plaintext (used immediately to encrypt) and CiphertextBlob (the wrapped DEK, persisted). This one-call-per-batch shape is what keeps you under the KMS request ceiling at billions of rows.
2. Every value is encrypted locally with AESGCM(dek). A fresh random 12-byte nonce per value is mandatory for GCM — reusing a nonce with the same key is catastrophic (it breaks confidentiality). We store nonce || ciphertext so decryption can recover the nonce.
3. The finally block overwrites the plaintext DEK in memory the moment encryption finishes. This shrinks the window where a memory dump or crash core could leak a live key. The wrapped DEK that survives is useless without a KMS Decrypt call the attacker cannot make.
4. On read, one kms.decrypt(wrapped_dek) unwraps the DEK for the whole batch — again one KMS call, not one per row. IAM decides whether this call succeeds; a reader without kms:Decrypt on the KEK gets an access-denied error and never sees plaintext.
5. Scoping one DEK per batch (or per tenant) bounds the blast radius: a single leaked DEK exposes only that batch. Finer scoping means more GenerateDataKey calls; coarser scoping means a bigger blast radius. Tune the granularity to your threat model.

Output.

Stage	KMS calls	Local ops	Persisted
Encrypt 1M-row batch	1 (GenerateDataKey)	1M AES-GCM	1M (nonce+ct) + 1 wrapped DEK
Decrypt 1M-row batch	1 (Decrypt)	1M AES-GCM	—
Rotate KEK	re-wrap DEKs only	0 data re-encrypt	new wrapped DEKs

Rule of thumb. One GenerateDataKey per batch, encrypt locally, store nonce||ciphertext plus one wrapped DEK, and wipe the plaintext DEK in a finally. Never call the KMS per row — that is the mistake that turns a working design into a throttled outage.

Worked example — rotate the KEK without re-encrypting the data

Detailed explanation. KEK rotation is where the two-level hierarchy pays off. Because the KEK only wraps DEKs, rotation re-wraps each DEK under the new KEK version — a kilobyte operation — and never touches the ciphertext. Walk through both automatic rotation (KMS handles it) and manual re-wrap (when you rotate to a brand-new KEK).

• Automatic. KMS rotates the KEK's backing material yearly; old versions are retained so old wrapped DEKs still unwrap. Zero action needed.
• Manual re-wrap. To move to a new KEK entirely, unwrap each DEK under the old KEK and re-wrap under the new one. The data is never decrypted.

Question. Re-wrap every batch's DEK from an old KEK to a new KEK without decrypting any data.

Input.

Component	Value
Old KEK	alias/pii-kek-2025
New KEK	alias/pii-kek-2026
What changes	wrapped DEK only
What does NOT change	ciphertext rows

Code.

def rewrap_dek(wrapped_dek: bytes, old_kek: str, new_kek: str) -> bytes:
    # 1. Unwrap under the OLD KEK (KMS knows which version from the blob)
    dek = kms.decrypt(CiphertextBlob=wrapped_dek)["Plaintext"]
    try:
        # 2. Re-wrap the SAME DEK under the NEW KEK
        new_wrapped = kms.encrypt(KeyId=new_kek, Plaintext=dek)["CiphertextBlob"]
    finally:
        dek = b"\x00" * len(dek)     # never persist the plaintext DEK
    return new_wrapped               # the ciphertext ROWS are untouched

-- Rotation touches only the key catalogue, never the data tables
UPDATE governance.batch_keys
SET    wrapped_dek = :new_wrapped,
       kek_alias   = 'alias/pii-kek-2026',
       rotated_at  = now()
WHERE  batch_id    = :batch_id;

-- The multi-terabyte fact table with the ciphertext columns is NOT rewritten.

Step-by-step explanation.

1. kms.decrypt unwraps the DEK under the old KEK. KMS embeds the key-version in the wrapped blob, so it automatically uses the right version — you don't pass the old KEK id on decrypt.
2. kms.encrypt(KeyId=new_kek, ...) re-wraps the same DEK bytes under the new KEK. The DEK value is unchanged, so the data it encrypted stays valid; only its wrapper changes. The plaintext DEK exists in memory for microseconds and is wiped in finally.
3. The only persisted change is the wrapped_dek (and kek_alias) in the key catalogue — a kilobyte-per-batch update. The terabyte ciphertext tables are never read or rewritten during rotation.
4. This is why the DEK/KEK split exists: rotation cost is O(number of DEKs), not O(volume of data). With one DEK per tenant, rotating the master key across a petabyte warehouse is thousands of tiny KMS calls, not a petabyte re-encryption job.
5. For routine rotation you often need nothing at all — KMS automatic key rotation keeps old key versions so historical wrapped DEKs still unwrap; you only do manual re-wrap when policy demands moving to an entirely new KEK (e.g. a suspected compromise or a custody change).

Output.

Rotation type	Data re-encrypted	Work done	Downtime
KMS automatic	none	0 (KMS internal)	none
Manual re-wrap to new KEK	none	re-wrap N DEKs	none
(Naive re-encrypt data)	all rows	O(volume)	large

Rule of thumb. Rotate the KEK by re-wrapping DEKs, never by re-encrypting data. If your "key rotation" plan involves rewriting the fact table, you have not used envelope encryption — you have used a single-level key and inherited its rotation pain.

Senior interview question on envelope encryption

A senior interviewer might ask: "You must column-encrypt a 5-billion-row events table landing in Snowflake, with a per-tenant key so tenants can be crypto-shredded independently, and a yearly key-rotation requirement. Walk me through the envelope-encryption design, how you avoid hammering the KMS, the DEK caching strategy on read, and how rotation and crypto-shredding both work without re-encrypting the data."

Solution Using per-tenant KEKs, per-batch DEKs, a bounded DEK cache, and re-wrap rotation

import boto3, os, time
from cryptography.hazmat.primitives.ciphers.aead import AESGCM

kms = boto3.client("kms")
_dek_cache: dict[str, tuple[bytes, float]] = {}   # wrapped_dek_id -> (dek, expiry)
DEK_TTL_SECONDS = 60                               # bound the live-key window

def tenant_kek(tenant_id: int) -> str:
    return f"alias/pii-kek-tenant-{tenant_id}"      # per-tenant KEK = per-tenant shred

def encrypt_partition(tenant_id: int, values: list[str]):
    # ONE DEK per (tenant, partition) -> bounded blast radius, few KMS calls
    resp = kms.generate_data_key(KeyId=tenant_kek(tenant_id), KeySpec="AES_256")
    dek, wrapped = resp["Plaintext"], resp["CiphertextBlob"]
    aead = AESGCM(dek)
    rows = []
    try:
        for v in values:
            n = os.urandom(12)
            rows.append(n + aead.encrypt(n, v.encode(), None))
    finally:
        dek = b"\x00" * len(dek)
    return rows, wrapped

def get_dek(wrapped: bytes) -> bytes:
    key = wrapped[:16].hex()                         # cache key = blob prefix
    hit = _dek_cache.get(key)
    if hit and hit[1] > time.time():
        return hit[0]                                # cache hit: no KMS call
    dek = kms.decrypt(CiphertextBlob=wrapped)["Plaintext"]
    _dek_cache[key] = (dek, time.time() + DEK_TTL_SECONDS)
    return dek

-- Per-tenant key catalogue: rotation and crypto-shred both live here
CREATE TABLE governance.tenant_keys (
    tenant_id    BIGINT       NOT NULL,
    partition_id BIGINT       NOT NULL,
    kek_alias    TEXT         NOT NULL,
    wrapped_dek  BYTEA,                      -- NULL after crypto-shred
    rotated_at   TIMESTAMPTZ,
    shredded_at  TIMESTAMPTZ,
    PRIMARY KEY (tenant_id, partition_id)
);

-- Yearly rotation: re-wrap DEKs under the new KEK version (data untouched)
-- Crypto-shred a tenant: schedule KMS deletion of their KEK + NULL the DEKs
UPDATE governance.tenant_keys
SET    wrapped_dek = NULL, shredded_at = now()
WHERE  tenant_id = :tenant_id;

Step-by-step trace.

Concern	Mechanism	Result
KMS rate limit	1 GenerateDataKey per (tenant, partition)	~thousands of calls, not billions
Read amplification	60 s DEK cache	most reads skip KMS entirely
Live-key exposure	cache TTL bounded at 60 s	short memory-leak window
Rotation	re-wrap DEKs under new KEK	zero data re-encryption
Crypto-shred	delete tenant KEK + NULL DEKs	tenant data unrecoverable
Blast radius	one DEK per tenant-partition	one leaked DEK = one partition

After deployment, the 5-billion-row load makes one GenerateDataKey per tenant-partition — a few thousand KMS calls, not five billion. Reads unwrap each DEK once and cache it for 60 seconds, so a dashboard scanning a partition pays a single KMS Decrypt. Yearly rotation re-wraps the DEKs under the new KEK version and never rewrites the ciphertext. When a tenant exercises right-to-erasure, scheduling deletion of their KEK plus nulling the wrapped DEKs renders every ciphertext copy of that tenant — warehouse, replicas, backups — permanently undecryptable.

Output:

Metric	Value
KMS calls, 5B-row initial load	~1 per tenant-partition
KMS calls per dashboard read	~1 per partition per 60 s
KEK rotation cost	O(number of DEKs), data untouched
Crypto-shred cost	O(1) per tenant
Blast radius of one leaked DEK	one tenant-partition

Why this works — concept by concept:

• GenerateDataKey per batch — the envelope write primitive. One KMS call mints a DEK that encrypts a whole partition locally, keeping the design under the KMS request ceiling regardless of row count.
• Per-tenant KEK — scoping the master key to a tenant makes crypto-shredding a per-tenant operation and bounds any single DEK's blast radius to one tenant-partition. Custody and erasure both hang off this scoping.
• Bounded DEK cache — caching the unwrapped DEK for a short TTL collapses read-path KMS calls from per-read to per-TTL, while the TTL cap keeps the window where a live key sits in memory small. It is the deliberate trade between KMS load and key exposure.
• Re-wrap rotation — because the KEK only wraps DEKs, rotation re-wraps kilobytes and leaves the terabytes untouched. Rotation cost is decoupled from data volume, which is the whole reason the hierarchy has two levels.
• Crypto-shred via key deletion — erasure is a key operation, not a data scan. Destroying the tenant KEK and forgetting the wrapped DEKs makes the ciphertext unrecoverable everywhere at once — the strongest and cheapest right-to-erasure primitive.
• Cost — one KMS mint per partition, one KMS unwrap per partition per TTL, one local AES op per value, O(DEKs) rotation, O(1) erasure. Compared to per-row KMS calls this is orders of magnitude fewer requests; compared to data re-encryption on rotation it is kilobytes versus terabytes.

SQL
Topic — sql
SQL problems on key catalogues and secure lookups

Practice →

Design Topic — design Design problems on key-management hierarchies

Practice →

---

3. Application-side column encryption — deterministic, randomized, format-preserving

Randomized encryption is maximally secret but kills equality; deterministic encryption restores the join and leaks the frequency; format-preserving encryption keeps the shape

The mental model in one line: application-side column encryption comes in three modes with a hard trade-off — randomized (AES-GCM, a fresh nonce per value) makes every ciphertext unique and leaks nothing but length, but destroys equality so you can never join or filter on the column; deterministic encryption (AES-SIV, no nonce) makes equal plaintexts produce equal ciphertext so joins and equality lookups work, at the cost of leaking the frequency distribution; and format-preserving encryption (FF3-1) produces ciphertext of the same type and length as the input so a 16-digit card stays 16 digits and drops into an existing schema — and choosing the mode per column is the core design skill. The mode is dictated by the query pattern, and getting it wrong either breaks your analytics or over-exposes your data.

The three modes and what each buys.

• Randomized (AES-GCM). A random nonce per encryption means the same plaintext encrypts to a different ciphertext every time. Leaks only the length. No equality, no join, no filter — the column becomes write-and-bulk-decrypt only. Correct for columns that are stored but never queried (SSN, raw PAN before tokenization, free-text notes).
• Deterministic (AES-SIV). No nonce (or a synthetic IV derived from the plaintext), so equal plaintexts always yield equal ciphertext. Restores WHERE col = ?, JOIN ON, GROUP BY, and DISTINCT. The cost: an attacker sees which ciphertexts repeat, i.e. the frequency histogram — for a low-cardinality column (gender, boolean, country) that can effectively reveal the values.
• Format-preserving (FF3-1). A NIST-specified FPE mode that maps a string over an alphabet to another string of the same length and alphabet. A 16-digit card number becomes another 16-digit number; a fixed-length ID keeps its format. Deterministic by nature (so joinable) and drop-in for legacy CHAR(n) columns and downstream validators that check length/Luhn.

The frequency-leakage problem — the deterministic tax.

• What leaks. With deterministic encryption an attacker who steals the column can count how often each ciphertext appears. If they know the plaintext distribution (e.g. country codes, where "US" dominates), they can map the most common ciphertext to the most common plaintext — a frequency analysis attack.
• When it's acceptable. High-cardinality, high-entropy columns (email, account number, UUID) leak little useful information because almost every value is unique. Deterministic encryption is safe here.
• When it's dangerous. Low-cardinality columns (sex, blood type, small enums) leak almost everything under frequency analysis. Do not deterministically encrypt low-cardinality sensitive columns; use randomized encryption plus a separate access path, or don't put them in the shared warehouse.
• The per-column key. Use a distinct key per column so ciphertexts can't be correlated across columns, and consider a per-tenant key so one tenant's frequency profile doesn't leak into another's.

The blind-index alternative to deterministic encryption.

• The idea. Instead of deterministically encrypting the searchable column, store it randomized (secret) and add a separate "blind index" column: a keyed HMAC (or truncated HMAC) of the plaintext. Equality search hits the blind index; the real value stays randomized.
• The trade. A blind index lets you tune leakage — a truncated HMAC creates deliberate collisions (many plaintexts share a bucket), so an attacker sees buckets, not exact frequencies, while you still narrow a lookup to a small candidate set and confirm by decrypting.
• When to prefer it. When you need equality search on a low-to-medium cardinality column but deterministic encryption would leak too much — the blind index's bucket size is a leakage dial deterministic encryption doesn't have.

Common interview probes on the modes.

• "Can you join on an encrypted column?" — only if it's deterministic (or via a blind index); randomized destroys the join.
• "What does deterministic encryption leak?" — the frequency distribution of the column.
• "How do you encrypt a card number in place without changing the schema?" — format-preserving encryption (FF3-1).
• "Deterministic on a sex column — okay?" — no; low cardinality means frequency analysis recovers it. Use randomized or a blind index.

Worked example — deterministic-encrypt an email so you can still JOIN on it

Detailed explanation. The canonical deterministic use case: an email column that must join across a customers table and an events table, both encrypted, without ever exposing the plaintext. AES-SIV gives equal-in-equal-out ciphertext so the join key matches. Build the encryption and the join.

• Cipher. AES-SIV (RFC 5297), deterministic, no nonce required.
• Key. Per-column DEK from the envelope (section 2), distinct from other columns' keys.
• Join. JOIN ON c.email_det = e.email_det on the ciphertext.

Question. Deterministically encrypt email in both tables and show that a join on the ciphertext returns the right rows.

Input.

Component	Value
Cipher	AES-SIV (deterministic)
Column	email in customers and events
Key scope	per-column DEK
Join	on the deterministic ciphertext

Code.

from cryptography.hazmat.primitives.ciphers.aead import AESSIV

# email_dek is a 512-bit key (AES-SIV uses two 256-bit halves), from the envelope
def enc_email(email_dek: bytes, email: str) -> bytes:
    siv = AESSIV(email_dek)
    # No nonce -> deterministic: same email always -> same ciphertext
    return siv.encrypt([email.strip().lower().encode()])   # normalise first!

# Encrypt on ingest for BOTH tables with the SAME per-column key
cust_rows  = [(cid, enc_email(EMAIL_DEK, e)) for cid, e in raw_customers]
event_rows = [(eid, enc_email(EMAIL_DEK, e)) for eid, e in raw_events]

-- The join runs on the deterministic ciphertext; plaintext never appears
SELECT c.id            AS customer_id,
       count(e.id)     AS event_count
FROM   analytics.customers c
JOIN   analytics.events    e
       ON e.email_det = c.email_det        -- ciphertext equality = plaintext equality
GROUP  BY c.id;

Step-by-step explanation.

1. AES-SIV is deterministic by construction: with no external nonce, the synthetic IV is derived from the plaintext and key, so identical inputs always yield identical outputs. That equality is exactly what a join needs.
2. Normalising the plaintext before encrypting (strip().lower()) is critical — deterministic encryption only matches on byte-identical input, so [e-mail] and [e-mail] would otherwise become different ciphertexts and fail to join. Canonicalise every value the same way on every write.
3. Both tables must be encrypted with the same per-column key. If customers.email and events.email used different keys, equal plaintexts would encrypt to different ciphertext and the join would silently return zero rows.
4. The SQL join compares email_det byte-for-byte. Because ciphertext equality mirrors plaintext equality under deterministic encryption, the join produces exactly the rows a plaintext join would — but the warehouse never holds a plaintext email.
5. The accepted leakage: an attacker with the column sees which encrypted emails repeat most. For email (high cardinality, near-unique per person) that reveals almost nothing useful, which is why deterministic is the right call here and would be the wrong call on a low-cardinality column.

Output.

Plaintext email (never stored)	Deterministic ciphertext (stored)	Joins?
[e-mail]	0x9f3a…c1	matches across tables
[e-mail]	0x22b8…4e	matches across tables
[e-mail] (again)	0x9f3a…c1 (identical)	yes

Rule of thumb. Deterministic-encrypt only high-cardinality columns you must join on, always normalise the plaintext before encrypting, and use one shared per-column key across every table that must join. Ciphertext equality is the whole trick — protect it by canonicalising inputs.

Worked example — format-preserving encryption of a card number in place

Detailed explanation. A legacy card_number CHAR(16) column and a downstream validator that runs a Luhn check mean the ciphertext must also be a 16-digit number. FF3-1 maps a digit string to another digit string of the same length, so the encrypted value drops into the existing column and passes format validation while being cryptographically protected. Build the FPE encrypt/decrypt.

• Cipher. FF3-1 over the decimal alphabet (radix 10).
• Format. 16-digit input → 16-digit output; type and length preserved.
• Tweak. A per-record "tweak" (non-secret) diversifies ciphertext where needed.

Question. Format-preserving-encrypt a 16-digit PAN so it stays 16 digits and fits CHAR(16), and show the round-trip.

Input.

Component	Value
Algorithm	FF3-1 (NIST SP 800-38G rev)
Alphabet	digits 0–9 (radix 10)
Input	4111111111111111 (16 digits)
Output	16 digits, same column

Code.

# Illustrative FF3-1 usage (via an FPE library); FF3-1 preserves length+radix
from ff3 import FF3Cipher

# key + tweak come from the envelope DEK; radix 10 = decimal digits
cipher = FF3Cipher.withCustomAlphabet(FPE_KEY_HEX, FPE_TWEAK_HEX, "0123456789")

def fpe_encrypt_pan(pan16: str) -> str:
    assert len(pan16) == 16 and pan16.isdigit()
    ct = cipher.encrypt(pan16)      # -> another 16-digit string
    assert len(ct) == 16            # SAME length: fits CHAR(16)
    return ct

def fpe_decrypt_pan(ct16: str) -> str:
    return cipher.decrypt(ct16)     # exact inverse

enc = fpe_encrypt_pan("4111111111111111")   # e.g. "8352771044190028"
dec = fpe_decrypt_pan(enc)                   # -> "4111111111111111"

-- No schema change: the ciphertext still fits the existing CHAR(16) column
-- card_number CHAR(16)  -- was plaintext PAN; now holds FF3-1 ciphertext
-- Downstream length/format validators keep passing because the shape is preserved.
UPDATE payments.cards
SET    card_number = :fpe_ciphertext        -- 16 digits in, 16 digits out
WHERE  id = :id;

Step-by-step explanation.

1. FF3-1 is a format-preserving encryption mode: given an alphabet (here decimal digits) and a key, it encrypts a string to another string over the same alphabet and length. A 16-digit input becomes a 16-digit output — no base64, no binary blob, no schema migration.
2. The output drops straight into the existing CHAR(16) column. Legacy code that assumes a 16-character numeric card number keeps working; format/length validators still pass (though a Luhn check would fail unless you special-case it — FPE preserves length, not the checksum).
3. FF3-1 is deterministic for a given key and tweak, so it is joinable like AES-SIV — and it carries the same frequency-leakage caveat. The optional per-record tweak lets you diversify ciphertext (e.g. tweak by customer id) to reduce cross-record correlation where you don't need to join.
4. Decryption is the exact inverse under the same key and tweak, recovering the original PAN in a trusted service. Only that service holds the key; the warehouse holds only the format-preserving ciphertext.
5. Use FPE specifically when the format matters — legacy fixed-width columns, downstream systems that validate length/type, or partial-reveal needs (FPE lets you keep the last four digits clear while encrypting the rest). If format doesn't matter, plain AES-GCM/AES-SIV into a BINARY column is simpler and has stronger, better-studied security margins.

Output.

Stage	Value	Length	Fits CHAR(16)?
Plaintext PAN	4111111111111111	16	yes
FF3-1 ciphertext	8352771044190028	16	yes (no migration)
Decrypted	4111111111111111	16	round-trips exactly

Rule of thumb. Reach for format-preserving encryption only when the shape of the column must be preserved — legacy fixed-width schemas, format validators, or partial reveal. When the shape is free to change, binary AES-GCM/AES-SIV is the simpler, stronger default.

Senior interview question on encryption modes

A senior interviewer might ask: "You have a customers table with email (analysts join on it daily), national_id (only a compliance export reads it), and blood_type (needed for a rare medical report). Design the per-column encryption modes, justify each against the query pattern and the leakage, and explain why a naive 'deterministic-encrypt everything' design would be a security bug."

Solution Using deterministic email, randomized national_id, and a blind index for the low-cardinality column

from cryptography.hazmat.primitives.ciphers.aead import AESGCM, AESSIV
import hmac, hashlib, os

# email: HIGH cardinality, must join -> deterministic AES-SIV
def enc_email(dek512: bytes, email: str) -> bytes:
    return AESSIV(dek512).encrypt([email.strip().lower().encode()])

# national_id: never queried -> randomized AES-GCM (frequency-hiding)
def enc_nid(dek: bytes, nid: str) -> bytes:
    n = os.urandom(12)
    return n + AESGCM(dek).encrypt(n, nid.encode(), None)

# blood_type: LOW cardinality (8 values) -> randomized value + BLIND INDEX
#   store the value randomized; search via a truncated keyed HMAC bucket
def enc_blood(dek: bytes, idx_key: bytes, bt: str) -> tuple[bytes, str]:
    n = os.urandom(12)
    ct = n + AESGCM(dek).encrypt(n, bt.encode(), None)          # secret value
    full = hmac.new(idx_key, bt.encode(), hashlib.sha256).hexdigest()
    blind_bucket = full[:2]     # TRUNCATE to 1 byte -> deliberate collisions
    return ct, blind_bucket

-- Storage: each column's mode matches its query pattern and leakage budget
CREATE TABLE analytics.customers (
    id            BIGINT  NOT NULL,
    email_det     BYTEA   NOT NULL,      -- deterministic: joinable
    national_id_c BYTEA,                 -- randomized: opaque, never queried
    blood_type_c  BYTEA,                 -- randomized value (secret)
    blood_bucket  CHAR(2)                -- blind index: coarse, bucketed search
);

-- Equality search on the low-cardinality column via the blind index,
-- then confirm by decrypting only the small candidate set in a trusted svc.
SELECT id, blood_type_c
FROM   analytics.customers
WHERE  blood_bucket = :query_bucket;     -- narrows, does not reveal

Step-by-step trace.

Column	Cardinality	Query pattern	Mode chosen	Leakage
email	high	daily join	deterministic AES-SIV	near-zero (near-unique)
national_id	high	never queried	randomized AES-GCM	none but length
blood_type	low (8)	rare equality	randomized + blind index	coarse buckets only
(naive) blood_type	low (8)	—	deterministic	full frequency = values

After deployment, analysts join on email_det because deterministic ciphertext preserves equality; national_id is opaque AES-GCM and leaks nothing because nothing queries it; and blood_type is stored randomized (so its exact frequency never leaks) with a truncated HMAC blind index that buckets many plaintexts together — an equality lookup narrows to a bucket and a trusted service decrypts the handful of candidates. The naive "deterministic everything" design would have turned blood_type into a frequency histogram that trivially recovers all eight values.

Output:

Design choice	Naive (deterministic all)	This solution
email join	works	works
national_id secrecy	frequency leak (harmless, high card)	full secrecy
blood_type secrecy	full recovery via frequency	coarse buckets only
blood_type search	direct equality	blind-index bucket + decrypt
leakage dial	none	truncation length tunes it

Why this works — concept by concept:

• Deterministic for high-cardinality joins — AES-SIV makes equal emails equal ciphertext, so the daily join works. Because email is near-unique, the frequency it leaks is not exploitable, so the trade is safe here.
• Randomized for never-queried columns — AES-GCM with a per-value nonce gives national_id maximal secrecy. Losing queryability costs nothing because nothing queries it — the mode matches the access pattern.
• Blind index for low-cardinality search — storing blood_type randomized keeps its exact frequency hidden; a truncated keyed-HMAC bucket enables equality search while deliberately colliding many plaintexts, so the attacker sees buckets, not values. The truncation length is an explicit leakage dial.
• Per-column keys — distinct DEKs per column stop an attacker from correlating ciphertext across columns. The blind-index key is separate again, so the index can't be reversed with the encryption key.
• Why deterministic-everything is a bug — on a low-cardinality column, deterministic ciphertext repeats exactly as often as its plaintext, so frequency analysis recovers the values. Choosing the mode by cardinality-and-query-pattern, not by convenience, is the security decision.
• Cost — one local AES op per value, one HMAC per blind-index write, and a slightly larger candidate set to decrypt on a bucketed search. Compared to deterministic-everything it trades a little read work for closing a real frequency-analysis hole.

SQL
Topic — sql
SQL join and equality-lookup problems

Practice →

Data Transformation Topic — data-transformation Data-transformation problems on encode/decode pipelines

Practice →

---

4. Tokenization vaults — vault-based vs vaultless

tokenization swaps the sensitive value for a meaningless surrogate — the warehouse only ever sees the token, which is how you drop it out of PCI scope

The mental model in one line: tokenization is the pattern where a sensitive value (a card number, an SSN) is replaced before it reaches the warehouse by a surrogate token that carries no exploitable relationship to the original, and the ability to reverse the token back to the value lives in a separate, locked-down tokenization service — vault-based tokenization keeps a stored token↔value map, vaultless tokenization derives the token cryptographically (via FPE) with no stored map, and in both cases the warehouse never holds the raw value, which is precisely how it drops out of PCI-DSS audit scope. Tokenization and encryption are cousins, but the compliance framing is different: an encrypted card is still "cardholder data you store"; a token is not, so the systems that only see tokens are out of scope.

Tokenization vs encryption — the distinction interviewers test.

• Reversibility mechanism. Encryption reverses with a key — anyone with the key and the ciphertext recovers the value. Tokenization reverses with a lookup (vault) or a derivation (vaultless FPE) that lives behind a service you must call and be authorised by. There is no "key" that unlocks a vault-based token; there is only the vault.
• Compliance scope. This is the headline. Under PCI-DSS, a system that stores encrypted PANs is still in scope (it stores cardholder data). A system that stores only tokens is out of scope if the tokens are non-reversible without the vault. Tokenization is chosen specifically to shrink the audit boundary to the tokenization service.
• Analytics. Tokens are typically deterministic, so the same card always tokenizes to the same token — you can still GROUP BY card, count distinct cards, and join on the token, all without the raw PAN ever entering the warehouse.
• Format. Tokens are usually format-preserving (a 16-digit token for a 16-digit card, often with the last four digits preserved for display) so they drop into existing schemas and UIs.

Vault-based tokenization.

• The store. A dedicated, heavily-secured datastore holds the token → value mapping (and the reverse). Generating a token inserts a new mapping (or returns the existing one for that value if deterministic).
• The strength. The token has no cryptographic relationship to the value — it's a random surrogate. Even a full compromise of the token, without the vault, reveals nothing. This is the strongest confidentiality model.
• The weakness. The vault is a single point of failure and a scaling bottleneck: every tokenize and detokenize is a call to it, it must be highly available, and it grows with the number of distinct values. Backup, replication, and access control of the vault are the whole ballgame.

Vaultless tokenization.

• The idea. Derive the token from the value using format-preserving encryption keyed by a secret — no stored map. "Detokenize" is just decrypting the token with the key. It's essentially FPE rebranded for the tokenization/compliance conversation.
• The strength. No vault to scale, replicate, or become a bottleneck; tokenization is a local cryptographic operation. Scales horizontally.
• The weakness. Because it's keyed encryption, whoever holds the key can reverse every token — the security rests on the key (and its KMS custody), exactly like column encryption. It is arguably not "true" tokenization to a strict auditor because the token is reversible with a key. Know your auditor's definition.

Detokenization — the need-to-know path.

• Separate, audited path. Detokenization must be a distinct, tightly-authorised service call — not a capability every reader has. Only the narrow set of processes that genuinely need the raw value (payment settlement, fraud review) may detokenize, and every call is logged.
• Never bulk-detokenize into the warehouse. The entire value of tokenization evaporates if a job detokenizes a column back into a warehouse table. Detokenize per-record, on demand, in a trusted service, and never persist the result in a shared store.

Common interview probes on tokenization.

• "How does tokenization differ from encryption?" — reversal via vault lookup/derivation vs via a key; and PCI scope reduction.
• "Vault-based vs vaultless?" — stored map (strongest secrecy, scaling bottleneck) vs FPE-derived (scales, key-reversible).
• "Why tokenize instead of encrypt the card?" — to take the warehouse out of PCI-DSS scope.
• "How do you detokenize safely?" — a separate, authorised, audited service call; never bulk into the warehouse.

Worked example — tokenize a card number through a vault

Detailed explanation. The canonical vault flow: a tokenization service receives a PAN, checks the vault for an existing token (deterministic reuse), otherwise mints a random format-preserving token, stores the mapping, and returns the token. The warehouse ingests only the token. Build the tokenize call and the vault schema.

• Vault. token_vault(token PK, value_ciphertext, last4, created_at) — the value itself is stored encrypted inside the vault.
• Tokenize. Deterministic reuse if the value already has a token; else mint a new one.
• Warehouse. Stores card_token and last4 only.

Question. Implement the tokenize path and show what the warehouse row looks like.

Input.

Component	Value
Vault store	token_vault (secured, off-warehouse)
Token format	16-digit, last4 preserved
Determinism	same PAN → same token
Warehouse stores	token + last4 only

Code.

import os, secrets

class TokenVault:
    def __init__(self, db, value_dek):
        self.db = db                       # the locked-down vault datastore
        self.value_dek = value_dek         # encrypts the stored value at rest

    def tokenize(self, pan: str) -> str:
        # 1. Deterministic reuse: if this PAN already has a token, return it
        existing = self.db.lookup_token_by_value_hash(_hmac(pan))
        if existing:
            return existing

        # 2. Mint a random, format-preserving surrogate (last4 preserved)
        token = _mint_token(pan)           # 16 digits, ends in pan[-4:]

        # 3. Store token -> encrypted value mapping INSIDE the vault
        self.db.insert(token=token,
                       value_ct=_aesgcm_encrypt(self.value_dek, pan),
                       value_hash=_hmac(pan),   # for deterministic reuse lookup
                       last4=pan[-4:])
        return token

def _mint_token(pan: str) -> str:
    body = "".join(secrets.choice("0123456789") for _ in range(12))
    return body + pan[-4:]                  # random 12 + real last4 for display

-- The WAREHOUSE table never stores a PAN -- only the token + last4
CREATE TABLE analytics.payments (
    id          BIGINT   NOT NULL,
    card_token  CHAR(16) NOT NULL,     -- surrogate; out of PCI scope
    last4       CHAR(4)  NOT NULL,     -- safe to display
    amount      BIGINT   NOT NULL,
    region      VARCHAR
);
-- Analysts GROUP BY card_token to count distinct cards -- no PAN needed.

Step-by-step explanation.

1. The tokenize call first checks whether this PAN already has a token (via a keyed hash lookup, so the vault index isn't a plaintext PAN list). Deterministic reuse means the same card always maps to the same token, which is what lets analytics GROUP BY card_token.
2. If no token exists, the service mints a fresh surrogate: 12 random digits plus the real last four for display. The random body has no cryptographic relationship to the PAN — you cannot derive the card from the token without the vault.
3. The mapping is stored inside the vault with the value itself encrypted at rest (AES-GCM under a vault DEK). Even a vault-database compromise yields ciphertext, not PANs — defence in depth on top of the surrogate.
4. The warehouse ingests only card_token and last4. It never receives, stores, or logs the PAN, so it is not "storing cardholder data" and is out of PCI-DSS scope — the audit boundary shrinks to the vault and the tokenization service.
5. Analysts do real work on the token: count distinct cards, join purchases by card, detect a card used across regions — all on the surrogate, all without any process in the warehouse ever seeing a real card number.

Output.

Location	Stores	In PCI scope?
Tokenization service + vault	token ↔ encrypted PAN map	yes (the only place)
Warehouse payments	card_token, last4, amount	no (tokens only)
Analyst query result	tokens, counts, last4	no

Rule of thumb. Tokenize before the value reaches the warehouse, store the real value encrypted inside the vault, and expose only the token plus a display-safe last4 downstream. The moment a raw PAN lands in the warehouse — even briefly, even in a log — the warehouse is back in scope.

Worked example — vaultless tokenization with FPE (no stored map)

Detailed explanation. When the vault's scaling and availability burden is unacceptable, vaultless tokenization derives the token from the value with format-preserving encryption — no stored mapping, so no vault to replicate or bottleneck on. Detokenize is decrypt. Build it and contrast the operational profile.

• Tokenize. token = FF3-1_encrypt(key, value) — deterministic, format-preserving, no storage.
• Detokenize. value = FF3-1_decrypt(key, token) — a keyed operation behind an authorised service.
• Trade. No vault to scale; but the key reverses every token, so key custody is everything.

Question. Implement vaultless tokenize/detokenize and state when it beats a vault.

Input.

Component	Value
Mechanism	FF3-1 keyed FPE
Stored map	none
Detokenize	decrypt with the key
Key custody	KMS-wrapped DEK

Code.

from ff3 import FF3Cipher

# Key + tweak from the envelope; no vault, no stored mapping
_cipher = FF3Cipher.withCustomAlphabet(TOK_KEY_HEX, TOK_TWEAK_HEX, "0123456789")

def tokenize_vaultless(pan: str) -> str:
    body = _cipher.encrypt(pan[:-4])        # encrypt all but last4
    return body + pan[-4:]                    # keep last4 for display

def detokenize_vaultless(token: str) -> str:  # authorised, audited service only
    return _cipher.decrypt(token[:-4]) + token[-4:]

# No database write on tokenize -> scales horizontally, no vault SPOF

Step-by-step explanation.

1. tokenize_vaultless runs FF3-1 over the sensitive portion and appends the real last four for display. It is a pure local cryptographic operation — no database insert, no network call to a vault — so it scales with CPU and has no single point of failure.
2. Determinism falls out of FPE for free (same key/tweak → same token), so analytics still work: the same card always tokenizes identically, joinable and countable like the vault case.
3. detokenize_vaultless is just decryption, but it must be gated behind the same authorised, audited service boundary as vault detokenization — the difference is where reversibility lives (a key vs a vault), not who's allowed to use it.
4. The operational win is enormous at scale: no vault to shard, replicate across regions, back up, or keep highly available. The tokenization tier is stateless behind the key.
5. The operational risk is concentrated in the key: because anyone with the key can reverse every token, the key's KMS custody, rotation, and access policy are the entire security posture — and a strict PCI assessor may treat a key-reversible token as encryption rather than tokenization, so confirm the compliance framing before committing.

Output.

Property	Vault-based	Vaultless (FPE)
Stored mapping	yes (token↔value)	none
Scaling	vault is the bottleneck	stateless, horizontal
Reversal secret	the vault	the key
Strongest secrecy	yes (random surrogate)	key-dependent
Auditor view	classic tokenization	may be treated as encryption

Rule of thumb. Choose vault-based tokenization when you need the strongest possible secrecy and a strict PCI boundary and can operate a highly-available vault; choose vaultless FPE when scale and availability dominate and your assessor accepts key-derived tokens. The decision is operational and regulatory, not just cryptographic.

Senior interview question on tokenization

A senior interviewer might ask: "Payments wants card analytics in the warehouse — count distinct cards, cards-per-customer, cross-region reuse — but security demands the warehouse stay out of PCI-DSS scope. Design the tokenization architecture: where tokenization happens, vault vs vaultless, how analysts still get their aggregates, and how the narrow set of services that need the real PAN detokenize safely."

Solution Using edge tokenization, deterministic tokens for analytics, and an audited detokenization service

# 1. Tokenize at the EDGE -- before the value ever enters the warehouse path
def ingest_payment(event, vault, warehouse):
    token = vault.tokenize(event["pan"])       # PAN never continues downstream
    warehouse.write({
        "id":         event["id"],
        "card_token": token,                    # deterministic -> analytics work
        "last4":      event["pan"][-4:],
        "amount":     event["amount"],
        "region":     event["region"],
    })
    # event["pan"] is dropped here; it is never logged or persisted downstream

-- 2. Analysts get every aggregate they need -- on the token, never the PAN
-- distinct cards
SELECT count(DISTINCT card_token) AS distinct_cards FROM analytics.payments;
-- cards used across multiple regions (fraud signal)
SELECT card_token, count(DISTINCT region) AS regions
FROM   analytics.payments
GROUP  BY card_token
HAVING count(DISTINCT region) > 1;

# 3. Detokenization is a SEPARATE, authorised, audited service -- narrow callers only
class DetokenizeService:
    def detokenize(self, token: str, principal: str, reason: str) -> str:
        if not self.authz.allowed(principal, "detokenize"):
            raise PermissionError("not authorised to detokenize")
        self.audit.log(principal=principal, token=token, reason=reason)  # every call logged
        return self.vault.detokenize(token)     # per-record; never bulk into a table

Step-by-step trace.

Stage	What sees the PAN	What is stored
Edge ingest	tokenization service only	token + last4 in warehouse
Warehouse analytics	nothing	tokens, counts, last4
Fraud / settlement	detokenize service (authorised)	nothing persisted
Audit	never the PAN	who detokenized what, when, why

After deployment, the PAN is tokenized at the edge and never flows into the warehouse, so the warehouse — and every analyst query, dashboard, and BI extract built on it — is out of PCI-DSS scope. Analysts still compute distinct-card counts, cards-per-customer, and cross-region reuse because the tokens are deterministic. The only place a real card number can be recovered is the audited detokenization service, which authorises the caller, logs every request with a reason, and returns exactly one value per call — never a bulk dump.

Output:

Metric	Before (PAN in warehouse)	After (edge tokenization)
Warehouse PCI scope	fully in scope	out of scope
Distinct-card analytics	works	works (deterministic tokens)
Cross-region fraud signal	works	works
PAN exposure surface	every warehouse reader	detokenize service only
Detokenization audit	none	per-call, with reason

Why this works — concept by concept:

• Edge tokenization — swapping the PAN for a token before the warehouse path means the raw value never lands, so the warehouse is not "storing cardholder data" and drops out of PCI-DSS scope. Placement, not just the algorithm, is what descopes.
• Deterministic tokens — the same card always tokenizes identically, so count(DISTINCT card_token), cards-per-customer, and cross-region reuse all work on the surrogate. Analytics keeps its power without the sensitive value.
• Vault-stored encrypted value — the real PAN lives only inside the vault, encrypted at rest, so even a vault-database compromise yields ciphertext, not cards. Defence in depth behind the surrogate.
• Audited detokenization path — reversal is a separate, authorised, logged service that returns one value per call and never bulk-detokenizes into a shared store. This is what keeps the descoping real instead of theoretical.
• Cost — one vault call per ingest and per detokenize, a highly-available vault to operate, and a stateless analytics tier that touches only tokens. Compared to encrypting the PAN in place, tokenization adds vault operations but removes the entire warehouse from audit scope — usually a large net win for card data.

Design
Topic — design
Design problems on tokenization and scope reduction

Practice →

Database Topic — database Database problems on surrogate keys and lookup stores

Practice →

---

5. BYOK / HYOK for warehouses — who owns the key

BYOK imports your key material into the provider's KMS; HYOK keeps the key in your own HSM and never releases it — and either one lets you crypto-shred a tenant

The mental model in one line: BYOK (bring your own key) and HYOK (hold your own key) are two custody models that answer "who controls the master key protecting the warehouse" — BYOK imports customer-supplied key material into the cloud provider's KMS so the provider can decrypt with a key the customer generated (and can revoke), while HYOK keeps the key permanently inside the customer's own HSM so it never leaves customer premises and the warehouse must call out to unwrap on every use — and both models put the ultimate on/off switch in the customer's hands, which is exactly what enables crypto-shredding: destroy the key and the data becomes unrecoverable everywhere at once. The trade is control versus availability: HYOK gives maximal control and couples the warehouse's availability to the customer's HSM; BYOK gives strong control with the provider's operational reliability.

The custody spectrum — provider-managed → BYOK → HYOK.

• Provider-managed keys. The cloud provider generates and holds the KEK; the customer trusts the provider entirely. Simplest, but the customer cannot independently revoke access and has no cryptographic proof the provider can't read the data.
• BYOK. The customer generates the key material (often in their own HSM), then imports the wrapped material into the provider's KMS. The provider now decrypts with a key the customer created and can schedule for deletion. The key does live in the provider KMS during use, so this is about control and revocation, not about the key never touching the provider.
• HYOK / external key manager. The key never enters the provider's infrastructure. The warehouse calls out to the customer's external key manager (HSM) for every wrap/unwrap. Maximal control and a hard availability coupling: if the customer's HSM is unreachable, the warehouse cannot decrypt.

The per-warehouse implementations.

• Snowflake Tri-Secret Secure. Combines a Snowflake-maintained key with a customer-managed key (in the cloud KMS) so that both are required to decrypt — the customer's half is the revocation lever. Backed by BYOK-style customer-managed keys.
• BigQuery CMEK / EKM. Customer-Managed Encryption Keys point BigQuery at a Cloud KMS key the customer controls (BYOK-style); Cloud External Key Manager (EKM) is the HYOK-style option where the key lives in a third-party/on-prem HSM and BigQuery calls out to it.
• Databricks customer-managed keys. Customer-managed keys in the cloud KMS protect managed storage and the control-plane secrets — BYOK-style custody with customer revocation.
• Redshift + KMS. Redshift encrypts with a KMS CMK; pointing it at a customer-managed CMK gives BYOK-style control and the ability to revoke by disabling the key.

Crypto-shredding — the erasure lever both models enable.

• The pattern. Encrypt each tenant's (or each subject's) data under a distinct key. To erase that tenant/subject, destroy the key. The ciphertext in the live warehouse, every replica, every backup, and every cold archive becomes permanently undecryptable — no scanning required.
• Why it beats DELETE. A DELETE (or even overwrite) cannot reach every backup, snapshot, and downstream copy; proving completeness is nearly impossible. Key destruction is O(1), provable, and reaches every copy simultaneously because none of them are decryptable without the key.
• The requirement. Crypto-shredding only works if the key is scoped to the erasure unit (per tenant, per data subject) — a single shared key means shredding erases everyone. Per-tenant DEKs (section 2) are the enabling design.

Common interview probes on BYOK/HYOK.

• "BYOK vs HYOK?" — imported-into-provider-KMS-with-revocation vs never-leaves-your-HSM-with-availability-coupling.
• "How do you honour right-to-erasure at warehouse scale?" — crypto-shred a per-tenant/per-subject key.
• "What's the risk of HYOK?" — your warehouse's availability is now coupled to your HSM's availability.
• "Name a warehouse feature for this." — Snowflake Tri-Secret Secure, BigQuery CMEK/EKM, Databricks customer-managed keys, Redshift customer CMK.

Worked example — wire a customer-managed KEK into the envelope hierarchy

Detailed explanation. BYOK in practice means the per-tenant KEK from section 2 is a customer-managed key: the customer controls its lifecycle (create, disable, schedule-delete) in the cloud KMS, and the warehouse's DEKs are wrapped by it. Disabling the customer key instantly severs decryption. Build the wiring and the revocation.

• Customer KEK. A KMS key whose policy grants the customer control (including disable/delete).
• DEKs. Wrapped by the customer KEK; stored beside the ciphertext.
• Revoke. Disable the customer KEK → all unwraps fail → access severed with no data change.

Question. Wire per-tenant customer-managed KEKs into the envelope path and show how disabling one revokes access instantly.

Input.

Component	Value
KEK	customer-managed KMS key per tenant
Control	customer can disable / schedule-delete
DEK	wrapped by the customer KEK
Revoke	disable the KEK (reversible) or delete (shred)

Code.

# The KEK id resolves to the TENANT'S customer-managed KMS key
def tenant_customer_kek(tenant_id: int) -> str:
    return CUSTOMER_KEK_ARNS[tenant_id]      # customer created + controls this key

def encrypt_for_tenant(tenant_id: int, values: list[str]):
    resp = kms.generate_data_key(KeyId=tenant_customer_kek(tenant_id),
                                 KeySpec="AES_256")
    dek, wrapped = resp["Plaintext"], resp["CiphertextBlob"]
    # ... encrypt locally, store ciphertext + wrapped DEK ... (as in section 2)
    return wrapped

def read_for_tenant(wrapped_dek: bytes):
    # If the customer has DISABLED their KEK, this call fails -> access revoked
    try:
        dek = kms.decrypt(CiphertextBlob=wrapped_dek)["Plaintext"]
    except kms.exceptions.DisabledException:
        raise PermissionError("tenant key disabled: access revoked by customer")
    # ... decrypt locally ...

# Customer-driven revocation (reversible) -- no data is touched:
aws kms disable-key --key-id <tenant customer KEK>
#   -> every wrapped DEK for that tenant can no longer be unwrapped
#   -> the warehouse cannot decrypt that tenant's columns
#   -> re-enable to restore; schedule-key-deletion to crypto-shred permanently

Step-by-step explanation.

1. The per-tenant KEK is a customer-managed KMS key — the customer generated it (or imported the material via BYOK) and holds the IAM/key-policy rights to disable or delete it. The warehouse pipeline only ever asks the KMS to wrap/unwrap; it cannot override the customer's control.
2. Encryption is unchanged from section 2 — GenerateDataKey under the tenant's customer KEK issues a DEK, values are encrypted locally, and the wrapped DEK is stored beside the ciphertext. The only difference is whose key sits at the top of the envelope.
3. Revocation is a single KMS call by the customer: disable-key. Immediately, every Decrypt of that tenant's wrapped DEKs fails with a disabled-key error, so the warehouse can no longer read that tenant's protected columns — without any change to the stored data.
4. Disabling is reversible (re-enable to restore access); this is the "suspend access" control. To make erasure permanent the customer schedules key deletion, after which the wrapped DEKs can never be unwrapped again — the crypto-shred.
5. Because access hinges on the customer's key, the customer has cryptographic assurance that revoking the key revokes access — the provider cannot read the data with the key disabled/deleted. This is the trust property BYOK exists to provide.

Output.

Customer action	KMS effect	Warehouse effect	Reversible?
Key enabled	unwrap succeeds	tenant data readable	—
disable-key	unwrap fails	access revoked	yes (re-enable)
schedule-key-deletion	key destroyed after window	permanently unreadable	no (crypto-shred)

Rule of thumb. Make each tenant's KEK a customer-managed key so revocation is a single customer-side KMS call and no data rewrite. Disable to suspend, delete to shred. The warehouse must treat a disabled-key error as "access revoked," not as a bug to retry around.

Worked example — crypto-shred a tenant on offboarding

Detailed explanation. Right-to-erasure and tenant offboarding both reduce to the same operation when keys are per-tenant: destroy the tenant's key and record it. No scan of the warehouse, no reach into backups — the ciphertext everywhere becomes noise. Build the offboarding routine and prove completeness.

• Precondition. Each tenant's data is encrypted under a tenant-scoped DEK/KEK (never a shared key).
• Shred. Schedule deletion of the tenant KEK; null the wrapped DEKs; record the timestamp.
• Proof. After the KMS deletion window, unwrap is impossible → data provably unrecoverable.

Question. Implement the crypto-shred and explain why it satisfies erasure across backups a DELETE cannot.

Input.

Component	Value
Key scope	one KEK per tenant
Shred step 1	schedule KMS key deletion
Shred step 2	NULL wrapped DEKs + record shredded_at
Coverage	live + replicas + backups + cold archives

Code.

def crypto_shred_tenant(tenant_id: int):
    kek = tenant_customer_kek(tenant_id)

    # 1. Schedule irreversible destruction of the tenant's master key
    kms.schedule_key_deletion(KeyId=kek, PendingWindowInDays=7)

    # 2. Forget every wrapped DEK for the tenant and stamp the shred
    db.execute("""
        UPDATE governance.tenant_keys
        SET    wrapped_dek = NULL, shredded_at = now()
        WHERE  tenant_id = %s
    """, (tenant_id,))

    # 3. No need to touch the (possibly petabytes of) ciphertext anywhere.
    #    After the KMS window, no wrapped DEK can be unwrapped -> data is noise.
    audit.log(event="crypto_shred", tenant_id=tenant_id)

-- Proof-of-erasure report: which tenants are shredded and unrecoverable
SELECT tenant_id, shredded_at,
       (wrapped_dek IS NULL) AS dek_forgotten
FROM   governance.tenant_keys
WHERE  shredded_at IS NOT NULL;
-- The ciphertext columns still physically exist but are undecryptable forever.

Step-by-step explanation.

1. The precondition is per-tenant key scoping — the whole pattern collapses if tenants share a key, because shredding one would erase all. Section 2's per-tenant DEK/KEK design is what makes this offboarding routine possible.
2. schedule_key_deletion starts the irreversible destruction of the tenant's KEK (KMS enforces a mandatory waiting window as a safety net against mistakes). Once the window elapses, the key material is gone and can never wrap or unwrap again.
3. Nulling the wrapped DEKs removes even the wrapped copies from the operational store, and the shredded_at stamp records exactly when erasure was initiated — the auditable proof.
4. Crucially, no ciphertext is touched. The tenant's encrypted rows still sit in the live table, in read replicas, in last night's backup, and in the cold archive — but every one of those copies is now undecryptable because the only key that could unwrap their DEKs is being destroyed. Erasure reaches all copies without visiting any of them.
5. This is precisely why crypto-shredding beats DELETE for right-to-erasure: a DELETE must find and remove every copy across live storage, replicas, snapshots, and backups (and prove it did), which is operationally near-impossible; key destruction is O(1) and provably complete because completeness follows from cryptography, not from having scanned every copy.

Output.

Erasure approach	Reaches backups?	Cost	Provable?
DELETE rows	no (backups untouched)	O(copies) scan	hard
Overwrite + DELETE	partially	O(copies)	hard
Crypto-shred key	yes (all copies at once)	O(1)	yes (no key = no plaintext)

Rule of thumb. Design erasure as key destruction from day one by scoping a key per erasure unit (tenant or data subject). When legal asks "prove the data is gone," the answer is "the key is destroyed, so no copy — live or backup — is decryptable," which is stronger and cheaper than any DELETE-everywhere campaign.

Senior interview question on BYOK/HYOK and crypto-shredding

A senior interviewer might ask: "You run a multi-tenant analytics platform on Snowflake. A regulated banking customer demands to hold their own key, be able to revoke your access at any moment, and get a provable right-to-erasure. Walk me through BYOK vs HYOK for this, how you'd scope keys for crypto-shredding, the availability trade-off HYOK introduces, and what breaks if their key store goes down."

Solution Using per-tenant customer-managed keys, Tri-Secret-style dual control, and crypto-shred erasure

# 1. Per-tenant custody: each tenant's KEK is THEIR customer-managed key.
#    Banking tenant -> HYOK (external key manager); others -> BYOK (KMS import).
def tenant_key_config(tenant_id: int) -> dict:
    cfg = TENANT_KEY_CONFIG[tenant_id]
    # cfg = {"mode": "HYOK", "ekm_endpoint": "...", "kek": "..."} or
    #       {"mode": "BYOK", "kek": "arn:aws:kms:...:key/imported-..."}
    return cfg

def unwrap_dek(tenant_id: int, wrapped_dek: bytes) -> bytes:
    cfg = tenant_key_config(tenant_id)
    if cfg["mode"] == "HYOK":
        # Warehouse calls OUT to the customer's HSM; key never leaves it
        return external_key_manager(cfg["ekm_endpoint"]).unwrap(wrapped_dek)
    else:  # BYOK: customer-managed key inside the provider KMS
        return kms.decrypt(CiphertextBlob=wrapped_dek)["Plaintext"]

# 2. Revocation + erasure are single customer-side operations
def revoke_access(tenant_id: int):        # reversible: suspend
    cfg = tenant_key_config(tenant_id)
    if cfg["mode"] == "HYOK":
        external_key_manager(cfg["ekm_endpoint"]).disable()   # HSM stops unwrapping
    else:
        kms.disable_key(KeyId=cfg["kek"])                     # KMS stops unwrapping

def erase_tenant(tenant_id: int):         # irreversible: crypto-shred
    cfg = tenant_key_config(tenant_id)
    if cfg["mode"] == "HYOK":
        external_key_manager(cfg["ekm_endpoint"]).destroy()
    else:
        kms.schedule_key_deletion(KeyId=cfg["kek"], PendingWindowInDays=7)
    db.execute("UPDATE governance.tenant_keys SET wrapped_dek=NULL, shredded_at=now() "
               "WHERE tenant_id=%s", (tenant_id,))

# 3. Availability trade-off, made explicit
# HYOK:  warehouse decrypt  =>  round-trip to customer HSM
#        customer HSM DOWN   =>  that tenant's encrypted columns are UNREADABLE
#        (their availability is now coupled to their own key store)
# BYOK:  key lives in provider KMS (high SLA); customer keeps disable/delete control
#        weaker "never touches provider" guarantee, stronger availability

Step-by-step trace.

Requirement	BYOK	HYOK	Chosen for banking tenant
Customer controls key lifecycle	yes (KMS import)	yes (own HSM)	HYOK
Key never touches provider	no	yes	HYOK
Revoke access instantly	disable KMS key	disable HSM	HYOK
Crypto-shred erasure	schedule KMS delete	destroy in HSM	HYOK
Availability of tenant data	provider KMS SLA	coupled to customer HSM	trade accepted

After deployment, the banking tenant runs HYOK: their key lives in their own external key manager, Snowflake (via an external-key-manager integration, the Tri-Secret-style dual-control model) calls out to unwrap on each use, and the key material never enters the provider. The customer can disable the key to instantly suspend all decryption, or destroy it to crypto-shred their data across every copy. Other tenants run BYOK — customer-managed keys imported into the provider KMS, giving them revocation and shredding with the provider's availability SLA. The explicit cost the banking tenant accepts: if their HSM is unreachable, their columns are temporarily unreadable, because availability is now coupled to a key store they alone operate.

Output:

Metric	BYOK tenants	HYOK banking tenant
Key location	provider KMS (imported)	customer HSM only
Revoke access	disable KMS key	disable HSM key
Right-to-erasure	schedule KMS deletion	destroy in HSM
Availability coupling	provider SLA	customer HSM uptime
Provider can read w/o key	no	no

Why this works — concept by concept:

• Per-tenant customer-managed KEK — scoping the master key to each tenant makes revocation and crypto-shredding per-tenant operations, and puts the on/off switch in the customer's hands. It is the foundation both custody models build on.
• BYOK vs HYOK — BYOK imports the customer's key into the provider KMS (control + revocation, provider availability); HYOK keeps it in the customer HSM and calls out to unwrap (maximal control, availability coupled to the HSM). The banking tenant's "key never touches you" demand forces HYOK.
• Tri-Secret-style dual control — requiring both a provider key and the customer key to decrypt means the customer's half is a hard revocation lever; neither party alone can read the data. It is BYOK/HYOK expressed as a warehouse feature.
• Crypto-shred erasure — destroying the tenant's key renders every ciphertext copy unrecoverable at once, giving a provable right-to-erasure that a DELETE across backups cannot match. Per-tenant scoping is what keeps the shred surgical.
• Explicit availability trade — HYOK couples the tenant's data availability to their own HSM; naming this trade (and monitoring the HSM as a hard dependency) is the senior move, versus discovering it during an outage.
• Cost — HYOK adds a network round-trip to the customer HSM per unwrap (mitigated by the bounded DEK cache from section 2) and a hard availability dependency; BYOK adds only customer key management on the provider KMS. Both cost far less than the alternative of having no cryptographic revocation or erasure story for a regulated tenant.

Design
Topic — design
Design problems on key custody and multi-tenant isolation

Practice →

ETL
Topic — etl
ETL problems on tenant-scoped ingestion and erasure

Practice →

---

Cheat sheet — column encryption and tokenization recipes

• Which control when. Warehouse-native masking only redacts at read time and leaves plaintext on disk — never sufficient alone for regulated columns. Application-side column encryption for values that must be secret against a stolen snapshot. Tokenization for card data you want out of PCI scope entirely. Pick per column from (where the key lives × breach leak × queryability × write cost).
• Envelope encryption template. One GenerateDataKey per batch/partition/tenant mints a data encryption key (DEK) in plaintext + wrapped form; encrypt values locally with the DEK; store nonce||ciphertext plus the ONE wrapped DEK beside the data; wipe the plaintext DEK in a finally. The key-encryption-key (KEK) lives in KMS and never enters the app. Never call the KMS per row.
• DEK caching. Cache the unwrapped DEK for a bounded TTL (seconds–minutes) to collapse read-path KMS calls; cap the TTL so a live key never sits in memory long. One Decrypt per partition per TTL, not per read.
• Mode decision line. Randomized AES-GCM (fresh nonce per value) = maximal secrecy, NO join/equality — use for never-queried columns. Deterministic encryption AES-SIV (no nonce) = equal-in-equal-out, joinable, LEAKS frequency — use only on high-cardinality columns you must join. Format-preserving encryption FF3-1 = same type/length, joinable, fits legacy CHAR(n) — use when the column shape must be preserved.
• Frequency-leak guard. Never deterministically encrypt a LOW-cardinality sensitive column (sex, blood type, small enums) — frequency analysis recovers the values. Store it randomized and add a truncated-HMAC blind index for equality search; the truncation length is the leakage dial.
• Normalise before deterministic encryption. strip().lower() (or your canonical form) BEFORE encrypting, and use ONE shared per-column key across every table that must join — otherwise ciphertext equality breaks and joins silently return zero rows.
• Vault vs vaultless tokenization. Vault-based = stored token↔value map, strongest secrecy (random surrogate, no key reverses it), but the vault is a SPOF/scaling bottleneck. Vaultless = FPE-derived token, no stored map, scales horizontally, but the key reverses every token (a strict PCI assessor may call it encryption). Tokenize at the EDGE, before the value reaches the warehouse.
• Detokenization rule. A separate, authorised, audited service call — never bulk-detokenize a column back into a warehouse table. One value per call, every call logged with a reason. The descoping is only real if the raw value never re-lands.
• BYOK vs HYOK. BYOK = import customer key material into the provider KMS (control + revocation, provider availability). HYOK = key stays in the customer HSM, warehouse calls out to unwrap (maximal control, availability coupled to the HSM). Warehouse features: Snowflake Tri-Secret Secure, BigQuery CMEK/EKM, Databricks customer-managed keys, Redshift customer CMK.
• Crypto-shredding for right-to-erasure. Scope a key per erasure unit (tenant/data subject). To erase: schedule-key-deletion on the KEK + NULL the wrapped DEKs + stamp shredded_at. No ciphertext is touched; every copy (live, replica, backup, cold) becomes undecryptable at once. O(1) and provable — far stronger than DELETE-across-backups.
• Key rotation. Rotate the KEK by RE-WRAPPING DEKs under the new KEK version — kilobytes, no data re-encryption. KMS automatic rotation retains old versions so historical DEKs still unwrap. If your rotation plan rewrites the fact table, you haven't used envelope encryption.
• Never do. Never store a plaintext DEK; never log a DEK or a PAN; never reuse a GCM nonce; never deterministically encrypt low-cardinality data; never let a raw sensitive value land in the warehouse or a log even briefly; never rely on provider at-rest encryption as your only control.
• Per-warehouse quick map. Snowflake: Tri-Secret Secure (customer key + Snowflake key). BigQuery: CMEK (BYOK) or Cloud EKM (HYOK) + policy-tag masking. Databricks: customer-managed keys on managed storage. Redshift: customer-managed KMS CMK. All support pointing the top of the envelope at a customer-controlled key.

Frequently asked questions

What is column encryption in one sentence?

Column encryption is the practice of encrypting the value in a specific sensitive column — a national ID, a card number, an email — so that what physically lands in the warehouse (and in every backup, replica, and cold copy) is ciphertext rather than plaintext, and the key needed to reverse it lives somewhere the warehouse and its readers cannot reach. It is fundamentally different from provider "encryption at rest," which protects against a stolen physical disk but leaves the plaintext fully visible to any query, any leaked query result, and any over-privileged role. Serious column encryption sits on an envelope encryption hierarchy: a data encryption key encrypts the values, a key-encryption-key in a KMS wraps that DEK, and the mode (randomized, deterministic encryption, or format-preserving encryption) is chosen per column to match its query pattern. It is the load-bearing control for protecting PII, PCI, and PHI in a shared analytics warehouse.

Envelope encryption vs plain column encryption — what's the difference?

Plain column encryption uses a single key directly on the data; envelope encryption uses two levels — a short-lived data encryption key (DEK) that encrypts the data, and a long-lived key-encryption-key (KEK) in a KMS that wraps (encrypts) the DEK. The wrapped DEK is stored next to the ciphertext, and the KEK never leaves the KMS. This buys two things a single key can't: first, you encrypt millions of rows with a single GenerateDataKey call instead of hitting the KMS per row (a naive single-key design either exposes the key in the app or throttles on KMS calls); second, you rotate the KEK by simply re-wrapping the DEK — a kilobyte operation — without ever re-encrypting the terabytes of underlying data. Every serious cloud column-encryption design (AWS KMS, GCP KMS, Azure Key Vault) uses envelope encryption, which is why "we encrypt with a key in a config file" is the answer that fails a senior interview.

Deterministic vs randomized encryption — when do I pick each?

Pick randomized encryption (AES-GCM with a fresh nonce per value) when the column is stored but never queried — a national ID or SSN read only by a bulk compliance export. Randomized makes every ciphertext unique, so it leaks nothing but length and defeats frequency analysis, at the cost of destroying equality (no joins, no WHERE col = ?). Pick deterministic encryption (AES-SIV, no nonce) when you must still join or filter on the column — an email that analysts join daily. Deterministic makes equal plaintexts produce equal ciphertext, so joins and equality lookups work, but it leaks the frequency distribution: an attacker sees which ciphertexts repeat. That's harmless on high-cardinality columns (email, account number — nearly every value is unique) and dangerous on low-cardinality columns (sex, blood type — frequency analysis recovers the values). The rule: deterministic only for high-cardinality columns you must query; randomized for everything else; and for low-cardinality columns you must search, store randomized plus a truncated-HMAC blind index whose bucket size tunes the leakage.

Tokenization vs encryption — what's the difference?

Both replace a sensitive value with something unreadable, but they reverse differently and — crucially for compliance — they're scoped differently. Encryption reverses with a key: anyone holding the key and the ciphertext recovers the value, and under PCI-DSS an encrypted card number is still "cardholder data you store," so the system stays in audit scope. Tokenization replaces the value with a surrogate token whose reversal lives in a separate service — either a vault (a stored token↔value map, where the token has no cryptographic relationship to the value) or vaultless FPE derivation. A system that stores only non-reversible tokens is out of PCI scope entirely, which is the main reason to tokenize card data rather than encrypt it. Tokens are usually deterministic and format-preserving, so analytics — distinct-card counts, cards-per-customer, cross-region reuse — still work on the token, while the raw PAN never enters the warehouse. Choose tokenization when scope reduction is the goal; choose encryption when you need the value protected but reversible by key holders (and can accept staying in scope).

What is BYOK vs HYOK?

Both are customer key-custody models that answer "who controls the master key protecting the warehouse." BYOK (bring your own key) means the customer generates key material — often in their own HSM — and imports it into the cloud provider's KMS; the provider then decrypts with a key the customer created and can disable or schedule for deletion at any time. The key does live in the provider KMS during use, so BYOK is about control and revocation, not about the key never touching the provider. HYOK (hold your own key) keeps the key permanently inside the customer's own external key manager / HSM; it never enters the provider's infrastructure, and the warehouse calls out to the customer's HSM to unwrap on every use. HYOK gives maximal control and a cryptographic guarantee the provider can't read the data without a live call to the customer — at the cost of coupling the warehouse's availability to the customer's HSM (if the HSM is down, that data is unreadable). Warehouse implementations include Snowflake Tri-Secret Secure, BigQuery CMEK (BYOK) and Cloud EKM (HYOK), Databricks customer-managed keys, and Redshift customer-managed KMS keys.

How do you delete regulated data — what is crypto-shredding?

Crypto-shredding is the pattern where you encrypt each tenant's (or each data subject's) data under a distinct key, and to erase that tenant you destroy the key rather than the data. Once the key is gone, the ciphertext in the live warehouse, every read replica, last night's backup, and the cold archive all become permanently undecryptable — noise — without you ever visiting a single copy. This is dramatically stronger and cheaper than a DELETE, which must find and remove every copy across live storage, snapshots, and backups and then somehow prove it reached them all (nearly impossible in practice). Crypto-shredding is O(1) and provable: completeness follows from cryptography, not from having scanned every location. The one requirement is that the key must be scoped to the erasure unit — a per-tenant or per-subject key — because a single shared key means shredding erases everyone. This is why per-tenant DEKs and BYOK/HYOK custody are the enabling design for a real GDPR/CCPA right-to-erasure at warehouse scale.

Practice on PipeCode

• Drill the SQL practice library → for the access-control, secure-lookup, join-on-token, and sensitive-column problems senior interviewers love.
• Rehearse on the design practice library → for the envelope-encryption hierarchy, tokenization-vault, and BYOK/HYOK key-custody system-design scenarios.
• Sharpen the pipeline axis with the ETL practice library → for tenant-scoped ingestion, edge tokenization, and crypto-shred erasure workflows.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the control-per-column decision grid against real graded inputs.

Lock in column-encryption muscle memory

Docs explain the primitives. PipeCode drills explain the decision — when randomized encryption kills the join, when deterministic leaks the frequency, when tokenization descopes the warehouse, when a crypto-shred beats a DELETE across every backup. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →