Unit tests fail intermittently on multi-replica ClickHouse Cloud (Accepted columns for expected output are: []) #737

Describe the bug

On a ClickHouse Cloud service with 2 replicas, unit tests fail intermittently with:

Invalid column name: 'org_id' in unit test fixture for expected output.
Accepted columns for expected output are: []

Which tests fail changes on every run. Two runs of the same commit, 23 minutes apart:

The test that failed in run 1 passed in run 2, and vice versa. Roughly 1-2 out of 52 unit tests fail per run. Nothing about the tests changed between the runs, and the fixtures are correct.

The same commit passes consistently against a single-replica dev service, and it passed on the 2-replica service several times before it started failing.

Because dbt skips everything downstream of a failing test, this also stops production models from being rebuilt — 3 nodes were skipped in run 1 (1 model and its tests) and 18 in run 2 (3 models and theirs), a different set each time. So on a multi-replica service the practical effect is that a random subset of models silently stops being updated.

Steps to reproduce

1. Point a dbt project with several unit tests at a ClickHouse Cloud service with 2 or more replicas.
2. Run dbt build (or dbt test --select test_type:unit) a few times.
3. Some runs fail with Accepted columns for expected output are: [], and the failing tests differ between runs.

Expected behaviour

Unit tests are fixture-based and do not read production data, so they should be deterministic regardless of how many replicas the service has.

What I found

Accepted columns ... are: [] does not mean the fixture is wrong. dbt-core raises it from format_row when the column list collected for the model is empty:

{%- if column_name not in column_name_to_data_types %}
    {{ exceptions.raise_compiler_error(
        "Invalid column name: '" ~ column_name ~ "' in unit test fixture for " ~ fixture_name ~ "."
        "\nAccepted columns for " ~ fixture_name ~ " are: " ~ (column_name_to_data_types.keys()|list)
    ) }}

(The message is easy to misread — dbt-labs/dbt#10014 asks for it to say why the columns could not be read.)

That list comes from a temporary table this adapter creates and reads back in unit.sql (1.10.0; the run_query wrapper differs on main, but the temp-table-then-inspect approach is the same):

{% do run_query(get_create_table_as_sql(True, temp_relation, get_empty_subquery_sql(sql))) %}
{%- set columns_in_relation = adapter.get_columns_in_relation(temp_relation) -%}

create_table_or_empty builds it session-scoped and node-local (unchanged on main):

{% if temporary -%}
    create temporary table {{ relation.identifier }}
    engine Memory

Memory tables are not replicated across nodes on ClickHouse Cloud by design, and that page recommends running all operations in a single session or using a client with sticky connections. The adapter does not send X-ClickHouse-Replica-Tag, so replica-aware routing does not apply — "Requests without the header keep normal load balancing".

Requests to this service really are spread across nodes. Five consecutive SELECT hostName() calls (new connection each time) returned two distinct node names, 3/2:

c-xxxxx-server-aaaaaaa-0
c-xxxxx-server-aaaaaaa-0
c-xxxxx-server-bbbbbbb-0
c-xxxxx-server-aaaaaaa-0
c-xxxxx-server-bbbbbbb-0

So when the CREATE TEMPORARY TABLE and the follow-up system.columns query land on different nodes, the table is not there and the column list comes back empty.

What I have not captured is which node served each of those two statements in a failing run, so that last step is inference rather than direct observation. It is the explanation that fits everything else, and it accounts for both the single-replica service being unaffected and the failing test changing between runs.

Code examples, such as models or profile settings

profiles.yml (ClickHouse Cloud, 2 replicas):

prod:
  type: clickhouse
  schema: analytics
  host: <service>.clickhouse.cloud
  port: 8443
  user: default
  password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
  secure: True
  threads: 4

The three models hit so far are all views with several joins, but nothing else links them, and each of their unit tests passes on other runs.

dbt and/or ClickHouse server logs

Model and test names are redacted; they are unrelated to the failure and differ between runs.

Run 1:

180 of 236 ERROR model_a::test_a  [ERROR in 1.02s]
Completed with 1 error, 0 partial successes, and 0 warnings:
  Runtime Error in unit_test test_a
  Compilation Error in unit_test test_a
    Invalid column name: 'org_id' in unit test fixture for expected output.
    Accepted columns for expected output are: []
    > in macro format_row (macros/unit_test_sql/get_fixture_sql.sql)
    > called by macro get_expected_sql (macros/unit_test_sql/get_fixture_sql.sql)
    > called by macro materialization_unit_clickhouse (macros/materializations/unit.sql)
Done. PASS=232 WARN=0 ERROR=1 SKIP=3 NO-OP=0 TOTAL=236

Run 2, same commit, 23 minutes later:

119 of 236 ERROR model_b::test_b  [ERROR in 0.40s]
193 of 236 ERROR model_c::test_c  [ERROR in 0.80s]
Completed with 2 errors, 0 partial successes, and 0 warnings:
    Invalid column name: 'org_id' in unit test fixture for expected output.
    Accepted columns for expected output are: []
Done. PASS=216 WARN=0 ERROR=2 SKIP=18 NO-OP=0 TOTAL=236

The single-replica dev service, same commit, same time: Done. PASS=236 WARN=0 ERROR=0 SKIP=0.

Configuration

Environment

• dbt version: 1.11.7
• dbt-clickhouse version: 1.10.0
• clickhouse-connect version (if using http): 0.14.1
• Python version: 3.12.13
• Operating system: ubuntu-24.04 (GitHub Actions)

ClickHouse server

• ClickHouse Server version: 26.4.1.2212 (ClickHouse Cloud, 2 replicas)
• ClickHouse Server non-default settings, if any: defaults; the adapter sets mutations_sync=3, alter_sync=3, insert_distributed_sync=1 and a per-connection session_id

Possible fix

ChHttpClient.columns_in_query already gets names and types without materialising anything:

def columns_in_query(self, sql: str, **kwargs) -> List[ClickHouseColumn]:
    query_result = self._client.query(f"SELECT * FROM ( \n{sql} \n) LIMIT 0", **kwargs)

unit.sql already uses get_columns_in_query(sql) for tested_expected_column_names when expected_rows is empty. Building column_name_to_data_types and column_name_to_quoted the same way would take the temporary table out of this path entirely, along with the dependency on both statements reaching the same node. The ClickHouseColumn objects it returns expose data_type and quoted, which is what the materialization needs.

Related: #524 discusses make_temp_relation vs make_intermediate_relation usage and notes that unit.sql was copied into this adapter specifically to mark the relation as temporary. #685 covers connection pooling and replica distribution from the opposite direction.

Workaround

Excluding unit tests from the production run (dbt build --exclude test_type:unit) and keeping them in CI and dev, which are single-replica. Unit tests do not depend on production data, so coverage is not lost, but the production build no longer runs them.