replace_where emits INSERT … BY NAME … REPLACE WHERE rejected by all-purpose compute yet parses on DBSQL

[tkosciuszek]

Describe the bug

For incremental_strategy='replace_where', the adapter (≥1.11.0) emits:

INSERT INTO target BY NAME REPLACE WHERE TABLE
This parses fine on a SQL warehouse but fails on a Databricks All-purpose cluster when dbt job runs on a job compute that executes the SQL against the all-purpose cluster where it fails.

[PARSE_SYNTAX_ERROR] Syntax error at or near 'REPLACE'. SQLSTATE: 42601

The BY NAME clause is gated only on the insert_by_name DBR capability (min_version=(12,2), sql_warehouse_supported=True), which returns true on the cluster — but that runtime's parser does not accept BY NAME in the REPLACE WHERE … TABLE form.

So the adapter generates SQL its own target compute can't parse.

Databricks docs suggest that any DBR 13.3 LTS and above should support INSERT INTO ... BY NAME syntax and makes no mention of exception for all-purpose clusters

Steps To Reproduce

Run a delta incremental model, incremental_strategy='replace_where', with incremental_predicates, against an all-purpose cluster (observed on DBR 16.4)

Expected behavior

INSERT INTO target REPLACE WHERE TABLE (no BY NAME), as ≤1.10.x produced — which runs on both compute types.

Screenshots and log output

All purpose cluster notebook test for confirmation:

System information

The output of dbt --version:

Core:
  - installed: 1.11.11
  - latest:    1.11.11 - Up to date!

Plugins:
  - databricks: 1.12.1 - Up to date!
  - spark:      1.10.1 - Up to date!

The operating system you're using:
MacOs 26.5.1 (25F80)

The output of python --version:
Python 3.12.11

Additional context

Possibly related: PR #1348

[sd-db]

I don't think this is related specifically to AP clusters but more on the specific combination of INSERT INTO ... BY NAME REPLACE WHERE not being supported on the DBR version. I checked this works well on 18.1+. While ``INSERT INTO ... BY NAMEwas added a long time back, theREPLACE WHERE` variant has only been supported quite recently(issue ref). The easiest fix would be to introduce a new capability for this behaviour so that this does not error out on older DBR versions. I am working on the fix. Thanks for raising !