Home / DE Professional practice test / Data Transformation, Cleansing, and Quality

Free · 8 questions with explanations

Data Transformation, Cleansing, and Quality: Databricks Data Engineer Professional Practice Questions

Exam-style questions on Data Transformation, Cleansing, and Quality. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Updated November 16, 2026

1 Data Transformation, Cleansing, and Quality Medium

A Delta table sales contains category, product_id, and revenue. A report needs the top 3 products per category by total revenue, but must include ties (for example, if the 3rd and 4th products have the same revenue, both should appear). Which approach best satisfies the requirement?

  1. AAggregate revenue per (category, product_id), then compute dense_rank() over PARTITION BY category ORDER BY total_revenue DESC and filter dense_rank <= 3.
  2. BAggregate revenue per (category, product_id), then compute row_number() over PARTITION BY category ORDER BY total_revenue DESC and filter row_number <= 3.
  3. CSkip window functions: GROUP BY category and use ORDER BY total_revenue DESC LIMIT 3 to automatically return the top 3 per category.
  4. DUse rank() and filter rank <= 3; rank() never produces gaps, so it always returns exactly three rows per category.
Show answer & explanation

Correct answer: A

WHY A: dense_rank() assigns the same rank to ties and does not create gaps, so filtering dense_rank <= 3 includes all products tied within the top-3 ranks. WHY NOT B: row_number() breaks ties arbitrarily, which can exclude tied items that should be included. WHY NOT C: LIMIT 3 applies to the whole result set, not per category. WHY NOT D: rank() *does* produce gaps when there are ties, so rank <= 3 can return fewer than 3 ranks worth of data depending on ties; it also doesn’t guarantee exactly three rows.

2 Data Transformation, Cleansing, and Quality

A data engineer must deduplicate a Delta table of change events so that, for each account_id, only the record with the latest event_ts is kept; if two records share the same account_id and event_ts, the one with the higher ingest_seq wins, and exactly one row per account_id must remain. The engineer wants a single-pass, deterministic transformation. Which approach is correct and deterministic?

  1. AGroup by account_id and select max_by(struct(event_ts, ingest_seq), event_ts); max_by on event_ts alone returns the latest event, and ties are broken arbitrarily by Spark, which is acceptable because tied timestamps represent the same logical record.
  2. BDefine w = Window.partitionBy('account_id').orderBy(col('event_ts').desc(), col('ingest_seq').desc()), add row_number().over(w) as rn, and filter rn == 1; the compound ordering makes the winner deterministic and guarantees exactly one row per account_id.
  3. CDefine w = Window.partitionBy('account_id').orderBy(col('event_ts').desc()), add rank().over(w) as rk, and filter rk == 1; rank returns the top record per account and resolves ties by keeping all tied rows so no data is lost.
  4. DUse dropDuplicates(['account_id']) after sorting the DataFrame globally by event_ts descending and ingest_seq descending; the prior global sort guarantees dropDuplicates keeps the first (latest) row it encounters per account_id.
  5. EUse df.groupBy('account_id').agg(first('event_ts', ignorenulls=True)) with the DataFrame pre-sorted by ingest_seq; first after sorting returns the latest event deterministically because Spark preserves sort order through the aggregation.
Show answer & explanation

Correct answer: B

WHY B: A window partitioned by account_id ordered by event_ts descending then ingest_seq descending, with row_number() filtered to 1, keeps exactly one deterministic winner per account and correctly applies the tie-breaker. This is the canonical deterministic dedup pattern. WHY NOT A: max_by(event_ts) breaks ties arbitrarily, so the required ingest_seq tie-breaker is not applied deterministically; the assumption that tied timestamps are the same record contradicts the stated rule. WHY NOT C: rank() (and dense_rank()) return ALL rows tied at the top, so an account with tied event_ts yields more than one row, violating the 'exactly one row' requirement. WHY NOT D: dropDuplicates does not guarantee it keeps the first row of a prior sort — Spark does not preserve a global sort order through the shuffle that dropDuplicates performs, so the result is non-deterministic. WHY NOT E: Spark aggregations do not preserve input sort order, so first() after a sort is non-deterministic; first is not a reliable 'latest' selector.

3 Data Transformation, Cleansing, and Quality Easy

