onSchemaChange with insert_overwrite generates invalid SQL: Unrecognized name: partitions_for_replacement

[apilaskowski]

Summary

When an incremental table uses incrementalStrategy: "insert_overwrite" together with onSchemaChange set to FAIL, EXTEND or SYNCHRONIZE, the generated script fails at runtime with:

Unrecognized name: partitions_for_replacement

INSERT_OVERWRITE on its own (with onSchemaChange unset or IGNORE) is unaffected.

Cause

cli/api/dbadapters/execution_sql.ts, executeDynamicDmlSql() emits the partition-replacement MERGE through EXECUTE IMMEDIATE, while partitions_for_replacement remains an ordinary script variable declared in the enclosing block:

BEGIN
  DECLARE partitions_for_replacement DEFAULT (
    ARRAY(
      SELECT DISTINCT DATE(ts)
      FROM `tbl_df_temp_<id>_temp`
      WHERE DATE(ts) IS NOT NULL
    )
  );

  EXECUTE IMMEDIATE (
    "MERGE `project.dataset.tbl` DATAFORM_DEST " ||
    "USING `tbl_df_temp_<id>_temp` DATAFORM_SOURCE " ||
    "ON FALSE " ||
    "WHEN NOT MATCHED BY SOURCE AND DATE(ts) IN UNNEST(partitions_for_replacement) THEN " ||
    "DELETE " ||
    "WHEN NOT MATCHED BY TARGET THEN " ||
    "INSERT (" || dataform_columns_list || ") VALUES (" || dataform_columns_list || ")"
  );
END;

BigQuery evaluates dynamic SQL in a separate scope. Script variables are not visible inside the EXECUTE IMMEDIATE string; they have to be passed with USING (values only, via ? / @name placeholders) or baked into the statement text before execution.

Reproduce

Minimal, warehouse-only repro:

BEGIN
  DECLARE partitions_for_replacement DEFAULT [1, 2, 3];
  EXECUTE IMMEDIATE (
    "SELECT x FROM UNNEST([1, 5]) AS x WHERE x IN UNNEST(partitions_for_replacement)"
  );
END;

Query error: Unrecognized name: partitions_for_replacement at [1:52]

Through the CLI, any incremental table combining the two settings reproduces it on the second run (the first run just creates the table, so no schema-change procedure is generated):

config {
  type: "incremental",
  incrementalStrategy: "insert_overwrite",
  onSchemaChange: "EXTEND",
  bigquery: { partitionBy: "DATE(ts)" }
}
SELECT CURRENT_TIMESTAMP() AS ts, 1 AS id

When this was introduced

This is a consequence of moving the incremental DML inside the stored procedure in #2320. Before that change the same MERGE was emitted statically, in the same block as the DECLARE, so the variable resolved correctly:

BEGIN
  DECLARE partitions_for_replacement DEFAULT ( ... );
  MERGE `project.dataset.tbl` DATAFORM_DEST
  USING `staging_table_temp_<id>` DATAFORM_SOURCE
  ON FALSE
  WHEN NOT MATCHED BY SOURCE AND DATE(ts) IN UNNEST(partitions_for_replacement)
  THEN DELETE
  WHEN NOT MATCHED BY TARGET THEN INSERT (`id`,`field1`) VALUES (`id`,`field1`);
END;

That older form ran, but used the pre-alteration column list — the stale-column bug #2320 set out to fix. So the combination goes from "executes with the wrong column list" to "does not execute at all". Note that on released versions 3.0.58–3.0.64 the combination was already unusable for a different reason (#2307), so no published release is known to handle insert_overwrite + onSchemaChange correctly.

Only the golden-file test covers this path (cli/api/goldens/insert_overwrite_extend.sql); goldens assert on generated text, not on whether BigQuery accepts it, which is why it was not caught.

Possible approach

Inline the array into the statement text with FORMAT and %T, which produces a valid array literal:

EXECUTE IMMEDIATE FORMAT(
  "SELECT x FROM UNNEST([1, 5]) AS x WHERE x IN UNNEST(%T)", partitions_for_replacement);
-- Dynamic SQL: ... WHERE x IN UNNEST([1, 2, 3])  → returns 1

Verified working against BigQuery. This is also closer to how the managed Dataform service builds this statement, which uses EXECUTE IMMEDIATE FORMAT(''' ... ''', ...) rather than string concatenation.

Whichever approach is taken, it would be worth adding live execution coverage for insert_overwrite + onSchemaChange, since a golden-only test cannot catch a SQL validity problem like this.

Versions

Component	Value
Warehouse	BigQuery
Affected path	incrementalStrategy: "insert_overwrite" + onSchemaChange in (FAIL, EXTEND, SYNCHRONIZE)
Unaffected	insert_overwrite with onSchemaChange unset or IGNORE; merge/default strategy with any onSchemaChange