Security, Governance & Compliance

Pipelines carry data about real people: names, emails, orders and places. This data needs care. A leak, or a deletion request you cannot meet, hurts those people and the company.

Personal data is any data that points to a living person. A name is personal data. An email address is too.

We use the shop again. The five customers and the six orders are the same as before.

We add a made-up email column, such as asha@example.com. Every email, role, log and key in this chapter is made up.

What you already know

• Data Contracts, Schema Evolution & Governance: owners, access grants and one masking policy.
• Data Observability & Monitoring: lineage, which is the map of what reads what.
• Delta Lake & the Lakehouse: time travel, and VACUUM, which deletes old files.
• The Warehouse Platform: Storage, Compute & Loading: time travel in Snowflake.
• Reliability, Failure & Recovery: backups and a second copy in another region.

We do not teach those again. We use them, and we add what they do not cover.

What you will learn

1. The words the law uses.
2. How to classify columns and hide them.
3. Why a plain hash of an email is weak.
4. Who holds the key, and where secrets live.
5. Roles, views and row rules.
6. Audit logs and lineage.
7. How to delete one person from everywhere.
8. The other rights people have, and retention.

The main privacy law in Europe is the GDPR, the General Data Protection Regulation. It covers every company set up in the EU, for all the personal data it handles. Norway, Iceland and Liechtenstein apply it too.

A company outside the EU can be covered too. This happens when it offers goods or services to people in the EU, or tracks what they do there. The UK has its own similar law, the UK GDPR.

Six words decide who owes what. Look at the table, with the shop as the example:

You build pipelines for the shop, so you work for the controller. Say Asha asks for her data to be deleted. The request goes to the shop.

The shop cannot say, "our cloud provider holds it." The shop must tell each processor what to do. A processor does not decide on its own.

Two more words are often mixed up:

• PII means personally identifiable information. It is a US term, and it is often narrower than personal data.
• PCI-DSS is a card-industry security standard, not a law. You agree to it in your contracts with card companies.

A dynamic IP address and a cookie id can be personal data. European courts have said this about IP addresses. They point to a person if someone can link them to one.

You cannot protect data you have not found. The first step is classification: you label each column by how sensitive it is. Then you pick a treatment for each column.

Here is the customers table with the made-up email column:

SELECT customer_id, name, email, country
FROM   customers
ORDER  BY customer_id;

Now label each column and pick a treatment. Look at the table:

You have five treatments to choose from:

• Drop: do not load the column at all.
• Mask: hide part of the value.
• Pseudonymise: swap the value for a stable code, so rows still join. The next section shows how.
• Generalise: keep less detail, such as a birth year instead of a birth date.
• Tokenise: swap the value for a random token. A locked vault maps it back. Card numbers use this.

Mask is the easiest to see. This query keeps the first letter of each email:

SELECT customer_id,
       LEFT(email, 1) || '***' AS email_masked,
       country
FROM   customers
ORDER  BY customer_id;

A reader now sees a letter, not an email. Apply the treatment when the data arrives. If raw emails reach every table first, every copy and backup holds them.

Analysts often need to count or join by person. They do not need to read the email. A hash can help.

A hash function turns a value into a short fixed code. The same input always gives the same code. Look at the first 8 characters of a SHA-256 hash of each email:

SELECT customer_id,
       SUBSTR(SHA2(email, 256), 1, 8) AS email_hash
FROM   customers
ORDER  BY customer_id;

But a plain hash is weak. Emails are easy to guess. An attacker hashes a list of known emails and compares the codes.

If the attacker hashes asha@example.com, the code starts with ea4e36a8. Now the attacker knows that row 1 is Asha. The hash did not hide a value that can be guessed.

The fix is a keyed hash, called HMAC. You mix a secret key into the hash. Without the key, an attacker cannot test guesses.

First make the emails lowercase and trim spaces. Otherwise one person gets two codes.

import hmac, hashlib

key = get_secret("pseudonym-key")   # bytes, from a secrets manager
clean = email.strip().lower()
code = hmac.new(key, clean.encode(), hashlib.sha256).hexdigest()[:8]

You may know a salt, a random value added to each row. Do not use a random salt per row here.

The same email would then give different codes, and you could not join. Use one secret key for all rows.

