Multi Statement Writes from Spark to Snowflake

[pvignesh92]

Does Spark support multi statement writes to Snowflake in a single session? To elaborate, I have a requirement where I need to do

1. A selective deletion of data from a Snowflake table and
2. Insert records to Snowflake table ( Ranges from around 1 M rows)

I want to wrap these both into a single transaction so that I could get Consistency here. I can't use overwrite mode as it will overwrite my entire table in Snowflake. I have seen runQuery of sfutils support multiStatements but it is more to handle data within Snowflake and not from Databricks to Snowflake.

Thanks,

Vignesh

[-werners-]

are you looking for something like a merge (upsert) or a replaceWhere?

[pvignesh92]

Yes. It's kind of replace where. My input tables are in Databricks layer. So I join them and write to a dataframe. Then I want to write this dataframe to Snowflake table by selectively deleting certain data as I have separate pipeline for different regions. So my overwrite on Snowflake table should only be for that region.

[-werners-]

The only way this can be done in a single command is if snowflake has something like a replaceWhere. I did some searching but did not find anything.

[pvignesh92]

Yes. I have tried the DELETE command using sfutils runQuery and then my data insertion using df.write method. They are captured in different sessions with two different session id in Snowflake. I basically want to wrap these two statements as a single transaction

[-werners-]

if snowflake does not have the possibility, it is not possible in spark either.

[Lakshay]

Hi @Vigneshraja Palaniraj​ , Could you please check the below document if this meets your use case

https://docs.databricks.com/sql/language-manual/delta-merge-into.html

[pvignesh92]

Hi @Lakshay Goel​ . Thanks for your response. This option is more about when you want to update your delta table that is existing in Databricks layer itself. But I'm looking for the option when I want to write to Snowflake with dataframe write API using Spark snowflake connector

[pvignesh92 · Accepted solution]

In my analysis, I got the below understanding

1. If your data is sitting in Snowflake and you have a set of DDL/DML queries that need to wrapped into a single transaction, you can use MULTI_STATEMENT option to 0 and use snowflake utils runQuery method to run them as single transaction. All these command will be executed in Snowflake with a single session id
2. If the requirement is to write a dataframe computed from Databricks to Snowflake along with DDL command, then they will be executed as two different transactions with two different session ids. So not really possible out of the box to wrap them to a single session.