scd2/merge compound key comparison uses separator-less CONCAT, causing key collisions that retire wrong records

[lfpll]

dlt version

1.23.0 (still present on master: dlt/destinations/sql_jobs.py:377-378)

Describe the problem

SqlMergeFollowupJob.gen_concat_sql joins compound key columns with no separator:

@classmethod
def gen_concat_sql(cls, columns: Sequence[str]) -> str:
    return f"CONCAT({', '.join(columns)})"

In gen_scd2_sql this is used to scope the retire statement by merge key:

UPDATE dataset.table SET valid_to = ...
WHERE valid_to IS NULL
  AND _dlt_load_id NOT IN (SELECT _dlt_load_id FROM staging.table)
  AND CONCAT(key_a, key_b) IN (SELECT CONCAT(key_a, key_b) FROM staging.table)

Distinct composite keys can collide after concatenation: ('doc-7', '12') and ('doc-71', '2') both concat to 'doc-712'. When a record with key ('doc-71', '2') arrives in an incremental batch, the unrelated active record ('doc-7', '12') matches the CONCAT ... IN clause, fails the row-hash check, and is wrongly retired (valid_to set) even though it still exists in the source.

We hit this in production (BigQuery destination) on a table keyed by (document_number, line_id): whenever a newer document whose number is a string-prefix extension of an older one appeared in an incremental batch, the older document's lines were falsely retired. The records then reappear on the next full load, so downstream sees rows flickering in and out of the active set.

Expected behavior

Composite key comparison should be collision-free, e.g. insert a separator that is unlikely to appear in data:

return f"""CONCAT({", '\\x1f', ".join(columns)})"""

or compare as a tuple / EXISTS join instead of a concatenated string. Related: a NULL in any key column currently makes the whole CONCAT NULL, silently excluding that row from the comparison — worth handling in the same fix (e.g. COALESCE with a null sentinel).

Steps to reproduce

Create an scd2 table with compound merge_key and two rows keyed ('12', '3') and ('1', '23'); run an incremental load containing only one of them with changed content — the other row gets retired despite not being in the batch.