Data Deduplication

Production engineering guide for data deduplication covering patterns, implementation strategies, and operational best practices.

Data deduplication removes duplicate records from datasets, particularly when data is ingested from multiple sources or during batch processing operations. This process becomes essential when data quality impacts downstream analytics, reporting, or system integrity.

Remove Duplicates During Ingestion

Configure a deduplication policy for a streaming ingestion pipeline

When ingesting data from a source that may produce duplicate events, configure your pipeline to track and eliminate duplicates based on a known identifier field. Most streaming systems support deduplication through a combination of key fields and a timestamp or sequence number.

For example, in Apache Kafka with a consumer group, you can enable idempotent delivery using:

# Producer configuration
bootstrap.servers=localhost:9092
enable.idempotence=true
acks=all
retries=2147483647
max.in.flight.requests.per.connection=5

The consumer must be configured to maintain state for offset tracking, typically using a group.id and enable.auto.commit=false.

Use a primary key constraint during data loading

When writing to a relational database, define a primary key on the fields that uniquely identify each record. This prevents insertion of duplicate rows when using INSERT ... ON DUPLICATE KEY UPDATE or equivalent mechanisms.

Example with PostgreSQL:

CREATE TABLE user_events (
    event_id UUID PRIMARY KEY,
    user_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    data JSONB
);

-- Insert with conflict resolution
INSERT INTO user_events (event_id, user_id, event_type, timestamp, data)
VALUES ('uuid-123', 456, 'click', '2023-01-01 10:00:00', '{"page": "/home"}')
ON CONFLICT (event_id) DO NOTHING;

Implement a deduplication filter in batch processing

In batch jobs, deduplicate by grouping records and selecting the most recent or preferred version. Use SQL window functions to identify duplicates.

-- Example: remove duplicates keeping latest timestamp
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
    FROM raw_events
)
DELETE FROM raw_events
WHERE event_id IN (
    SELECT event_id FROM ranked WHERE rn > 1
);

Handle Duplicate Detection Across Systems

Deduplicate data across multiple ingestion sources

When combining data from several sources, identify duplicate records using a combination of fields that define uniqueness. This requires defining a consistent deduplication key across all sources.

Example with a SQL merge operation:

-- Identify duplicates in a unioned dataset
WITH source_data AS (
    SELECT id, name, email, source_system, timestamp
    FROM (
        SELECT id, name, email, 'system_a' as source_system, ts as timestamp FROM system_a_events
        UNION ALL
        SELECT id, name, email, 'system_b' as source_system, ts as timestamp FROM system_b_events
    )
),
duplicates AS (
    SELECT id, name, email, source_system, timestamp,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC) as rn
    FROM source_data
)
DELETE FROM source_data WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

Configure a de-duplication service for a data lake

Use a structured approach to identify and remove duplicate files or records from storage systems. This typically involves computing checksums or hash values of data blocks.

Example using find and md5sum for file-level deduplication:

# Find duplicate files by content
find /data -type f -exec md5sum {} \; | sort | uniq -d > duplicates.txt

# Remove duplicates, keeping the first occurrence
while read -r hash filename; do
    if [ -f "$filename" ]; then
        echo "Removing: $filename"
        rm "$filename"
    fi
done < <(awk '{print $2}' duplicates.txt)

Detect Duplicate Data Patterns

Identify duplicate records using a hash-based approach

Compute a consistent hash value from key fields to identify potential duplicates. This is particularly useful when comparing data between systems or during migration.

Example with Python and pandas:

import pandas as pd

def compute_hash(df, key_columns):
    """Compute a deterministic hash of specified columns"""
    # Create a combined string of key columns
    combined = df[key_columns].apply(lambda x: '|'.join(x.astype(str)), axis=1)
    return pd.Series(combined).str.encode('utf-8').apply(hash)

# Apply to dataset
df['duplicate_hash'] = compute_hash(df, ['user_id', 'event_type'])
duplicates = df[df.duplicated(subset=['duplicate_hash'], keep=False)]

Find duplicate records in a partitioned dataset

When working with partitioned data, deduplication must be applied within each partition to avoid false positives across partitions.

Example SQL for partitioned table:

-- Remove duplicates within each date partition
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY date_partition, user_id ORDER BY timestamp DESC) as rn
    FROM partitioned_events
)
DELETE FROM partitioned_events
WHERE event_id IN (
    SELECT event_id FROM ranked WHERE rn > 1 AND date_partition = '2023-01-01'
);

