Configuring meltano.yml

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

Configuring the ClickHouse state backend in meltano.yml allows Meltano to persist pipeline state, such as incremental bookmarks and full-table markers, within a ClickHouse warehouse. This integration enables ClickHouse to function as a first-class state storage provider alongside other supported databases like Postgres or Snowflake.

The configuration involves defining a connection URI or providing individual settings to the clickhouse state backend scheme. The backend utilizes a ReplacingMergeTree engine to handle state updates in an OLAP environment, ensuring the most recent state is retrieved during pipeline execution.

Sources: README.md:1-12, src/meltano_state_backend_clickhouse/backend.py:12-25

Primary Configuration

The core configuration for the state backend is defined under the state_backend key in meltano.yml. The primary requirement is the uri, which follows the standard ClickHouse connection string format.

state_backend:
  uri: clickhouse://user:password@host:8123/meltano

Sources: README.md:21-25, src/meltano_state_backend_clickhouse/backend.py:127-134

URI Parsing Logic

The backend parses the URI to extract connection parameters. If specific settings are provided in addition to the URI, they act as overrides.

Configuration Settings

The following table details the specific settings available for the ClickHouse state backend. These settings can be configured in meltano.yml under state_backend.clickhouse or via environment variables.

Setting	Type	Default	Description
host	String	localhost	ClickHouse server hostname
port	Integer	8123	ClickHouse HTTP interface port
database	String	(Required)	ClickHouse database name
user	String	(Required)	ClickHouse username
password	String	""	ClickHouse password (Sensitive)
secure	Boolean	False	Use HTTPS/TLS for connection
schema	String	meltano	Database/Schema used for state storage
table	String	state	Table name for state storage

Sources: pyproject.toml:29-36, src/meltano_state_backend_clickhouse/backend.py:65-115, README.md:27-30

Database Architecture

The backend creates two tables within the specified schema: a state table and a lock table. Because ClickHouse does not support standard row-level updates or primary key conflict errors, the backend uses specific table engines to manage state consistency.

State Storage Structure

State is stored in a table (default meltano.state) using the ReplacingMergeTree engine. This engine uses the updated_at column as a versioning mechanism.

Column	Type	Description
state_id	String	Unique identifier for the pipeline state
partial_state	Nullable(String)	JSON string of the partial state
completed_state	Nullable(String)	JSON string of the completed state
updated_at	DateTime64(3)	High-precision timestamp for versioning

Sources: src/meltano_state_backend_clickhouse/backend.py:218-228, README.md:35-37

Lock Management Structure

To prevent concurrent writes to the same state_id, the backend implements a "best-effort" advisory lock.

Lock Acquisition Flow

The backend uses an "insert-then-verify" pattern with a settle period to handle concurrency in the absence of transactional locks.

1. Cleanup: Remove stale locks older than 300 seconds.
2. Insert: Insert a candidate lock row with a unique lock_id.
3. Settle: Wait for LOCK_SETTLE_SECONDS (1.0s) to allow network jitter and concurrent inserts to propagate.
4. Verify: Query all candidate locks for the state_id. If the current lock_id is the minimum (lowest), the lock is acquired.
5. Release: Delete the lock row upon completion.

Summary

Configuring meltano.yml for the ClickHouse state backend requires defining a uri and optionally overriding specific parameters like host, port, or table. The system is designed to work within ClickHouse's OLAP constraints by utilizing ReplacingMergeTree for versioned state storage and a settle-based locking mechanism to ensure data integrity during concurrent pipeline runs.

Sources: README.md:32-41, src/meltano_state_backend_clickhouse/backend.py:12-25