COF-C03 Question 34
Single answerUser-Defined Functions (UDFs)A data engineering team wants to standardize how phone numbers are cleaned before loading them into reporting tables. They decide to create a reusable SQL UDF that removes non-numeric characters from an input string and returns the normalized value. The UDF will be called from many SELECT statements across multiple schemas. To simplify maintenance, the team also wants the function to behave consistently no matter which schema is active in the session. Which approach should the engineer take?
- A
Create a temporary SQL UDF in each developer session so that the function is isolated from schema context changes.
- B
Create a permanent SQL UDF in a shared schema and reference objects in the function definition with fully qualified names when needed.
- C
Create a JavaScript UDF because SQL UDFs cannot be reused across multiple schemas.
- D
Create the UDF as a table function so it can be called anywhere without regard to the active schema.
Show answer and explanation
Correct answer: B
Explanation
For this scenario, the best solution is a permanent scalar SQL UDF stored in a shared schema. That supports centralized maintenance, reuse in many queries, and consistent execution across sessions. In Snowflake, temporary UDFs are session-scoped and therefore unsuitable for enterprise-wide standardization. A scalar UDF is also the right fit because the function returns a single value for each input, unlike a table function, which returns rows. When UDF logic references other objects, fully qualifying object names is a practical way to avoid issues caused by changes in current database or schema context. This reflects Snowflake guidance on object naming, resolution, and reusable database object design.
- A. Incorrect.
Incorrect. Temporary UDFs exist only for the session in which they are created and are not suitable for standardizing shared logic across many users, sessions, or workloads. They also do not solve the broader requirement for centralized maintenance and cross-schema reuse.
- B. Correct.
Correct. A permanent SQL UDF created in a shared schema is the appropriate design for reusable business logic. Because name resolution can depend on context, using fully qualified object names in the function definition when external objects are referenced helps ensure consistent behavior regardless of the current database or schema in the session. This aligns with Snowflake best practices for reusable database objects.
- C. Incorrect.
Incorrect. SQL UDFs are reusable and can be referenced from different schemas when proper naming and privileges are used. Choosing JavaScript is unnecessary for simple string transformation logic like removing non-numeric characters, and it does not address the schema-context concern better than a permanent SQL UDF.
- D. Incorrect.
Incorrect. A table function returns a set of rows and is intended for rowset-producing logic, not a simple scalar string transformation. For returning one cleaned phone number per input value, a scalar UDF is the correct object type.