Introduction
Relevant source files
The following files were used as context for generating this wiki page:
The meltano-state-backend-clickhouse project is a Meltano state backend add-on that enables pipeline state persistence within a ClickHouse warehouse. It registers the clickhouse state-backend scheme, allowing Meltano to store incremental bookmarks, full-table markers, and other pipeline metadata in an OLAP environment. This implementation brings ClickHouse to functional parity with other supported state backends like Postgres, Snowflake, and BigQuery.
Sources: README.md:1-9, src/meltano_state_backend_clickhouse/init.py:1-7
Because ClickHouse is an OLAP column store lacking row-level updates and standard transactional locking primitives, this backend implements specific behaviors to ensure state integrity. It utilizes the ReplacingMergeTree engine for state storage and a custom "best-effort" advisory lock mechanism to handle concurrent state access.
Sources: src/meltano_state_backend_clickhouse/backend.py:3-16, README.md:27-36
Core Architecture and Components
The system is built around the ClickhouseStateStoreManager, which inherits from Meltano's base StateStoreManager. It manages the connection to ClickHouse, the initialization of required tables, and the execution of state operations such as get, set, and delete.
Sources: src/meltano_state_backend_clickhouse/backend.py:156-157, pyproject.toml:39-40
Connection Management
The backend parses connection parameters from a standard ClickHouse URI or individual configuration settings. It uses the clickhouse-connect library to interface with the database.
Sources: src/meltano_state_backend_clickhouse/backend.py:101-153, pyproject.toml:28-29
Component Diagram
The following diagram illustrates the relationship between Meltano, the ClickHouse State Backend, and the ClickHouse database.
Configuration and Settings
The backend is configured via the MELTANO_STATE_BACKEND_URI or specific Meltano settings.
| Setting Name | Description | Default Value |
|---|---|---|
state_backend.clickhouse.host | ClickHouse server hostname | localhost |
state_backend.clickhouse.port | ClickHouse HTTP interface port | 8123 |
state_backend.clickhouse.database | ClickHouse database name | Required |
state_backend.clickhouse.user | ClickHouse username | Required |
state_backend.clickhouse.password | ClickHouse password | "" |
state_backend.clickhouse.secure | Use HTTPS/TLS | False |
state_backend.clickhouse.schema | Database used for state storage | meltano |
state_backend.clickhouse.table | Table name for state storage | state |
Sources: src/meltano_state_backend_clickhouse/backend.py:56-150, README.md:15-25
Data Model and Storage
ClickHouse utilizes specialized engines to simulate state management.
State Table
The state table stores the actual pipeline bookmarks. It uses ReplacingMergeTree(updated_at) to handle updates by keeping the row with the highest version (timestamp) for a given state_id. Queries use the FINAL keyword to collapse these versions during read.
Sources: src/meltano_state_backend_clickhouse/backend.py:202-211, README.md:30-31
Lock Table
The lock table implements best-effort advisory locking. It includes a TTL (Time To Live) as a backstop for stale lock cleanup.
| Field | Type | Description |
|---|---|---|
state_id | String | Identifier for the pipeline state |
lock_id | String | Unique UUID for the specific lock attempt |
locked_at | DateTime | Timestamp of lock acquisition |
Sources: src/meltano_state_backend_clickhouse/backend.py:218-226, tests/test_backend.py:108-115
Advisory Lock Mechanism
Due to the lack of insert-conflict primitives in ClickHouse, the backend employs a "settle-then-decide" logic to prevent double-acquisition of locks by concurrent processes.
Lock Acquisition Flow
- Insert Candidate: The process inserts a row with a unique
lock_idinto the lock table. - Settle: The process waits for
LOCK_SETTLE_SECONDS(default 1.0s) to allow network jitter and simultaneous inserts to resolve. - Verify: The process queries all rows for that
state_id(withoutFINAL). - Decide: If the process's
lock_idis the minimum (lowest) in the set, the lock is acquired. Otherwise, it deletes its candidate row and retries.
State Operations Logic
Setting State
The set operation performs an INSERT into the ReplacingMergeTree. The updated_at column is automatically populated with the current timestamp (now64(3)), which ensures that subsequent reads using FINAL will prioritize this new data.
Sources: src/meltano_state_backend_clickhouse/backend.py:205-209, 230-244
Retrieving State
The get operation retrieves state by querying with the FINAL modifier. This collapses all existing versions of a state_id in ClickHouse's background merge process logic at query time, returning only the most recent entry.
Sources: src/meltano_state_backend_clickhouse/backend.py:246-267, tests/test_backend.py:53-60
Summary
The ClickHouse state backend provides a robust integration for Meltano pipelines to persist state in OLAP environments. By leveraging ReplacingMergeTree for versioned state storage and a sophisticated "settle-then-decide" advisory lock mechanism, it overcomes the inherent limitations of ClickHouse's non-transactional nature to provide reliable state management and concurrency control.
Sources: src/meltano_state_backend_clickhouse/backend.py:3-16, README.md:27-36