Data Residency & Sovereignty: Multi-Region Architectures for Compliant Data Platforms

data residency is a claim about geography — it says the physical bytes of a given dataset are stored and processed inside a named region, and never leave it. That sounds like a one-line configuration flag, and for a single bucket it almost is. But a real data platform is a mesh of storage layers, warehouses, replication jobs, caches, backups, key stores, and query engines, and residency is only satisfied when every one of those components keeps the row inside the boundary. The moment a nightly cross-region replica, a global materialized view, or a helpfully-replicated encryption key carries a byte across the line, the guarantee is gone — usually silently, and usually discovered in an audit rather than a test.

Residency is also routinely confused with two neighbours it is not. It is not data sovereignty, which is the harder question of whose laws can compel access to the data regardless of where it sits — a US-headquartered provider can be reachable under the US CLOUD Act even for bytes stored in Frankfurt. And it is not disaster recovery, which is about keeping copies for availability; DR wants your data in more than one place, residency wants it in exactly one jurisdiction, and the two goals pull in opposite directions. This guide walks the four design problems an interviewer will actually probe — region-pinned storage with geo-partitioning, per-region warehouse deployments with hard replication boundaries, encryption with in-region key residency, and cross-region aggregate-only egress — and pairs each with a Solution-Tail 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 partitioning practice library →, rehearse the region-shard decisions on the sharding practice set →, and harden who-can-read-what on the access-control practice set →.

---

On this page

• Why residency and sovereignty are different problems
• Region-pinned storage & geo-partitioning
• Regional warehouses & replication boundaries
• Encryption, key residency & BYOK
• Cross-region aggregate-only egress
• Cheat sheet — residency recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why residency and sovereignty are different problems

Residency is about geography, sovereignty is about jurisdiction — conflate them and you build the wrong control

The one-sentence invariant: residency answers "where is the data physically stored and processed?" while sovereignty answers "whose legal authority can reach it?" — and a design can satisfy one while violating the other. You can host EU customer data in an EU region (residency satisfied) on a provider whose parent company is legally compellable under a foreign statute (sovereignty not satisfied). Interviewers who work in regulated industries open with this distinction on purpose, because a candidate who treats the two as synonyms will pick a control that looks compliant on an architecture diagram but fails the actual legal test.

The two questions, kept apart.

• Residency — where the bytes live. A physical-location property of every copy of the data: primary storage, replicas, backups, temp files, query spill, log lines that echo payloads, and CDN caches. Residency is satisfied only when the union of all those locations stays inside the allowed region set.
• Sovereignty — whose laws govern. A jurisdictional property: which government can lawfully compel disclosure or seizure. This depends on where the operator is incorporated, where its staff and support sit, and which treaties apply — not only where the disk is. The US CLOUD Act and various national-security statutes are the usual examples of extraterritorial reach.
• Why the gap matters. The Schrems II ruling turned on exactly this gap: storing EU data in the EU was not sufficient if a non-EU authority could still compel it. Sovereignty controls (in-region operator, customer-held keys, confidential computing) exist precisely to close a gap that residency alone leaves open.

Distinct from disaster recovery — the copies problem.

• DR wants redundancy across failure domains. The instinct of every availability design is "replicate to a second region so a regional outage does not take us down." That instinct directly manufactures a residency violation if the second region is in a different jurisdiction.
• Residency wants a bounded footprint. The reconciliation is to keep DR inside the residency boundary — a second availability zone or a second in-jurisdiction region — never a convenient far-away region. "Back it up to us-east-1 for safety" is the classic way an EU-residency platform quietly breaks.
• Backups and snapshots count. A backup is a copy; a copy has a location; that location is in scope. Cross-region backup replication is the single most common silent residency breach because it is configured once, for good reasons, and then forgotten.

The three legal forces that drive the architecture.

• Data-localization laws. Some jurisdictions require certain data to be stored (and sometimes processed) only inside the country — for example national rules covering personal, health, financial, or government data. These force region-pinning and per-region deployments.
• Cross-border transfer rules. Frameworks like the GDPR permit transfers only under specific mechanisms (adequacy decisions, standard contractual clauses, supplementary measures). These force you to prove what leaves a region and under what basis.
• Extraterritorial reach. Statutes that let a government compel a provider regardless of storage location. These force sovereignty controls that residency cannot provide — customer-controlled keys and, at the extreme, sovereign-cloud operators.

What interviewers listen for.

• Do you separate "where it is stored" from "who can compel it" in the first two sentences? — senior signal.
• Do you flag that DR replication is the usual way residency breaks, unprompted? — required framing.
• Do you name backups, temp files, and logs as in-scope copies, not just the primary table? — the detail that separates a real design from a diagram.
• Do you reach for customer-held keys when the interviewer adds "but the operator is foreign"? — the sovereignty move.

Worked example — the same customer, two very different guarantees

Detailed explanation. The cleanest way to feel the distinction is to hold the storage location fixed and vary only the operator, then hold the operator fixed and vary only the storage. Each move flips exactly one of the two properties, which is the whole point: residency and sovereignty are independent axes, and a compliant design has to satisfy both simultaneously rather than assuming one implies the other.

