Data Cleansing and Deduplication – DP-203 Azure Data Engineer Exam Guide
Introduction
Data cleansing and deduplication are foundational activities in any data engineering pipeline. For the DP-203 Azure Data Engineer Associate exam, understanding how to identify, implement, and optimize these processes on the Azure platform is critical. This guide covers why these practices matter, what they entail, how they work in Azure, and how to approach exam questions confidently.
Why Data Cleansing and Deduplication Are Important
Poor data quality is one of the biggest threats to reliable analytics, machine learning models, and business intelligence. Without proper cleansing and deduplication:
• Inaccurate reports are produced, leading to flawed business decisions.
• Duplicate records inflate metrics such as customer counts, revenue totals, and inventory figures.
• Downstream processes break or produce inconsistent results when they encounter null values, malformed strings, or conflicting data types.
• Storage and compute costs increase because redundant or garbage data consumes resources unnecessarily.
• Regulatory compliance (e.g., GDPR, HIPAA) may be compromised when duplicate or incorrect records make it impossible to honor data subject requests accurately.
In the context of Azure data engineering, clean data ensures that services such as Azure Synapse Analytics, Azure Databricks, Azure Data Factory, and Power BI all deliver trustworthy, actionable insights.
What Is Data Cleansing?
Data cleansing (also called data cleaning or data scrubbing) is the process of detecting and correcting (or removing) corrupt, inaccurate, incomplete, or irrelevant records from a dataset. Common cleansing tasks include:
• Handling null or missing values: Replacing nulls with default values, using forward-fill or backward-fill strategies, or removing rows/columns with excessive missing data.
• Standardizing formats: Ensuring dates follow a consistent format (e.g., ISO 8601), phone numbers use a uniform pattern, and text fields use consistent casing.
• Correcting data types: Converting strings to integers, dates to datetime objects, and ensuring Boolean fields are truly binary.
• Removing invalid or outlier records: Filtering out rows where values fall outside acceptable ranges or violate business rules.
• Trimming whitespace and special characters: Removing leading/trailing spaces and unwanted characters that cause join failures.
• Resolving inconsistencies: Mapping synonyms (e.g., "USA", "US", "United States") to a single canonical value.
What Is Deduplication?
Deduplication is the process of identifying and eliminating duplicate records within a dataset. Duplicates can arise from:
• Multiple source systems sending overlapping data.
• Repeated ingestion of the same batch file.
• User error during manual data entry.
• At-least-once delivery semantics in streaming pipelines.
Deduplication strategies include:
• Exact matching: Identifying rows where every column value is identical.
• Fuzzy matching: Using similarity algorithms (Levenshtein distance, Soundex, Jaro-Winkler) to find records that are near-duplicates (e.g., "Jon Smith" vs. "John Smith").
• Key-based deduplication: Selecting the most recent or most complete record for each unique business key.
• Windowed deduplication: In streaming scenarios, removing duplicates within a defined time window.
How Data Cleansing and Deduplication Work in Azure
Azure provides several services and tools to implement these processes:
1. Azure Data Factory (ADF) – Data Flows
ADF Mapping Data Flows provide a visual, code-free environment for data transformation. Key transformations include:
• Derived Column: Create new columns or modify existing ones — use expressions to trim whitespace, convert types, replace nulls, or standardize formats.
• Filter: Remove rows that do not meet quality criteria.
• Aggregate with GROUP BY: Combine duplicate rows by grouping on a key and selecting MAX, MIN, or FIRST/LAST values for other columns.
• Exists: Compare two streams to find matching or non-matching records.
• Rank / Window: Assign row numbers partitioned by a key and ordered by a timestamp, then filter to keep only the row with rank = 1 — a classic deduplication pattern.
• Assert: Validate data quality rules and route failing rows to an error output.
2. Azure Databricks / Apache Spark
When working in Spark (PySpark or Spark SQL):
• dropDuplicates() removes exact duplicates based on specified columns.
• Window functions such as ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) allow you to keep only the latest record per key.
• na.fill() and na.drop() handle missing values.
• regexp_replace(), trim(), lower(), and to_date() standardize text and date fields.
• Delta Lake provides MERGE (UPSERT) capabilities that inherently prevent duplicates when loading data incrementally. The MERGE INTO … WHEN MATCHED … WHEN NOT MATCHED syntax is essential knowledge for DP-203.
• Delta Lake's OPTIMIZE and VACUUM commands help maintain clean, deduplicated physical storage.
• Structured Streaming with dropDuplicates() and watermarking enables deduplication in near-real-time scenarios.
3. Azure Synapse Analytics
• Synapse Pipelines offer the same Mapping Data Flows as ADF for cleansing and deduplication.
• Dedicated SQL Pools: Use T-SQL with CTEs and ROW_NUMBER() to identify and remove duplicates. Example pattern:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY modified_date DESC) AS rn
FROM staging_table
)
SELECT * FROM cte WHERE rn = 1;
• Serverless SQL Pools: Query data lake files directly and apply cleansing logic using OPENROWSET and T-SQL functions.
• Spark Pools in Synapse: Use PySpark or Scala with the same patterns described above for Databricks.
4. Azure Stream Analytics
For streaming data:
• Use tumbling, hopping, or session windows combined with GROUP BY to aggregate and deduplicate events within a time window.
• LAG() function can detect consecutive duplicate events.
• Built-in temporal operations handle late-arriving data that could otherwise create duplicates.
5. Azure Event Hubs / Kafka
• At-least-once delivery means consumers must handle duplicates. Implementing idempotent writes (e.g., using a unique event ID as a primary or partition key in the target store) is a key pattern.
Common Patterns and Best Practices
• Cleanse early, cleanse often: Apply cleansing as close to the ingestion point as possible (bronze-to-silver layer in a medallion architecture).
• Medallion Architecture: Bronze (raw), Silver (cleansed, deduplicated, conformed), Gold (aggregated, business-ready). The DP-203 exam frequently references this pattern.
• Schema enforcement: Use Delta Lake's schema enforcement to reject records that do not conform to the expected schema at write time.
• Data quality checks: Implement assertions, row counts, and checksum validations in your pipelines.
• Surrogate keys: Generate surrogate keys after deduplication to uniquely identify cleansed records in dimension tables.
• Slowly Changing Dimensions (SCDs): SCD Type 1 (overwrite) and Type 2 (history tracking) require deduplication logic to determine current vs. historical records.
• Idempotent pipelines: Design pipelines so that re-running them does not introduce duplicates. Use MERGE/UPSERT instead of INSERT.
Exam Tips: Answering Questions on Data Cleansing and Deduplication
The DP-203 exam tests both conceptual understanding and practical implementation. Here are specific tips:
1. Know the ROW_NUMBER() pattern cold.
Many questions present a scenario where duplicate rows exist in a staging table and ask how to keep only the latest. The answer almost always involves ROW_NUMBER() OVER (PARTITION BY key ORDER BY date DESC) with a filter for rn = 1. Recognize this pattern in both T-SQL and PySpark contexts.
2. Understand Delta Lake MERGE.
Questions about incremental loading into a Delta table will test your knowledge of the MERGE statement. Know the syntax: MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. This is the preferred pattern for idempotent, deduplication-safe loads.
3. Distinguish between ADF Data Flows and Spark code.
If a question specifies a code-free or low-code approach, think ADF Mapping Data Flows. If it mentions notebooks, PySpark, or Scala, think Databricks or Synapse Spark Pools.
4. Medallion architecture questions.
When asked where cleansing and deduplication should occur, the answer is typically in the silver layer (the bronze-to-silver transformation). Bronze holds raw data; silver holds cleansed and conformed data.
5. Streaming deduplication.
For streaming scenarios, remember that dropDuplicates() with withWatermark() is the Spark Structured Streaming approach. For Azure Stream Analytics, windowed GROUP BY with aggregation handles deduplication. Know when to use each service.
6. Watch for "idempotent" as a keyword.
If a question mentions ensuring a pipeline can be safely re-run, the answer involves MERGE/UPSERT patterns, not plain INSERT. This is a deduplication concern.
7. Handle null values correctly.
Know the difference between na.drop() (removes rows with nulls), na.fill() (replaces nulls with a specified value), and COALESCE() / ISNULL() in T-SQL. Questions may ask for the best approach based on business requirements.
8. Fuzzy matching is typically a Databricks/Spark task.
If a question involves matching similar but not identical records (e.g., customer name variations), the answer usually involves a Spark-based approach with UDFs or libraries, not T-SQL or ADF Data Flows alone.
9. Read all answer options carefully.
Some questions include distractors like using DISTINCT — while DISTINCT removes exact row duplicates, it does not allow you to keep the most recent version of a record by key. ROW_NUMBER() or MERGE is usually the more complete answer.
10. Cost and performance considerations.
If a question asks about the most cost-effective way to deduplicate large datasets in the data lake, consider serverless SQL pools (pay per query) for ad-hoc cleansing vs. dedicated pools or Spark for scheduled, large-scale processing.
11. Know the Assert transformation.
ADF Mapping Data Flows include the Assert transformation for data quality validation. It allows you to define rules and route non-conforming rows to a separate output — a likely exam topic for data quality scenarios.
12. Schema enforcement vs. schema evolution.
Delta Lake can enforce schemas (rejecting bad data) or allow schema evolution (adding new columns). For cleansing purposes, schema enforcement is the more relevant concept — it prevents corrupted records from entering the silver layer.
Summary
Data cleansing and deduplication ensure that your data assets are accurate, consistent, and trustworthy. On Azure, these tasks are accomplished through ADF Data Flows, Spark (in Databricks or Synapse), Delta Lake MERGE, T-SQL ROW_NUMBER() patterns, and streaming deduplication with watermarks. For the DP-203 exam, focus on knowing which tool to use in which scenario, the ROW_NUMBER() deduplication pattern, Delta Lake MERGE syntax, the medallion architecture's role in organizing cleansed data, and the difference between batch and streaming deduplication strategies. Mastering these concepts will prepare you to confidently answer any question on data cleansing and deduplication.
