AI_REDACT

AI_REDACT

Overview

Redacts personally identifiable information (PII) from text, replacing sensitive data with placeholder tokens.

Syntax

AI_REDACT(text)

Parameters

• text (VARCHAR): Text containing potential PII to redact

PII Types Detected

• Email addresses
• Phone numbers
• Credit card numbers
• Social Security numbers
• Names (person names)
• Physical addresses
• Dates of birth
• IP addresses

Use Cases

• Data privacy compliance (GDPR, CCPA)
• Anonymize customer data for analysis
• Protect sensitive information in logs
• Secure data sharing
• Testing and development data preparation

Code Examples

Example 1: Basic PII Redaction

SELECT AI_REDACT(
    'Contact John Doe at john.doe@email.com or call 555-123-4567'
) AS redacted_text;

Output:

Contact [NAME] at [EMAIL] or call [PHONE]

Example 2: Redact Customer Support Tickets

SELECT 
    ticket_id,
    AI_REDACT(customer_message) AS redacted_message,
    ticket_category
FROM support_tickets
WHERE created_date >= CURRENT_DATE - 30;

Output:

ticket_id | redacted_message | ticket_category
----------|------------------|----------------
1001      | My email is [EMAIL] and I need help | Technical Support
1002      | Call me at [PHONE] regarding order [SSN] | Billing

Example 3: Create Anonymized Dataset

CREATE TABLE customer_feedback_anonymized AS
SELECT 
    feedback_id,
    AI_REDACT(feedback_text) AS anonymized_feedback,
    product_id,
    rating,
    feedback_date
FROM customer_feedback;

Example 4: Redact Before Sharing

WITH redacted_data AS (
    SELECT 
        order_id,
        AI_REDACT(customer_notes) AS safe_notes,
        order_total,
        order_date
    FROM orders
)
SELECT *
FROM redacted_data
WHERE order_date >= '2024-01-01';

Example 5: Conditional Redaction

SELECT 
    user_id,
    CASE 
        WHEN user_consent = false THEN AI_REDACT(user_comments)
        ELSE user_comments
    END AS comments
FROM user_activity;

Data Output Examples

Email and Phone Redaction

Input: "Please contact me at jane.smith@company.com or 555-987-6543"
Output: "Please contact me at [EMAIL] or [PHONE]"

Credit Card Redaction

Input: "My card number is 4532-1234-5678-9010"
Output: "My card number is [CREDIT_CARD]"

Multiple PII Types

Input: "John Doe, SSN: 123-45-6789, lives at 123 Main St, Anytown, CA 90210"
Output: "[NAME], SSN: [SSN], lives at [ADDRESS]"

Model Information

• Model Used: Snowflake managed PII detection model
• Context Window: 4,096 tokens
• Output: Text with PII replaced by placeholders

Limitations & Considerations

Detection Accuracy

• May not catch all PII variations
• Context-dependent detection
• False positives possible
• Manual review recommended for critical data

Input Size

• Maximum 4,096 tokens
• Longer text will be truncated
• Use in batches for large documents

Cost

• Billing based on input tokens
• Automatic prompt overhead added
• Consider caching results

Regional Availability

• AWS US West/East: ✓
• AWS US East (Commercial Gov): ✓
• Azure East US: ✓
• EU regions: ✓

Best Practices

1. Validate Redaction Results

-- Check that sensitive data was redacted
SELECT 
    original_text,
    AI_REDACT(original_text) AS redacted,
    CASE 
        WHEN AI_REDACT(original_text) LIKE '%@%.%' THEN 'Email not redacted'
        WHEN AI_REDACT(original_text) REGEXP '[0-9]{3}-[0-9]{2}-[0-9]{4}' THEN 'SSN not redacted'
        ELSE 'OK'
    END AS validation
FROM sensitive_data
WHERE validation != 'OK';

2. Combine with Access Controls

-- Create view with automatic redaction for non-privileged users
CREATE VIEW customer_data_safe AS
SELECT 
    customer_id,
    AI_REDACT(contact_info) AS contact_info,
    purchase_history
FROM customers;

GRANT SELECT ON customer_data_safe TO analyst_role;

3. Preserve Data for Analysis

-- Redact but keep metadata for analysis
SELECT 
    ticket_id,
    AI_REDACT(description) AS safe_description,
    LENGTH(description) AS original_length,
    REGEXP_COUNT(description, '@') AS email_mentions,
    created_date
FROM support_tickets;

4. Document Redaction Policy

-- Tag redacted data
CREATE TABLE redacted_logs AS
SELECT 
    log_id,
    AI_REDACT(log_message) AS redacted_message,
    'PII_REDACTED' AS data_classification,
    CURRENT_TIMESTAMP() AS redacted_at,
    CURRENT_USER() AS redacted_by
FROM application_logs;

Common Use Cases

GDPR Compliance

-- Anonymize customer data for analytics
CREATE TABLE customer_analytics_gdpr AS
SELECT 
    customer_segment,
    product_category,
    AI_REDACT(feedback_text) AS anonymous_feedback,
    sentiment_score
FROM customer_feedback;

Test Data Generation

-- Create safe test data from production
CREATE TABLE orders_test_data AS
SELECT 
    order_id,
    AI_REDACT(shipping_address) AS test_address,
    AI_REDACT(customer_notes) AS test_notes,
    order_total
FROM orders_production
LIMIT 1000;

Log Sanitization

-- Sanitize application logs before analysis
SELECT 
    timestamp,
    log_level,
    AI_REDACT(message) AS safe_message,
    application_name
FROM application_logs
WHERE log_level = 'ERROR'
  AND timestamp >= CURRENT_DATE - 7;

Data Sharing

-- Prepare data for external partners
CREATE TABLE partner_share AS
SELECT 
    transaction_id,
    transaction_date,
    AI_REDACT(customer_details) AS anonymized_customer,
    product_sku,
    quantity
FROM transactions
WHERE share_approved = true;

Advanced Patterns

Selective Redaction

-- Redact only specific fields
SELECT 
    customer_id,
    customer_name,  -- Keep name
    AI_REDACT(email) AS email,  -- Redact email
    AI_REDACT(phone) AS phone,  -- Redact phone
    city,  -- Keep city
    AI_REDACT(street_address) AS street_address  -- Redact address
FROM customers;

Audit Trail

CREATE TABLE data_access_log AS
SELECT 
    CURRENT_USER() AS accessed_by,
    CURRENT_TIMESTAMP() AS accessed_at,
    'customer_pii' AS table_name,
    customer_id,
    AI_REDACT(sensitive_field) AS redacted_value
FROM customer_pii
WHERE CURRENT_USER() NOT IN (SELECT user FROM privileged_users);

Related Functions

• AI_FILTER - Filter based on sensitive content
• AI_CLASSIFY - Classify data sensitivity
• AI_EXTRACT - Extract non-PII information