Skip to content
Tutorials Center
My learning

Topics

Lesson 82 of 125 ยท Designing Databases

Versioning, Audit Tables and Slowly Changing Dimensions

Keep the history of changing data in SQL. Learn Type 1, 2 and 3 slowly changing dimensions, valid-from and valid-to rows, as-of queries and audit triggers.

An UPDATE is destructive: the old value is gone the moment the new one is written. That is fine for a typo, but wrong for facts that really changed. A customer moves from Lisbon to Madrid. A product's price goes up. An employee changes department. If you only keep the latest value, you can no longer answer questions such as "How much did we sell in Lisbon last year?" (every old order now looks like it was placed in Madrid) or "What did this product cost on the day of that order?"

The solution is to store history. This lesson covers the two common ways to do it. The first is the slowly changing dimension (SCD) used in analytics and data warehouses: slowly changing attributes get one row per version, with a validity period. The second is an audit (history) table that logs every change, often filled by a trigger. Both rest on the same idea, and both will make your self joins and range comparisons work harder.

Three ways to handle a change

A dimension is a descriptive table, such as customers or products, that facts (orders, sales) refer to. "Slowly changing" means its attributes change occasionally. The data-warehouse literature names a few standard strategies:

Type What it does History kept?
Type 1 Overwrite the old value None
Type 2 Close the old row, insert a new row Full
Type 3 Keep the previous value in an extra column Only one step back

Type 1 is a plain UPDATE: use it for corrections, where the old value was simply wrong. Type 3 adds a column such as previous_city; it is cheap but remembers only one change. Type 2 is the workhorse, and the rest of this lesson focuses on it.

Type 2: one row per version

In a Type 2 dimension, a customer is not one row but a sequence of rows, one per version. Each row records the period when it was true with valid_from and valid_to, and each version has its own surrogate key (sk), because the business key (customer_id) now repeats:

flowchart LR
  V1["Version 1<br/>sk 1: Ana in Lisbon<br/>2023-01-01 to 2024-06-01"] --> V2["Version 2<br/>sk 2: Ana in Madrid<br/>2024-06-01 to 9999-12-31"]
  B["customer_id = 1<br/>(business key)"] -.-> V1
  B -.-> V2

One customer, two versions. The business key stays the same; each version gets a surrogate key and a validity period.

Use half-open intervals: valid_from is included and valid_to is excluded, so a version ends exactly when the next one starts and no day belongs to two versions. For the current row, many designs use a far-future date such as 9999-12-31 instead of NULL, because it lets you write date BETWEEN ... and < comparisons without special cases. An is_current flag makes "give me today's customers" trivial.

SQL A Type 2 customer dimension

Press Run (or Ctrl + Enter) to see the result here.

The as-of query (the second one) is the heart of temporal data. For any date there is exactly one version per customer whose half-open interval contains it. On March 1, 2024 Ana was still in Lisbon, and Cleo was in Rome; Ben never moved. Because periods never overlap, the result has one row per customer, as long as the data is maintained correctly.

Recording a change

When a customer moves, you don't UPDATE the city. You perform two steps that must succeed or fail together, so put them in one transaction: close the current row by setting its valid_to to the change date, and insert the new version starting that same day.

flowchart TD
  A["Change detected<br/>Ben moves to Bergen on 2025-09-01"] --> B["BEGIN"]
  B --> C["UPDATE current row:<br/>valid_to = 2025-09-01, is_current = 0"]
  C --> D["INSERT new row:<br/>valid_from = 2025-09-01, is_current = 1"]
  D --> E["COMMIT"]

Closing the old version and opening the new one in a single transaction keeps the history gap-free.

SQL Close the old version, insert the new one

Press Run (or Ctrl + Enter) to see the result here.

Ben's history now has two rows that meet exactly at 2025-09-01. A real loading job (often called an ETL or ELT job) does this for many customers at once: it compares the incoming data with the current rows, and for each changed customer runs the same close-and-insert pair. Only compare the attributes you want to track; if a harmless column like phone changes, you can overwrite it (Type 1) without creating a new version.

