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
  │    └─ batches the input rows into JSON
  │
  ├─ Cloud Services Layer
  │    └─ calls API Gateway with the API Integration credentials
  │
  ├─ API Gateway (AWS API Gateway / Azure APIM / GCP API Gateway)
  │    └─ authentication, rate limiting, routing
  │
  └─ Backend (Lambda / Azure Functions / Cloud Functions)
       └─ runs the business logic -> returns JSON

Request / Response JSON Format

-- The request format Snowflake sends
{
  "data": [
    [0, "Hello world"],
    [1, "Snowflake is great"],
    [2, "External functions rock"]
  ]
}

-- The response format your backend must return
{
  "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 function (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]
        # Business logic: return the character count of the text
        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

-- Assumes the API Integration already exists
-- (see the API Integrations article for CREATE API INTEGRATION details)

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

-- A basic call
SELECT id, text, char_count(text) AS count
FROM documents
LIMIT 1000;

-- Call it conditionally with a CASE expression (cost optimization)
SELECT
  id,
  text,
  CASE
    WHEN needs_translation = TRUE
    THEN translate_text(text, 'ja')
    ELSE text
  END AS translated_text
FROM documents;

-- Persist the result
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