topic Re: APPLY AS DELETE without operation in Data Engineering

APPLY AS DELETE without operation

smit_tw — Fri, 06 Dec 2024 16:49:53 GMT

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?

Re: APPLY AS DELETE without operation

szymon_dybczak — Fri, 06 Dec 2024 17:33:38 GMT

Hi @smit_tw ,

Take a look at below article:

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

Re: APPLY AS DELETE without operation

smit_tw — Fri, 06 Dec 2024 20:58:57 GMT

@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.