How to Remove Duplicate Rows with PySpark in Databricks

Duplicate data creeps in through reprocessing, repeated loads or poorly designed joins — and it quickly inflates counts, distorts metrics and breaks reports. Removing duplicate rows with PySpark in Databricks is one of the most common day-to-day cleaning tasks, and it is simple once you know the right functions. This guide shows, step by step, how to spot and eliminate duplicates, whether across every column or only in the ones that define a unique record. At the end, you will save the result into a Delta table ready to reuse.

Prerequisites

• A Databricks workspace with an active cluster (or SQL warehouse).
• A Python notebook attached to the cluster.
• Basic knowledge of Python and the DataFrame concept.
• The spark variable already comes available in any Databricks notebook.

Step 1: Create a sample DataFrame with duplicates

To follow along, we start by creating a small DataFrame. Notice that the id=2 row is fully repeated and that id=3 appears twice with different cities — that is, a duplicate only on the key column. These two cases call for different strategies, as you will see.

from pyspark.sql import Row

dados = [
    Row(id=1, cliente="Ana",   cidade="Porto",  atualizado="2026-07-10"),
    Row(id=2, cliente="Bruno", cidade="Lisboa", atualizado="2026-07-11"),
    Row(id=2, cliente="Bruno", cidade="Lisboa", atualizado="2026-07-11"),
    Row(id=3, cliente="Rita",  cidade="Braga",  atualizado="2026-07-12"),
    Row(id=3, cliente="Rita",  cidade="Faro",   atualizado="2026-07-15"),
]

df = spark.createDataFrame(dados)
df.show()

Step 2: Count the duplicates before deleting

Before removing anything, it is worth knowing how many duplicates exist — that way you confirm the impact of the cleanup and catch surprises. Grouping by every column and filtering the groups with more than one row reveals the exact repetitions. Keep this number: at the end you will compare it with the final count to confirm how many rows were removed.

print("Total de linhas:", df.count())

duplicados = (df.groupBy(df.columns)
                .count()
                .filter("count > 1"))
duplicados.show()

Step 3: Remove fully duplicated rows

When two rows are equal across all columns, the dropDuplicates() method with no arguments solves the problem, keeping just one copy of each row. The distinct() method does exactly the same and is only a shorter way to write it. Both trigger a shuffle, so apply them after you have already dropped unnecessary columns.

sem_duplicados = df.dropDuplicates()
print("Depois de dropDuplicates():", sem_duplicados.count())

# Equivalente:
sem_duplicados = df.distinct()

Step 4: Remove duplicates by key column

Often a row is considered a duplicate based on a business key (for example, the id), even if the other columns differ. For that case, list the columns to consider and Spark deduplicates by them alone.

por_chave = df.dropDuplicates(["id"])
por_chave.show()

Careful: when you use a subset of columns, Spark keeps an arbitrary row from each group — it does not guarantee which of the rows stays. If that matters, use the next step.

Step 5: Keep the most recent record

When you want to control which row survives — for example, keeping the most recent version of each id — use a window (Window) with row_number(). The idea is to number the rows within each group, ordered by descending date, and keep only the one that gets number 1.

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, col

janela = Window.partitionBy("id").orderBy(col("atualizado").desc())

mais_recente = (df.withColumn("rn", row_number().over(janela))
                  .filter(col("rn") == 1)
                  .drop("rn"))
mais_recente.show()

With this approach, id=3 keeps the city "Faro", because it is the 2026-07-15 update, and you discard the old version in a controlled way.

Step 6: Save the result into a Delta table

To reuse the cleaned data, save the DataFrame into a managed Delta table. Adjust the catalog and schema name to your environment.

(mais_recente.write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("vendas.clientes_limpos"))

Verify the result

Compare the count before and after and confirm there is no longer any repeated key. If the last query returns no rows, the cleanup worked as expected: you went from 5 rows to 3 unique records per id.

print("Linhas originais:", df.count())
print("Linhas finais:", mais_recente.count())

(mais_recente.groupBy("id")
             .count()
             .filter("count > 1")
             .show())

Conclusion

In just a few steps you went from repeated data to a clean, reliable set: dropDuplicates() for identical rows, a subset of columns for business keys, and a Window when you need to choose exactly which record to keep. From here, you can schedule this notebook as a Job to run automatically, or combine the logic with MERGE INTO to deduplicate incrementally as new data arrives. Which key column best identifies a unique record in your own data?