The History Problem

Why Overwriting Destroys Information When a dimension attribute changes, the simplest approach is to overwrite the old value with the new one. Customer moved to Seattle? UPDATE customers SET city = 'Seattle'. Done. Simple. But now every fact row that referenced this customer while they lived in Portland shows 'Seattle.' Last quarter's revenue by region report just retroactively changed. Portland lost revenue it actually had. Seattle gained revenue it did not earn. This is the slowly changing dimension problem. Dimension data changes over time (customer addresses, product prices, employee titles), and how you handle those changes determines whether your historical reports stay accurate. The Q1 revenue was earned while the customer lived in Portland. After the overwrite, it appears under Sea