DEA-C02 Data Transformation Practice Question
A data engineer is building a transformation pipeline that must parse semi-structured log data stored in a VARIANT column named log_data. The JSON structure contains a nested array under the key 'events', and each element in the array has a 'timestamp' field. The engineer needs to produce one row per event with the timestamp extracted as a TIMESTAMP_NTZ value. Which SQL construct should be used to achieve this transformation efficiently?
⚠ Common exam trap
The trap here is assuming that GET_PATH or PARSE_JSON can directly explode arrays into rows, but they cannot; only FLATTEN with LATERAL performs that row multiplication.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use the FLATTEN function with the LATERAL keyword to explode the 'events' array, and access the 'timestamp' field using the colon notation, casting it to TIMESTAMP_NTZ.
The FLATTEN function with LATERAL is the standard Snowflake construct for exploding nested arrays in VARIANT columns. It produces one row per array element, and the colon notation allows direct access to nested fields. Casting the extracted value to TIMESTAMP_NTZ ensures the correct data type for downstream processing. This method is efficient and leverages Snowflake's native semi-structured data handling.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the FLATTEN function with the LATERAL keyword to explode the 'events' array, and access the 'timestamp' field using the colon notation, casting it to TIMESTAMP_NTZ.
Why this is correct
FLATTEN with LATERAL is designed to explode nested arrays in VARIANT data, producing one row per element. The colon notation accesses nested fields, and casting ensures the correct data type. This approach is efficient and standard for transforming semi-structured data in Snowflake.
- ✗
Use the GET_PATH function to directly extract the 'timestamp' field from the array without exploding it, relying on implicit casting to TIMESTAMP_NTZ.
Why it's wrong here
GET_PATH can extract a specific path but does not explode arrays; it would return an array of timestamps, not one row per event. Implicit casting of an array to TIMESTAMP_NTZ will fail. This does not meet the requirement of one row per event.
- ✗
Use the OBJECT_CONSTRUCT function to rebuild the JSON, then use a JavaScript UDF to iterate over the array and return each timestamp.
Why it's wrong here
OBJECT_CONSTRUCT creates a new JSON object, which is unnecessary here. A JavaScript UDF can process arrays but is less efficient than native SQL functions and adds complexity. For simple array explosion, FLATTEN is the optimal choice.
- ✗
Use the PARSE_JSON function to convert the VARIANT to a string, then use SPLIT_TO_TABLE to separate the array elements, and finally extract the timestamp with regular expressions.
Why it's wrong here
PARSE_JSON converts a string to VARIANT, not the other way around. SPLIT_TO_TABLE is for delimited strings, not JSON arrays. Regular expressions are error-prone and inefficient for structured data. This method is convoluted and not recommended for nested JSON transformation.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.