@Enric Llop​ :

When using Delta Live Tables to perform a "rip and replace" operation, where you want to replace the existing data in a table with new data, there are a few things to keep in mind.

First, the apply_changes function is used to apply changes from a source table to a target table. The source table can be a Delta table or a streaming DataFrame, while the target table must be a Delta table. To perform a "rip and replace" operation, you can create a temporary table that contains only the new data, and then use apply_changes with the apply_as_delete option to delete the existing data in the target table before inserting the new data. Here's an example:

from delta.tables import *
from pyspark.sql.functions import lit
 
# Create a temporary table with only the new data
new_data = DeltaTable.forPath(spark, "path/to/new/data")
new_data.createOrReplaceTempView("tmp_new_data")
tmp_new_data = spark.sql("SELECT *, 0 as DeleteFlag FROM tmp_new_data")
 
# Create the target table if it doesn't exist
target_table = DeltaTable.forPath(spark, "path/to/target/table")
target_table.createOrReplaceTempView("tmp_target_table")
 
# Apply the changes to the target table, deleting the existing data
# and inserting the new data
target_table.alias("target").merge(
    tmp_new_data.alias("source"),
    "target.Hash_Key = source.Hash_Key"
).whenMatchedDelete(condition="source.DeleteFlag = 1").whenNotMatchedInsertAll().execute()

In this example, we create a temporary table tmp_new_data that contains only the new data, with a DeleteFlag column set to 0. We also create a temporary view tmp_target_table that refers to the target table. We then use the merge function to apply changes from tmp_new_data to tmp_target_table. The whenMatchedDelete option deletes any rows from the target table where the DeleteFlag is 1, effectively removing the existing data. The whenNotMatchedInsertAll option inserts all rows from tmp_new_data that don't match any rows in the target table.

Note that this example assumes that the Hash_Key column is used as the primary key for the table. You may need to adjust the join condition in the merge function to match the columns in your table. Also note that the merge function is a Delta-specific operation and may not work with other types of tables.