AI_TRANSLATE

AI_TRANSLATE

Overview

Translates text between supported languages using neural machine translation.

Syntax

AI_TRANSLATE(
  text,
  source_language,
  target_language
)

Parameters

• text (VARCHAR): Text to translate (max 4,096 tokens)
• source_language (VARCHAR): Source language code (e.g., 'en', 'es', 'fr')
• target_language (VARCHAR): Target language code

Supported Languages

• English (en)
• Spanish (es)
• French (fr)
• German (de)
• Italian (it)
• Portuguese (pt)
• Dutch (nl)
• Russian (ru)
• Chinese Simplified (zh)
• Japanese (ja)
• Korean (ko)
• Arabic (ar)
• And many more...

Use Cases

• Multilingual customer support
• International content localization
• Product description translation
• Review analysis across languages
• Global communication
• Documentation translation

Code Examples

Example 1: Simple Translation

SELECT AI_TRANSLATE(
    'Hello, how are you?',
    'en',
    'es'
) AS spanish_translation;

Output:

Hola, ¿cómo estás?

Example 2: Translate Customer Reviews

SELECT 
    review_id,
    original_language,
    review_text AS original_text,
    AI_TRANSLATE(review_text, original_language, 'en') AS english_translation
FROM international_reviews
WHERE original_language != 'en';

Example 3: Batch Translation for Localization

CREATE TABLE product_descriptions_translated AS
SELECT 
    product_id,
    description_en,
    AI_TRANSLATE(description_en, 'en', 'es') AS description_es,
    AI_TRANSLATE(description_en, 'en', 'fr') AS description_fr,
    AI_TRANSLATE(description_en, 'en', 'de') AS description_de,
    AI_TRANSLATE(description_en, 'en', 'zh') AS description_zh
FROM products;

Example 4: Analyze Multilingual Feedback

WITH translated_feedback AS (
    SELECT 
        feedback_id,
        customer_country,
        AI_TRANSLATE(feedback_text, detected_language, 'en') AS feedback_english
    FROM customer_feedback
    WHERE detected_language != 'en'
)
SELECT 
    customer_country,
    AI_SENTIMENT(feedback_english) AS avg_sentiment,
    AI_AGG(
        feedback_english,
        'Summarize common themes in customer feedback'
    ) AS themes
FROM translated_feedback
GROUP BY customer_country;

Example 5: Create Multilingual Support Responses

SELECT 
    ticket_id,
    customer_language,
    support_response_en,
    AI_TRANSLATE(support_response_en, 'en', customer_language) AS localized_response
FROM support_tickets
WHERE status = 'Awaiting Response';

Data Output Examples

English to Spanish

Input: "Thank you for your order. It will be delivered tomorrow."
Output: "Gracias por su pedido. Será entregado mañana."

French to English

Input: "Nous sommes ravis de vous servir."
Output: "We are delighted to serve you."

German to English

Input: "Ihre Bestellung wurde versandt."
Output: "Your order has been shipped."

Model Information

• Model Used: Snowflake managed translation model
• Context Window: 4,096 tokens
• Quality: Neural machine translation (high quality)

Limitations & Considerations

Input Size

• Maximum 4,096 tokens per translation
• Longer texts need to be split
• Use in batches for documents

Translation Quality

• Professional quality for common languages
• May vary for specialized terminology
• Context helps improve accuracy
• Idiomatic expressions may not translate literally

Cost

• Billing based on input tokens
• Automatic prompt overhead added
• Both source and target text count

Regional Availability

• AWS US West/East: ✓
• Azure East US: ✓
• EU regions: ✓
• Cross-region inference: ✓

Best Practices

1. Handle Language Detection

-- Use AI_CLASSIFY to detect language first
WITH detected_lang AS (
    SELECT 
        message_id,
        message_text,
        AI_CLASSIFY(
            message_text,
            ['en', 'es', 'fr', 'de', 'zh', 'ja']
        ) AS detected_language
    FROM messages
)
SELECT 
    message_id,
    AI_TRANSLATE(message_text, detected_language, 'en') AS english_text
FROM detected_lang
WHERE detected_language != 'en';

2. Preserve Formatting

