Construct SCD2-Based Historical Data on Indication Level Metric

Requirements: Develop SQL logic, Read the product_indication_metrics table. Find the indication-level metric updates as periodic. Add SCD2 history using business_key = product_id + indication_id. Detect attribute changes and trigger new version with updated date ranges. Only one record should be marked current_flag = 1 per pair. Write the SCD table in 'product_indication_metrics'. Display the final historical Output table with SCD2 results with column of ‘product_id’, ‘indication_id’, ‘metric_type’, ‘metric_value’, ‘effective_start_dt’, ‘effective_end_dt’, ‘current_flag’, ‘updated_timestamp’.

 

Unity Catalog: ‘product_indication_metrics’