SnowPro Associate: Platform Question 311
Single answer○ Function argumentA data engineer creates a SQL UDF in Snowflake to standardize customer names from multiple source systems:
CREATE OR REPLACE FUNCTION normalize_name(raw_name VARCHAR) RETURNS VARCHAR AS $$ UPPER(TRIM(raw_name)) $$;
Later, the engineer tries to call the function with data from a VARIANT column that contains JSON, using:
SELECT normalize_name(customer_data:name) FROM staging_customers;
Some rows fail because the JSON field is not consistently stored as a string. The engineer wants the function call to work reliably without changing the UDF definition. Which approach should be used?
- A
Cast the function argument to VARCHAR when calling the function, for example: normalize_name(customer_data:name::VARCHAR)
- B
Change the UDF to accept ANY data type so Snowflake can infer the correct argument type at runtime
- C
Wrap the argument with TO_VARIANT so the existing VARCHAR parameter can process semi-structured input automatically
- D
Use the function with named arguments, for example: normalize_name(raw_name => customer_data:name), so Snowflake performs implicit conversion
Show answer and explanation
Correct answer: A
Explanation
This question tests understanding of function arguments in Snowflake, especially how argument types must align with a function's defined signature. The UDF normalize_name(raw_name VARCHAR) expects a string argument. When the caller passes a value extracted from a VARIANT column, the result is still semi-structured unless explicitly converted. In practice, JSON path expressions often require casting before they can be used with string functions or UDFs expecting VARCHAR. The most reliable solution is to cast the argument at the call site, such as customer_data:name::VARCHAR. This reflects Snowflake best practices for working with semi-structured data and SQL functions: ensure the argument type matches the function definition rather than assuming automatic conversion will always occur. Relevant Snowflake documentation areas include SQL UDF creation and usage, data type conversion/casting, and querying semi-structured data in VARIANT columns.
- A. Correct.
Correct. The UDF parameter is defined as VARCHAR, so passing a VARIANT expression from semi-structured JSON can fail if Snowflake cannot safely use the value as a string. Explicitly casting the function argument to VARCHAR at call time ensures the argument type matches the function signature. This is the practical fix when the UDF definition should remain unchanged.
- B. Incorrect.
Incorrect. Snowflake SQL UDFs do not support defining a parameter as a generic ANY type for runtime inference in this way. Function signatures are based on declared argument data types, and callers must supply compatible values or explicitly cast them.
- C. Incorrect.
Incorrect. TO_VARIANT converts a value to VARIANT, which moves the argument farther away from the expected VARCHAR type. Since the UDF expects VARCHAR, converting the input to VARIANT does not help and would make the type mismatch worse.
- D. Incorrect.
Incorrect. Named arguments improve readability in some function or procedure calls, but they do not change the underlying data type of the value being passed. Snowflake will not perform a conversion just because the parameter name is specified explicitly.