What are slowly changing dimensions (SCD)?

Slowly changing dimensions (SCD) are a modeling technique for tracking how a dimension's attributes change over time, a customer's address, a product's category, an employee's title, without losing the history of what the value used to be. The most common form, Type 2, keeps every version of a row instead of overwriting it.

The SCD types

• Type 0: The value never changes once set. No handling needed.
• Type 1: The old value is overwritten. Simple, but history is gone: you can't tell what the value used to be.
• Type 2: A new row is added for each change, with effective-from and effective-to dates (or a current-row flag). The old row stays exactly as it was.
• Type 3: A limited history, usually just the previous value, stored in an extra column alongside the current one.

Why it matters

Reports need to reflect the world as it was at the time, not as it is now. If a customer moved regions in March and a sale happened in February, that sale should still show the old region in a report built with SCD Type 2. Overwrite the dimension (Type 1) and the February sale silently gets reattributed to the new region the next time the report runs.

How OptimaFlo handles this today

Ready-layer (gold) tables in OptimaFlo are built from SQL, generated by the Analytics Engineer role and reviewed and approved by you, on top of the Clean layer. That SQL can implement any of the SCD types, including Type 2 versioning with effective dates, if you write or ask for it that way. What OptimaFlo does not do is detect a changing dimension and version it automatically: there's no built-in SCD Type 2 engine that watches a source column and starts appending history rows on its own. If you need SCD, it's a pattern you build into your Gold SQL, the same way you'd build it in a hand-written warehouse model.

Related terms

• Star schema: The modeling pattern SCD tables usually live inside, as dimension tables joined to a fact table.
• Medallion architecture: The Raw, Clean, and Ready layers where an SCD table would sit, built as part of the Ready layer.