You are using Snowflake to store sensor data, which comes in the form of JSON. Each JSON document contains fields like `timestamp' , 'sensor_id' , and a variable number of readings stored within a…

Question

Options

• AUse LATERAL FLATTEN' to explode the readings' array into rows and then calculate the
• BLoad the data into a relational table with a separate column for each potential reading, based on
• CCreate a IJDF in Python to calculate the average directly from the JSON document.
• DUse a stored procedure to iterate through each JSON document and calculate the average.
• EUse the 'GET_PATH' function repeatedly within the query to extract each reading and then

• A
  61% (11)
• B
  6% (1)
• C
  6% (1)
• D
  22% (4)
• E
  6% (1)

Explanation

LATERAL FLATTEN is Snowflake's native, purpose-built mechanism for exploding semi-structured arrays into rows, and it executes within Snowflake's massively parallel processing (MPP) engine - meaning the work is distributed across nodes and the query optimizer can apply standard aggregations like AVG() efficiently, even on large arrays. Option B fails because assuming a fixed number of columns for a variable array is a schema anti-pattern that breaks when array lengths differ. Option C (a Python UDF) is wrong because UDFs run outside the core query engine, incurring serialization overhead that compounds badly at scale. Option D (a stored procedure with iteration) is row-by-row processing - it cannot leverage parallelism and is universally slower than set-based SQL for large datasets. Option E using repeated GET_PATH calls requires hardcoding array indices, cannot handle variable-length arrays, and similarly can't be parallelized.

Memory tip: In Snowflake, arrays in JSON = LATERAL FLATTEN. If you see "variable number of values" and "aggregate," that pairing always points to FLATTEN - it's the bridge between semi-structured data and Snowflake's parallel SQL engine.

No community discussion yet for this question.