COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A data engineer needs to transform a JSON column stored in a VARIANT type into a relational table. The JSON contains a top-level array of objects, each with keys `id`, `name`, and `tags`, where `tags` is itself an array of strings. The engineer wants each object to become a row, with the `tags` array flattened into a separate column containing one tag per row. Which combination of Snowflake functions will produce one row per tag while preserving `id` and `name`?
⚠ Common exam trap
Test-takers frequently confuse object-key extraction with array flattening; `OBJECT_KEYS` works on objects, while `FLATTEN` is required for arrays.
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
✓
`LATERAL FLATTEN(input => json_col:tags)` combined with `json_col:id::INT` and `json_col:name::STRING` in the SELECT list.
To explode a nested array within a VARIANT column, `LATERAL FLATTEN` is the correct tool. It takes an array as input and returns one row per element, while the lateral join keeps the original row's other columns accessible. Referencing `json_col:id` and `json_col:name` in the SELECT list preserves those attributes. Other functions either treat the array as a string, operate on object keys, or flatten the wrong level of the JSON structure, so they do not produce the required one-row-per-tag output.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
`ARRAY_TO_STRING(json_col:tags, ',')` followed by `SPLIT_TO_TABLE` on the resulting string.
Why it's wrong here
`ARRAY_TO_STRING` concatenates array elements into a single delimited string, which loses the individual row structure until `SPLIT_TO_TABLE` re-splits it. This two-step process is unnecessarily complex and can break if any tag contains the delimiter. It also requires an extra parsing step and may not preserve the association with `id` and `name` correctly if the string is empty or contains special characters. It is not the idiomatic Snowflake approach.
- ✗
`OBJECT_KEYS(json_col)` to extract the array elements, then `GET` to access each tag.
Why it's wrong here
`OBJECT_KEYS` returns the keys of a JSON object, not the elements of an array. Applying it to an array would not produce the tag values. Additionally, `GET` is used to retrieve a value by key from an object, not to iterate an array. This combination would fail to flatten the tags array and would not produce one row per tag, making it unsuitable for the transformation.
- ✓
`LATERAL FLATTEN(input => json_col:tags)` combined with `json_col:id::INT` and `json_col:name::STRING` in the SELECT list.
Why this is correct
`LATERAL FLATTEN` is designed to explode an array into multiple rows. When applied to `json_col:tags`, it produces one row per element of the tags array. The outer query can still reference `json_col:id` and `json_col:name` because the lateral join preserves the original row context. This yields exactly one row per tag with the corresponding id and name, which matches the requirement.
- ✗
`PARSE_JSON` on the VARIANT column followed by `FLATTEN` on the entire JSON object.
Why it's wrong here
`PARSE_JSON` is used to convert a string into a VARIANT, but the column is already VARIANT, so this step is redundant. More importantly, flattening the entire JSON object rather than the specific `tags` array would produce rows for all top-level keys, not just the tags. This would not yield one row per tag and would mix unrelated data, failing the requirement to flatten only the tags array while preserving id and name.
About these practice questions
This COF-C03 question is part of Courseiva's 280-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. 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 COF-C03 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 COF-C03 exam.