ClickHouse / ClickHouse/dbt-clickhouse

EXCHANGE TABLES may omit ON CLUSTER when replacing ReplicatedMergeTree tables on one-shard multi-replica clusters

Open
#635 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Python
Stars
362
Forks
177
Avg merge
2d 10h
Merged PRs (30d)
8

Description

### Describe the bug
`dbt-clickhouse` can run `EXCHANGE TABLES` without `ON CLUSTER` for a `ReplicatedMergeTree` table on an `Atomic` database in a one-shard, multi-replica cluster.

This caused one dbt table model to diverge by host: the writer host received a new table UUID/ZooKeeper path, while reader hosts kept serving the previous table UUID/ZooKeeper path.

The likely issue is that `exchange_tables_atomic(backup_relation, existing_relation)` renders `on_cluster_clause(target_relation)` from the already-existing relation. If that existing relation is discovered as non-clustered, the exchange is local-only even though the replacement table was created cluster-wide.

This only appears when replacing an existing table. Initial creation works because dbt creates the model relation directly. On subsequent runs, dbt creates a replacement table and exchanges it with the existing relation; the `ON CLUSTER` decision for that exchange is based on the existing relation metadata.

### Steps to reproduce

1. Use a ClickHouse cluster with one shard and multiple replicas.
2. Configure dbt-clickhouse with `cluster` set, `database_engine: Atomic`, and a table model using `engine: ReplicatedMergeTree`.
3. Run the model once so the table is created. The first run should succeed because there is no existing relation to exchange.
4. Run the same model again, so dbt materializes a replacement table and uses atomic `EXCHANGE TABLES` against the existing relation.
5. Query `system.tables` / `system.replicas` on writer and reader hosts.

The issue appears on replacement of an existing table, not on initial table creation.

### Expected behaviour
`CREATE TABLE`, `SYSTEM SYNC REPLICA`, `EXCHANGE TABLES`, and cleanup should all run consistently `ON CLUSTER`, so every host sees the same final table UUID and ZooKeeper path.
### Code examples, such as models or profile settings
Profile:
```yaml
clickhouse-stage:
type: clickhouse
schema: test
cluster: test_cluster
database_engine: Atomic
host: writer.example.net
port: 9440
user: "{{ env_var('CLICKHOUSE_USER') }}"
password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
secure: true
driver: native
```

Model config:

```yaml
models:
- name: transactions
config:
materialized: table
engine: ReplicatedMergeTree
order_by: "toDate(transaction_date)"
```

Relevant upstream macro:

`{% do exchange_tables_atomic(backup_relation, existing_relation) %}`
Current implementation uses `target_relation` / `existing_relation` for ON CLUSTER:

```jinja
SYSTEM SYNC REPLICA {{ on_cluster_clause(target_relation) }} {{ target_relation.schema }}.{{ target_relation.identifier }}
EXCHANGE {{ obj_types }} {{ old_relation }} AND {{ target_relation }} {{ on_cluster_clause(target_relation) }}
```

Workaround tested successfully:

```jinja
SYSTEM SYNC REPLICA {{ on_cluster_clause(old_relation) }} {{ target_relation.schema }}.{{ target_relation.identifier }}
EXCHANGE {{ obj_types }} {{ old_relation }} AND {{ target_relation }} {{ on_cluster_clause(old_relation) }}
dbt and/or ClickHouse server logs
```

Before workaround:

```
writer: table uuid = b04c43d8-..., zookeeper_path = /clickhouse/test_cluster/tables/test/b04c43d8-.../01, total_replicas = 1
reader: table uuid = dc3cb55d-..., zookeeper_path = /clickhouse/test_cluster/tables/test/dc3cb55d-.../01, total_replicas = 2
```

After workaround:

```
writer: table uuid = 473b1c8c-..., zookeeper_path = /clickhouse/test_cluster/tables/test/473b1c8c-.../01, total_replicas = 3
reader: table uuid = 473b1c8c-..., zookeeper_path = /clickhouse/test_cluster/tables/test/473b1c8c-.../01, total_replicas = 3
```

dbt debug output after workaround showed:

```
CREATE TABLE test.transactions__dbt_backup ON CLUSTER "test_cluster" ...
SYSTEM SYNC REPLICA ON CLUSTER "test_cluster" test.transactions
EXCHANGE TABLES test.transactions__dbt_backup AND test.transactions ON CLUSTER "test_cluster"
DROP TABLE IF EXISTS test.transactions__dbt_backup ON CLUSTER "test_cluster" SYNC
```

Configuration:
* Environment
* dbt version: 1.9.3
* dbt-clickhouse version: 1.9.3
* clickhouse-driver version: 25.8.1.4349
* clickhouse-connect version (if using http): not used
* Python version: 3.12

Contributor guide

Open the contributing guide

Research direction

Start at the exchange_tables_atomic macro and trace its calls to on_cluster_clause for the existing and replacement relations. Reproduce the one-shard, multi-replica replacement flow, then verify that CREATE TABLE, SYSTEM SYNC REPLICA, EXCHANGE TABLES, and cleanup use the cluster consistently and produce matching UUID and ZooKeeper path data on every host.

Written by the indexing model from the issue text.

Assessment

Tech stack
python
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.