ClickHouse cluster mode overrides user-set insert_quorum with 'auto', causing silent data loss in insert-overwrite #6171

Summary

In ClickHouse cluster mode (cluster: set on the gateway) and Cloud mode, ClickhouseConnectionConfig._static_connection_kwargs unconditionally sets insert_quorum = "auto", overriding a user-provided connection_settings.insert_quorum. It also writes "auto" back into the user's connection_settings dict, because settings = self.connection_settings or {} aliases it.

# sqlmesh/core/config/connection.py (0.236.1)
settings = self.connection_settings or {}
...
if self.cluster or self.cloud_mode:
    ...
    settings["insert_quorum"] = "auto"

auto is a majority quorum (replicas / 2 + 1, i.e. 3 of 4). That is not enough for the insert-overwrite path of the ClickHouse adapter, and it silently loses data.

Impact: silent data loss in incremental models

_insert_overwrite_by_condition works like this:

1. Fill a temp table with the new rows.
2. Copy the target partition's remaining rows into the same temp table.
3. Run ALTER TABLE target ON CLUSTER … REPLACE PARTITION … FROM temp.

With a majority quorum, an insert into the temp table can return while one replica has not fetched the part yet. For a ReplicatedMergeTree, the ON CLUSTER REPLACE PARTITION is executed by a single replica, and it replaces the partition with the temp-table parts it currently has. If that is the lagging replica, the copied rows are dropped. Every following batch then builds on the truncated partition.

Observed on a 1 shard × 4 replica cluster (INCREMENTAL_BY_TIME_RANGE, batch_size 7, partitioned_by toYear(...)):

• system.query_log: Settings['insert_quorum'] = 'auto' on all SQLMesh queries, despite connection_settings.insert_quorum: 4.
• The copy insert into the temp table finished at 09:21:44.913. The REPLACE PARTITION DDL entry was executed on replica B at 09:21:45.644 (is_initial_query = 0).
• system.part_log: the temp table had two parts (452 and 24,182 rows). Replica B fetched the 24,182-row part only at 09:21:45. The target partition received only the 452-row part on all replicas. several weeks of data disappeared, with no error and all audits passing.

alter_sync = 2 does not help, because it only waits after the replace.

Expected

A user-provided insert_quorum in connection_settings is respected (e.g. only default to auto if not set). The user's settings dict is not mutated.

Workaround

We patch _static_connection_kwargs to re-apply the configured value. With insert_quorum: 4, SELECT getSetting('insert_quorum') over the SQLMesh connection returns 4 instead of auto.

Suggestion

Use settings.setdefault("insert_quorum", "auto") and copy the dict (settings = dict(self.connection_settings or {})). Possibly also document that the insert-overwrite path needs a full quorum (= number of replicas) on multi-replica clusters, or make the adapter sync the temp table before REPLACE PARTITION.

Related: #6008 (cluster-mode CTAS on every replica).

Environment

• sqlmesh 0.236.1
• ClickHouse 26.8.36.3, self-hosted, 1 shard × 4 replicas, ReplicatedMergeTree, gateway with cluster: default