Deduplicating Data on the Databricks Lakehouse (five major strategies)

Deduplication is a core topic in a Lakehouse because data is stored in files (Delta tables). Unlike many OLTP systems, we do not have enforced primary keys. That means duplicates can exist even when the data “looks” correct.

If you are not yet a member of Medium, you can access the extended version on the SunnyData blog for free.

Why duplicates are dangerous (a simple example)

Imagine you have:

• A fact table for sales with columns like (sale_id, country_id, amount).
• a dimension table country with rows like (country_id, country_name)

Now assume the country dimension contains duplicate entries for the same country_id (e.g., two records that represent the same country, or multiple versions of the same row). When you join sales to a country, a single sales row can match multiple dimension rows, so the join multiplies the facts. Country changed name, it happens:

Result: your SUM becomes wrong (for example, 20000 instead of 10000).

Note that this issue cannot be resolved by changing the join type. A join cannot eliminate duplication; it can only reveal it. Even if you pre-aggregate sales, duplicated dimension rows will still multiply the aggregated outcomes.

AI danger (why this problem will get worse)

Analysts reuse existing patterns.

• BI tools auto-generate SQL.
• AI assistants may produce a “reasonable-looking” query that joins tables and sums amounts.

If there are duplicates in the dimension, even a query that appears correct — such as one written by an AI assistant — may produce inaccurate results, like double-counting. This means deduplication is essential not only for correctness now, but also for ensuring your data remains reliable for self-service users and queries generated automatically by AI tools.

Deduplication strategies in the Lakehouse

Below are several strategies that work well on Delta/Lakehouse. Each one has a different “best use case.” The best teams usually combine two layers:

1. prevent or reduce duplicates early (ingestion/streaming), and
2. Detect/stop duplicates with data quality checks.

Strategy A: Deterministic dedup rule: “latest wins.”

When to use

• You ingest updates in micro-batches (files, Auto Loader, Kafka micro-batch, etc.)
• Duplicates often appear in the incoming batch (due to retries, late arrivals, or replays).

Idea

1. Deduplicate the incoming batch before writing/merging.
2. MERGE into the target using a deterministic rule: “latest record wins.”

To make “latest wins” truly deterministic, you need:

• a stable business key (what identifies the entity, e.g., country_id or customer_id)
• an ordering column (e.g. updated_at, ingest_time, version, sequence_id)
• a tie-breaker if timestamps can be equal (e.g., file name + row number, or a monotonically increasing sequence)

Pros

• Easy to understand
• Deterministic results (same input → same output)
• Works for most batch/micro-batch pipelines

Cons

• Can become expensive at scale if your MERGE requires a large scan of the target
• The “Latest wins” logic can become challenging to maintain as rules grow more complex, for example, when integrating multi-column priority or source ranking.

MERGE INTO dim_country AS t
USING (
  SELECT country_id, country_name, updated_at
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY updated_at DESC) AS rn
    FROM stg_country_changes
  ) x
  WHERE rn = 1
) AS s
ON t.country_id = s.country_id
WHEN MATCHED THEN UPDATE SET
  t.country_name = s.country_name,
  t.updated_at   = s.updated_at
WHEN NOT MATCHED THEN INSERT (country_id, country_name, updated_at)
VALUES (s.country_id, s.country_name, s.updated_at);

Strategy B: Delta Change Data Feed (CDF) + “AUTO CDC” pipelines

When to use

• You have a source that produces changes (inserts/updates/deletes)
• You want a more declarative CDC approach (instead of hand-written MERGE logic everywhere)

Idea

Delta Change Data Feed, in combination with Lakeflow Spark Declarative Pipelines “Auto CDC”, applies changes to your target table. That syntax is optimized for storing Slowly Changed Dimensions and is ideal for deduplication; the simplest option is SCD type 1 (without history). Functionality is optimized for all possible scenarios (including late-arriving data, etc.)

Key details:

• create_auto_cdc_flow() replaced apply_changes()
• stored_as_scd_type expects 1 or 2 (SCD Type 1 / Type 2)
• Deletes can be handled with apply_as_deletes

