snowflake-cloud-data-platform

Can't append to Snowflake table from Snowpark


I want to insert a pandas DataFrame into a Snowflake table, however I get

Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.

when I run

session.write_pandas(df=my_df, table_name='MY_TABLE_NAME')

However, when I insert and overwrite the content of the table, then it works:

session.write_pandas(df=my_df, table_name='MY_TABLE_NAME', overwrite=True)

Am I missing some option here to append only?

Edit

For context, I am running this in a Snowflake Python Worksheet. Before the line with session.write_pandas(...) I query multiple tables (via session.table(...).filter(...).to_pandas()).

Edit 2

Running session.sql("SHOW PARAMETERS LIKE 'AUTOCOMMIT';").show() right before write_pandas, I see that AUTOCOMMIT is set to false. I could not find a way to set it in the Snowpark Python API docs. Is there some other way to set it?

"key" "value" "default" "level" "description" "type"
AUTOCOMMIT false true ACCOUNT The autocommit property determines whether is s...
wrapped within a transaction or not. If autocom...
statement that requires a transaction is execut...
implicitly. If autocommit is off then an explic...
to close a transaction. The default autocommit ...
BOOLEAN

Solution

  • Set AUTOCOMMIT to TRUE on account level in Snowflake.

    Reposting @sriga's comment, which answered the question:

    If you are changing the account parameter inside the stored procedure, it won't allow. Instead you can change the account level parameter by running Alter session set autocommit=True; else you can run your python script outside the snowflake and change the session parameters as mentioned in the below code

    Snowflake enforces the prohibition on setting AUTOCOMMIT inside a stored procedure. Note that changing the AUTOCOMMIT behavior outside a stored procedure will continue to work.