ARA-C01 Question 360
Single answerFunctionsA Snowflake architect is designing a reusable function to standardize customer email addresses across multiple databases. The function must trim spaces, convert values to lowercase, and return NULL when the input is NULL. Security policy requires that application teams can use the function without needing direct access to the underlying reference table that stores a list of blocked domains used inside the function logic. The function will be called from SQL queries at high volume, so the architect wants the simplest implementation that meets the requirement and minimizes unnecessary overhead. Which approach should the architect choose?
- A
Create a secure SQL UDF that encapsulates the normalization logic and any reference-table lookups, then grant USAGE on the function to application roles.
- B
Create a JavaScript UDF because JavaScript UDFs are required whenever the function references a table and must hide underlying objects from callers.
- C
Create an external function so that the email normalization and blocked-domain validation run outside Snowflake, because external functions are the preferred option for high-volume SQL calls.
- D
Create a scalar SQL UDF and grant SELECT on the blocked-domain reference table to all application roles so the function can execute successfully.
Show answer and explanation
Correct answer: A
Explanation
The key design decision is to use the simplest native function type that satisfies both performance and security requirements. Snowflake supports SQL UDFs, JavaScript UDFs, and external functions, among others. When logic can be expressed in SQL, SQL UDFs are generally preferred because they stay inside Snowflake's execution engine and avoid unnecessary language-runtime or network overhead. The security requirement points specifically to a secure UDF, which is intended to prevent exposure of underlying logic and referenced objects while allowing controlled invocation through function privileges. This is a common architectural pattern when centralizing reusable business logic across teams. In Snowflake documentation, secure UDFs and secure views are described as mechanisms for protecting sensitive logic and data access patterns, while external functions are intended for calling remote services rather than for native transformations that Snowflake can already perform efficiently.
- A. Correct.
Correct. A secure SQL UDF is the best fit here. The logic is SQL-based, the function is scalar and intended for high-volume invocation, and secure UDFs are specifically designed to protect underlying implementation details and referenced objects from callers. Granting USAGE on the function allows consumers to invoke it without direct access to the underlying table used inside the function body. This aligns with Snowflake best practices of using SQL UDFs when SQL can express the logic, reserving more complex runtimes only when needed.
- B. Incorrect.
Incorrect. JavaScript UDFs are not required simply because a function uses table-based logic or because implementation details need to be hidden. In fact, if the requirement can be met in SQL, a SQL UDF is generally the simpler and more efficient choice. Choosing JavaScript here would add unnecessary runtime complexity without providing a security advantage over a secure SQL UDF.
- C. Incorrect.
Incorrect. External functions are used to invoke remote services outside Snowflake, typically through API integration. They introduce network round trips, external infrastructure, and operational complexity. For simple normalization and lookup logic that can be handled natively in Snowflake, external functions are not the preferred design, especially for high-volume SQL calls where minimizing overhead is important.
- D. Incorrect.
Incorrect. A regular scalar SQL UDF may work functionally, but granting SELECT on the blocked-domain reference table to all application roles violates the stated security requirement. The requirement is that application teams should be able to use the function without direct access to the underlying reference table. A secure UDF is designed for this exact use case.