idempotent-updates.py

# Databricks notebook source
# DBTITLE 1,For retrying the example in same session
spark.conf.unset("spark.databricks.delta.write.txnAppId")
spark.conf.unset("spark.databricks.delta.write.txnVersion")  

# COMMAND ----------

# DBTITLE 1,Create sample input table
spark.range(10).write.format("delta").saveAsTable("numbers")

# COMMAND ----------

# DBTITLE 1,Show data in table
spark.sql("select * from numbers order by id").show()

# COMMAND ----------

# DBTITLE 1,Set some dummy values for app Id and transaction version
spark.conf.set("spark.databricks.delta.write.txnAppId", "my-app")
spark.conf.set("spark.databricks.delta.write.txnVersion", "123")  

# COMMAND ----------

# DBTITLE 1,Update table
spark.sql("update numbers set id = id + 1").show()

# COMMAND ----------

# DBTITLE 1,Make sure data is updated
spark.sql("select * from numbers order by id").show()

# COMMAND ----------

# DBTITLE 1,Update table once again - app id and txn version are still the same
spark.sql("update numbers set id = id + 1").show()

# COMMAND ----------

# DBTITLE 1,Has the latest update been executed?
spark.sql("select * from numbers order by id").show()

# COMMAND ----------

# DBTITLE 1,Increment txn version
spark.conf.set("spark.databricks.delta.write.txnVersion", "124")  

# COMMAND ----------

# DBTITLE 1,Do another update - could be different UPDATE statement but using the same one for simplicity
spark.sql("update numbers set id = id + 1").show()

# COMMAND ----------

# DBTITLE 1,Has it run or not?
spark.sql("select * from numbers order by id").show()

# COMMAND ----------

# DBTITLE 1,Clean up
spark.sql("drop table if exists numbers").show()

# COMMAND ----------