[Feature] Consolidate Database Connection Classes

[astro-anand]

Description

Currently, cosmos profile mappings are not user friendly. For example, Snowflake has 4 different classes for different auth methods. If a user changes their conn to use a different auth method, DAG code needs to be updated

Use case/motivation

To align with Airflow's connection paradigm, I propose consolidating to a single ProfileMapping class per database. The implementation could attempt to import the relevant hook from the provider mocked example below

try:
    from airflow.providers.snowflake.hooks.snowflake import SnowflakeHook
    return SnowflakeHook(snowflake_conn_id=conn_id).get_connection()
except ImportNotFound:
    # factory that returns our custom classes based on the conn details

We could leave the existing classes unchanged, but this allows us to offload auth to the provider packages and play less catchup

Related issues

Example of cosmos team needing to maintain parity with snowflake provider: #1798

Are you willing to submit a PR?

• Yes, I am willing to submit a PR!

[tatiana]

Hi @astro-anand! Thanks for proposing this. I would love to see Cosmos configurations simplified.

To give some historical context: Cosmos has a function that attempts to identify automatically which profile to use based on the Airlfow connection:
https://github.com/astronomer/astronomer-cosmos/blob/24fd845e19548983113a549225e688c8f684a5b1/cosmos/profiles/__init__.py#L61C5-L76

However, by the time it was more widely used, it caused a few issues for end users. I don't remember which ones were, and it would be a good idea to revisit the implementation and advance with it.

Your suggestion to have a single profile mapping per database may be the solution.

One challenge with using the hook is that users must install the Airflow provider, which can lead to conflicts with dbt if installed in the same Python virtualenv.

What if you tried to unify many Snowflake profile mappings into one, without necessarily using a hook, so we could see how it looks like?