DataBricks — Deduplicate Data with Window function and QUALIFY

Data deduplication is a crucial step in data engineering to ensure clean and accurate datasets. In Databricks (Apache Spark SQL), Window Functions help identify duplicate records by assigning a rank or row number to each row within a partition. Traditionally, ROW_NUMBER() is used along with a subquery or Common Table Expression (CTE) to remove duplicates.

With the QUALIFY clause, Databricks simplifies this process by filtering window function results directly in the SQL query, eliminating the need for subqueries.

✅ Scenario: Removing Duplicates from a Dataset

Assume we have a dataset of customer transactions where duplicate records exist based on the First_Name, Last_Nameand Date. We want to keep only the latest score date for each user.

🎯 Goal:

• Identify duplicate User (First_Name, Last_Name)entries.
• Retain only the latest score date per user(based on Date).