What you will be able to do
- Name the objects that let SQL call a model hosted outside Snowflake: API integration, proxy service and remote service
- Write a remote-service response in the JSON format an external function accepts
- Judge when an external function is the wrong choice because of data movement, overhead or scalar-only output
1.How SQL reaches a model outside Snowflake
Some models stay where they were built. A fraud model behind an endpoint that another team runs on another cloud platform is a common example. If analysts must score rows with plain SQL and the model cannot move, an external function is the bridge. An external function is a type of UDF with no code of its own. It is a database object holding what Snowflake needs to call code that is stored and executed elsewhere, and SQL calls it like any other function: select my_database.my_schema.my_external_function(col1) from table1;.
Three pieces sit behind that call:
- The remote service is the code that does the work. It must expose an HTTPS endpoint, accept JSON and return JSON. An AWS Lambda function, an Azure Function or an HTTPS server on EC2 all qualify.
- The proxy service is a cloud-native HTTPS relay, such as Amazon API Gateway or Azure API Management. Snowflake calls this, never the remote service directly. The proxy can authenticate requests and check that the caller is a paying subscriber.
- The API integration, created with
CREATE API INTEGRATION, is the Snowflake object that stores the security information for the proxy.CREATE EXTERNAL FUNCTIONrecords the proxy URL and names this integration.
The integration type matters. A storage integration over the endpoint's bucket would give access to model files, but nothing would execute them. Model scoring from SQL needs an API integration, a proxy in front of the endpoint, and an external function that targets the proxy.
At query time, the chain runs in this order:
- Snowflake reads the external function definition and its API integration.
- It composes an HTTP POST with the row data as JSON, headers and authentication from the integration, and sends it to the proxy.
- The proxy forwards the request to the remote service.
- The result travels back through the proxy into the SQL statement. If the service answers with a code that signals asynchronous processing, Snowflake polls with GET requests until the result is ready or the call times out.
Setup takes cloud-side work before the first call. An administrator who knows the platform's security model has to configure it, and on AWS the API Gateway endpoint must be regional or private.
Checkpoint 1 of 6· Match them up
An analyst scores rows in SQL with a fraud model that runs behind an endpoint on another cloud. Match each piece of that setup to its role.
Tap a term, then the definition that fits it.
Snowflake never calls the model endpoint itself. It reads the external function and its API integration, then POSTs the rows to the proxy, and the proxy forwards them to the remote service. A storage integration could not take the integration's place, because it gives access to files but nothing would run the model.
“Snowflake does not call a remote service directly. Instead, Snowflake calls a proxy service, which relays the data to the remote service.”Source: docs.snowflake.com
Checkpoint 2 of 6· Exam question
A retail data scientist's fraud model is hosted behind an Amazon SageMaker endpoint run by another team. Analysts must score rows with plain SQL inside Snowflake without moving the model. What is the MOST appropriate approach?
Correct answer: C — Create an API integration and a CREATE EXTERNAL FUNCTION that targets an Amazon API Gateway proxy service placed in front of the SageMaker endpoint
- A. Stored procedures do not host another team's SageMaker container, and the endpoint is not an image in a Snowflake repository. The model would simply not be reachable this way.
- B. A storage integration and external table only read files from cloud storage. They cannot invoke a hosted endpoint or return predictions.
- C. An external function calls a remote service through a cloud proxy service (such as API Gateway) using an API integration, so analysts can score rows from SQL while the model stays where it is hosted.
- D. Notification integrations send outbound messages for queues and alerts. They are not a request-response channel, so no prediction is returned to the query.
Sources1
2.The JSON contract between Snowflake and the remote service
Snowflake sends rows in batches, and every row it sends starts with its row number. The request body is a JSON object with a data array. Each inner array holds the 0-based row index followed by the function's arguments:
{ "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 must follow the same format. It is a JSON object with a data array, holding exactly one [row_number, value] pair per input row, with the row numbers matching the request and in the order they were received. Snowflake uses the row numbers to match each result to its input row. A bare list of scores has no row numbers and no data key, so it fails. So do newline-separated text, an object keyed by column names, and a single aggregate number for the whole batch.
{
"data":
[
[ 0, { "City" : "Warsaw", "latitude" : 52.23, "longitude" : 21.01 } ],
[ 1, { "City" : "Toronto", "latitude" : 43.65, "longitude" : -79.38 } ]
]
}The HTTP status code is part of the contract too. 200 means the batch was processed and 202 means it is still being processed. Any other code is treated as an error. A Content-MD5 header is optional, and if it is present and does not match the body, the query fails. Each request carries a batch ID in sf-external-function-query-batch-id. Retries happen per batch, so a remote service can see the same rows more than once and can use that ID as an idempotency token.
Checkpoint 3 of 6· Check yourself
A remote fraud-scoring service writes an audit record for every row it scores. After a query hit a timeout, some rows appear twice in the audit log, but the query results in Snowflake are correct. What is the likeliest cause, and what should the service do?
Snowflake retries per batch, so a remote service can receive the same rows more than once. Each request carries a unique batch ID that the service can use to spot repeats. A 202 makes Snowflake poll with GET requests, not resend the rows. Content-MD5 is optional and only causes a failure when it doesn't match the body. Row numbers that don't match the request make the call fail; they don't trigger a resend.
“Retry operations (e.g. due to timeouts) are typically done at the batch level.”Source: docs.snowflake.com
Checkpoint 4 of 6· Exam question
A team's external function score_fraud returns an error on every call. The remote service currently responds with the body [0.82, 0.13, 0.55] for a batch of three input rows. Which change to the response makes it compatible with an external function?
Correct answer: D — Return a JSON object with a data array that pairs each row number with its score, e.g. {"data": [[0, 0.82], [1, 0.13], [2, 0.55]]}
- A. Snowflake parses a JSON response; plain text lines are not a supported format, and ordering alone is not how rows are matched.
- B. Responses are not keyed by source column names. Each value must be paired with the row number it belongs to inside the
dataarray. - C. External functions are scalar: they return one value per input row, not one aggregate per batch.
- D. External functions send and expect JSON with a top-level
dataarray of [row number, value] pairs, so Snowflake can match each returned value to its input row.
3.When an external function is the wrong tool
External functions buy reach at the cost of performance and control. The remote side can be written in Go or C#, or use commercial scoring libraries that internal UDFs cannot load, and the same service can serve Snowflake and other software. Everything else on the list is a cost.
Data leaves Snowflake. Every batch travels over HTTPS through a cloud proxy to the remote endpoint. That adds network latency, and it puts the rows outside Snowflake's control: a third-party service could keep copies of what it receives. If customer rows must not leave the account, an external function cannot be used.
The output shape is fixed. External functions must be scalar, returning one value per input row, so a model that emits several rows per input has no external-function form. Each batch's response is capped at 10MB. Only functions can be external, not stored procedures.
Overhead and governance limits complete the list. External functions usually run slower than built-in functions or internal UDFs, and the optimizer cannot see inside the remote service to optimize around it. Future grants of privileges on external functions are not supported. External functions also cannot be shared through Secure Data Sharing, used in a column's DEFAULT clause, or used in a COPY transformation.
| Option | Where the model runs | Data movement | Wins when |
|---|---|---|---|
| External function | Remote service behind a cloud proxy such as Amazon API Gateway | Each batch leaves Snowflake over HTTPS | The model must stay on an external platform and SQL users still need to call it |
| Registry version on a warehouse | Virtual warehouse | Stays in Snowflake | CPU-runnable models scored in batch from SQL |
| Registry version on SPCS | Snowpark Container Services compute pool | Stays in Snowflake | GPU, large models or custom packages |
Checkpoint 5 of 6· Check yourself
Compliance rules say customer rows must never leave the Snowflake account. A team wants analysts to score those rows in SQL with a CPU-only XGBoost model that currently runs behind an AWS Lambda endpoint. Which approach meets the rule?
With an external function, every batch is sent over HTTPS through a proxy to the remote service. A private endpoint or an authenticated proxy still sends the rows out of Snowflake, where the remote side could keep copies. A registry version on a warehouse keeps the data inside Snowflake, and CPU-runnable XGBoost is a good fit for warehouse scoring. A storage integration only gives access to files and runs nothing.
“if you call a third party’s function, that party could keep copies of the data passed to the function.”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
A bank must not send customer rows outside its cloud account. Select TWO characteristics of external functions that make them a poor fit for this requirement or for large-batch scoring.(Select 2)
Correct answers: D, E — Each batch of rows travels over HTTPS through a cloud proxy service to the remote endpoint, adding network latency and moving data out of Snowflake; An external function must return exactly one scalar value per input row, so multi-row table outputs for each input row are not available to the caller
- A. The remote service is hosted outside Snowflake, so it is not executed on a warehouse. Warehouse credits only cover the calling query.
- B. External functions are SQL functions and work in any SQL context, so this restriction does not exist.
- C. External functions have no connection to the Model Registry, and no re-registration step is part of invoking them.
- D. Rows are serialized and sent to a remote service through a proxy, so latency is added and data leaves the Snowflake boundary, which conflicts with the bank's constraint.
- E. External functions are scalar, which limits the shapes of output that can be returned compared with in-Snowflake table functions.
Sources1
Sources
(2)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“An external function is a type of UDF.”
↩︎ How SQL reaches a model outside Snowflake“An API integration stores information, such as security information, that is needed to work with a proxy service or remote service.”
↩︎ How SQL reaches a model outside Snowflake“The proxy service can increase security by authenticating requests to the remote service.”
↩︎ How SQL reaches a model outside Snowflake“on AWS, external functions require regional endpoints or private endpoints.”
↩︎ How SQL reaches a model outside Snowflake“Retry operations (e.g. due to timeouts) are typically done at the batch level.”
↩︎ The JSON contract between Snowflake and the remote service“External functions have more overhead than functions (both built-in functions and internal UDFs) and usually execute more slowly.”
↩︎ When an external function is the wrong tool“that party could keep copies of the data passed to the function.”
↩︎ When an external function is the wrong tool“Currently, external functions must be scalar functions. A scalar external function returns a single value for each input row.”
↩︎ When an external function is the wrong tool“The maximum response size per batch is 10MB.”
↩︎ When an external function is the wrong tool“Future grants of privileges on external functions are not supported.”
↩︎ When an external function is the wrong tool“Snowflake does not call a remote service directly. Instead, Snowflake calls a proxy service, which relays the data to the remote service.”
↩︎ Exam trap 2“Snowflake does not call a remote service directly. Instead, Snowflake calls a proxy service, which relays the data to the remote service.”
↩︎ Checkpoint“if you call a third party’s function, that party could keep copies of the data passed to the function.”
↩︎ Checkpoint - 2.
“The row number (i.e. the 0-based index of the row within the batch).”
↩︎ The JSON contract between Snowflake and the remote service“must be returned in the same order as they were received.”
↩︎ The JSON contract between Snowflake and the remote service“Other values are treated as errors.”
↩︎ The JSON contract between Snowflake and the remote service“The JSON response returned to Snowflake should contain one row for each row sent by Snowflake.”
↩︎ Exam trap 1“The value can be a compound value (for example, an OBJECT), but it must be exactly one value”
↩︎ Prediction