Sooner or later, someone asks: "What did this customer's plan look like when they signed up last February?" The current row won't tell you that; it's been edited three times since. This is the classic historical/temporal data problem: how do you manage changes to dimensional data over time so you can answer "row as of T" on demand. This article is about the schema pattern that answers that question in one SELECT.
There are several approaches to modeling and solving this problem, and I'll discuss some of those approaches below:
a) Event-driven/event sourcing. This has to do with when the domain naturally consists of discrete meaningful facts, e.g., sensor readings, prescriptions, stock movements, financial transactions. Here the change of records events is the natural state and the database is designed for recording these events. It is a Bad fit when the domain thinks in records (customers, products, contracts) whose changes don't have natural event semantics.
b) Versioning in place (this is what SCD Type 2 refers to, if you want to look it up — SCD explainer). The database table holds every version of every record with valid_from / valid_to denoting the change time; the domain still thinks in records. Getting the records state at a certain point in time requires filtering for versions in the valid from/to window. Great when versions have domain meaning. Bad fit when the app is already built; adding versioning in place means every existing query, view, and join needs a WHERE valid_to IS NULL filter. In greenfield the choice between this and a sibling history table (SCD Type 4) is close, and comes down to whether "one table with a filter convention" or "current and history split" fits the team's mental model.
