# Find and redact personal data

Shape / columns

A variant of [Hash or mask a column](https://meridian.online/operations/hash-or-mask-a-column).

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

Page: <https://meridian.online/operations/hash-or-mask-a-column/find-and-redact-personal-data>

## Status

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.

## What other tools call it

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

- Duckle: [`PII Redact`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- AWS Glue: [`Detect PII`](https://docs.aws.amazon.com/glue/latest/dg/detect-PII.html) (read 2026-09-27)
- KNIME Analytics Platform: [`KNIME Presidio Extension (extension)`](https://api.hub.knime.com/extensions/org.knime.python.features.presidio) (read 2026-09-27)