All topics
18 / 55
RPDadvanced

Slowly Changing Dimensions in the RPD

Explain how Type 1 and Type 2 slowly changing dimension patterns are modeled and reported on within the RPD's logical layer.

The warehouse decides how dimension history is kept. The RPD has to expose it correctly, and Type 2 is where the modelling becomes interesting.

It's like a passport with stamps for every address you've ever lived at (Type 2) versus a driver's license that just gets reprinted with your new address every time you move (Type 1) — one preserves your full history, the other only ever shows the present.

Key Concepts

1
Type 1 overwrites. One row per business entity, current values only. A customer moves region and the old region is gone.
2
The RPD needs nothing special — it is an ordinary dimension.
3
Type 2 keeps history.
    SK   CUST_ID  NAME   REGION  EFF_DATE    EXP_DATE    CURRENT
    501  C100     Asha   North   2020-01-01  2023-06-30  N
    847  C100     Asha   South   2023-07-01  9999-12-31  Y
4
One business entity, two rows. The surrogate key identifies a version; CUST_ID identifies the customer.
CUST_ID
5
Which creates the question the model must answer. A sale in 2021 to customer C100 — is it reported under North or South?
6
    "as was"   the region at the time of the sale -> North
    "as is"    the customer's current region      -> South
7
Both are legitimate and they are different questions. Finance usually wants "as was" for historical reporting; sales management often wants "as is" to see the current structure.
8
The RPD choice follows from the fact's foreign key.
    fact joins on the surrogate key (847, 501)
      -> "as was". The fact points at the version current when it
         happened.
    fact joins on the business key (C100), filtered to CURRENT = 'Y'
      -> "as is". Everything rolls up to today's attributes.
9
The warehouse decides which key the fact carries; the RPD's physical join implements it.
10
Exposing both means two logical dimensions over the same physical table — one joined by surrogate key, one by business key filtered to current — presented as "Customer" and "Customer (Current)".
11
That is the mature answer: the choice is not the modeller's to make once, because different reports genuinely need different answers.
12
The defect to watch for. A dimension with Type 2 rows and no filter on CURRENT in a lookup context returns a row per version, so a count of customers counts versions instead. It looks like duplicate data and it is the model not restricting the join.
CURRENT
13
What the interviewer is probing.1. "What does the RPD need to handle a type 2 dimension?" Probing: the modelling. Stalls: "Nothing." Moves up: the join to the fact on the surrogate key, not the natural key, so each fact row points at the version current when it occurred.
14
2. "What is the business question this answers?" Probing: the purpose. Stalls: "History." Moves up: whether sales are reported against the sales rep's region at the time of the sale, or their region today — both are valid and must be chosen.
15
3. "How do you offer both views?" Probing: the depth. Stalls: "You cannot." Moves up: a current-view alias of the dimension joined on the natural key alongside the historical one, exposed as separate presentation folders.
16
4. "What is the usual defect found in testing?" Probing: experience. Stalls: "None." *Moves up:* the join using the natural key, so every historical version matches and the numbers multiply.
Use cases:scddimensional-modelingdata-warehouse