ARA-C01 Question 362
Single answerExternal functionsA financial services company uses a Snowflake external function to send transaction attributes to an AWS-hosted fraud-scoring API through API Gateway and a proxy service. During month-end processing, analysts run ad hoc queries that call the external function for millions of rows. The security team requires that no data be cached in Snowflake because scores can change quickly, and the platform team wants to reduce unnecessary outbound calls and cost when duplicate inputs appear in the same query. Which approach should the architect recommend?
- A
Define the external function as VOLATILE and rely on Snowflake to cache repeated results within the same statement to reduce duplicate calls.
- B
Define the external function as IMMUTABLE so Snowflake can safely eliminate duplicate calls, while setting the function to disable result caching for returned values.
- C
Implement batching in the proxy/service handling the external function requests, and define the external function with VOLATILE semantics so Snowflake does not cache returned values.
- D
Replace the external function with a JavaScript UDF that makes direct HTTPS calls to the fraud API so Snowflake can control per-row invocation and avoid caching.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to use batching with the external function's remote service and classify the function as VOLATILE. In Snowflake, external functions are intended for invoking remote services outside Snowflake through a supported integration pattern, such as API Gateway plus a proxy service. For high-volume workloads, batching is a key design best practice because Snowflake can send multiple rows in a single request payload, reducing per-call overhead and cost. Since the fraud score can change rapidly, the function should not be declared IMMUTABLE. VOLATILE more accurately reflects that repeated invocations with the same arguments may produce different results. This aligns with Snowflake guidance on choosing volatility based on whether identical inputs can return different outputs. Also, replacing external functions with JavaScript UDFs for direct HTTP access is not a valid Snowflake architecture pattern. Relevant Snowflake documentation includes the sections on external functions, API integrations, request batching, and function volatility/immutability semantics.
- A. Incorrect.
Incorrect. VOLATILE is the right volatility classification when results can change quickly, but Snowflake should not be relied upon to cache repeated results for a VOLATILE external function. In fact, the requirement explicitly says scores must not be cached in Snowflake. This option also conflates volatility with a beneficial deduplication behavior that is not the recommended design pattern for external functions under changing results.
- B. Incorrect.
Incorrect. Marking the function IMMUTABLE would be inappropriate because the fraud score can change for the same input over time. IMMUTABLE indicates that the function returns the same result for the same inputs, which could allow optimizer behaviors inconsistent with the business requirement. Also, external functions are designed so architects manage downstream behavior through the remote service pattern, and using IMMUTABLE here would misrepresent the function's semantics.
- C. Correct.
Correct. External functions support batching of rows in requests, which is the recommended way to improve efficiency and reduce outbound call overhead at scale. Because the fraud score can change quickly and the security team does not want Snowflake caching returned values, VOLATILE is the appropriate classification. This combination aligns with both cost/performance goals and correctness requirements.
- D. Incorrect.
Incorrect. Snowflake UDFs do not provide a supported pattern for arbitrary direct outbound HTTPS calls from JavaScript UDF code to external APIs in place of external functions. External network invocation is exactly the use case external functions are intended to address, typically through an API integration and cloud provider-managed endpoint such as API Gateway.