True upserts in an append-only world: ReplacingMergeTree and FINAL.

Day 07 of the WClickHouse Open-Source Engineering Series.

You don't need slow SQL UPDATE statements to maintain current state in ClickHouse. WClickHouse harnesses ReplacingMergeTree for lightning-fast upserts.

The Pain Points We Faced

  • Attempting expensive UPDATE mutations on billions of ClickHouse rows
  • Dealing with duplicate state updates in streaming event pipelines
  • Slow analytical queries returning outdated record revisions

The Implementation 

# Configure ReplacingMergeTree with version column for upserts db = WClickHouse( OrderStateModel, db_config, engine="ReplacingMergeTree(version) ORDER BY order_id" ) # Append updated version: ClickHouse deduplicates on merge! db.insert(OrderStateModel(order_id=101, status="shipped", version=2)) # Query guaranteed latest state: latest = db.execute_query("SELECT * FROM orderstatemodel FINAL WHERE order_id = 101")

Why This Architecture Wins

  • ReplacingMergeTree: Declare replacing engines directly in WClickHouse initialization.
  • FINAL Modifier Support: Query true current state with instant background deduplication.
  • Zero Mutation Locks: Writes remain fast appends while ClickHouse cleans duplicates in background.

Verification & Status

Tested and verified against live ClickHouse server instances with 95%+ test coverage. Built for Python 3.9 through 3.14 with Apache Arrow and Pydantic v2.

ClickHouse #Python #DataEngineering #OLAP #BigData #Wisrovi