Data-Engineer-Associate - Data Transformation and Modeling (22% of the exam) - Section 3.3

Manipulate columns, rows and table structures by adding, dropping, splitting and renaming columns, applying filters, and exploding arrays.

Use withColumn, drop, withColumnRenamed, split and filter, and their SQL equivalents, to reshape data. Explode arrays into rows and predict the output, including how explode treats null or empty arrays compared with explode_outer.

withColumnwithColumnRenamedsplitexplodefilter

Practice question for this objective

Free sampleData Transformation and Modelingmedium

A DataFrame of orders holds order_id typed STRING and line_items typed ARRAY<STRING>. A data engineer explodes line_items to build a one row per item DataFrame and finds the output count lower than expected, because every order whose line_items array holds no elements has vanished. The requirement is that such an order still appears once, with a null item value. Which change meets it?

items = orders.select("order_id", explode("line_items").alias("item"))

ACall posexplode on line_items instead, because it returns the position of each element beside its value and therefore retains an input row whose array holds no elements.
BMove the explode call from a select into a withColumn call on line_items, because withColumn preserves every input row while select discards rows whose generated value is null.
CFilter the DataFrame on line_items being not null before the explode, so that an array holding no elements counts as present and survives the generator step downstream.
DCall explode_outer on line_items in place of explode, because it emits a single row carrying a null value for any input row whose array is empty or is itself null. Correct

explode drops input rows whose array is empty or null, while explode_outer keeps them with a single null value. A generator function produces one output row for each element of its input collection, so an array holding zero elements produces zero rows and the parent row disappears from the result. explode_outer changes that contract: when the input array is empty or null it emits exactly one row with null in the generated column, preserving the parent row and its other columns. This is the same distinction as an inner join against an outer join.

Why A is wrong: posexplode does add a position column, which makes it look like a richer generator, but it discards empty and null arrays in exactly the same way explode does, so the missing orders stay missing.

Why B is wrong: Where a generator is called does not change how it treats an empty array, and withColumn applies the same generator semantics, so the row count is identical either way.

Why C is wrong: A null check does not help because an empty array is not null, and filtering removes rows rather than adding them, so the orders in question are still dropped by the generator.

Why D is correct: explode_outer is the outer variant of the generator, defined to produce a single null valued output row rather than nothing when the input array has no elements, which is the required behaviour.

See more Data-Engineer-Associate practice questions, answers explained.

Exam traps in Data Transformation and Modeling

Answers that look right on this material and are not. Each one is a distractor from a different question in the Data-Engineer-Associate bank for this domain.

Each of those orders is kept once, with item holding an empty string, so the count of distinct order_id values in the result matches the source table.
Why it is wrong: An empty array feels like it should map to an empty string, which makes this tempting, but explode emits one row per element and an empty array has no elements to emit, so nothing is substituted.
The NULL values were rendered as the text "null" before the comparison ran, and that text does not match the predicate, so those rows were quietly discarded.
Why it is wrong: No implicit rendering of NULL to a string happens in a comparison, so the missing rows cannot be attributed to a text match failing.
Call na.drop with how set to "any", which removes a row as soon as a null appears in it.
Why it is wrong: Tempting because the requirement does say to drop rows containing nulls, but with no subset the rule covers every column and would discard the valid rows whose promo_code or notes is empty.

Examworthy is not affiliated with or endorsed by Databricks. Original, blueprint-aligned practice material only.