Question. For an EU customer whose personal data must stay in the EU and must not be reachable by a non-EU authority, classify four deployment options as residency-OK / sovereignty-OK.

Input.

option	storage region	operator jurisdiction
A	EU (Frankfurt)	non-EU parent company
B	US (Virginia)	non-EU parent company
C	EU (Frankfurt)	EU sovereign operator
D	EU (Frankfurt)	EU operator, customer-held keys

Code.

residency_ok  = (storage_region in ALLOWED_REGIONS)
sovereignty_ok = (operator_in_jurisdiction) OR (customer_controls_keys)

A: residency_ok=True,  sovereignty_ok=False   # in-region, but compellable abroad
B: residency_ok=False, sovereignty_ok=False   # wrong region entirely
C: residency_ok=True,  sovereignty_ok=True    # in-region + in-jurisdiction operator
D: residency_ok=True,  sovereignty_ok=True    # in-region + crypto control of access

Step-by-step explanation. Option A stores in the EU so residency passes, but the foreign parent can be compelled, so sovereignty fails — this is the Schrems II shape exactly. Option B fails both because the bytes are in the wrong region. Option C satisfies both by using an operator that is itself inside the jurisdiction. Option D keeps a commercial operator but moves the access control to the customer via customer-held encryption keys, so even a lawful demand to the operator yields ciphertext — sovereignty satisfied by cryptography rather than by corporate structure.

Output.

option	residency	sovereignty	verdict
A	OK	FAIL	non-compliant
B	FAIL	FAIL	non-compliant
C	OK	OK	compliant (sovereign operator)
D	OK	OK	compliant (customer keys)

Rule of thumb. Fix the storage region first to satisfy residency, then ask "who could still be compelled?" to satisfy sovereignty — if the answer is a party you do not control, you need in-region operation or customer-held keys, not a second region.

---

2. Region-pinned storage & geo-partitioning

Pin the bucket to a region and partition the fact table on a region key — location becomes a column, not a hope

The feature that makes residency enforceable rather than aspirational is that in modern storage the region is an immutable property of the container and the data model carries the region as a first-class key. You pin the S3 bucket / GCS bucket / warehouse dataset to a region at creation, you disable any replication that would copy it elsewhere, and you make region a partition key on every table that holds regulated rows so a row's residency is visible in the schema and enforceable in every query.

Pinning the storage container.

• Region is set at creation and often immutable. An S3 bucket lives in one region for life; a BigQuery dataset's location (EU, US, europe-west3) is fixed when created and cannot be changed — you recreate to move. Treat the region as part of the resource's identity.
• Block the replication features by default. S3 Cross-Region Replication, cross-region snapshot copy, and global-table features are opt-in conveniences that break residency. For a residency-bound bucket, the correct posture is an explicit deny (a bucket policy / SCP that forbids replication to non-allowed regions), not merely "we did not turn it on."
• Watch the implicit copies. Query engines spill to temp storage, CDNs cache objects at edges worldwide, and logs can echo payloads. Each of those has a region; each must be constrained to the boundary or scrubbed of regulated content.

Geo-partitioning the data model.

• Make region (or country) a partition key. PARTITION BY region (or a Hive-style region=EU/ path layout on object storage) physically groups each region's rows into their own files / partitions, so residency is a property you can see, scan, and enforce per partition.
• Partition pruning routes the query. A query filtered WHERE region = 'EU' reads only the EU partitions — the US partitions are pruned and never touched. This is both a performance win and a residency control: an EU-scoped job provably cannot read US data.
• Bucketing within a region for skew. If one region is huge, add a secondary bucket/shard on a high-cardinality key (customer_id) inside the region so files stay evenly sized without ever mixing regions.

Row-domiciled databases (the OLTP side).

• CockroachDB REGIONAL BY ROW. Each row carries a hidden crdb_region column; the database physically homes the row's replicas in that region's nodes, so a single logical table transparently keeps EU rows in EU nodes and US rows in US nodes.
• Citus / Yugabyte tablespaces. Distribute a Postgres table by a region/tenant key and pin each shard's tablespace to region-local storage — the same "location is a key" idea in a Postgres-compatible engine.
• The invariant. Whether OLTP or lakehouse, the design rule is identical: the region is part of the primary/partition key, and the storage for each key value is physically in that region.

Worked example — geo-partitioning a fact table and proving the prune

Detailed explanation. The everyday pattern is a fact table partitioned on region where each partition maps to region-local storage. The proof that residency holds is that a region-scoped query only ever scans that region's partition — you can read the guarantee straight out of the query plan, which is exactly what an auditor (or interviewer) wants to see.

Question. Partition an events table by region, then show that a query for EU events scans only the EU partition and never touches US storage.

Input.

event_id	region	user_id	amount
1	EU	u-eu-1	40
2	US	u-us-1	12
3	EU	u-eu-2	25
4	US	u-us-2	8

Code.

-- Region is a partition key, so each region's rows live in their own partition/storage
CREATE TABLE events (
    event_id BIGINT,
    region   STRING,        -- 'EU' | 'US'
    user_id  STRING,
    amount   DOUBLE
)
PARTITIONED BY (region);

