Mirroring, versioned history and SCD type 2: which one per table

Cover image

Three table rows stacked, the same entity in different versions, the first two dimmed and the last one highlighted.

Someone fixes a customer record on a Monday morning. Changes the city, changes the segment, saves it and goes for coffee. Nobody mentions it again.

On Friday, the sales manager opens last year's revenue-by-region report and finds a different number from the one they saw in January. No pipeline failed. No run threw an error. Nothing was deleted by mistake.

The problem is that the source table does not keep what used to be true. It keeps what is true now. And your pipeline copied that faithfully, right over the past.

In this article, we explain what each sync mode does to the destination table and what SCD type 2 means in terms of rows and columns. We also cover what it costs and how to choose table by table.

What mirroring does — and what it erases along the way

Mirroring means keeping a faithful copy of the source's current state in the destination. A new row is inserted, a changed row is updated, a row that disappeared is removed. At the end of every run, source and destination tell the same story.

For most tables that is exactly what you want. A cancelled order should disappear from the destination. A corrected address should show up corrected. Nobody opens the inventory dashboard wondering how many units were on the shelf back in July.

What database mirroring does not do is keep what was there before. It has nowhere to put it. Each destination row has room for one version, and the version that survives is always the last one. In practice, your historical report is rewritten every night, quietly.

The destination is a snapshot of the present.

SCD type 2 is a new row, not an update

Slowly changing dimension is the name Ralph Kimball gave, back in 1996, to the problem of a descriptive attribute changing over time. A customer moves city, a product moves category, a rep moves territory. These changes are rare, and it is precisely because they are rare that nobody notices the damage.

Type 2 solves it one way only: nothing is updated. Every change produces a new row, and the old row is closed with an end date. The table now holds several rows per entity, each valid for a window of time.

A customer who moved city and later changed loyalty tier looks like this:

Notice that one version's end is exactly the next one's start. The interval is closed at the beginning and open at the end, so no gap is left between rows and no date falls into two versions at once.

Three things move. The customer_id stops identifying the row and starts identifying the entity, repeated across every version. What identifies the row is the key plus the start of validity — or a version column created by the load, when the implementation generates one. And the sales table now points at the version, not at the customer.

The February 2025 sale points at the São Paulo version. It stays a São Paulo sale after the move, after the tier upgrade, after everything. That is what type 2 buys you: the past stops moving.

The implementations vary, the mechanism does not

You will find type 2 implemented in several shapes. The concept is one; what changes is the set of control columns.

• Active flag only: cheap and limited. You know which version applies today, and you do not know when each one applied. It cannot answer "how did this look on March 15?".
• Validity window only: more expressive. It forces a date comparison into every query.
• Window plus flag: the most common arrangement. The redundancy is deliberate, because filtering on the active flag is far cheaper than comparing two dates.
• Version number or surrogate key: a column of its own per row, useful for the fact table to point straight at a version and for auditing the load.
• Open end: null, or a sentinel date such as 9999-12-31. The sentinel spares you from handling nulls in every date comparison; null is more honest about what has not happened yet. If you also keep an active flag, you rarely feel the difference.
• Detection by comparison or by hash: the load compares column by column, or stores a hash of the tracked columns and compares only that. On a wide table, the hash wins.
• Tracked and untracked columns: not every change deserves a version. A mistyped phone number is a correction, not a business change.

Different names, same idea: close instead of overwrite.

The other types, one sentence each

Type 2 is the most used, not the only one. Kimball formalised the rest in the third edition of The Data Warehouse Toolkit, in 2013.

• Type 0: the original value never changes. Signup date, acquisition channel, tax ID.
• Type 1: plain overwrite. SCD type 1 is mirroring, and it is there to fix mistakes.
• Type 2: a new row per version, with a validity window.
• Type 3: one extra column holds the previous value. It keeps one step of history, not all of them.
• Type 4: history leaves the main table and moves into a separate one.
• Type 5: type 4 with a shortcut to the current profile in the main table.
• Type 6: a versioned row that also carries the current value, to answer both questions.
• Type 7: the fact table keeps two keys, and the query picks whether it wants the past or the present.

