SnowPro Associate: Platform Question 309
Single answer○ Function argumentA data engineer creates a SQL UDF to standardize customer names and wants the function to work when the input column is VARCHAR, CHAR, or another text-compatible type. The engineer also wants to avoid forcing callers to cast values before calling the function. Which function definition best meets this requirement?
- A
CREATE FUNCTION normalize_name(name VARCHAR) RETURNS VARCHAR AS $$ UPPER(TRIM(name)) $$;
- B
CREATE FUNCTION normalize_name(name STRING) RETURNS STRING AS $$ UPPER(TRIM(name)) $$;
- C
CREATE FUNCTION normalize_name(name ANY) RETURNS VARCHAR AS $$ UPPER(TRIM(name)) $$;
- D
CREATE FUNCTION normalize_name(name VARIANT) RETURNS VARCHAR AS $$ UPPER(TRIM(name::VARCHAR)) $$;
Show answer and explanation
Correct answer: B
Explanation
The best answer is the function that declares the argument as STRING. In Snowflake, STRING is a synonym for VARCHAR and is appropriate for SQL UDFs that operate on text values. Function arguments in SQL UDFs must use valid Snowflake SQL data types; there is no generic ANY type for SQL UDF parameters. Although VARIANT can hold many kinds of values, it is typically used for semi-structured data rather than routine text-processing functions. Best practice is to define function arguments as the most appropriate specific type for the expected input, which improves clarity and avoids unnecessary casting. This aligns with Snowflake documentation on CREATE FUNCTION, SQL UDF argument data types, and the use of STRING/VARCHAR as equivalent string data types.
- A. Incorrect.
Incorrect. Defining the argument as VARCHAR works for VARCHAR inputs, but it is more restrictive than necessary for a function intended to accept any text-compatible input without requiring explicit casts. While VARCHAR is a valid string type in Snowflake, using the more generic string alias is better aligned with the requirement to support string inputs broadly.
- B. Correct.
Correct. In Snowflake SQL, STRING is an alias for VARCHAR and is commonly used to define function arguments intended for textual input. This allows callers to pass character string values such as CHAR and VARCHAR without redesigning the function. For a function that applies string operations like UPPER and TRIM, a string argument type is the appropriate choice.
- C. Incorrect.
Incorrect. ANY is not a valid SQL UDF argument data type in Snowflake. A common misconception is that SQL UDFs support a fully generic argument type similar to loosely typed programming languages, but Snowflake requires supported SQL data types for function arguments.
- D. Incorrect.
Incorrect. VARIANT can store many data types, and casting from VARIANT to VARCHAR inside the function can work in some cases, but this is not the best fit for a function specifically designed for text input. Using VARIANT weakens type specificity, can introduce unnecessary casting, and is generally less appropriate than defining the argument as a string type when the expected input is textual.