A Lakeflow Spark Declarative Pipelines (DLT) pipeline ingests customer records. The team wants to enforce that customer_id IS NOT NULL and age BETWEEN 0 AND 120. Invalid records should be excluded from the curated table (not written), but the pipeline should continue processing valid records and produce data quality metrics for how many were dropped. Which expectation action/operator is the best fit?

  1. AUse @dlt.expect('valid_customer', 'customer_id IS NOT NULL AND age BETWEEN 0 AND 120') because expect drops invalid rows by default and logs metrics.
  2. BUse a Python UDF inside the expectation clause to validate multiple fields; DLT expectations allow custom Python functions inside constraints.
  3. CUse @dlt.expect_or_fail('valid_customer', 'customer_id IS NOT NULL AND age BETWEEN 0 AND 120') so the update is rolled back and metrics are recorded for debugging.
  4. DUse @dlt.expect_or_drop('valid_customer', 'customer_id IS NOT NULL AND age BETWEEN 0 AND 120') so invalid rows are dropped before write and drop counts are logged.
Show answer & explanation

Correct answer: D

WHY D: expect_or_drop corresponds to the “drop on violation” behavior: invalid rows are dropped before writing, and the pipeline records metrics (including dropped counts) for observability. WHY NOT A: expect (warn/default) retains invalid records in the target while logging metrics; it doesn’t exclude them. WHY NOT C: expect_or_fail fails the update when invalid records are detected; in addition, when a flow fails, metrics are not recorded for that failed update in the same way as warn/drop. WHY NOT B: Expectation constraints must be valid SQL boolean expressions and do not allow custom Python functions or external calls in the constraint logic.

4 Data Transformation, Cleansing, and Quality

A data engineer joins a 4 TB events fact table to a 900 MB dim_customer dimension on customer_id. The query profile shows the join is executed as a sort-merge join with heavy shuffle, a few reducer tasks running far longer than the rest, and most time spent in shuffle write/read. The dimension easily fits in executor memory. On a modern Databricks Runtime with Adaptive Query Execution enabled, which single change most directly eliminates the shuffle bottleneck for this join?

  1. ARepartition both tables by customer_id before the join using .repartition('customer_id'), forcing co-located partitions so the sort-merge join no longer needs a shuffle exchange at join time.
  2. BIncrease spark.sql.shuffle.partitions to several thousand so the skewed reducer tasks are split across more partitions, which removes the long-running tasks by spreading the shuffled fact rows more evenly.
  3. CAdd a broadcast hint for the dimension (e.g., events.join(broadcast(dim_customer), 'customer_id')), converting the sort-merge join to a broadcast hash join so the small dimension is shipped to every executor and the large fact table is not shuffled on customer_id.
  4. DCache the dimension with dim_customer.cache() and call .count() to materialize it; a cached dimension is read from memory during the sort-merge join, eliminating the shuffle read cost on the dimension side.
  5. EEnable spark.sql.adaptive.skewJoin.enabled only and set a very low advisoryPartitionSizeInBytes; AQE will then broadcast the dimension automatically once it detects the skew, without any code change to the join.
Show answer & explanation

Correct answer: C

WHY C: When one side of the join comfortably fits in memory (900 MB), a broadcast hash join ships that side to every executor and avoids shuffling the 4 TB fact table on the join key entirely — removing the shuffle and the skewed sort-merge reducers. This is the most direct fix. WHY NOT B: More shuffle partitions can help even-sized skew somewhat but still shuffles the entire 4 TB fact table and does not address key-value skew where one customer_id dominates; it treats symptoms. WHY NOT A: Explicit .repartition('customer_id') still performs a full shuffle of both tables (the very cost you want to remove) and does not co-locate them for a shuffle-free join in Spark's execution model. WHY NOT D: Caching the dimension avoids re-reading it from storage but a sort-merge join still shuffles both sides on customer_id; caching does not change the join strategy or remove the shuffle. WHY NOT E: AQE skew-join splitting mitigates skewed partitions in a sort-merge join but does not convert it to a broadcast join automatically at 900 MB unless within the broadcast threshold; the explicit broadcast hint is the direct, reliable remedy here.

5 Data Transformation, Cleansing, and Quality Medium

