COF-C03 Question 57
Single answerTypesA data engineering team is loading event data from JSON files into Snowflake. The raw payload contains an attribute named order_id, but the values are inconsistent across files: some records store it as a number (12345), some as a string ("12345"), and some records omit it entirely. The team wants to preserve the raw JSON while also creating a relational view that exposes order_id as a numeric column for downstream reporting. Which approach best meets this requirement with the least risk of load failures?
- A
Load the JSON into a VARIANT column, then define the view using TRY_TO_NUMBER(v:order_id) AS order_id_num
- B
Create the target table with order_id NUMBER and load the JSON directly into that column so Snowflake enforces type consistency during ingestion
- C
Cast the full JSON document to OBJECT and then use TO_NUMBER on the OBJECT to derive order_id_num in the view
- D
Store the JSON in a VARCHAR column and use implicit casting in queries because Snowflake automatically converts semi-structured content to numbers when possible
Show answer and explanation
Correct answer: A
Explanation
Snowflake best practice for semi-structured JSON is to ingest the raw payload into a VARIANT column. VARIANT can store values of different underlying types, including numbers, strings, arrays, and objects, which makes it ideal when the same JSON attribute is inconsistently typed across records. To expose a relational numeric field, the view should extract the attribute with path notation, for example v:order_id, and use a safe conversion function such as TRY_TO_NUMBER. This avoids failures when the field is missing or contains unexpected text, returning NULL instead. In contrast, forcing ingestion into a NUMBER column can fail or lose fidelity, and storing JSON as VARCHAR sacrifices Snowflake's native semi-structured capabilities. This aligns with Snowflake documentation on semi-structured data types, especially VARIANT, and conversion functions such as TRY_TO_NUMBER for resilient transformations.
- A. Correct.
Correct. VARIANT is the appropriate Snowflake type for storing semi-structured JSON while preserving the raw payload. In the view, extracting the path with v:order_id and applying TRY_TO_NUMBER safely converts numeric and numeric-like string values to NUMBER, while returning NULL for missing or non-convertible values instead of causing query errors. This is a common best practice when source JSON has inconsistent typing.
- B. Incorrect.
Incorrect. Loading directly into a NUMBER column increases the risk of ingestion issues because the source data is not consistently numeric and some records omit the field. This approach also does not preserve the raw JSON document as required. Snowflake supports structured and semi-structured storage separately, and VARIANT is designed for this exact scenario.
- C. Incorrect.
Incorrect. Although JSON objects can be represented as OBJECT within VARIANT semantics, TO_NUMBER cannot be meaningfully applied to the entire OBJECT. The correct pattern is to extract the specific attribute, such as v:order_id, and then cast or TRY_CAST that value. This option reflects a misunderstanding between the JSON container and the scalar field inside it.
- D. Incorrect.
Incorrect. VARCHAR can store raw text, but it is not the best type for querying JSON attributes in Snowflake. Semi-structured querying with path notation is designed for VARIANT. Relying on implicit casting from VARCHAR is less robust, makes querying more cumbersome, and does not provide the native semi-structured handling Snowflake offers for JSON data.