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).

🚀 Why This Works Best in Databricks?

✅ Handles complex deduplication in a single query
✅ Removes subqueries and CTEs, improving readability
✅ Optimized for performance in large datasets

✅ Summary

• Window functions help assign rankings to rows without changing the dataset structure.
• ROW_NUMBER() is commonly used to handle deduplication.
• QUALIFY simplifies the filtering process, eliminating the need for CTEs or subqueries.
• Recommended for Databricks, as it improves both readability and performance.

Your support is greatly appreciated! If you found this article valuable, don’t forget to clap👏, follow✌️, and subscribe❤️💬🔔 to stay connected and receive more insightful content. Let’s grow and learn together!

⭐To learn more, please follow us — 

http://www.sql-datatools.com

⭐To Learn more, please visit our YouTube channel at —

http://www.youtube.com/c/Sql-datatools

⭐To Learn more, please visit our Instagram account at —

https://www.instagram.com/asp.mukesh/

⭐To Learn more, please visit our twitter account at —

https://twitter.com/macxima

⭐To Learn more, please visit our Medium account at —

https://medium.com/@macxima

SELECT 
    'CREATE TABLE ' + TABLE_NAME + ' (' +
    STRING_AGG(
        COLUMN_NAME + ' ' + DATA_TYPE + 
        CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END + ' ' +
        CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END, ', '
    ) + ');'
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME;