Privacy and preparation
Detecting PII in structured exports: CSV, JSON and database tables
By SourceX Editorial · Updated
Short answer
To detect PII in a database or structured export, classify every column first, then scan cell values only where columns hold free text, nested JSON or mixed content. Column names, data types and sampled values reveal most identifiers quickly; notes, comments and payload fields hide the rest and need pattern scanning plus human review.
Key takeaways
- Classify columns before scanning cells; most structured PII sits in predictable fields.
- Column names can mislead, so confirm each label with sampled values and simple profiling.
- Free-text and JSON columns need cell-level scanning, because names and context hide inside sentences.
- Join keys and quasi-identifiers can re-link records even after obvious identifiers are removed.
- Automated scanners help but cannot guarantee they find everything, so sample their output by hand.
Why classify columns before scanning cells?#
Column classification comes first because structured exports hold most personal data by design, in fields built to store it. A ticket table has a requester email column, a CRM export has contact name and phone columns, and a dispatch table has a service address. Labeling those columns takes far less effort than scanning every cell, and it settles most of the export.
Each column gets one label, and the label drives the treatment. The free-text label is the one that marks where cell-level scanning is still needed.
- Direct identifier: names, emails, phone numbers, street addresses, account numbers.
- Quasi-identifier: postal code, birth date, job title, precise timestamps, small-town locations.
- Sensitive: health, financial, government ID, precise location, background check fields.
- Free text: notes, comments, descriptions, message bodies, JSON payloads.
- Internal identifier: customer IDs, employee IDs and ticket numbers that join to other tables.
- Safe: status codes, product SKUs, categories, amounts with no person attached.
Field names that usually hold PII#
Field names are the fastest first signal, and the same names recur across help desks, CRMs, ERPs and field service systems. Search the schema for these patterns, including abbreviations and vendor prefixes, before looking at any values.
Treat any column named custom_field, misc, ref or extra as unknown until its values are checked. Legacy systems often repurpose these fields for whatever a team needed at the time, and the original meaning is rarely documented.
| Field name patterns | What they usually hold | Default treatment |
|---|---|---|
| first_name, last_name, full_name, contact_name, requester | Customer or contact names | Drop or replace with a role label |
| email, email_address, from, cc, reply_to | Email addresses, sometimes with display names | Drop or replace with export-only tokens |
| phone, mobile, cell, fax, callback | Phone numbers | Drop |
| address, street, city, zip, postal_code, lat, lng | Home or site locations | Drop or coarsen to region |
| dob, birth_date, ssn, tax_id, license_no | Sensitive identifiers | Drop |
| ip_address, user_agent, device_id, cookie | Online identifiers | Drop |
| created_by, assigned_to, owner, agent, technician | Employee names or IDs | Replace with role or team labels |
| notes, comments, description, body, memo, payload | Free text that may contain anything | Scan cell by cell |
- 01Your work records
- 02Personal details removed
- 03You approve the release
- 04Licensed to AI developers
Confirming labels with sampled values and profiling#
Sampled values confirm or overturn what a column name suggests. Pull a random sample from each column across the full date range and every source system, and read it alongside simple profile statistics: distinct value counts, null rates, value lengths and the most common formats.
Profiling flags columns that behave like identifiers even when their names do not. A text column where nearly every value is unique and shaped like an email address is an email column, whatever the schema calls it. A ref2 column full of nine-digit values deserves the same suspicion as a column labeled ssn.
Pattern checks add precision. Regular expressions find emails, phone numbers and postal codes, and checksum tests such as the Luhn check help tell likely card numbers apart from order numbers of the same length. Some arbitrary numbers pass the Luhn check by chance, so treat a match as a flag for review, not proof.
Scanning free-text and JSON columns cell by cell#
Free-text and JSON columns are where structured exports stop being structured, so they need cell-level scanning. A ticket description, a delivery exception note or a CRM activity body can hold names, phone numbers, health remarks and pasted credentials in any order.
For JSON, flatten nested objects into key paths first, such as payload.customer.email or messages.author, and classify those paths the way you classify columns. Then scan the string values that remain. Watch for embedded base64 attachments and HTML bodies, which hide content from simple text scans.
Several tools do this work. Presidio, an open-source MIT-licensed SDK now governed by the community Data Privacy Stack project, combines named-entity recognition, regular expressions, rules and checksums. Google's Sensitive Data Protection inspects text and Cloud Storage repositories, and a 2023 snapshot of the Amazon Comprehend developer guide lists PII entity types including names, addresses, emails, passwords and access keys. Presidio's own documentation warns that automated detection cannot guarantee it finds all sensitive information, so plan for human sampling.
Join keys and quasi-identifiers: the PII you do not see#
Join keys and quasi-identifiers can re-identify people after every name and email is gone. A customer_id that matches the CRM, a technician_id that matches payroll, or a combination of postal code, birth date and job title can point back to one person.
Generate new tokens for each export rather than reusing production IDs, so the recipient cannot join the data back to anything outside the package. Keep the mapping table inside your own environment, with access limited to the people running preparation.
| Column type | Why it matters | Usual treatment |
|---|---|---|
| Internal customer or employee IDs | Join to systems that hold names | Replace with tokens generated for this export |
| Hashed emails or phone numbers | The same input gives the same hash, so values can be matched | Treat as identifiers; drop or re-tokenize |
| Exact timestamps | Pinpoint a person's visit, shift or call | Round, or shift dates consistently |
| Postal code with other attributes | Narrows rows to a household or small group | Coarsen to region or drop |
| Rare categories | Small groups make individuals visible | Merge into broader categories |
A repeatable workflow for each export#
The column register is what makes the work repeatable. When the next export arrives from the same system, most labels carry over, and only new or changed columns need fresh review.
- Export the schema and row counts for every table in scope.
- Label each column from its name, then confirm with a sample across the full date range.
- Profile uniqueness, formats and null rates to catch misnamed identifier columns.
- Flatten JSON into key paths and classify them like columns.
- Scan free-text values with a pattern and entity scanner.
- Check join keys and combinations of quasi-identifiers.
- Have a reviewer read samples of scanner output before and after treatment.
- Record each column's label, treatment and reviewer in the column register.
Illustrative: a 3PL checks its warehouse and shipment tables#
Illustrative: a fictional third-party logistics provider prepares an export of shipment and exception history from its warehouse management system database and NetSuite. The tables include consignee contacts, driver phone numbers, dock appointment notes and a JSON exception payload captured by handheld scanners.
The IT lead labels the contact, phone and address columns as direct identifiers and drops them. Customer IDs are replaced with export-only tokens, delivery timestamps are rounded and consignee locations are coarsened to region. The JSON payload turns out to hold photo references and the names of people who signed for freight, so those paths are removed. Notes columns are scanned and sampled, and the column register goes to counsel with the export plan. The company approves a narrower scope than first proposed.
How SourceX approaches structured exports#
SourceX handles structured scanning in the Preparation step of the SourceX five-step transaction, after Supply and Rights have set what is in scope. The initial fit check uses metadata such as system names and record families, so no tables or files are shared at that stage.
The column register and scan results become part of the privacy record in the SourceX Evidence Packet, alongside provenance, licensing rights, permitted use and release authorization. The supplier reviews and approves the treated export before anything is delivered.
Frequently asked questions
Is hashing an email column enough to remove PII?
Usually not. A hash of the same email always produces the same value, so anyone holding a list of emails can hash it and match rows. Hashed identifiers are generally treated as personal data. If the dataset needs to link records from the same customer, generate new random tokens for the export and keep the mapping out of the delivered package.
Do we need to scan every row, or is a sample enough?
Column labels can rest on samples, as long as the samples span the full date range and every source system. Free-text columns should be scanned in full by an automated tool, because one unusual note can hold a card number or a health detail. Human review then works on samples of the scanned and treated output.
Can table and column names themselves be sensitive?
Yes. Schemas sometimes include customer names in table names, such as a dedicated table for one large account, and custom fields can carry a client or project name. Those names can reveal confidential business relationships even when no personal data is present, so review the schema itself as well as the values.
What about employee names in created_by and assigned_to fields?
Employee names are personal data and add little to a licensed dataset. Replace them with role or team labels, such as dispatcher or escalation agent, so the workflow stays readable. Keep the mapping internal, and check whether any column joins back to HR or payroll systems through an employee number.
Which detection tool should we use?
The right tool depends on where the data lives and how much free text it holds. Cloud-native scanners suit data already in that cloud, while open-source libraries suit exports processed on your own machines. Whatever you choose, test it on a labeled sample of your own records, because accuracy varies by record type and writing style.
Sources
- Presidio is an open-source, MIT-licensed SDK for PII identification and anonymization that combines named-entity recognition, regular expressions, rule-based logic and checksums with context. Source
- Presidio's documentation warns that because it uses automated detection mechanisms, there is no guarantee it will find all sensitive information, and additional systems and protections should be employed. Source
- Presidio moved from a Microsoft-owned project to an independent, community-governed open-source project under the Data Privacy Stack GitHub organization and remains MIT-licensed. Source
- Google's DLP API v2 definition states that Sensitive Data Protection provides a sensitive data inspection, classification and de-identification platform that works on text, images and Google Cloud storage repositories. Source
- As documented in the Amazon Comprehend Developer Guide archived on GitHub in June 2023, Comprehend detects PII entity types including NAME, ADDRESS, EMAIL, PHONE, PASSWORD and AWS access and secret keys. Source
Related resources
See if your company qualifies
A short company assessment. No data uploads are needed.