CertSafari
    Snowflake SnowPro Advanced: Data Engineer (DEA-C02)· Lessons

    Domain 5 · Lesson 17/22

    Snowflake external functions and API integrations

    Define and create external functions.

    20 min read
    3.57% of exam
    8 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Trace an external function call from SQL through the proxy service to the remote service and back
    • Write a CREATE EXTERNAL FUNCTION statement and choose its optional clauses, including SECURE, HEADERS, CONTEXT_HEADERS and MAX_BATCH_ROWS
    • Describe the batched JSON request and response a remote service must handle, including row numbers, status codes and retries
    • List where an external function cannot be used
    • Secure an API integration with a trust policy, API_ALLOWED_PREFIXES and an optional API key
    • Grant the minimum privileges needed to create and call an external function

    Key concept

    External function — An external function is a UDF that holds no code of its own. It is a database object that tells Snowflake which proxy URL to call and which API integration to authenticate with, and the actual logic runs in a remote service outside Snowflake.

    1.The four pieces of an external function

    An external function lets a SQL query call code that Snowflake does not run itself: a geocoder, a machine-learning scoring model, an address validator. The function is defined in SQL, but the work happens somewhere else, so data never has to be exported, processed and reloaded. It's useful when the logic needs a language or library that an internal UDF can't use. Remote services can be written in languages such as Go or C#, and they can call commercial third-party libraries.

    Four objects work together on every call. Knowing which one owns which job is the basis for every security and troubleshooting question about external functions.

    The components of an external function call
    ComponentWhere it livesWhat it does
    External functionA database and schema in SnowflakeStores the proxy URL and the API integration name; called like any UDF, e.g. my_database.my_schema.my_external_function(col1)
    API integrationSnowflake account-level object, created with CREATE API INTEGRATIONStores security information Snowflake needs to authenticate to the proxy service
    Proxy serviceCloud provider, e.g. Amazon API Gateway or Microsoft Azure API Management serviceReceives Snowflake's request, can authenticate callers or enforce subscriptions, relays to the remote service and back
    Remote serviceOutside Snowflake, e.g. an AWS Lambda function, an Azure Function, or an HTTPS server on EC2Runs the code; must accept JSON, return JSON, and expose an HTTPS endpoint

    At query time, Snowflake reads the external function definition and the API integration it names. From the function it gets the proxy URL. From the integration it gets the proxy resource and the authentication details. Snowflake then sends an HTTP POST with the rows as JSON, its own headers, and the authentication from the integration. The proxy forwards the request to the remote service, and the result goes back the same way to the SQL statement. If the remote service replies that it is still working, Snowflake polls it with GET requests until it gets a result, an error, or a timeout.

    To a SQL user, an external function looks like any other UDF. It returns a value, accepts parameters, can be overloaded by signature, and can appear in a SELECT list, a WHERE clause, a view definition or a nested expression such as upper(zipcode_to_city_external_function(zipcode)). The return value can be compound, for example a VARIANT holding JSON. Two things set it apart. It must be scalar, returning exactly one row for each row it receives. And because the optimizer can't see inside the remote service, it carries more overhead and usually runs more slowly than built-in functions or internal UDFs.

    Checkpoint 1 of 9· Match them up

    Match each part of an external function call to the job it does.

    Tap a term, then the definition that fits it.

    Sources1

    2.Writing CREATE EXTERNAL FUNCTION

    Only two clauses do the essential wiring: API_INTEGRATION, which names the integration used to authenticate, and AS, which gives the invocation URL of the proxy and resource. The other clauses adjust how requests are built and how the function behaves. Use the CREATE OR ALTER variant to change an existing function's integration, headers, batch limit, translators or SECURE flag without dropping it.

    Full CREATE EXTERNAL FUNCTION syntaxsql
    CREATE [ OR REPLACE ] [ SECURE ] EXTERNAL FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
      RETURNS <result_data_type>
      [ [ NOT ] NULL ]
      [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
      [ { VOLATILE | IMMUTABLE } ]
      [ COMMENT = '<string_literal>' ]
      API_INTEGRATION = <api_integration_name>
      [ HEADERS = ( '<header_1>' = '<value_1>' [ , '<header_2>' = '<value_2>' ... ] ) ]
      [ CONTEXT_HEADERS = ( <context_function_1> [ , <context_function_2> ...] ) ]
      [ MAX_BATCH_ROWS = <integer> ]
      [ COMPRESSION = <compression_type> ]
      [ REQUEST_TRANSLATOR = <request_translator_udf_name> ]
      [ RESPONSE_TRANSLATOR = <response_translator_udf_name> ]
      AS '<url_of_proxy_and_resource>';
    Optional clauses and what they control
    ClauseEffectDefault or limit
    CALLED ON NULL INPUT / RETURNS NULL ON NULL INPUT (STRICT)Whether the remote service is called at all when an argument is nullCALLED ON NULL INPUT
    VOLATILE / IMMUTABLEDeclares whether the same input always gives the same output; Snowflake does not verify IMMUTABLEVOLATILE; set it explicitly
    HEADERSConstant key-value strings sent on every request, prefixed sf-custom-; the caller cannot change themHeader names and values total at most 8 KB; no underscores in names
    CONTEXT_HEADERSSends results of context functions such as CURRENT_ROLE() or CURRENT_USER() as sf-context- headers, plus base64 copiesOnly listed context functions are supported
    MAX_BATCH_ROWSUpper limit on rows per batch sent to the proxyUnset: Snowflake estimates the batch size
    SECUREHides the URL, headers and context headers from non-ownersOff

    HEADERS and CONTEXT_HEADERS meet different needs. A HEADERS entry such as 'volume-measure' = 'liters' arrives at the remote service as sf-custom-volume-measure, and its value must be a constant string. CONTEXT_HEADERS = (current_timestamp) arrives as sf-context-current-timestamp, with its value filled in per query. Context function results can contain characters that are illegal in HTTP headers. Snowflake replaces each run of them with one space and also sends a -base64 header carrying the original value. A remote service that bills or audits by Snowflake user or role can read those context headers. A service that needs a fixed unit or API version should use HEADERS.

    Checkpoint 2 of 9· Check yourself

    A third-party scoring service bills each customer by the Snowflake role that ran the query, so every request has to say which role called the function. Which clause should the function author add to CREATE EXTERNAL FUNCTION?

    Sources2

    3.The batched JSON request and response

    Snowflake doesn't send one HTTP request per row. It groups rows into batches, which allows more parallelism and can keep the remote service from being overloaded. Each POST body is a JSON object with a single key, data. Its value is an array of rows, and each row is an array whose first element is the row's 0-based index within the batch, followed by the function's arguments. Numbers, booleans and strings keep their JSON types, dates, timestamps and binary values become strings, and NULL becomes JSON null.

    Request body for f(integer, varchar, timestamp): row number first, then the three argumentsjson
    { "data": [ [0, 10, "Alex", "2014-01-01 16:00:00"], [1, 20, "Steve", "2015-01-01 16:00:00"], [2, 30, "Alice", "2016-01-01 16:00:00"], [3, 40, "Adrian", "2017-01-01 16:00:00"] ] }

    The response has the same shape: one row back for every row received, each row being the row number and exactly one value. That value can be an OBJECT, which SQL then reads with path notation such as val:city. Row numbers must match the request and come back in the same order, because Snowflake uses them to line results up with input rows. A remote service that drops a row or reorders a batch breaks the query.

    Response body: each row number paired with one OBJECT valuejson
    {
        "data":
            [
                [ 0, { "City" : "Warsaw",  "latitude" : 52.23, "longitude" :  21.01 } ],
                [ 1, { "City" : "Toronto", "latitude" : 43.65, "longitude" : -79.38 } ]
            ]
    }

    Status codes are simple. A 200 means the batch is done. A 202 means it was received and is still processing, so Snowflake polls with GET requests that carry the same batch ID as the original POST. Anything else is an error. The response can also include a base64 Content-MD5 header, which Snowflake checks against the body. A mismatch fails the query, so this header protects integrity, not identity. Each batch can return at most 10MB.

    Retries make statelessness a hard requirement. If a response is lost to a transient network error, or the service returns 429 or a 5XX code, Snowflake sends the batch again until a retry timeout that users cannot configure. The remote service can therefore see the same row twice. A service that assigns sequential IDs ends up with gaps. Because a retried batch reuses its batch ID in the sf-external-function-query-batch-id header, the service can use that ID as an idempotency token. If calls time out while every hop looks healthy, lower MAX_BATCH_ROWS to cap the batch size. Otherwise leave it unset: it is a ceiling for memory-limited services, not a speed setting.

    Checkpoint 3 of 9· Check yourself

    An external function's remote service gives each row it processes a sequential ID. After a few runs, the logs show some rows processed twice and gaps in the ID sequence. No query failed. What is the best way to handle this?

    Sources341

    4.Where external functions cannot be used

    The exclusions fall into a small, specific list:

    • Any object shared through Secure Data Sharing: a view, table, UDF or masking policy that calls an external function cannot be granted to a share, and the external function itself cannot be shared either.
    • A DEFAULT clause of CREATE TABLE: the statement fails if a column default calls an external function.
    • A COPY transformation: an external function cannot be used while loading.
    • Stored procedures: the feature creates functions only, and scalar ones at that.

    The sharing rule catches teams that build a view for internal use and later try to add it to a share. The view works in the provider account, but granting it to a share isn't supported. To share that data, pre-compute the external function's output into a table and share the table. Setting a function up also takes cloud-side work before the first call. An administrator needs to know the platform's security model, and on AWS the proxy needs a regional or private endpoint.

    Checkpoint 4 of 9· Check yourself

    Your team built a view that calls an external function to add geocodes to customer addresses. Analysts in your account query it with no problems. A partner account now wants the same enriched data through Secure Data Sharing. What should the team do?

    Sources1

    5.Securing the API integration and the proxy

    The API integration is how Snowflake authenticates to the proxy without anyone storing a password in a function definition. It is credential-less. An administrator creates a trust relationship between Snowflake and the cloud provider using the provider's own identity mechanism, and the cloud provider checks each connection against it. This separates the roles: administrators control what an integration may reach, and function authors only reference the integration.

    An AWS API integration: the IAM role to assume and the URL prefixes functions may usesql
    create or replace api integration demonstration_external_api_integration_01 api_provider=aws_api_gateway api_aws_role_arn='arn:aws:iam::123456789012:role/my_cloud_account_role' api_allowed_prefixes=('https://xyz.execute-api.us-west-2.amazonaws.com/production/') enabled=true;

    On AWS the trust works in both directions. The integration names an IAM role in your account through api_aws_role_arn. Snowflake then authenticates through a Snowflake-owned IAM user, which must be allowed to assume that role. After creating the integration, run DESCRIBE INTEGRATION and record two values: API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID. The external ID often ends in =, and that character belongs to the value. Both values then go into the IAM role's trust policy. The user ARN becomes the principal, and the external ID becomes an sts:ExternalId condition. A caller who learns the role's ARN still can't assume it without being that principal and presenting that external ID. Gateway throttling or response checksums don't control who can assume the role; the trust policy does.

    IAM role trust policy limited to the integration's IAM user and external IDjson
    { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::1234567898012:user/development/development_user" }, "Action": "sts:AssumeRole", "Condition": {"StringEquals": { "sts:ExternalId": "EXTERNAL_FUNCTIONS_SFCRole=3_8Hcmbi9halFOkt+MdilPi7rdgOv=" }} } ] }

    API_ALLOWED_PREFIXES controls where the integration's credentials can be used. Each listed URL is a prefix, and any external function that references the integration must point its AS URL under one of them. When one integration serves several teams, setting it to the approved gateway path, such as https://xyz.execute-api.us-west-2.amazonaws.com/production/, stops a team from pointing a new function at an unapproved endpoint. A bare 'https://' allows any HTTPS endpoint. MAX_BATCH_ROWS, COMPRESSION and CONTEXT_HEADERS can't do this job, because they are clauses of CREATE EXTERNAL FUNCTION, not properties of the integration.

    API keys add a second check that the gateway can use. Some proxy services expect a subscription key to confirm a paying customer or enforce quotas. The integration's optional API_KEY clause, set in CREATE or ALTER API INTEGRATION, sends one. The service developer chooses the key's format, and Snowflake doesn't validate it. Snowflake does keep it out of query history and DESCRIBE INTEGRATION output. The key adds to IAM and doesn't replace it.

    The remaining layers belong to the proxy and the remote service. Traffic between Snowflake and the proxy uses HTTPS, and on AWS every request to API Gateway is signed with sigv4. Restrict the gateway with a resource policy and set the POST method to require AWS_IAM authorization. If you wrote the remote service yourself, secure it as well and serve it over HTTPS.

    Checkpoint 5 of 9· Match them up

    An administrator is securing an AWS API integration that several teams share. Which security layer does each job?

    Tap a term, then the definition that fits it.

    Checkpoint 6 of 9· Exam question

    A security reviewer is auditing an AWS-hosted external function. The Lambda proxy is fronted by an IAM role, and the reviewer wants to be certain that only this specific Snowflake account's integration can assume that role, not any other AWS API Gateway caller who discovers the role's ARN. Which mechanism enforces that requirement?

    Checkpoint 7 of 9· Exam question

    A platform team manages a single API integration that several teams will use to create external functions. To prevent one team from pointing a new external function at an unapproved endpoint while still sharing the same integration's credentials, what should the platform team configure on the integration object?

    Sources5678

    6.Privileges to create and call, and SECURE external functions

    External function privileges have three levels, and each level needs only its own grant:

    • Creating an API integration requires ACCOUNTADMIN or a role with the account-level CREATE INTEGRATION privilege. Account administrators can grant and revoke ownership and usage on each integration.
    • Creating an external function requires USAGE on the API integration it binds to, plus the normal rights to create a function in the target schema.
    • Calling the function requires the privileges the owner grants, just as for any UDF.

    Suppose ACCOUNTADMIN created address_api_integration and an analyst role's CREATE EXTERNAL FUNCTION fails with insufficient privileges. The minimal fix is USAGE on that one integration. Granting OWNERSHIP would also let the analyst change the integration's role ARN and allowed prefixes. Granting CREATE INTEGRATION would let them create new integrations to any endpoint. Neither is needed just to bind a function. Callers also need explicit grants: future grants on external functions aren't supported, so a role cannot be pre-authorized on functions that don't exist yet. Grant privileges on each new function after it is created.

    The SECURE keyword hides the plumbing from callers. A secure UDF keeps its definition from non-owners (Secure UDFs). For an external function, CREATE SECURE EXTERNAL FUNCTION hides the proxy URL, the HEADERS, the context headers and the binary context headers from every user who isn't the owner. That makes sense when headers carry a tenant identifier or the URL shows internal gateway layout. Callers can still use the function, but they can't see where it sends their data.

    Checkpoint 8 of 9· Check yourself

    Last month an administrator ran a future grant so that the REPORTING role would get privileges on every new function created in the ANALYTICS schema. Today a new external function in that schema fails for REPORTING with an insufficient-privileges error. What should the function owner do?

    Checkpoint 9 of 9· Exam question

    A data engineer used ACCOUNTADMIN to create an API integration named address_api_integration that points to a third-party address validation service. An analyst role now needs to run CREATE EXTERNAL FUNCTION bound to that integration, but the statement fails with an insufficient privileges error. What should the engineer grant to let the analyst role create the function without over-provisioning access?

    Sources52

    Practise it for real

    Link a Snowflake API integration to an AWS IAM role so that only your account's integration can assume it

    1. 1.With a role that has ACCOUNTADMIN or CREATE INTEGRATION, run CREATE API INTEGRATION with api_provider=aws_api_gateway, your IAM role's ARN in api_aws_role_arn, and api_allowed_prefixes set to your gateway's resource invocation URL.

      Why: The integration names the role to assume and limits which proxy URLs functions may call.

      You should see: The integration is created and enabled.

    2. 2.Run DESCRIBE INTEGRATION on it and record API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID, including any trailing '='.

      Why: These values identify your Snowflake account to AWS.

      You should see: Two property values copied into your worksheet.

    3. 3.In the AWS IAM console, edit the role's trust relationship: set Statement.Principal.AWS to the user ARN and add a StringEquals condition on sts:ExternalId with the external ID.

      Why: This lets only your integration assume the role.

      You should see: A trust policy shaped like the one in this lesson, saved with Update Trust Policy.

    4. 4.Run CREATE EXTERNAL FUNCTION with API_INTEGRATION set to the integration and AS set to a URL under the allowed prefix, then grant USAGE on the function to the calling role.

      Why: Binding the function needs USAGE on the integration, and callers need their own grant.

      You should see: The calling role can run SELECT with the function and gets one result per input row.

    Stuck? Get a nudge

    If the call fails with a permissions error from AWS, compare the external ID in the trust policy with the value from DESCRIBE INTEGRATION character by character, including the trailing '='.

    Sources

    (8)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “An API integration stores information, such as security information, that is needed to work with a proxy service or remote service.”
      ↩︎ The four pieces of an external function
      “External functions have more overhead than functions (both built-in functions and internal UDFs) and usually execute more slowly.”
      ↩︎ The four pieces of an external function
      “The maximum response size per batch is 10MB.”
      ↩︎ The batched JSON request and response
      “A DEFAULT clause of a CREATE TABLE statement.”
      ↩︎ Where external functions cannot be used
      “Currently, external functions cannot be shared with data consumers via Secure Data Sharing.”
      ↩︎ Where external functions cannot be used
      “Only functions, not stored procedures, can be written using the external functions feature.”
      ↩︎ Where external functions cannot be used
      “Snowflake does not call a remote service directly. Instead, Snowflake calls a proxy service, which relays the data to the remote service.”
      ↩︎ Key concept
      “Future grants of privileges on external functions are not supported.”
      ↩︎ Exam trap 4
      “Snowflake stores security-related external function information in an API integration.”
      ↩︎ Checkpoint
      “An external function can appear in any clause of a SQL statement in which other types of UDF can appear.”
      ↩︎ Prediction
      “For example, you cannot create a shared view that uses an external function.”
      ↩︎ Checkpoint
      “Future grants of privileges on external functions are not supported.”
      ↩︎ Checkpoint
    2. 2.
      “This is the invocation URL of the proxy service (e.g. API Gateway or API Management service)”
      ↩︎ Writing CREATE EXTERNAL FUNCTION
      “IMMUTABLE: The function always returns the same result when called with the same input. Snowflake does not check or guarantee this”
      ↩︎ Writing CREATE EXTERNAL FUNCTION
      “Note specifically that the underscore character is not allowed in header names.”
      ↩︎ Writing CREATE EXTERNAL FUNCTION
      “the URL, the HTTP headers, and the context headers are hidden from all users who are not owners of the function.”
      ↩︎ Privileges to create and call, and SECURE external functions
      “This parameter is not a performance tuning parameter. This parameter specifies a maximum size, not a recommended size.”
      ↩︎ Exam trap 1
      “This is similar to HEADERS, but instead of using constant strings, it binds Snowflake context function results to HTTP headers.”
      ↩︎ Checkpoint
    3. 3.
      “The first column is always the row number (i.e. the 0-based index of the row within the batch).”
      ↩︎ The batched JSON request and response
      “must be returned in the same order as they were received”
      ↩︎ The batched JSON request and response
      “Snowflake uses the optional Content-MD5 header to check the integrity of the response.”
      ↩︎ The batched JSON request and response
    4. 4.
      “When Snowflake retries a request for a specific batch, Snowflake uses the same batch ID as it used earlier for the same batch.”
      ↩︎ The batched JSON request and response
      “Even if the query contains an ORDER BY clause, the ORDER BY is usually applied after the external function(s) have been called.”
      ↩︎ Prediction
    5. 5.
      “Snowflake uses credential-less API integration objects to authenticate to the proxy service endpoint.”
      ↩︎ Securing the API integration and the proxy
      “the administrator can also specify an allowed list of endpoints that the API integration object can access”
      ↩︎ Securing the API integration and the proxy
      “The key is opaque to Snowflake, and Snowflake does not validate it.”
      ↩︎ Securing the API integration and the proxy
      “For AWS, all Snowflake HTTP requests (going to the API Gateway) are signed using AWS sigv4 authentication.”
      ↩︎ Securing the API integration and the proxy
      “To create an API integration, you need ACCOUNTADMIN privileges or a Snowflake role with the CREATE INTEGRATION privilege.”
      ↩︎ Privileges to create and call, and SECURE external functions
      “The owner must grant callers (other than the owner) appropriate privilege(s) on the function.”
      ↩︎ Privileges to create and call, and SECURE external functions
      “An API_KEY is in addition to, not a substitute for, IAM (Identity and Access Management).”
      ↩︎ Exam trap 2
      “the author of the external function must be granted USAGE privilege on the API integration.”
      ↩︎ Exam trap 3
      “An API_KEY is in addition to, not a substitute for, IAM (Identity and Access Management).”
      ↩︎ Checkpoint
    6. 6.
      “Explicitly limits external functions that use the integration to reference one or more HTTPS proxy service endpoints”
      ↩︎ Securing the API integration and the proxy
    7. 7.
      “a Snowflake-owned IAM (identity and access management) user must be granted permission to assume an IAM role in your AWS account”
      ↩︎ Securing the API integration and the proxy

    Ready to test yourself?

    Practise the 13 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.