orphaned __dbt_new_data_<UUID> tables after failed/interrupted delete+insert incremental runs

## orphaned __dbt_new_data_ tables after failed/interrupted delete+insert incremental runs

`delete+insert` incremental strategy leaves orphaned `__dbt_new_data_` tables after failed or interrupted runs

### Describe the bug

When using the `delete+insert` incremental strategy, `dbt-clickhouse` creates intermediate tables with names similar to:

```text
__dbt_new_data_
```

During successful executions, these intermediate tables are normally cleaned up.

However, when a dbt run fails, is interrupted, cancelled, times out, or otherwise terminates before successful completion, some of these `__dbt_new_data_` tables remain in ClickHouse.

Repeated failed or interrupted executions therefore result in multiple orphaned tables accumulating in the target database.

For example:

```text
model_a__dbt_new_data_12345678_1234_1234_1234_123456789abc
model_a__dbt_new_data_87654321_4321_4321_4321_cba987654321
model_b__dbt_new_data_abcdef12_3456_7890_abcd_ef1234567890
```

These tables then have to be removed manually or through an external cleanup process such as an Airflow maintenance DAG.

The creation of the intermediate table itself is expected behavior.

The issue is that the intermediate relation is not reliably cleaned up when the materialization terminates unsuccessfully.

---

### Steps to reproduce

1. Create an incremental dbt model using the `delete+insert` incremental strategy.

```sql
{{ config(
materialized='incremental',
incremental_strategy='delete+insert'
) }}

SELECT
...
FROM ...
```

2. Run the model:

```bash
dbt run --select
```

3. Allow dbt to reach the point where the intermediate table has been created:

```text
__dbt_new_data_
```

4. Cause the dbt execution to terminate unsuccessfully.

Examples include:

* cancel the dbt command;
* terminate the Airflow worker or Kubernetes pod;
* cause the ClickHouse query to fail;
* trigger a timeout;
* trigger a connection/network failure;
* cause another exception after creation of the intermediate relation.

5. Inspect ClickHouse afterward:

```sql
SELECT
database,
name,
metadata_modification_time
FROM system.tables
WHERE name LIKE '%__dbt_new_data_%'
ORDER BY metadata_modification_time;
```

6. The generated intermediate table remains in the database.

For example:

```text
prod.__dbt_new_data_
```

7. Repeat the failed run.

A new UUID-based intermediate table can be created while the previous one remains, resulting in multiple orphaned relations over time.

---

### Expected behaviour

Intermediate relations created internally by the `delete+insert` materialization should be cleaned up whenever possible, regardless of whether execution succeeds or fails.

For example, the lifecycle should behave conceptually like:

```text
CREATE intermediate table
|
v
execute delete+insert materialization
|
+---- success
|
+---- exception/failure
|
v
attempt DROP of intermediate table
```

In implementation terms, cleanup could potentially be handled using exception-safe behavior similar to a `try/finally` pattern.

At minimum, if dbt receives and handles an exception after creating the intermediate relation, it should attempt to remove the generated:

```text
__dbt_new_data_
```

table before propagating the failure.

This would prevent production databases from gradually accumulating dbt internal relations and would avoid requiring external cleanup jobs specifically for dbt-generated staging tables.

Obviously, cleanup cannot be guaranteed in cases where the process is forcefully killed and has no opportunity to execute cleanup logic.

However, failures and exceptions handled by dbt/dbt-clickhouse should ideally attempt cleanup.

---

### Code examples, such as models or profile settings

Example incremental model configuration:

```sql
{{ config(
materialized='incremental',
incremental_strategy='delete+insert'
) }}

SELECT
...
FROM ...
{% if is_incremental() %}
WHERE ...
{% endif %}
```

The generated intermediate relations follow a naming pattern similar to:

```text
__dbt_new_data_
```

For example:

```text
payroll_model__dbt_new_data_12345678_1234_1234_1234_123456789abc
```

The following query can be used to identify leftover intermediate tables:

```sql
SELECT
database,
name,
metadata_modification_time
FROM system.tables
WHERE name LIKE '%__dbt_new_data_%'
ORDER BY metadata_modification_time;
```

The affected ClickHouse connection uses the HTTP driver.

Relevant `profiles.yml` configuration:

```yaml
driver: http
port: 8123
```

---

### dbt and/or ClickHouse server logs

