This route decides which columns in this warehouse are personal data, who is allowed to say so, and what happens when that data has to leave the platform or disappear from it. The first two are engineering decisions and take an afternoon. The third — what 'erased' means and by when — is a number the client's data protection officer has to sign, and it is fixed by retention settings you choose in week one, long before the first request arrives.
Stand 29.07.2026
Marking the data
Two mechanisms, in this order: declare what you know in the DDL, then let a scan find what you did not. Neither protects anything on its own — a tag is a handle for the policies in the access route to grab.
The canonical model flags exactly three columns on gold.dim_customer as personal data: email, phone, street. customer_name is deliberately not among them — this is a B2B distributor, so the customer name is a company name. The contact columns are where a named employee appears.
Those three columns take three of the four governed pii values declared once at account level in the access route — email, phone, address. The fourth, name, is declared and unused: it exists so that the day a contact-person column arrives, the answer is an existing tag value rather than a new tag.
sqlThe tag is applied in the DDL that ships the column
-- The governed tag and its permitted values are created ONCE at account
-- level (see the access route); ASSIGN is granted to a group, not to everyone.
-- CREATE GOVERNED TAG pii VALUES ('email', 'phone', 'address', 'name');
SET TAG ON COLUMN dwh_prod.gold.dim_customer.email pii = email;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.phone pii = phone;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.street pii = address;
-- NOT: ALTER TABLE ... ALTER COLUMN email SET TAGS ('pii' = 'email').
-- That statement applies an UNGOVERNED tag that happens to share the name.
-- ABAC policies read governed tags only, so the column then looks tagged in
-- Catalog Explorer, satisfies a review by eye, and is served raw.
Governed is the operative word: the value list is fixed at account level and applying a tag needs an explicit permission, so nobody invents pii = kinda_sensitive on a Friday afternoon. The drift check is one query, and it belongs in CI:
sqlColumns that look like personal data and carry no classification
SELECT c.table_name, c.column_name
FROM dwh_prod.information_schema.columns AS c
LEFT JOIN dwh_prod.information_schema.column_tags AS t
ON t.schema_name = c.table_schema
AND t.table_name = c.table_name
AND t.column_name = c.column_name
AND t.tag_name = 'pii'
WHERE c.table_schema IN ('silver', 'gold')
AND c.column_name RLIKE '(email|phone|mobile|street|birth|iban)'
AND t.tag_value IS NULL; -- any row here fails the build
Why here
A classification written in a wiki page protects nothing. A classification written as a tag is the join key between 'this column is personal data' and every mask, access review and audit query that has to act on it — one policy on the catalog covers every table carrying the tag, including tables nobody has built yet.
The concrete failure here is the second customer table. Six months in, somebody builds a customer extract for a marketing use case. Under per-table masks it is naked until a human remembers. Under tag-driven policy, if the tag ships in the same CREATE TABLE as the column, the new table is protected on its first write.
Doing it
Tag in the same pull request as the column. A tag clicked into Catalog Explorer exists in prod and not in dwh_dev, so nobody tests against the mask until production.
Classify silver.customer too. Personal data does not become personal at the gold boundary, and analysts with silver access read the same email addresses.
Keep the value list to the four the access route declares. A vocabulary of fifteen gets policies written against a handful of them, and then no one can say which columns are uncovered without running the drift query.
Run the untagged-lookalike query in CI over silver and gold, and fail the build on a non-empty result.
Embedding it
Have the team classify dim_customer themselves before showing them your list, then diff. The disagreement is always customer_name, and that argument is the one worth having.
Get one engineer to add a contact column and watch CI catch it. A check they have seen fire is a check they trust.
Put the value list under the DPO's name and let the team take it to them. A classification engineering owns alone gets overruled at the worst possible moment.
When someone proposes tagging everything, hand them the query that counts the columns and let the number argue.
Defaults
Tags applied in DDL, in the same commit as the column, in all three catalogs.
Governed tags with a fixed value list; ASSIGN granted to a group.
Classify from silver onward, not only in gold.
Every tag value is referenced by at least one policy. An unreferenced value is decoration.
Gotchas
Tagging gold.dim_customer and forgetting silver.customer. The standing access review over PII-tagged tables then reports full coverage, because the untagged table is not in its scope — the report is clean precisely because the gap is invisible to it.
Tags applied by hand in the UI. dwh_dev has no tag, so the mask never evaluates there, the developer's test passes, and the first time anyone sees the masked column is in production.
A value list that grows. A policy matching has_tag_value('pii', 'email') does not match a column tagged email_marketing, and governed values stop you inventing a value outside the list — they do not stop someone adding one to it. The symptom is a column that is correctly tagged and completely unmasked.
Rebuilding a table to change a column type. The replacement column arrives untagged, because the tag is bound to the column and not to its name, so the mask stops applying the moment the migration runs and nothing errors. The untagged-lookalike query in CI is what catches that; a quarterly review is not.
Automated data classification scans Unity Catalog tables and proposes governed tags on columns that look like personal data. It is GA alongside ABAC, and it is an agentic scan over the catalog rather than a regex you maintain. Detection exclusions — teaching it to stop reporting a false positive — were Beta at the 29.07.2026 pass. That sub-feature is not in the currency register, so check the classification doc below before you design a review process on top of it.
It will find email on gold.dim_customer, which you tagged in DDL anyway. The finding you actually want is the contact address sitting in a free-text field of a new bronze.shop_order attribute that arrived with schema evolution last Tuesday.
Why here
The reason to run it is bronze. bronze.shop_order lands JSON with schema evolution, so a new field appears whenever the webshop team ships, and nobody tells the warehouse. A classification scan is the only mechanism in this architecture that notices.
The reason not to lean on it is timing. A scan runs on a schedule; a column exists from its first write. In the window between the two the data is present and unclassified — and that is exactly the window in which someone builds the report that reads it.
Doing it
Enable it on dwh_prod and, separately, on dwh_dev. A pii = email finding in dev is how you discover the production copy somebody made.
Give the result review a named person and a cadence. Unreviewed proposals are the entire failure mode.
Treat a bronze finding as a source-contract ticket, not just a tagging ticket — a new personal-data field means the source changed shape.
Keep DDL tags as the primary mechanism. The scan is the net underneath, not the plan.
Embedding it
Run the first scan on dev with the team watching. It usually finds something embarrassing, and it is much better that they find it than that you do.
Ask them to predict the findings before it runs. The gap between prediction and result is an honest measure of how well they know their own data.
Hand the review to their data steward. If it sits on your calendar it stops the week you leave.
Defaults
DDL tags first, automated classification as the safety net.
Scan dev and test as well as prod.
A named reviewer and a fixed cadence, written into the anonymisation concept.
Bronze findings open a conversation with the source team, not just a ticket.
Gotchas
Believing the scan means coverage. Proposals that nobody confirms attach to nothing, so the number of findings goes up while the number of protected columns stays flat.
The free-text column. A comment field containing 'Frau Weber unter 0170… anrufen' is personal data no classifier reliably catches and no column mask can partially redact. The fix is not to land free text in gold at all.
The scan reads sample values, which is itself processing of personal data. Switch it on without adding it to the record of processing activities and the finding is not a mis-tagged column — it is an undocumented processing activity, raised by the DPO in the meeting that was meant to sign off your concept.
Building the review workflow on detection exclusions. Without them the same false positives return on every scan, the queue is forty rows of the same thing, and within two months nobody opens it — so the one genuine bronze finding sits unconfirmed among them. With them, the workflow rests on a Beta surface. Pick which of the two you are living with and write it in the concept.
Two places where a team's mental model is wrong in a way that feels safe. One is a legal misreading, the other a shortcut — and they compound, because a hashed production copy in dev is two mistakes that each hide the other.
Hashing personal data is pseudonymisation under GDPR Art. 4(5). The result is still personal data: still in scope, still subject to erasure requests, retention limits and the record of processing activities. This is the most commonly wrong claim in DACH data projects, and it is normally stated with complete confidence in the first workshop.
The canonical model makes the point without needing an extra example. customer_sk = sha2(concat_ws('||', customer_id), 256), and gold.fct_order_line carries customer_sk with no contact columns — so it is tempting to call the fact table PII-free. It is not. gold.dim_customer holds the mapping back to customer_id and email. That mapping is the 'additional information' in Art. 4(5), and holding it is exactly what keeps the pseudonym personal.
sqlWhy a hash over a known population is not a secret
-- The 'additional information' need not even be a lookup table. Given the
-- customer list you already have, the hash inverts by enumeration: forty
-- thousand SHA-256 evaluations, well under a second.
SELECT sha2(concat_ws('||', customer_id), 256) AS customer_sk,
customer_id,
email
FROM dwh_prod.silver.customer
WHERE _tech_is_current;
What does reach anonymisation is losing the ability to single a person out. gold.agg_revenue_month at month × region × product group is anonymous while every cell aggregates enough customers — and stops being anonymous the moment a region contains one of them. That is what a small-cell suppression threshold is for, and it is a number, not a principle.
Why here
The consequence is operational, not academic. If a hashed dataset is personal data then the dev copy of it is a transfer, the retention clock runs on it, erasure requests reach it, and it has to appear in the processing register. A team that believes hashing settled the question skips all four, and the gap gets found by an auditor rather than by them.
The useful framing in the room is the three tests from Article 29 Working Party Opinion 05/2014 — the EDPB's predecessor, and still what a DACH regulator reasons from: can you single out an individual, can you link records across datasets, can you infer an attribute. A deterministic hash fails the second one by design — preserving linkability is the whole reason you built it.
Doing it
Write 'pseudonymisiert', never 'anonymisiert', in German documents. The two words carry the legal distinction directly, and using them loosely is how the misunderstanding spreads.
Use the hash for what it is good at — a stable, non-guessable join key — and get protection from masks and from not carrying the column at all.
Where genuinely anonymous output is needed, aggregate and suppress small cells, with the threshold written down as a figure.
Do not sell salt-and-forget as anonymisation unless the salt is provably destroyed. A salt in a secret scope is a salt you still hold.
Embedding it
Run the enumeration above live against the real customer list. Ninety seconds, and 'but it's hashed' never comes back.
Make the team write the sentence for the concept themselves, in German. Getting the wording right is where the understanding actually lands.
When someone proposes hashing to solve an access problem, ask who can read the mapping. The answer is 'we can', and the question answers itself.
Defaults
Hash to join, mask to protect, aggregate to anonymise. Three jobs, three mechanisms, never substituted for each other.
Pseudonymised datasets inherit the retention, erasure and access rules of their source.
The suppression threshold is a documented number.
'Anonymised' appears in a document only where no individual can be singled out. Otherwise the word is 'pseudonymised'.
Gotchas
'It's hashed, so it's anonymous.' The dataset stays in scope, so the retention clock, the erasure duty and the processing register all still apply — and none of them were built, because the team believed they were exempt.
Hashing a low-cardinality column. sha2 over postal_code, country or customer_segment brute-forces instantly: the hash is unreadable to your own analysts and perfectly readable to anyone with a motive.
Hashing to enable a join between two datasets and then calling it a privacy control. Linkability is precisely what was built, and it is the second of the three tests above.
Stripping contact columns from an extract but keeping customer_sk. The recipient cannot read an email and can still single out one person and follow them across every row you sent.
The rule is one sentence — no production data in dwh_dev, no exceptions, no 'just this once'. Breaking it takes half a line of SQL that looks entirely reasonable in a pull request:
sqlThe one statement that ends the classification story
CREATE TABLE dwh_dev.gold.dim_customer
DEEP CLONE dwh_prod.gold.dim_customer;
Whatever the tags on the copy say, the policy does not travel: ABAC policies are attached to the catalog, schema or table they were created on, so nothing in dwh_dev masks anything. The copy is unmasked by construction, and it was made in good faith by someone with legitimate production access.
Test data comes from factories instead. The fixtures schema in dwh_test is seeded by the same row factories the PySpark tests use, and synthetic contact data uses reserved ranges — addresses at @example.com, which RFC 2606 reserves so a stray test mail can never reach a real inbox, and numbers from the block the national regulator reserves for drama and testing.
Why here
Two failures, and the second is the one that lasts. The first is obvious: personal data in a catalog with looser grants and a differently bound workspace. The second is that a copy is a snapshot — it drifts from production's schema, and the tests passing against it stop proving anything. The team ends up with a compliance exposure and a test suite that lies, and only notices the second one.
Doing it
Remove the grant that makes it possible: dev principals get no SELECT on dwh_prod, with workspace-catalog binding behind it, so the clone fails rather than being merely discouraged.
Build the fixture factories in week one. The rule only holds while the compliant path is faster than the copy.
When production shape is genuinely needed, copy the distribution — row counts, cardinalities, null rates — and generate rows to match. A profile is not personal data.
If a masked subset must exist, produce it on the production side and move only the result. Masking after the copy has landed is masking after the incident.
Embedding it
Ask where the current dev data came from. Somebody always knows, and the answer is usually a clone from last year.
Run a classification scan on dwh_dev in front of them. The findings make the argument so you do not have to.
Have them write the dim_customer fixture factory, not you. A team that owns its fixtures stops wanting the copy.
Defaults
Fixtures generated by factories; no path from prod data into dev, enforced by grants rather than by policy prose.
Distribution profiles, not rows, when volume realism matters.
Any masked extract is produced on the prod side of the boundary.
The test-data rule is a named chapter in the anonymisation concept with the DPO as a reviewer.
Gotchas
DEEP CLONE across catalogs. The masks were attached to dwh_prod and do not evaluate in dwh_dev, so every developer sees raw email addresses and the audit trail records only a legitimate read by someone entitled to it.
'Anonymised' dev data that keeps customer_id or customer_sk. The names are gone; every individual is still singled out and followable across every table.
The one-off debug copy. It never gets dropped, because the ticket that justified it closed and nobody owns the cleanup. You find it a year later in a classification scan.
Dropping the copy and calling it done. The files persist until VACUUM passes the retention threshold, which turns 'we deleted it' into 'we deleted it, in about a week'.
Leaving, and being erased
Data leaves the platform, and data has to disappear from it. The first is a design choice made once; the second is a request that arrives without warning and finds out whether the first one was made well.
When somebody outside the warehouse needs numbers there are two options, and one of them survives a governance review. A CSV export is a copy that leaves with no lineage, no revocation, no audit trail past the download, and no way to honour an erasure request against it. Delta Sharing keeps the data where it is and lends a view of it.
sqlShare the aggregate, not the dimension
CREATE SHARE revenue_reporting
COMMENT 'Monthly revenue for group controlling. No personal data crosses.';
ALTER SHARE revenue_reporting
ADD TABLE dwh_prod.gold.agg_revenue_month;
-- Databricks-to-Databricks: the recipient is an identity, not a token. The
-- sharing identifier is <cloud>:<region>:<metastore-uuid>, and it is theirs to
-- hand you — they run SELECT CURRENT_METASTORE() and send you the result.
CREATE RECIPIENT group_controlling
USING ID 'aws:eu-central-1:00000000-0000-0000-0000-000000000000'
COMMENT 'Group controlling metastore.';
GRANT SELECT ON SHARE revenue_reporting TO RECIPIENT group_controlling;
The design decision is in the second statement. gold.agg_revenue_month is month × region × product group, so nothing personal crosses the boundary and the sharing conversation never becomes a privacy conversation. If the recipient truly needs customer grain, share a view that drops email, phone and street — not the dimension with a mask on it.
Why here
Revocation is the whole argument. REVOKE SELECT ON SHARE takes effect on the next read; a spreadsheet in someone's Downloads folder is permanent. So is erasure: Art. 17 obliges you to remove the customer's data, and you cannot remove it from a file you no longer control — the export made a promise you can no longer keep.
The second argument is evidence. Recipient reads land in system.access.audit like any other access, so 'who saw this, and when' has an answer for external consumers and not only internal ones.
Doing it
Default to Databricks-to-Databricks when the recipient is on Databricks. Open sharing hands out a credential file, and a bearer token is an identity anyone holding the file can assume.
Share aggregates and purpose-built views. Never share a dimension that carries contact columns.
Where open sharing is unavoidable, rotate the recipient token on a schedule and monitor its expiry. A D2D recipient has no token, so this chore only exists in the case you were trying to avoid — and an unrotated token is either a silent outage on the expiry date or a permanent credential.
Check whether query-result download is even enabled in the workspace. If exports are possible, they are happening.
Embedding it
Ask what the last CSV that left the building contained and who still has it. The silence is the lesson.
Have the team put the share and the recipient under version control — Terraform, or the DDL above in a reviewed migration job. Bundles had no share resource type at the 29.07.2026 pass, so 'just declare it in the bundle' is the answer people give and then cannot implement.
Put a revocation drill in the game day: revoke, confirm the recipient's read fails, restore. Ten minutes, and the control stops being theoretical.
Defaults
Sharing over export without exception; an unavoidable export gets an owner, a purpose and an expiry date in writing.
D2D over open sharing whenever the recipient has a metastore.
Share the narrowest object that answers the question — aggregate first, view second, base table never.
The share and its recipients are code, reviewed and versioned like the tables they expose — not a Catalog Explorer click nobody can diff.
Gotchas
Assuming a column mask or row filter travels with a shared table. It fails one of two ways and you cannot tell which in advance: ADD TABLE rejects the table outright, which costs an afternoon, or the recipient reads the column unmasked and you never find out, because their reads reach your audit log and their result sets do not. Share a view that physically has no contact columns and the question stops existing.
Open sharing to a recipient who is on Databricks. You replaced an identity with a file, and files get forwarded — the access log then shows one recipient for an unknown number of readers.
Sharing WITH HISTORY when the point was to limit what the recipient sees. History includes the versions from before you removed the column.
The export that already happened. Nobody volunteers it. system.access.audit is where the result downloads are, and the answer is usually 'yes, monthly, for two years'.
An Art. 17 request arrives for one customer. In this warehouse that customer exists in bronze.sap_kna1 once per daily extract for as long as bronze has been running, in silver.customer and gold.dim_customer once per validity interval, and as a customer_sk on every gold.fct_order_line row they ever generated. Deleting the dimension rows breaks the facts and destroys the revenue history.
Redact, do not delete. Keep the key, the intervals and the non-identifying attributes; overwrite the identifying ones, in place, across every interval. Note who the data subject is here: the named contact reachable through email, phone and street, not the customer, which is a company and has no Art. 17 right. customer_name is a company name and stays — erasing it would destroy a business attribute the request never covered.
sqlErasure is a correction, not a business change — it must not open a new version
UPDATE dwh_prod.gold.dim_customer
SET email = NULL,
phone = NULL,
street = NULL,
_tech_hash = sha2(concat_ws('||', customer_segment), 256)
WHERE customer_id = :customer_id;
-- Same in silver, where the SCD2 comparison actually runs. The hash is
-- recomputed over the rule-4 trigger set ONLY: customer_segment opens a new
-- version, an address change does not. Skip this line and the next run reads a
-- difference, closes the current interval and opens a fresh one holding the
-- redaction.
UPDATE dwh_prod.silver.customer
SET email = NULL,
phone = NULL,
street = NULL,
_tech_hash = sha2(concat_ws('||', customer_segment), 256)
WHERE customer_id = :customer_id;
customer_sk, customer_name, region_sk, customer_segment, country and the intervals survive, so gold.fct_order_line still joins, gold.agg_revenue_month still reconciles, and the accounting record required by § 147 AO stays intact. Which columns survive is not an engineering decision — the DPO names them, and the list belongs in the anonymisation concept, versioned.
Why here
The step everyone forgets is the source. If SAP still holds the record, the next daily full extract lands in bronze.sap_kna1, the snapshot comparison sees a change, and the pipeline writes the email back overnight. The ticket is closed, the DPO has been answered, and the data is back inside twenty-four hours with nobody watching. Erase at source first; keep a suppression list the pipeline reads, so a re-delivery is re-redacted rather than re-applied. That list is itself a list of data subjects — same tags, same grants.
The second forgotten step is physical, and it applies to the UPDATE above as much as to a delete. The redacted row lands in a new file; the file holding the old email address is still there and still readable by time travel until VACUUM passes the retention threshold. Where rows are genuinely deleted rather than redacted, deletion vectors add one more layer: a DELETE marks rows without rewriting the Parquet file, so REORG TABLE … APPLY (PURGE) is what rewrites it and VACUUM is what removes the old one.
Either way the honest answer to 'when is it gone' is a number derived from that threshold, and it has to be shorter than the deadline you promised. Check who owns the schedule before you quote it: on a managed table under predictive optimization you are not the one running VACUUM, so the number is that feature's cadence rather than your job's — see the predictive-optimization register entry.
Doing it
Erase at source, then in the warehouse, then verify after the next scheduled load that it stayed erased.
Redact in place. Never delete SCD2 rows, and recompute or exclude _tech_hash so the erasure does not open a new interval.
Set the bronze retention window in week one. bronze.sap_kna1 keeps one row per customer per day forever by default, and 'forever' is what makes every future erasure a full-history rewrite.
Where physical removal is required, follow DELETE with REORG TABLE … APPLY (PURGE) and VACUUM, and state the resulting lag in the reply to the data subject.
Ship the erasure as a parameterised job with a fixture customer and an assertion — not a notebook someone opens under pressure.
Embedding it
Have the team list every place one customer physically exists before you show them yours. The list is always shorter than reality, and the gap is the lesson.
Make the erasure job theirs, with its own test, and exercise it in the game day alongside the revocation drill.
Send them to the DPO for the redact/retain column list before any SQL is written. Engineering guesses this wrong in both directions.
Diary the follow-up check for the day after the next full load. Whoever finds a resurrected row once never skips that check again.
Defaults
Redaction over deletion for dimensions; deletion only where nothing downstream depends on the key.
Source first, warehouse second, verification after the next load — as three steps in one runbook.
A deliberate bronze retention window, so erasure is bounded work rather than a full-history rewrite.
The stated erasure lag matches the VACUUM retention actually configured — and the cadence predictive optimization actually runs at — not the one you would like.
Gotchas
The overnight resurrection. Erasure applied only in the warehouse; the next full extract of bronze.sap_kna1 re-delivers the customer and the pipeline writes the email back. Nobody notices until the next access review, if there is one.
The erasure opening a new SCD2 version. _tech_hash was computed over every attribute rather than over the rule-4 trigger set, so nulling the contact columns looks like a change: the next run closes the current interval and opens a new one, and the history now claims the customer changed address on the day they asked to be forgotten.
DELETE with deletion vectors. SELECT returns nothing and the bytes remain in the Parquet files until REORG TABLE … APPLY (PURGE) plus a VACUUM past the threshold. 'It is deleted' is true for readers and false for storage.
Time travel and clones. DELETE does not touch older table versions, and last quarter's DEEP CLONE is a separate table with its own copy — the erasure has to reach each one, which is one more bill for the dev copy nobody dropped.
Promising 'immediately'. VACUUM cannot go below the retention threshold without disabling a safety check, and disabling it to keep a promise is how a concurrent reader gets a missing file instead of a row.
The generic platform material lives in the official docs and is not restated here. These are the pages worth having open while you work through this route.
Apply tags to Unity Catalog securable objects — SET TAG against a governed tag versus ALTER TABLE ... SET TAGS against a free-text one — the distinction the first code block turns on
Data classification — what the automated scan does, how to scope it, and which parts are still Beta