This is pseudonymisation: a stable code replaces the identifier. The GDPR says data is still personal data if extra information can link it back to a person. Here the extra information is the key, or a list of guesses.

So pseudonymised data still has erasure requests, access requests and breach rules.

Anonymous data is different. Nobody can identify a person by any means reasonably likely to be used. The law then does not apply.

That is hard to reach. Look at paid revenue by country. No names are in the result:

SELECT c.country,
       COUNT(DISTINCT c.customer_id) AS customers,
       SUM(o.amount) AS revenue
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  o.status = 'paid'
GROUP  BY c.country
ORDER  BY revenue DESC;

Every row counts one customer. So each total belongs to one person, even with no name.

A common fix is a minimum group size: show a group only if it has at least a set number of people. Pick the number with your privacy team.

Encryption turns data into unreadable code. Only someone with the right key can read it again. A key is a secret value that locks and unlocks data.

Encryption in transit protects data on the network. It uses TLS, the standard for secure web connections.

Encryption at rest protects data stored on disks.

Which threat does each one stop? Look at the table:

Many real leaks look like the last two rows. So do not only ask, "is it encrypted?" Ask, "who can decrypt it?"

With a provider-managed key, the cloud provider holds the key. It decrypts for anyone allowed to read. With a customer-managed key, you own the key in a key management service (KMS).

A KMS is a locked service that holds master keys. You ask it to decrypt, and the master key never leaves it.

Reading then needs two permissions: one for the storage, and one for the key. You can remove either one.

Large data is usually locked in two steps, called envelope encryption. A data key locks the data. A master key in the KMS locks the data key.

One more idea links to deletion: crypto-shredding. You lock each person’s data with its own key.

To make the person’s data unreadable, you destroy the key. You must plan this before you store the data.

Keep secrets out of code

A secret is a password, a token or a key. If it is in code, it stays in git history, and everyone who reads the code sees it. Compare the two ways:

# Bad: the password is in the code
conn = connect(user="loader", password="made-up-Pa55word")

# Good: fetch it when the job runs
password = secrets_manager.get("warehouse-loader-password")
conn = connect(user="loader", password=password)

Least privilege means each person and each program gets the least access that does the job.

A role is a named set of permissions. You give roles to people, not one grant at a time. This is role-based access control, or RBAC.

Data Contracts, Schema Evolution & Governance showed grants and one masking policy. Here you add a view. A view is a saved query that acts like a table.

You give a role access to the view and not to the table. One view can hide columns and rows. Say the shop has three made-up roles:

• SUPPORT sees every customer and the full email.
• ANALYST sees every customer, with the email masked.
• ANALYST_IN sees only customers in country IN, with the email masked.

The third role needs a row rule. A small table says which countries a role may see. Here is the view, run as ANALYST_IN:

CREATE SECURE VIEW customers_safe AS
SELECT c.customer_id,
       CASE WHEN CURRENT_ROLE() = 'SUPPORT' THEN c.email
            ELSE LEFT(c.email, 1) || '***' END AS email,
       c.country
FROM   customers c
WHERE  CURRENT_ROLE() IN ('SUPPORT', 'ANALYST')
   OR  c.country IN (SELECT rc.country
                     FROM   role_countries rc
                     WHERE  rc.role_name = CURRENT_ROLE());

SELECT * FROM customers_safe;   -- run as ANALYST_IN

Now run the same view as each role. Look at the rows they see:

You keep one table and one view. The other way is a masked copy for each group. Copies drift apart, and an erasure request must then be done in every copy.

Warehouses also have built-in masking and row policies. The idea is the same.

A service account is a login that a program uses, not a person. Pipelines often run as one. Give it only what it needs, and review it like a person.

A grant says who can read data. An audit log says who did read it. These are two different questions.

Here is a made-up access log for 2026-01-22. Find who read the raw customers table:

SELECT user_name, role_name, accessed_at
FROM   access_log
WHERE  object_name = 'customers'
ORDER  BY accessed_at;

Lineage is the map of what reads what, from Data Observability & Monitoring. For privacy it answers one question: where did Asha’s email go? You need that answer for deletion requests and for breach reports.

A data catalog is a searchable list of datasets. Each dataset has an owner and a label such as "holds personal data". Without a catalog, nobody can say which tables hold personal data.

The right to erasure is Article 17 of the GDPR. A person can ask you to delete their personal data.

