If we need to track historical changes to our supplier lead times at Anduril, how would you implement slowly changing dimensions (SCD) in your data model?
Use the supplied supplier and lead-time snapshot data to produce the historical records needed for a Type 2 dimension, including effective date ranges and the current-record indicator.
supplier_id, supplier_name, lead_time_days, effective_from, effective_to, and is_current.supplier_id, then effective_from ascending. The latest version for each supplier has a null effective_to.| Column | Type | Description |
|---|---|---|
| supplier_idPK | INT | Unique supplier identifier |
| supplier_name | VARCHAR(120) | Supplier name |
| Column | Type | Description |
|---|---|---|
| snapshot_idPK | INT | Unique snapshot identifier |
| supplier_id | INT | Referenced supplier |
| snapshot_date | DATE | Date the lead time was observed |
| lead_time_days | INT | Observed supplier lead time in days |