[Feature] Multi-profile support for running dbt on multiple warehouses

[wornjs]

Description

Hello Cosmos team,

I’ve been using Astronomer Cosmos extensively and really enjoying the experience so far.
Thank you for building such a great integration between Airflow and dbt.

Recently, we migrated our data lake file format to Iceberg, and as a result, we are now operating a multi-warehouse DW environment using both StarRocks and Snowflake.

In this setup, we would like to:

Run the same dbt project / models

Within a single DAG

Against multiple targets / profiles (e.g. StarRocks and Snowflake)

Ideally, this would allow us to execute the same logic in parallel by switching only the dbt profile or connection, without duplicating DAGs or dbt projects.

From my understanding, Cosmos currently assumes a single dbt profile per DAG execution.
I wanted to ask whether there are any plans or discussions around supporting:

Multiple dbt profiles within a single DAG

Running the same dbt project against different adapters (e.g. StarRocks & Snowflake)

Selecting the target/profile at the DAG or task level

If this is already on the roadmap or if there is a recommended pattern (such as DAG duplication or parameterized execution), I’d really appreciate any guidance.

Thanks again for the great work, and I’m looking forward to your thoughts.

Best regards,

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes, I am willing to submit a PR!

[corsettigyg]

@wornjs cant you just use task groups for that? for each task group you specify a different profile but they all live in the same dag

[wornjs]

Hi, thanks for the response.

To provide more context on my requirement: I am managing over 100 models, and they need to be executed on different query engines depending on the data source and logic, even within the same project.

For example:
Model A, B (Log data): Must run on Snowflake.
Model C (DB data): Must run on StarRocks.
Model D (Join A & C): Joins Iceberg tables from both engines, running on a specific engine.

The current workaround of splitting them into multiple DbtTaskGroups based on tags is not ideal because:
Lineage Fragmentation: It breaks the visual and operational dependency graph (Upstream/Downstream) between models in the Airflow UI.
Scalability: Manually defining and connecting dozens of task groups for 100+ models is highly inefficient.

What I am proposing is a way to define the query engine (or profile) directly in the model's config, like this:

# dbt_project.yml or model.sql
models:
  - name: log_transformation
    config:
      query_engine: snowflake  # Should map to a Snowflake ProfileConfig
  - name: db_transformation
    config:
      query_engine: starrocks  # Should map to a StarRocks ProfileConfig

It would be great if Cosmos could:

• Parse these custom configs from the manifest.json.
• Dynamically assign the corresponding ProfileConfig (or Connection ID) to the generated Airflow Task at the individual model level.

This feature would allow us to maintain a single, unified DAG while leveraging the strengths of different compute engines. Is there any plan to support this kind of dynamic profile routing within a single Task Group?

[pankajastro]

Cosmos supports overriding the dbt profile config for individual nodes. The docs explaining this are here: https://astronomer.github.io/astronomer-cosmos/profiles/index.html#customising-the-profile-config-per-dbt-node
— could you take a look and see if this fixes your issue?

[wornjs]

I’ve read the documentation about customising the profile, but that configuration only allows overriding a subset of settings within a single adapter.

What I’m trying to achieve is different:
I want to use multiple adapters in an Iceberg environment and be able to switch or configure them independently, rather than just partially overriding settings for one adapter.