Snowflake AI SQL User Guide: AI_REDACT

What is AI_REDACT?

AI_REDACT is a Snowflake Cortex AI function that detects and masks personally identifiable information (PII) in text. It automatically identifies names, emails, phone numbers, addresses, financial data, and more — replacing them with category markers like [NAME], [EMAIL], [PHONE_NUMBER]. No external services, no regex maintenance, no ML pipelines required.

Listen to a quick technical breakdown of the Snowflake AI_REDACT function. In this short 5-minute audio overview, we explore what the function does, how its parameters work, and when you would use it in real-world Snowflake workloads.

[embed]

What we’ll cover

1. Basic Redaction — Redact all detected PII from text with a single function call
2. Side-by-Side Comparison — View original and redacted text together
3. Selective Redaction — Redact only specific PII categories (e.g. emails and phone numbers)
4. Detect Mode — Identify PII spans without redacting, returned as JSON
5. Filtered Redaction — Redact only certain data categories (e.g. medical notes)
6. Dynamic Redaction Rules — Drive redaction categories from a lookup table using ARRAY_AGG and CTEs
7. Creating Safe Copies — Materialise redacted data and chain with other AI functions

Key takeaway: AI_REDACT supports two modes — redact (default) replaces PII with markers, and detect returns a JSON object with the PII spans. The optional categories array lets you control exactly which PII types are masked.

PII Categories Reference: For a complete list of supported PII categories (NAME, EMAIL, PHONE_NUMBER, SSN, ADDRESS, PAYMENT_CARD_DATA, PASSPORT, DATE_OF_BIRTH, etc.), see the official documentation: Snowflake AI_REDACT PII Categories

Setup

Before using AI_REDACT, we create a temporary table with sample text that contains various types of PII — names, emails, phone numbers, SSNs, addresses, passport numbers, and financial data.

Create Database and Schemas

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS DEMO_AI;
CREATE SCHEMA IF NOT EXISTS DEMO_AI.RAW;

Create Warehouse

CREATE WAREHOUSE IF NOT EXISTS SUPERHERO_GEN1_XS_WH
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;

Set Context

USE ROLE ACCOUNTADMIN;
USE DATABASE DEMO_AI;
USE SCHEMA RAW;
USE WAREHOUSE SUPERHERO_GEN1_XS_WH;

Create Temporary Table with Sample PII Data

CREATE OR REPLACE TEMPORARY TABLE DEMO_AI.RAW.REDACT_DEMO
(
    RECORD_ID INT,
    CATEGORY VARCHAR,
    RAW_TEXT VARCHAR
);

INSERT INTO DEMO_AI.RAW.REDACT_DEMO VALUES
(1, 'SUPPORT_TICKET',
'Hi, my name is John Smith and I need help with my account. My email is john.smith@example.com and you can reach me at 555-867-5309. My account number is AC-2024-78543.'),

(2, 'MEDICAL_NOTE',
'Patient Sarah Johnson, DOB 03/15/1985, SSN 123-45-6789, was seen today for a follow-up appointment. She reported improvement in her condition. Her insurance ID is BCBS-998877665. Contact her at sarah.j@healthmail.com or 212-555-0147.'),

(3, 'FINANCIAL_RECORD',
'Wire transfer of $50,000 initiated by Michael Chen (passport no. E12345678) from account 4532-1234-5678-9012 to IBAN GB29NWBK60161331926819. His home address is 742 Evergreen Terrace, Springfield, IL 62704.'),

(4, 'HR_NOTE',
'Employee review for Emily Rodriguez, employee ID EMP-44521. Emily has been with the company since January 2019. Her salary is $125,000. Emergency contact: David Rodriguez at 415-555-0198, david.rod@gmail.com. Home address: 1600 Pennsylvania Ave, Washington DC 20500.'),

(5, 'CUSTOMER_FEEDBACK',
'Great service from your team! My order #ORD-2024-99871 arrived on time. I paid with my Visa ending in 4242. Please send the receipt to jane.doe@company.org. My loyalty number is LY-554433. Thanks, Jane Doe.'),

(6, 'CHAT_LOG',
'Agent: Can you verify your identity? Customer: Sure, my name is Robert Williams, born July 4th 1990. My drivers license number is D1234567 and my phone is 303-555-0199. Agent: Thank you Robert, I have verified your identity.');

