[Python] How to perform two session.write_pandas transactionally on snowflake?

When working with adding df1, and df2 into two different tables, I want to ensure that df1 doesn't write if df2 fails or any of the middle steps fail. But this doesn't work when using "write_pandas" function to write pandas DF to table.

session.sql("commit").collect()
session.write_pandas(df,table1,db,schema,auto_create_table=True)
raise Exception("transaction Test")
session.write_pandas(df,table2,db,schema,auto_create_table=True)
session.sql("commit").collect()


This basically should be able to rollback table1 insert after raising Exception, but it doesn't. We see the dataframe items appended to table1. Direct SQL Statements do work

session.sql("commit").collect()
session.sql("insert into DB.SC.T1('col1','col2') values (99,'ninenine')")
raise Exception("transaction Test")
session.sql("insert into DB.SC.T2('col1','col2') values (88,'eightyeight')")
session.sql("commit").collect()


So here the first table doesn't get new values when it fails before committing. Is

Continue reading...