-- Regional read: the planner prunes to a single partition
SELECT region, COUNT(*) AS n, SUM(amount) AS total
FROM events
WHERE region = 'EU'
GROUP BY region;

Step-by-step explanation. PARTITIONED BY (region) lays the four rows into two partitions — region=EU/ holding events 1 and 3, and region=US/ holding events 2 and 4 — each of which can be pinned to region-local storage. When the query filters WHERE region = 'EU', the planner performs partition pruning: it resolves the predicate against the partition key at plan time and lists only the region=EU/ files, so the US partition is never opened. The residency guarantee is therefore structural, not procedural — the EU job has no code path that reads US bytes.

Output.

region	n	total
EU	2	65

Rule of thumb. If residency is not visible as a partition key you can filter on, you cannot prove it in a query plan — make region a partition key first, everything else (pruning, per-region storage, per-region access) follows.

SQL interview question on geo-partitioning for residency

Question. An interviewer gives you a global orders table and a requirement that an EU analyst's session must be physically incapable of scanning non-EU rows. Using partitioning, how do you both store EU rows separately and guarantee an EU-scoped query prunes everything else? Show the DDL and the pruned read.

Solution Using partitioning on a region key with partition pruning

Code.

CREATE TABLE orders (
    order_id  BIGINT,
    region    STRING,          -- residency key: 'EU' | 'US' | 'APAC'
    customer  STRING,
    amount    DECIMAL(12,2),
    created_at TIMESTAMP
)
PARTITIONED BY (region);

-- Each partition is pinned to region-local storage at load time, e.g.
--   region=EU   -> s3://eu-central-1-orders/ (bucket locked to EU)
--   region=US   -> s3://us-east-1-orders/    (bucket locked to US)

-- EU analyst session runs only region-scoped reads:
SELECT customer, SUM(amount) AS lifetime_value
FROM orders
WHERE region = 'EU'            -- prunes US and APAC partitions
GROUP BY customer;

Step-by-step trace.

step	partitions on disk	predicate	partitions scanned
1	EU, US, APAC	(load time)	—
2	EU, US, APAC	region = 'EU'	EU only
3	US, APAC	(never listed)	pruned
4	EU	aggregate	grouped in region

1. PARTITIONED BY (region) writes each region's rows into a separate partition directory that is loaded into a region-pinned bucket, so the physical bytes for US and APAC are not even in EU storage.
2. The EU query's WHERE region = 'EU' is a partition predicate; the planner evaluates it against partition metadata and lists only the EU files.
3. The US and APAC partitions are pruned — no file handle is opened, so an EU session has no path to non-EU bytes even with a bug in the WHERE clause on non-partition columns.
4. The aggregate runs entirely within the EU partition, so both the scan and the result stay in region.

Output:

customer	lifetime_value
c-eu-ada	240.00
c-eu-lin	95.50

Why this works — concept by concept:

• Region as partition key — promoting region to a partition key turns residency from a runtime hope into a physical layout, so each region's rows occupy their own files in their own pinned storage.
• Partition pruning — because the filter is on the partition key, the planner discards non-EU partitions at plan time; an EU session cannot open US files, which is a stronger guarantee than row-level filtering that still scans everything.
• Pinned storage per partition — mapping each partition to a region-locked bucket means the boundary is enforced by the storage layer, not only by the query, so a rogue full-scan still cannot pull a US byte into EU compute.
• Auditable plan — the pruned plan is the evidence: you can show an auditor that the EU workload lists only EU partitions, which is exactly the proof residency reviews demand.
• Cost — pruning makes the scan O(rows in one region) instead of O(rows globally), so residency and query cost improve together rather than trading off.

Partitioning
Topic — partitioning
Partition-key and partition-pruning problems

Practice →

Sharding Topic — sharding Region-shard and data-placement problems

Practice →

---

3. Regional warehouses & replication boundaries

Give each region its own warehouse and draw a hard line replication may not cross — then let only metadata across

Partitioning keeps rows apart inside one system; the next layer up keeps whole systems apart. The compliant multi-region pattern is one warehouse deployment per region — a Snowflake account, a BigQuery project/dataset, or a Redshift cluster pinned to that region — with an explicit replication boundary between them that raw regulated data is never allowed to cross. The subtlety interviewers love is that some things should cross the boundary (schema, lineage, tags, policies) and some things must never (the actual rows), so the boundary is selective, not a total wall.

One warehouse per region.

• Location is immutable and per-deployment. A BigQuery dataset's location and a Snowflake account's region are fixed at creation. You do not have one global warehouse with a residency flag; you have N regional warehouses and a routing layer that sends each tenant's queries to the right one.
• Sharding the platform by region. Treating each region as an independent shard means a breach, outage, or noisy-neighbour problem in one region is contained to that region's blast radius — the US shard failing does not touch EU data or EU availability.
• Routing by residency key. The application resolves a tenant's region and dispatches to that region's warehouse. The residency key from Section 2 becomes the routing key here — same column, different layer.

The replication boundary — what must not cross.

