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

  1. Data with repeated keys
  2. Are the duplicates identical in every column? Yes: distinct(). No: Need a specific version per key.
  3. Does it matter which version survives? Yes: row_number over key ordered by desc(timestamp), keep rn = 1. No: dropDuplicates([keys]).
  4. Check: count of rows equals count of distinct keys
  5. 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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic