[Bug] Large Time Gaps Between dbt Statements (Suspected Python GIL Issue)

[misteliy]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Description:

We're observing significant time gaps (up to 2 minutes) between dbt statements (COMMIT and BEGIN/ROLLBACK) in our Redshift data pipeline, leading to increased model processing times. This issue affects multiple models and occurs in various stages of the dbt run. Initial investigation points to a potential bottleneck related to the Python Global Interpreter Lock (GIL).

Steps to Reproduce:

1. Run a dbt project with multiple models (e.g., 2000+ models).
2. Observe the dbt logs and Redshift query history.
3. Notice large time gaps (minutes) between COMMIT and subsequent BEGIN/ROLLBACK statements for individual models.

Example:

https://getdbt.slack.com/archives/CJARVS0RY/p1738171778055219

Observed Behavior:

• With multiple threads (e.g., 5), we see significant time gaps between dbt statements, resulting in longer overall processing times (e.g., 3.5 hours for ~2k models).
• With a single thread, the time gaps disappear, and the processing time increases (e.g., 4.5 hours for ~2k models), but the individual model execution is more consistent. (See attached screenshot of single-threaded execution without time gaps).
• Processing with 5 threads is not even 2x faster, let alone 5x faster, than single-threaded execution.

Suspected Root Cause:

The observed behavior strongly suggests a bottleneck related to the Python GIL. When multiple threads are used, the GIL likely prevents true parallel execution of dbt operations, leading to queuing and delays between statements.

Workaround:

Running dbt with a single thread eliminates the time gaps, but significantly increases the overall processing time.

Impact:

Increased dbt run times, impacting data freshness and downstream processes.

Attachments:

• Screenshots from Slack thread showing dbt logs and Redshift query history with time gaps.
• Screenshot of single-threaded execution without time gaps.

Questions:

• Can the dbt-redshift adapter be optimized to minimize GIL contention?
• Are there any recommended configurations or best practices for running dbt with multiple threads on Redshift to avoid this issue?
• if we have autocommit in dbt profile it should not submit any transactional statements at all
• if there are no hooks it should not submit 2 fake commands BEGIN/ROLLBACK
• Can dbt take benefit from the new option(GIL becomes optional) introduced in python 3.13? (edited)
  (https://gtm-roche.slack.com/archives/D03G68BDEDS/p1738743200437589)
  https://medium.com/@mitesh.singh.jat/gil-becomes-optional-in-python-3-13-a-game-changer-for-multithreading-4c5d28856803

Expected Behavior

dbt statements within a single model should execute consecutively with minimal delay.

Steps To Reproduce

• dbt-redshift adapter version: 1.8.1
• dbt version: 1.8.9
• Redshift cluster: (Please specify cluster type and size if possible)
• MWAA (Managed Workflows for Apache Airflow) - dbt runs are orchestrated through MWAA.
• autocommit: enabled

Relevant log output

Environment

- OS:
- Python:
- dbt-core:
- dbt-redshift:
- node type: ra3.16xlarge
- autoWLM: No, reverted to the manual tuned WLM

Additional Context

No response

[ernestoongaro]

@misteliy can you specify the flavour of Redshift you're running (Serverless or RA3)

[VersusFacit]

Amy has notified us that this is has been identified as a Redshift issue. They are drilling down for now.

Seems like this issue has already been triaged by Redshift service team and it is related to a recent change in P185. We have disabled the new optimization through a configuration change and you will need to reboot your cluster for it to take effect. Please reboot your cluster as per your convenience and let us know if you still see the issue

We'll close out this ticket here for now but please reopen in the future should there be additional work to be done.

[misteliy]

Please reopen the issue. It remains unresolved, and the root cause of the observed behavior is still unclear.