Repository: databricks/dbt-databricks

Issue:
Unique temp tables persist and throw warnings on session close
### Describe the bug

When using the `unique_tmp_table_suffix` config with incremental models, a temporary table is generated with a name like `dbt_task__dbt_tmp_a232cf9a-252c-40b4-8d10-f45e23011df4`. This table name contains the `-` illegal character that must be quoted for successful executions.

However, despite quoting of table identifiers set to `true`, this table name is not quoted when attempting to drop the table when the session closes leading to warnings and the temporary table ultimately not being dropped. This leaves temporary tables behind after every model execution which causes clutter in the catalog. 

### Steps To Reproduce

1. Create any model with an incremental materialization and the config `unique_tmp_table_suffix` enabled e.g.
  ```sql
  {{ config(
      materialized='incremental',
      unique_tmp_table_suffix=True,
  ) }}

  SELECT * FROM VALUES (1,2,3)
  ```

2. Run the model to create it initially.
3. Run the model again to incrementally add data to it. The model will be updated, but the temporary table storing the intermediary data will remain with warnings thrown in the log.

### Expected behavior

The temporary table should be dropped without warnings.

### Screenshots and log output

```
20:41:32  databricks-sql-connector adapter: Attempt to close session raised an exception at the server: 
[INVALID_IDENTIFIER] The unquoted identifier schema_changes__dbt_tmp_ce5713c1-86db-449b-b96a-1d2b12f56b30 is invalid and must be back quoted as: `schema_changes__dbt_tmp_ce5713c1-86db-449b-b96a-1d2b12f56b30`.
Unquoted identifiers can only contain ASCII letters ('a' - 'z', 'A' - 'Z'), digits ('0' - '9'), and underbar ('_').
Unquoted identifiers must also not start with a digit.
Different data sources and meta stores may impose additional restrictions on valid identifiers. SQLSTATE: 42602 (line 1, pos 32)

== SQL ==
schema_changes__dbt_tmp_ce5713c1-86db-449b-b96a-1d2b12f56b30
--------------------------------^^^
```

### System information
**The output of `dbt --version`:**
```
Core:
  - installed: 1.9.4
  - latest:    1.9.4 - Up to date!

Plugins:
  - databricks: 1.10.0 - Up to date!
  - spark:      1.9.2  - Up to date!
```

**The operating system you're using:**
MacOS 15.3.2

**The output of `python --version`:**
Python 3.12.9

Discussion:

Solution patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 96226b81..35460de3 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,6 +7,7 @@
 - Added internal table property delta.columnMapping.maxColumnId to ignore_list to allow enabling delta.columnMapping.mode without breaking concurrent job runs (thanks @
   iamatharkhan!) ([991](https://github.com/databricks/dbt-databricks/pull/991))
 - For insert_overwrite, raise exception when using SQL Warehouse and inform users that such use causes truncate + insert ([992](https://github.com/databricks/dbt-databricks/pull/992))
+- Remove illegal characters in unique temporary table names which prevented dropping these tables on session close (thanks @geo-martino!) ([995](https://github.com/databricks/dbt-databricks/pull/995))
 
 ## dbt-databricks 1.10.0 (Apr 08, 2025)
 
diff --git a/dbt/adapters/databricks/impl.py b/dbt/adapters/databricks/impl.py
index d9ae0a1b..6185c625 100644
--- a/dbt/adapters/databricks/impl.py
+++ b/dbt/adapters/databricks/impl.py
@@ -753,8 +753,9 @@ class DatabricksAdapter(SparkAdapter):
         return return_columns
 
     @available
-    def generate_unique_temporary_table_suffix(self, suffix_initial: str = "__dbt_tmp") -> str:
-        return f"{suffix_initial}_{str(uuid4())}"
+    @staticmethod
+    def generate_unique_temporary_table_suffix(suffix_initial: str = "__dbt_tmp") -> str:
+        return f"{suffix_initial}_{re.sub(r'[^A-Za-z0-9]+', '_', str(uuid4()))}"
 
     @available
     @staticmethod


Test patch:
diff --git a/tests/unit/test_adapter.py b/tests/unit/test_adapter.py
index cae0ed7d..bdbe0263 100644
--- a/tests/unit/test_adapter.py
+++ b/tests/unit/test_adapter.py
@@ -1,3 +1,4 @@
+import re
 from multiprocessing import get_context
 from typing import Any, Optional
 from unittest.mock import Mock, patch
@@ -945,6 +946,20 @@ class TestDatabricksAdapter(DatabricksAdapterBase):
                 list(table_names)[:5]
             )
 
+    def test_generate_unique_temporary_table_suffix_adds_unique_identifier(self):
+        suffix_initial = "dbt_tmp"
+
+        result = DatabricksAdapter.generate_unique_temporary_table_suffix(suffix_initial)
+        assert result != suffix_initial
+        assert result.startswith(suffix_initial)
+        assert result != DatabricksAdapter.generate_unique_temporary_table_suffix(suffix_initial)
+
+    def test_generate_unique_temporary_table_suffix_generates_no_new_illegal_characters(self):
+        suffix_initial = "dbt_tmp"
+
+        result = DatabricksAdapter.generate_unique_temporary_table_suffix(suffix_initial)
+        assert not re.match(r"[^A-Za-z0-9_]+", result.replace(suffix_initial, ""))
+
 
 class TestCheckNotFound:
     def test_prefix(self):