A PySpark job defines the following window spec for cumulative metrics: w = Window.partitionBy('customer_id').orderBy('event_time') and then computes F.sum('amount').over(w) without calling rowsBetween or rangeBetween. Which window frame is used by default, and what is the correct way to compute a rolling sum over the last 7 rows per customer (row-based, not value/range-based)?

  1. ADefault: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Rolling 7 rows: keep the same spec; Spark automatically limits to the last 7 rows when an ORDER BY is present.
  2. BDefault: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when an ordering is defined. Rolling 7 rows: w7 = w.rowsBetween(-6, 0) and then F.sum('amount').over(w7).
  3. CDefault: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when an ordering is defined. Rolling 7 rows: w7 = w.rangeBetween(-6, 0) and then F.sum('amount').over(w7).
  4. DDefault: no frame is defined; Spark throws an error unless you specify either rowsBetween or rangeBetween. Rolling 7 rows: w7 = w.rowsBetween(-7, -1).
Show answer & explanation

Correct answer: B

WHY B: In PySpark, when ordering is defined, the default frame is a growing range frame from unbounded preceding to current row (rangeFrame, UNBOUNDED PRECEDING to CURRENT ROW). To get the last 7 rows (row-count based), you must explicitly specify a row frame like rowsBetween(-6, 0). WHY NOT A: With an ORDER BY, Spark does not default to a full-partition, unbounded-following frame, and it never auto-limits to 7 rows. WHY NOT C: rangeBetween(-6, 0) is range/value-based and depends on the ordering column’s value semantics; it’s not “last 7 rows”. WHY NOT D: Spark does not require an explicit frame; it chooses a default based on whether ordering is defined.

6 Data Transformation, Cleansing, and Quality Medium

A classic job uses Auto Loader to ingest JSON files where new fields can appear without notice and some fields occasionally arrive with type mismatches. The pipeline must keep running (no manual restarts), and unexpected fields must not be silently dropped. Which configuration best satisfies these requirements while minimizing custom state handling?

  1. ASet cloudFiles.schemaEvolutionMode='rescue' with a persistent cloudFiles.schemaLocation and checkpoint; new fields and type mismatches are kept in _rescued_data
  2. BSet badRecordsPath with a persistent cloudFiles.schemaLocation and checkpoint; new fields and type mismatches are kept in the bad records files for replay
  3. CSet cloudFiles.schemaEvolutionMode='addNewColumns' and remove the checkpoint; new fields are added to the target and type mismatches are cast to strings
  4. DSet an explicit schema with cloudFiles.schemaEvolutionMode='addNewColumns'; new fields are appended to that schema and type mismatches are written as nulls
Show answer & explanation

Correct answer: A

WHY A: In rescue mode, Auto Loader does not fail the stream due to new columns and does not ignore unexpected fields; instead, it records unexpected columns and type/case mismatches in the rescued data column (default _rescued_data), allowing the pipeline to continue and letting you quarantine or inspect rescued content later. A persistent cloudFiles.schemaLocation and a stable checkpoint are required for reliable incremental processing and schema tracking, so no custom state handling is needed. WHY NOT B: badRecordsPath stores malformed or corrupt records, but type mismatches and unexpected columns are handled by the rescued data column when it is configured; they are not necessarily treated as bad records, so this option confuses the corrupt-record sink with schema drift handling. WHY NOT C: Removing the checkpoint breaks exactly-once guarantees and can cause reprocessing, and the checkpoint is what lets Auto Loader track state without custom code. addNewColumns also stops the stream when a new column arrives (it updates the schema and the stream typically needs a restart), and capturing mismatched values is the job of the rescued data column, not of a cast to strings. WHY NOT D: When you provide an explicit schema, addNewColumns is not allowed in the same way (and defaults differ); you typically need schema hints or other handling. With an explicit schema and no rescued data column, mismatched values end up as nulls, which silently loses data the requirements say must be kept; this is not the keep-running configuration.

7 Data Transformation, Cleansing, and Quality

