[Bug] `snowflake_dml_explicit_transaction` macro incompatible with Snowflake unmanaged Iceberg tables (error 091586)

[sfc-gh-rduan]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Which packages are affected?

• dbt-adapters
• dbt-tests-adapter
• dbt-athena
• dbt-athena-community
• dbt-bigquery
• dbt-postgres
• dbt-redshift
• dbt-snowflake
• dbt-spark

Current Behavior

When using the insert_overwrite incremental strategy against a Snowflake unmanaged Iceberg table (externally cataloged via AWS Glue, etc.), dbt fails with:

091586 (42601): Unmanaged Iceberg tables cannot be modified within a multi-statement transaction.

The root cause is that snowflake_dml_explicit_transaction unconditionally wraps all DML in BEGIN/COMMIT. Snowflake's engine intentionally blocks DML on unmanaged Iceberg tables inside multi-statement transactions because it cannot guarantee rollback semantics against an external catalog (Glue, etc.).

Expected Behavior

The INSERT OVERWRITE runs successfully. Since INSERT OVERWRITE is an inherently atomic single statement, the BEGIN/COMMIT wrapper provides no additional atomicity benefit and should be skipped for unmanaged Iceberg targets.

Steps To Reproduce

Steps to reproduce

1. Create an unmanaged Iceberg table backed by an external catalog (e.g. AWS Glue via CATALOG_SOURCE = ICEBERG_REST).
2. Define a dbt model with materialized='incremental' and incremental_strategy='insert_overwrite' targeting that table.
3. Run dbt run --select <model>.

In Snowflake

CREATE OR REPLACE CATALOG INTEGRATION glue_rest_catalog_int
      CATALOG_SOURCE = ICEBERG_REST
      TABLE_FORMAT = ICEBERG
      CATALOG_NAMESPACE = 'iceberg_sujan_db'
      REST_CONFIG = (
        CATALOG_URI = 'https://glue.us-west-1.amazonaws.com/iceberg'
        CATALOG_API_TYPE = AWS_GLUE
        CATALOG_NAME = 'xxx'
      )
      REST_AUTHENTICATION = (
        TYPE = SIGV4
        SIGV4_IAM_ROLE = 'arn:aws:iam::xxx:role/snowflake-glue-role'
        SIGV4_SIGNING_REGION = 'us-west-1'
      )
      ENABLED = TRUE;

-- Create writable unmanaged Iceberg table
 CREATE OR REPLACE ICEBERG TABLE test_iceberg_rest
      CATALOG_TABLE_NAME = 'test_iceberg_table'
      CATALOG = 'glue_rest_catalog_int'
      EXTERNAL_VOLUME = 'exvol';

INSERT INTO test_iceberg_rest VALUES (1, 'test', current_timestamp());

Now select the values from the table via dbt project

{{ config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        alias='test_iceberg_rest'
    ) }}

    SELECT
        1 AS id,
        'repro_test' AS name,
        current_timestamp() AS created_at

EXECUTE DBT PROJECT FROM WORKSPACE "USER$"."PUBLIC"."DEFAULT$"
PROJECT_ROOT='/iceberg_repro'
ARGS = 'run --select iceberg_test';

Error:

First error in model 'iceberg_test': Database Error in model iceberg_test (models/iceberg_test.sql)
  091586 (42601): Unmanaged Iceberg tables cannot be modified within a multi-statement transaction.
  compiled code at /tmp/dbt/target/run/iceberg_repro/models/iceberg_test.sql. 
Context: DBT 1.9.4, Command: dbt run.
 Check logs/dbt.log for more details.

Relevant log output

Environment

- dbt-snowflake version: 1.9.4
- Snowflake error code: 091586
- Catalog type: AWS Glue (ICEBERG_REST)
- Strategy: `insert_overwrite`

Additional Context

No response