Verify Data

SELECT * FROM DEMO_AI.RAW.REDACT_DEMO;

1. Basic Redaction (Simplest Usage)

Basic redaction masks all detected PII with a single function call. No configuration needed — AI_REDACT automatically identifies and replaces all PII types.

How it works:

• Pass text as the only argument
• AI_REDACT scans for all known PII categories
• Each PII instance is replaced with a marker like [NAME], [EMAIL], [PHONE_NUMBER]
• The surrounding non-PII text is preserved intact

SELECT 
    AI_REDACT
    (
        'My name is John Smith and my email is john.smith@example.com. Call me at 555-867-5309.'
    ) AS REDACTED_TEXT;

Basic Redaction on a Table Column

SELECT 
    RECORD_ID,
    CATEGORY,
    AI_REDACT(RAW_TEXT) AS REDACTED_TEXT
FROM 
    DEMO_AI.RAW.REDACT_DEMO;

2. Side-by-Side Comparison (Original vs Redacted)

Viewing original and redacted text together is essential for validating that AI_REDACT is correctly identifying PII.

SELECT 
    RECORD_ID,
    CATEGORY,
    RAW_TEXT AS ORIGINAL_TEXT,
    AI_REDACT(RAW_TEXT) AS REDACTED_TEXT
FROM 
    DEMO_AI.RAW.REDACT_DEMO
WHERE 
    RECORD_ID = 1;

3. Selective Redaction (Specific PII Categories Only)

Pass an array of PII category names as the second argument to redact only those types. All other PII is left untouched.

How it works:

• Pass an array of category strings: ['EMAIL', 'PHONE_NUMBER']
• Only the specified PII types are redacted
• All other PII remains visible in the output
• Useful when you need to share data with teams that are authorised to see some PII but not all

Full list of categories: See Snowflake AI_REDACT PII Categories for every supported category name.

SELECT 
    AI_REDACT
    (
        'My name is John Smith and my email is john.smith@example.com. Call me at 555-867-5309.',
        ['EMAIL', 'PHONE_NUMBER']
    ) AS REDACTED_TEXT;

Selective Redaction on a Table Column

SELECT 
    RECORD_ID,
    AI_REDACT
    (
        RAW_TEXT,
        ['NAME', 'EMAIL', 'PHONE_NUMBER']
    ) AS REDACTED_TEXT
FROM 
    DEMO_AI.RAW.REDACT_DEMO;

4. Detect Mode (Identify PII Without Redacting)

Detect mode returns a JSON object describing where PII exists in the text — the category, the matched text, and the character positions — without actually redacting anything.

How it works:

• Pass mode => 'detect' as a named argument
• Returns a JSON object with a spans array
• Each span includes: category, text, start, and end positions
• Useful for auditing, reporting, and building custom redaction logic

SELECT 
    AI_REDACT
    (
        'My name is John Smith and my email is john.smith@example.com. Call me at 555-867-5309.',
        mode => 'detect'
    ) AS DETECTED_SPANS;

Flatten Detected Spans into Rows

Use LATERAL FLATTEN on the :spans array to get one row per detected PII item.

Key pattern:

• S.VALUE:category::VARCHAR — the PII type (NAME, EMAIL, etc.)
• S.VALUE:text::VARCHAR — the actual PII text found
• S.VALUE:start::INT / S.VALUE:end::INT — character positions

SELECT 
    S.VALUE:category::VARCHAR AS PII_CATEGORY,
    S.VALUE:text::VARCHAR AS PII_TEXT,
    S.VALUE:start::INT AS START_POS,
    S.VALUE:end::INT AS END_POS
FROM 
(
    SELECT 
        AI_REDACT
        (
            'Patient Sarah Johnson, DOB 03/15/1985, SSN 123-45-6789. Email: sarah.j@healthmail.com, phone 212-555-0147.',
            mode => 'detect'
        ) AS OUTPUT_JSON
),
LATERAL FLATTEN(INPUT => OUTPUT_JSON:spans) S;

5. Dynamic Redaction Rules from a Lookup Table

In production, different data categories require different PII to be redacted. Store the rules in a normalised lookup table and join dynamically at query time using ARRAY_AGG and a CTE.

