You are responsible for optimizing the data loading process into a Snowflake table 'PRODUCT REVIEWS'. The source data is in JSON format and contains nested structures. You notice that the virtual ware

Question

Options

• AIncrease the size of the virtual warehouse used for data loading. Use INSERT statements to load
• BCreate a smaller virtual warehouse specifically for data loading, and a separate, larger warehouse
• CUse INSERT with SELECT statements to load the data and utilize the SLATERAL FLATTEN'
• DUse INSERT statements to load the data into a staging table with a VARIANT column. After the
• EUse INSERT with SELECT statements to load the data and utilize the `LATERAL FLATTEN'

• A
  3% (1)
• B
  14% (4)
• C
  62% (18)
• D
  3% (1)
• E
  17% (5)

Explanation

Option C is correct because LATERAL FLATTEN is Snowflake's native function for unpacking nested JSON arrays and objects into relational rows during the load itself - this means the data lands in a properly typed, flat structure that Snowflake's columnar engine can scan and prune efficiently, eliminating the need to parse JSON at query time. This single INSERT ... SELECT ... LATERAL FLATTEN operation solves both problems simultaneously: the warehouse isn't repeatedly re-parsing complex JSON on every query, and the load is handled in one optimized pass.

Why the distractors fail:

• A - Upsizing the warehouse treats the symptom (overload) not the cause; slow post-load queries remain because raw nested JSON still needs parsing.
• B - Splitting warehouses reduces resource contention but doesn't change the data structure, so query performance stays slow.
• D - Loading into a VARIANT staging column just defers the problem; VARIANT stores raw JSON as a semi-structured blob, and querying it is always slower than querying properly typed columns.
• E - Appears nearly identical to C but likely differs in a subtle but critical detail in the full (untruncated) option, making C the more complete or correct formulation.

Memory tip: Think "flatten at load, fast at query" - if you flatten the JSON once during INSERT using LATERAL FLATTEN, every downstream SELECT benefits for free. Deferring the flattening (VARIANT columns, bigger warehouses) just moves the pain later.

No community discussion yet for this question.