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.

   CopyExpand

   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';
   

   Show lessSee more

   Scroll to top

2. View unredacted data.

   CopyExpand

   SELECT * FROM raw_table;
   

   Show lessSee more

   Scroll to top

3. Create a redaction table.

   CopyExpand

   CREATE OR REPLACE TABLE redaction_table (value VARCHAR);
   

   Show lessSee more

   Scroll to top

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

   CopyExpand

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

   Show lessSee more

   Scroll to top

5. View redacted results.

   CopyExpand

   SELECT * FROM redaction_table;
   

   Show lessSee more

   Scroll to top

6. Run the AI_SENTIMENT function on redacted text.

   CopyExpand

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

   Show lessSee more

   Scroll to top

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.

   CopyExpand

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

   Show lessSee more

   Scroll to top

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

   CopyExpand

   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;
   

   Show lessSee more

   Scroll to top

3. Query the results.

   CopyExpand

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

   Show lessSee more

   Scroll to top

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.

   CopyExpand

   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
       );
   

   Show lessSee more

   Scroll to top

2. View the allowlist table

   CopyExpand

   SELECT * FROM string_list;
   

   Show lessSee more

   Scroll to top

   Allowlist table output:

   Expand

   VALUE
   Mike
   David
   

   Show lessSee more

   Scroll to top

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

   CopyExpand

   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
     $$;
   

   Show lessSee more

   Scroll to top

4. Test the UDF.

   CopyExpand

   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.'
     );
   

   Show lessSee more

   Scroll to top

5. Run AI_REDACT in detect mode.

   CopyExpand

   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;
   

   Show lessSee more

   Scroll to top

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"
    }
  ]
}