4.2 Data Cleansing, Deduplication & Standardization

Raw data arriving in the Bronze layer is almost always messy, duplicated, and inconsistently formatted. A core responsibility of a Data Engineer is to write robust PySpark transformations to cleanse, deduplicate, and standardize this data as it moves into the Silver layer. The Databricks Data Engineer Professional exam rigorously tests your knowledge of specific PySpark DataFrame methods, built-in functions, and optimization techniques used for these critical tasks. Implementing these transformations efficiently ensures that downstream analytics and machine learning models receive high-quality, trustworthy data.

Handling Nulls and Missing Data

Dealing with missing data is one of the most common challenges in data engineering. PySpark provides the DataFrame.na submodule specifically for dealing with null or missing values, offering methods to either remove or impute the missing data.

Dropping Null Values

The df.na.drop() method is used to remove rows that contain null values. You can configure its behavior using several parameters:

• how: Accepts "any" (drops a row if any column is null) or "all" (drops a row only if all columns are null).
• thresh: Drops rows that have less than thresh non-null values.
• subset: A list of columns to consider. This is highly recommended to avoid dropping rows based on nulls in irrelevant columns.

# Drop rows where either 'customer_id' or 'transaction_date' is null
clean_df = df.na.drop(subset=["customer_id", "transaction_date"])

Filling Null Values

When dropping rows is not acceptable, imputation is required. The df.na.fill(value) method replaces null values with a specified literal value. You can pass a dictionary to map specific replacement values to specific columns, ensuring that strings are replaced with strings and integers with integers.

# Impute missing values with appropriate defaults
imputed_df = df.na.fill({
    "age": 0, 
    "status": "UNKNOWN", 
    "is_active": False
})

Another common function for handling nulls is coalesce(*cols), which returns the first non-null value among the provided columns. This is particularly useful when joining datasets or consolidating multiple optional fields.

Handling Corrupt Data During Ingestion

When reading semi-structured data like JSON or CSV, schema mismatches or malformed rows can frequently occur. Spark provides three distinct read modes to handle these situations, specified via .option("mode", "<MODE>"). Understanding these modes is heavily tested on the exam.

1. PERMISSIVE (Default): When a corrupt record is encountered, Spark sets all the malformed fields to null and places the entire unparsed string into a designated rescue column (usually named _corrupt_record if specified in the schema, or handled automatically by Auto Loader's rescueDataColumn). This prevents data loss while allowing pipelines to continue.
2. DROPMALFORMED: Completely drops any row that contains malformed data or does not match the provided schema. This is useful when corrupt data is entirely useless and you want to filter it out immediately at the source.
3. FAILFAST: Throws an exception and halts the read operation immediately upon encountering the very first corrupt record. This is used in highly strict environments where any deviation from the expected schema indicates a critical upstream failure that must be addressed immediately.

# Example of reading JSON with a corrupt record column in PERMISSIVE mode
schema = "id INT, name STRING, _corrupt_record STRING"
df = spark.read.schema(schema) \
    .option("mode", "PERMISSIVE") \
    .option("columnNameOfCorruptRecord", "_corrupt_record") \
    .json("/path/to/data.json")

Advanced Deduplication Techniques

Duplicate data is a ubiquitous issue, particularly in distributed architectures with at-least-once delivery semantics (such as Kafka or Kinesis integrations).

Basic Deduplication

In PySpark, basic deduplication is performed using the dropDuplicates() (or the alias drop_duplicates()) method. If called without arguments, it drops rows where all columns are exactly identical. To deduplicate based on a primary key or a specific combination of columns, you pass a list of column names to the subset parameter.

# Deduplicate based on a composite primary key
deduped_df = df.dropDuplicates(subset=["transaction_id", "system_source"])

Deterministic Deduplication with Window Functions

A major caveat of dropDuplicates() on a batch DataFrame is that the row retained is non-deterministic; Spark simply keeps the first one it processes. If you need to keep the most recent record based on a timestamp, you must use Window functions.

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, col, desc

# Define a window partitioned by the ID and ordered by timestamp descending
windowSpec = Window.partitionBy("transaction_id").orderBy(desc("update_timestamp"))

# Assign a row number and filter for the most recent record
latest_records_df = df.withColumn("rn", row_number().over(windowSpec)) \
                      .filter(col("rn") == 1) \
                      .drop("rn")

This pattern is critical for maintaining slowly changing dimensions (SCDs) and ensuring accurate state in the Silver layer.

String Standardization and Data Masking

Inconsistent string formatting (e.g., mixed casing, leading/trailing spaces) can severely break joins and aggregations. PySpark's pyspark.sql.functions module provides essential string manipulation tools to standardize data:

• trim(col): Removes leading and trailing whitespace.
• lower(col) / upper(col): Converts strings to all lowercase or all uppercase.
• regexp_replace(col, pattern, replacement): Replaces substrings that match a regular expression. This is extremely powerful for removing unwanted characters or standardizing formats like phone numbers.
• split(col, pattern): Splits a string column into an array column based on a delimiter.
• concat(*cols) or concat_ws(sep, *cols): Concatenates multiple columns together.

Data Masking and PII Protection

During the cleansing phase, Personally Identifiable Information (PII) must often be masked, obfuscated, or tokenized before data enters wider-access layers like Gold. regexp_replace combined with hashing functions like sha2() are commonly used. Databricks Unity Catalog also offers dynamic data masking, but physical masking during ETL remains a core competency.

from pyspark.sql.functions import regexp_replace, sha2

# Masking a credit card number to show only the last 4 digits
masked_df = df.withColumn(
    "masked_cc", 
    regexp_replace(col("credit_card"), r"\d{12}(\d{4})", "****-****-****-$1")
)

# Hashing an email address for anonymization (using SHA-256)
anonymized_df = df.withColumn("email_hash", sha2(col("email"), 256))

Timestamp and Timezone Standardization

Timezone inconsistencies are a notorious source of errors in global data engineering. A widely accepted best practice is to standardize all timestamps to Coordinated Universal Time (UTC) during the Silver refinement stage. This prevents errors when aggregating data across different geographical regions.

Databricks provides specific functions for robust timezone handling:

• to_utc_timestamp(timestamp, timezone): Converts a timestamp from a given local timezone to UTC.
• from_utc_timestamp(timestamp, timezone): Converts a UTC timestamp back to a local timezone.
• current_timestamp(): Generates the current timestamp at the execution time.

from pyspark.sql.functions import to_utc_timestamp

# Standardizing an event time recorded in Pacific Time to UTC
standardized_time_df = df.withColumn(
    "event_time_utc", 
    to_utc_timestamp(col("event_time"), "America/Los_Angeles")
)

Mastering these transformation functions allows you to build reliable, idempotent data cleansing pipelines that prepare raw data for downstream consumption, fulfilling the core requirements of a Databricks Data Engineer Professional.