From type 4 onwards, the motivation is almost always the same: type 2 grew too large.

What type 2 costs

It solves one real problem and creates three.

The table grows. A dimension with a volatile attribute and a few million rows becomes a storage problem and a query problem. Worth measuring before you turn the mode on everywhere.

The wrong join multiplies everything. If someone joins sales to customers on customer_id alone, each sale becomes three. It is the most common mistake right after switching type 2 on, and it does not raise an error: it inflates the number and hands it to you looking correct.

Every "right now" query needs a filter. The dashboard that used to be a plain SELECT now needs the active-version filter. Forget it, and you count the customer three times.

The three queries below show the difference. The first one is the one that inflates:

-- wrong: each sale becomes one row per customer version
SELECT c.city, SUM(s.amount)
  FROM sales s
  JOIN customers c ON c.customer_id = s.customer_id
 GROUP BY c.city;

-- "as it stood back then": the sale date picks the version
SELECT c.city, SUM(s.amount)
  FROM sales s
  JOIN customers c ON c.customer_id = s.customer_id
                  AND s.sale_date >= c.valid_from
                  AND (s.sale_date < c.valid_to OR c.valid_to IS NULL)
 GROUP BY c.city;

-- "as it stands today": filter the active version
SELECT c.city, SUM(s.amount)
  FROM sales s
  JOIN customers c ON c.customer_id = s.customer_id
                  AND c.active
 GROUP BY c.city;

These are different questions, with different and equally legitimate answers. Type 2 does not choose for you — it only makes both possible.

How to decide, table by table

The decision does not belong to the connection or to the pipeline. It belongs to each table, and sometimes to each column.

Type 2 earns its cost when the attribute shows up in a historical report, when someone compares periods by it, or when you are required to show what applied on a given date. Customer records, product tables, sales hierarchies, price lists.

Stay with mirroring when nobody cares about the attribute's past, when the table is large and volatile, or when the data is already an event with a date of its own. Orders, stock movements, access logs.

There is no better or worse mode. There is the mode your team's question demands — and most destinations end up with tables in both.

How this looks in Januss

In Januss the mode is chosen table by table, within the same source. One table on full load, another on mirroring, another on history, all in the same pipeline. History mode is SCD type 2.

Once a table is set to history, Januss creates the destination with four control columns alongside yours:

_januss_valid_from  TIMESTAMP  NOT NULL   -- when this version became active
_januss_valid_to    TIMESTAMP  NULL       -- null while the version is open
_januss_active      BOOLEAN    NOT NULL   -- the current version of the key
_januss_synced_at   TIMESTAMP  NOT NULL   -- when this row was written

The destination's primary key becomes your key plus _januss_valid_from — the pair that identifies a version, exactly as described above. Those names are the defaults: if _januss_ clashes with your destination's convention, all five system column names are set on your account and apply to every pipeline.

On the next load, the engine compares the active version against the row that arrived from the source. Identical, and nothing is written — a redelivered message never produces a duplicate version. Different, and it closes the active version and inserts the new one in the same transaction: the closed row's _januss_valid_to takes the same instant as the new row's _januss_valid_from. The chaining comes from the commit, not from the luck of the clock.

A row that disappears from the source is not deleted either. Its active version is closed and the entity is left with no open version — its past stays queryable.

Two limits worth knowing before you switch the mode on. The comparison covers all of your columns: you cannot pick which ones trigger a new version, so a mistyped phone number produces a version like any other change. And when the same record changes several times inside one capture batch, Januss stores the batch's final image, not each intermediate step. Fidelity is per run, not per event.

Want to see which mode each of your tables is asking for? It is a 14-day trial, no credit card: create your workspace.

Sources

The slowly changing dimension types and their naming follow the Kimball Group:

• Slowly Changing Dimensions, Part 2 — Kimball Group
• Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7 — Kimball Group