9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL

9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL

DP-750 Exam Focus: Master enterprise data cleansing patterns in PySpark and Spark SQL. Understand the operational differences between distinct() and dropDuplicates(subset), window-based deduplication with ROW_NUMBER(), safe type conversions using try_cast(), null evaluation with coalesce() and nvl(), corrupt record handling across parser modes (PERMISSIVE, DROPMALFORMED, FAILFAST), and regular expression cleansing.

---

1. Deduplication Strategies: Row-Level vs. Key-Level

Data ingestion from distributed systems, message queues (Event Hubs, Kafka), and REST endpoints frequently produces duplicate records due to at-least-once delivery semantics or upstream retries. Data engineers must apply the appropriate deduplication strategy based on business requirements.

1. Full-Row Deduplication (distinct)

distinct() evaluates every single column across a row. If two rows have identical values in all columns, only one is retained.

# PySpark: Full-row deduplication
df_distinct = df_raw.distinct()

-- SQL: Full-row deduplication
SELECT DISTINCT * FROM bronze.orders_raw;

2. Subset Key Deduplication (dropDuplicates)

In real-world data pipelines, rows may have the same business key (e.g., order_id) but slightly different technical metadata (such as different ingest_timestamp or file_offset values). dropDuplicates([subset]) drops duplicates based exclusively on the specified subset of columns, retaining an non-deterministic instance of the duplicated rows.

# PySpark: Deduplicate on business keys
df_dedup = df_raw.dropDuplicates(["customer_id", "transaction_id"])

3. Deterministic Deduplication with Window Functions

Because dropDuplicates() picks an arbitrary row among duplicate keys, enterprise pipelines requiring deterministic retention (e.g., "keep only the most recent record by timestamp") must use the ROW_NUMBER() window function pattern.

-- SQL: Deterministic deduplication retaining the latest record
WITH ranked_records AS (
    SELECT 
        order_id,
        customer_id,
        order_status,
        order_amount,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY order_id 
            ORDER BY updated_at DESC, ingest_timestamp DESC
        ) AS row_num
    FROM bronze.sales.orders_raw
)
SELECT 
    order_id,
    customer_id,
    order_status,
    order_amount,
    updated_at
FROM ranked_records
WHERE row_num = 1;

# PySpark: Deterministic deduplication with Window
from pyspark.sql.window import Window
from pyspark.sql.functions import col, row_number

spec = Window.partitionBy("order_id").orderBy(col("updated_at").desc(), col("ingest_timestamp").desc())

df_latest = (df_raw
    .withColumn("row_num", row_number().over(spec))
    .filter(col("row_num") == 1)
    .drop("row_num"))

Exam Trap: dropDuplicates() causes a full distributed shuffle across worker nodes based on the key hashing. If deduplicating billions of rows, ensure the cluster is sized adequately and consider partitioning by date before running deduplication.

---

2. Comprehensive Null Handling & Imputation

Missing or null values can distort statistical calculations, break join conditions, and cause unexpected null-propagation in scalar expressions.

SQL Null Evaluation & Coalescing Functions

-- SQL Null Sanitization Pipeline
SELECT 
    customer_id,
    COALESCE(work_phone, mobile_phone, home_phone, 'UNAVAILABLE') AS primary_phone,
    NVL(discount_percentage, 0.0) AS applied_discount,
    NVL2(shipping_address, 'SHIPPABLE', 'DIGITAL_ONLY') AS fulfillment_mode,
    NULLIF(TRIM(middle_name), '') AS sanitized_middle_name
FROM silver.crm.customer_profiles;

PySpark DataFrame Null Utilities (DataFrame.na)

PySpark provides a dedicated .na sub-module for high-level DataFrame null operations:

# 1. Fill null values across specific columns
df_clean = df.na.fill({
    "discount_percentage": 0.0,
    "preferred_language": "en-US",
    "is_active": True
})

# 2. Drop rows containing nulls
# how='any': drops row if ANY column in subset is null
# how='all': drops row only if ALL columns in subset are null
df_filtered = df.na.drop(how="any", subset=["order_id", "customer_id", "order_amount"])

# 3. Replace sentinel values (e.g., 'N/A', 'UNKNOWN') with NULL
df_standardized = df.na.replace({"N/A": None, "UNKNOWN": None, "NULL": None}, subset=["postal_code"])

---

3. Safe Type Casting with TRY_CAST

In standard Spark SQL and PySpark, invoking CAST(col AS type) on invalid data causes fatal runtime exceptions in ANSI SQL mode (which is enabled by default in modern Databricks runtimes):

-- Throws SparkException / NumberFormatException in ANSI mode:
SELECT CAST('INVALID_NUMBER' AS INT); 

The TRY_CAST / try_cast() Paradigm