How it works:

• Create a REDACT_RULES table mapping each data CATEGORY to the REDACT_CATEGORY values it needs
• Use ARRAY_AGG(REDACT_CATEGORY) grouped by CATEGORY to build the categories array
• JOIN the rules to the data table so each row gets its own category-specific redaction
• Adding or removing rules is a simple INSERT/DELETE — no query changes needed

Tip: Refer to the PII Categories documentation to choose the right category names for your rules table.

Create the Rules Lookup Table

CREATE OR REPLACE TEMPORARY TABLE DEMO_AI.RAW.REDACT_RULES
(
    CATEGORY VARCHAR,
    REDACT_CATEGORY VARCHAR
);

INSERT INTO DEMO_AI.RAW.REDACT_RULES VALUES
('SUPPORT_TICKET',    'PHONE_NUMBER'),
('MEDICAL_NOTE',      'NAME'),
('MEDICAL_NOTE',      'EMAIL'),
('MEDICAL_NOTE',      'PHONE_NUMBER'),
('MEDICAL_NOTE',      'DATE_OF_BIRTH'),
('FINANCIAL_RECORD',  'PAYMENT_CARD_DATA'),
('FINANCIAL_RECORD',  'ADDRESS'),
('FINANCIAL_RECORD',  'PASSPORT'),
('HR_NOTE',           'ADDRESS'),
('CUSTOMER_FEEDBACK', 'EMAIL'),
('CUSTOMER_FEEDBACK', 'PAYMENT_CARD_DATA'),
('CHAT_LOG',          'NAME'),
('CHAT_LOG',          'PHONE_NUMBER'),
('CHAT_LOG',          'DATE_OF_BIRTH');

View the Rules per Category

SELECT 
    CATEGORY,
    ARRAY_AGG(REDACT_CATEGORY) AS REDACT_CATEGORIES
FROM 
    DEMO_AI.RAW.REDACT_RULES
GROUP BY 
    CATEGORY;

Apply Dynamic Redaction Rules Using a CTE

WITH RULES AS
(
    SELECT 
        CATEGORY,
        ARRAY_AGG(REDACT_CATEGORY) AS REDACT_CATEGORIES
    FROM 
        DEMO_AI.RAW.REDACT_RULES
    GROUP BY 
        CATEGORY
)
SELECT 
    D.RECORD_ID,
    D.CATEGORY,
    AI_REDACT(D.RAW_TEXT, R.REDACT_CATEGORIES) AS REDACTED_TEXT
FROM 
    DEMO_AI.RAW.REDACT_DEMO D
JOIN 
    RULES R ON D.CATEGORY = R.CATEGORY;

6. Creating a Redacted Copy of the Data

Materialise the redacted output into a safe table that can be shared with analysts who should not see PII.

CREATE OR REPLACE TEMPORARY TABLE DEMO_AI.RAW.REDACT_DEMO_SAFE AS
WITH RULES AS
(
    SELECT 
        CATEGORY,
        ARRAY_AGG(REDACT_CATEGORY) AS REDACT_CATEGORIES
    FROM 
        DEMO_AI.RAW.REDACT_RULES
    GROUP BY 
        CATEGORY
)
SELECT 
    D.RECORD_ID,
    D.CATEGORY,
    AI_REDACT(D.RAW_TEXT, R.REDACT_CATEGORIES) AS REDACTED_TEXT
FROM 
    DEMO_AI.RAW.REDACT_DEMO D
JOIN 
    RULES R ON D.CATEGORY = R.CATEGORY;

SELECT * FROM DEMO_AI.RAW.REDACT_DEMO_SAFE;

Summary

PII Categories Reference: Category Reference

Try:

• Build a PII inventory using detect mode + LATERAL FLATTEN across your entire dataset
• Create dynamic redaction rules per data category using the lookup table pattern
• Chain AI_REDACT with AI_SENTIMENT and AI_CLASSIFY to analyse redacted text safely
• Use CTAS to materialise redacted copies for downstream analytics and BI tools

Next Steps

• Learn more on docs.snowflake.com
• Sign up for a Snowflake Trial

If you found this useful, follow me on LinkedIn for more Cortex AI SQL and Snowflake AI Data Cloud use cases.