Add a unique index on (customer_id) where is_current = 1 if your database supports partial indexes (PostgreSQL and SQLite do). It guarantees at most one current row per customer. Preventing overlapping periods is harder: PostgreSQL offers exclusion constraints on date ranges, while other systems rely on careful loading code and periodic validation queries.

Joining facts to the right version

Now the payoff. A fact table such as orders stores the business key and the date. To report with the attributes as they were when the order happened, join on the key and require the order date to fall inside the version's period:

SQL Revenue by the city at order time

Press Run (or Ctrl + Enter) to see the result here.

The two reports disagree, and both are legitimate. Revenue "at the time" credits Lisbon with Ana's early order of 100, Madrid with her two later orders (140), and Rome with Cleo's November order. Revenue "by current city" puts all of Ana's 240 in Madrid and all of Cleo's 160 in Milan, as if they had always lived there. A Type 2 dimension lets you choose per question, while a Type 1 table can only give the second answer.

For large fact tables, many warehouses look up the surrogate key during loading and store it in the fact row (customer_sk). The join then needs no date range at all: JOIN dim_customer USING (customer_sk). The price is a more complex load, but queries become simple and fast. This is also where the star schema thinking pays off.

Audit tables and triggers

The second form of history is an audit table: it logs what changed, by whom and when, for every UPDATE or DELETE. It's the right tool for operational tables where you want accountability, not analysis: "who changed this price, and what was it before?" A trigger fills the log automatically, so no application code can forget it:

SQL An audit trigger

Press Run (or Ctrl + Enter) to see the result here.

Three updates ran, but only two changed a price, so the log has two rows: 500 -> 550 and 550 -> 600. The WHEN OLD.price <> NEW.price clause keeps no-op updates out of the log. In a real system the audit row would also store the user (CURRENT_USER in most databases, or a value your application passes in) and the timestamp; here changed_at is filled by a default, which is why it is not printed.

Audit tables grow forever, so index them by the entity id and time, and archive old rows. Some databases ship this as a built-in feature, such as system-versioned temporal tables in SQL Server, MariaDB and the SQL standard, where the database maintains the history table and you query it with FOR SYSTEM_TIME AS OF. Use those when you have them; the concepts in this lesson are exactly what they automate.

Choosing an approach

  • Corrections of mistakes: Type 1, plain UPDATE.
  • Reporting on how things were: Type 2 dimension with validity periods.
  • Who changed what: audit table filled by a trigger or by built-in temporal tables.
  • Only the previous value matters: Type 3, an extra column.

Whatever you pick, decide early which attributes are tracked and which are overwritten, document the interval convention (half-open, far-future end date), and test the as-of query with dates on the exact boundaries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A customer moves city and you must be able to report old orders under the old city. Which strategy fits?

    Type 2 keeps one row per version with a validity period, so each order can be joined to the version that was valid on its date.
  2. 2. Why use half-open intervals (valid_from <= date < valid_to)?

    With a closed end date the boundary day would match two versions. With an exclusive end, a day belongs to exactly one version.
  3. 3. Why should closing the old row and inserting the new one be done in one transaction?

    If the UPDATE succeeded but the INSERT failed, the customer would have no current row. A transaction makes both steps succeed or fail together.
  4. 4. What does a Type 3 dimension store?

    Type 3 adds a column such as previous_city. It is simple but keeps only the last change.
  5. 5. What is the main job of an audit trigger?

    An AFTER UPDATE or AFTER DELETE trigger can write the old values to a log table, so no application code path can skip the audit.

Summary

  • UPDATE overwrites; to answer questions about the past, you must store history explicitly.
  • Type 1 overwrites, Type 2 adds a row per version with valid_from/valid_to, Type 3 keeps one previous value in a column.
  • Use half-open intervals and a far-future end date for the current row; add is_current for convenience.
  • To record a change, close the current row and insert the new one in a single transaction.
  • Join facts to dimension versions on the key plus order_date >= valid_from AND order_date < valid_to to report values as they were.
  • Audit tables, usually filled by triggers (or built-in temporal tables), record who changed what and when.

Next: Reading Query Plans, where you start looking at how the database actually runs your queries.