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.

• GitHub: https://github.com/wisrovi/wclickhouse
• PyPI: https://pypi.org/project/wclickhouse

ClickHouse #Python #DataEngineering #OLAP #BigData #Wisrovi