[Bug] dbt-spark snapshots persist helper columns for list unique keys #2101

Existing issue search

• I searched existing issues. The reports linked under Related reports and prior art describe the same helper-column failure class in historical or other-adapter implementations, but none covers the current dbt-spark list-key override exactly.

Which packages are affected?

• dbt-spark

Current Behavior

A dbt-spark snapshot configured with a list-valued unique_key persists dbt's generated dbt_unique_key_N staging columns into the target snapshot table. This happens even when the list contains only one item.

A minimal reproduction uses the check strategy, an explicit column projection, and this generic configuration:

strategy: check
check_cols:
  - tracked_value
  - secondary_value
  - is_active
unique_key:
  - id

The failure develops across repeated runs:

1. The initial snapshot creates the target successfully.
2. On the next run, dbt's staging relation contains dbt_unique_key_1. The Spark materialization does not exclude that helper, so it treats it as a missing/source column and adds it to the target operation.
3. On a later run, snapshotted_data contains both the persisted helper and a newly generated helper with the same name, which can produce an ambiguous-column/invalid compiled merge failure.

The same defect affects composite lists such as [id, sub_id], which generate dbt_unique_key_1 and dbt_unique_key_2. It is not specific to the check strategy: timestamp snapshots use the same list-key staging helpers and Spark merge path. A scalar unique_key avoids the current numbered-helper path.

Expected Behavior

dbt_unique_key_N columns are internal staging helpers. They must not be added to the snapshot target or included in its merge columns. Both one-item and multi-item list keys should work with check and timestamp strategies, including snapshots whose source SQL explicitly projects columns.

This should match dbt-core's default snapshot materialization and the equivalent behavior already present in dbt-databricks.

Steps To Reproduce

1. Create a Delta source table with id, tracked_value, secondary_value, and is_active columns.
2. Configure a snapshot using the check configuration above and source SQL that explicitly selects those four columns.
3. Run dbt snapshot to create the snapshot.
4. Change tracked_value for one source row and run dbt snapshot again.
5. Change it again and run dbt snapshot a third time.
6. Inspect the target schema after the second run or observe the later failure caused by the leaked/duplicated dbt_unique_key_1 helper.

A two-column list reproduces the same behavior with two numbered helpers.

Environment

• adapter in the affected production path: dbt-spark
• affected storage format: Delta
• affected implementation: present in dbt-spark 1.9.3, 1.10.3, 1.11.0, and current main

Root Cause

dbt-spark/src/dbt/include/spark/macros/materializations/snapshot.sql filters only dbt_unique_key / DBT_UNIQUE_KEY. For list keys, dbt generates dbt_unique_key_1...N; those names are not removed from either missing_columns or source_columns. Because dbt-spark overrides the global snapshot materialization, the filtering already implemented in dbt-core does not apply automatically.

Related reports and prior art

• dbt-labs/dbt-spark#157 reported the same three-run helper-column leak on Delta in the older scalar-key implementation. It was closed automatically as stale without a linked fix; a later report said the failure remained with Delta auto-merge disabled. The current issue is narrower: numbered helpers generated by list-valued keys in the present dbt-spark override.
• #940 and #1703 report the same numbered-helper leak in dbt-athena. They require an adapter-specific Athena change and therefore do not fix dbt-spark.
• #1188 is a related but inverse dbt-snowflake bug in which a scalar key is treated as an iterable.
• databricks/dbt-databricks#901 covered the equivalent dbt-databricks list-key failure, fixed by databricks/dbt-databricks#904. That fix is the direct implementation precedent for this issue.

Scope Clarification

During the same investigation, a separate [DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE] error was observed while using dbt-databricks on a different snapshot. That error concerns multiple source rows matching one target row and is not the numbered-helper leak described here. This issue and its proposed fix are intentionally scoped only to dbt-spark's handling of list-key helper columns.