New Fusion connection management on Snowflake and BigQuery produces timeouts (threads config is ignored)

[schwericke]

Describe the bug
dbtf run --help shows --threads defaults to max_cpu, not the profile value:

--threads <THREADS>    The number of threads to use [Run with --threads 0 to use max_cpu [default: max_cpu]]

profiles.yml sets threads: 4, but dbtf run ignores that, causing severe warehouse contention. Core correctly reads the profile and uses 4 threads.

What version of dbt Fusion is this bug in? (find out by running dbt --version)
2.0.0-preview.120

Is this a discrepancy between the dbt Fusion Engine and dbt Core? Check one.

• YES
• NO

Expected behavior
Fusions should read threads from profiles.yml

Operating System and CPU Type:
Linux 6.6.87.2 (WSL2), x86_64, 22 CPUs

[dataders]

hey @schwericke what data platform are you on?

have you seen the recent Fusion-specific addition to the Using threads docs page?

we could respect user threads, but our current philosophy is that users shouldn't have to think about it and dbt should handle warehouse concurrency well.

Admittedly, we have improvements to handle it better. For example, if you're on Snowflake, we did ship a change in preview.120 that we know opens too many connections. I'd be curious if you were to update to a newer version, if you see any improvement.

[botana-te]

hi @dataders! I'm on preview.120. I can confirm dbtf makes at least 80 connections to Redshift on parallel although my profiles.yml sets it for 16 threads. It would be great if it used the threads the user set.

[dataders]

@botana-te thanks for flagging! we're looking into this specific Redshift issue as we speak. stay tuned.

[schwericke]

@dataders Thank you for your answer. Good to know that you are aware of the congestions issues with Snowflake. I share your vision that it would be nice if the user does not need to mind the thread settings anymore.

In my current situation working on Snowflake with an extra small warehouse, my 17 base models run into a severe traffic jam.
My build with Fusion needs 120s versus 20s with Core. If this is a feature it requires some refinement for small warehouses.

With the newest preview.126 I do sadly not experience any improvements.

[radian21]

Reporting in that on dbt-fusion 2.0.0-preview.149 in Snowflake, I'm getting numerous dbt1308 timeouts. My project is large-ish at 1,400+ models. Lots of queuing and timeouts due to the warehouse not being able to process the queue fast enough to keep up with Fusion pushing onto the queue - at least that's my theory.

[eliasdefaria]

@radian21 Thanks for flagging! Wanted to make sure @felipecrv saw this as he the engineer on our end working on this issue. We'll keep you updated once that's resolved. I'm going to update the title of this issue to reflect the timeout scenario

[sitegroove]

Having same issue on Bigquery. Opens to many connections and getting timeout errors, so basically for any production runs it completely useless on large number of models with data tests. For some reason setting --threads 1 works, but if i set --threads 2 then it ignores it and opens to many connections causing timeouts. This looks like a critical issue with fusion. cc @eliasdefaria