← All posts
Data ModelingRegulatory ReportingFintechData Engineering

Type 2 Slowly Changing Dimensions: A Fintech Merchant Example

March 16, 2026·8 min read

Imagine you process payments for a merchant called Acme Ltd. In January they are registered in Ireland. In March they move their legal entity to the United Kingdom. In April a regulator asks for a report of all Irish-domiciled merchant transactions from Q1.

If your data model treats merchant attributes as a single mutable row and you just updated the country field when they moved, you cannot answer that question correctly. The merchant now looks like a UK entity, and all their January transactions look like UK transactions too.

This is the problem Slowly Changing Dimensions Type 2 (SCD Type 2) was designed to solve.

What SCD Type 2 Does

Instead of overwriting a record when something changes, you close out the old version and insert a new one. Each row gets an effective date range that says when that version was valid.

The result looks like this:

select * from dim_merchants order by merchant_id, effective_from;

-- merchant_id | country | effective_from | effective_to | is_current
-- M001        | IE      | 2026-01-01     | 2026-03-14   | false
-- M001        | GB      | 2026-03-15     | 9999-12-31   | true

Two rows for the same merchant. The first captures their Irish registration through March 14th. The second captures their UK registration from March 15th onward. The date 9999-12-31 is a conventional placeholder meaning "still current."

Why This Matters for Regulatory Reporting

In fintech, regulatory reports often depend on what a merchant looked like at the time of a transaction. The Central Bank of Ireland wants payment volume for Irish-regulated merchants. The FCA wants the same for UK merchants. If a merchant moves jurisdictions mid-year, your reports need to reflect which regulatory regime covered each transaction.

The key is a point-in-time join:

select
  t.transaction_id,
  t.amount,
  t.created_at,
  m.country as merchant_country_at_time
from transactions t
join dim_merchants m
  on  t.merchant_id  = m.merchant_id
  and t.created_at  >= m.effective_from
  and t.created_at   < m.effective_to;

The join is not just on merchant_id. It also checks that the transaction date falls inside the version's validity window. Every transaction is attributed to what the merchant was at that moment, not what they are today.

Run the Q1 Irish merchant report and Acme Ltd's January transactions correctly appear under Ireland. Run the Q2 UK report and everything from March 15th onward correctly appears under the UK.

Building the Merge

Querying the dimension is straightforward. The harder part is keeping it current as changes come in from upstream systems.

The pattern is: find what changed, expire the old version, insert the new one.

-- Step 1: find merchants whose tracked attributes changed
with changed as (
  select
    s.merchant_id,
    s.country,
    s.legal_name,
    current_date as effective_from
  from staging_merchants s
  join dim_merchants d
    on  s.merchant_id = d.merchant_id
    and d.is_current  = true
  where s.country    <> d.country
     or s.legal_name <> d.legal_name
),

-- Step 2: expire the old current row
expired as (
  update dim_merchants
  set
    effective_to = c.effective_from,
    is_current   = false
  from changed c
  where dim_merchants.merchant_id = c.merchant_id
    and dim_merchants.is_current  = true
  returning dim_merchants.merchant_id
)

-- Step 3: insert the new current row
insert into dim_merchants
  (merchant_id, country, legal_name, effective_from, effective_to, is_current)
select
  merchant_id,
  country,
  legal_name,
  effective_from,
  '9999-12-31',
  true
from changed;

For brand new merchants that are not in the dimension yet:

insert into dim_merchants
  (merchant_id, country, legal_name, effective_from, effective_to, is_current)
select
  s.merchant_id,
  s.country,
  s.legal_name,
  current_date,
  '9999-12-31',
  true
from staging_merchants s
left join dim_merchants d on s.merchant_id = d.merchant_id
where d.merchant_id is null;

Which Attributes to Version

Not everything needs SCD Type 2. The question to ask is: does a historical report need to know what this field was at the time of the event?

Attributes worth versioning in a fintech context:

  • Country or jurisdiction: directly determines which regulator owns a transaction
  • Legal entity name: matters for audit trails and dispute resolution
  • Merchant category code: affects fee calculations and reporting buckets
  • Risk tier: relevant for compliance and risk reporting

Attributes you probably do not need to version:

  • Internal metadata like created_at or updated_by
  • Contact details that do not affect reporting logic
  • Display names with no compliance significance

Over-versioning adds storage cost and makes queries harder to write. Under-versioning loses history you will need later. The right scope comes from your reporting and compliance requirements.

Watch Out for Off-By-One on Dates

A common mistake is getting the boundary wrong. If a merchant's country changes on March 15th, transactions on exactly March 15th should use the new country.

The safest convention is effective_from inclusive and effective_to exclusive:

  • Set effective_to on the old row equal to the new row's effective_from (not minus one day)
  • Join using >= effective_from and < effective_to

This creates clean non-overlapping ranges with no gaps and no ambiguity at the boundary.

-- Old row: effective_from = 2026-01-01, effective_to = 2026-03-15
-- New row: effective_from = 2026-03-15, effective_to = 9999-12-31

-- Transaction on 2026-03-15 joins to the new row. Correct.
and t.created_at >= m.effective_from
and t.created_at  < m.effective_to

Why It Is Worth the Complexity

SCD Type 2 adds real overhead. The merge logic is more involved. Queries need the date range condition. New analysts will be confused by multiple rows per merchant until they understand the pattern.

But in a regulated environment, the alternative is worse. You cannot tell a regulator "we cannot reproduce that Q1 report because we overwrote the merchant's country." Auditors expect point-in-time accuracy on the data that drove financial reporting.

Build the model right once and your reporting stays defensible as things change. Merchants move, businesses restructure, regulations evolve. The data should reflect what was true at the time, not just what is true now.