[Feature] Support for Custom Pool Selection in dbt-Cosmos for Airflow

[wornjs]

Description

Title: Support for Custom Pool Selection in dbt-Cosmos for Airflow

Description:
Currently, when using dbt-Cosmos, we are limited to a specific pool in Airflow. However, we would like to schedule models based on their characteristics, as follows:

Pool Size 1: For heavy tasks to be processed one at a time, dedicating DW resources exclusively to a single task.
Pool Size 10: For lightweight tasks, allowing multiple queries to be executed concurrently.
If a model does not specify a pool, it should use the default pool. However, if a pool is defined in the model’s configuration, that pool should be applied to the task.

  - name: model_name
    description: description
    meta:
      owner: "owner"
      cosmos: 
         pool: custom_pool

This feature would allow for better resource management and parallelization based on the workload requirements of each model.

Additionally, it would be useful to allow more flexible configurations under meta.cosmos, such as specifying multiple values for dbt-Cosmos use. For example, setting a custom warehouse in Snowflake.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes, I am willing to submit a PR!