Update Snowflake table

Question

I am trying to update snowflake table via databricks. where i have created databricks temp table and created query based on temp table which will update snowflake table. But i am not sure if it is possible at all Could someone help me on this.

query = """MERGE INTO dw_3nf.temp_tgt target
USING
    (SELECT source1.id as mergekey, 0 as deleted, source1.* FROM dw_3nf.temp_src as source1
    UNION ALL
    SELECT NULL as mergekey,0 as deleted, source1.*
    FROM dw_3nf.temp_src source1 JOIN dw_3nf.temp_tgt target
    ON source1.id = target.id
    WHERE target.live_flag = 1 AND source1.name <> target.name
    UNION ALL
    SELECT target.id as mergekey, 1 as deleted, source.*
    FROM dw_3nf.temp_tgt as target left join dw_3nf.temp_src as source
    ON source.id = target.id 
    WHERE source.id is null and target.live_flag=1
    ) staged_updates
    ON target.id = mergekey
    WHEN MATCHED AND target.live_flag = 1 AND staged_updates.name <> target.name THEN
    UPDATE SET live_flag = 0
    WHEN MATCHED AND staged_updates.deleted = 1 and target.live_flag=1 THEN
    UPDATE SET live_flag=2
    WHEN NOT MATCHED THEN
    INSERT (id, name, live_flag)
    VALUES(staged_updates.id,staged_updates.name,1)"""
df.createOrReplaceTempView("source")
spark.write \
  .format("snowflake") \
  .options(**options) \
  .option("query", query) \
  .save()```

what is the question you are asking, "is the the correct snowflake sql" or "is this the correct databircks code" or "do these match", I say this because if you can from databricks push into a new table via a insert, and from snowflake merge that data into the destination, then combining the two tasks together also should work. — Simeon Pilgrim, Mar 21 '21 at 03:40
Query is correct but i am finding a way to run from databricks, not sure if this is possible. I wanted to understand if we can execute sql on snowflake via databricks which is dependent on databricks temp table. You are right we can create replace databricks temp table to snowflake but we were asked not to created any temp tables. So i am trying to find a way. — SunithaM, Mar 21 '21 at 08:54

score 0 · Answer 1 · answered Mar 21 '21 at 11:47

Take a step back and think about the systems.

'Databricks Cluster' <---> 'Snowflake Cluster'

You want to avoid too much communincation between both system, because the network is slow. So I would recommend either:

copy/insert your data into snowflake and transform merge it there
prepare the data in databricks, copy the result to snowflake merge it there

So can you merge into a Snowflake table from a databricks table in one statement, I don't know. Should you do it. Probably not.

You are right Zephro, thats what i experienced we can't do it. i just want to check if there are any options to push down to snowflake. — SunithaM, Mar 22 '21 at 14:05

Update Snowflake table

1 Answers1