Investigate slow execution time of dbt tests on ClickHouse Cloud

[BentsiLeviav]

Description

When running the adapter test suite against a ClickHouse Cloud instance, the runtime is significantly longer compared to running locally with Docker.

• Local (Docker): a few minutes

• Cloud: 30–40 minutes

This large discrepancy makes development and CI workflows inefficient, slowing down test runs and feedback loops when validating against ClickHouse Cloud.

[koletzilla]

Yesterday I took a look at this issue. I executed these tests locally to benchmark different CH configurations:

pytest tests/integration/adapter/query_settings/test_query_settings.py --durations=0

This takes different amounts of time depending on the region where the CH cluster is located. I executed them from Amsterdam and:

• With the ClickHouse cluster located in Ireland, it took consistently 5 seconds.
• With the ClickHouse cluster located in the US, it took ~15 seconds.

The load on the ClickHouse instances was similar:
~96 requests sent to CH. ~54 of them came from the setup of the CH connection. 12 of them were 'SET allow_nondeterministic_mutations = 1'.

• About 2s of total query_duration_ms
• No CPU/Memory impact.

~96 queries, at ~100ms of difference between accessing EU and US clusters from my laptop, mostly accounts for the ~10s of difference I'm seeing.

Just for reference, each time we create a new CH connection, these queries are sent. All of them are useful, but we need to think about all of them being executed several times in each test execution.

SET allow_nondeterministic_mutations = 1 --sent by dbt-clickhouse's dbclient.py
EXISTS DATABASE `dbt_clickhouse_5861_test_query_settings_1756744589372`
SELECT 1 AS check FORMAT Native
SELECT name, value, readonly as readonly FROM system.settings LIMIT 10000 FORMAT Native
SELECT version(), timezone()

Also, my laptop didn't have any impact on CPU/Memory during the execution of these tests.

Conclusions

• Resource usage is not a problem on either the runner side or the CH cluster side.
• Latency to the CH cluster is a hugely relevant factor when thinking about the performance of these tests.
• It's relevant the queries needed by each new connection we create.

Next steps

• Already mentioned by @BentsiLeviav and now I believe it's more important than ever: we are not parallelizing the execution of the tests. Taking into account the low resource utilization, adding parallelism would reduce the latency impact on test performance. Just by doing this I reduced the times by half in my limited test setup (and I expect more improvements as part of the time is probably related to the whole setup/teardown process):

pip install pytest-xdist
# Notice the `-n 4` at the end to execute the test in 4 workers.
pytest tests/integration/adapter/query_settings/test_query_settings.py --durations=0 -n 4
# We need to apply a small fix to the user creation/deletion at startup/teardown, but after that it works without problems.

• Optimize the number of requests needed by the CH connector: all the queries it's doing are useful and have their value. For our particular context, where all the connections are created one after the other with the same settings, only the first set of queries are really useful. It would be nice if we could find a way to:
  • A) Reuse the CH connections instead of recreating them or
  • B) Reduce the number of internal queries needed, maybe by providing default values that are already configured.
• Test with Github Runner/CH clusters closer to each other. To be honest this is the one that is least likely to have an impact right now as both should be running in the same region. There isn't a lot of information about which regions Github actions run in, but they're supposed to be running in the US at one Azure datacenter. The cluster where the tests are running is in AWS us-east-2. Maybe by tuning the location of both of them a bit more we can improve the results, but we need to test it.

[BentsiLeviav]

we are not parallelizing the execution of the tests. Taking into account the low resource utilization, high parallelism would improve the latency impact on test performance.

We test for different Python versions in the cloud workflow. IIRC, when we had all versions running concurrently, we noticed a collision between runs when working on the same test (it was because some internal dbt macro loaded the tables with the table name only, and without the schema. Can we verify it by reverting to the matrix structure?
@koletzilla, generally, did you notice any collisions?

[koletzilla]

We test for different Python versions in the cloud workflow. IIRC, when we had all versions running concurrently, we noticed a collision between runs when working on the same test (it was because some internal dbt macro loaded the tables with the table name only, and without the schema. Can we verify it by reverting to the matrix structure? @koletzilla, generally, did you notice any collisions?

Right now I have only found one collision related to the users created (and later removed) in the conftest.py's test_config function. I saw it's also impacting in the fix-cloud-tests branch.

https://github.com/ClickHouse/dbt-clickhouse/actions/runs/17213244541/job/48830222204

I'll check also for the others you mention.