High-Level Architecture Design
Relevant source files
The following files were used as context for generating this wiki page:
The ClickHouse State Backend for Meltano provides a mechanism to persist pipeline state—such as incremental bookmarks and full-table markers—within a ClickHouse warehouse. This implementation allows Meltano to utilize ClickHouse as a state store, bringing it to parity with other supported backends like Postgres or Snowflake. Sources: README.md:3-8, src/meltano_state_backend_clickhouse/backend.py:1-15
The architecture is specifically designed to handle ClickHouse's nature as an OLAP (Online Analytical Processing) store. Since ClickHouse lacks row-level updates and traditional ACID transactions, the backend implements state management using ReplacingMergeTree engines for versioning and a custom "best-effort" advisory lock system to manage concurrency. Sources: src/meltano_state_backend_clickhouse/backend.py:4-15, README.md:30-38
Core Components
The system is centered around the ClickhouseStateStoreManager, which inherits from Meltano's base StateStoreManager. It manages the connection to ClickHouse and handles the lifecycle of state and lock tables. Sources: src/meltano_state_backend_clickhouse/backend.py:180-184, src/meltano_state_backend_clickhouse/init.py:1-7
Architecture Overview Diagram
This diagram illustrates the relationship between the Meltano core, the ClickHouse State Backend, and the ClickHouse database.
Data Schema and Storage
The backend utilizes two primary tables within a configurable schema (defaulting to meltano): the state table and the lock table. Both tables use the ReplacingMergeTree engine, which is critical for simulating updates in ClickHouse by keeping only the newest record based on a specific version column. Sources: src/meltano_state_backend_clickhouse/backend.py:230-264, README.md:39-40
| Table Type | Engine | Key Column | Version Column | Purpose |
|---|---|---|---|---|
| State Table | ReplacingMergeTree | state_id | updated_at | Stores partial and completed JSON state objects. |
| Lock Table | ReplacingMergeTree | state_id | locked_at | Manages best-effort advisory locks for state IDs. |
Sources: src/meltano_state_backend_clickhouse/backend.py:237-264
State Table Structure
state_id: String (Primary Key)partial_state: Nullable(String) - JSON representation of partial state.completed_state: Nullable(String) - JSON representation of completed state.updated_at: DateTime64(3) - The versioning timestamp.
Sources: src/meltano_state_backend_clickhouse/backend.py:237-245
State Retrieval Logic
Because ClickHouse does not delete old versions immediately, all read operations for a specific state_id must use the FINAL modifier. This ensures that the query returns only the most recent version of the state as determined by the updated_at timestamp. Sources: src/meltano_state_backend_clickhouse/backend.py:281-285, README.md:33-34
Concurrency and Locking Mechanism
ClickHouse does not support standard primary key violation errors on INSERT. To prevent race conditions during state updates, the backend implements a specialized acquire_lock method. This process uses a "settle-then-decide" strategy. Sources: src/meltano_state_backend_clickhouse/backend.py:11-15, src/meltano_state_backend_clickhouse/backend.py:358-380
Lock Acquisition Flow
- Cleanup: Stale locks older than
STALE_LOCK_SECONDS(300s) are deleted. Sources: src/meltano_state_backend_clickhouse/backend.py:46-47, src/meltano_state_backend_clickhouse/backend.py:341-346 - Candidate Insertion: The backend inserts a candidate row with a unique
lock_id(UUID). Sources: src/meltano_state_backend_clickhouse/backend.py:408-412 - Settle Period: The process waits for
LOCK_SETTLE_SECONDS(1.0s) to allow concurrent race participants to insert their own rows. Sources: src/meltano_state_backend_clickhouse/backend.py:52-53, src/meltano_state_backend_clickhouse/backend.py:416-417 - Verification: The backend queries all candidate rows for that
state_id(withoutFINAL). If the locallock_idis the lexicographically smallest, the lock is acquired. Sources: src/meltano_state_backend_clickhouse/backend.py:351-356, src/meltano_state_backend_clickhouse/backend.py:418-420 - Cleanup/Retry: If the tie-break is lost, the candidate row is removed and the process retries. Sources: src/meltano_state_backend_clickhouse/backend.py:422-427
Configuration
The backend is configured via a URI or specific environment variables. It registers settings as Meltano entry points. Sources: pyproject.toml:31-41, src/meltano_state_backend_clickhouse/backend.py:73-125
| Setting | Environment Variable | Default | Description |
|---|---|---|---|
host | CLICKHOUSE_HOST | localhost | ClickHouse server hostname. |
port | CLICKHOUSE_PORT | 8123 | HTTP interface port. |
database | CLICKHOUSE_DATABASE | - | Target database for state. |
schema | CLICKHOUSE_SCHEMA | meltano | Database/Schema for state storage. |
table | CLICKHOUSE_TABLE | state | Table name for state storage. |
secure | CLICKHOUSE_SECURE | False | Use HTTPS/TLS. |
Sources: src/meltano_state_backend_clickhouse/backend.py:45-48, src/meltano_state_backend_clickhouse/backend.py:73-125, src/meltano_state_backend_clickhouse/backend.py:168-175
Conclusion
The high-level architecture of the ClickHouse state backend successfully adapts Meltano's state management requirements to the constraints of an OLAP database. By leveraging ReplacingMergeTree for versioning and a deterministic tie-breaking mechanism for locking, it provides a reliable state storage solution for high-volume data environments. Sources: src/meltano_state_backend_clickhouse/backend.py:4-15, README.md:30-38