PII Classification and Masking

Security and Privacy Basics defined PII and the three ways to reduce it; on a lake you first have to find it. Presidio (https://github.com/data-privacy-stack/presidio 11,139 ) (MIT, 2.2.364; pip 21,050 install presidio-analyzer presidio-anonymizer plus spaCy's en_core_web_lg), started by Microsoft and since 2026 a community project, combines regular expressions, checksums, context words and named-entity recognition. It knows Singapore's NRIC but not Malaysia's MyKad, a number that packs three quasi-identifiers into one field:

The MyKad number 011223-12-1792 (sample data)
Part	Example	Meaning
YYMMDD	011223	Date of birth (23 December 2001)
PB	12	Place of birth: 01-16 states, 60-99 abroad
###G	1792	Serial; last digit odd for men, even for women

privacy/mykad.py adds a PatternRecognizer (dashed pattern 0.6, bare twelve digits 0.2, context words ic, mykad, nric) whose validate_result() rejects numbers without a valid date or place code. privacy/classify.py samples 200 values per column and tags the column when most match; DataHub's screenshot shows the result. Phone numbers lost to spaCy calling them dates until DATE_TIME was dropped, and nothing flagged birth_date or postcode. Tag such quasi-identifiers by rule: Latanya Sweeney estimated in 2000 that birth date, gender and ZIP code single out 87% of Americans. Then apply the techniques in SQL, with an HMAC pseudonym (stable for joins, irreversible without the key) and a count of people unique on the release:

privacy/three_ways.sql: masking, pseudonymization and a k-anonymity check in TrinoSQL

-- Masking, pseudonymization and anonymization of BookNest's customers (Trino 483).
USE iceberg.booknest;
SELECT customer_id, regexp_replace(email, '^(.)[^@]*', '$1***') AS masked,
       'CUSTOMER_' || upper(substr(to_hex(hmac_sha256(to_utf8(email),
                                to_utf8('demo-key-keep-in-a-vault'))), 1, 8)) AS pseudonym,
       '******-**-' || substr(national_id, 11) AS ic_masked
FROM customers WHERE country = 'MY' ORDER BY customer_id LIMIT 2;
-- Anonymization by generalization: how many people are unique on what we would release?
SELECT released, count_if(n = 1) AS unique_people, min(n) AS k FROM (
  SELECT 'birth date + postcode + country' AS released, count(*) AS n FROM customers
  GROUP BY birth_date, postcode, country
  UNION ALL SELECT 'birth year + country', count(*) FROM customers
  GROUP BY year(birth_date), country
  UNION ALL SELECT 'birth decade + country', count(*) FROM customers
  GROUP BY year(birth_date) / 10, country)
GROUP BY released ORDER BY unique_people DESC;

Output

 customer_id |      masked      |     pseudonym     |   ic_masked
-------------+------------------+-------------------+----------------
          12 | g***@example.com | CUSTOMER_A9FFDDA4 | ******-**-1792
          26 | c***@example.com | CUSTOMER_AED05D0A | ******-**-5051
            released             | unique_people | k
---------------------------------+---------------+----
 birth date + postcode + country |          5000 |  1
 birth year + country            |             9 |  1
 birth decade + country          |             0 | 19

All 5,000 customers are unique on birth date, postcode and country; birth decade plus country is 19-anonymous (each combination occurs at least 19 times). The masked IC still reveals gender in its last digit: mask by what a field encodes.

Before tickets reach an external LLM, privacy/scrub_tickets.py replaces entities with <PERSON> and the like. A name survived in 172 of 300 tickets with Security and Privacy Basics's regexes, in 4 with Presidio (spaCy missed "Eli Quist"), and in none with Presidio plus a deny list built from the customers table. Still log what was sent and keep the most sensitive data on a model inside the organization.

MENU