Dedup & Latest-Record Patterns
Apache Spark · Cleaning & Complex Types
Theory
Duplicates come from retries, repeated file loads and change feeds that send many versions of the same record. Spark offers three levels of dedup. df.distinct() removes rows that are identical in every column. df.dropDuplicates(["order_id"]) keeps one row per key, but which row it keeps is not guaranteed, so never use it when the version matters.
When you need the newest version of each key, use the latest-record pattern: a window partitioned by the key and ordered by the update timestamp descending, F.row_number() over it, then filter rn == 1. For example, a customer who moved from Jaipur to Pune has two rows; ordering by updated_at desc keeps the Pune row. A groupBy alternative is F.max_by("city", "updated_at") per column, or F.max(F.struct("updated_at", ...)) to pick a whole row. Add a tie-breaker (such as an ingestion id) in case two versions share a timestamp.
Why we use it
Duplicate rows inflate counts and revenue, and picking an arbitrary version of a changed record gives wrong current values (old address, old status). A deterministic latest-record step is the core of incremental loads, slowly changing dimensions and CDC processing.
When to use it
Use distinct for exact duplicate rows, dropDuplicates(keys) when any copy of a key is fine, and row_number = 1 when you need the latest or best version by a column. On streams, use dropDuplicatesWithinWatermark or dropDuplicates with a watermark to bound state.
How it works
- Data with repeated keys
- Are the duplicates identical in every column? Yes: distinct(). No: Need a specific version per key.
- Does it matter which version survives? Yes: row_number over key ordered by desc(timestamp), keep rn = 1. No: dropDuplicates([keys]).
- Check: count of rows equals count of distinct keys
- One correct row per key
Syntax
df.distinct()
df.dropDuplicates(["order_id"]) # any one row per key
w = Window.partitionBy("customer_id").orderBy(F.desc("updated_at"))
df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn")
df.groupBy("customer_id").agg(F.max_by("city", "updated_at"))Example
from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window
spark = SparkSession.builder.appName("techtown-spark").getOrCreate()
customer_changes = spark.createDataFrame([
(101, "Asha", "Jaipur", "Retail", "2026-01-01 10:00:00"),
(101, "Asha", "Pune", "Retail", "2026-03-15 08:30:00"),
(102, "Ravi", "Pune", "Corporate", "2026-02-10 12:00:00"),
(102, "Ravi", "Pune", "Corporate", "2026-02-10 12:00:00"), # exact duplicate
(103, "Meera", "Delhi", "Retail", "2026-01-20 09:00:00"),
(103, "Meera", "Delhi", "SMB", "2026-04-02 17:45:00"),
], "customer_id INT, name STRING, city STRING, segment STRING, updated_at STRING"
).withColumn("updated_at", F.to_timestamp("updated_at"))
print("rows:", customer_changes.count(), "| after distinct:", customer_changes.distinct().count())
w = Window.partitionBy("customer_id").orderBy(F.desc("updated_at"))
latest = (customer_changes
.withColumn("rn", F.row_number().over(w))
.filter(F.col("rn") == 1)
.drop("rn")
.orderBy("customer_id"))
latest.show()
# Same answer with groupBy + max_by (no window)
customer_changes.groupBy("customer_id").agg(
F.max_by("city", "updated_at").alias("city"),
F.max_by("segment", "updated_at").alias("segment")).orderBy("customer_id").show()
# Output:
# rows: 6 | after distinct: 5
# +-----------+-----+-----+---------+-------------------+
# |customer_id| name| city| segment| updated_at|
# +-----------+-----+-----+---------+-------------------+
# | 101| Asha| Pune| Retail|2026-03-15 08:30:00|
# | 102| Ravi| Pune|Corporate|2026-02-10 12:00:00|
# | 103|Meera|Delhi| SMB|2026-04-02 17:45:00|
# +-----------+-----+-----+---------+-------------------+
#
# +-----------+-----+---------+
# |customer_id| city| segment|
# +-----------+-----+---------+
# | 101| Pune| Retail|
# | 102| Pune|Corporate|
# | 103|Delhi| SMB|
# +-----------+-----+---------+
Key points
- distinct = all columns identical; dropDuplicates(keys) = one arbitrary row per key
- dropDuplicates does not promise which row survives: do not use it to get the latest
- Latest record: row_number over partitionBy(key).orderBy(desc(ts)), keep rn = 1
- Add a tie-breaker column when two versions can share a timestamp
- max_by(col, ts) per column is a window-free alternative for a few columns