Unclosed connections prevent dbt from exiting on Snowflake with keepalives enabled

[drewbanin]

Issue

Issue description

The client_session_keep_alive config for snowflake-connector-python is implemented using a threaded heartbeat. During the dbt docs generate command (and possibly others), dbt doesn't close 100% of the connections it opens. As a result, the snowflake-connector-python threads continue to heartbeat in perpetuity, preventing dbt from exiting. See the comment here while slowly and deliberately raising the palm of your hand into the vicinity of your face.

We should, in general, ensure that dbt always closes any connections that it opens.

Results

dbt finishes executing, but control is not returned to the terminal. Instead, python hangs on a lock held by snowflake-connector-python, and the user needs to ctrl-c to exit.

System information

The output of dbt --version:

0.12.2

The operating system you're running on: Any

Steps to reproduce

1. Set up a Snowflake profile, configuring client_session_keep_alive to be True
2. Run dbt docs generate
3. wait forever

[briamkin]

Step 3 is my favorite

[beckjake]

I don't want to take credit for this since I can't tell you how it happened, but I am ~90% sure this is fixed in 0.13.0, possibly as part of the connection management work I did. I sure can't reproduce it, and I can easily reproduce it on 0.12.2.

[tayloramurphy]

@beckjake So we're seeing this now. We're running in GitLab CI and it hangs on "flush usage events". Setting to False fixes it. Here's some info about the runners if that's useful. https://docs.gitlab.com/ee/user/gitlab_com/#shared-runners

The command we're using is dbt docs generate --profiles-dir profile --target prod

Edit:
We're on 0.14.0 and we see this after upgrading to 0.14.1.

[drewbanin]

Thanks @tayloramurphy - I just moved this card into the LMA milestone + reopened the issue. We'll check it out!

[gkushida]

We're also seeing this with run-operation on 0.14.0. First observed in Kubernetes but seems like it can be reproduced from other envs as well.

[krishbox]

We're also seeing this when a dbt run fails or when a dbt run has "Nothing to do" on 0.14.4. We can reproduce this at will.

[drewbanin]

@krishbox are you still seeing this on 0.15.0?

[davehowell]

I'm on 0.16.1, seeing some dbt cloud jobs end with an error that seems related to this setting - these are jobs that are running for ages, 4+ hours, because there's a bit of a backlog of processing but that's another story.

The error is

Database error while running on-run-end
Database Error
  390114 (08001): Authentication token has expired.  The user must authenticate again.

I checked the parameter setting on Snowflake by running show parameters in account and it is set to false. So in theory there should be no token timeout and this error should never happen.

In my dbt_project.yml I have no on run end section, so I am not sure what the failure is there.

As a side note, how do profiles relate to dbt Cloud, are they just the environment? I thought perhaps I could try setting this parameter to true to test what happens but can't see where that would be possible in dbt Cloud.

[drewbanin]

hey @davehowell - dbt Cloud does not currently support the client_session_keep_alive configuration, so I think the problem you're describing might be different from the bug that this issue was tracking.

This issue tracked a bug where, when the client_session_keep_alive flag was enabled, dbt would hang and become unresponsive at the end of a dbt run. It sounds like the issue you're having is just that the config is not enabled, right?

If that's the case, then I have some good news for you -- we plan on adding this config to dbt Cloud in the near future :). For any questions about dbt Cloud, please do feel free to write into [email protected], or get in touch with us in the application by clicking the 💬 in the top right corner of the page!

Just to add some additional details:

• It's confusing to me that you're seeing this error if you do not have any on-run-end hooks configured. This might be related to closing open connections, or something like that?
• Snowflake imposes a 4 hour lifetime for connections. The client_session_keep_alive config, when enabled, periodically refreshes the connection to keep it alive for more than 4 hours.
• Profiles in dbt Cloud are indeed partially managed through Environments for deployments (eg. scheduled runs), but we'd probably add this as a config to the base Connection object in Cloud instead

[davehowell]

@drewbanin thanks for the clarification. It's confusing for me too. Ideally I would never need the 4+ hours keep alive; I know the core issue is that these jobs should be optimized so they aren't running for so long, I can work around my current processing backlog by running some of the models individually.