External Functions
Sometimes, the logic you need to apply to your data cannot be written in SQL, Python, or Java inside Snowflake. Perhaps you need to call a proprietary 3rd-party API to calculate shipping taxes, or invoke an existing Machine Learning model hosted in AWS SageMaker to score customer risk.
To solve this, Snowflake provides External Functions. These allow your SQL queries to securely reach out to external REST APIs, process data, and return the results directly into your query output.
Topics Covered
- How External Functions Work
- Setting up the API Integration
- Creating an External Function
- Use Cases (Geocoding, ML Inference)
How External Functions Work
An External Function does not execute code inside Snowflake's Virtual Warehouse. Instead, it delegates the work to a remote service.
The Architecture:
- The Query: A user writes a SQL query in Snowflake containing the external function:
SELECT my_external_func(name) FROM customers; - The Payload: Snowflake takes the data from the
customerstable, batches it into a JSON payload, and sends an HTTPS POST request. - The Proxy (API Gateway): Snowflake never talks directly to your code. It talks to an API Gateway (e.g., AWS API Gateway, Azure API Management).
- The Remote Service: The API Gateway triggers a serverless function (e.g., AWS Lambda) which executes your custom logic (like calling a 3rd party API) and returns the modified JSON back to Snowflake.
Hands-On: Setting up an External Function
Let's assume an e-commerce company in Chennai wants to use a proprietary AWS Lambda function to translate customer reviews from Tamil to English on the fly during a query.
Step 1: The API Integration
Before Snowflake can send data outside its secure perimeter, an Account Administrator must create an API Integration. This object holds the trust relationship between Snowflake and the Cloud Provider's API Gateway.
(To create an integration, you must use the ACCOUNTADMIN role. Verify this by running SELECT CURRENT_ROLE();)
USE ROLE ACCOUNTADMIN;
-- 1. Create the API Integration pointing to AWS API Gateway
CREATE OR REPLACE API INTEGRATION TRANSLATION_API_INT
API_PROVIDER = AWS_API_GATEWAY
API_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_translation_role'
API_ALLOWED_PREFIXES = ('https://xyz123.execute-api.ap-south-1.amazonaws.com/prod/')
ENABLED = TRUE;
-- 2. Grant usage to the Data Engineering role
GRANT USAGE ON INTEGRATION TRANSLATION_API_INT TO ROLE SYSADMIN;
Step 2: Creating the External Function
Once the integration is created, the Data Engineer creates the actual function that end-users will call in their SQL queries.
USE ROLE SYSADMIN;
CREATE OR REPLACE EXTERNAL FUNCTION TRANSLATE_TAMIL_TO_ENGLISH(text STRING)
RETURNS STRING
API_INTEGRATION = TRANSLATION_API_INT
AS 'https://xyz123.execute-api.ap-south-1.amazonaws.com/prod/translate';
Step 3: Using the External Function
To the end-user or BI analyst, the external function looks and behaves exactly like a native Snowflake SQL function.
-- Query the raw customer reviews table
CREATE OR REPLACE TABLE RAW_REVIEWS (REVIEW_ID INT, CUSTOMER_NAME STRING, TAMIL_REVIEW STRING);
INSERT INTO RAW_REVIEWS VALUES
(1, 'Arjun', 'பொருளின் தரம் மிகச் சிறந்தது'),
(2, 'Priya', 'டெலிவரி தாமதமானது');
-- Call the external API during a SELECT query
SELECT
REVIEW_ID,
CUSTOMER_NAME,
TAMIL_REVIEW,
TRANSLATE_TAMIL_TO_ENGLISH(TAMIL_REVIEW) AS ENGLISH_TRANSLATION
FROM RAW_REVIEWS;
Behind the scenes, Snowflake batches the Tamil strings, sends them to AWS Lambda via the API Gateway, and projects the English translations back into the SQL result set.
Common Use Cases
Because external functions require network hops, they introduce latency. You should not use them for basic math or string manipulation. Use them when you must access external systems.
- Geocoding: Converting raw addresses ("MG Road, Bangalore") into Latitude/Longitude coordinates using the Google Maps API.
- Machine Learning Inference: Sending customer features to a hosted ML model to retrieve a real-time "Fraud Probability" score.
- Data Enrichment: Sending a company domain name (e.g.,
beems.tech) to the Clearbit API to retrieve the company's industry, revenue size, and headcount. - Custom Tokenization/Encryption: Sending sensitive PII to an external enterprise tokenization server (like Protegrity or HashiCorp Vault).
Performance and Security Considerations
- Latency: Every call introduces network latency. Snowflake mitigates this by batching rows into arrays. Ensure your external API (e.g., Lambda) is designed to accept an array of rows, not just a single value.
- Timeouts: If the external API takes longer than 10 minutes to respond, Snowflake will fail the query.
- Authentication: Snowflake secures the connection by assuming an IAM role (AWS) or using Azure AD tokens. Never put API keys in the URL.
Summary
- External Functions allow Snowflake SQL to call external APIs via Cloud API Gateways (AWS/Azure/GCP).
- You must create an
API INTEGRATIONto establish a secure trust relationship. - They are ideal for Data Enrichment, Geocoding, and ML Inference.
- Be mindful of network latency; ensure the remote service can handle batched requests.