ExamFinch.com My Study

Study guide

4.2 Data Cleansing, Deduplication & Standardization

Practice this exam

Databricks Certified Data Engineer Professional Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Developing Code for Data Processing using Python and SQL - Core Spark SQL & PySpark

1.1 Complex PySpark Transformations & DataFrame API 1.2 Advanced Spark SQL Patterns 1.3 Streaming Workloads with Structured Streaming 1.4 Lakeflow & Delta Live Tables (DLT) Declarative Pipelines

2 Chapter 2: Developing Code for Data Processing using Python and SQL - Streaming & Advanced Patterns

2.1 Advanced Delta Lake Features 2.2 Custom & Stateful Streaming Operations 2.3 Python & PySpark Code Modularization 2.4 User-Defined Functions (UDFs) & Performance Impact

3 Chapter 3: Data Ingestion & Acquisition

3.1 Auto Loader (cloudFiles) Production Ingestion 3.2 Change Data Capture (CDC) Ingestion Patterns 3.3 Batch & Streaming Connectors

4 Chapter 4: Data Transformation, Cleansing, and Quality

4.1 Data Validation & Quality Frameworks 4.2 Data Cleansing, Deduplication & Standardization 4.3 Silver and Gold Medallion Layer Refinement 4.4 Handling Schema Drift and Evolution

5 Chapter 5: Data Sharing and Federation

5.1 Delta Sharing Architecture & Governance 5.2 Lakehouse Federation

6 Chapter 6: Monitoring and Alerting & Observability

6.1 System Tables & Observability 6.2 Databricks Workflows & Job Monitoring 6.3 Spark UI & Query Execution Diagnostics 6.4 Data Quality & Pipeline Alerting

7 Chapter 7: Cost & Performance Optimisation - Data Layout & Compute

7.1 Data Storage & Layout Optimization 7.2 Compute Choice & Cluster Sizing 7.3 Photon Engine & Query Acceleration

8 Chapter 8: Cost & Performance Optimisation - Skew & Cost Governance

8.1 Partitioning & Data Skew Remediation 8.2 Cost Governance & DBU Optimization

9 Chapter 9: Ensuring Data Security and Compliance

9.1 Unity Catalog Access Control & Least Privilege 9.2 Dynamic Data Masking & Row/Column-Level Security 9.3 Secrets Management & Network Security 9.4 Compliance, Audit & Data Privacy

10 Chapter 10: Data Governance in Unity Catalog

10.1 Unity Catalog Governance Architecture 10.2 Data Lineage & Tagging in Unity Catalog 10.3 Managed vs External Tables & Storage Governance

11 Chapter 11: Debugging and Deploying - Asset Bundles & CI/CD

11.1 Databricks Asset Bundles (DABs) & Infrastructure as Code 11.2 CI/CD Automation & Databricks CLI / REST API 11.3 Troubleshooting Pipeline & Job Failures 11.4 Code Versioning & Databricks Git Integration

12 Chapter 12: Data Modelling & Lakehouse Analytics

12.1 Medallion Architecture Design Patterns 12.2 Dimensional Modeling in the Lakehouse 12.3 Analytics & BI Optimization

4.2 Data Cleansing, Deduplication & Standardization

Key Takeaways Explain This Topic AI

The dropDuplicates() method is used in PySpark to remove duplicate rows, and can be scoped to specific columns using the subset parameter.

When handling corrupt records during JSON or CSV read operations, the PERMISSIVE mode inserts nulls for corrupt fields, DROPMALFORMED drops the row, and FAILFAST throws an exception.

String standardization is commonly achieved using PySpark functions like regexp_replace, trim, lower, and upper to ensure consistent data formats.

Timestamp standardization often involves converting timezones using convert_timezone() or standardizing all times to UTC using to_utc_timestamp() to avoid aggregation errors.

Window functions utilizing row_number() combined with sorting are required to achieve deterministic deduplication, ensuring the most recent record is retained.

Last updated: July 2026

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.

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.

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.

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.