Configure Deduplication Behavior

Set up a deduplication window for time-series data

Configure a time window within which records are considered duplicates. This is useful when events may arrive out of order or with slight timing variations.

Example configuration:

# Configuration file for deduplication
deduplication:
  window_seconds: 300  # 5 minutes
  key_fields:
    - user_id
    - event_type
  timestamp_field: event_timestamp

Configure deduplication with custom logic for specific fields

Some systems allow you to define custom logic for when records should be considered duplicates. This often involves writing a function or expression that evaluates multiple conditions.

Example with a SQL-based deduplication rule:

-- Consider records duplicates if user_id matches AND event_type matches AND timestamp is within 5 minutes
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY user_id, event_type 
               ORDER BY timestamp DESC
           ) as rn
    FROM events
    WHERE timestamp >= NOW() - INTERVAL '5 minutes'
)
DELETE FROM events
WHERE event_id IN (
    SELECT event_id FROM ranked WHERE rn > 1
);

Handle Failure Scenarios

Resolve deduplication conflicts in a multi-node system

In distributed systems, race conditions can cause duplicate processing. Configure your system to track which records have been processed using a state store.

Example with a simple lock table approach:

-- Create tracking table
CREATE TABLE deduplication_tracker (
    id UUID PRIMARY KEY,
    processed_at TIMESTAMP DEFAULT NOW(),
    lock_expiration TIMESTAMP
);

-- Try to process and claim the record
BEGIN;
INSERT INTO deduplication_tracker (id, lock_expiration)
VALUES ('uuid-123', NOW() + INTERVAL '1 hour')
ON CONFLICT (id) DO NOTHING;

-- If insert succeeded, proceed with processing
-- If not, record was already being processed
COMMIT;

Detect and recover from failed deduplication attempts

Deduplication operations can fail silently. Monitor for scenarios where duplicates persist after a deduplication run.

Example query to find records that should have been removed:

-- Find potential duplicate records that survived deduplication
SELECT user_id, event_type, COUNT(*) as count
FROM events
GROUP BY user_id, event_type
HAVING COUNT(*) > 1;

Handle partial deduplication failures

When deduplication fails partway through a batch, ensure partial results are not lost. Most systems support resuming from a checkpoint.

Example with error handling:

# Run deduplication job with retry logic
for attempt in {1..3}; do
    if psql -c "
        DELETE FROM events 
        WHERE event_id IN (
            SELECT event_id 
            FROM (
                SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
                FROM events
            ) ranked
            WHERE rn > 1
        );
    "; then
        echo "Deduplication completed successfully"
        break
    else
        echo "Attempt $attempt failed, retrying..."
        sleep 5
    fi
done

Common Pitfalls and Gotchas

Silent failures in deduplication jobs

Deduplication systems often fail silently when they encounter unexpected data types or malformed records. Always validate input data before processing.

Example of a silent failure scenario:

-- This query may not detect duplicates if user_id is null
DELETE FROM events
WHERE event_id IN (
    SELECT event_id 
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
        FROM events
    ) ranked
    WHERE rn > 1
);

Incorrect deduplication keys causing false positives

Using the wrong combination of fields for deduplication can lead to removing valid records. Always test with sample data.

Example of problematic deduplication:

-- Wrong: Using only user_id without timestamp
DELETE FROM events
WHERE event_id IN (
    SELECT event_id 
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
        FROM events
    ) ranked
    WHERE rn > 1
);

Memory issues during large-scale deduplication

Processing very large datasets without proper resource management can cause failures. Always consider batch processing for large tables.

Example with chunked processing:

-- Process in batches to avoid memory issues
DO $$
DECLARE
    batch_size INTEGER := 10000;
    processed_count INTEGER;
BEGIN
    LOOP
        EXECUTE format('
            DELETE FROM events 
            WHERE event_id IN (
                SELECT event_id 
                FROM (
                    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
                    FROM events
                    WHERE event_id IN (
                        SELECT event_id FROM events LIMIT %s
                    )
                ) ranked
                WHERE rn > 1
            )
        ', batch_size);
        
        GET DIAGNOSTICS processed_count = ROW_COUNT;
        EXIT WHEN processed_count = 0;
    END LOOP;
END $$;

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.