Snowflake connection timeout

[cmcarthur]

In a 6+ hour run against a Snowflake cluster, the run ends with:

252007 (08001): Authentication token has expired. The user must authenticate again.

I suspect that the master connection is timing out during the 6 hour run, and we aren't refreshing it like we do with model connections.

[cmcarthur]

According to this support post, an idle auth token is revoked after 4 hours. So any dbt run that takes more than 4 hours will fail post run hooks.

https://support.snowflake.net/s/question/0D50Z00006uSiDlSAK/authentication-token-expired-issue

[drewbanin]

4 hour models are an anti-pattern. closing

[nsnyder3]

@drewbanin Could you say more about what you see an an anti-pattern? An entire dbt run that takes longer than 4 hours is an anti-pattern? We don't have any single models that take longer than 4 hours, but our entire run together can take that long, especially when we are doing a full refresh from a source table with ~70 billion records.

I'd love to understand:

1. when you expect the session to time out
2. how we can design our models to run in less than 4 hours when we are processing ~70 billion records.

[drewbanin]

hey @nsnyder3 - great questions. We resolved a couple of different issues in dbt that could contribute to this timeout error around the time we closed this issue. You can check those other discussions here: #657, #693, #764

I think that the issues linked above should ameliorate many of these Snowflake timeout problems in the wild, but if you're still having issues, I'd be really grateful if you could create an issue with some more information about your particular scenario.

I'll also note we've recently created a new issue to optionally disable timeouts altogether on Snowflake. You can check that out here #827. I'd love to know if you think this will be a suitable fix for your use case.