Finding and fixing duplicates, missing values, and nulls

In Azure Databricks, data engineers use Apache Spark DataFrame APIs and SQL functions to detect and resolve duplicate, missing, and null values as part of building reliable ETL pipelines, typically within Delta Lake tables. Common techniques include dropDuplicates(), fillna()/coalesce(), dropna(), and constraint enforcement, often applied in the Bronze-to-Silver transformation stage of a medallion architecture. Choosing the right resolution strategy (drop, impute, or flag) depends on business requirements and data lineage needs.

1 · Learn the must-know

• 1dropDuplicates() removes exact duplicate rows across all columns by default, but you can pass a subset of column names to define duplicates based on specific keys only.
• 2isNull()/isNotNull() and the SQL IS NULL/IS NOT NULL predicates are the standard way to filter or count null values; comparing with = NULL always returns null (falsy), not true.
• 3na.fill() (or fillna()) lets you replace nulls with a specified value per column or globally, while na.drop() (or dropna()) removes rows based on 'any' or 'all' null conditions across specified columns.
• 4coalesce() returns the first non-null value from a list of columns, making it useful for consolidating data from multiple overlapping source columns.
• 5Delta Lake table constraints (NOT NULL and CHECK) can proactively prevent null or invalid values from being written, enforcing data quality at write time rather than relying solely on downstream cleansing.
• 6When deduplicating streaming or incrementally loaded data, window functions (row_number() over a partition ordered by a timestamp) are commonly used to identify and keep only the most recent record per key, which dropDuplicates() alone cannot guarantee correctly.

2 · Check your understanding