Repository: dbt-labs/dbt-snowflake (python)

Issue:
[ADAP-734] [Bug] When dbt is stopped with ctrl+c, queries are logged as cancelled, but continue to run on the database.
### Is this a new bug in dbt-snowflake?

- [X] I believe this is a new bug in dbt-snowflake
- [X] 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 
```sql
select system$cancel_all_queries(0123456789);
```
replacing `0123456789` with your query's session ID. 

Output: `1 cancelled.`

### Relevant log output

```shell
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

```markdown
- 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.

Pull request:
Update connections.py so it doesn’t run anything after its canceled
resolves #711
[docs](https://github.com/dbt-labs/dbt-snowflake/issues/711)

<!---
  Include the number of the issue addressed by this PR above if applicable.
  PRs for code changes without an associated issue *will not be merged*.
  See CONTRIBUTING.md for more information.

  Include the number of the docs issue that was opened for this PR. If
  this change has no user-facing implications, "N/A" suffices instead. New
  docs tickets can be created by clicking the link above or by going to
  https://github.com/dbt-labs/docs.getdbt.com/issues/new/choose.
-->

### Problem

<!---
  Describe the problem this PR is solving. What is the application state
  before this PR is merged?
-->

The queries arent cancelled after typing ctrl + c in console, this might have several performance issues
### Solution

<!---
  Describe the way this PR solves the above problem. Add as much detail as you
  can to help reviewers understand your changes. Include any alternatives and
  tradeoffs you considered.
-->
Changed the cancel command from
```console
system$abort_session(session_id)
```
to
```console
system$cancel_all_queries(session_id)
```


### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md) and understand what's expected of me
- [x] I have run this code in development and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR 
- [x] This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX


Patch:
diff --git a/.changes/unreleased/Fixes-20230804-175222.yaml b/.changes/unreleased/Fixes-20230804-175222.yaml
new file mode 100644
index 0000000..b222b21
--- /dev/null
+++ b/.changes/unreleased/Fixes-20230804-175222.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Cancel all queries when terminating dbt
+time: 2023-08-04T17:52:22.956964-06:00
+custom:
+  Author: julio-romero
+  Issue: "711"
diff --git a/dbt/adapters/snowflake/connections.py b/dbt/adapters/snowflake/connections.py
index 5c1ba57..3272eee 100644
--- a/dbt/adapters/snowflake/connections.py
+++ b/dbt/adapters/snowflake/connections.py
@@ -384,7 +384,7 @@ class SnowflakeConnectionManager(SQLConnectionManager):
 
         connection_name = connection.name
 
-        sql = "select system$abort_session({})".format(sid)
+        sql = "select system$cancel_all_queries({})".format(sid)
 
         logger.debug("Cancelling query '{}' ({})".format(connection_name, sid))
 


Test patch:
diff --git a/tests/unit/test_snowflake_adapter.py b/tests/unit/test_snowflake_adapter.py
index dd1fb8b..85bbb38 100644
--- a/tests/unit/test_snowflake_adapter.py
+++ b/tests/unit/test_snowflake_adapter.py
@@ -265,7 +265,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
 
             self.assertEqual(len(list(self.adapter.cancel_open_connections())), 1)
 
-            add_query.assert_called_once_with("select system$abort_session(42)")
+            add_query.assert_called_once_with("select system$cancel_all_queries(42)")
 
     def test_client_session_keep_alive_false_by_default(self):
         conn = self.adapter.connections.set_connection_name(name="new_connection_with_new_config")