ReplacingMergeTree Engine Deep Dive

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/meltano_state_backend_clickhouse/backend.py
• pyproject.toml
• src/meltano_state_backend_clickhouse/init.py
• tests/test_backend.py

The ReplacingMergeTree engine is the core architectural choice for the Meltano ClickHouse state backend. Because ClickHouse is an OLAP (Online Analytical Processing) column store, it lacks traditional row-level UPDATE primitives and unique primary key constraints found in transactional databases like PostgreSQL. To achieve state persistence where the newest pipeline state (bookmarks, markers) must overwrite previous versions, the project leverages this specific table engine.

In this implementation, state updates are modeled as fresh INSERT operations. The ReplacingMergeTree engine, combined with the FINAL modifier during selection, ensures that only the most recent version of a state ID—determined by a version column—is returned to the application. This approach brings ClickHouse to parity with other Meltano state backends like Snowflake or BigQuery.

Sources: README.md:27-33, src/meltano_state_backend_clickhouse/backend.py:12-16

State Table Architecture

The state table is defined with state_id as the primary sorting key and uses a DateTime64 column named updated_at to handle versioning. When background merges occur, ClickHouse retains only the row with the highest updated_at value for each unique state_id.

Table Schema

The ClickhouseStateStoreManager ensures the state table exists with the following structure:

Column	Type	Description
state_id	String	Unique identifier for the pipeline state. Used as the ORDER BY key.
partial_state	Nullable(String)	JSON string containing incremental state data.
completed_state	Nullable(String)	JSON string containing finalized state data.
updated_at	DateTime64(3)	Version column. Defaults to now64(3).

Sources: src/meltano_state_backend_clickhouse/backend.py:192-200

Data Flow for State Retrieval

To guarantee that the application sees the "current" state, queries against the state table must use the FINAL keyword. This collapses superseded rows during execution, even if ClickHouse's background merge process hasn't processed them yet.

The diagram shows how the FINAL modifier forces the engine to resolve duplicates at query time.
Sources: src/meltano_state_backend_clickhouse/backend.py:228-232, README.md:31-33

Advisory Locking with ReplacingMergeTree

ClickHouse does not support standard transactional locks or primary key violation errors on INSERT. To prevent concurrent processes from corrupting state for the same state_id, the project implements a "best-effort" advisory lock using a separate table also powered by ReplacingMergeTree.

Lock Table Schema

Column	Type	Description
state_id	String	The ID being locked.
lock_id	String	A unique UUID generated by the acquiring process.
locked_at	DateTime	Timestamp of acquisition, used as the engine's version column and for TTL.

Sources: src/meltano_state_backend_clickhouse/backend.py:207-214

The Settle-then-Decide Pattern

Because multiple processes might check for a lock simultaneously and both see the table as "empty," an immediate verification is insufficient. The implementation uses a "settle" step where the process inserts its candidate row and then waits (LOCK_SETTLE_SECONDS = 1.0) before checking for a winner.

This diagram illustrates how the lock_id comparison acts as a tie-breaker when multiple rows exist before a merge.
Sources: src/meltano_state_backend_clickhouse/backend.py:317-340, tests/test_backend.py:115-125

Lock Maintenance and TTL

The lock mechanism includes two layers of cleanup to ensure liveness:

1. Active Cleanup: The acquire_lock method explicitly deletes stale rows older than STALE_LOCK_SECONDS (300 seconds) before attempting acquisition.
2. Engine TTL: The table is configured with TTL locked_at + INTERVAL 300 SECOND, allowing ClickHouse to automatically reap dead-process locks during background merges.

Sources: src/meltano_state_backend_clickhouse/backend.py:65-66, src/meltano_state_backend_clickhouse/backend.py:214-215, src/meltano_state_backend_clickhouse/backend.py:285-290

Configuration Parameters

The backend behavior is controlled through Meltano settings that define how to connect to the ClickHouse instance where the ReplacingMergeTree tables reside.

Setting	Type	Default	Description
host	STRING	localhost	ClickHouse server hostname.
port	INTEGER	8123	HTTP interface port.
database	STRING	-	Target database name.
schema	STRING	meltano	Schema/Database for state storage.
table	STRING	state	Base name for the state table.

Sources: src/meltano_state_backend_clickhouse/backend.py:77-130, pyproject.toml:29-37

Implementation Details

The ReplacingMergeTree behavior is primarily encapsulated in the _ensure_tables, set, and get methods of the ClickhouseStateStoreManager.

# From src/meltano_state_backend_clickhouse/backend.py:192-200
self.client.command(
    f"""
    CREATE TABLE IF NOT EXISTS {self.state_table} (
        state_id String,
        partial_state Nullable(String),
        completed_state Nullable(String),
        updated_at DateTime64(3) DEFAULT now64(3)
    ) ENGINE = ReplacingMergeTree(updated_at)
    ORDER BY state_id
    """
)

The set operation performs a simple INSERT (Lines 218-226), while the get operation uses FINAL to resolve the latest version (Lines 236-240).

Summary

The use of the ReplacingMergeTree engine is fundamental to supporting Meltano state in ClickHouse. By treating state changes as immutable inserts and leveraging ClickHouse's merge logic for versioning, the backend overcomes the lack of transactional updates. Furthermore, the specialized lock implementation addresses the lack of uniqueness constraints, ensuring safe concurrent operation in production pipelines.

Sources: src/meltano_state_backend_clickhouse/backend.py:12-23, README.md:27-38