SnowPro Associate: Platform Question 308
Single answer○ Function argumentA data engineering team creates a SQL UDF in Snowflake to standardize customer names before loading them into a reporting table:
CREATE OR REPLACE FUNCTION normalize_name(name STRING) RETURNS STRING AS $$ UPPER(TRIM(name)) $$;
Later, an analyst runs the following query against a staging table that contains a VARIANT column named RAW_RECORD:
SELECT normalize_name(RAW_RECORD:name) AS cleaned_name FROM customer_stage;
The query fails because the argument type does not match what the function expects. What is the best way to call the function so the query succeeds while preserving the intended logic?
- A
SELECT normalize_name(RAW_RECORD:name::STRING) AS cleaned_name FROM customer_stage;
- B
SELECT normalize_name(TO_VARIANT(RAW_RECORD:name)) AS cleaned_name FROM customer_stage;
- C
SELECT normalize_name(PARSE_JSON(RAW_RECORD:name)) AS cleaned_name FROM customer_stage;
- D
SELECT normalize_name(OBJECT_CONSTRUCT(RAW_RECORD:name)) AS cleaned_name FROM customer_stage;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, SQL UDFs are defined with specific argument data types, and the values passed to them must match those types or be explicitly cast when needed. In this scenario, the UDF normalize_name(name STRING) expects a STRING input. However, RAW_RECORD:name is extracted from a VARIANT column, so it is treated as a semi-structured value rather than a plain STRING. The correct solution is to cast the extracted field using ::STRING before passing it as the function argument.
This reflects a common real-world pattern when working with semi-structured data in Snowflake: extract from VARIANT, cast to the expected scalar type, then pass into SQL expressions or functions. Snowflake documentation on semi-structured data and SQL UDFs emphasizes that field extraction from VARIANT often requires explicit casting when a downstream function expects a standard SQL data type.
- A. Correct.
Correct. The SQL UDF expects a STRING argument, but extracting a field from a VARIANT column returns a semi-structured value. Casting the extracted value to STRING aligns the function argument type with the UDF definition. This is the standard approach when passing data from VARIANT into functions that expect structured scalar types.
- B. Incorrect.
Incorrect. TO_VARIANT converts the value to VARIANT, which moves it further away from the STRING type required by the UDF. A common misconception is that VARIANT is universally accepted by functions, but function signatures must still match or be coercible in the required way.
- C. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON text into a semi-structured value such as OBJECT, ARRAY, or VARIANT-like JSON content. Here, the goal is not to create JSON; it is to provide a STRING to the UDF. Using PARSE_JSON would not resolve the argument mismatch.
- D. Incorrect.
Incorrect. OBJECT_CONSTRUCT creates an OBJECT from key-value pairs, not a STRING. This would produce a semi-structured object and is unrelated to the requirement of passing a scalar text value into the function. Someone might choose this if they confuse JSON object construction with field extraction.