Immutable historical records, slowly changing dimension

In September 2012, PacifiCorp filed revised tariff language with FERC as part of a rate case that had been settled with an effective date of December 25, 2011, which was nine months earlier than the filing. Then, in June 2013, the company filed again, this time to incorporate a separate settlement agreement, also dated back to the same date, December 25, 2011.

A later compliance filing found that the two updates hadn't fully reconciled. Some tariff sections still reflected only the September 2012 language, not the terms from the June 2013 settlement, even though both claimed the same effective date. PacifiCorp had to go back and refile, submitting corrected versions and explicitly stating which set of sheets applied for which stretch of time. It was possible to sort this out because FERC's tariff-filing system never throws an old version away.

FERC's eTariff system works by treating every tariff provision as a stack of dated versions instead of a value that gets updated in place. Each revision a utility files becomes its own tariff record, carrying a proposed effective date, and when a newer version takes over, the older one is marked superseded, or, in FERC's own terminology, OBE or overtaken by events.

When two versions end up claiming the same effective date, as happened in the PacifiCorp case, the system uses something it calls a Record Effective Priority Order to decide which one governs. The result is that a tariff provision that's been revised a dozen times over a decade still has all twelve versions sitting in the system, each stamped with the window it applied to.

Type 1 vs Type 2 slowly changing dimension It’s important to avoid overwriting historical data when it gets corrected. Ralph Kimball's data warehousing framework, which is the standard reference most data engineers learn from, calls a record whose attributes change over time a slowly changing dimension, and it lays out a few ways to handle the change.

The simplest, Type 1, just overwrites the old value with the new one and keeps no history. It's fine for correcting a typo, but Oracle's own technical documentation on the pattern warns plainly that once you overwrite a value this way, "you may find that some of your reports that depended on the value will not return the same information as before". It’s a polite way of saying your past reports quietly become wrong the moment someone runs them again.

Type 2 is the alternative, in which you insert a new row with its own effective and expiration dates and a flag marking which version is current, and the old row stays exactly as it was. It takes more storage and is a more convoluted implementation, but it lets you find out "what was true on this date" and get an answer.

Geographic boundaries make the stakes higher because unlike a rate sheet, a boundary can look identical and still not be the same shape twice. The Census Bureau's TIGER/Line files, which define the county, tract, and block boundaries used in nearly all U.S. demographic data, are revised most years, and through projects like the University of Minnesota's National Historical Geographic Information System, the Census Bureau publishes each year's boundaries as its own dated "vintage" rather than folding changes into one continuously updated file.

Keeping the old row is an acknowledgment that the old row was never wrong to begin with. It was correct for its own window of time, and the only mistake available is forgetting that a window existed.