[Feature] Add Support for the token_file_path parameter used for OAuth in Snowpark Container Services

[sfc-gh-dflippo]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I will be submitting a PR for dbt-snowflake to support the token_file_path parameter in a profile.

Background

Snowflake Container Services (SPCS) provides an OAuth token file, /snowflake/session/token, to containers to allow them to log into Snowflake without needing a service account. The token in this file expires but is refreshed automatically by the SPCS service. The Snowflake Python Connector added a token_file_path parameter in version 3.11.0 (June 2024) to make it easier for clients to connect without having to read the OAuth token from the file every time a new connection is needed. Instead, by passing the token_file_path parameter set to /snowflake/session/token the driver will automatically handle retrieving new OAuth tokens whenever a new connection is created.

My changes and testing

I have made the necessary changes to the connection.py file and will be submitting a pull request to go with this issue. I have successfully tested these changes in a Snowpark container. I made my changes branched from the v1.9.0 tag because I was unable to get hatch in the main branch to build an adapter that dbt would pick up. I would deploy it but dbt --version would not show the snowflake adapter and projects would fail saying that it could not find the snowflake adapter. Switching back to the v1.9.0 branch I was able to use make dev, build my wheel for SPCS using bash ./scripts/build-dist.sh and install locally using pip install .. After local regression testing, I deployed the wheel to SPCS and verified it could connect using the token file. I'm guessing that I'm not the only person unable to build from main right now.

Ancillary changes

As you will see in my PR, I also updated the error checking for this and related parameters. When the parameter is added, my code validates that the token file exists and has a useful error message if it does not. I also removed the error for a missing user parameter when token_file_path is used because SPCS OAuth does not need a user specified. Finally, I also added a condition to skip the token parameter logic when token_file_path is being used.

Describe alternatives you've considered

I originally tried using an entrypoint script to lookup the token and set an environment variable when the container starts. That works initially but if dbt opens a new connection later, the OAuth token may have expired, producing an error.

Who will this benefit?

These changes benefit Snowflake customers deploying dbt in Snowpark Container Services. Even the dbt native app from dbt Labs could utilize this for authentication, eliminating the need for a different security integration.

Are you interested in contributing this feature?

Yes, I will be submitting a PR to go with the issue

Anything else?

No response