Snowflake External Functions: How They Work and CREATE EXTERNAL FUNCTION

1.Four pieces: function, proxy, remote service, integration

An external function is a kind of UDF, but it has no handler code. Inside Snowflake it is a database object in a specific database and schema. It stores what Snowflake needs to reach the code, including the URL of the proxy service. Three other pieces sit around it. The remote service is the code that does the work. It must behave like a function and return a value. It must accept JSON input, return JSON output and expose an HTTPS endpoint. It can be an AWS Lambda function, a Microsoft Azure Function, or an HTTPS server such as Node.js on an EC2 instance. The proxy service relays requests and responses between Snowflake and the remote service. It can also authenticate requests and support subscription billing. The API integration is a Snowflake object that holds the security information needed to work with the proxy.

Why choose this over an ordinary UDF? The remote code can be written in languages internal UDFs don't support, such as Go and C#. It can use libraries an internal UDF can't reach, such as commercial machine-learning scoring libraries. The same service can also be called from software outside Snowflake. The cost is setup: before the first call, an administrator has to configure the cloud platform, and that work needs knowledge of the platform's security.

Sources1

2.One call, end to end: POST, batches and JSON rows

While a query runs, Snowflake reads two things: the external function definition, which gives the proxy URL and the name of the API integration, and the API integration itself, which gives the proxy resource and its authentication details. Snowflake then builds an HTTP POST containing the data as JSON, HTTP headers and the integration's authentication information, and sends it to the proxy. The proxy forwards the request to the remote service. The result travels back the same way to the SQL statement. If the remote service replies with a status code that means 'still processing', Snowflake polls with HTTP GET requests until it gets the result, the call times out or an error comes back.

Rows go out in batches. Batching allows more parallelism and can keep the remote service from being overloaded. Each batch has a unique batch ID, and retries usually happen one batch at a time. The POST body is a JSON object with a single key, data. Its value is an array with one entry per row. The first element of each row is the row number, and the function arguments follow.

{ "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 uses the same shape. It returns one row per input row, each holding a row number and exactly one value. The value can be compound, such as an OBJECT. The row numbers must match the request and come back in the same order. Snowflake also sends headers such as sf-external-function-current-query-id, for linking calls back to Snowflake queries, and sf-external-function-query-batch-id.

{
    "data":
        [
            [ 0, { "City" : "Warsaw",  "latitude" : 52.23, "longitude" :  21.01 } ],
            [ 1, { "City" : "Toronto", "latitude" : 43.65, "longitude" : -79.38 } ]
        ]
}

Sources12

3.Writing CREATE EXTERNAL FUNCTION

The DDL has three parts. The first is the signature: name, arguments and RETURNS type. The arguments should match what the remote service expects. The second is API_INTEGRATION, which names the integration used to authenticate to the proxy. The third is AS, the invocation URL of the proxy and resource. Everything else is optional. Because functions are resolved by name and argument types, external functions can be overloaded. CREATE OR ALTER EXTERNAL FUNCTION updates an existing function in place.

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>';

Two defaults catch people out. VOLATILE is the default, and Snowflake doesn't verify IMMUTABLE: if you mark a non-deterministic service IMMUTABLE, the behaviour is undefined. Snowflake therefore recommends always setting the option explicitly. MAX_BATCH_ROWS exists only for remote services with memory or similar limits. Leave it unset unless the service needs a cap. If calls time out while every component looks healthy, one documented step is to try a smaller batch size.

Sources3

4.Calling it from SQL, and where it can't go

To a SQL user, an external function looks like any other UDF. It takes parameters, returns a value (which can be a VARIANT holding JSON), and can be nested inside larger expressions. You call it by its fully qualified name.

select my_database.my_schema.my_external_function(col1) from table1;

The limits are what the exam tests. External functions must be scalar: one value per input row. Only functions can be built this way, not stored procedures. They can't be shared through Secure Data Sharing, and neither can any shared object that uses one, such as a shared view. They can't be used in a CREATE TABLE DEFAULT clause or in a COPY transformation. A batch response can be at most 10MB. Because the optimizer can't see inside the remote service, external functions carry more overhead than built-in functions or internal UDFs and usually run more slowly.

The remote service also has to be designed for how Snowflake calls it. Batch sizes, the order of batches and the order of rows within a batch can all vary. ORDER BY is usually applied after the external function runs. So each row's result must depend only on that row. Snowflake retries on transient network errors, 429 responses and 5XX responses, so the service may see the same row more than once. A retried batch keeps its batch ID, which makes the ID usable as an idempotency token.

Sources14

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Set MAX_BATCH_ROWS to make an external function run faster.Why is that wrong?

   MAX_BATCH_ROWS is a ceiling for remote services with memory or similar limits. If you leave it unset, Snowflake picks the batch size.

   Covered in Writing CREATE EXTERNAL FUNCTION

2. 2.An external function can enrich rows inside a COPY INTO transformation while loading.Why is that wrong?

   COPY transformations are on the list of places where external functions can't be used, along with DEFAULT clauses and shared objects.

   Covered in Calling it from SQL, and where it can't go

3. 3.The remote service receives each row exactly once, in ORDER BY order.Why is that wrong?

   Snowflake may retry a batch, so a row can arrive more than once. Batch and row order can vary, so each row should be processed independently.

   Covered in Calling it from SQL, and where it can't go