https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> hello! I'm getting this error when ...
# ask-elementary-ai
e
@kapa.ai hello! I'm getting this error when elementary's post hook runs 
cannot insert multiple commands into a prepared statement
k
A message from kapa.ai
This error typically happens when Elementary’s on-run-end hook generates a SQL statement that your warehouse doesn’t accept as a single prepared statement (e.g., multiple commands wrapped together). From the collected issues and Slack threads, similar post‑hook problems have been resolved in a few ways, depending on what the hook is trying to write: 1. If it’s related to dbt artifacts / dbt_columns uploads Several warehouses (Snowflake, BigQuery, Redshift) have hit errors when Elementary tries to insert a large batch of artifacts or column metadata in a single statement. In those cases, users either: - Disabled the corresponding autoupload hook, or - Changed Elementary’s insert mode to chunk the inserts. To reduce the size/complexity of insert statements, set in `dbt_project.yml`:
Copy code
yaml    vars:      insert_rows_method: chunk      dbt_artifacts_chunk_size: 5000
This makes Elementary insert artifacts in smaller batches instead of one big statement, which has fixed Snowflake “too many expressions” errors and similar issues in post‑hooks. [[Snowflake list limit](https://elementary-community.slack.com/archives/C02CTC89LAX/p1699519549117769); [Too many queries](https://github.com/elementary-data/elementary/issues/1689)] If the failing part is specifically dbt columns upload, you can temporarily disable it:
Copy code
yaml    vars:      disable_dbt_columns_autoupload: true
This was used as a hotfix for dbt_columns insert failures in Snowflake. [[Snowflake columns bug](https://elementary-community.slack.com/archives/C02CTC89LAX/p1708091691731959)] 2. If it’s concurrency / multiple jobs finishing together When multiple dbt runs finish at the same time, concurrent Elementary post‑hooks can conflict (especially on BigQuery and Redshift), causing transaction‑related errors. The documented mitigations are: - Disable automatic artifacts upload in hooks and run it separately on a schedule:
Copy code
yaml      vars:        disable_dbt_artifacts_autoupload: true
Then update artifacts with:
Copy code
bash      dbt run --select elementary.edr.dbt_artifacts
at a controlled time, rather than on every job completion. [[BQ concurrent updates](https://elementary-community.slack.com/archives/C02CTC89LAX/p1692802428509179); [BQ DML rate limits](https://elementary-community.slack.com/archives/C02CTC89LAX/p1747726633866209)] - Or fully decouple running your project from updating Elementary tables:
Copy code
yaml      models:        your_project:          +materialized: table        elementary:          +schema: elementary          edr:            +enabled: "{{ var('enable_elementary_models', false) }}"
Then:
Copy code
bash      dbt run                      # runs your models only      dbt run --select elementary --vars '{enable_elementary_models: true}'
so the Elementary models (and their writes) run in a separate, non‑overlapping job. [[Parallel jobs guidance](https://elementary-community.slack.com/archives/C02CTC89LAX/p1738245835314909)] 3. Version / schema mismatches after upgrade If this started right after upgrading Elementary, it may be hitting an object that is a view instead of a table (e.g., `INSERT statement's target must be a table`/“cannot insert into a view”). Users fixed this by: - Running:
Copy code
bash      dbt run -s elementary
after upgrading, to rebuild Elementary’s schema objects as the correct materializations. [[Redshift view insert issue](https://elementary-community.slack.com/archives/C02CTC89LAX/p1703064322575999); [Snowflake DBT_COLUMNS table](https://elementary-community.slack.com/archives/C02CTC89LAX/p1699528965324569)] --- What I’d recommend next 1. Check your 
dbt.log
for the exact SQL Elementary is running when this error appears and which table it’s writing to. 2. Apply, in order: - 
insert_rows_method: chunk
+ 
dbt_artifacts_chunk_size
. - If it points to 
dbt_columns
, set 
disable_dbt_columns_autoupload: true
. - If you run many jobs in parallel, consider 
disable_dbt_artifacts_autoupload: true
and/or the pattern where Elementary models run in a separate job. If you can share (sanitized) warehouse type, Elementary version, and the failing SQL statement, I can narrow down which of these configs is most likely to fix your specific error.
e
@kapa.ai I'm sorry, but this error is supposedly related to 
redshift-connector
inability to process multiple commands in a prepared statement. the query that's failing is:
Copy code
begin transaction;
        
        
            insert into "dwh"."prod_elementary"."dbt_models" select * from "dbt_models__tmp_20251128134011060060134011118634";
        
        commit;
k
A message from kapa.ai
You’re right: that error message is characteristic of 
redshift-connector
rejecting multiple SQL commands in a single prepared statement (
begin; ... insert ...; commit;
) rather than anything Elementary‑specific. From the knowledge sources, there are a few Redshift + Elementary post‑hook issues, but none explicitly mention this exact 
cannot insert multiple commands into a prepared statement
error. We do, however, see: - Redshift regressions in 
dbt-redshift
1.5.x and later that break queries due to connector behavior, fixed or mitigated by dbt configuration changes. [[Redshift % regression](https://elementary-community.slack.com/archives/C02CTC89LAX/p1685633764828849)] - A more recent Redshift issue where Elementary’s on‑run‑end hooks started failing after upgrading `dbt-redshift`/dbt Cloud “Versionless”, and users fixed it by changing a dbt global flag rather than modifying Elementary. [[On-run-end commit error](https://github.com/elementary-data/elementary/issues/1712)] In that last case, the solution was:
Copy code
yaml # dbt_project.yml flags:   restrict_direct_pg_catalog_access: False
Users reported that enabling 
restrict_direct_pg_catalog_access
in newer 
dbt-redshift
versions caused Elementary’s hooks to fail, and setting it back to 
False
restored normal behavior. [[On-run-end commit error](https://github.com/elementary-data/elementary/issues/1712)] Given that: - Your failing statement is a standard dbt transaction block (
begin; insert; commit;
) emitted by Elementary’s macro, and - The connector is the one refusing multiple commands, the most consistent, documented workaround in our sources is to adjust dbt’s Redshift flags rather than change Elementary SQL. ### Recommended next steps 1. In 
dbt_project.yml
, add (or ensure) this config:
Copy code
yaml    flags:      restrict_direct_pg_catalog_access: False
Then re‑run your dbt job and see if the Elementary post‑hook still fails. [[On-run-end commit error](https://github.com/elementary-data/elementary/issues/1712)] 2. If the error persists, based on the available docs I don’t have a confirmed Elementary‑side config that changes this 
begin/commit
behavior specifically for Redshift. The knowledge sources don’t mention a Redshift‑only override of the transaction wrapper. Because of that, and since you’ve already traced it to 
redshift-connector
, the strongest evidence we have is that this is a dbt/connector interaction, and the documented fix is the 
restrict_direct_pg_catalog_access
flag. If that doesn’t resolve it, the next step would be to open an issue either: - in 
dbt-redshift
(connector behavior with multi‑statement prepared queries), or - in Elementary’s repo with your exact dbt/dbt‑redshift versions and the failing SQL, so the teams can coordinate a connector‑friendly pattern for these transaction blocks.
e
Hey @Emanuele De Sanctis, we’ve just started encountering this. Did you get to the bottom of it? We’ve just disabled Elementary this morning as a bunch of our pipelines were failing due to this
d
⚠️ I haven't thoroughly tested it; make sure to back up your Elementary data before running it to avoid losing any history. The same problem started for us last Friday, adding this file to 
/your_dbt_project/macros
seems to have fixed the issue for me:
Copy code
{% macro redshift__get_delete_and_insert_queries(relation, insert_relation, delete_relation, delete_column_key) %}
    {% set queries = [] %}

    {% if delete_relation %}
        {% set delete_query %}
            delete from {{ relation }}
            where
            {{ delete_column_key }} is null
            or {{ delete_column_key }} in (select {{ delete_column_key }} from {{ delete_relation }});
        {% endset %}
        {% do queries.append(delete_query) %}
    {% endif %}

    {% if insert_relation %}
        {% set insert_query %}
            insert into {{ relation }} select * from {{ insert_relation }};
        {% endset %}
        {% do queries.append(insert_query) %}
    {% endif %}

    {% do return(queries) %}
{% endmacro %}
It's essentially a copy of 
athena__get_delete_and_insert_queries
macro used by Elementary to insert into Redshift. Instead of returning a string nested in a list (cf. 
default__get_delete_and_insert_queries
), we make a list of strings. These macros are available in your project at 
your_dbt_project/dbt_packages/elementary/macros/utils/table_operations/delete_and_insert.sql
Ideally, Elementary's team should add this macro to 
delete_and_insert.sql
(if they believe this is the proper fix) Not sure why this started to get rejected by Redshift 🤷
e
Did you also have the issue? If there’s multiple of us in the last few days then I’ll open an issue on the GitHub
s
d
Yes, I started to experience the issue Friday, ~20:00 CET
e
Cool. I’ll open the issue as it started this morning for us. I know our Redshift cluster restarted over the weekend but the timings don’t work out for this becoming an issue
👍 1
n
Hey @Euan Blackledge @David Baussart @Emanuele De Sanctis! Thank you for bringing this to our attention. We'll take a closer look. Making sure so we can investigate, you are using Redshift and running dbt package 0.20.1?
e
We’re a couple versions behind: 
0.18.0
. We’ve not made any updates to the project beyond new/updated models over the last few months so have let the packages go a bit stale.
👀 1
n
https://github.com/elementary-data/dbt-data-reliability/pull/899 The fix will be released in a couple of days 🙌
🙌 2
e
Amazing. Thanks very much
d
Thank you
b
So happy to see other people were running into this 😅 — was looking through my PRs to figure out what I broke.
🙌 1
n
Hey everyone, this was released in Elementary dbt 0.21.0 magic
🙌 1
m
Hi everyone, I am still experiencing the issue after upgrading. Did the upgrade fix the issue for everyone else?
➕ 1
m
I too am still experiencing the issue. It appears when I run 
dbt build
commands it's fine, but the error persists when running 
dbt test
commands 🤔
n
Hey everyone! Thanks for letting us know. We have another solution we want to try, is anyone available to help us test it with us today before we release?
e
Following
a
following
m
@Noya Offer what would yall need to have it tested out? I can potentially help out
b
Hm, we haven't run into any issues after the upgrade. Running on patch 195
n
@Bear Jordan That's great! Thanks for sharing
CC @Guy Eshdat
m
@Noya Offer adding that macro fixed the issue when running 
dbt test
commands! no issue now kirby dance
n
Hey @Michael, this is great! Thanks for the update
e
Hello! I don't know if anyone still needs this, but our problem was caused by a bug in 
sqlparse
: https://github.com/andialbrecht/sqlparse/issues/826
We pinned its version to 
0.5.3
and the problem disappeared
33 Views
Open in Slack
PreviousNext
Powered by