External functions in Snowflake are a powerful feature that enables users to call external API endpoints from within SQL queries. These functions extend Snowflake's native capabilities by allowing integration with external services, custom business logic, or third-party applications hosted outside …External functions in Snowflake are a powerful feature that enables users to call external API endpoints from within SQL queries. These functions extend Snowflake's native capabilities by allowing integration with external services, custom business logic, or third-party applications hosted outside the Snowflake environment.
External functions work by sending data from Snowflake to a remote service via HTTPS and receiving processed results back. The architecture involves three main components: the external function definition in Snowflake, an API integration object, and the remote service itself (typically hosted on cloud platforms like AWS Lambda, Azure Functions, or Google Cloud Functions).
To create an external function, you must first establish an API integration, which defines the security and connection parameters for the external service. This integration specifies the allowed API endpoints, authentication methods, and role-based access controls. The external function definition then references this integration and maps input parameters to the remote service.
Key characteristics of external functions include:
1. **Scalar and Tabular Returns**: External functions can return scalar values or tabular data depending on implementation.
2. **Batching**: Snowflake automatically batches rows when calling external functions to optimize performance and reduce API calls.
3. **Security**: All communication occurs over HTTPS, and API integrations support various authentication mechanisms including API keys and OAuth.
4. **Synchronous Processing**: External function calls are synchronous, meaning the query waits for the response before proceeding.
5. **Cost Considerations**: Usage involves both Snowflake compute costs and potential charges from the external service provider.
Common use cases include machine learning model inference, data enrichment from external sources, custom transformations using programming languages not native to Snowflake, and integration with enterprise applications.
For the SnowPro Core exam, understand that external functions require proper privileges, API integrations must be configured by administrators, and they provide flexibility for complex data transformation scenarios that cannot be handled through standard SQL operations.
External Functions in Snowflake: Complete Guide
What Are External Functions?
External functions in Snowflake are user-defined functions (UDFs) that allow you to call code hosted outside of Snowflake. They enable you to extend Snowflake's capabilities by invoking remote services, APIs, or custom logic running on cloud platforms like AWS Lambda, Azure Functions, or Google Cloud Functions.
Why Are External Functions Important?
External functions are crucial because they:
• Extend Functionality: Allow integration with machine learning models, third-party APIs, and custom business logic • Enable Data Enrichment: Fetch additional data from external sources during query execution • Support Complex Processing: Offload computationally intensive operations to specialized services • Maintain Security: Keep sensitive algorithms and proprietary code outside Snowflake while still using them in queries
How External Functions Work
External functions operate through a specific architecture:
1. API Integration: First, you must create an API integration object that defines the connection to the remote service 2. Proxy Service: Requests go through a cloud provider's API gateway (AWS API Gateway, Azure API Management, or Google Cloud API Gateway) 3. Remote Service: The actual function code runs on the cloud provider's serverless compute platform 4. Data Flow: Snowflake sends data in batches as JSON payloads, receives responses, and incorporates results into query output
Creating External Functions - Key Steps
1. Create the remote service (Lambda function, Azure Function, etc.) 2. Set up the API gateway/proxy service 3. Create an API integration in Snowflake using CREATE API INTEGRATION 4. Create the external function using CREATE EXTERNAL FUNCTION 5. Grant appropriate privileges to users who need access
Key Syntax Components
API Integration: CREATE API INTEGRATION requires specifying: • api_provider (aws_api_gateway, azure_api_management, google_api_gateway) • api_allowed_prefixes (URLs the integration can access) • enabled = true/false
External Function: CREATE EXTERNAL FUNCTION requires: • Function name and parameters • RETURNS clause specifying return type • API_INTEGRATION reference • AS clause with the endpoint URL
Security Considerations
• External functions require ACCOUNTADMIN or appropriate privileges to create API integrations • The USAGE privilege on the API integration must be granted to function creators • Cloud provider IAM roles and policies must be properly configured • Trust relationships between Snowflake and the cloud provider are essential
Limitations to Remember
• External functions are scalar functions (return one value per input row) • They cannot be used in all contexts where regular UDFs work • Performance depends on network latency and remote service response time • Data is sent over the network, which has cost and security implications • Snowflake sends data in batches, not row-by-row
Exam Tips: Answering Questions on External Functions
1. Remember the Two-Step Process: Questions often test whether you know that both an API integration AND an external function must be created
2. Know the Required Privileges: ACCOUNTADMIN is needed for API integrations; USAGE on API integration is needed to create external functions
3. Understand the Architecture: Expect questions about the proxy service requirement - Snowflake does not connect to remote services on its own; it requires an API gateway
4. Scalar vs Table Functions: External functions are always scalar - if a question mentions returning multiple rows from a single call, it is likely not describing external functions correctly
5. Cloud Provider Components: Know which API gateway corresponds to which cloud (AWS API Gateway for AWS, Azure API Management for Azure, Google Cloud API Gateway for GCP)
6. Data Format: Remember that data is exchanged as JSON between Snowflake and the remote service
7. Use Cases: Common exam scenarios include ML model scoring, geocoding, sentiment analysis, and third-party API calls
8. Cost Awareness: Questions may reference that external functions incur additional costs from both Snowflake compute and the cloud provider's services