To prevent malformed source strings from crashing production batch and streaming jobs, data engineers must use try_cast() (or TRY_CAST). When try_cast() encounters an unparseable value or invalid format, it returns NULL instead of raising a runtime exception.

-- Safe evaluation: Returns NULL without failing the query
SELECT 
    raw_id,
    TRY_CAST(raw_id AS BIGINT) AS parsed_id,
    TRY_CAST(event_date_str AS DATE) AS parsed_event_date,
    TRY_CAST(amount_str AS DECIMAL(12,2)) AS parsed_amount
FROM bronze.raw_events;

# PySpark: Safe casting using expr / try_cast
from pyspark.sql.functions import expr

df_safe = df.select(
    expr("try_cast(transaction_id as BIGINT) as transaction_id"),
    expr("try_cast(order_date as DATE) as order_date"),
    expr("try_cast(revenue as DECIMAL(10,2)) as revenue")
)

Error Quarantine Pattern with TRY_CAST

By combining try_cast() with conditional filtering, you can quarantine invalid records for offline triage while allowing valid records to proceed downstream:

-- Identify and quarantine records that failed type casting
CREATE OR REPLACE TABLE silver.quarantine.invalid_cast_records AS
SELECT 
    raw_record_id,
    raw_payload,
    'AMOUNT_PARSE_FAILURE' AS quarantine_reason,
    current_timestamp() AS quarantine_timestamp
FROM bronze.raw_events
WHERE amount_str IS NOT NULL 
  AND TRY_CAST(amount_str AS DECIMAL(12,2)) IS NULL;

---

4. Corrupt Record Handling & File Parser Modes

When reading unstructured or semi-structured files (JSON, CSV) via Spark DataFrame readers, corrupted rows (syntax errors, mismatched quotes, truncated records) can disrupt parsing.

Spark Parser Modes

                          SPARK FILE PARSER MODES

  +-------------------------------------------------------------------------+
  | 1. PERMISSIVE (Default)                                                 |
  |    - Sets corrupted fields to NULL.                                     |
  |    - Captures raw bad record in `_corrupt_record` column.               |
  +-------------------------------------------------------------------------+
  | 2. DROPMALFORMED                                                        |
  |    - Silently drops corrupted records entirely from output DataFrame.   |
  |    - No error thrown; bad rows vanish without audit trail.              |
  +-------------------------------------------------------------------------+
  | 3. FAILFAST                                                             |
  |    - Immediately aborts execution upon encountering the first bad row.  |
  |    - Throws RuntimeException.                                           |
  +-------------------------------------------------------------------------+

Capturing Corrupt Records with _corrupt_record

To use the PERMISSIVE mode effectively with quarantine tracking, declare a dedicated _corrupt_record column in the explicit reading schema:

from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DoubleType

schema = StructType([
    StructField("order_id", IntegerType(), True),
    StructField("customer_id", IntegerType(), True),
    StructField("amount", DoubleType(), True),
    StructField("_corrupt_record", StringType(), True) # Special quarantine column
])

df_json = (spark.read
    .option("mode", "PERMISSIVE")
    .option("columnNameOfCorruptRecord", "_corrupt_record")
    .schema(schema)
    .json("abfss://raw@adlsgen2.dfs.core.windows.net/incoming_orders/"))

# Split into clean and corrupted DataFrames
df_clean = df_json.filter(col("_corrupt_record").isNull()).drop("_corrupt_record")
df_corrupt = df_json.filter(col("_corrupt_record").isNotNull())

---

5. String Manipulation, Regex Parsing, & Standardization

Cleansing raw attributes often requires regular expression pattern extraction and text normalization.

-- SQL Regular Expression and String Cleansing
SELECT 
    -- Extract 10-digit phone number digits from messy strings
    REGEXP_REPLACE(raw_phone, '[^0-9]', '') AS clean_phone_digits,
    
    -- Extract domain from email
    REGEXP_EXTRACT(email_address, '@([a-zA-Z0-9.-]+)', 1) AS email_domain,
    
    -- Standardize state codes
    UPPER(TRIM(state_province)) AS normalized_state,
    
    -- Mask credit card numbers leaving last 4 digits
    CONCAT('XXXX-XXXX-XXXX-', SUBSTRING(REGEXP_REPLACE(cc_num, '[^0-9]', ''), -4)) AS masked_cc
FROM bronze.customer_feed;

# PySpark: Regex and string extraction
from pyspark.sql.functions import col, regexp_replace, regexp_extract, upper, trim

df_curated = (df
    .withColumn("clean_phone", regexp_replace(col("raw_phone"), r"[^0-9]", ""))
    .withColumn("email_domain", regexp_extract(col("email"), r"@([a-zA-Z0-9.-]+)", 1))
    .withColumn("state", upper(trim(col("state"))))
)