Read Operations and the FINAL Modifier
Relevant source files
The following files were used as context for generating this wiki page:
ClickHouse is an OLAP column store designed for high-performance analytics, which intentionally lacks row-level UPDATE primitives and insert-uniqueness constraints found in transactional databases. To manage state updates for Meltano, the meltano-state-backend-clickhouse project utilizes the ReplacingMergeTree engine. This engine handles state persistence by keeping multiple versions of a row and resolving them during background merges or explicitly during read operations.
The FINAL modifier is critical to the backend's read operations. Since ClickHouse does not perform immediate row-level updates, several versions of the same state_id may exist simultaneously in storage. Applying the FINAL modifier during a SELECT query forces ClickHouse to collapse these superseded versions on the fly, ensuring that the operation returns only the most recent version (highest updated_at) for each unique key.
Sources: README.md:31-36, src/meltano_state_backend_clickhouse/backend.py:10-18
Storage Architecture and Table Schema
The backend initializes two primary tables: the state table and the lock table. Both utilize the ReplacingMergeTree engine to manage data versioning.
State Table Structure
The state table stores the actual Meltano pipeline state, including partial and completed bookmarks. It is ordered by state_id and versioned by updated_at.
| Column | Type | Description |
|---|---|---|
state_id | String | Unique identifier for the Meltano state (Primary Key). |
partial_state | Nullable(String) | JSON string of the partial pipeline state. |
completed_state | Nullable(String) | JSON string of the completed pipeline state. |
updated_at | DateTime64(3) | Versioning timestamp used for merging (default now64(3)). |
Sources: src/meltano_state_backend_clickhouse/backend.py:228-237, tests/test_backend.py:53-58
Logic Flow for State Retrieval
When a read operation is performed, the system must account for the asynchronous nature of ClickHouse merges. The following diagram illustrates how the FINAL modifier ensures data consistency during a get request.
The FINAL modifier is essential here because, without it, a simple SELECT might return multiple rows for the same state_id if a background merge hasn't occurred yet.
Sources: src/meltano_state_backend_clickhouse/backend.py:254-273
Read Operations and Methods
The ClickhouseStateStoreManager implements several methods that rely on the FINAL modifier to provide a consistent view of the state.
State Retrieval: get(state_id)
The get method retrieves the merged state for a specific job. It uses the FINAL keyword in the SQL query to ensure the "newest write wins" logic.
SELECT partial_state, completed_state FROM <state_table> FINAL
WHERE state_id = {state_id:String} LIMIT 1
Sources: src/meltano_state_backend_clickhouse/backend.py:254-263, tests/test_backend.py:56-58
Discovering State IDs: get_state_ids(pattern)
To list available state identifiers, the backend queries the state table with SELECT DISTINCT state_id ... FINAL. This prevents duplicate IDs from appearing in the results when multiple un-merged versions of a state record exist in the database.
Sources: src/meltano_state_backend_clickhouse/backend.py:299-317, tests/test_backend.py:65-70
Global Cleanup: clear_all()
Before truncating the table, clear_all() performs a count of existing states using SELECT count() ... FINAL. This ensures the returned count reflects the actual number of logical states rather than the total number of physical rows (versions) in the table.
Sources: src/meltano_state_backend_clickhouse/backend.py:286-297, tests/test_backend.py:73-77
Implementation of Advisory Locking
While read operations use FINAL for consistency, the locking mechanism uses a more nuanced approach because FINAL only resolves to the newest row by timestamp, not necessarily the winner of a race condition.
The Best-Effort Lock
ClickHouse does not support primary key violations on INSERT. The backend implements an "insert-then-verify" pattern.
The lock verification uses _candidate_lock_ids, which deliberately skips the FINAL modifier. This is because the tie-break logic requires seeing every row inserted by concurrent processes to correctly determine the winner via the lowest UUID.
Sources: src/meltano_state_backend_clickhouse/backend.py:326-368, tests/test_backend.py:108-158
Configuration Summary
The backend behavior is influenced by several settings defined in the project.
| Setting | Default | Description |
|---|---|---|
state_backend.clickhouse.schema | meltano | Database where state and lock tables reside. |
state_backend.clickhouse.table | state | Name of the state storage table. |
LOCK_SETTLE_SECONDS | 1.0 | Time to wait before verifying a lock acquisition. |
STALE_LOCK_SECONDS | 300 | Period after which a lock is considered stale. |
Sources: src/meltano_state_backend_clickhouse/backend.py:52-57, pyproject.toml:29-36
Conclusion
The use of the FINAL modifier is a cornerstone of the meltano-state-backend-clickhouse architecture. It bridges the gap between ClickHouse's OLAP-optimized storage and the requirement for transactional-like state management in Meltano. By combining ReplacingMergeTree with FINAL on read paths and a settle-then-verify strategy for locking, the backend ensures state consistency even in the absence of traditional row-level updates.
Sources: src/meltano_state_backend_clickhouse/backend.py:10-18, README.md:31-36