Detect and redact personally identifiable information (PII)¶

Basic redaction examples¶

The following example redacts a name and an address from the input text.

SELECT AI_REDACT(
  input => 'My name is John Smith and I live at twenty third street, San Francisco.'
  );

Basic redaction output:

My name is [NAME] and I live at [ADDRESS]

The following example redacts only names and email addresses from the input text. Note that the text only contains a first name, which is recognized and redacted as [NAME]. The input text does not contain an email address, so no email placeholder appears in the output.

SELECT AI_REDACT(
  input => 'My name is John and I live at twenty third street, San Francisco.',
  categories => ['NAME', 'EMAIL']
  );

Selective redaction output:

My name is [NAME] and I live at twenty third street, San Francisco.

End-to-end example¶

The following example processes rows from one table and inserts the redacted output into another table. You could use a similar approach to store the redacted data in a column in an existing table. After redaction, the text is passed to the AI_SENTIMENT function to extract overall sentiment information.

1. Create a table with unredacted text.

   CREATE OR REPLACE TABLE raw_table AS
     SELECT 'My previous manager, Washington, used to live in Kirkland. His first name was Mike.' AS my_column
     UNION ALL
     SELECT 'My name is William and I live in San Francisco. You can reach me at (415).450.0973';
   

   Copy

2. View unredacted data.

   SELECT * FROM raw_table;
   

   Copy

3. Create a redaction table.

   CREATE OR REPLACE TABLE redaction_table (value VARCHAR);
   

   Copy

4. Redact PII from raw_table and insert the rows into redaction_table.

   INSERT INTO redaction_table
     SELECT AI_REDACT(my_column) AS value FROM raw_table;
   

   Copy

5. View redacted results.

   SELECT * FROM redaction_table;
   

   Copy

6. Run the AI_SENTIMENT function on redacted text.

   SELECT
       value AS redacted_text,
       AI_SENTIMENT(value) AS summary_sentiment
     FROM redaction_table;
   

   Copy

Chunking example¶

This example illustrates how to redact PII from long text by splitting the text into smaller chunks, redacting each chunk separately, and then recombining the redacted chunks into the final output. This approach works around AI_REDACT’s token limits.

1. Create a table with patient data.

   CREATE OR REPLACE TABLE patients (
     patient_id INT PRIMARY KEY,
     patient_notes TEXT
     );
   

   Copy

2. Split the text into chunks, apply AI_REDACT to each chunk, and concatenate the redacted chunks.

   CREATE OR REPLACE TABLE final_temp_table AS
     WITH chunked_data AS (
       SELECT
           patient_id,
           chunk.value AS chunk_text,
           chunk.index AS chunk_index
         FROM
           patients,
           LATERAL FLATTEN(
               input => SNOWFLAKE.CORTEX.SPLIT_TEXT_RECURSIVE_CHARACTER(
                   patient_notes,
                   'none',
                   1000
                   )
               ) AS chunk
         WHERE
           patient_notes IS NOT NULL
           AND LENGTH(patient_notes) > 0
       ),
     redacted_chunks AS (
         SELECT
             patient_id,
             chunk_index,
             chunk_text,
             TO_VARIANT(results:value) AS redacted_chunk,
             TO_VARIANT(results:error) AS error_string
           FROM (
             SELECT
                 patient_id,
                 chunk_index,
                 chunk_text,
                 AI_REDACT(chunk_text,TRUE) AS results
               FROM
                 chunked_data
           )
     ),
     final AS (
         SELECT
             chunk_text AS original,
             IFF(error_string IS NOT NULL, chunk_text, redacted_chunk) AS redacted_text,
             patient_id,
             chunk_index
           FROM
             redacted_chunks
     )
     SELECT * FROM final;
   

   Copy

3. Query the results.

   SELECT
       patient_id,
       LISTAGG(redacted_text, '') WITHIN GROUP (ORDER BY chunk_index) AS full_output
     FROM final_temp_table
     GROUP BY patient_id;
   

   Copy

Basic detection example¶