A data engineer must transform a long-format sales(region, product_category, amount) table into a wide report with one row per region and one summed-amount column per product_category. New product categories are added frequently, and the engineer wants the transformation to include every category present in the data at run time without editing the query each time a category is added. Which Spark approach produces the correct dynamic wide output?

  1. AUse df.groupBy('region').rollup('product_category').sum('amount'); rollup produces one subtotal column per distinct category value, giving the same wide layout as a pivot while also computing grand totals.
  2. BUse df.groupBy('region').pivot('product_category', ['electronics','apparel','home']).sum('amount') with the full category list hardcoded; an explicit list is required because pivot cannot determine the columns otherwise, and unlisted categories are summed into an other column.
  3. CUse df.groupBy('region', 'product_category').agg(sum('amount')) and then collect() the distinct categories on the driver to build the wide schema with a Python loop of withColumn calls, because pivot cannot create columns dynamically.
  4. DUse a SQL GROUP BY region with a CASE WHEN product_category = ... sum for each category; this is the only way to guarantee all categories appear because CASE expressions are evaluated per row regardless of new categories.
  5. EUse df.groupBy('region').pivot('product_category').sum('amount'); calling pivot without an explicit value list triggers a distinct scan of product_category, so all categories present at run time become columns automatically.
Show answer & explanation

Correct answer: E

WHY E: pivot('product_category') without an explicit value list makes Spark run a distinct scan of the pivot column, so every category present at run time becomes a column automatically — exactly the dynamic behavior required (at the cost of one extra scan). WHY NOT B: Hardcoding the value list is more efficient but defeats the requirement to pick up new categories automatically, and pivot does NOT bucket unlisted values into an other column — they are simply excluded. WHY NOT C: pivot CAN create columns dynamically; a driver-side collect plus manual withColumn loop is unnecessary and does not scale. WHY NOT D: A per-category CASE WHEN must enumerate categories in the query, so it must be edited whenever a new category appears — the opposite of dynamic. WHY NOT A: rollup computes hierarchical subtotals as extra rows, not one column per category; it does not reshape long to wide.

8 Data Transformation, Cleansing, and Quality

A data engineer builds a Lakeflow Spark Declarative Pipeline (Delta Live Tables) that loads a Silver transactions table. The requirement is: rows that fail a critical data-quality rule (amount > 0) must NOT enter the Silver table, but they must also NOT be lost — they must be routed to a separate quarantine table for investigation, and the pipeline must keep running rather than aborting the update. Which implementation meets all of these requirements?

  1. AApply @dp.expect_or_drop('valid_amount', 'amount > 0') to the Silver table, and define a second table that reads the same source with the inverted predicate (amount <= 0 OR amount IS NULL) as the quarantine table.
  2. BApply @dp.expect_or_fail('valid_amount', 'amount > 0') to the Silver table, and define a quarantine table that re-reads the failing rows recorded in the pipeline event log when the update stops on a violation.
  3. CApply @dp.expect('valid_amount', 'amount > 0') to the Silver table, and define a quarantine table that reads the system-managed _quarantine table where warn-level expectations write the rows they keep out of Silver.
  4. DAdd CONSTRAINT valid_amount EXPECT (amount > 0) ON VIOLATION DROP ROW to the Silver table, and set the pipelines.quarantine.autoCapture pipeline flag to mirror the dropped rows into a quarantine Delta table.
  5. EApply @dp.expect_all_or_drop({'valid_amount': 'amount > 0'}) to the Silver table, and define a quarantine table that reads the change data feed of dropped rows this decorator emits for the Silver table.
Show answer & explanation

Correct answer: A

WHY A: The standard quarantine pattern in Lakeflow Spark Declarative Pipelines is: put expect_or_drop (or an ON VIOLATION DROP ROW constraint) on the good table so failing rows are removed but the pipeline keeps running, and define a separate table that reads the same source with the inverted predicate (amount <= 0 OR amount IS NULL) to retain the bad rows. Valid rows reach Silver, invalid rows are preserved in quarantine, and nothing is lost. The @dp decorators come from pyspark.pipelines; the legacy dlt module exposes the same expectations. WHY NOT B: expect_or_fail aborts the pipeline update on the first violation, violating the 'keep running' requirement, and the event log records data-quality metrics, not the full offending rows for re-materialization. WHY NOT C: expect (warn) does NOT drop the row: warned rows still flow into the Silver table and are only counted in the metrics, and there is no automatic system _quarantine table. WHY NOT D: ON VIOLATION DROP ROW correctly drops without failing, but pipelines.quarantine.autoCapture is not a real configuration flag; capturing dropped rows requires the explicit inverted-predicate table. WHY NOT E: expect_all_or_drop applies a dictionary of expectations and drops rows that fail any of them; it does not emit dropped rows as a change data feed, so the quarantine table cannot be populated this way.

Take the full DE Professional practice test →