CERTIFIED-DATA-ANALYST-ASSOCIATE Question #5: Real Exam Question with Answer & Explanation

Question

A data engineer is working with a nested array column products in table transactions. They want to expand the table so each unique item in products for each row has its own row where the transaction_id column is duplicated as necessary. They are using the following incomplete command: Which of the following line of code can they use to fill in the blank in the above code block so that it successfully completes the task?

Options

• Aarray distinct(produces)
• Bexplode(produces)
• Creduce(produces)
• Darray(produces)
• Eflatten(produces)

Explanation

explode() is the correct function because it takes an array column and generates a new row for each element in the array, duplicating all other columns (like transaction_id) for each resulting row - exactly the "unnesting" behavior described.

Why the distractors are wrong:

• A. array_distinct() removes duplicate elements from an array but keeps the array as a single value - it doesn't expand rows.
• C. reduce() aggregates array elements into a single scalar value using a lambda function - the opposite of expanding.
• D. array() creates an array from individual values - it doesn't unpack one.
• E. flatten() collapses a nested array-of-arrays into a single array, but still returns one array per row, not individual rows.

Memory tip: Think of explode() literally - it "explodes" one row with an array into many rows, one per element. If you picture a row "blowing up" into multiple rows, that's your cue that explode() is the answer whenever a question asks about turning array elements into individual rows.

No community discussion yet for this question.