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

Question

A Snowflake table named 'ORDERS' contains columns: `ORDER ID (NUMBERY, `CUSTOMER ID (NUMBERY, 'ORDER DATE (DATE), and `ORDER DETAILS (VARIANT)'. The 'ORDER DETAILS column stores JSON data. You need to insert data into this table from a CSV file. However, the CSV file only contains data for 'ORDER ICY, `CUSTOMER ICY, and 'ORDER DATE. The 'ORDER DETAILS' column should be populated with a default JSON object containing a status of 'PENDING' for all new orders. Which SQL INSERT statement is the most efficient and correct way to achieve this? A. B. C. D. E.

Options

• AINSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_DETAILS) SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, {'status': 'PENDING'} FROM @my_stage/orders.csv (FILE_FORMAT => 'my_csv_format');
• BINSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_DETAILS) SELECT $1, $2, $3, PARSE_JSON('{"status": "PENDING"}') FROM @my_stage/orders.csv (FILE_FORMAT => 'my_csv_format');
• CINSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE) SELECT $1, $2, $3 FROM @my_stage/orders.csv (FILE_FORMAT => 'my_csv_format');
• DINSERT INTO ORDERS SELECT $1, $2, $3, '{"status": "PENDING"}' FROM @my_stage/orders.csv (FILE_FORMAT => 'my_csv_format');

Explanation

Option B is the most efficient and correct. OBJECT CONSTRUCT directly creates a JSON object within Snowflake, which is more efficient than parsing a string using 'PARSE_JSONZ SELECT statement with stages usually use '$1 , $2, $3' notation for accessing the CSV fields. Options C uses VALUES' clause which does not integrate well with @my_stage'. Option D and E is trying to cast to VARIANT but uses wrong casting notations.

No community discussion yet for this question.