ARA-C01 Question 367
Single answerUser-Defined Functions (UDFs)A retail company uses Snowflake to score incoming orders for fraud risk. The scoring logic is called from SQL queries used by multiple analytics teams, so architects want a reusable function interface. The logic depends on a small lookup table of high-risk ZIP codes that is updated daily, and the security team does not want analysts to have direct SELECT access to that table. The architects also want to avoid rewriting application SQL whenever the lookup data changes. Which approach best meets these requirements?
- A
Create a secure SQL UDF owned by a role with access to the lookup table, and reference the high-risk ZIP code table inside the UDF so analysts can call the function without direct access to the table.
- B
Create a JavaScript UDF that performs a SELECT against the high-risk ZIP code table at runtime and return the fraud score to the calling SQL statement.
- C
Create a scalar SQL UDF that accepts the list of high-risk ZIP codes as an ARRAY parameter from each calling query, so the UDF remains independent of database objects.
- D
Create an external function that queries the high-risk ZIP code table through an API integration, because only external functions can hide underlying table access from analysts.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a secure SQL UDF that encapsulates the lookup-table access. In Snowflake, SQL UDFs are appropriate when you need reusable SQL-callable logic, including logic that can reference Snowflake objects such as tables. This gives calling teams a stable function name and signature while allowing the underlying lookup data to be updated independently. Using SECURE helps protect the function definition and implementation details from consumers, which aligns with the requirement to avoid exposing sensitive logic or underlying object details. By contrast, JavaScript UDFs are for expression-level computation and cannot issue SQL statements to query tables. External functions are designed for calling external services, not for solving native table-lookup problems within Snowflake. Snowflake documentation on user-defined functions, secure UDFs, and external functions supports these distinctions and the best-practice choice of native SQL UDFs for reusable in-platform logic.
- A. Correct.
Correct. A SQL UDF can encapsulate logic that references database objects such as tables, allowing callers to use a stable function interface while the underlying lookup table changes over time. Making the function SECURE is appropriate when you want to protect the implementation details and expose only the function contract to consumers. This pattern supports reuse across teams and avoids granting analysts direct SELECT access to the underlying lookup table.
- B. Incorrect.
Incorrect. JavaScript UDFs in Snowflake are intended for row-by-row computation and do not execute arbitrary SQL statements such as SELECT against Snowflake tables at runtime. Querying database objects from procedural logic is a capability associated with stored procedures, not JavaScript UDFs. This option reflects a common confusion between UDFs and stored procedures.
- C. Incorrect.
Incorrect. Passing the high-risk ZIP code list from every calling query would push data management to each consumer, increasing maintenance burden and making the SQL interface less stable. It also does not satisfy the requirement to avoid rewriting application SQL when lookup data changes. While ARRAY parameters are valid in some designs, this is not the best architectural choice for centrally managed lookup data.
- D. Incorrect.
Incorrect. External functions are used to invoke remote services outside Snowflake through API integrations. They are not required to hide table access, and introducing them here would add unnecessary network, security, operational, and cost complexity for logic that can be handled natively inside Snowflake. This distractor targets the misconception that external functions are the default solution whenever encapsulation is needed.