• Disable cross-region replication of data. Snowflake database replication, BigQuery cross-region dataset copies, Redshift cross-region snapshot copy, and Kafka MirrorMaker are all capable of copying regulated rows across the line. For a residency-bound deployment these are denied by policy, with an allow-list only for non-regulated or already-aggregated data.
• Geo-fence the streaming layer. If Kafka feeds the warehouse, the topics carrying regulated data must live in region-local clusters and MirrorMaker mirroring must be scoped so a EU topic is not mirrored into a US cluster. The stream is as much in scope as the table.
• Backups stay in region. Cross-region backup/snapshot copy is the most common accidental breach; the backup destination must be an in-region (or in-jurisdiction) location, verified in policy, not assumed.

What may cross — the metadata / data-plane split.

• Central control plane, regional data plane. Catalog metadata — table names, schemas, column types, lineage, ownership, classification tags, and access policies — is generally not the regulated payload and can live in a central catalog (e.g. a single governance catalog) that spans regions. The rows themselves stay in the regional data plane.
• Why the split is safe. Knowing that an EU table customers has a email column of type string tagged PII is metadata; the actual email values are data. Sharing the former globally lets you run one governance model while the latter never leaves the EU.
• The trap. Metadata must be genuinely free of payload — column statistics like min/max, sample values, or histograms can leak actual data (a min email, a sampled name), so residency-grade catalogs suppress value-bearing statistics for regulated columns.

Worked example — routing a tenant to its regional warehouse

Detailed explanation. The mechanical heart of the pattern is a resolver: given a tenant, return the region, and from the region return the warehouse connection. No query ever names a region literally; it always goes through the resolver, so there is exactly one place that enforces "this tenant's data lives here," and it is trivial to audit.

Question. Implement a router that sends a tenant's read to its region's warehouse and refuses if the requested region is outside the tenant's allowed set.

Input.

tenant	home_region	requested_region
acme-eu	EU	EU
globex-us	US	US
acme-eu	EU	US

Code.

WAREHOUSES = {
    "EU": "snowflake://eu-central-1/acct_eu",
    "US": "snowflake://us-east-1/acct_us",
}

def route(tenant_home_region: str, requested_region: str) -> str:
    if requested_region != tenant_home_region:
        raise PermissionError(
            f"residency violation: tenant homed in {tenant_home_region} "
            f"may not query {requested_region}"
        )
    return WAREHOUSES[requested_region]      # region-pinned warehouse

Step-by-step explanation. Every request carries the tenant's home_region (resolved from the residency key) and the region the query wants to touch. The router refuses any request where those differ, so an EU tenant cannot be pointed at the US warehouse even by a mis-built query. When they match, it returns the connection string for that region's own warehouse account, which is physically pinned to the region — the boundary is enforced at the connection layer before a single row is read.

Output.

tenant	requested_region	result
acme-eu	EU	routed to acct_eu
globex-us	US	routed to acct_us
acme-eu	US	PermissionError (blocked)

Rule of thumb. Never let application code name a warehouse region directly — force every query through one resolver keyed on the tenant's residency, so the boundary lives in one auditable function instead of scattered across the codebase.

SQL interview question on replication boundaries

Question. Product wants a single global dashboard of order counts, but raw EU orders may not leave the EU. An engineer proposes replicating the EU orders table into the US warehouse to make the join easy. Explain why that breaks residency and design the replication so the boundary holds while the dashboard still works.

Solution Using region-local aggregation before any cross-region copy

Code.

-- WRONG: replicate raw EU rows to US so a global query can read them
--   CREATE TABLE us.orders_eu_copy AS SELECT * FROM eu.orders;   -- residency breach

-- RIGHT: aggregate inside the EU, replicate only the non-personal summary
-- (runs in the EU warehouse, on EU storage)
CREATE TABLE eu.orders_daily AS
SELECT
    order_date,
    'EU'        AS region,
    COUNT(*)    AS order_count,
    SUM(amount) AS revenue
FROM eu.orders
GROUP BY order_date;          -- no customer, no row-level PII

-- Only orders_daily (counts + revenue, no personal data) is allowed
-- across the replication boundary into the shared reporting layer.

Step-by-step trace.

step	location	data shape	crosses boundary?
1	EU warehouse	raw orders (PII)	never
2	EU warehouse	orders_daily aggregate	eligible
3	boundary policy	check: no PII columns	allow
4	US / shared layer	orders_daily unioned	yes (safe)

1. Replicating raw orders copies personal rows into US storage governed by US law — that is the exact residency breach, no matter that the query "only wanted counts."
2. Instead, the aggregation runs inside the EU, on EU storage, producing orders_daily that has date, region, count, and revenue — no customer, no personal field.
3. The replication boundary policy inspects the object: because orders_daily carries no PII-tagged columns, it is on the allow-list to cross.
4. The shared dashboard unions each region's orders_daily, so the global view exists while raw rows never left their home region.

Output:

order_date	region	order_count	revenue
2026-03-01	EU	1204	48210.00
2026-03-01	US	3391	121750.00

Why this works — concept by concept:

