7.2 Handling Missing and Null Data

Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Spark Architecture and Execution

1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage

2 Chapter 2: Shuffles, Memory and Persistence

2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels

3 Chapter 3: Spark SQL Sessions, Tables and Views

3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views

4 Chapter 4: SQL Sources and Stored Layout

4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout

5 Chapter 5: SQL Queries and Analytics

5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics

6 Chapter 6: DataFrame Construction and Columns

6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions

7 Chapter 7: DataFrame Validation and Inspection

7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion

8 Chapter 8: Aggregations and Dates

8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions

9 Chapter 9: DataFrame Joins and File IO

9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning

10 Chapter 10: Shared Variables and Stateful Processing

10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores

11 Chapter 11: Troubleshooting and Tuning

11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins

12 Chapter 12: Structured Streaming

12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks

13 Chapter 13: Spark Connect and Deployment

13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices

14 Chapter 14: Pandas API on Spark

14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization

7.2 Handling Missing and Null Data

Key Takeaways Explain This Topic AI

Spark distinguishes between SQL NULL (unknown value in typed columns), Python None (PySpark client representation of null), and IEEE NaN (Not a Number in float/double columns).

Missing values must be identified using col.isNull(), col.isNotNull(), or isnan(col); direct equality comparisons (col == None) evaluate to NULL under three-valued logic and fail to filter correctly.

df.na.drop() / df.dropna() purges rows containing nulls based on how ('any' vs 'all'), a minimum non-null count (thresh), or a targeted column subset.

df.na.fill() / df.fillna() replaces null values matching the target column data type, or accepts a dictionary mapping specific column names to distinct replacement values.

F.coalesce(*cols) returns the first non-null value across multiple columns from left to right, providing an efficient fallback chain for missing attributes without complex case branching.

Last updated: October 2026

Real-world datasets are rarely pristine. Missing fields, unrecorded measurements, corrupted values, and malformed inputs are pervasive across enterprise data pipelines. Apache Spark provides robust, dedicated APIs for detecting, filtering, dropping, imputing, and replacing missing data.

To use these tools effectively and succeed on certification exams, developers must understand how Spark models nulls, the interaction between SQL NULL and IEEE NaN , and the precise behavior of the DataFrameNaFunctions suite ( df.na ).

The Taxonomy of Missing Data in Spark

Spark distinguishes between three related but fundamentally distinct concepts of missing or undefined data:

SQL NULL : Represents missing, unknown, or unassigned data in any SQL data type. SQL three-valued logic applies: comparisons involving NULL evaluate to UNKNOWN ( NULL ) rather than TRUE or FALSE .

Python None : The client-side representation of SQL NULL in the PySpark environment. When inspecting individual Row objects in Python or passing literal values, None translates to SQL NULL in the JVM.

IEEE 754 NaN (Not a Number): Specific to floating-point numeric columns ( FloatType and DoubleType ). Represents undefined mathematical results (such as an explicitly supplied float("nan"); Spark division-by-zero behavior instead depends on ANSI mode and is not a reliable NaN constructor).

Critical Differences Between NULL and NaN

• Attribute SQL NULL IEEE NaN
• Applicable Types All data types (numeric, string, date, struct, etc.) FloatType and DoubleType only
• Identification Method col.isNull() / col.isNotNull() pyspark.sql.functions.isnan(col)
• Handled by df.na.drop() Yes Yes (treated as null in drop/fill)
• Behavior in isNull() Returns True Returns False
• Sorting Order NULLS FIRST (asc) / NULLS LAST (desc) Treated as larger than any valid float

Note

Because isNull() returns False for NaN values, checking for all missing or invalid float values requires combining both predicates: col("score").isNull() | isnan(col("score")) .

Detecting and Filtering Missing Values

To filter rows based on nullity, PySpark provides dedicated Column methods: from pyspark.sql.functions import col, isnan

• # Filter rows where email is missing (NULL)
• df_missing_email = df.filter(col("email").isNull())

• # Filter rows where email is populated (NOT NULL)
• df_valid_email = df.filter(col("email").isNotNull())

• # Filter rows where a float measurement is NaN
• df_nan_temp = df.filter(isnan(col("temperature")))

• # Filter rows where a float column is EITHER null OR NaN
• df_invalid_temp = df.filter(col("temperature").isNull() | isnan(col("temperature")))
• The Equality Comparison Trap

A critical mistake made by new PySpark developers is attempting to test for nulls using equality comparisons: # INCORRECT: Returns an empty DataFrame or null condition

• # df_bad = df.filter(col("email") == None)
• # df_bad2 = df.filter(col("email") == "null")

In SQL three-valued logic, NULL == NULL does not evaluate to TRUE ; it evaluates to UNKNOWN ( NULL ). Because filter operations only retain rows where the condition evaluates strictly to TRUE , writing col("email") == None drops all rows including those where email is null! Always use col.isNull() and col.isNotNull() .

Dropping Missing Data: df.na.drop() / df.dropna()

The DataFrameNaFunctions suite provides df.na.drop() , which is also available via the top-level DataFrame alias df.dropna() . Both methods are completely identical. df.na.drop(how="any", thresh=None, subset=None)

Parameter Breakdown

how ( str ):

