Key pair authentication for long running dbt builds eventually is no longer able to authenticate

[marcusintrohive]

Hey! As our dbt builds have grown over time we are noticing occasionally they are failing to connect using key pair authentication.

The error we are seeing from DBT is. Something to note is that we are successfully able to run the bulk of our dbt build before this happens so the key pair file does exist on the airflow worker and we are successfully making the connection.

[2023-03-16 18:16:15,149] {{functions.py:236}} ERROR - [0m18:16:15  [33m('Could not deserialize key data. The data may be in an incorrect format, it may be encrypted with an unsupported algorithm, or it may be an unsupported key type (e.g. EC curves with explicit parameters).', [_OpenSSLErrorWithText(code=151584876, lib=9, reason=108, reason_text=b'error:0909006C:PEM routines:get_name:no start line')])[0m

Recently we added connect_retries: 3 to our dbt_profile hoping that would resolve the issue but unfortunately did not.

We are using an older version of airflow-dbt-python and airflow which we plan to upgrade over the next couple weeks.

• airflow-dbt-python[snowflake]==0.14.5
• airflow 2.0.2

I see in https://github.com/tomasfarias/airflow-dbt-python/issues/79 the support to use airflow connections was added. Wondering if you have any insight of how the connection would be created using a keypair authentication.

Currently we are setting up our private key using an airflow plugin.

We are running Airflow using MWAA.

[tomasfarias]

Thanks for opening an issue @marcusintrohive.

Wondering if you have any insight of how the connection would be created using a keypair authentication.

The feature was built so that any profile parameters used by the dbt adapter (dbt-snowflake in this case) can be passed as Airflow connection extras. This means you can add the private_key_path and private_key_passphrase parameters when creating a connection, for example:

airflow connections add 'my_snowflake' \
    --conn-json '{
        "conn_type": "snowflake",
        "extra": {
            "account": "my-account-id",
            "database": "my-database",
            "warehouse": "my-warehouse",
            "private_key_path": "/path/to/my/key",
            "private_key_passphrase": "my-passphrase"
        }
    }'

I don't know what plugin you are using but if you are able to load your private key file in the worker, this should work like with any other Airflow connection as dbt target (see an example DAG of this).

However, I can imagine the user experience is not the best: it still requires your key to be available in a path that your worker can access to, and you probably want the private_key_passphrase to be in the password field instead.

In contrast, the Snowflake Airflow provider package connection has a private_key_content extra field so that you can pass the contents of the key instead (no file required). And it uses password as the private key passphrase.

We could add some similar support in airflow-dbt-python if you think it could be valuable. Unfortunately, I don't have access to a Snowflake instance so testing and judging the value of such a feature is hard from my point of view, so I'm open to your opinion on the topic.

[marcusintrohive]

Thanks for the insight on this @tomasfarias.

Ending up being an issue with the airflow plugin we were using to create the key pair file from the contents of a value in AWS Secrets Manager. Will be looking at improving this using the airflow connection.