• Replication boundary — the design decision is what may cross, not merely whether a link exists; raw regulated rows are denied and only derived, non-personal summaries are allowed.
• Aggregate-in-region — computing the summary on EU storage means the personal rows are consumed where they live and only the reduced, safe result is a candidate for egress.
• Policy on the object, not the intent — the boundary check inspects the actual columns/tags of what is crossing, so "we only wanted counts" cannot smuggle a SELECT *.
• Regional sharding — each warehouse stays an independent shard, so the shared reporting layer depends on small summaries rather than a fragile cross-region raw replica.
• Cost — moving kilobytes of daily aggregates instead of gigabytes of raw orders is both cheaper and the compliant path, so correctness and egress cost align.

Sharding
Topic — sharding
Region-shard and boundary-isolation problems

Practice →

Partitioning Topic — partitioning Per-region layout and pruning problems

Practice →

---

4. Encryption, key residency & BYOK

Encrypt every row, but keep the key in the jurisdiction — because whoever holds the key controls the data

Region-pinning and boundaries control where the plaintext sits. Encryption plus key residency controls who can turn ciphertext back into plaintext, and that is the lever that actually delivers sovereignty. The design is envelope encryption: each row (or file) is encrypted with a data key, the data key is wrapped by a customer master key, and the master key is held in a key store that resides in the jurisdiction and is controlled by you — so a lawful demand served on a foreign operator yields unreadable bytes, and revoking the key is a one-move "crypto-shred" of the data.

Envelope encryption — the mechanism.

• Two-tier keys. A per-object data encryption key (DEK) encrypts the bytes; a customer master key (CMK / KEK) encrypts the DEK. Only the small wrapped DEK travels with the data; the CMK never leaves the key store.
• Why two tiers. Rotating or revoking one CMK re-controls millions of objects without re-encrypting them all — you re-wrap DEKs, not petabytes. It also means the powerful key can live somewhere much more tightly controlled than the data.
• Where residency bites. The CMK's location and control is the sovereignty control. If the CMK lives in an in-jurisdiction key store you control, the operator holding the ciphertext cannot read it, so extraterritorial reach is blunted by cryptography.

BYOK, HYOK, and external key stores.

• BYOK (bring your own key). You generate the key material and import it into the cloud provider's KMS; you can disable or delete it, which revokes the provider's ability to decrypt. Good, but the key still operates inside the provider's boundary.
• HYOK / external key store (hold your own key). The key material never enters the provider at all — it lives in your own HSM or an external key store (e.g. AWS KMS External Key Store / GCP EKM), and every decrypt makes a call out to your key manager. This is the strongest sovereignty posture short of a fully sovereign cloud, because you can cut access instantly and unilaterally.
• Access control on the key is the real control. The IAM policy on the CMK decides who can Decrypt. Sovereignty is enforced by making that policy grant decrypt only to in-jurisdiction principals — the key policy, not the storage location, is where you win or lose.

The multi-region-key foot-gun.

• Multi-region KMS keys replicate the key material. Cloud "multi-region keys" are a convenience that copies the same key into several regions so ciphertext is portable. For residency that is precisely wrong: it makes the data decryptable in another jurisdiction.
• Prefer per-region single-region keys. Each region gets its own CMK, created and held in that region, so EU ciphertext can only be decrypted by the EU key held under EU control.
• Crypto-shredding. Because access hinges on the key, deleting the region's CMK renders all that region's ciphertext permanently unreadable — a clean, provable deletion that satisfies "right to erasure" without hunting every backup copy.

Worked example — per-region CMK wrapping a data key

Detailed explanation. The pattern is one CMK per region, each wrapping the DEKs for that region's data. Encrypting an EU row uses the EU CMK; there is no path to decrypt it with the US CMK, because they are different keys held under different control. This makes the key the enforcement point for both residency and sovereignty at once.

Question. Encrypt an EU customer row so that only the EU key can decrypt it, and show what a request to decrypt it with the US key produces.

Input.

{"customer_id": "c-eu-1", "region": "EU", "email": "[e-mail]"}

Code.

CMK = {"EU": eu_key_store, "US": us_key_store}   # one master key per region, held in-region

def encrypt_row(row: dict) -> dict:
    region = row["region"]
    dek = generate_data_key()                     # random per-object DEK
    ciphertext = aes_encrypt(dek, row)            # encrypt the bytes
    wrapped_dek = CMK[region].wrap(dek)           # CMK never leaves the store
    return {"region": region, "wrapped_dek": wrapped_dek, "ct": ciphertext}

def decrypt_row(obj: dict, use_region: str) -> dict:
    dek = CMK[use_region].unwrap(obj["wrapped_dek"])   # fails unless region matches
    return aes_decrypt(dek, obj["ct"])

Step-by-step explanation. encrypt_row picks the CMK for the row's region, generates a fresh DEK, encrypts the bytes with the DEK, and asks the EU CMK to wrap the DEK — the CMK itself stays inside the EU key store. To read the row you must unwrap the DEK with the same CMK that wrapped it. Calling decrypt_row(obj, "US") asks the US key store to unwrap a DEK wrapped by the EU key; the US CMK is a different key and the unwrap fails, so the ciphertext is inert outside the EU.

Output.

operation	key used	result
encrypt EU row	EU CMK	wrapped DEK + ciphertext
decrypt with EU CMK	EU CMK	plaintext row
decrypt with US CMK	US CMK	unwrap fails — ciphertext stays opaque