You must answer within one month. For complex or many requests you may extend by two more months, but you must tell the person within the first month.

Say Asha, customer 1, asks on 2026-01-22. Her data is in more places than the customers table. You can only delete what you can find, so lineage comes first.

Look at the places and the action for each:

Start with the database. Remove her name and email, and keep the row, so orders still join:

UPDATE customers
SET    name = '[removed]', email = NULL
WHERE  customer_id = 1;

SELECT customer_id, name, email, country
FROM   customers
ORDER  BY customer_id;

The lake, the warehouse and the backups need their own steps. The next section shows how old versions and backups keep her data alive.

A delete does not remove the old versions. Time travel keeps them on purpose, and backups keep old copies too.

Delta Lake & the Lakehouse showed deletion vectors. With them on, DELETE only marks her row, and the row stays in the file. REORG TABLE with APPLY (PURGE) rewrites the file without her, before the VACUUM.

Look at how long each one keeps Asha:

Delta Lake & the Lakehouse taught time travel and VACUUM. Here the delete and the VACUUM are 7 days apart:

-- 2026-01-22: remove Asha from the lake copy
DELETE FROM customers_lake WHERE customer_id = 1;
REORG TABLE customers_lake APPLY (PURGE);   -- only if deletion vectors are on

-- 2026-01-29 or later: remove the old files
VACUUM customers_lake;

Snowflake can keep her data for 8 days or more after the delete. With a longer time travel setting, it can outlast the one month you have to reply.

Tell the person that old copies expire on a set date. Ask your legal team how to word it.

Crypto-shredding, from the encryption section, makes her data unreadable in a file you cannot edit. It needs one key per person, from the first day. Whether that counts as erasure is a question for your legal team.

Deleting her today does not stop her coming back tomorrow. A backfill can reload an old file with her row. A restored backup can do the same.

The fix is a suppression list: a small table of people who asked to be erased. Every load checks it first. Keep as little as you can: the key and the date, not the name.

Say the 2026-01-08 file is loaded again. It has five rows. The suppression list is made up:

SELECT s.customer_id, s.name, s.country
FROM   staging_customers s
LEFT   JOIN suppression_list x
       ON x.customer_id = s.customer_id
WHERE  x.customer_id IS NULL
ORDER  BY s.customer_id;

Erasure is one right of several. Each right asks your pipeline for a capability. Look at the table:

All of these have the same one-month deadline as erasure. Each one is a lineage or catalog problem. If you cannot find a person’s data, you cannot answer any of them.

Breach notification is different. A breach is when personal data leaks, or is lost or changed by mistake. Article 33 says you usually must tell the regulator within 72 hours of finding out.

The audit log, the catalog and lineage help you say what leaked.

Some rules depend on the country and on your contract. Check with your legal team.

Article 5 says to keep personal data only as long as you need it. A retention policy sets a time limit for each dataset. The cheapest rule is to not collect what you do not need.

Say the shop keeps login events for 14 days. The job runs on 2026-01-22, so the cutoff is 2026-01-08.

The table is made up. The IP addresses come from a range reserved for examples:

DELETE FROM login_events
WHERE  event_date < DATE '2026-01-08';

SELECT event_id, customer_id, ip_address, event_date
FROM   login_events
ORDER  BY event_id;

This delete leaves old versions in time travel, as the last sections showed. Include them in the plan.

Data residency means the data must stay in a region. It covers backups and replica copies too.

The second region copy from Reliability, Failure & Recovery can break a residency rule. Check each region against your rules.

Six ideas to keep:

1. Classify each column. Then drop, mask, pseudonymise or generalise it.
2. A plain hash of an email can be guessed. A keyed hash is still personal data.
3. Encryption at rest stops disk theft. Ask who can decrypt, and keep secrets out of code.
4. Give roles access to a view. Keep one table, not one copy per group.
5. For erasure, find every copy. Delete, keep what the law requires, wait out old versions, and suppress.
6. Set a retention time, and do not collect what you do not need.

Check yourself

• Why does the 80.00 total for Dev point to one person?
• Why is a hashed email still personal data?
• On 2026-01-22 you delete a row from a Delta table. When can VACUUM remove the old files?
• Why must you run deletions again after restoring a backup?

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed. The last one, erasure, is the hardest.