COF-C03 Question 299
Single answerStructuredA data engineering team is building a curated Snowflake table for downstream BI dashboards. One column, CUSTOMER_PROFILE, must store JSON-like customer attributes, but the team wants to enforce a consistent schema so analysts can reliably query fields such as loyalty_tier and marketing_opt_in without handling mixed data types at runtime. Which Snowflake approach best meets this requirement?
- A
Store CUSTOMER_PROFILE in a VARIANT column and rely on BI tools to cast each field as needed during query time
- B
Store CUSTOMER_PROFILE in a structured OBJECT type with explicitly defined attribute names and data types
- C
Store CUSTOMER_PROFILE as a VARCHAR column containing JSON text and create views that parse the text when needed
- D
Store CUSTOMER_PROFILE in an ARRAY column so each attribute can be accessed by position for consistent reporting
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a structured OBJECT type when the requirement is to store object-like data while enforcing a known schema. In Snowflake, semi-structured types such as VARIANT, OBJECT, and ARRAY are flexible, but structured types add schema constraints so data types and fields are known and validated. This is useful for curated layers and reporting use cases where consistency matters more than raw flexibility. By contrast, VARIANT is better when the incoming JSON structure can vary, and VARCHAR is typically a weaker choice for JSON because it requires manual parsing. This aligns with Snowflake guidance on choosing structured types when stronger typing and predictable query behavior are needed.
- A. Incorrect.
Incorrect. A VARIANT column can store semi-structured data flexibly, but it does not enforce a fixed schema for the contained attributes. Different rows can contain different keys or inconsistent data types, which is exactly what the team wants to avoid. This is a common choice when flexibility is preferred over schema enforcement, but it does not best satisfy the requirement for consistent typed fields.
- B. Correct.
Correct. A structured OBJECT type allows Snowflake to enforce a defined schema for object attributes, including attribute names and their data types. This is appropriate when the business needs predictable structure and stronger type guarantees for downstream queries. Analysts can query known fields more reliably because the data shape is constrained rather than loosely defined as with semi-structured VARIANT data.
- C. Incorrect.
Incorrect. Storing JSON as VARCHAR preserves the raw text but provides even less native structure than VARIANT or structured types. Every query or view would need to parse the text, increasing complexity and reducing reliability. This approach does not enforce schema at storage time and is generally less suitable for governed analytical datasets.
- D. Incorrect.
Incorrect. ARRAY is intended for ordered lists of elements, not named attributes such as loyalty_tier or marketing_opt_in. Using positional access for customer profile attributes would be hard to maintain, error-prone, and semantically inappropriate. It also would not model named business attributes as clearly as a structured OBJECT.