Your MAU chart shows a 12% month-over-month increase. Your CFO asks whether this is real growth or a data artifact. You run a distinct count on user_id and get 2.3 million. You run a count with a explicit dedup key and get 2.0 million. The 300,000-row gap is duplicates — records that your pipeline should have caught but did not.

Duplicate records are the most common cause of metric inflation in production data lakes. They come from three places: retries without idempotency keys, window-function dedup with wrong partition columns, and late-arriving data that bypasses the merge logic. Each one produces a different failure signature, and each one requires a different fix.

The Three Sources of Duplicates

1. Ingestion Retries Without Idempotency Keys

Your Kafka consumer reads a batch of events, processes them, and writes to S3. The consumer crashes after writing but before committing the offset. On restart, it re-processes the same batch. Every event in that window is written twice.

The fix is a dedup key embedded in each record — a composite of (source, event_id) that is hashed and stored as a separate column.

import hashlib
import pyarrow.parquet as pq
from pyarrow import Table

def dedup_table(table: Table, dedup_key: str = "dedup_id") -> Table:
    seen = set()
    mask = []
    for i in range(table.num_rows):
        row_key = table.column(dedup_key)[i].as_py()
        if row_key in seen:
            mask.append(False)
        else:
            seen.add(row_key)
            mask.append(True)
    return table.filter(mask)

This works for batch reprocessing but does not scale to streaming. For streaming, the dedup belongs in the sink connector or the target table’s merge logic.

2. Window-Function Dedup With Wrong Partitions

The most common dedup pattern uses ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ingestion_time DESC). This works when user_id is the correct grain. It fails when the same user sends events from multiple devices, or when the same transaction is recorded under slightly different timestamps.

-- This looks correct but misses duplicates
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id, event_type
    ORDER BY ingestion_ts DESC
  ) AS rn
  FROM raw_events
) WHERE rn = 1

If event_type is NULL for 15% of rows, those rows share a single partition slot. The window function picks one arbitrarily and drops the rest. Metrics based on COUNT(DISTINCT user_id) after this dedup are wrong.

The fix: use a natural key that includes all columns that define uniqueness.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id, event_type, COALESCE(session_id, ''), DATE(timestamp)
    ORDER BY ingestion_ts DESC
  ) AS rn
  FROM raw_events
) WHERE rn = 1

This catches duplicates at the transaction grain. Test the partition key by running COUNT(*) vs COUNT(DISTINCT concat(user_id, event_type, session_id)) on a sample.

3. Late-Arriving Data That Bypasses Merge Logic

Delta Lake and Iceberg support MERGE operations that upsert based on a match key. If the merge key is not the same as the dedup key, late-arriving records insert new rows instead of updating existing ones.

from delta import DeltaTable

delta_table = DeltaTable.forPath(spark, "s3://lake/events")

# Merge on order_id — but if order_id is NULL, every late record inserts
spark.sql("""
  MERGE INTO events AS target
  USING updates AS source
  ON target.order_id = source.order_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *
""")

If order_id is NULL for 5% of records, every batch of late-arriving data inserts 5% new rows. Those rows accumulate. After 20 batches, the table has 100% more rows than unique orders.

The fix: enforce a NOT NULL constraint on the merge key. Reject records that cannot be merged.

How to Measure Your Duplicate Rate

Run a row-count-to-unique-count ratio across your most critical tables. If the ratio exceeds 1.05 (5% duplicates), you have a problem.

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT concat(user_id, event_type, session_id)) AS unique_rows,
  ROUND((COUNT(*) - COUNT(DISTINCT concat(user_id, event_type, session_id))) * 100.0 / COUNT(*), 2) AS duplicate_pct
FROM raw_events

Schedule this as a dbt test that alerts when duplicate_pct > 1.0.

Production Dedup Strategy

Layer	Method	Overhead	Accuracy
Ingestion	Idempotency key + hash column	Low — one hash per record	High — catches retries
Storage	MERGE with NOT NULL natural key	Medium — compaction cost	High — blocks insertion duplicates
Query	ROW_NUMBER() with correct partition key	Low — per-query cost	Moderate — depends on partition grain
Audit	Row-count ratio dbt test	Negligible	Detection only, no prevention

Use all four. Ingestion dedup prevents the write, storage dedup prevents the insert, query dedup protects downstream consumers, and the dbt test catches what the first three miss.

Trade-offs

Idempotency keys add 16 bytes per record. For tables with billions of rows, this is a non-issue. For high-cardinality streaming with tight latency SLAs, the hash computation adds microseconds per event.

MERGE operations are 20-40% slower than INSERT. If your table receives append-only data from a trusted source, skip the merge and use INSERT with a post-ingestion dedup pass. Reserve MERGE for tables where late-arriving updates are common.

ROW_NUMBER() dedup on every query adds CPU. Materialize the dedup into a view or a separate table if the query runs more than once per hour. Otherwise, the per-query cost is acceptable.

Scenario	Recommended Approach
Streaming ingestion with retries	Idempotency key + hash at producer
Batch ingestion from unreliable sources	MERGE with NOT NULL natural key
Ad-hoc analytics on raw data	ROW_NUMBER() per query
BI dashboards and executive metrics	Materialized dedup table + daily row-count audit

Your metrics are only as reliable as your dedup strategy. If you cannot explain how every row in your table is uniquely identified, your growth numbers are guesses.