[CT-1724] [Bug] Snowflake Account/Password Auth Failures Should Not Trigger A Retry

[beauxparker]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Currently on job runs if the environment's deployment credentials are incorrect, dbt will attempt to open a Snowflake connection 3 times, stating "Snowflake adapter: Got a retryable error when attempting to open a snowflake connection." This can lead to the user being temporarily locked out in Snowflake for the cooldown period, unless their system administrator takes action.

Expected Behavior

I think it would be ideal if the connection was not attempted more than once if the user's credentials are incorrect as to not lock the user out/

Steps To Reproduce

1. In a deployment environment, update the deployment credentials to either have an incorrect Snowflake username or password.
2. Schedule or kick off a run with bad credentials.
3. Once the run fails you can see in the debug logs that dbt attempts to open the connection 3 times before the user is locked out.

Relevant log output

Database Error2022-12-23 17:34:09.316913 (ThreadPoolExecutor-1_0): 17:34:09  Opening a new connection, currently in state init
2022-12-23 17:34:10.111064 (ThreadPoolExecutor-1_0): 17:34:10  Snowflake adapter: Got a retryable error when attempting to open a snowflake connection.
3 attempts remaining. Retrying in 30 seconds.
Error:
250001 (08001): Failed to connect to DB: {account}.snowflakecomputing.com: Incorrect username or password was specified.
2022-12-23 17:34:40.778304 (ThreadPoolExecutor-1_0): 17:34:40  Snowflake adapter: Got a retryable error when attempting to open a snowflake connection.
2 attempts remaining. Retrying in 30 seconds.
Error:
250001 (08001): Failed to connect to DB: {account}.snowflakecomputing.com: Incorrect username or password was specified.
2022-12-23 17:35:11.524513 (ThreadPoolExecutor-1_0): 17:35:11  Snowflake adapter: Got a retryable error when attempting to open a snowflake connection.
1 attempts remaining. Retrying in 30 seconds.
Error:
250001 (08001): Failed to connect to DB: {account}.snowflakecomputing.com:. Incorrect username or password was specified.
  250001 (08001): Failed to connect to DB: {account}.snowflakecomputing.com: User temporarily locked. Contact your local system administrator or please create a case with Snowflake Support or reach us on our support line:

Environment

- OS:
- Python:
- dbt-core:
- dbt-snowflake:

Additional Context

No response

[dbeatty10]

Thanks for opening @beauxparker !

It feels reasonable to expect to not retry in the case of an "Incorrect username or password was specified" error. In fact, you make a compelling case why it is desireable to not retry.

Selfishly, I decided not to reproduce this -- I'm going to trust your log output on this one 😎

What I think is going on

Here is where I think the retry is happening in dbt-snowflake.

So it'd be a matter of determining which type of Snowflake error is being raised and then making sure it isn't in the retryable_exceptions list.

But I have a few unconfirmed suspicions:

• That "Incorrect username or password was specified" will correspond to HTTP response code 403.
• HTTP response code 403 corresponds to FORBIDDEN in snowflake-connector-python
• ❗FORBIDDEN corresponds to OtherHTTPRetryableError in snowflake-connector-python [1]

If that's all true, then that means snowflake-connector-python is bundling all these different errors into OtherHTTPRetryableError:

        BAD_REQUEST,  # 400
        FORBIDDEN,  # 403
        METHOD_NOT_ALLOWED,  # 405
        REQUEST_TIMEOUT,  # 408

With my naive eyes, I'd expect only 408's to be retryable, but not 400's, 403's, or 405's. 🤷

Next steps

As a result of the above, I'm not sure what the the consequences would be of changing the behavior of dbt to not retry OtherHTTPRetryableErrors. I will defer to one of our adapter engineers to make a call one way or the other how we should handle this going forward.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[joellabes]

Reopening this because it has bitten me a few times this month getting ready for the hackathon.

With my naive eyes, I'd expect only 408's to be retryable, but not 400's, 403's, or 405's. 🤷

I think this is right (backed up by this SO post).

Did we get a ruling from anyone on the adapters team?

Would this be better as an issue on the snowflake-connector-python repo, suggesting that 408s should be the only 4xx error that are retryable?

[dbeatty10]

Thanks for re-opening @joellabes!

At the most detailed level, this seems like an issue within snowflake-connector-python itself. i.e. it is categorizing 403's, etc as being "retryable" here as introduced in this commit.

It would be solved "automatically" if snowflake-connector-python reduced the scope of HTTP codes included in OtherHTTPRetryableError.

Assuming they can't or won't, I'm going to re-label this as "help wanted" for a motivated community member to take on.

Acceptance criteria

• 408 (request timeout) is retryable within dbt-snowflake
• No other 4xx codes are retryable within dbt-snowflake

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.