The other day I stumbled upon a product that brought back memories of sleepless nights from three years ago. Back then, I was solving the task of data deduplication for pharmacy product analytics in ClickHouse, and it turned into a real nightmare. ReplacingMergeTree worked painfully slow, materialized views consumed insane amounts of CPU, and deduplication happened at best an hour or two after data arrival. That magical moment when you realize ClickHouse documentation slightly embellishes reality.

The Deduplication Problem: When Data Comes from Everywhere

In modern analytics systems, data flows in from multiple sources simultaneously. In my pharmacy case, these were:

  • Multiple POS system terminals sending the same transaction
  • API integrations with suppliers that retried requests on failures
  • Warehouse management systems duplicating inventory updates
  • Mobile applications that sent events multiple times with poor connectivity

The result - up to 20% duplicate records in raw data. With millions of transactions per day, this meant serious distortions in analytics and wrong business decisions.

Why ReplacingMergeTree is Pain

ClickHouse offers ReplacingMergeTree to solve deduplication, but in practice this table creates more problems than it solves:

1. Unpredictable Deduplication

Deduplication only happens during background merges, which ClickHouse runs on its own schedule. This can take 10 minutes to several hours. In my case, critical reports for management showed incorrect numbers until merging occurred. "The data will be correct… eventually" - great answer to the director's question about daily revenue.

2. FINAL Kills Performance

To guarantee deduplicated data, you need to use the FINAL modifier in queries:

SELECT * FROM pharmacy_transactions FINAL
WHERE date = today()

On large data volumes, such queries execute 10-50x slower than regular ones. When you have a 500 million record table, a FINAL query can run for minutes. Like waiting for a webpage to load on dial-up in 2024.

3. Materialized Views Devour CPU

The alternative approach through materialized views was also problematic:

CREATE MATERIALIZED VIEW pharmacy_transactions_deduped
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(transaction_date)
ORDER BY (pharmacy_id, transaction_id)
AS SELECT
    pharmacy_id,
    transaction_id,
    product_id,
    quantity,
    price,
    transaction_date,
    max(updated_at) as updated_at
FROM pharmacy_transactions_raw
GROUP BY pharmacy_id, transaction_id, product_id, quantity, price, transaction_date

Such views consumed up to 40% of cluster CPU during active data writes, slowing down all other operations. Nothing brings more joy than realizing your "optimization" is eating half the cluster resources.

4. Sharding Problems

In distributed setups, ReplacingMergeTree cannot deduplicate data across shards. If the same transaction hits different shards (which easily happens with uneven key distribution), duplicates remain forever. Because why make deduplication actually work when you can leave users guessing?

Real Pain Numbers

Here are concrete metrics from that project:

  • 70 seconds - time to load 250,000 records into ReplacingMergeTree
  • Up to 1 hour - waiting time for background merging
  • 10-50x - query slowdown with FINAL
  • 40% - additional CPU load from materialized views
  • 15-20% - volume of duplicate data in raw tables

Modern Solution: Upstream Deduplication

Recently I discovered an approach that solves this problem radically - deduplicating data before it reaches ClickHouse. Tools like GlassFlow perform deduplication in the data stream between Kafka and ClickHouse.

Disclaimer: I haven't been paid a cent for this - just sharing a genuinely good product that would have saved me tons of headaches.

The principle is simple:

  1. Data flows into Kafka as usual
  2. Stream processor analyzes the flow and removes duplicates by specified keys
  3. Only unique records reach ClickHouse
  4. Use regular MergeTree without all the ReplacingMergeTree problems

Real Results

Testing on the same 250,000 records with 20% duplicates:

  • 125 seconds - total processing time from Kafka to ClickHouse
  • Immediately - deduplicated data availability
  • 0 additional load on ClickHouse
  • 9,000+ records/sec - processing performance

Application in Other Domains

This approach is especially effective for:

E-commerce and Retail

  • Purchase deduplication on API retries
  • Eliminating double-counting of conversions from different sources (Meta Ads + CRM + email)
  • Inventory synchronization between warehouses and stores

Financial Systems

  • Preventing transaction duplication on network failures
  • Consolidating data from different payment providers
  • Eliminating retry attempts in payment processing

Logging and Monitoring

  • Deduplicating events from multiple application instances
  • Eliminating duplicates on HTTP request retries
  • Consolidating metrics from different monitoring systems

Migration Strategy

If you're currently suffering from ReplacingMergeTree, here's an action plan:

1. Current State Analysis

  • Measure the share of duplicate data
  • Evaluate FINAL query execution time
  • Analyze load from materialized views

2. Parallel Deployment

  • Set up stream processor alongside existing system
  • Compare deduplication results
  • Measure performance and latencies

3. Gradual Migration

  • Switch tables one by one
  • Validate data consistency
  • Monitor system performance

Conclusion

ReplacingMergeTree works around duplicates after they've already landed in storage, and that's exactly where it creates problems: query-time merges that aren't guaranteed to have finished, and no way to know from a query alone whether you're seeing final data. Deduplicating upstream - before a row ever reaches ClickHouse - avoids that class of problem entirely instead of managing it.