DAA-C01 Question 188
Single answer3.1 Use SQL extensibility features.A retail analytics team wants to standardize customer lifetime value (CLV) calculations across dashboards and ad hoc analysis in Snowflake. Today, analysts repeatedly copy a complex SQL expression into many queries, which has led to inconsistent results and difficult maintenance. The team wants a solution that can be called directly in SQL SELECT statements, accepts input parameters such as revenue, tenure, and churn risk, and centralizes the business logic so updates are made in one place. Which Snowflake SQL extensibility feature is the BEST fit for this requirement?
- A
Create a SQL scalar user-defined function (UDF) that encapsulates the CLV formula and returns a single value
- B
Create a stored procedure that computes CLV and invoke it inline from every SELECT statement
- C
Create a user-defined table function (UDTF) because any reusable SQL logic should be implemented as a table-returning object
- D
Create a materialized view with the CLV formula and require all analysts to query the view instead of calling reusable logic
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a SQL scalar UDF. Under Snowflake SQL extensibility features, scalar UDFs are appropriate when you need reusable, centralized business logic that accepts arguments and returns a single value usable directly in SQL statements. This fits common analytics scenarios where teams want to avoid duplicating formulas across dashboards and ad hoc queries. Stored procedures are better for workflow-style logic rather than inline expressions. UDTFs are used when the output is tabular rather than scalar. Materialized views can improve performance for fixed query patterns, but they do not solve the need for parameterized reusable logic. This aligns with Snowflake documentation and best practices around choosing between UDFs, UDTFs, and stored procedures based on whether the logic returns a scalar, a table, or performs procedural operations.
- A. Correct.
Correct. A SQL scalar UDF is designed to encapsulate reusable SQL logic that accepts parameters and returns a single value. In this scenario, the CLV calculation is an expression reused across many SELECT statements, so a scalar UDF provides the cleanest and most maintainable solution. Analysts can call the function directly in SQL, updates are centralized, and the function matches the requirement of returning one calculated value per row.
- B. Incorrect.
Incorrect. Stored procedures are intended for procedural logic and orchestration, such as executing multiple SQL statements, handling control flow, or performing administrative tasks. They are not the best fit for a reusable expression that needs to be invoked inline in a SELECT list for row-by-row calculation. This option reflects a common misconception that procedures and functions are interchangeable in SQL queries.
- C. Incorrect.
Incorrect. A UDTF returns a set of rows, not a single scalar value. UDTFs are useful when custom logic needs to output tabular results that can be joined or selected from, but they are unnecessarily complex for a single-value CLV calculation used in expressions. The misconception here is assuming all reusable SQL logic should be implemented as a table function.
- D. Incorrect.
Incorrect. A materialized view can precompute and store query results for performance, but it is not a general-purpose parameterized SQL extensibility feature. The requirement explicitly calls for passing input parameters such as revenue, tenure, and churn risk directly from queries. A materialized view cannot be called like a function with different input values per query or per expression.