We have an ingestion pipeline built in Snowflake stored procedures.

Our stored procedure is quite complex – and it also handles adding columns. The reason is because we’re ingesting a JSON object – and the only way to identify new columns is by comparing the INFORMATION_SCHEMA.COLUMNS view and then checking against each JSON payload.

With that the stored proc has the following logic:

CREATE PROCEDURE OUTERMOST_STORED_PROC () 
...
AS 
...
$$
    BEGIN TRANSACTION ; 

    COPY INTO SF_TMP FROM S3_STG; 

    CALL another_inner_proc ();

    -- Some error handling that will COMMIT or ROLLBACK

$$;


CREATE PROCEDURE another_inner_proc () 
...
AS 
...
$$
    
    -- Some loop against each JSON object created in SF_TMP 


    -- IF some new column is detected 
    CALL PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes);


    -- ELSE proceed with INSERT / UPDATE logic from SF_TMP to FINAL_TABLE

$$;


CREATE PROCEDURE PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes);
 () 
...
AS 
...
$$
    
    -- Execute an ALTER TABLE ADD COLUMN based on parameters: affected_table, new_columns, new_columns_dtypes

$$;

Now, I’m getting an error on PROC_THAT_UPDATES_SCHEMA – Modifying a transaction that has started at a different scope is not allowed. and upon reading the docs, this involves a DDL query and autocommits, hence affects the outermost transaction.

Has anyone had a similar experience? Any workarounds for this scenario?