Data-Engineer-Associate domain - 22% of the exam

Data Transformation and Modeling

Data Transformation and Modeling is 22% of the Data-Engineer-Associate exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

66
Practice questions
7
Objectives
48
Medium
18
Hard

Where people go wrong

  • Assuming union matches columns by name

    The wrong answers treat DataFrame union as a name-based operation, or expect unionByName to repair any schema mismatch. union and its alias unionAll resolve columns by position, so a different column order silently places region values under order_id with no error. unionByName matches by name, which fixes the order, but by default it raises an analysis error when a column is absent. Passing allowMissingColumns as true fills the absent column with nulls, which removes that loud failure.

    See it in a sample question
  • Expecting a deduplication call to keep the latest row

    The wrong answers expect dropDuplicates, distinct or a bare aggregate to keep the newest record. dropDuplicates on a subset of columns leaves one row per key, but which duplicate survives is not guaranteed. distinct removes only rows identical in every column, so corrections with different timestamps all remain. Selecting the latest version needs row_number over a window partitioned by the key and ordered by the timestamp descending, then a filter on rank one, which also keeps every other column of the winning row.

    See it in a sample question
  • Choosing the gold object that does not fit the source

    The wrong answers match the gold object to the wrong source behaviour. A view stores only its query, so every read rescans the silver tables. A streaming table processes each source row once and expects an append-only source, so updates and deletes in silver make its refresh fail. A materialized view stores the result and follows a changing source, with a refresh that can be incremental. A hand-written rebuild job rewrites the full history on every run.

    See it in a sample question
  • Treating a CHECK constraint as a pipeline expectation

    The wrong answers swap the two quality mechanisms. A CHECK constraint belongs to the Delta table and is enforced on every write, so one violating row aborts the whole transaction and nothing is committed. It cannot drop individual rows or divert them to a quarantine table. An expectation in a Lakeflow Spark Declarative Pipelines pipeline governs only the datasets that pipeline builds, with actions such as DROP ROW. A quarantine table has to be declared by hand as a second dataset.

    See it in a sample question
  • Confusing the two parallelism settings

    The wrong answers treat spark.default.parallelism as the setting that sizes a DataFrame shuffle, or as a way to change a join strategy. spark.sql.shuffle.partitions sets the partition count for shuffles in DataFrame and SQL operations, and defaults to 200. spark.default.parallelism applies to RDD operations such as reduceByKey, so a DataFrame join or aggregation ignores it. Neither setting changes which join strategy the planner picks; that follows from the broadcast threshold or a broadcast hint.

    See it in a sample question

Objectives in this domain

What to study

The official documents this domain's practice questions cite most.

Sample questions from this domain

Free sampleData Transformation and Modelingmedium

A data engineer holds two DataFrames of the same width but with their columns declared in a different order: daily_north has columns order_id and region, while daily_south has columns region and order_id, both typed STRING. The engineer combines them with the call below and inspects the result. Which statement describes what the result contains?

combined = daily_north.union(daily_south)
combined.show(5)
  • AThe rows are aligned by column name, so every region value lands under region and every order_id value lands under order_id in the combined result.
  • BThe call raises an analysis error, because the two DataFrames declare the same column names in a different order and Spark refuses to resolve the union.
  • CThe rows are combined by column position, so the region values from daily_south land in the order_id column of the result and its order_id values land in region. Correct
  • DThe rows are combined by column position and duplicate rows are then removed, so the result holds fewer rows than the two input DataFrames hold together.
DataFrame union stacks rows by column position, not by column name, and keeps duplicate rows. The union method on a DataFrame is defined positionally: it validates that both sides have the same number of columns with compatible types, then stacks the nth column of one under the nth column of the other. Column names on the right side are discarded, so mismatched declaration orders produce values in the wrong columns with no error. Name based stacking requires unionByName instead.

Why A is wrong: This is tempting because SQL practitioners think of a union as a set operation over named columns, but the DataFrame union method resolves columns by position and ignores their names entirely.

Why B is wrong: An analysis error is a reasonable guess given how strict Spark is about schemas, but union checks only the number of columns and the compatibility of the types, which both hold here, so the query runs.

Why C is correct: Correct: DataFrame union is a positional operation, so the first column of the second DataFrame is stacked under the first column of the first, producing silently transposed values when the orders differ.

