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

Question

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 warehouse is consistently overloaded during the data loading process, and queries against the 'PRODUCT REVIEWS' table are slow after the data load. Which of the following strategies would BEST improve both the data loading performance and subsequent query performance, considering the use of INSERT statements?

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'

Explanation

Option C provides the most comprehensive solution. `LATERAL FLATTEN' efficiently transforms the nested JSON data during the insert process, improving query performance by eliminating the need to process JSON at query time. Using an appropriately sized warehouse and clustering the table on commonly queried fields further enhances query performance. Option A only addresses warehouse sizing but does not account for query optimization via flattening. Option B might help with resource allocation, but constructing JSON objects is not efficient. Option D does not use insert statement. Option E, turning on auto suspend will impact performance.

No community discussion yet for this question.