Reuse connections accross entire Dag

[DanMawdsleyBA]

I have run into issues when running an airflow dag with multiple tasks running in parallel which has caused tasks to hang waiting on a connection. As each tasks opens up 3 connections each it might be worth seeing if those connections can be reused across multiple tasks perhaps some sort of setup and teardown task (Airflow 2.7). Could also be extended to deal with initial profile mapping setup also

I have raised a separate issue on the dbt snowflake repo to see if there is anything possible within dbt itself to help manage these connections better.
dbt-labs/dbt-snowflake#824

[tatiana]

@DanMawdsleyBA I've been thinking about this issue. Is it only happening when running models?

We could consider using an Airflow Snowflake (potentially async) operator to run the compiled SQL file using Cosmos. We could experiment with this using:
https://astronomer.github.io/astronomer-cosmos/configuration/render-config.html#customizing-how-nodes-are-rendered-experimental

We may need to add the compiled SQL to the DbtNode definition.

If we see this approach is successful, we could consider having a Cosmos.AIRFLOW execution mode, where we'd attempt to use native Airflow operators to communicate with the database.

[tatiana]

This is likely associated to the original request: #925

[tatiana]

We'll be doing the following ticket, and hopefully, the original issue will be solved:

1. Change how the profiles.yml is created when using mocked profiles  #924
2. Cache profiles.yml file when using Cosmos ProfileMapping #925