Snowflake External Functions: How to Call External APIs and Build the Integration

External Functions are scalar functions that let Snowflake SQL queries call external REST APIs through a cloud provider's API Gateway. By placing AWS Lambda, Azure Functions, or Google Cloud Functions on the backend, you can complete ML inference, data enrichment, and external service integrations entirely within SQL.

External Function Data Flow

SELECT external_func(col) FROM table
  │
  ├─ Snowflake Virtual Warehouse
  │    └─ 入力行をバッチ化してJSON形式に変換
  │
  ├─ Cloud Services Layer
  │    └─ API Integrationの認証情報でAPI Gatewayへリクエスト
  │
  ├─ API Gateway (AWS API Gateway / Azure APIM / GCP API Gateway)
  │    └─ 認証・レート制限・ルーティング
  │
  └─ Backend (Lambda / Azure Functions / Cloud Functions)
       └─ ビジネスロジック実行 → JSON形式でレスポンスを返却

Request / Response JSON Format

-- Snowflakeが送信するリクエスト形式
{
  "data": [
    [0, "Hello world"],
    [1, "Snowflake is great"],
    [2, "External functions rock"]
  ]
}

-- バックエンドが返すレスポンス形式
{
  "data": [
    [0, "positive"],
    [1, "positive"],
    [2, "positive"]
  ]
}

Each row is an array of [row_number, value...]. Snowflake assigns the row number automatically, and the response must return the same row numbers. Backend code parses this format and returns results in the same shape.

AWS Lambda Backend Implementation Example

# AWS Lambda関数 (Python)
import json

def lambda_handler(event, context):
    body = json.loads(event['body'])
    rows = body['data']

    results = []
    for row in rows:
        row_number = row[0]
        input_text = row[1]
        # ビジネスロジック: テキストの文字数を返す
        char_count = len(input_text) if input_text else 0
        results.append([row_number, char_count])

    return {
        'statusCode': 200,
        'body': json.dumps({'data': results})
    }

AWS: Creating an External Function

-- 前提: API Integrationが作成済み
-- (CREATE API INTEGRATIONの詳細はAPI Integrationsの記事を参照)

CREATE OR REPLACE EXTERNAL FUNCTION char_count(input_text VARCHAR)
  RETURNS INT
  API_INTEGRATION = aws_ml_api
  MAX_BATCH_ROWS = 500
  AS 'https://abc123.execute-api.us-east-1.amazonaws.com/prod/char-count';

Azure: Creating an External Function

CREATE OR REPLACE EXTERNAL FUNCTION translate_text(text VARCHAR, target_lang VARCHAR)
  RETURNS VARCHAR
  API_INTEGRATION = azure_translate_api
  AS 'https://my-apim.azure-api.net/translate/v1';

GCP: Creating an External Function

CREATE OR REPLACE EXTERNAL FUNCTION geocode_address(address VARCHAR)
  RETURNS VARIANT
  API_INTEGRATION = gcp_geocode_api
  AS 'https://my-gateway-abc123.uc.gateway.dev/geocode';

Components by Cloud Provider

Calling Patterns

-- 基本的な呼び出し
SELECT id, text, char_count(text) AS count
FROM documents
LIMIT 1000;

-- CASE式と組み合わせて条件付き呼び出し（コスト最適化）
SELECT
  id,
  text,
  CASE
    WHEN needs_translation = TRUE
    THEN translate_text(text, 'ja')
    ELSE text
  END AS translated_text
FROM documents;

-- 結果を永続化
CREATE TABLE enriched_documents AS
SELECT
  d.*,
  geocode_address(d.address) AS geo_result,
  geo_result:lat::FLOAT AS latitude,
  geo_result:lng::FLOAT AS longitude
FROM documents d
WHERE d.address IS NOT NULL;

Performance Tuning

Security Considerations

• Restrict destinations with the API Integration: minimize the URLs allowed via API_ALLOWED_PREFIXES and add excluded paths through API_BLOCKED_PREFIXES.
• Control access with USAGE privileges: GRANT USAGE on the External Function only to specific roles to prevent unauthorized use.
• Configure authentication on API Gateway too: in addition to Snowflake authentication, configure API key validation and throttling on API Gateway.
• Validate input on the backend: validate input values inside Lambda/Cloud Functions to prevent injection attacks.

Limitations

• External Functions can be created only as scalar functions (table functions are not supported).
• Only the HTTPS protocol is supported (HTTP is not).
• Return types are limited to scalar types such as VARCHAR, INT, FLOAT, BOOLEAN, and VARIANT.
• Calling an External Function requires a Virtual Warehouse (credit consumption applies).
• You can specify IMMUTABLE / VOLATILE / STABLE volatility, but this affects caching behavior.

Check Your Understanding

Frequently Asked Questions