Source data is in the bronze layer. We need to implement SCD type 2 in the target layer. If data arrives daily in batch and the bronze layer is refreshed each day, what logic would you use between the bronze and target layers to achieve SCD type 2?

To implement SCD type 2 between a bronze layer (raw data) and a target layer (consolidated, slowly changing dimension), follow these steps:

1. Staging: Load the daily batch from the bronze layer into a temporary staging table.
2. Hashing: Compute a hash of the business key plus all dimension attributes to detect changes.
3. Identify changes: Join the staging table to the target on the business key. For each key, compare the hash values. If the hash differs or the key is new, mark the row as changed.
4. Close old records: For changed rows, update the existing target record(s) by setting is_active = 0 and close_date = current timestamp.
5. Insert new records: Insert the new version of the record into the target with is_active = 1, open_date = current timestamp, and the new hash.
6. Atomicity: Wrap the updates and inserts in a single transaction or use a MERGE statement to avoid partial updates.
7. Incremental refresh: Since the bronze layer is refreshed daily, the staging table will contain only the latest batch, making the process efficient.

This logic ensures that every change is captured with historical context, enabling accurate reporting and audit trails.