In a data warehouse project, can you explain an example of a slowly changing dimension?

A slowly changing dimension (SCD) is a dimension that changes slowly over time, such as a customer’s address or a product’s category. The most common implementation is SCD Type 2, which preserves historical data by creating a new record for each change. For example, consider a Customer dimension with attributes CustomerID, Name, Address, and EffectiveDate. When a customer moves, you insert a new row with the new address, set the new row’s EffectiveDate to the change date, and mark the previous row as inactive (e.g., EndDate or a flag). This allows queries to retrieve the customer’s address at any point in time. In contrast, SCD Type 1 overwrites the existing record, losing history, while Type 3 adds a new column for the previous value. Choosing the right type depends on reporting requirements: if you need to track historical changes, use Type 2; if only the latest value matters, use Type 1. In practice, you design the dimension table with surrogate keys, effective dates, and a status flag, and you build ETL logic that detects changes and inserts new rows accordingly.