[Feature] allow to pass a profiles_yml_filepath from a cloud provider

[corsettigyg]

Description

now that cosmos is moving in a direction of improving its integration with cloud providers, I believe it would be very beneficial to allow to also fetch the profiles from a cloud path. this would open many possibilities for users, such as dynamically creating and push a profiles to, lets say, s3 and using it in the same DAG.

Use case/motivation

in my company we use databricks, which means that we manually decide how to allocate the resources for our data warehouse. we want to use cosmos to render our dbt dags by domain, but each dag essentially has a a different need of resources, and it would be very good if we could dynamically create and use profiles for different warehouses in a very straightforward way. Using a cloud provider's storage is the easiest way to persist and communicate the generated profiles.yml

Related issues

No response

Are you willing to submit a PR?

• [ ] Yes, I am willing to submit a PR!