Why does this help dedup

CDC-based pipelines address merge complexity by handling changes consistently and predictably? By clearly defining change events, including ordering, many duplicate patterns caused by reprocessing can be avoided.

In my opinion, it is the best available approach for dimensions. For transactions (big data), performance can be reduced as it still needs to scan for keys.

CREATE OR REFRESH STREAMING TABLE dim_country_cdc;

CREATE FLOW dim_country_flow
AS AUTO CDC INTO dim_country_cdc
FROM stream(country_cdc)
KEYS (country_id)
APPLY AS DELETE WHEN operation = "DELETE"
SEQUENCE BY sequence_num
COLUMNS * EXCEPT (operation, sequence_num)
STORED AS SCD TYPE 1;

Strategy C: Streaming dedup with Spark Structured Streaming transformWithStateInPandas (bounded duplicates window)

When to use

• You ingest a stream of events.
• Duplicates can arrive late, but only within a bounded window (example: max 24 hours late)
• You want low-latency processing without doing heavy batch-style dedup joins.

Idea

Use stateful streaming deduplication:

• key by event_id (or another unique event key)
• keep state for a limited time (TTL), such as 24 hours
• output only the first occurrence of each key within the TTL window
• Duplicates arriving within the TTL window are dropped.
• After TTL, the state is evicted (so memory stays bounded)

What you get

• One output record per event_id per TTL window
• This matches the rule: “If you’re sure duplicates won’t arrive later than the state window, it’s perfect.”

Pros

• Very efficient for streaming use cases
• State is kept in a small and fast file-based database — RocksDV
• Good “exactly-once-ish” behavior when combined with proper checkpointing

Cons

• Only safe if you really have a bounded lateness/duplication window
• State size must be managed (TTL, watermarks, partitioning strategy)
• Not ideal if duplicates can arrive months later

In my opinion, it is the best solution for processing large transactional data volumes. Below code I successfully implemented.

spark.conf.set("spark.sql.streaming.stateStore.providerClass", "org.apache.spark.sql.execution.streaming.state.RocksDBStateStoreProvider")

from pyspark.sql.types import StructType, StructField, LongType, StringType
from pyspark.sql.streaming import StatefulProcessor, StatefulProcessorHandle
import pandas as pd
from typing import Iterator

# Output schema for the deduplicated records
output_schema = StructType(
    [StructField("id", LongType(), True), StructField("data", StringType(), True)]
)

class DeduplicateProcessor(StatefulProcessor):
    def init(self, handle: StatefulProcessorHandle) -> None:

        self.seen_flag = handle.getValueState(
            "seen_flag", output_schema
        )  
        # schema can be diffrent we don't need to keep everything in store
        # third param is TTL in seconds

    def handleInputRows(
        self, key, rows: Iterator[pd.DataFrame], timer_values
    ) -> Iterator[pd.DataFrame]:

        # we loop all rows for given key in current micro-batch as it is grouping, we can implement some logic here
        for pdf in rows:
            for _, pd_row in pdf.iterrows():
                data = pd_row

        # we are checking is data exisitng for given Key (one from groupBY) in RocksDB
        if not self.seen_flag.exists():

            self.seen_flag.update((data[0],data[1])) # data which will be stored together with our key in RocksDB
            yield pd.DataFrame(
                {"id": key, "data": (data[1],)}
            ) # data which we return to browser

    def close(self):
        # Some DBR versions require close() with no argument
        pass

display(
    spark.readStream.table("default.events")
    .groupBy("id")
    .transformWithStateInPandas(
        statefulProcessor=DeduplicateProcessor(),
        outputStructType=output_schema,
        outputMode="Append",
        timeMode="None",
    )
)

Strategy D: DLT / Lakeflow expectations: count PK occurrences + fail or quarantine (the “count trick”)

When to use

• You want a data quality contract: “This key must be unique.”
• You want to stop the pipeline or isolate bad records.

Idea

Databricks documents a primary key uniqueness validation pattern:

• groupBy(pk).count()
• expectation: count == 1

