Full-Table Markers

Relevant source files

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

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

Full-table markers are a specific type of pipeline state persisted within ClickHouse to track the progress and metadata of Meltano data integration jobs. Within the meltano-state-backend-clickhouse project, these markers are stored as part of the broader state management system, which includes incremental bookmarks and other synchronization metadata.

The system leverages ClickHouse's OLAP capabilities to store and retrieve these markers efficiently. Because ClickHouse lacks traditional row-level updates, the markers are managed using a ReplacingMergeTree engine, ensuring that only the most recent version of a marker for a specific state_id is considered valid during retrieval.
Sources: README.md:1-10, src/meltano_state_backend_clickhouse/backend.py:1-13

Architecture and Storage

The storage of full-table markers is handled by the ClickhouseStateStoreManager. The backend creates a dedicated state table and a corresponding lock table within a specified ClickHouse database (defaulting to meltano).

State Table Schema

The markers are stored in a table with a schema designed to handle versioning through ClickHouse's merging mechanism. The updated_at field serves as the versioning column.

Field	Type	Description
state_id	String	Unique identifier for the pipeline/job state.
partial_state	Nullable(String)	JSON string containing in-progress state data.
completed_state	Nullable(String)	JSON string containing finalized state data.
updated_at	DateTime64(3)	The versioning key used by ReplacingMergeTree.

Sources: src/meltano_state_backend_clickhouse/backend.py:228-237, README.md:32-34

Table Engine Logic

The use of ReplacingMergeTree(updated_at) is critical for state consistency. Since ClickHouse does not support row-level UPDATE operations, every time a marker is updated, a new row is inserted. When querying, the backend uses the FINAL keyword to ensure that ClickHouse collapses these rows and returns only the entry with the highest updated_at value.

The diagram above illustrates how the ReplacingMergeTree engine handles multiple versions of the same state_id.
Sources: src/meltano_state_backend_clickhouse/backend.py:5-10, src/meltano_state_backend_clickhouse/backend.py:237, src/meltano_state_backend_clickhouse/backend.py:274

Data Flow for Marker Management

The ClickhouseStateStoreManager implements several methods to manage the lifecycle of full-table markers.

Setting and Retrieving State

• set(state: MeltanoState): This method serializes the partial_state and completed_state into JSON and inserts a new row into the ClickHouse state table.
• get(state_id: str): Retrieves the latest marker for a given ID. It utilizes SELECT ... FINAL to filter out superseded versions.

Sources: src/meltano_state_backend_clickhouse/backend.py:255-269, src/meltano_state_backend_clickhouse/backend.py:271-291

Deletion and Cleanup

• delete(state_id: str): Removes state for a specific ID using the ClickHouse DELETE command.
• clear_all(): Truncates the state table, effectively removing all markers.

Sources: src/meltano_state_backend_clickhouse/backend.py:293-301, src/meltano_state_backend_clickhouse/backend.py:303-315

Concurrency and Locking

To prevent race conditions when multiple processes attempt to update the same full-table markers, the backend implements a "best-effort" advisory lock.

Lock Mechanism

Since ClickHouse lacks unique constraints or primary key violation errors, the locking mechanism follows an "insert-then-verify" pattern with a settle period.

1. Insert: A candidate row is inserted into the _lock table with a unique lock_id (UUID).
2. Settle: The process waits for LOCK_SETTLE_SECONDS (1.0 second).
3. Verify: The process queries the lock table for all candidate rows for that state_id. If its lock_id is the lexicographically smallest (the "winner"), the lock is acquired.
4. Cleanup: Stale locks (older than 5 minutes) are cleaned up before acquisition attempts.

The sequence diagram shows the settle-then-decide logic required to achieve consistency in ClickHouse.
Sources: src/meltano_state_backend_clickhouse/backend.py:11-13, src/meltano_state_backend_clickhouse/backend.py:382-440, tests/test_backend.py:115-150

Configuration Options

The behavior of the state backend and its markers can be tuned through environment variables or meltano.yml.

Setting	Environment Variable	Default
host	MELTANO_STATE_BACKEND_CLICKHOUSE_HOST	localhost
port	MELTANO_STATE_BACKEND_CLICKHOUSE_PORT	8123
schema	MELTANO_STATE_BACKEND_CLICKHOUSE_SCHEMA	meltano
table	MELTANO_STATE_BACKEND_CLICKHOUSE_TABLE	state
secure	MELTANO_STATE_BACKEND_CLICKHOUSE_SECURE	False

Sources: src/meltano_state_backend_clickhouse/backend.py:59-122, README.md:20-25, pyproject.toml:29-37

Full-table markers provide a reliable way to maintain pipeline state in ClickHouse by adapting traditional state management patterns to an OLAP-optimized architecture. By utilizing ReplacingMergeTree and a custom advisory locking strategy, the system ensures data integrity even in the absence of standard transactional primitives.