[X] I have searched the existing issues, and I could not find an existing issue for this bug
Current Behavior
We made connection reuse the default in #1109 - this is breaking some users jobs when they depend on thread session isolation. We should make reuse_connections opt-in instead of opt-out.
Expected Behavior
Make this behave the way they were prior to making reuse_connections: True by default.
Steps To Reproduce
First, let's create a UDF to return the current session timezone - we will be using this in our model code later:
CREATE OR REPLACE FUNCTION development_jyeo.dbt_jyeo.get_current_timezone()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
const timezone = Intl.DateTimeFormat().resolvedOptions().timeZone;
return timezone;
$$
;
select development_jyeo.dbt_jyeo.get_current_timezone();
-- America/Los_Angeles
Make sure the account level timezone is America/Los_Angeles (Snowflakes default):
-- macros/check_tz.sql
{% macro check_tz() %}
{% set query %}
SHOW PARAMETERS LIKE 'TIMEZONE' IN SESSION;
{% endset %}
{% if execute %}
{% set results = run_query(query).columns[1].values()[0] %}
{% do print('=========================================') %}
{% do print('SESSION TIMEZONE IS: ' ~ results) %}
{% do print('=========================================') %}
{% endif %}
{% endmacro %}
-- models/a.sql
{{ config(sql_header="ALTER SESSION SET TIMEZONE = 'Pacific/Auckland';") }}
select current_timestamp()::text as c, development_jyeo.dbt_jyeo.get_current_timezone() as ctz
-- models/b.sql
select current_timestamp()::text as c, development_jyeo.dbt_jyeo.get_current_timezone() as ctz
The idea here is that only model a.sql should operate on timezone Pacific/Auckland - and model b.sql should follow the account timezone America/Los_Angeles.
First we build using 1.8.latest:
$ dbt build --threads 1
00:36:05 Running with dbt=1.8.5
00:36:05 Registered adapter: snowflake=1.8.3
00:36:06 Found 2 operations, 2 models, 2 data tests, 449 macros
00:36:06
00:36:11
00:36:11 Running 1 on-run-start hook
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
00:36:13 1 of 1 START hook: my_dbt_project.on-run-start.0 ............................... [RUN]
00:36:13 1 of 1 OK hook: my_dbt_project.on-run-start.0 .................................. [OK in 0.00s]
00:36:13
00:36:14 Concurrency: 1 threads (target='sf')
00:36:14
00:36:14 1 of 4 START sql table model dbt_jyeo.a ........................................ [RUN]
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
=========================================
SESSION TIMEZONE IS: Pacific/Auckland
=========================================
00:36:18 1 of 4 OK created sql table model dbt_jyeo.a ................................... [SUCCESS 1 in 4.24s]
00:36:18 2 of 4 START sql table model dbt_jyeo.b ........................................ [RUN]
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
00:36:22 2 of 4 OK created sql table model dbt_jyeo.b ................................... [SUCCESS 1 in 3.66s]
00:36:22 3 of 4 START test accepted_values_a_ctz__Pacific_Auckland ...................... [RUN]
00:36:24 3 of 4 PASS accepted_values_a_ctz__Pacific_Auckland ............................ [PASS in 2.61s]
00:36:24 4 of 4 START test accepted_values_b_ctz__America_Los_Angeles ................... [RUN]
00:36:27 4 of 4 PASS accepted_values_b_ctz__America_Los_Angeles ......................... [PASS in 2.48s]
00:36:27
00:36:27 Running 1 on-run-end hook
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
00:36:28 1 of 1 START hook: my_dbt_project.on-run-end.0 ................................. [RUN]
00:36:28 1 of 1 OK hook: my_dbt_project.on-run-end.0 .................................... [OK in 0.00s]
00:36:28
00:36:29
00:36:29 Finished running 2 table models, 2 data tests, 2 project hooks in 0 hours 0 minutes and 23.22 seconds (23.22s).
00:36:29
00:36:29 Completed successfully
00:36:29
00:36:29 Done. PASS=4 WARN=0 ERROR=0 SKIP=0 TOTAL=4
^ So far so good. The session that built model b was independent of the session that built model a - therefor changing the timezone on the session that built model a did not affect model b.
Now, we do a local install of dbt-snowflake from the main branch:
$ git rev-parse HEAD
3fbc0749491f40d34014336457753b140e1fb1ee
^ 3fbc0749491f40d34014336457753b140e1fb1ee is ahead of commit 6857e6b800329a6490c3339824249c18b1af5eda - which contains the commit that made reuse_connectionsTrue by default.
$ dbt build --threads 1
00:45:21 Running with dbt=1.9.0-a1
00:45:22 Registered adapter: snowflake=1.9.0-a1
00:45:22 Unable to do partial parsing because of a version mismatch
00:45:22 Found 2 models, 2 operations, 2 data tests, 452 macros
00:45:22
00:45:25
00:45:25 Running 1 on-run-start hook
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
00:45:27 1 of 1 START hook: my_dbt_project.on-run-start.0 ............................... [RUN]
00:45:27 1 of 1 OK hook: my_dbt_project.on-run-start.0 .................................. [OK in 0.00s]
00:45:27
00:45:27 Concurrency: 1 threads (target='sf')
00:45:27
00:45:27 1 of 4 START sql table model dbt_jyeo.a ........................................ [RUN]
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
=========================================
SESSION TIMEZONE IS: Pacific/Auckland
=========================================
00:45:30 1 of 4 OK created sql table model dbt_jyeo.a ................................... [SUCCESS 1 in 2.20s]
00:45:30 2 of 4 START sql table model dbt_jyeo.b ........................................ [RUN]
=========================================
SESSION TIMEZONE IS: Pacific/Auckland
=========================================
=========================================
SESSION TIMEZONE IS: Pacific/Auckland
=========================================
00:45:31 2 of 4 OK created sql table model dbt_jyeo.b ................................... [SUCCESS 1 in 1.74s]
00:45:31 3 of 4 START test accepted_values_a_ctz__Pacific_Auckland ...................... [RUN]
00:45:32 3 of 4 PASS accepted_values_a_ctz__Pacific_Auckland ............................ [PASS in 0.56s]
00:45:32 4 of 4 START test accepted_values_b_ctz__America_Los_Angeles ................... [RUN]
00:45:32 4 of 4 FAIL 1 accepted_values_b_ctz__America_Los_Angeles ....................... [FAIL 1 in 0.60s]
00:45:32
00:45:32 Running 1 on-run-end hook
=========================================
SESSION TIMEZONE IS: America/Los_Angeles
=========================================
00:45:33 1 of 1 START hook: my_dbt_project.on-run-end.0 ................................. [RUN]
00:45:33 1 of 1 OK hook: my_dbt_project.on-run-end.0 .................................... [OK in 0.00s]
00:45:33
00:45:34
00:45:34 Finished running 2 table models, 2 data tests, 2 project hooks in 0 hours 0 minutes and 11.68 seconds (11.68s).
00:45:34
00:45:34 Completed with 1 error and 0 warnings:
00:45:34
00:45:34 Failure in test accepted_values_b_ctz__America_Los_Angeles (models/schema.yml)
00:45:34 Got 1 result, configured to fail if != 0
00:45:34
00:45:34 compiled code at target/compiled/my_dbt_project/models/schema.yml/accepted_values_b_ctz__America_Los_Angeles.sql
00:45:34
00:45:34 Done. PASS=3 WARN=0 ERROR=1 SKIP=0 TOTAL=4
^ Now things don't work so well anymore.
The session is shared and causes model b to no longer use the account timezone default.
7fb454 being ahead of 6857e6 - therefor some customers ran into this.
dbt Cloud users probably want to do:
In their extended attributes so that this doesn't automatically get turned on for them by default (whenever we roll forward again but have not yet fixed this issue of reuse_connections: True by default).
Is this a new bug in dbt-snowflake?
Current Behavior
We made connection reuse the default in #1109 - this is breaking some users jobs when they depend on thread session isolation. We should make reuse_connections opt-in instead of opt-out.
Expected Behavior
Make this behave the way they were prior to making
reuse_connections: True
by default.Steps To Reproduce
First, let's create a UDF to return the current session timezone - we will be using this in our model code later:
Make sure the account level timezone is
America/Los_Angeles
(Snowflakes default):Let's setup our
profiles.yml
:^ Key here being that neither of these keys
client_session_keep_alive
,reuse_connections
are set by the end user (dbt Cloud default profile).Now, let's setup our dbt project.
The idea here is that only model
a.sql
should operate on timezonePacific/Auckland
- and modelb.sql
should follow the account timezoneAmerica/Los_Angeles
.First we build using 1.8.latest:
^ So far so good. The session that built model
b
was independent of the session that built modela
- therefor changing the timezone on the session that built modela
did not affect modelb
.Now, we do a local install of dbt-snowflake from the main branch:
^
3fbc0749491f40d34014336457753b140e1fb1ee
is ahead of commit6857e6b800329a6490c3339824249c18b1af5eda
- which contains the commit that madereuse_connections
True
by default.^ Now things don't work so well anymore.
The session is shared and causes model b to no longer use the account timezone default.
Relevant log output
No response
Environment
Additional Context
In dbt Cloud, the debug logs show which commit of the adapter we are on. We have rolled forward and backward a few times this week.
As of this writing...
^ We are back on
e5b28d
- which is prior to commit6857e6
(the one that introduced this default).However, towards the end of last week, all throughout the weekend, we had been in a rolled forward state:
7fb454
being ahead of6857e6
- therefor some customers ran into this.dbt Cloud users probably want to do:
In their extended attributes so that this doesn't automatically get turned on for them by default (whenever we roll forward again but have not yet fixed this issue of
reuse_connections: True
by default).