COF-C03 Question 298
Single answerStructuredA data engineering team is designing a Snowflake table to store customer profile information. Each row must include a customer ID and a profile object with a consistent schema: first_name as STRING, age as NUMBER, and active as BOOLEAN. The team wants Snowflake to enforce this internal structure at write time so that malformed profile data is rejected instead of being stored as generic semi-structured data. Which approach should the engineer use?
- A
Create the column as OBJECT and rely on downstream queries to cast first_name, age, and active into the expected types
- B
Create the column as VARIANT and define a masking policy that validates the JSON keys and value types on insert
- C
Create the column using a structured OBJECT type, for example OBJECT(first_name VARCHAR, age NUMBER, active BOOLEAN)
- D
Create separate VARCHAR columns for the full JSON document and use a CHECK constraint with regular expressions to validate the schema
Show answer and explanation
Correct answer: C
Explanation
Snowflake supports structured data types, including structured OBJECT, ARRAY, and MAP types, to enforce a defined schema within nested data. In this scenario, the requirement is not just to store JSON-like data, but to guarantee that the object contains specific attributes with specific data types at write time. A structured OBJECT definition best fits that requirement. By contrast, VARIANT and regular semi-structured OBJECT are flexible and schema-on-read oriented, which is useful for evolving or unpredictable data but does not provide the same strict internal type enforcement. This aligns with Snowflake documentation and best practices that distinguish semi-structured types such as VARIANT/OBJECT/ARRAY from structured types that provide stronger schema guarantees for nested content.
- A. Incorrect.
Incorrect. A regular semi-structured OBJECT column can store key-value data, but it does not enforce a fixed internal schema for required attributes and their data types in the same way a structured type does. Relying on downstream casts shifts validation to query time rather than enforcing structure when data is written.
- B. Incorrect.
Incorrect. VARIANT can store semi-structured data, but masking policies are designed for data protection and conditional obfuscation, not for schema enforcement of JSON content during inserts. This option confuses governance features with type enforcement capabilities.
- C. Correct.
Correct. Structured types in Snowflake allow definition of a fixed schema inside complex data types. Declaring a column as OBJECT(first_name VARCHAR, age NUMBER, active BOOLEAN) enforces the expected fields and data types, helping reject malformed data at write time rather than storing loosely typed semi-structured content.
- D. Incorrect.
Incorrect. Storing JSON as VARCHAR removes native support for querying nested content and does not represent best practice for structured or semi-structured data in Snowflake. Although CHECK constraints can validate some patterns, regular expressions are not a robust or recommended way to enforce typed object schemas compared with Snowflake structured types.