[ADAP-734] [Bug] When dbt is stopped with ctrl+c, queries are logged as cancelled, but continue to run on the database.

[jomccr]

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

When I stop dbt during query execution with ctrl+c, the system function system$abort_session(session_id) is executed. This ends the session, but seems to orphan any queries executing within the session. I can see the queries continue to run in the snowflake query history pane.

Expected Behavior

All queries being executed within a session should be aborted with ctrl+c.

Steps To Reproduce

1. Write a new model models/example/slow_model.sql that selects from a large table or is slow to execute.
2. Execute dbt run -m slow_model
3. While this is running, hit ctrl+c at the dbt command line
4. Wait for dbt to cancel query, exit, raise KeyboardInterrupt and return back to shell.

Then, observe in snowflake query history pane two things: system$abort_session(session_id) succeeded, and the slow model is still running.

To kill this slow model, copy session id, open Snowflake SQL worksheet and execute

select system$cancel_all_queries(0123456789);

replacing 0123456789 with your query's session ID.

Output: 1 cancelled.

Relevant log output

00:02:18  Running with dbt=1.5.1
00:02:18  Found 15 models, 0 tests, 0 snapshots, 0 analyses, 321 macros, 0 operations, 0 seed files, 55 sources, 0 exposures, 0 metrics, 0 groups
00:02:18
00:02:20  Concurrency: 8 threads (target='dev')
00:02:20
00:02:20  1 of 1 START sql table model jomccr.support_ndr_basket_item_matrix ............. [RUN]
^C00:02:24  CANCEL query model.nmr_truview_dbt.support_ndr_basket_item_matrix .............. [CANCEL]
00:03:07  1 of 1 ERROR creating sql table model jomccr.support_ndr_basket_item_matrix .... [ERROR in 46.40s]
00:03:07
00:03:07  Exited because of keyboard interrupt
00:03:07
00:03:07  Done. PASS=0 WARN=0 ERROR=0 SKIP=0 TOTAL=0
00:03:07
00:03:07  Finished running  in 0 hours 0 minutes and 48.64 seconds (48.64s).
00:03:07  Encountered an error:

00:03:07  Traceback (most recent call last):
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 86, in wrapper
    result, success = func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 71, in wrapper
    return func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 142, in wrapper
    return func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 168, in wrapper
    return func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 215, in wrapper
    return func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/requires.py", line 250, in wrapper
    return func(*args, **kwargs)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/cli/main.py", line 565, in run
    results = task.run()
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/task/runnable.py", line 441, in run
    result = self.execute_with_hooks(selected_uids)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/task/runnable.py", line 404, in execute_with_hooks
    res = self.execute_nodes()
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/task/runnable.py", line 350, in execute_nodes
    self.run_queue(pool)
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/task/runnable.py", line 277, in run_queue
    self.job_queue.join()
  File "/Users/john.mccrary/Library/Python/3.9/lib/python/site-packages/dbt/graph/queue.py", line 198, in join
    self.inner.join()
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/queue.py", line 90, in join
    self.all_tasks_done.wait()
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/threading.py", line 312, in wait
    waiter.acquire()
KeyboardInterrupt

Environment

- OS: MacOS Ventura 13.4 (M1 macbook pro)
- Python: 3.9.6
- dbt-core: 1.5.1
- dbt-snowflake: 1.5.1

Additional Context

I think the issue could be resolved by replacing system$abort_session with system$cancel_all_queries on this line https://github.com/dbt-labs/dbt-snowflake/blob/main/dbt/adapters/snowflake/connections.py#L387 but I have not tested this.

[jomccr]

Thanks @julio-romero ! I think this will solve a lot of $$$ from people accidentally leaving queries running.

[noel]

I am seeing this with dbt-snowflake 1.7.5

To reproduce, create a my_model.sql like

SELECT r.*
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION AS r
,LATERAL (SELECT SYSTEM$WAIT(30)) AS delay

from the terminal run
dbt run -s my_model

Cancel the run with CTRL-C

When you check snowflake, the query is still running.

[jomccr]

I can confirm that it's working on mac with the following dbt --version

Core:
  - installed: 1.8.3
  - latest:    1.8.3 - Up to date!

Plugins:
  - snowflake: 1.8.3 - Up to date!

I think this didn't get released to dbt-snowflake until 1.7+ so you may need to upgrade your adapter w/ pip install --upgrade dbt-snowflake.

[noel]

to close this out, the issue occurs if you do multiple CTRL-C in fast succession. A single one will abort as expected.