Rule of thumb. One CMK per region, single-region, held in-jurisdiction — never a multi-region key — so the only way to read a region's data is a key that region controls.

SQL interview question on key residency and access revocation

Question. Regulators order you to make an EU dataset immediately unreadable to a specific processor, across the live table and every backup, without physically deleting terabytes. How does an in-region key design let you do this in one operation, and what access-control setting makes it enforceable?

Solution Using access-control on a region-local CMK (crypto-shred)

Code.

-- All EU data is encrypted with a DEK wrapped by the EU CMK.
-- The table (and every backup) stores only ciphertext + wrapped DEK.

-- Access to decrypt is a KEY POLICY, not a table grant:
--   grant kms:Decrypt on cmk_eu to  <in-region service principals>
--   deny  kms:Decrypt on cmk_eu to  <the ordered-out processor>

-- To make the dataset unreadable to that processor RIGHT NOW:
REVOKE DECRYPT ON KEY cmk_eu FROM ROLE processor_x;   -- single control-plane op

-- To crypto-shred the WHOLE dataset (live + all backups) irreversibly:
--   schedule deletion of cmk_eu  ->  every ciphertext ever wrapped by it
--   becomes permanently undecryptable, no matter which backup holds it.

Step-by-step trace.

step	action	scope affected	data physically deleted?
1	revoke Decrypt from processor_x	live + backups	no
2	processor_x reads table	ciphertext only	—
3	(erasure) schedule CMK deletion	every wrapped DEK	no
4	any future read, any copy	unwrap fails	effectively yes

1. Because decryption requires kms:Decrypt on the EU CMK, revoking that permission from processor_x instantly removes their ability to read — for the live table and every backup, since all copies share the same wrapping key.
2. The processor can still fetch bytes, but without the key those bytes are ciphertext, so the revocation is a single control-plane change with immediate, global effect.
3. For full erasure, scheduling deletion of the CMK is one operation whose reach is every object the key ever wrapped, wherever it is stored.
4. After the key is gone, no principal — not even you — can unwrap those DEKs, so the data is provably unreadable without hunting down each physical copy.

Output:

target	before revoke	after revoke	after CMK deletion
processor_x reads	plaintext	denied (ciphertext)	denied (ciphertext)
everyone reads	plaintext	plaintext	unreadable (shredded)

Why this works — concept by concept:

• Key policy as access control — decryption is gated by kms:Decrypt on the CMK, so who-can-read is a key-policy decision that applies uniformly to the live table and every derived copy at once.
• Envelope indirection — because every object's DEK is wrapped by the one region CMK, a single key action re-governs or destroys millions of rows without touching the rows themselves.
• Crypto-shredding — deleting the region key makes all ciphertext it wrapped permanently opaque, giving a provable, backup-inclusive "right to erasure" that physical deletion sweeps can never guarantee.
• In-region, single-region key — holding one CMK per region under in-jurisdiction control is what makes both the revoke and the shred lawful, unilateral, and outside a foreign operator's reach.
• Cost — the operation is O(1) key actions rather than O(bytes) re-encryption or deletion, so a compliance mandate becomes a single reversible-then-irreversible control-plane step.

Access control
Topic — access-control
Key-policy and access-revocation problems

Practice →

Partitioning Topic — partitioning Per-region key and data-placement problems

Practice →

---

5. Cross-region aggregate-only egress

Keep the rows home and let only insight travel — spokes aggregate, a k-anonymity gate filters, the hub unions sums

The final pattern answers the question every business eventually asks: "we need a global view, but the data cannot leave its region — now what?" The answer is aggregate-only egress in a hub-and-spoke shape. Each regional spoke computes aggregates locally, a privacy gate (a minimum group size, a.k.a. k-anonymity) drops any aggregate small enough to re-identify an individual, and only the surviving, safe summaries cross to a central hub that unions them. Raw rows never move; the metadata/control plane is central; the data plane stays regional.

Aggregate-only, computed in region.

• Reduce before you move. The spoke runs GROUP BY on region-local storage, so what leaves is COUNT/SUM/AVG per group — not the underlying rows. The reduction is the compliance boundary: individuals are gone by the time anything crosses.
• Suppress small groups (k-anonymity). An aggregate over a tiny group can re-identify a person (a SUM(salary) for one employee is that salary). A HAVING count(*) >= k threshold suppresses groups below k so no aggregate leaks an individual. Common k values are 5–20 depending on sensitivity.
• Beware differencing attacks. Two overlapping aggregates can be subtracted to reveal a suppressed cell; robust designs add consistent suppression rules (or noise / differential privacy) so the hub cannot reconstruct a small group by arithmetic.

Hub-and-spoke topology.

• Spokes = regional data planes. Each region is a full, independent shard holding its raw data and doing its own aggregation — the same regional-warehouse shards from Section 3.
• Hub = a thin global layer. The hub stores only the safe aggregates and unions them into global metrics. It holds no raw regional data, so the hub's own jurisdiction is not a residency problem.
• The control plane is separate from the data plane. Schema, job definitions, lineage, and policies (metadata) are managed centrally and pushed to spokes; the data (rows) never flow the other way. This is the same metadata/data split as Section 3, applied to analytics.

