ARA-C01 Question 366
Single answerUser-Defined Functions (UDFs)A financial services company has several analytics teams querying the same governed customer data in Snowflake. The architecture team wants to standardize masking of account identifiers so analysts can use a single reusable function in SELECT statements across databases. The masking logic must be deterministic, return the same result for the same input, and rely only on SQL expressions so it can be easily reviewed by governance teams. The team also wants to minimize operational overhead and avoid external network calls or language runtimes. Which approach best meets these requirements?
- A
Create a secure SQL UDF that accepts the account identifier and returns a masked value using only SQL expressions.
- B
Create an external function that calls a REST endpoint to mask account identifiers and mark it as immutable.
- C
Create a Python UDF to mask the identifier because Python UDFs are the only reusable functions that can be shared across databases.
- D
Create a JavaScript UDF that performs the masking and stores intermediate results in a temporary table for reuse.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a secure SQL UDF. Snowflake supports SQL UDFs for reusable logic written directly in SQL, which is ideal when the transformation is deterministic and can be expressed with built-in SQL functions. Marking the function as SECURE is aligned with governance and data-sharing best practices because secure UDFs help protect the function definition and underlying sensitive logic. By contrast, external functions are designed for integrating with remote services and introduce additional infrastructure, network latency, and failure modes that are unnecessary here. Python and JavaScript UDFs are valuable when SQL is insufficient, but they add language runtime considerations and are less appropriate when the requirement is specifically to keep logic in SQL for reviewability and low overhead. Snowflake documentation on UDFs, secure objects, and external functions supports these distinctions.
- A. Correct.
Correct. A secure SQL UDF is the best fit for deterministic masking logic implemented entirely with SQL expressions. SQL UDFs are appropriate when the logic can be expressed in SQL, and using SECURE helps protect the implementation details of the function from unauthorized users while enabling governed reuse. This approach avoids external calls and additional runtime dependencies, reducing operational complexity.
- B. Incorrect.
Incorrect. External functions invoke remote services through API integrations, which introduces network dependency, latency, and operational overhead. They are intended for use cases requiring off-platform processing, not for simple deterministic masking that can be implemented directly in SQL. Also, describing the function as 'immutable' does not change the fact that it still depends on an external service and is not the simplest architecture for this requirement.
- C. Incorrect.
Incorrect. Python UDFs can be useful for logic not easily expressed in SQL, but they are not the only reusable functions that can be referenced across databases. In this scenario, the requirement explicitly favors SQL-only logic, easy governance review, and minimal operational overhead. A Python runtime adds unnecessary complexity compared with a SQL UDF.
- D. Incorrect.
Incorrect. JavaScript UDFs do not support performing DML side effects such as storing intermediate results in temporary tables as part of the UDF execution model. More broadly, this option violates the requirement to use SQL-only logic and minimize operational complexity. For deterministic masking, a SQL UDF is a better architectural choice.