SnowPro Associate: Platform Question 310
Single answer○ Function argumentA data engineer is creating a SQL UDF in Snowflake to standardize phone numbers. The function should accept a phone number and an optional country code, defaulting to 'US' when the caller does not provide the second argument. Which approach correctly supports this requirement while allowing analysts to call the function with either one or two arguments?
- A
Create the function with a DEFAULT value for the second argument, for example: CREATE FUNCTION normalize_phone(phone STRING, country STRING DEFAULT 'US') ...
- B
Create two overloaded functions with the same name: one that accepts (phone STRING) and one that accepts (phone STRING, country STRING), and have the one-argument version apply the 'US' logic
- C
Create one function that accepts a single VARIANT argument containing both phone and country, because Snowflake SQL UDFs do not support multiple arguments
- D
Create one function with two required arguments and rely on Snowflake to automatically pass NULL for the missing second argument when callers use only one argument
Show answer and explanation
Correct answer: B
Explanation
This question tests applied knowledge of function arguments in Snowflake UDFs. Snowflake supports function overloading, meaning multiple functions can share the same name as long as their argument signatures differ. However, Snowflake UDFs do not support default argument values in the function definition, and callers cannot omit required arguments unless another overload matches the call. For this scenario, the correct design is to create overloaded functions: one with a single phone argument and another with phone plus country. This is the documented and practical approach for handling optional-like parameters in Snowflake functions.
- A. Incorrect.
Incorrect. In Snowflake, user-defined functions do not support defining default values for function arguments in the CREATE FUNCTION signature. A candidate might choose this because default argument syntax exists in some programming languages and database procedures, but it is not the supported pattern for Snowflake UDFs.
- B. Correct.
Correct. In Snowflake, a practical way to support optional-like behavior for UDF arguments is to create overloaded functions with the same name but different argument signatures. The one-argument function can implement the default 'US' behavior directly or delegate to the two-argument version. This allows callers to use either one or two arguments while keeping invocation syntax simple.
- C. Incorrect.
Incorrect. Snowflake SQL UDFs do support multiple arguments, so wrapping values into a single VARIANT is unnecessary for this requirement. Although VARIANT can be useful for semi-structured inputs, using it here would complicate calls and reduce clarity when overloading cleanly solves the problem.
- D. Incorrect.
Incorrect. Snowflake does not automatically supply NULL for omitted required UDF arguments. Function calls must match an existing function signature. Someone might pick this option if they assume SQL function calls behave like some application languages with optional parameters, but Snowflake requires the provided argument count and types to match a defined function.