Why D is wrong: The positional half of this is right, but the DataFrame union method behaves like SQL UNION ALL and keeps duplicates; removing them requires an explicit call to distinct or dropDuplicates.

Free sampleData Transformation and Modelingmedium

A bronze Delta table holds click events in which the same event_id appears several times because a producer retried its deliveries. The duplicate rows agree on event_id but differ in their ingested_at timestamps. A data engineer runs the PySpark code below and then inspects the result. Which statement describes what the deduped DataFrame contains?

df = spark.read.table("bronze.click_events")
deduped = df.dropDuplicates(["event_id"])
  • ANo rows are removed at all, because a row is treated as a duplicate when every column in the DataFrame matches, and the differing ingested_at values make each row unique.
  • BOne row survives for each event_id, and which of the duplicate rows survives is not guaranteed, so a window ranking on ingested_at is required if the latest row must be the one kept. Correct
  • CThe row carrying the greatest ingested_at value is retained for each event_id, because the method orders the group by the columns absent from the subset before discarding the rest.
  • DThe statement fails with an analysis error, because a subset passed to the method must name every column of the DataFrame for the duplicate comparison to be well defined.
Deduplicating on a subset of columns keeps one arbitrary row per key, so a ranking window is needed for latest-record semantics. When a column list is supplied, duplicate detection considers those columns alone, so each event_id group is reduced to a single surviving row. The engine makes no promise about which member of the group survives, because that depends on partitioning and task ordering rather than on any sort. Deterministic behaviour such as keeping the newest record therefore has to be expressed with a ranking function over a window partitioned by the key and ordered by the timestamp.

Why A is wrong: Tempting because that is exactly the behaviour of a call with no column list, which compares whole rows. Here a subset was supplied, so comparison is restricted to event_id and duplicates are removed.

Why B is correct: Correct: passing a subset of columns collapses each event_id group to a single row, but the method gives no ordering guarantee about which duplicate is retained, so deterministic selection needs a ranking window.

Why C is wrong: Tempting because keeping the newest record is usually the intent, but no such ordering step exists in the method. Latest-record semantics have to be written explicitly with a ranking window.

Why D is wrong: Tempting for a candidate who has only seen the whole-row form, but naming a subset of columns is supported and is the usual way to deduplicate on a business key.

Free sampleData Transformation and Modelinghard

An analytics team publishes a gold object in Unity Catalog as a view over a large silver Delta table. Dashboard queries against the view take as long as the same query issued directly against the silver table, and the object consumes no storage in the catalog. Which statement explains the behaviour?

CREATE VIEW gold.sales.orders_enriched AS
SELECT o.order_id, o.order_total, c.region
FROM silver.sales.orders_clean o
JOIN silver.sales.customers c USING (customer_id)
  • AA view stores a copy of its result in Unity Catalog storage and refreshes that copy whenever the underlying silver tables change, so the cost seen here is the refresh rather than the read.
  • BA view registers only its query definition in Unity Catalog, so each dashboard query runs the underlying join against the silver tables at read time and no result rows are stored. Correct
  • CA view stores its result only after the first successful query, so the first read is slow and every later dashboard read is served from the stored rows in the catalog.
  • DA view keeps its rows in the SQL warehouse result cache rather than in catalog storage, so the rows are discarded whenever the warehouse restarts and every query pays a full scan again.
A view stores only its query definition, so every read re-executes the underlying query and the object consumes no storage. Unity Catalog persists a view as a named query plus its resolved schema. Reading the view substitutes that query into the caller's statement, so the work performed is the underlying scan and join, which is why timings match the direct query and no bytes are stored.

Why A is wrong: Tempting because it describes a real gold object, but that object is a materialized view; a plain view stores no result copy and has no refresh of its own to charge the query for.

Why B is correct: Correct: the catalog holds the view text and its schema, and every read substitutes that query, which is exactly why the runtime matches the direct query and why the object occupies no storage.

Why C is wrong: Tempting because it sounds like lazy caching, but Unity Catalog never populates a view with rows on first read; the second and every later query re-runs the same underlying join.

Why D is wrong: Tempting because warehouse result caching is real and does reset on restart, but it is a query-level cache that applies to any statement, not the storage model of a view, and it is not why the view holds no data.

