State Backend Class Implementation

Relevant source files

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

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

The State Backend Class Implementation for ClickHouse provides a specialized storage provider for Meltano pipeline state. It enables Meltano to persist incremental bookmarks and full-table markers in a ClickHouse warehouse, achieving parity with other supported backends like Postgres or Snowflake.
Sources: README.md:3-8, src/meltano_state_backend_clickhouse/backend.py:1-13

Because ClickHouse is an OLAP column store, it lacks traditional row-level updates and transaction primitives. This implementation addresses these limitations by using the ReplacingMergeTree engine for state persistence and a "best-effort" advisory locking mechanism for concurrency control.
Sources: src/meltano_state_backend_clickhouse/backend.py:1-13, README.md:32-37

Core Architecture and Data Flow

The central component is the ClickhouseStateStoreManager, which inherits from Meltano's base StateStoreManager. It manages the connection to ClickHouse using the clickhouse-connect library and handles the lifecycle of state and lock tables.
Sources: src/meltano_state_backend_clickhouse/backend.py:168-175, pyproject.toml:31-33

Class Hierarchy

The following diagram illustrates the relationship between the ClickHouse-specific implementation and the Meltano core classes.

Configuration and Connectivity

The backend can be configured via a URI or specific Meltano settings. The connection_params_from_uri function parses the URI and applies overrides from individual settings.
Sources: src/meltano_state_backend_clickhouse/backend.py:108-118, README.md:21-30

Configuration Options

Setting Name	Type	Description	Default
state_backend.clickhouse.host	String	ClickHouse server hostname	localhost
state_backend.clickhouse.port	Integer	ClickHouse HTTP interface port	8123
state_backend.clickhouse.database	String	Database name for state storage	Required
state_backend.clickhouse.user	String	ClickHouse username	Required
state_backend.clickhouse.password	String	ClickHouse password	""
state_backend.clickhouse.secure	Boolean	Connect over HTTPS/TLS	False
state_backend.clickhouse.schema	String	Schema/Database (State storage)	meltano
state_backend.clickhouse.table	String	Table name (State storage)	state

Sources: src/meltano_state_backend_clickhouse/backend.py:53-105, src/meltano_state_backend_clickhouse/backend.py:159-165

Database Schema Implementation

The backend automatically initializes two tables using the ReplacingMergeTree engine to handle updates via versioning rather than row-level modifications.
Sources: src/meltano_state_backend_clickhouse/backend.py:220-222

State Table

Stores the actual pipeline state. The updated_at column serves as the versioning key.

• Table Name: <schema>.<table_name> (Default: meltano.state)
• Engine: ReplacingMergeTree(updated_at)
• Querying: Reads use the FINAL modifier to retrieve only the latest version per state_id.

Lock Table

Used for advisory locking.

• Table Name: <schema>.<table_name>_lock (Default: meltano.state_lock)
• Engine: ReplacingMergeTree(locked_at)
• TTL: locked_at + INTERVAL 300 SECOND (Stale lock cleanup backstop).

Sources: src/meltano_state_backend_clickhouse/backend.py:223-251, README.md:32-35

State Management Operations

The backend implements standard state operations by interacting with the ClickHouse client.

• Set: Inserts a new row with current JSON-serialized state. ClickHouse treats this as a new version.
• Get: Executes a SELECT ... FINAL query to fetch the newest state for a state_id.
• Delete/Clear: Uses DELETE FROM ... or TRUNCATE TABLE commands.
• List: Fetches unique state_id values, supporting glob-style filtering (e.g., dev:*).

Sources: src/meltano_state_backend_clickhouse/backend.py:253-333, tests/test_backend.py:52-78

Advisory Locking Mechanism

ClickHouse lacks INSERT uniqueness constraints, so the backend implements a "Settle-then-Decide" algorithm to prevent race conditions during concurrent state access.
Sources: src/meltano_state_backend_clickhouse/backend.py:355-375

Locking Flow

1. Clean Stale Locks: Synchronously delete rows older than 300 seconds.
2. Insert Candidate: Insert a row with a unique lock_id (UUID) for the state_id.
3. Settle Period: Wait for LOCK_SETTLE_SECONDS (1.0s) to allow concurrent processes to finish their inserts.
4. Verify: Query all rows for the state_id (without FINAL). The winner is the process with the lexicographically smallest lock_id.
5. Release/Retry: If the process did not win, it deletes its candidate row and retries.

Summary

The ClickhouseStateStoreManager bridges the gap between ClickHouse's OLAP design and Meltano's requirement for a transactional state store. By leveraging ReplacingMergeTree for versioning and a settled locking protocol, it ensures data integrity even in environments where standard SQL constraints and row-level updates are unavailable.
Sources: src/meltano_state_backend_clickhouse/backend.py:1-13, README.md:32-40