The issue is observed when a dbt execution terminates unsuccessfully after the `__dbt_new_data_` intermediate relation has already been created.

After the failed execution, the temporary/intermediate table remains visible in ClickHouse:

```sql
SELECT
database,
name,
metadata_modification_time
FROM system.tables
WHERE name LIKE '%__dbt_new_data_%'
ORDER BY metadata_modification_time;
```

The orphaned relation is not automatically removed after the failed run.

Exact dbt and ClickHouse logs from a failed execution can be provided if required.

A useful reproduction would be:

```text
dbt starts incremental model
|
v
__dbt_new_data_ created
|
v
materialization encounters error
|
v
dbt run fails
|
v
__dbt_new_data_ still exists
```

---

### Configuration

#### Environment

* dbt version: **1.11.13**

* `dbt-core`
* pinned in `uv.lock`

* dbt-clickhouse version: **1.10.2**

* defined in `pyproject.toml` / `uv.lock`

* clickhouse-driver version (if using native): **0.2.11**

* installed as a `dbt-clickhouse` dependency
* **not actively used**
* the dbt profile uses `driver: http`

* clickhouse-connect version (if using http): **1.7.1**

* active ClickHouse client
* `profiles.yml` uses port `8123`
* `driver: http`

* Python version: **3.13**

* `.python-version` is configured for Python 3.13
* Airflow image uses Python 3.13

* Operating system: **Linux — Debian-based container**

* runtime image:

```text
apache/airflow:3.3.0-python3.13
```

* Airflow version: **3.3.0**

For reference, the relevant environment versions are:

```text
dbt-core: 1.11.13
dbt-clickhouse: 1.10.2
clickhouse-connect: 1.7.1
clickhouse-driver: 0.2.11 (installed but not used)
Python: 3.13
Airflow: 3.3.0
ClickHouse Server: 25.6.5.41
Connection driver: HTTP
Connection port: 8123
```

---

#### ClickHouse server

* ClickHouse Server version: **25.6.5.41 (official build)**

* ClickHouse Server non-default settings, if any:

* None known that appear relevant to this behavior.

* `CREATE TABLE` statements for tables involved:

* The issue does not appear to depend on a specific model schema.
* It concerns lifecycle management of intermediate relations created internally by the `delete+insert` incremental materialization.

* Sample data for these tables:

* Sample data does not appear necessary to reproduce the behavior.
* The issue concerns the lifecycle and cleanup of dbt-generated intermediate tables rather than their contents.

---

### Additional context

This occurs in an orchestrated dbt environment running through Airflow.

The important distinction is that creation of:

```text
__dbt_new_data_
```

is not itself considered a bug.

The issue is that these relations can remain indefinitely after unsuccessful dbt executions.

In an orchestrated environment, unsuccessful termination may occur because of:

* dbt model errors;
* ClickHouse query failures;
* resource exhaustion;
* task timeout;
* network or connection issues;
* Airflow task failure;
* Kubernetes worker/pod termination;
* manual cancellation.

Because each execution uses a unique identifier in the intermediate relation name, repeated failures can result in:

```text
model__dbt_new_data_UUID_1
model__dbt_new_data_UUID_2
model__dbt_new_data_UUID_3
model__dbt_new_data_UUID_4
...
```

This causes unnecessary tables to accumulate in production.

An external workaround is possible by periodically querying:

```sql
SELECT
database,
name,
metadata_modification_time
FROM system.tables
WHERE name LIKE '%__dbt_new_data_%';
```

and removing sufficiently old relations through an Airflow/cron maintenance job.

However, this is only a workaround.

Since these relations are created internally by dbt-clickhouse, their cleanup should ideally also be handled by dbt-clickhouse whenever the process still has the opportunity to perform cleanup after a failure.

It may also be useful for cleanup logic to be defensive and use something equivalent to:

```sql
DROP TABLE IF EXISTS
```

when handling failures.

This would significantly reduce the possibility of orphaned `__dbt_new_data_` relations accumulating in long-running production environments.

Start at the dbt-clickhouse delete+insert incremental materialization entry point and trace creation and cleanup of the __dbt_new_data_ relation. Reproduce it with the provided dbt run and failure scenarios, using the system.tables query to verify leftovers. Done means handled failures attempt cleanup while preserving successful runs; forcefully killed processes are out of scope.

Written by the indexing model from the issue text.