Shape / columns · All operations

Find and redact personal data

A variant of Hash or mask a column

Detect personal data such as emails and card numbers in text, then redact or report it.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Find emails and card numbers in text, then redact them and report the count

SQL
CREATE TEMP TABLE notes AS SELECT * FROM (VALUES
  (1, 'Paid with 4111 1111 1111 1111 by ann.lee@example.co.uk, ref 12345678'),
  (2, 'Call back; card 5500-0055-5555-5559.'),
  (3, 'Nothing personal here, order 12345678'),
  (4, NULL)
) t(id, body);
CREATE TEMP TABLE result AS
SELECT id,
       regexp_replace(regexp_replace(body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}', '[EMAIL]', 'g'),
                      '\b(?:\d[ -]?){12,18}\d\b', '[CARD]', 'g') AS redacted,
       len(regexp_extract_all(body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}')) AS emails_found,
       len(regexp_extract_all(body, '\b(?:\d[ -]?){12,18}\d\b')) AS cards_found
FROM notes;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(redacted ORDER BY id) FROM result) = ['Paid with [CARD] by [EMAIL], ref 12345678', 'Call back; card [CARD].', 'Nothing personal here, order 12345678', NULL] AND (SELECT list([emails_found, cards_found] ORDER BY id) FROM result WHERE id <= 3) = [[1, 1], [0, 1], [0, 0]]) THEN 'as expected' ELSE error('the email and the card numbers were not redacted and counted, or an eight-digit reference was taken for a card') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the email and the card numbers were not redacted and counted, or an eight-digit reference was taken for a card”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

AWS Glue (1)
  • Detect PIIread 2026-09-27

    Detect personally identifiable information per row or per column, then mask, remove or report it

Duckle (1)
  • PII Redactread 2026-09-25

    redact email, phone, SSN and card numbers by regex

KNIME Analytics Platform (1)

Background

Redaction is the removal of information from a document or dataset for legal or security purposes.[1] The US government defines personally identifiable information (PII) as information that can be used to distinguish or trace an individual's identity.[1] Presidio provides modules that identify and anonymise private entities in text and images, such as credit card numbers, names and locations.[2]

When to use it

AWS Glue's Detect PII transform can scan every cell for personally identifiable information (PII), or scan a sample of rows to find which columns hold it.[3] In AWS Glue, a column is labelled as holding a PII entity only when the share of scanned rows containing it reaches a detection threshold, such as 10 percent.[3] AWS Glue's high detection sensitivity finds more entities, while its low sensitivity finds fewer and reduces false positives.[3] Presidio uses automated detection, so there is no guarantee that it will find all sensitive information, and other systems and protections should be used as well.[2] Treat a redaction pass as a filter that lowers the risk, not as proof the text is clean, and read a sample of what it let through. (Meridian's view)

Context words, such as SSN for a social security number, can raise the likelihood that a custom AWS Glue pattern matches, which helps where field names do not describe their contents.[3] AWS Glue cannot apply two different edit-in-place actions, redaction and hashing, to the same column, though detection can always be applied.[3] Write the patterns tight enough that an order number or a reference is not taken for a card, and test them on text that holds both. (Meridian's view)

The method

Presidio's analyser runs a set of recognisers, each in charge of detecting one or more kinds of personal entity.[4] Presidio's recognisers draw on named entity recognition, regular expressions, rule-based logic and checksums, together with relevant context.[2] The Luhn algorithm is not meant to be a cryptographically secure hash, but most credit card numbers use it to tell valid numbers from mistyped or incorrect ones.[5]

AWS Glue's Detect PII can replace a detected value with a chosen string, partly redact it, replace it with its SHA-256 hash, or store the detected entities in a new column.[3] Presidio's anonymiser treats each detected entity with an operator such as replace, redact, hash, mask or encrypt.[6] When no operator is given, Presidio's anonymiser replaces each entity with its entity type, such as <PHONE_NUMBER>.[6]

Sources

  1. [1]De-Identifying Government Datasets: Techniques and Governancestandard
  2. [2]Home - Presidiodocumentation
  3. [3]Detect and process sensitive data - AWS Gluedocumentation
  4. [4]Home - Presidiodocumentation
  5. [5]Luhn algorithm - Wikipediasecondary source
  6. [6]Home - Presidiodocumentation