[bug] Changes to redshift delete and insert transaction causing failure on elementary models

[bcchap-ab]

Summary

We encountered a series of elementary model failures related to the way delete and insert transactions are handled by dbt-redshift.
This impacted the following models:

• dbt_exposures
• dbt_seeds
• dbt_snapshots
• dbt_models
• dbt_sources
• dbt_tests
• dbt_columns

Error Encountered

We saw the same error message across all failed models:

Failure in model dbt_exposures (models/edr/dbt_artifacts/dbt_exposures.sql)
Database Error in model dbt_exposures (models/edr/dbt_artifacts/dbt_exposures.sql)
cannot insert multiple commands into a prepared statement

Looking at the debug logs, we can see that the failure occurs during the insert into statement within a transaction block:

    /* --ELEMENTARY-METADATA-- {"command": "build", "invocation_id": "ac4f20af-b813-4ea5-9a91-4c0b16276af3", "package_name": "elementary", "resource_name": "dbt_exposures", "resource_type": "model"} --END-ELEMENTARY-METADATA-- */
        begin transaction;
            insert into "dev"."dbt_cloud_pr_49697_1448_elementary"."dbt_exposures" select * from "dbt_exposures__tmp_20251210191537530897191537538974";
        commit;

Previous to this, the last successful run had a different dbt version and did not include the insert into statement within the transaction block:

    /* --ELEMENTARY-METADATA-- {"command": "build", "invocation_id": "3e52e19e-296d-47fb-9d82-4a1133a266c1", "package_name": "elementary", "resource_name": "dbt_exposures", "resource_type": "model"} --END-ELEMENTARY-METADATA-- */
        begin transaction;

Temporary Fix, Investigation, and Version Comparison

As a temporary solution we removed the package in it's entirety.
We are currently using elementary version 0.20.1. Upon further investigation, we found that the last most successful run used "dbt_version": "2025.11.18+df0aad6" and started failing once "dbt version": "2025.12.10+2a1d9a9" was implemented. We attempted both upgrading and downgrading dbt versions, but the failure persisted. We also attempted to update elementary to the latest version, but the failure also persisted.

[juanjodevio]

I had the same issue, and v21.0 resolved it. However, somehow, even though all models display as successful, it still raises a database error at the end.

[arbiv]

@bcchap-ab @juanjodevio Thanks for reporting this issue!
It was fixed in: #901