How would you design a Customer dimension table that preserves historical customer attributes? Explain your approach and implementation.

Designing a Customer dimension that preserves historical attributes typically involves implementing a Slowly Changing Dimension (SCD) type 2. The table should include a surrogate key (e.g., customer_sk) that uniquely identifies each row, a natural key (customer_id) that maps to the source system, and columns for all attributes that can change over time. To capture history, add effective_start_date, effective_end_date, and a current_flag column. When a change is detected, insert a new row with the updated attributes, set the effective_start_date to the change date, and close the previous row by setting its effective_end_date to the day before the new start date and clearing its current_flag. The current row has NULL for effective_end_date and current_flag = 1. This approach allows queries to retrieve the state of a customer at any point in time by filtering on the date range. For reporting, you can create a view that always returns the current row or use a time‑based join. Indexes on the surrogate key and effective date columns improve performance. If the source system supports change data capture, you can automate the process with ETL jobs that detect changes and apply the SCD logic.