Start with the historical question
Enterprise data platforms commonly model customers, products, locations, employees, and organizational units as dimensions. All of them change. A customer moves to another segment, a product is reassigned to a category, or an employee transfers to a different team. If the table stores only the current value, an order placed last year may suddenly appear under today's category or department. No row has been lost, yet the meaning of history has changed, and the same report can return a different answer next month.
Before choosing a storage pattern, define the required time perspective. A current-state view applies today's structure to all events. An event-time view shows the dimension values that were valid when each event occurred. A system-time view answers what the platform knew on a particular date. Management reporting often needs the first two; audit, correction, and late-arriving-data workflows may need the third. Building a sophisticated history table without agreeing on this distinction usually produces complexity without a trustworthy answer.
Version dimensions around effective periods
The standard event-time pattern is a Type 2 slowly changing dimension. Instead of updating an existing row, the pipeline closes the old version and inserts a new one. Each version receives its own surrogate key and carries the business key, effective-from timestamp, effective-to timestamp, and a current-version indicator. The fact table must reference the surrogate key so that the association is frozen at load time. Joining facts only by customer number or product code can accidentally attach every historical transaction to the latest dimension row.
- Choose tracked attributes deliberately: version fields such as department, product category, or customer segment when they change analytical meaning. A spelling correction may not deserve a new version.
- Use one boundary convention: half-open intervals, where the start is inclusive and the end is exclusive, make adjacent versions easier to validate and avoid midnight ambiguity.
- Prevent overlaps: a business key should resolve to no more than one version at any event time. Enforce this in loading tests even if the database cannot express the constraint directly.
- Provide an unknown member: when a fact arrives before its dimension, connect it to an explicit unknown version rather than leaving the key null or selecting the newest row.
Not every attribute should be Type 2. If reports only need the latest phone number or contact address, overwriting that value is usually appropriate. A limited previous value can sometimes be stored in the same row when only one transition matters. Many practical models use a hybrid approach: version the small set of attributes that affect analysis and overwrite the rest. This controls growth, simplifies the semantic layer, and reduces the chance that users select an obsolete field by mistake.
Handle late arrivals, corrections, and two clocks
Routine daily changes are the easy case. The difficult cases are late arrivals and retroactive corrections. An organizational transfer might reach the warehouse at month end even though it became effective in the middle of the month. Starting the new version on the load date would be wrong. The pipeline may need to split an existing interval at the business-effective timestamp and reassign facts within the corrected period. Before doing so, it should detect overlapping source records, conflicting changes with the same effective time, and sources that provide only dates while downstream events use timestamps.
If the platform must also explain when it learned about a change, one effective period is insufficient. A bitemporal model stores business-valid time, describing when the value was true in the real world, and system-recorded time, describing when the platform received or corrected it. This supports audit trails and reproduction of previously published outputs, but it makes loading, querying, and governance harder. Do not introduce bitemporal logic everywhere by default. It is most defensible for contracts, pricing, access rights, regulated records, or other data where knowledge time changes the answer.
Make the history safe to query
A correct physical model can still be used incorrectly. Publish clearly named views or semantic models for current customer, customer at transaction time, and customer as of a selected date. Analysts should not have to recreate interval joins in every dashboard. Fact loading should resolve the dimension version using the event timestamp and record exceptions when no version is found, multiple versions match, or the unknown member is used. Reprocessing must also be idempotent so that a retry does not create another copy of the same version.
Validation should go beyond row counts. Test that periods for each business key do not overlap, that only one current version exists, and that every resolved fact timestamp falls inside its dimension version. Use scenario tests covering an ordinary change, a late dimension, a retroactive correction, and a rerun of the same input. After a correction, verify explicitly whether an already published report should remain reproducible or be restated. Both policies can be valid, but they should not be accidental.
Finally, document timestamp precision, time zone, source precedence, and correction rules in the data contract. ERP, CRM, LINE services, cloud applications, and IoT platforms often represent time differently; those differences must be reconciled before warehouse loading. Also label outputs as original history, corrected history, or history recast under current classifications. When several systems contribute to the same dimension, an integration team can help establish these rules, but the business owner must still decide which historical interpretation is authoritative.