Snowflake stored procedure fails from dbt

Question

I have a problem to execute a stored procedure in Snowflake by dbt:

The description of my procedure is like: MyStoredProcedure(ARRAY, VARCHAR, VARCHAR)

So, when I want to run it, I use array_construct function to create the first argument, for example: call MyStoredProcedure(array_construct(array_construct('str_1', 'str_2')), 'schema_name', 'table_name');

This works when I run it in Snowflake. However, when I run this from dbt it fails with this error:

Modifying a transaction that has started at a different scope is not allowed.

Which I am sure that it is something related to invoking array_construct in this call.

I should mention that to run this from dbt I have defined a macro like this:

{% macro MyStoredProcedure() %}
    {% set query -%}
        CALL MyStoredProcedure(
           array_construct(array_construct('str_1', 'str_2')),
           'schema_name',
           'table_name');
    {%- endset %}

    {% do run_query(query) %}
{% endmacro %}

And run it of course like: dbt run-operation MyStoredProcedure

I appreciate any tip or idea to help me with this problem.

Thanks

Answer 1

I'm still very much learning about snowflake but from reading this section of the docs, it appears to me that this could just be a mechanic of your multi-scope procedure call.

From what I can see, the typical (non-dbt) way of doing this in snowflake would be:

begin transaction;
insert 1; 
call storedprocedure();
insert 2;
commit;

Maybe you can adjust your macro something like this which will overcome the scoping nesting issue since everything will be completed in one transaction?

{% macro MyStoredProcedure() %}

    {% set query -%}
        begin transaction;
        CALL MyStoredProcedure(
           array_construct(array_construct('str_1', 'str_2')),
           'schema_name',
           'table_name');
        commit;
    {%- endset %}

    {% do run_query(query) %}
{% endmacro %}

Unable to test this since I can't reproduce the procedure itself.