You are tasked with loading JSON data containing customer information into Snowflake. The JSON structure is complex and varies across records. You want to optimize query performance on a frequently ac

Question

Options

• ALoad the JSON data directly into a VARIANT column without any transformation.
• BCreate a separate table with a VARCHAR column for 'address.city' and use a view to join it with
• CCreate a virtual column on the VARIANT column that extracts 'address.city' and index the virtual
• DFlatten the JSON data during loading using a query that extracts 'address.city' into a separate
• EUse the LATERAL FLATTEN function to create a separate row for each field in the JSON,

• A
  14% (7)
• B
  8% (4)
• C
  28% (14)
• D
  46% (23)
• E
  4% (2)

Explanation

Flattening 'address.city' into a dedicated typed column at load time is the best strategy because Snowflake's columnar storage and micro-partition pruning work most efficiently on native types (VARCHAR, NUMBER, etc.) rather than semi-structured VARIANT data - every query against a VARIANT column must parse the JSON at runtime, whereas a pre-extracted column is instantly scannable.

Why the distractors fail:

• A - Storing raw JSON in VARIANT defers parsing to query time on every execution, which is the performance problem this question is trying to solve.
• B - A join between two tables adds overhead; while it technically isolates the city column, it requires a join operation on every query, which is worse than a single flat table.
• C - Snowflake does not support traditional indexes. It uses micro-partition-based pruning, so "index the virtual column" is a fabricated concept in this context and won't help.
• E - LATERAL FLATTEN explodes a nested array into rows; it's a query-time tool for arrays, not a storage optimization strategy for a scalar field like 'address.city'.

Memory tip: Think "pay once, benefit many" - flatten on ingest so the parsing cost is paid once during loading, not on every SELECT. If a field is frequently accessed, it earns its own column.

No community discussion yet for this question.