Synch-up the daily incremental load with other downstream tables

Background: As on data pipeline for daily run, the process should ensure the synchronization of incremental load for each batch to their respective downstream applications. So, if the data refresh has happened on the primary tables then this should also trigger the corresponding sub-processes to refresh the data in other dependent tables associated in current pipeline.

 

Requirement: Update d_product_sk table if there is data refresh has happened in d_product table.

 

Logic: Compare both the tables based on prod_id & prod_id_sn. So, delete the records from d_product_sk if prod_id are found in d_product_sk . And if prod_id are available in d_product but not available in d_product_sk then insert those records in d_product_sk and do auto-increment of plant_key_sn _starting from max value of_ plant_key_sn.

 

Expected output: Databricks SQL.