When even aggregates are not enough — sovereign cloud.

• Sovereign-cloud offerings. When regulation demands that even the operator be in-jurisdiction (in-country support staff, in-country legal entity, isolated from foreign parent control), providers offer sovereign-cloud options — for example AWS European Sovereign Cloud, Microsoft Cloud for Sovereignty, and Google Sovereign Controls — plus national efforts like Gaia-X.
• Confidential computing. Encrypting data in use (enclaves / trusted execution) closes the last gap where plaintext exists in memory during processing, so even the operator's admins cannot read it.
• Choose the least-heavy control that clears the bar. Region-pinning handles residency; customer-held keys handle most sovereignty; sovereign-cloud and confidential computing are for the strictest mandates. Reaching for the heaviest control everywhere is expensive and slow — match the control to the legal requirement.

Worked example — a spoke aggregate that suppresses small groups

Detailed explanation. The everyday building block is a spoke query that aggregates and applies the k-anonymity HAVING filter in the same statement, so the object that leaves the region is already both reduced and suppressed. Nothing downstream has to remember to re-apply the gate.

Question. In the EU spoke, produce per-city order counts that may leave the region, suppressing any city with fewer than 5 customers.

Input.

city	customers	orders
Berlin	900	3400
Munich	120	510
Aland (tiny)	3	7

Code.

-- Runs in the EU spoke, on EU storage. Output is egress-eligible.
SELECT
    city,
    COUNT(DISTINCT customer_id) AS customers,
    COUNT(*)                    AS orders
FROM eu.orders
GROUP BY city
HAVING COUNT(DISTINCT customer_id) >= 5;   -- k-anonymity: drop tiny groups

Step-by-step explanation. The GROUP BY city reduces raw orders to one row per city — individuals are already gone. The HAVING COUNT(DISTINCT customer_id) >= 5 clause drops any city whose group is smaller than k=5, so "Aland" with 3 customers is suppressed and never appears in the output. What remains is a small, non-identifying summary that is safe to send to the hub; the raw eu.orders rows stay in the EU.

Output.

city	customers	orders
Berlin	900	3400
Munich	120	510

Rule of thumb. Apply the k-anonymity HAVING filter in the same query that aggregates, in-region — so the artifact that crosses the boundary is provably suppressed and no downstream step can forget the gate.

SQL interview question on aggregate-only cross-region reporting

Question. You must build a global "revenue by product category" report from EU and US spokes without any raw row leaving its region, and a category with fewer than 10 customers in a region must be suppressed. Design the spoke query and the hub union.

Solution Using in-region aggregation, a k-anonymity gate, and a hub union

Code.

-- SPOKE (runs identically in EU and US, each on its own region-local storage)
CREATE TABLE {region}.category_daily AS
SELECT
    'EU'                        AS region,     -- literal per spoke
    category,
    COUNT(DISTINCT customer_id) AS customers,
    SUM(amount)                 AS revenue
FROM eu.orders
GROUP BY category
HAVING COUNT(DISTINCT customer_id) >= 10;      -- suppress < 10 customers

-- HUB (thin global layer; stores only the safe aggregates, no raw rows)
SELECT category, SUM(revenue) AS global_revenue, SUM(customers) AS customers
FROM (
    SELECT * FROM eu.category_daily            -- crossed the boundary: safe
    UNION ALL
    SELECT * FROM us.category_daily
) t
GROUP BY category;

Step-by-step trace.

step	location	rows	privacy gate
1	EU spoke	raw orders (PII)	stays in EU
2	EU spoke	category_daily (>=10)	small cats suppressed
3	US spoke	category_daily (>=10)	small cats suppressed
4	hub	union + re-aggregate	only safe sums

1. Each spoke aggregates its own raw orders in region, so the personal rows never move; only category, customers, and revenue are produced.
2. The HAVING ... >= 10 gate suppresses any category with fewer than 10 customers in that region, so no small-group aggregate can re-identify a person before egress.
3. The two suppressed summaries are the only objects that cross the replication boundary into the hub.
4. The hub UNION ALLs the regional summaries and re-aggregates to a global figure; it holds no raw data, so its own location is not a residency concern.

Output:

category	global_revenue	customers
electronics	512000.00	4100
grocery	288500.00	9600

Why this works — concept by concept:

• Aggregate-in-region — reducing raw rows to group sums on region-local storage means individuals are eliminated before anything is eligible to move, so egress carries insight, not people.
• k-anonymity gate — the HAVING count >= k filter guarantees every crossing aggregate covers enough individuals to be non-identifying, converting "aggregate" from "usually safe" into "provably suppressed."
• Hub-and-spoke — spokes are independent regional shards holding the data; the hub is a thin union layer holding only safe summaries, so no single place concentrates cross-region raw rows.
• Metadata/data split — the schema and job run centrally while the rows stay regional, giving one global report definition without one global data pile.
• Cost — the hub moves and stores O(groups) aggregates instead of O(rows) raw data, so the compliant design is also the cheaper and faster one to run globally.

Partitioning
Topic — partitioning
In-region aggregation and layout problems