Free sampleData Transformation and Modelingmedium

A silver Delta table in Unity Catalog carries the constraint added by the statement below. A nightly batch job then appends 10,000 rows in one write, of which 3 carry a negative order_total. Which statement describes the outcome of that write?

ALTER TABLE silver.orders ADD CONSTRAINT positive_total CHECK (order_total > 0)
  • AThe 3 offending rows are discarded and the remaining 9,997 rows are committed, with the discarded count reported against the named constraint.
  • BThe write fails with a constraint violation error and none of the 10,000 rows are committed, because the append is a single transaction that is rejected as a whole. Correct
  • CAll 10,000 rows are committed and the 3 offending rows are flagged in the table history so that an engineer can find and correct them afterwards.
  • DThe 3 offending rows are committed with their order_total rewritten to null, so that the constraint expression is satisfied by the values actually stored.
A Delta CHECK constraint rejects the whole write transaction on any violating row rather than dropping or repairing that row. CHECK constraints are enforced by the Delta protocol during commit, and a Delta write is atomic, so one failing row invalidates the entire transaction. That is the key difference from a pipeline expectation, which evaluates each record independently and can warn on it or drop it while still publishing the rest.

Why A is wrong: This is the behaviour of a pipeline expectation using ON VIOLATION DROP ROW, not of a table CHECK constraint, which has no facility for dropping individual rows.

Why B is correct: Correct. A CHECK constraint is enforced at write time and a violation aborts the transaction, so the table is left exactly as it was before the append began.

Why C is wrong: Tempting because Delta history is detailed, but history records operations rather than individual bad values, and a CHECK constraint does not permit a violating row to be committed at all.

Why D is wrong: A CHECK constraint never edits data on the way in; it only accepts or rejects the write, and substituting values silently would hide the very defect the constraint exists to catch.

Free sampleData Transformation and Modelinghard

A daily job joins two large Delta tables on a cluster with 32 cores in total. An engineer wants to control how many partitions the join's shuffle produces and finds two configuration settings whose names both suggest parallelism. Which statement correctly distinguishes the two for this DataFrame job?

spark.conf.set("spark.sql.shuffle.partitions", 128)
spark.conf.set("spark.default.parallelism", 128)
  • Aspark.default.parallelism sets the partition count produced by the join shuffle, while spark.sql.shuffle.partitions applies to lower level RDD operations such as reduceByKey.
  • BBoth settings are consulted for the join shuffle, and the planner uses whichever of the two carries the larger value at the moment the stage is submitted for execution.
  • Cspark.sql.shuffle.partitions sets the partition count produced by shuffles in DataFrame and SQL operations, while spark.default.parallelism governs raw RDD shuffles and the default partitioning of RDD sources. Correct
  • Dspark.sql.shuffle.partitions takes effect only when adaptive query execution is switched off, so on a cluster with adaptive execution active the engineer has to use spark.default.parallelism instead.
The shuffle partition count for DataFrame and SQL work comes from spark.sql.shuffle.partitions, not from spark.default.parallelism. Spark keeps two separate defaults. The SQL planner inserts an exchange whose post shuffle partition count is taken from spark.sql.shuffle.partitions, so every DataFrame join, aggregation and window shuffle is sized by that value. spark.default.parallelism is the RDD API default, used for operations such as reduceByKey and for the default partition count of parallelised collections, and changing it leaves a DataFrame join unaffected.

Why A is wrong: This is tempting because the word parallelism sounds like the more general setting and therefore the more authoritative one, but the two roles are reversed: the SQL setting is the one that governs a DataFrame join shuffle.

Why B is wrong: This is tempting because setting both values is a common defensive habit, but no rule combines them: the DataFrame path reads only the SQL setting, and the RDD path reads only the other.

Why C is correct: Correct: a join expressed through the DataFrame or SQL API is planned by the SQL engine, which reads spark.sql.shuffle.partitions for the post shuffle partition count, whereas spark.default.parallelism is the RDD level default.

Why D is wrong: This is tempting because adaptive query execution does change the final partition count, but it starts from the configured SQL value and merges partitions downward rather than ignoring the setting or deferring to the RDD default.

Other domains in this exam

See also the Data-Engineer-Associate cert hub, the study guide, and the cheat sheet.

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