-- Keep HTML/Markdown tags intact
SELECT 
    AI_TRANSLATE(
        '<h1>Welcome</h1><p>Thank you for visiting</p>',
        'en',
        'es'
    ) AS translated_html;
-- Output: <h1>Bienvenido</h1><p>Gracias por visitar</p>

3. Cache Translations

-- Avoid re-translating same content
CREATE TABLE translation_cache AS
SELECT DISTINCT
    original_text,
    source_lang,
    target_lang,
    AI_TRANSLATE(original_text, source_lang, target_lang) AS translation,
    CURRENT_TIMESTAMP() AS cached_at
FROM content_to_translate;

-- Use cached translations
SELECT 
    c.content_id,
    COALESCE(tc.translation, AI_TRANSLATE(c.text, 'en', 'es')) AS spanish_text
FROM content c
LEFT JOIN translation_cache tc
    ON c.text = tc.original_text
    AND tc.source_lang = 'en'
    AND tc.target_lang = 'es';

4. Split Long Documents

-- For documents > 4096 tokens, split into chunks
WITH text_chunks AS (
    SELECT 
        doc_id,
        SUBSTRING(doc_text, (n - 1) * 4000 + 1, 4000) AS chunk,
        n AS chunk_number
    FROM documents,
         TABLE(GENERATOR(ROWCOUNT => CEIL(LENGTH(doc_text) / 4000.0)))
)
SELECT 
    doc_id,
    LISTAGG(AI_TRANSLATE(chunk, 'en', 'fr'), '') 
        WITHIN GROUP (ORDER BY chunk_number) AS full_translation
FROM text_chunks
GROUP BY doc_id;

Common Use Cases

E-commerce Localization

CREATE TABLE product_catalog_global AS
SELECT 
    product_id,
    product_name_en,
    description_en,
    -- Translate to major markets
    AI_TRANSLATE(product_name_en, 'en', 'es') AS product_name_es,
    AI_TRANSLATE(description_en, 'en', 'es') AS description_es,
    AI_TRANSLATE(product_name_en, 'en', 'de') AS product_name_de,
    AI_TRANSLATE(description_en, 'en', 'de') AS description_de
FROM products;

Multilingual Customer Support

SELECT 
    ticket_id,
    customer_message_original,
    customer_language,
    AI_TRANSLATE(customer_message_original, customer_language, 'en') AS message_for_agent,
    support_response_en,
    AI_TRANSLATE(support_response_en, 'en', customer_language) AS response_to_customer
FROM support_conversations;

Global Sentiment Analysis

WITH translated_reviews AS (
    SELECT 
        region,
        product_id,
        AI_TRANSLATE(review_text, review_language, 'en') AS review_english
    FROM global_reviews
)
SELECT 
    region,
    product_id,
    AVG(AI_SENTIMENT(review_english)) AS avg_sentiment,
    COUNT(*) AS review_count
FROM translated_reviews
GROUP BY region, product_id;

Content Publishing

-- Publish blog posts in multiple languages
INSERT INTO published_articles
SELECT 
    article_id,
    'es' AS language,
    AI_TRANSLATE(title, 'en', 'es') AS title,
    AI_TRANSLATE(content, 'en', 'es') AS content,
    author_id,
    CURRENT_TIMESTAMP() AS published_at
FROM articles_english;

Quality Assurance

Back-Translation Check

-- Verify translation quality
WITH translation_test AS (
    SELECT 
        original_text,
        AI_TRANSLATE(original_text, 'en', 'es') AS spanish,
        AI_TRANSLATE(
            AI_TRANSLATE(original_text, 'en', 'es'),
            'es',
            'en'
        ) AS back_to_english
    FROM marketing_copy
)
SELECT 
    original_text,
    back_to_english,
    AI_SIMILARITY(original_text, back_to_english) AS quality_score
FROM translation_test
WHERE quality_score < 0.8;  -- Flag low-quality translations

Related Functions

• AI_CLASSIFY - Detect source language
• AI_SENTIMENT - Analyze translated content
• AI_COMPLETE - For context-aware translation
• AI_SUMMARIZE_AGG - Summarize across languages