Two standard operating modes:

Fail fast when duplicates appear (strongest contract)

Use this when downstream correctness matters more than availability. If duplicates appear, the pipeline fails and is connected to monitoring, which requires you to review the cause of the duplicates.

CREATE OR REFRESH MATERIALIZED VIEW country_pk_counts AS
SELECT
  country_id,
  COUNT(*) AS cnt
FROM
  countries
GROUP BY
  country_id;

-- Expectation: fail if any cnt != 1
CREATE OR REFRESH MATERIALIZED VIEW country_pk_assert (
    CONSTRAINT pk_is_unique EXPECT(cnt = 1) ON VIOLATION FAIL UPDATE
  ) AS
SELECT
  *
FROM
  country_pk_counts;

Quarantine duplicates (keep pipeline running)

Use this when you cannot block the pipeline. You keep processing good records and route duplicates to a quarantine table for review and replay.

-- 1) Add per-row duplicate count (cnt) using a window
-- Use PRIVATE so it’s an intermediate dataset (not published to the catalog)
CREATE OR REFRESH PRIVATE MATERIALIZED VIEW country_with_cnt AS
SELECT
  c.*,
  RANK(*) OVER (PARTITION BY country_id ORDER BY timestamp) AS cnt
FROM
  countries c;

-- 2) Clean dataset with a data-quality rule:
-- Drop anything where cnt != 1 (so all rows for duplicated keys are rejected)
CREATE OR REFRESH MATERIALIZED VIEW country_silver (
    CONSTRAINT unique_country_id EXPECT(cnt = 1) ON VIOLATION DROP ROW
  ) AS
SELECT
  country_id,
  country_name,
  cnt
FROM
  country_with_cnt;

-- 3) Quarantine dataset = the reverse condition (cnt > 1)
CREATE OR REFRESH MATERIALIZED VIEW country_duplicated (
    CONSTRAINT duplicated_country_id EXPECT(cnt > 1) ON VIOLATION DROP ROW
  ) AS
SELECT
  country_id,
  country_name,
  cnt
FROM
  country_with_cnt;

Pros

Strategy D is about proving uniqueness and making data quality visible and enforceable. No other solution offers this level of observability.

Cons

Efficiency can be very low due to constant counting, even for medium-sized datasets.

Strategy E: Lakebase synced tables — dedup at sync time using a Timeseries Key

When to use

• You serve data through Lakebase (managed Postgres inside Databricks)
• Synced tables require a Primary Key.
• Your source table can contain duplicate PKs, but you want a clean serving layer.

Idea

During sync configuration:

• Choose the Primary Key
• If duplicates exist, choose a Timeseries Key so that only the latest row per PK is retained.

Pros

• Easy out-of-the-box friendly configuration.
• You can expose a deduped table to apps/users even if the raw lakehouse data is messy.

Cons

• This fixes the serving output, but it does not automatically fix analytics correctness upstream.
• You should still deduplicate earlier for reporting and trustworthy aggregations.
• You still have in Lakehouse duplicated and need to sync back the deduplicated table (sync cost) or read through Lakehouse federation (double compute penalty)

After strategies are bulletproof, use information constraints (PK/FK) to support optimization.

Once you’ve implemented and validated deduplication, you can add informational PK/FK constraints in Databricks (Unity Catalog + Delta). These constraints are not enforced, but they can still help the query optimizer.

If you mark a constraint with RELY, Photon can use it for query rewrites such as:

• eliminating unnecessary joins
• eliminating unnecessary DISTINCT operations

Critical warning

Because constraints are not enforced, you are responsible for ensuring they are true. If you mark an invalid constraint as RELY, you can get incorrect query results.

ALTER TABLE dedup_demo.country_silver
ADD CONSTRAINT country_pk PRIMARY KEY (country_id) NOT ENFORCED RELY;

Notebooks with pipelines from the article are available at: https://github.com/hubert-dudek/medium/tree/main/topics/202602

Hubert Dudek (author)

If you like this blog post, consider buying me a coffee :-) https://ko-fi.com/hubertdudek