How do you design a dimension table for a slowly changing dimension?

The key takeaway with a properly designed and maintained Type 1 SCD is that there is only one version of the data available: the most recent version. SCDs can't reliably answer point-in-time questions like "What was the price of Widget X on November 12, 2021?" or "What was the credit status of Customer Q at the end of 2019?" If the price or credit status changed after the time period of interest, you're out of luck. Two common use cases for a Type 1 SCD include stable and long-lived data, such as a list of U.S. states; and historically insignificant data, such as supplementary customer information like a phone number or e-mail address.

The temptation of Type one SCD is that its alluringly simple, but it comes at the cost of tracking changes over time. This version of SCD is mainly used when the only relevant changes that occur to the dimensional data are corrections, and no meaningful changes are expected (or possible).

I've applied "Type 1: Overwrite" in my work experience to handle a Slowly Changing Dimension (SCD) variable. This solution is simple in implementation thus less cost of investment. In business value, the variable just needs to be in its latest version (e.g. name of an entity like hospitals, provinces, etc.) because the changes are really rare and do not affect the analysis. For more complex cases, we could consider other design types for better outcomes.

Kurt's observation that we are tempted to do Type 1 for simplicity is dead-on. I would add that often times clients will come to you saying something like "well, we only ever run reports about the current month so we don't need history," then later say "we have to do a compliance report that reflects what was true at the end of last calendar year." If dimensional attributes change over time and these are actual changes rather than corrections, strive to use a Type 2 at least.

SCDs are usually a sign of a poorly designed DW. "Oh. We didn't think about that when we were planning the data warehouse." The price of a product would typically be a fact, not a dimension. But there are applications where someone will ask "what percentage of our sales are for products under $10?" or something similar. Most large online retailers should be asking those kinds of questions. An experienced architect would help steer the design to accommodate those questions. (Granted, that doesn't help existing systems where the usage changes.)

Given the relatively inexpensive cost of compute and storage in the modern era of easily scalable cloud data warehouses, the value to be derived from a Type 2 SCD often outweighs the cost. Implementing a Type 2 SCD can be done most efficiently on any DBO that contains a "updated_at" field, so that identifying records that have changed since the last update can save processing an entire table looking for changed dimensions. This allows you to track and monitor for dimensional change over time that is not possible in Type 1.

In a big data platform, Instead of having active indicator, consider adding a start date and derive end date dynamically via window function like Lead/Lag Example : NVL(LEAD(START_DT) OVER(PARTITION BY <PRIMARY KEY> ORDER BY START_DT)-1,<HIGH END DATE for latest record>) AS END_DT

Report queries can be very complex with Type 2 data, and cause performance issues. The query has to check for valid or invalid rows. Aggregations spread over a longer timeline also become complex.

I agree with the dating approach for managing Type 2 dimensions, but also advocate creating a separate Type 1 version to provide options for the consumer, as I've found that most users, especially report writers, find Type 1 sufficient for their needs. However, there will always be a need to see the history of change in an enterprise's core dimensional data, so it's irresponsible, imo, not to plan for and provide Type 2 capabilities.

That last sentence is a problem. The idea behind the data warehouse is that queries are optimized at the expense of writes. An oversimplified dimension could be an identity column, product name, and price. An existing row is 123,'Product 1', 9.99. When the price changes to 10.49 a new row is added, 314,'Product 1', 10.49. To insert a new row the fact will have to join based on the name and price. The query and all reporting logic remains unchanged.

This is rarely used in practice, and is widely regarded as a last resort when monitoring SCDs. Only in certain cases where dependencies are structurally limiting is Type 3 SCD used.

In many, many years (30+) of building models I only used this once - in a Financial data warehouse. It is one of those situations where theory rarely becomes practice.

The best thing about designing is thinking through how the dimensions can be classified by information domain or within an information domain using a well-defined semantic glossary. This will help think-through where to add new requirements. The next thing towards physicalising the well classified semantic glossary is to consider dimensions, smaller dimensions called mini-dimensions. Either add new column, a new dimension, a new mini-dimension. Adding a column should not be too impactful. Where's the new column being added? To a fact? To an existing dimension? With a good design, using well classified terms (A set of knowledge semantically well-organised) augmentation with new requirements will be less painful.

Before implementing a slowly changing dimension model, it is crucial to carefully evaluate the data capture process, ensuring that all relevant attributes are captured accurately to maintain a comprehensive historical record. We can always rebuild a model but sometimes can't re-extract the data from source. Additionally, capturing timestamps within these extracts becomes paramount as it enables effective tracking and analysis of changes over time, enabling accurate SCD modelling based on source not load events.

As noted by others, the current low cost of storage and computing can enable hybrid and at times innovative solutions that provide great insights into valuable business questions. A practical example: understanding how an interaction with a customer changes over time, across various states, with data for time in status for multiple teams. Leveraging insights from types 2 and 3 can be merged to enable greater reporting flexibility and the added ability to answer questions from multiple teams.