Skip to content
Cheat sheets

Data Engineer Associate · Domain 3: Data Transformation and Modeling · 22% of the exam

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

distinct and dropDuplicates, keeping the latest row per key, groupBy with agg, count and countDistinct, approx_count_distinct, avg, and describe and summary.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

The bronze table customer_updates holds several rows per customer_id, each with an updated_at timestamp. The silver table must keep exactly the most recent row, with all its columns, for each customer_id. Which approach does this?

  1. ASELECT DISTINCT * so that repeated rows for a customer collapse into one
  2. BNumber rows per customer_id by updated_at descending; keep row number 1
  3. CGROUP BY customer_id and select max(updated_at) along with the customer_id column
  4. DSort the table by updated_at descending and keep the first row with LIMIT 1
Show the answer and why

  • ASELECT DISTINCT * so that repeated rows for a customer collapse into one

    Incorrect

    DISTINCT removes only rows that are identical in every column; rows for the same customer with different timestamps remain.

  • BNumber rows per customer_id by updated_at descending; keep row number 1

    Correct

    row_number over a window partitioned by customer_id and ordered by updated_at DESC ranks the newest row first; filtering on 1, for example with QUALIFY, keeps it.

  • CGROUP BY customer_id and select max(updated_at) along with the customer_id column

    Incorrect

    This returns the latest timestamp per customer but drops the other columns of that row.

  • DSort the table by updated_at descending and keep the first row with LIMIT 1

    Incorrect

    This returns the single newest row in the whole table, not one row per customer.

Keeping the latest record per key is a window problem: rank the rows within each key by recency and keep rank 1.

Databricks documentation

Lesson: Window FunctionsTask 3.4Report a problem with this question

Question 2 · choose 1

A contacts DataFrame has several rows for some email addresses, with differences in other columns. The silver table needs one row per email, and any one of the rows for an email is acceptable. Which expression does this?

  1. Adf.distinct()
  2. Bdf.dropDuplicates(['email'])
  3. Cdf.groupBy('email').agg(count('*'))
  4. Ddf.dropna(subset=['email'])
Show the answer and why

  • Adf.distinct()

    Incorrect

    distinct removes only rows that are identical in every column, so rows for the same email with other differences stay.

  • Bdf.dropDuplicates(['email'])

    Correct

    dropDuplicates with a subset compares only the listed columns and keeps one row for each email.

  • Cdf.groupBy('email').agg(count('*'))

    Incorrect

    This returns each email with a count and drops all the other columns.

  • Ddf.dropna(subset=['email'])

    Incorrect

    This removes rows with a null email; duplicates remain.

dropDuplicates with a column subset deduplicates on a key, while distinct compares whole rows.

Databricks documentation

Task 3.4Report a problem with this question

Question 3 · choose 1

A dashboard shows unique visitors per day over billions of page-view rows. An estimate within about 5% is acceptable if the query runs much faster than an exact count. Which aggregate should the data engineer use?

  1. Acount_distinct('visitor_id')
  2. Bcount('visitor_id')
  3. Cdf.select('visitor_id').distinct().count()
  4. Dapprox_count_distinct('visitor_id')
Show the answer and why

  • Acount_distinct('visitor_id')

    Incorrect

    count_distinct gives the exact number, which costs more on very large data than the estimate the dashboard accepts.

  • Bcount('visitor_id')

    Incorrect

    count returns the number of non-null values, counting each visit, not unique visitors.

  • Cdf.select('visitor_id').distinct().count()

    Incorrect

    This is also an exact distinct count with the same full cost.

  • Dapprox_count_distinct('visitor_id')

    Correct

    approx_count_distinct estimates the distinct count with a default maximum relative standard deviation of 0.05.

approx_count_distinct trades a bounded error for speed on large data. Its rsd parameter sets the allowed error; below 0.01, an exact count_distinct is more efficient.

Databricks documentation

Task 3.4Report a problem with this question

Question 4 · choose 1

For a profiling report, a data engineer needs count, mean, standard deviation, minimum, the 25th, 50th and 75th percentiles, and maximum for the numeric columns of a DataFrame, in one call. Which method gives all of these?

  1. Adf.summary()
  2. Bdf.describe()
  3. Cdf.agg(avg('amount'), min('amount'), max('amount'))
  4. Ddf.printSchema()
Show the answer and why

  • Adf.summary()

    Correct

    By default summary computes count, mean, stddev, min, the 25%, 50% and 75% approximate percentiles, and max.

  • Bdf.describe()

    Incorrect

    describe returns count, mean, stddev, min and max, without the percentiles.

  • Cdf.agg(avg('amount'), min('amount'), max('amount'))

    Incorrect

    This computes three statistics for one column and none of the percentiles or the standard deviation.

  • Ddf.printSchema()

    Incorrect

    printSchema prints column names and types, not statistics.

describe gives the basic statistics; summary adds percentiles and lets you choose which statistics to compute.

Databricks documentation

Task 3.4Report a problem with this question

Question 5 · choose 2

A gold dataset needs total revenue and number of orders per region from the DataFrame orders(region, amount), with the result columns named revenue and order_count. Which TWO expressions produce it? (Choose TWO.)

  1. Aorders.agg(sum('amount').alias('revenue'), count('*').alias('order_count')).groupBy('region')
  2. Borders.groupBy('region').agg(sum('amount').alias('revenue'), count('*').alias('order_count'))
  3. Corders.select('region', sum('amount').alias('revenue'))
  4. Dspark.sql("SELECT region, sum(amount) AS revenue, count(*) AS order_count FROM orders GROUP BY region")
  5. Eorders.groupBy('region').sum('amount')
Show the answer and why

  • Aorders.agg(sum('amount').alias('revenue'), count('*').alias('order_count')).groupBy('region')

    Incorrect

    agg without groupBy collapses all rows into one first, so region is no longer available to group by.

  • Borders.groupBy('region').agg(sum('amount').alias('revenue'), count('*').alias('order_count'))

    Correct

    groupBy followed by agg computes several aggregates per group, and alias names the result columns.

  • Corders.select('region', sum('amount').alias('revenue'))

    Incorrect

    Mixing a plain column with an aggregate without groupBy is not a valid aggregation, and it lacks the order count.

  • Dspark.sql("SELECT region, sum(amount) AS revenue, count(*) AS order_count FROM orders GROUP BY region")

    Correct

    The SQL form with GROUP BY returns the same per-region aggregates and names, assuming orders is registered as a table or view.

  • Eorders.groupBy('region').sum('amount')

    Incorrect

    This computes only the revenue, under the default name sum(amount), and no order count.

Aggregations per group are groupBy plus agg in PySpark, or GROUP BY in SQL. Use alias or AS to give aggregate columns readable names.

Databricks documentation

Task 3.4Report a problem with this question

Practise domain 3 →Practise all domains →