APPLY AS DELETE without operation

[smit_tw]

We are performing a full load of API data into the Bronze table (append only), and then using the APPLY CHANGES INTO query to move data from Bronze to Silver using Stream to get only new records. How can we also utilize the APPLY AS DELETE functionality with a condition to mark records as deleted when their corresponding ID no longer exists in the New Bronze Streaming data?

For example:

• On Day 1, the Bronze table contains IDs: 1 to 5, and the Silver table will have IDs: 1 to 5.
• On Day 2, the Bronze table contains IDs: 1 to 4 and 6.
  • In this case, the Silver table should add ID 6, and mark ID 5 as deleted (using the __END_AT field).

How can we achieve this behavior using APPLY CHANGES INTO in Databricks?

[szymon_dybczak]

Hi @smit_tw ,

Take a look at below article:

Propagating Deletes: Managing Data Removal using D... - Databricks Community - 90978

[smit_tw]

@szymon_dybczak is it possible to do directly in Silver layer? We do not have option to go fo Gold layer. I tried to create a TEMP VIEW in Silver DLT pipeline but it gives error for circular dependency as I am comparing data from Silver it self and also creating it in same script.