ARA-C01 Question 223
Single answerFunctionsA Snowflake architect is designing a reusable function to standardize customer email addresses across multiple databases. The function must be callable from SQL, return the same result for the same input, and enforce masking of invalid values by returning NULL when the input does not match a valid email pattern. Security teams also require that application developers can use the function without seeing the underlying implementation logic. Which approach best meets these requirements?
- A
Create a secure SQL UDF that accepts a VARCHAR input and uses deterministic SQL expressions, including REGEXP_LIKE, to return a normalized email or NULL.
- B
Create an external function that calls a REST endpoint to validate and normalize the email, because external functions hide implementation details from developers by default.
- C
Create a JavaScript UDF marked as VOLATILE so Snowflake reevaluates the logic each time and therefore guarantees deterministic results.
- D
Create a user-defined table function (UDTF) that returns one row per email address, because table functions are the recommended way to encapsulate reusable scalar validation logic.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a secure SQL UDF. This design satisfies all stated requirements: it is callable directly from SQL, can be implemented with deterministic built-in SQL functions, can return NULL for invalid inputs, and can obscure the implementation from consuming developers through the SECURE property. In Snowflake, SQL UDFs are the preferred option when the logic can be expressed with SQL expressions and a scalar result is needed. Secure UDFs help protect sensitive business logic from exposure. By contrast, external functions are intended for integrations with external services, and UDTFs are designed for tabular rather than scalar outputs. Snowflake documentation on user-defined functions, secure objects, and external functions supports these distinctions and the associated best practices.
- A. Correct.
Correct. A secure SQL UDF is appropriate when the function must be invoked from SQL, return a scalar value, and conceal the implementation details from users who have permission to execute it. Using deterministic SQL logic such as LOWER/TRIM and REGEXP_LIKE fits the requirement that the same input should produce the same output. Returning NULL for invalid patterns is a common and valid scalar UDF design. Secure UDFs are specifically intended to protect the underlying definition from being exposed in query plans or metadata visible to consumers.
- B. Incorrect.
Incorrect. External functions are used to call out to external services through API integration, typically when logic must execute outside Snowflake. This introduces network dependency, latency, and operational complexity that are unnecessary for straightforward email normalization and validation. While external functions can abstract logic, they are not the best fit for a deterministic in-platform transformation that Snowflake SQL can handle directly.
- C. Incorrect.
Incorrect. JavaScript UDFs can be used for custom scalar logic, but marking a function VOLATILE does not guarantee deterministic behavior; it indicates the opposite concept, that results may vary even for the same input. Also, for simple string normalization and regex validation, a SQL UDF is generally simpler, more performant, and easier to govern than a JavaScript UDF.
- D. Incorrect.
Incorrect. A UDTF returns a set of rows and is intended for scenarios where one input may expand into multiple output rows or columns. The requirement here is for a scalar function that returns a single value per email input. Using a UDTF for scalar validation logic would be unnecessarily complex and not the best architectural choice.