Databricks Deduplication, Aggregations and Spark Tuning
Introduction
This lesson continues the Data Transformation and Modeling section of the official exam outline (about 22% of the scored questions). The PySpark and SQL cleaning lesson prepared typed silver data. This lesson develops Databricks deduplication and aggregation patterns for the Data Engineer Associate certification. Now you remove duplicates, compute summary statistics, and learn the handful of Spark settings that the exam expects you to recognize: shuffle partitions, parallelism, memory and the broadcast threshold. The exam does not ask you to be a performance engineer. It asks whether you know which setting matches which symptom, and whether you measure before and after a change.
After this lesson you will be able to choose between dropDuplicates and a window function, compute aggregates and profiles with count, approx_count_distinct, mean, summary and describe, and name the effect of the main Spark configuration properties.
Learning objectives
- Remove exact and key-based duplicates with
distinct,dropDuplicatesandROW_NUMBER()windows, and keep the latest record per key. - Compute aggregates with
groupBy/GROUP BY, and explain how null values affectcount. - Use
approx_count_distinct,describeandsummaryfor fast data profiling. - Explain the roles of
spark.sql.shuffle.partitions,spark.default.parallelism,spark.executor.memory,spark.driver.memoryandspark.sql.autoBroadcastJoinThreshold. - Describe how adaptive query execution (AQE) and auto-optimized shuffle reduce manual tuning.
- Apply a disciplined measure, change one setting, re-measure workflow.
1. Deduplication
Duplicates appear whenever sources retry, files are re-delivered, or change feeds contain several versions of a record. There are two different problems, and the exam checks that you can tell them apart.
| Problem | Meaning | Tool |
|---|---|---|
| Exact duplicates | Entire rows are identical | distinct(), dropDuplicates() with no argument, SELECT DISTINCT * |
| Key duplicates | Several rows share a business key but differ in other columns (for example an older and a newer version) | dropDuplicates(["key"]) (arbitrary row kept) or a window function (you choose which row is kept) |
distinct and dropDuplicates
orders = spark.read.table("silver.orders")
exact = orders.dropDuplicates() # same as distinct()
by_key = orders.dropDuplicates(["order_id"]) # one row per order_idSELECT DISTINCT * FROM silver.orders;dropDuplicates(["order_id"]) keeps one row per key, but it does not promise which one. If rows for the same order_id have different updated_at values, the survivor can be the old version. When "keep the latest" matters, use a window.
Keeping the latest record with a window function
ROW_NUMBER() numbers rows inside each window partition. Keep row 1 using a complete ordering. Here updated_at is a parsed timestamp, ingest_id is unique per customer version, and larger ingest ids are the agreed winner when timestamps tie. Reject or quarantine null business keys before ranking; a null key is not a valid customer identity.
CREATE OR REPLACE TABLE silver.customers AS
SELECT * EXCEPT (rn)
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, ingest_id DESC) AS rn
FROM bronze.customers_raw
WHERE customer_id IS NOT NULL
)
WHERE rn = 1;from pyspark.sql import Window, functions as F
bronze = spark.read.table("bronze.customers_raw").filter("customer_id IS NOT NULL")
w = Window.partitionBy("customer_id").orderBy(
F.col("updated_at").desc(), F.col("ingest_id").desc())
latest = (bronze.withColumn("rn", F.row_number().over(w))
.filter("rn = 1")
.drop("rn"))Two details are tested often. First, RANK() and DENSE_RANK() give the same rank to ties, so WHERE rank = 1 can return two rows for one key when two records share the latest timestamp; ROW_NUMBER() never ties. Second, add a tie-breaker to the ordering (for example updated_at DESC, ingest_id DESC) so the result is deterministic.
Concrete example. A retailer's CRM sends customer changes. Customer 8172 appears three times: address changes on 3 March, 9 March and 9 March (same day, two corrections, ingest ids 9001 and 9002). dropDuplicates(["customer_id"]) might keep the 3 March row. A window with ORDER BY updated_at DESC, ingest_id DESC keeps ingest 9002 under that tie-breaking contract. Arrival order alone does not establish which business change is latest; prefer a source change sequence when available.
Deduplicating streams
A streaming deduplication retains keys across micro-batches. Without an effective watermark, state can grow without bound. In Databricks Runtime 13.3 LTS and later, events.withWatermark("event_ts", "1 hour").dropDuplicatesWithinWatermark(["event_id"]) bounds state without putting the timestamp in the duplicate key. Choose the delay from the maximum event-time difference between duplicate copies and the lateness contract. This is bounded deduplication, not a permanent uniqueness guarantee for arbitrarily late events.
For persistent key reconciliation, an idempotent MERGE can help, but first reduce each source batch to one winner per key and handle retries explicitly. A merge is not an automatically enforced unique-key constraint. See Auto Loader checkpoints and replay behavior to distinguish file ingestion guarantees from duplicate business events.
2. Aggregations
An aggregation collapses many rows into one per group.
SELECT country,
count(*) AS rows_in_group,
count(country) AS rows_with_country,
count(DISTINCT customer_id) AS buyers,
round(avg(amount), 2) AS avg_amount,
sum(amount) AS revenue
FROM silver.orders
GROUP BY country;orders = spark.read.table("silver.orders")
(orders.groupBy("country")
.agg(F.count("*").alias("rows_in_group"),
F.count("country").alias("rows_with_country"),
F.countDistinct("customer_id").alias("buyers"),
F.round(F.mean("amount"), 2).alias("avg_amount"),
F.sum("amount").alias("revenue")))Know these behaviors:
count(*)counts rows.count(col)counts rows wherecolis not null. This difference is a favorite question.avg/mean,sum,minandmaxignore nulls. If every value is null,sumandavgreturn null, not zero.- Every column in the
SELECTlist must be either inGROUP BYor inside an aggregate function. HAVINGfilters groups after aggregation;WHEREfilters rows before it.
Approximate distinct counts
An exact distinct count can require substantial distributed aggregation and shuffle. approx_count_distinct uses a HyperLogLog++ sketch with a default relative standard deviation (RSD) of 0.05. An explicit RSD such as 0.02 requests a tighter statistical estimate at greater sketch cost. RSD is not a hard maximum error for every result; measure performance and compare representative results with exact counts.
SELECT approx_count_distinct(user_id, 0.02) AS approx_users FROM bronze.clickstream;Use an estimate for dashboards whose owners accept statistical uncertainty. If a contract requires an exact number or a strict error cap that this estimate cannot establish, compute the exact count. Both distinct-count functions ignore null identifiers; define how missing identifiers affect the report.
Profiling with describe and summary
df.describe() returns count, mean, stddev, min and max for numeric and string columns. df.summary() returns the same plus quartiles (25%, 50%, 75%) by default, and you can choose statistics, for example df.summary("count", "min", "max", "75%"). In notebooks, display(df.summary()) can support this investigation; the built-in data profile tab provides a separate interface whose displayed statistics can differ. These are quick tools to spot nulls (a count lower than the table size), outliers (a max far from the 75% quartile) and wrong types.
3. Basic Spark tuning
Spark splits data into partitions, and each partition is processed by a task. Operations like joins and aggregations need a shuffle: data is redistributed across the cluster by key, which is expensive because it involves network and disk. Most basic tuning is about the number and size of partitions and about memory.
| Property | What it controls | Default | Typical symptom and action |
|---|---|---|---|
spark.sql.shuffle.partitions |
Number of partitions after a shuffle in DataFrame/SQL joins and aggregations | 200 in the documented classic/AQE configuration; serverless notebooks/jobs default to auto |
Many tiny tasks on small data: lower it. Huge tasks that spill to disk on large data: raise it, or use auto |
spark.default.parallelism |
Default partition count for RDD operations such as parallelize and RDD shuffles |
Total cores in the cluster (typically) | Does not set the SQL shuffle partition count; use the SQL setting for that stage |
spark.executor.memory |
Heap memory per executor | Set by the node type on Databricks | Out-of-memory errors and spill in executors |
spark.driver.memory |
Heap memory of the driver | Set by the node type | Driver out of memory after collect() or large toPandas() |
spark.sql.autoBroadcastJoinThreshold |
Maximum size of a table that Spark broadcasts automatically in a join | 10 MiB (open-source default; inspect the actual runtime setting) | Raise it for a medium dimension table; set to -1 to disable automatic broadcasting through this threshold |
On Databricks, executor and driver memory come from the compute you select, and cluster-level properties are set in the compute's Spark configuration. Notebook-level settings use spark.conf.set("spark.sql.shuffle.partitions", "64"), where supported, which affects the current session. Driver/executor heap sizes are compute-startup settings, not session knobs that resize a running cluster. On serverless compute and SQL warehouses, most of these properties are managed for you and only a small allowlist can be set.
Shuffle partitions versus default parallelism
This pair is a classic trap. spark.sql.shuffle.partitions governs DataFrame and SQL shuffles (groupBy, join, orderBy). spark.default.parallelism governs RDD operations. Changing RDD parallelism does not directly set the partition count of the SQL aggregation shuffle. A possible starting heuristic for DataFrame work is to aim for shuffle partitions of roughly 100 to 200 MB each; with 200 partitions, about 20 to 40 GB of shuffled data fits that range.
Broadcast threshold
For a supported join type, the optimizer can broadcast a side whose estimated size is below spark.sql.autoBroadcastJoinThreshold, avoiding a shuffle join. Statistics and join semantics influence the plan. Raising the threshold too far risks driver and executor memory pressure, because the broadcast table is first collected on the driver. Databricks also applies adaptive execution at runtime and can convert a sort-merge join to a broadcast join when the real size after filtering is small, using a separate threshold (30 MB by default for the adaptive conversion). A broadcast(df) hint requests broadcasting, but unsupported join types can prevent it. Check the physical plan and memory capacity instead of treating the hint as a guarantee.
Adaptive query execution and auto-optimized shuffle
Adaptive query execution (AQE) is enabled by default for supported non-streaming queries on Databricks. It re-plans while the query runs, using real statistics: it coalesces small shuffle partitions, switches join strategies, and splits skewed partitions in shuffle joins. Setting spark.sql.shuffle.partitions to auto lets Databricks choose the partition number from the query plan and input size (auto-optimized shuffle). Because of AQE, many manual tuning steps are less necessary than in older Spark versions. The exam tends to ask which setting addresses which symptom, and not to compute values.
Worked example: diagnosing a slow nightly aggregation
Scenario. A nightly job aggregates order lines into daily revenue per store with a groupBy on classic compute supporting these settings. The Spark UI measures about 600 GB of shuffle input, evenly distributed across 200 tasks of about 3 GB each, and many tasks spill gigabytes to disk. The job takes 55 minutes. A teammate proposes raising spark.default.parallelism to 2,000.
Step 1: identify what the stage uses. The job uses DataFrames and a SQL aggregation, so the shuffle partition count comes from spark.sql.shuffle.partitions, which is the default 200. RDD default parallelism is not the control for this shuffle stage.
Step 2: compute a sensible target. Using decimal units, 600,000 MB of measured shuffle data divided by a candidate 200 MB per partition gives 3,000 initial partitions. Input file size alone would not justify this calculation because aggregation can reduce data before shuffling. The uniform oversized tasks and spill make this a hypothesis to test, not a universal optimal value; AQE may coalesce the final tasks.
Step 3: apply one change.
spark.conf.set("spark.sql.shuffle.partitions", "3000")Alternatively, set it to auto on supported compute and let auto-optimized shuffle decide.
Step 4: re-measure. Run again and compare duration, spill and the task time distribution in the Spark UI. An illustrative result of 14 minutes with no spill would support the change; these figures are not a benchmark or a promised speedup. If the time had not improved, you would revert and investigate skew rather than stack more changes.
Why the other options are worse. Raising spark.default.parallelism does not affect a DataFrame shuffle. Selecting compute with more executor memory might reduce spill but changes resources and cost, and does not directly change the shuffle partition count. Switching to an exact count(DISTINCT) would add work. A broadcast hint is irrelevant, since there is no join.
Worked example: choosing the right deduplication
Scenario. A bank loads bronze.accounts_raw with 5 million rows. Account 55012 appears twice: balance 1,200 at 08:00 and balance 1,450 at 09:30. Another account, 55013, appears twice with identical values in every column. The silver table must hold one row per account with the most recent balance.
Step 1: classify the duplicates. Account 55013 is an exact duplicate; distinct() would remove it. Account 55012 is a key duplicate with different content; distinct() keeps both rows because they are not identical.
Step 2: reject the shortcut. dropDuplicates(["account_id"]) gives one row per account but may keep the 08:00 balance of 1,200, which is stale.
Step 3: use a window. Partition by account_id, order by updated_at DESC plus a unique, business-approved tie-breaker, keep ROW_NUMBER() = 1. Account 55012 keeps the 09:30 row with 1,450, and account 55013 keeps one of its identical rows.
Step 4: validate. First check that account keys are non-null. Then count(*) = count(DISTINCT account_id) establishes one row per non-null key in that result. Check winner values separately: uniqueness alone does not prove that the latest balance was retained.
Why the others are worse. distinct() leaves the stale rows, dropDuplicates on the key may keep the wrong version, and RANK() could keep two rows if two updates share one timestamp.
Measuring and re-measuring
Tuning without measurement is guessing. A disciplined workflow:
- Record a baseline: run time, Spark UI stage durations, shuffle read/write sizes, spill and the task duration spread, ideally across several runs because caches and cluster state vary.
- Change one setting.
- Re-run and compare with the same data and compute.
- Keep the change only if the metric improves; document it.
On serverless compute, use available query profiles and managed-compute diagnostics rather than assuming the classic Spark UI and all configuration knobs are exposed. The Spark monitoring and troubleshooting lesson develops the evidence to collect.
Also remember that performance can change when data grows, so a setting tuned for last year's volume may be wrong today. Prefer adaptive features (AQE, auto shuffle, liquid clustering) over hard-coded values when they are available.
Common pitfalls and exam tips
count(*)versuscount(col). The second ignores nulls.dropDuplicates(keys)keeps an arbitrary row. UseROW_NUMBER()when you need the latest.RANKties.WHERE rank = 1may return several rows per key.ROW_NUMBERreturns exactly one.unionplusdistinctis costly. It shuffles all data; deduplicate on the smallest key set you can.- Parallelism versus shuffle partitions. DataFrame shuffles use
spark.sql.shuffle.partitions. - Too many or too few partitions. Thousands of tiny tasks waste scheduling time; a few giant tasks spill and run slowly.
- Driver memory.
collect()andtoPandas()bring all data to the driver and can crash it. - Approximate means approximate. Never use
approx_count_distinctwhere exact numbers are contractually required.
Exam tip: Match symptom to setting. Tasks too large and spilling on a DataFrame shuffle points to more shuffle partitions. A small join side that still shuffles points to a broadcast hint or a higher broadcast threshold. A driver crash after collecting points to driver memory or avoiding
collect(). Every tuning answer should include re-measuring.
Mini case study
Situation. A media company ingests viewing events with Auto Loader. Because upstream retries, the same event can arrive up to three times, with identical event_id and payload but different ingest_time. Product analytics needs the number of distinct viewers per day in a dashboard (a statistical estimate with RSD at most 3% is acceptable, without a hard per-result error guarantee), and finance needs the exact count of billable events. The daily job currently takes 40 minutes and its Spark UI shows 200 shuffle tasks of about 2.5 GB each with heavy spill.
Task. Propose the deduplication, aggregation and tuning approach.
Solution.
- Deduplicate on the key. Duplicates share
event_id, and all business fields, including event time and viewer id, are identical, sodropDuplicates(["event_id"])is sufficient; which copy survives does not matter. Use event time, not copy arrival time, for the reporting day. If business fields differ, use a window with an explicit source-version ordering and deterministic tie-breaker. - Aggregate. Finance uses
count(*)on valid billable events deduplicated across the complete billing window, including reconciled late arrivals. The dashboard groups by event day and usesapprox_count_distinct(viewer_id, 0.02)for its statistical precision requirement. The default RSD of 5% would not meet that request; the chosen estimate still does not guarantee a hard 3% error cap. Benchmark it rather than assuming it is always faster. - Tune. The tasks are far too large, so increase
spark.sql.shuffle.partitions(or useauto) and leavespark.default.parallelismalone, since there is no RDD work. - Verify. Compare duration and spill with the baseline in the Spark UI, keep the setting only if it helps.
Key terms
- Shuffle: redistribution of data across partitions by key, needed for joins and aggregations.
- Partition: a chunk of data processed by one task.
dropDuplicates: removes rows with the same values in the chosen columns, keeping an arbitrary one.ROW_NUMBER(): window function giving a unique sequence number per ordered partition.approx_count_distinct: sketch-based statistical estimate of distinct values (HyperLogLog++).describe/summary: quick descriptive statistics of a DataFrame.- AQE: adaptive query execution, re-optimization at runtime from actual statistics.
- Spill: writing intermediate data to disk when memory is insufficient.
Summary checklist
- I can remove exact and key-based duplicates and keep the latest row with
ROW_NUMBER(). - I know
count(*)counts rows andcount(col)ignores nulls. - I can use
approx_count_distinct,describeandsummary, and know when approximate is acceptable. - I can match
spark.sql.shuffle.partitions,spark.default.parallelism, executor and driver memory and the broadcast threshold to their symptoms. - I know AQE and
autoshuffle reduce manual tuning. - I measure a baseline, change one setting, and re-measure.
Official sources
- Exam guide: https://www.databricks.com/sites/default/files/2026-10/Databricks-Certified-Data-Engineer-Associate-Exam-Guide-August-2026.pdf (opens in a new tab)
- Adaptive query execution: https://docs.databricks.com/aws/en/optimizations/aqe (opens in a new tab)
- Spark configuration on Databricks: https://docs.databricks.com/aws/en/spark/conf (opens in a new tab)
- Spark performance tuning: https://spark.apache.org/docs/latest/sql-performance-tuning.html (opens in a new tab)
- Approximate distinct-count API: https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.approx_count_distinct.html (opens in a new tab)
- Watermarks and streaming deduplication: https://docs.databricks.com/aws/en/structured-streaming/watermarks (opens in a new tab)
