SOL-C01 Question #126: Real Exam Question with Answer & Explanation

Question

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 JSON array called 'readings'. You need to query this data to find the average of all 'readings' for a specific 'sensor _ id' for a given day. Which of the following approaches provides the MOST efficient query performance, assuming the 'readings' array can contain a large number of values?

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

Explanation

'LATERAL FLATTEN' is the recommended approach for efficiently querying data stored in JSON arrays. Exploding the array into rows allows you to leverage Snowflake's columnar architecture and aggregation functions for optimal performance. Option B is not suitable for variable number of readings and results in wide tables with nulls, harming performance. Option C and D are inefficient due to overhead of IJDF or Stored Procedure. Option E is less performant than Flatten because it requires multiple calls to GET PATH and struggles when the number of elements is

No community discussion yet for this question.