Self-Hosted ClickHouse Deployment

Relevant source files

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

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

The meltano-state-backend-clickhouse extension enables Meltano to use a self-hosted ClickHouse instance as a state backend. This allows pipeline state, such as incremental bookmarks and full-table markers, to be persisted in a ClickHouse warehouse, providing parity with other traditional RDBMS backends like Postgres or Snowflake.

This backend is specifically designed to handle ClickHouse's nature as an OLAP (Online Analytical Processing) store. Since ClickHouse does not support standard row-level updates or primary-key uniqueness constraints, this implementation utilizes specific table engines and a "best-effort" advisory locking mechanism to ensure state consistency and prevent race conditions during concurrent pipeline runs.

Sources: README.md:3-10, src/meltano_state_backend_clickhouse/backend.py:1-15

Architecture and Data Storage

The backend manages two primary tables within a configured ClickHouse database: a state table for persisting pipeline bookmarks and a lock table for managing concurrency.

State Table Schema

The state table uses the ReplacingMergeTree engine. This is critical because ClickHouse lacks row-level UPDATE primitives. By using ReplacingMergeTree(updated_at) keyed by state_id, the system can "update" state by inserting new rows. When queries are performed with the FINAL modifier, ClickHouse collapses these rows so that only the newest version (the one with the highest updated_at value) is returned.

Database Components and Engines

Table Type	Engine	Purpose
State Table	ReplacingMergeTree(updated_at)	Stores JSON-encoded pipeline state. Uses FINAL on reads for row-level update simulation.
Lock Table	ReplacingMergeTree(locked_at)	Manages advisory locks. Includes a TTL for automatic stale lock cleanup.

Sources: src/meltano_state_backend_clickhouse/backend.py:235-258, README.md:31-35

Concurrency and Locking Logic

Because ClickHouse does not provide INSERT uniqueness or conflict errors, the backend implements a "settle-then-decide" locking pattern. This mechanism is necessary to prevent multiple processes from simultaneously claiming a lock for the same state_id.

The Settle-then-Decide Flow

1. Cleanup: The process first attempts to delete stale locks (older than 300 seconds).
2. Candidate Insertion: The process generates a unique lock_id (UUID) and inserts a row into the lock table.
3. Settle Period: The process waits for a LOCK_SETTLE_SECONDS (default 1.0s) interval. This allows any near-simultaneous race participants to also insert their rows.
4. Verification: The process queries all rows for the state_id (without using FINAL).
5. Winner Selection: The process with the lexicographically lowest lock_id wins. If the current process lost, it deletes its candidate row and retries.

Configuration and Connectivity

Deployment requires configuring Meltano to point to the ClickHouse instance using a URI or specific environment variables.

Connection Parameters

Configuration can be handled via the MELTANO_STATE_BACKEND_URI or granular settings.

Setting	Environment Variable	Default	Description
host	CLICKHOUSE_HOST	localhost	Server hostname.
port	CLICKHOUSE_PORT	8123	HTTP interface port.
database	CLICKHOUSE_DATABASE	-	Target database name.
user	CLICKHOUSE_USER	-	Authentication username.
password	CLICKHOUSE_PASSWORD	-	Authentication password.
secure	CLICKHOUSE_SECURE	False	Use HTTPS/TLS.
schema	CLICKHOUSE_SCHEMA	meltano	Database/Schema for state storage.
table	CLICKHOUSE_TABLE	state	Table name for state storage.

Sources: src/meltano_state_backend_clickhouse/backend.py:61-125, README.md:18-28

Configuration Example

state_backend:
  uri: clickhouse://user:password@host:8123/meltano
  clickhouse:
    secure: true
    table: custom_state_table

Sources: README.md:25-29, src/meltano_state_backend_clickhouse/backend.py:175-185

Technical Implementation Details

The ClickhouseStateStoreManager class inherits from Meltano's StateStoreManager and overrides core methods to interact with the ClickHouse driver (clickhouse-connect).

Key Methods

• _ensure_tables(): Executed on initialization; creates the database and the state/lock tables using SQL commands if they do not exist.
• set(state): Inserts a new state row. Since the table uses ReplacingMergeTree, this effectively supersedes the old state once background merges occur or FINAL is used.
• get(state_id): Retrieves state using SELECT ... FROM table FINAL. This ensures only the most recent state is returned.
• acquire_lock(state_id): A context manager that implements the custom locking logic to protect state during writes.

Sources: src/meltano_state_backend_clickhouse/backend.py:226-260, src/meltano_state_backend_clickhouse/backend.py:262-277, src/meltano_state_backend_clickhouse/backend.py:285-305, src/meltano_state_backend_clickhouse/backend.py:379-438

Dependency Requirements

The backend requires clickhouse-connect (>=0.7) and meltano (>=3.7). For testing, testcontainers[clickhouse] is utilized to provide a real ClickHouse environment.

Sources: pyproject.toml:29-32, pyproject.toml:49-52

Summary

The Self-Hosted ClickHouse Deployment for Meltano State provides a robust way to store pipeline bookmarks in an OLAP environment. By leveraging the ReplacingMergeTree engine and a specialized multi-step locking algorithm, it overcomes ClickHouse's lack of traditional ACID transaction primitives to provide a reliable state management solution.