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

Perform deduplication and aggregate operations on DataFrames, such as count, approximate count distinct, mean and summary.

Remove duplicates with dropDuplicates or distinct and keep the latest record per key with a window function. Aggregate with groupBy and agg, and choose approx_count_distinct over an exact distinct count when the data is large and a small error is acceptable.

dropDuplicatesgroupBy and aggapprox_count_distinctwindow functionssummary statistics

Practice question for this objective

Free sampleData Transformation and Modelingmedium

An hourly dashboard reports the number of distinct visitor_id values in a clickstream table of roughly eight billion rows. The business has confirmed that an error of a few per cent in that figure is acceptable, and the platform team needs the query to finish inside the refresh window on the existing warehouse. Which aggregate should the engineer use?

AcountDistinct on visitor_id, since an exact figure is always preferable and the query optimiser removes the extra cost of the distinct pass automatically.
Bcount on visitor_id, which reports how many rows carry a value in that column and therefore reflects the size of the visiting audience.
Csize applied to collect_set of visitor_id, which gathers the unique identifiers first and then measures how many of them there are.
Dapprox_count_distinct on visitor_id, which builds a sketch of the key space and accepts an optional relative standard deviation to trade accuracy for speed. Correct

Use approx_count_distinct when a tolerated error buys a large cardinality count that an exact distinct pass could not deliver in time. approx_count_distinct estimates cardinality from a compact probabilistic sketch that merges cheaply across partitions, so its memory use does not grow with the number of distinct keys. An exact distinct count, and any approach that collects the keys themselves, must carry those keys through the shuffle, which is what makes them unsuitable at this scale.

Why A is wrong: It is tempting because it answers the same question exactly, but an exact distinct count must shuffle and hold the distinct keys, which is the cost the tolerance was granted to avoid.

Why B is wrong: count on a column returns the number of non null values rather than the number of distinct values, so a visitor with forty events is counted forty times and the figure is far too high.

Why C is wrong: collect_set does produce unique values, but it materialises every distinct identifier in memory before size is applied, which is heavier than an exact distinct count and risks failing the task outright.

Why D is correct: This is the aggregate designed for large cardinality estimates, and it keeps a small fixed size sketch per partition rather than the full set of keys, which is what makes it fit the refresh window.

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.

Sort the DataFrame by event_ts in descending order and then call bronze.dropDuplicates(["order_id"]), relying on the sort to fix which row survives.
Why it is wrong: This is tempting because the sort looks like it decides the winner, but dropDuplicates offers no ordering guarantee once the data is shuffled by order_id, so the surviving row for each key is arbitrary.
Apply dropDuplicates on a list holding order_id, which keeps one row per identifier out of the duplicates present in the batch.
Why it is wrong: Tempting because it does return one row per order_id, but the row it keeps is whichever the engine encounters first, so a superseded version can win and the correction is lost.
It returns an exact distinct count and gains its speed by pushing the distinct predicate down into the file scan, so the reported figure matches the one produced by an exact distinct count.
Why it is wrong: Tempting because predicate pushdown is a genuine optimisation, but it cannot make a distinct count both exact and cheap. The function trades exactness for a much smaller shuffle.

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