A data engineer needs to create a table in Snowflake to store JSON data from an external API. The API returns a nested JSON structure that is frequently updated with new fields. Which data type is MOS

Question

Options

• AVARCHAR with a size large enough to accommodate the largest expected JSON document.
• BVARIANT. Query efficiency is achieved using dot notation and FLATTEN table function, but
• COBJECT. Suitable if the JSON structure is strictly defined and known in advance, allowing for
• DARRAY. Best suited if the primary structure is an array of similar JSON objects, allowing for easy
• EBINARY. Suitable for storing raw JSON bytes. Requires custom conversion functions for querying,

• A
  10% (5)
• B
  70% (35)
• C
  14% (7)
• D
  4% (2)
• E
  2% (1)

Explanation

VARIANT is Snowflake's purpose-built semi-structured data type, designed specifically to store JSON, Avro, ORC, Parquet, and XML without requiring a predefined schema - making it ideal when an API adds new fields over time. You query it efficiently using dot notation (e.g., col:field.nested) and the FLATTEN function to explode arrays into rows, and Snowflake automatically builds micro-partition metadata to optimize these queries.

The distractors fail for these reasons: VARCHAR stores JSON as plain text with no native parsing, forcing expensive string manipulation on every query. OBJECT is too rigid - it requires a known, fixed schema, so new API fields would break it. ARRAY only fits if the top-level structure is a homogeneous array, which isn't guaranteed for arbitrary API responses. BINARY stores raw bytes with zero built-in JSON awareness, requiring manual conversion and offering no query optimization.

Memory tip: Think "VARIANT = variable structure" - it's the one type that bends to whatever shape your JSON takes, now or in the future. If the exam mentions "nested," "frequently updated," or "unknown fields," VARIANT is almost always the answer.

No community discussion yet for this question.