Database Schema Definition
Relevant source files
The following files were used as context for generating this wiki page:
The database schema for the ClickHouse state backend is designed to facilitate the persistence of Meltano pipeline states, such as incremental bookmarks and full-table markers, within a ClickHouse OLAP warehouse. Since ClickHouse lacks traditional transactional primitives like row-level updates or primary key conflict errors, the schema utilizes specific engine types and logical patterns to maintain data integrity and consistency.
The architecture centers around two primary tables: a state storage table and a lock management table. The state storage table uses the ReplacingMergeTree engine to handle state updates, while the lock table provides a best-effort advisory locking mechanism to prevent concurrent write issues across different processes.
Sources: README.md:1-12, src/meltano_state_backend_clickhouse/backend.py:10-25
Core Storage Schema
The primary table stores the JSON-serialized state data for each unique state_id. It is implemented using the ReplacingMergeTree engine, which automatically handles "updates" by keeping only the most recent version of a row based on a timestamp column when merges occur.
State Table Structure
The state table is defined with the following fields:
| Field | Type | Description |
|---|---|---|
state_id | String | Unique identifier for the Meltano state (e.g., pipeline ID). |
partial_state | Nullable(String) | JSON string containing partial or incremental state data. |
completed_state | Nullable(String) | JSON string containing fully completed state data. |
updated_at | DateTime64(3) | The versioning timestamp used by the ReplacingMergeTree engine. Defaults to now64(3). |
Sources: src/meltano_state_backend_clickhouse/backend.py:186-193, src/meltano_state_backend_clickhouse/backend.py:205-214
Data Engine and Retrieval Logic
Because ClickHouse is an OLAP store, it does not perform immediate row-level updates. Instead, multiple versions of the same state_id may exist in storage until a background merge occurs. To ensure the application always retrieves the latest state, queries utilize the FINAL keyword.
The diagram shows how the FINAL modifier ensures the row with the highest updated_at value is returned, simulating an update.
Sources: src/meltano_state_backend_clickhouse/backend.py:14-16, src/meltano_state_backend_clickhouse/backend.py:228-232
Lock Management Schema
The locking mechanism provides a best-effort advisory lock to coordinate access to specific state IDs. This is necessary because ClickHouse does not support unique constraints that would typically be used for locking in transactional databases.
Lock Table Structure
The lock table tracks active and candidate locks:
| Field | Type | Description |
|---|---|---|
state_id | String | The state ID being locked. |
lock_id | String | A unique UUID generated by the process attempting to acquire the lock. |
locked_at | DateTime | Timestamp of the lock attempt. |
Sources: src/meltano_state_backend_clickhouse/backend.py:202-206
Lock Lifecycle and TTL
The lock table includes a Time-To-Live (TTL) as a backstop for stale locks. Rows are automatically reaped after STALE_LOCK_SECONDS (default: 300 seconds).
The flow represents the "insert-then-verify" logic required to handle the lack of insert-conflict primitives in ClickHouse.
Sources: src/meltano_state_backend_clickhouse/backend.py:53-56, src/meltano_state_backend_clickhouse/backend.py:330-360
Configuration and Namespacing
The schema and table names are configurable through Meltano settings or URI parameters.
Schema Settings
| Setting | Environment Variable | Default |
|---|---|---|
schema | MELTANO_STATE_BACKEND_CLICKHOUSE_SCHEMA | meltano |
table | MELTANO_STATE_BACKEND_CLICKHOUSE_TABLE | state |
The backend automatically creates the lock table by appending _lock to the configured state table name.
Sources: src/meltano_state_backend_clickhouse/backend.py:49-50, src/meltano_state_backend_clickhouse/backend.py:108-125, src/meltano_state_backend_clickhouse/backend.py:168-169
Relationship Diagram
The following Entity-Relationship diagram illustrates the logical separation between state storage and lock management within the configured schema.
The diagram highlights that while both tables use state_id as the primary sorting key, they serve distinct operational purposes.
Sources: src/meltano_state_backend_clickhouse/backend.py:186-206, tests/test_backend.py:33-40
Conclusion
The Database Schema Definition for the ClickHouse state backend adapts to the OLAP nature of ClickHouse by utilizing the ReplacingMergeTree engine for state versioning and a custom multi-step insertion logic for advisory locking. By combining FINAL queries with a TTL-backed lock table, the system achieves parity with transactional state backends while operating in a high-performance column store environment.