Background

We need to determine whether two dlt.Dataset instances can be joined in a single SQL query. The current is_same_physical_destination (dlt/dataset/dataset.py:426) compares str(config) — which calls str(credentials), rendering the full connection URL including username, port, and database:

def is_same_physical_destination(dataset1, dataset2):
    return str(dataset1.destination_client.config) == str(dataset2.destination_client.config)

This is too strict for destinations that support cross-database joins (Snowflake, BigQuery, MSSQL, ClickHouse, Databricks, Dremio, Athena) — they return False even when tables can be joined across databases on the same server/account.

It is semantically wrong for the filesystem destination where joinability comes from the in-process DuckDB query engine, not data location.

A fingerprint() method exists on DestinationClientConfiguration that hashes a location identifier (e.g. host), but it is not used by is_same_physical_destination and is missing on several destinations (DuckDB, Athena, SQLAlchemy, Fabric).

What to implement

1. Add location() to DestinationClientConfiguration

Returns the unhashed physical location string — the identity of where this destination lives. Replaces per-destination fingerprint() overrides. fingerprint() becomes derived on the base class and is no longer overridden per-destination:

def location(self) -> str:
    """Returns the physical location of this destination (e.g. host, bucket URL, project id)"""
    return ""

def fingerprint(self) -> str:
    """Returns a hash of location()"""
    loc = self.location()
    return digest128(loc) if loc else ""

2. Add can_join_with(other) to DestinationClientConfiguration

Determines if tables from this destination can be joined with tables from other in a single query. This reflects the dlt destination's current capability, not the theoretical capability of the underlying database engine (e.g. DuckDB supports ATTACH for cross-database joins, but dlt doesn't implement it yet).

Default implementation compares locations. Destinations override for special semantics (e.g. filesystem, Postgres).

def can_join_with(self, other: "DestinationClientConfiguration") -> bool:
    """Check if tables from this destination can be joined with tables from other in a single query"""
    self_loc = self.location()
    other_loc = other.location()
    if not self_loc or not other_loc:
        return False
    return self_loc == other_loc

3. Update is_same_physical_destination in dlt/dataset/dataset.py

Delegate to can_join_with on the config.

Join compatibility per destination

location() is the physical identity of the destination. can_join_with determines joinability — it defaults to comparing locations but can be overridden.

SQLAlchemy — dialect-dependent can_join_with

SQLAlchemy's location() is host:port (or file path for sqlite). can_join_with depends on the backend dialect:

Missing location() implementations

These destinations currently have no fingerprint() override and return "":

Existing corrections needed