The following example identifies and returns the category, location, and text of each detected PII instance without redacting the input.

SELECT AI_REDACT(
    input => 'My old manager, Washington, used to live in Washington. His first name was Mike.',
    return_error_details => FALSE,
    mode => 'detect'
    );

Basic detection output:

{
  "spans": [
    {
      "category": "NAME",
      "end": 26,
      "start": 16,
      "text": "Washington"
    },
    {
      "category": "ADDRESS",
      "end": 54,
      "start": 44,
      "text": "Washington"
    },
    {
      "category": "NAME",
      "end": 79,
      "start": 75,
      "text": "Mike"
    }
  ]
}

End-to-end with allowlist example¶

The following example demonstrates a selective redaction workflow that uses detect mode and an allowlist. It loads a list of names to retain from a staged file, uses AI_REDACT in detect mode to identify PII locations, and then passes the results to a Python UDF that redacts only the PII not in the allowlist.

1. Retain an allowlist of values by loading the list from a stage into a temporary table.

   CREATE OR REPLACE TEMP TABLE string_list (value STRING);
   
   COPY INTO string_list
     FROM @mystage/allowlist.txt
     FILE_FORMAT = (
       TYPE = 'CSV'
       RECORD_DELIMITER = '\n'
       FIELD_DELIMITER = '\t'   -- any char NOT in file
       TRIM_SPACE = TRUE
       SKIP_HEADER = 0
       );
   

   Copy

2. View the allowlist table

   SELECT * FROM string_list;
   

   Copy

   Allowlist table output:

   VALUE
   Mike
   David
   

3. Create a Python UDF that selectively redacts PII based on the allowlist.

   CREATE OR REPLACE FUNCTION redact_spans_with_allowlist(
     SPAN_DATA VARIANT,
     ALLOWLIST ARRAY,
     ORIGINAL_TEXT STRING
     )
     RETURNS STRING
     LANGUAGE PYTHON
     RUNTIME_VERSION = '3.8'
     HANDLER = 'redact_text'
     AS
     $$
     def redact_text(span_data, allowlist, original_text):
         spans = span_data.get('spans', [])
         # Sort descending to maintain index integrity
         sorted_spans = sorted(spans, key=lambda x: x['start'], reverse=True)
   
         result = original_text
   
         for span in sorted_spans:
             text_val = span.get('text')
             if text_val in allowlist:
                 continue
   
             start, end = span['start'], span['end']
             label = f"[{span['category']}]"
   
             # Splice the string
             result = result[:start] + label + result[end:]
   
         return result
     $$;
   

   Copy

4. Test the UDF.

   SELECT redact_spans_with_allowlist(
     PARSE_JSON('{"spans": [{"category": "NAME", "end": 26, "start": 16, "text": "Washington"}, {"category": "NAME", "end": 79, "start": 75, "text": "Mike"}]}'),
     ARRAY_CONSTRUCT('Washington'), -- This will NOT be redacted
     'Hello, my name is Washington and his is Mike.'
     );
   

   Copy

5. Run AI_REDACT in detect mode.

   CREATE OR REPLACE TABLE raw (message TEXT);
   
   INSERT INTO raw (message) VALUES
     ('My old manager, Washington, used to live in Washington. His first name was Mike.');
   
   SELECT
       t.message AS message,
       AI_REDACT(input=>t.message, return_error_details=>FALSE, mode=>'detect') AS spans,
       redact_spans_with_allowlist(spans, l.str_list, message) AS result
     FROM raw t
       CROSS JOIN (
         SELECT ARRAY_AGG(value) AS str_list
           FROM string_list
         ) l;
   

   Copy

End-to-end with allowlist example output:

{
  "spans": [
    {"category": "NAME",
    "end": 26,
    "start": 16,
    "text": "Washington"
    },
    {"category": "ADDRESS",
    "end": 54,
    "start": 44,
    "text": "Washington"
    },
    {"category": "NAME",
    "end": 79,
    "start": 75,
    "text": "Mike"
    }
  ]
}