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.