Practice →

Access control Topic — access-control Egress-policy and suppression problems

Practice →

---

Cheat sheet — residency recipes

Pin a bucket region and block replication.

S3 bucket lives in one region for life; deny replication out of the boundary
- create bucket in eu-central-1
- bucket policy / SCP: DENY s3:PutBucketReplication to non-EU regions
- DENY cross-region snapshot/backup copy for this bucket

Geo-partition on a region key.

CREATE TABLE events (event_id BIGINT, region STRING, payload STRING)
PARTITIONED BY (region);          -- region=EU/ , region=US/  in pinned storage

SELECT * FROM events WHERE region = 'EU';   -- prunes US partitions

Route a tenant to its regional warehouse.

def route(home_region, requested_region, warehouses):
    if requested_region != home_region:
        raise PermissionError("residency violation")
    return warehouses[requested_region]     # region-pinned account

Per-region CMK envelope encryption.

dek = generate_data_key()
ct  = aes_encrypt(dek, row)
wrapped = CMK[row["region"]].wrap(dek)      # one single-region CMK per region
dek_out = CMK[row["region"]].unwrap(wrapped)  # decrypt needs the SAME region CMK; delete it -> crypto-shred

k-anonymity aggregate for egress.

SELECT city, COUNT(DISTINCT customer_id) AS customers, SUM(amount) AS revenue
FROM eu.orders
GROUP BY city
HAVING COUNT(DISTINCT customer_id) >= 10;   -- suppress small groups, then egress

Residency decision table.

Requirement	Control
Bytes must stay in region	Region-pinned storage + PARTITION BY region
One region must not read another	Partition pruning + per-region warehouse routing
No raw rows across the boundary	Aggregate-in-region + object-level egress policy
Operator must not be able to read	BYOK / HYOK, in-region single-region CMK
Even the operator must be in-jurisdiction	Sovereign cloud + confidential computing
Global view without moving rows	Hub-and-spoke, aggregate-only + k-anonymity

---

Frequently asked questions

What is the difference between data residency and data sovereignty?

Data residency is a statement about geography — that the physical bytes of a dataset are stored and processed within a named region and its copies (replicas, backups, temp files, caches) do not leave it. Data sovereignty is a statement about jurisdiction — whose laws can compel access to the data regardless of where it sits. You can satisfy residency (store EU data in the EU) while failing sovereignty (a foreign-headquartered operator remains legally compellable), which is why sovereignty controls like in-region operators and customer-held keys exist on top of region-pinning.

How is data residency different from disaster recovery?

Disaster recovery deliberately creates copies across failure domains so a regional outage does not cause data loss or downtime; residency deliberately bounds where copies may exist so data stays in one jurisdiction. The two pull in opposite directions, and cross-region DR replication is the single most common way a residency guarantee is silently broken. The fix is to keep DR inside the residency boundary — a second in-jurisdiction zone or region — rather than backing up to whichever region is cheapest.

How do you keep data from crossing a region boundary?

Make region a partition key so each region's rows live in their own partitions pinned to region-local storage, deploy one warehouse per region, and explicitly deny cross-region replication, snapshot copy, and stream mirroring for regulated data. Route every query through a resolver keyed on the tenant's residency so no code can point an EU session at US storage. When a global view is needed, aggregate inside each region and let only non-personal summaries cross the boundary, never raw rows.

What is BYOK and why does key residency matter?

BYOK (bring your own key) means you generate the encryption key material and import it into the provider's key service, keeping the power to disable or delete it; HYOK/external key stores go further and keep the key entirely in your own HSM so the provider never holds it. Key residency matters because whoever controls the key controls the data — envelope encryption wraps each object's data key with your region-local customer master key, so ciphertext stored under a foreign operator is unreadable without a key that stays in your jurisdiction, and revoking or deleting that key crypto-shreds the data across the live table and every backup at once.

Can metadata leave the region if the data cannot?

Usually yes — schema, column types, lineage, ownership, and classification tags are metadata, not the regulated payload, so a central catalog can span regions while the rows stay in the regional data plane. The important caveat is that value-bearing statistics (min/max, sampled values, histograms) can leak actual data, so a residency-grade catalog suppresses those for regulated columns. This metadata/data-plane split is what lets you run one global governance model without concentrating regulated data in one place.

What is a sovereign cloud?

A sovereign cloud is an offering designed so that not just the data but the operation stays in-jurisdiction — in-country legal entity, in-country support staff, and isolation from foreign-parent control — for mandates where region-pinning and customer keys are not enough. Examples include AWS European Sovereign Cloud, Microsoft Cloud for Sovereignty, and Google Sovereign Controls, alongside national initiatives like Gaia-X. Combined with confidential computing (encrypting data in use inside enclaves), it closes the gap where even the operator's administrators might otherwise access plaintext.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every residency idea above, from geo-partitioning on a region key to per-region warehouse routing, in-region CMK crypto-shredding, and k-anonymity aggregate egress, maps to a hands-on practice room where you build the layout against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you keep EU rows from ever reaching US compute?" holds up under a senior interviewer's depth probes.

Practice partitioning problems now →
Region-shard drills →