SOL-C01 · Question #123
You are inserting data into a table named 'EVENTS' which contains a column 'EVENT DATA' of type 'VARIANT'. The data being inserted comes from an external stage and is in JSON format. However, you are
The correct answer is B. Pre-process the JSON data outside of Snowflake to flatten or simplify the nested structures before E. Break down the INSERT operation into smaller batches, processing subsets of the data at a time.. Options B and E are correct because they directly address the root cause - excessive memory consumption from deeply nested JSON. Pre-processing the JSON outside Snowflake (B) reduces complexity before data ever enters the pipeline, eliminating the memory problem at its source. Br
Question
You are inserting data into a table named 'EVENTS' which contains a column 'EVENT DATA' of type 'VARIANT'. The data being inserted comes from an external stage and is in JSON format. However, you are encountering errors because some of the JSON documents contain deeply nested arrays and objects, leading to excessive memory consumption during the INSERT operation. Which of the following actions can you take to mitigate this issue and successfully load the data using the 'INSERT' command with data from a stage? Choose TWO.
Options
- AIncrease the size of the virtual warehouse to provide more memory for the INSERT operation.
- BPre-process the JSON data outside of Snowflake to flatten or simplify the nested structures before
- CUse = TRUE file format option for the stage.
- DUse the `VALIDATE function to identify problematic JSON documents and exclude them from the
- EBreak down the INSERT operation into smaller batches, processing subsets of the data at a time.
How the community answered
(28 responses)- A4% (1)
- B68% (19)
- C18% (5)
- D11% (3)
Explanation
Options B and E are correct because they directly address the root cause - excessive memory consumption from deeply nested JSON. Pre-processing the JSON outside Snowflake (B) reduces complexity before data ever enters the pipeline, eliminating the memory problem at its source. Breaking the INSERT into smaller batches (E) limits how much data Snowflake must hold in memory at once, allowing large datasets to load successfully without hitting memory limits.
Why the distractors are wrong:
- A - Scaling up the warehouse may help temporarily but is not a reliable fix; sufficiently complex nesting can exhaust even the largest warehouse, and it's wasteful rather than addressing the real problem.
- C -
STRIP_OUTER_ARRAY = TRUE(the likely intended option) removes a top-level JSON array wrapper, which can reduce parse overhead slightly, but it does not solve deep nesting or memory overload. - D -
VALIDATE()identifies format errors in staged files but cannot exclude problematic documents from anINSERToperation; it's a diagnostic tool, not a filtering mechanism.
Memory tip: Think "Fix it before it enters, or feed it in smaller bites." B = fix upstream, E = smaller portions - both reduce the memory burden Snowflake must handle at any one time. Scaling up (A) is tempting but just throws resources at a structural problem.
Topics
Community Discussion
No community discussion yet for this question.