"any" (default): Drops a row if any of the considered columns contain null or NaN .

"all" : Drops a row only if all of the considered columns contain null or NaN .

thresh ( int , optional):

If specified, overrides the how parameter.

Defines the minimum number of non-null values a row must possess across the examined columns to be kept. Rows with fewer non-null values than thresh are dropped.

subset ( List[str] , optional):

Restricts null evaluation to a specific list of column names. If omitted, all columns in the DataFrame are evaluated.

• Practical Dropping Scenarios # 1. Drop row if ANY column contains null
• df_clean1 = df.na.drop()

• # 2. Drop row ONLY if ALL columns are null
• df_clean2 = df.dropna(how="all")

• # 3. Drop row if customer_id OR email is null (ignoring nulls in other columns)
• df_clean3 = df.dropna(how="any", subset=["customer_id", "email"])

• # 4. Keep row only if at least 3 columns have non-null values
• df_clean4 = df.dropna(thresh=3)
• Imputing Missing Data: df.na.fill() / df.fillna()

Rather than discarding rows with missing values, pipelines frequently impute fallback defaults using df.na.fill() (aliased as df.fillna() ): df.na.fill(value, subset=None)

Type Matching Semantics

A pivotal feature of fillna() is data type matching . The type of the value argument determines which columns are eligible for imputation:

If value is an int or float , Spark replaces nulls only in numeric columns ( IntegerType , DoubleType , etc.).

If value is a str , Spark replaces nulls only in string columns ( StringType ).

If value is a bool , Spark replaces nulls only in boolean columns ( BooleanType ). # Replaces nulls with 0 in numeric columns only (string columns remain null)

df_filled_num = df.fillna(0)

• # Replaces nulls with 'UNKNOWN' in string columns only (numeric columns remain null)
• df_filled_str = df.fillna("UNKNOWN")
• Multi-Column Dictionary Imputation

To impute different default values across distinct columns and data types in a single operation, pass a Python dictionary mapping column names to replacement values: # Dictionary-based multi-type imputation

• imputation_dict = {
• "age": 0,
• "salary": 50000.0,
• "department": "Unassigned",
• "is_active": False
• }

df_imputed = df.fillna(imputation_dict)

When using a dictionary, the subset parameter is not required because the dictionary keys define the target columns.

Replacing Arbitrary Values: df.na.replace()

While fillna() specifically targets SQL NULL and NaN , datasets often represent missing or invalid data using sentinel values such as "-999" , "N/A" , "UNKNOWN" , or empty strings "" .

The df.na.replace() method searches and replaces specific values across target columns: # Replace sentinel strings across all string columns

df_replaced = df.na.replace("-999", None)

• # Replace multiple distinct values using a dictionary mapping on specific columns
• replace_rules = {
• "N/A": "Unknown",
• "missing": "Unknown",
• "null_str": "Unknown"
• }
• df_clean = df.na.replace(replace_rules, subset=["city", "state"])

Warning

In PySpark, na.replace() requires that the replacement value type matches the target column's existing data type. Attempting to replace a string with an integer in a StringType column will fail or produce conversion errors.

• Advanced Null Handling: coalesce() and Conditional Fallbacks
• Multi-Column Fallback with F.coalesce(*cols)

The coalesce() function from pyspark.sql.functions evaluates a sequence of column expressions from left to right and returns the first non-null value encountered for each row: from pyspark.sql.functions import coalesce, col, lit

• # Establish contact hierarchy: mobile -> home -> work -> fallback literal
• df_contact = df.withColumn(
• "primary_contact",
• coalesce(
• col("mobile_phone"),
• col("home_phone"),
• col("work_phone"),
• lit("No Contact Available")
• )
• )

Important

Disambiguation Exam Trap: Do not confuse the column function pyspark.sql.functions.coalesce(*cols) with the DataFrame method df.coalesce(numPartitions) . The column function performs SQL null-fallback evaluation across columns, whereas the DataFrame method reduces the number of partitions in a distributed dataset without triggering a full shuffle.

Conditional Imputation with when().otherwise()

For complex imputation logic depending on multiple criteria, combine when() and otherwise() : from pyspark.sql.functions import when

• # Illustrative policy defaults by department; these are not computed averages
• df_conditional = df.withColumn(
• "imputed_salary",
• when(col("salary").isNotNull(), col("salary"))
• .when(col("department") == "Engineering", lit(115000.0))
• .when(col("department") == "Sales", lit(85000.0))
• .otherwise(lit(60000.0))
• )
• Summary of Missing Data Handling APIs

• API Method Target Data Action Performed
• col.isNull() / col.isNotNull() SQL NULL Predicate for row filtering / conditional logic
• isnan(col) IEEE NaN Predicate for floating-point NaN detection
• df.na.drop() / df.dropna() NULL and NaN Drops rows matching how , thresh , or subset
• df.na.fill() / df.fillna() NULL and NaN Imputes scalar values or dictionary mappings by type
• df.na.replace() Arbitrary values Substitutes sentinel values or placeholders
• F.coalesce(*cols) NULL Returns first non-null column value from left to right

Mastering these missing data handling techniques ensures your Spark data pipelines remain resilient against malformed inputs and produce clean, analytics-ready datasets.