You have a JSON file stored in an internal stage containing customer data with varying schem

Question

Options

• ASome customers have address information stored as a nested JSON object, while others have it
• BUse the `LATERAL FLATTEN' function to explode the JSON and then use and to handle different
• CLoad the JSON data into a relational table and use a 'UNION ALL' query to combine records with
• DCreate two different views: one for customers with nested address objects and another for
• EUse to dynamically determine the column type and then apply appropriate conversion functions.
• FUse a stored procedure to iterate through each JSON record, determine the address format, and

• A
  60% (30)
• B
  6% (3)
• C
  2% (1)
• D
  2% (1)
• E
  8% (4)
• F
  22% (11)

Explanation

Note: The question and some answer choices appear truncated, but enough context remains to explain the core concept.

Option A correctly identifies the fundamental challenge: a varying schema where address data exists in multiple formats (nested object vs. flat/string). In Snowflake, the recommended solution is to store the JSON in a VARIANT column and use conditional expressions (IFF, CASE) combined with dot notation and functions like IS_OBJECT() or TRY_PARSE_JSON() to handle each format at query time - no restructuring required.

Why the distractors fail:

• B (LATERAL FLATTEN): Designed for exploding arrays into rows, not for resolving schema differences between field formats.
• C (UNION ALL on relational tables): Forces an upfront schema decision and adds unnecessary ETL complexity - defeats the purpose of semi-structured storage.
• D (Two views): Duplicates logic and maintenance burden; Snowflake's native VARIANT handling makes this redundant.
• E/F (Dynamic typing / stored procedures): Row-by-row procedural iteration breaks Snowflake's massively parallel set-based execution model, causing severe performance degradation at scale.

Memory tip: Think "VARIANT = flexible container." Whenever a Snowflake question involves JSON with inconsistent structure, the answer almost always involves VARIANT + conditional path access (IFF(IS_OBJECT(col:address), col:address:city, col:address)) rather than schema-rigid approaches like UNION ALL or procedural loops.

No community discussion yet for this question.