Stripping Personal Data From a CSV Before Sharing It

The easy way to share a customer file is to export it and send it. The better way takes ten minutes and starts from a different question: what does the person who receives this actually need? An analyst studying churn by plan needs the plan, the sign-up month and the region. They do not need the email, the full name, the birth date or the street. Every column you remove is one fewer thing that can be copied, forwarded or breached, and every exact value you replace with a coarser one makes a single record harder to tie to a person. This article shows a repeatable way to do that with a query, so the strip is code that you can review and rerun, not a series of deletions in a spreadsheet. It sits next to CSV security risks that catch teams out as the practical answer to the personal data section.

Start from the question

Write down the recipient's purpose in one sentence, then list the columns that sentence needs. For churn analysis by plan and region: plan, monthly revenue, region, sign-up month. Anything not on the list does not go, even if it seems harmless. The discipline matters more than any technique, because most leaks come from a column nobody thought about: a notes field with a phone number in it, a referrer with a user's session in the URL, an updated_by with an employee's name.

An allow-list is the safer form of this rule. A query that names the columns it exports keeps working when a developer adds a date_of_birth column to the table, whereas SELECT * with a deny-list of known sensitive columns starts leaking it on the day it is added. The query in the next section is an allow-list.

Remove, generalize, replace

Three operations cover most cases. Remove direct identifiers outright: names, emails, phone numbers, street addresses, national identifiers. Generalize quasi-identifiers, which are harmless alone and identifying in combination: birth date, postcode, city, job title, exact timestamps. Replace the join key with a token so that rows from the same customer can still be linked inside the file, without the real ID.

Here is the sample source, with eight columns:

id,email,full_name,birth_date,postcode,city,plan,mrr
101,[email protected],Ada Lovelace,1985-03-14,SW1A 1AA,London,pro,49
102,[email protected],Bob Stone,1990-11-02,M1 4BT,Manchester,free,0
103,[email protected],Cy Young,1978-07-30,EH1 1YZ,Edinburgh,pro,49
104,[email protected],Di Park,1999-01-21,SW1A 2AA,London,team,199

And the strip, as one query:

COPY (
  SELECT
    left(sha256('pepper-change-me' || id::VARCHAR), 12) AS customer_ref,
    year(birth_date::DATE)                              AS birth_year,
    split_part(postcode, ' ', 1)                        AS postcode_area,
    city, plan, mrr
  FROM read_csv('customers.csv', all_varchar=true)
) TO 'shared.csv' (HEADER);

customer_ref,birth_year,postcode_area,city,plan,mrr
04dd0a98ee4b,1985,SW1A,London,pro,49
fbd6e5d3c1b5,1990,M1,Manchester,free,0
d811e4d08e5e,1978,EH1,Edinburgh,pro,49
015bbf3754cd,1999,SW1A,London,team,199

The email and name are gone. The birth date is a year. The postcode is its outward code, the first half. The ID is a token derived from a secret string, so two files built with the same secret link the same customer and nobody without the secret can reverse it by hashing guesses. The secret here is a placeholder and must live in a vault, not in the query; hashing or masking columns in a CSV safely explains why a plain hash of an ID or an email is weaker than it looks, and why a keyed one is better.

Free text is the leak you did not see

A comments column is where the cleanup fails. People write phone numbers, names and addresses into notes, and no column-level rule catches it. The options are to drop the column, to replace it with a category computed elsewhere, or to scan and redact. Redaction is the weakest, because a pattern for emails and phone numbers misses a name and an address in a sentence, so it works as a check and not as a guarantee. Detecting encoded payloads hidden in CSV fields has a scanner that doubles as a pre-flight for this: run it over the stripped file and read what it flags.

Also strip the file's own metadata. The filename (customers_ada_export.csv), the workbook properties of an .xlsx and the path you saved it to can carry names. Name the output for its purpose, such as churn-analysis-input.csv.

Count the groups that remain

Stripped is not the same as anonymous. A file with no names can still identify someone when a few remaining attributes are rare in combination. The test is to group by the quasi-identifiers you kept and look at the small groups:

SELECT birth_year, postcode_area, count(*) AS n
FROM read_csv('shared.csv')
GROUP BY ALL
HAVING count(*) < 2
ORDER BY 1;

birth_year  postcode_area  n
1978        EH1            1
1985        SW1A           1
1990        M1             1
1999        SW1A           1

Every combination is unique, which is expected in four rows and instructive: a person who knows that someone born in 1985 lives in SW1A can find them. In a real file with 200,000 rows, you want few groups of size one and none that identify an unusual person, which means coarsening more until the smallest group meets a threshold. A common working target is that every combination appears at least five times, a rough version of k-anonymity. If a group stays small, coarsen further (decade of birth, a larger region), suppress the row, or reconsider sharing the file.

Rare values matter too. A job title that appears once, a plan name that only one customer has and a high-revenue outlier are all identifying, even when the quasi-identifiers look generic.

Keep the pipeline, not the file

Put the query in version control and run it as a job. A stripped file produced by hand once is a one-off that nobody can audit, and the next export will be done differently. A query in the repository has a reviewer, a date in its history and a diff when someone changes it. Record who received the file, what purpose was stated and when it should be deleted (handling CSV files full of customer data under GDPR covers that record). For a file with one-way tokens, keep the key separate and rotate it per recipient, so tokens cannot be joined across two recipients' copies.

Check the result before it goes anywhere. Count the columns, print the header, grep the file for the @ character and for strings of digits as long as a phone number, and look at ten random rows with your own eyes. It takes a minute and finds the surprises.

When stripping is not enough

Removing names does not make data anonymous in a legal sense. Under the GDPR, data that can be linked back to a person with additional information is pseudonymized and still personal data, and only data that cannot be linked back by any means reasonably likely to be used counts as anonymous (Regulation 2016/679, Recital 26). A token with a secret is pseudonymization. A generalized file with small groups is pseudonymization at best.

So stripping reduces risk and does not remove the obligations: you still need a basis for sharing, a recipient bound to handle it properly and a reason to hold it. For data that is sensitive by nature (health, finance, children), or that will be published and not sent to one recipient, stripping is the first step and not the last, and you want an expert review or a stronger technique such as aggregation, differential privacy or synthetic data. When a recipient only needs counts, send counts: an aggregated table of plan by region by month has no individuals in it at all, and it is easier to defend than any row-level file. The related files for partial masking are in hashing or masking columns in a CSV safely, and the DuckDB syntax used above is documented in the CSV overview.