[ClickHouse] insert_file() double-qualifies table name with database prefix when dataset_name is set, causing UNKNOWN_TABLE error

[apermana-dev]

dlt version

1.25.0

Describe the problem

When dataset_name is set on the pipeline (e.g. dataset_name="tes1"), every load job fails with UNKNOWN_TABLE, even though the target table exists and was successfully created by dlt moments earlier in the same run.

DB::Exception: Table dlt.`\`dlt\`.\`tes1____dlt_pipeline_state\`` does not exist. Maybe you meant dlt.tes1____dlt_pipeline_state?. (UNKNOWN_TABLE) (for url http://localhost:8123)

The suggested table name in the error (dlt.tes1____dlt_pipeline_state) is correct and does exist — confirmed via SELECT * FROM dlt.tes1____dlt_pipeline_state, which works fine. The failure only happens inside dlt's own load job, not on manual queries.

This affects every table, not just _dlt_pipeline_state — regular resource tables (e.g. trades → tes1___trades) fail the same way.

Expected behavior

Load job succeeds; table tes1___sample is populated.

Steps to reproduce

import dlt
from dlt.destinations import clickhouse
from dlt.destinations.impl.clickhouse.configuration import ClickHouseCredentials

credentials = ClickHouseCredentials(
    "clickhouse://default:@localhost:9000/dlt"
)
credentials.http_port = 8123

pipeline = dlt.pipeline(
    pipeline_name="repro_pipeline",
    destination=clickhouse(credentials),
    dataset_name="tes1",   
)

@dlt.resource
def sample():
    yield [{"id": 1, "value": "a"}]

load_info = pipeline.run(sample())
print(load_info)

Operating system

Linux

Runtime environment

Local

Python version

3.13

dlt data source

No response

dlt destination

No response

Other deployment details

Additional information

Actual behavior

Load job fails terminally with UNKNOWN_TABLE, even though the table was just created successfully in the same run.

Root cause

In dlt/destinations/impl/clickhouse/sql_client.py, insert_file():

def insert_file(self, file_path, table_name, file_format, compression):
    with clickhouse_connect.create_client(
        host=self.credentials.host,
        port=self.credentials.http_port,
        database=self.credentials.database,   # <- database already bound here
        ...
    ) as clickhouse_connect_client:
        return clk_insert_file(
            clickhouse_connect_client,
            self.make_qualified_table_name(table_name),  # <- already includes `database`.`prefix___table`
            ...
        )

make_qualified_table_name() returns a fully database-qualified identifier (e.g. `dlt`.`tes1____dlt_pipeline_state`). But the clickhouse_connect client passed into clk_insert_file is already bound to database=self.credentials.database. clickhouse_connect's insert_file helper appears to additionally qualify the given table name against the client's bound database, resulting in a doubled identifier: dlt. + `dlt`.`tes1____dlt_pipeline_state` — which ClickHouse rejects as UNKNOWN_TABLE since it's not a valid two-part identifier.

CREATE TABLE statements elsewhere in the client do not hit this bug (tables are created successfully), so this appears isolated to the insert_file code path.