08.6 SCD Type 2 — Historical Dimensions

1. What SCD Type 2 solves

SCD Type 2 preserves the history of an entity's descriptive attributes.

Instead of overwriting the existing row, a change closes the previous version and creates a new version.

SCD1 asks: “What is true now?” SCD2 asks: “What was true at a particular point in time?”

Databricks AUTO CDC supports SCD Type 2 and records the active interval using __START_AT and __END_AT. citeturn272126search0turn272126search1

2. Simple example

Customer C100 changes city:

customer_id	city	start	end	current
C100	Delhi	10	20	No
C100	Bengaluru	20	NULL	Yes

The old row remains queryable.

This enables questions such as:

• Which city did the customer belong to when an invoice was posted?
• Which sales territory was assigned during a historical period?
• Which product category was active on a specific date?
• How did a customer's classification change over time?

3. SCD1 vs SCD2

Requirement	SCD1	SCD2
Current state	Yes	Yes
Historical versions	No	Yes
Point-in-time reporting	No	Yes
Audit reconstruction	Limited	Strong
Storage	Lower	Higher
Query complexity	Lower	Higher
Typical use	Current master data	Historical dimensions

The choice is a business requirement, not simply a technical preference.

4. Architecture

Source
  |
CDC / snapshot / incremental
  |
  v
Bronze
  |
ordered change events
  |
  v
SCD2 processing
  |
  +--> close previous version
  +--> create new version
  |
  v
Silver Historical Dimension
  |
  +--> current-state queries
  +--> point-in-time analytics
  +--> audit / compliance

The Bronze layer should retain enough evidence to replay and investigate source changes.

5. Business key vs surrogate key

The business key identifies the logical entity.

Example:

customer_id = C100

A warehouse can additionally use a surrogate key:

customer_sk	customer_id	city	start	end
90001	C100	Delhi	10	20
90157	C100	Bengaluru	20	NULL

The business key stays constant while each historical version gets a distinct warehouse identity.

Do not assume that every Databricks SCD2 design requires a generated surrogate key. It is useful when facts and dimensions need stable version-level joins, but the natural business key plus effective interval can also be sufficient.

6. The core SCD2 interval model

Every version represents an interval:

[ __START_AT, __END_AT )

The start is inclusive and the end represents when that version stops being active.

The current version normally has:

__END_AT = NULL

This is much more powerful than a simple is_current flag because the interval supports point-in-time predicates.

7. Why sequencing matters

Suppose the source emits:

key	city	sequence
C100	Delhi	10
C100	Mumbai	20
C100	Bengaluru	30

The resulting history should be:

key	city	start	end
C100	Delhi	10	20
C100	Mumbai	20	30
C100	Bengaluru	30	NULL

Physical arrival order is not necessarily logical event order.

AUTO CDC uses the sequencing column to process out-of-order records. Databricks requires the sequencing value to represent the correct monotonically increasing event order. citeturn272126search0

8. Modern Databricks approach — AUTO CDC

For new Lakeflow pipeline designs, use AUTO CDC rather than starting with the legacy APPLY CHANGES terminology. Databricks states that AUTO CDC replaces the APPLY CHANGES APIs while retaining the same basic syntax. citeturn272126search1

SQL

CREATE OR REFRESH STREAMING TABLE customer_history;

CREATE FLOW customer_history_flow
AS AUTO CDC INTO customer_history
FROM STREAM customer_changes
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY sequence_num
COLUMNS * EXCEPT (operation, sequence_num)
STORED AS SCD TYPE 2;

The resulting target maintains version metadata.

Python

from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr

@dp.view
def customer_changes():
    return spark.readStream.table("bronze.customer_changes")

dp.create_streaming_table("customer_history")

dp.create_auto_cdc_flow(
    target="customer_history",
    source="customer_changes",
    keys=["customer_id"],
    sequence_by=col("sequence_num"),
    apply_as_deletes=expr("operation = 'DELETE'"),
    except_column_list=["operation", "sequence_num"],
    stored_as_scd_type=2
)

Databricks' current documentation shows this pattern with __START_AT and __END_AT generated for the SCD2 target. citeturn272126search1

9. What happens on an update

Initial:

customer_id	city	start	end
C100	Delhi	10	NULL

Update arrives:

C100 -> Bengaluru
sequence = 20

SCD2 produces:

customer_id	city	start	end
C100	Delhi	10	20
C100	Bengaluru	20	NULL

The old row is not destroyed.

10. What happens on a delete

A delete is a business event.

For a Type 2 history, the existing active version is closed according to the CDC semantics. The historical record remains available.

That is fundamentally different from simply deleting the row.

Ask:

“Do we need to remember that this entity existed and when it stopped being active?”

If yes, SCD2 deletion semantics are usually appropriate.

11. Current-row semantics

There are several ways to identify the current version.

End-date approach

__END_AT IS NULL

Explicit flag

A derived is_current column can be exposed for BI convenience.

Do not rely only on a flag when historical interval logic matters. The start/end interval is the stronger source of truth.

12. Point-in-time queries

Suppose an invoice was posted at sequence/time T.

The correct dimension version satisfies:

SELECT *
FROM customer_history
WHERE customer_id = 'C100'
  AND valid_time >= start_time
  AND (
      end_time IS NULL
      OR valid_time < end_time
  );

In a production model, use the actual __START_AT/__END_AT semantics or mapped business-time columns appropriate to the source.

The key idea is:

Find the dimension version whose validity interval contains the fact's event time.

13. SCD2 and fact joins

For a fact table with event time:

fact.customer_id = dimension.customer_id
AND fact.event_time >= dimension.valid_from
AND (
    dimension.valid_to IS NULL
    OR fact.event_time < dimension.valid_to
)

This is the classic temporal dimension join.

Be careful: range joins can become expensive at scale. Optimize the physical design and query pattern rather than assuming a normal equality join will behave the same way.

14. Track only important history

By default, SCD Type 2 can create a new version whenever a tracked value changes.

Databricks AUTO CDC allows a subset of columns to be tracked for history. Changes to untracked columns can update the current version without creating a new historical record. citeturn272126search0turn272126search1

Example:

dp.create_auto_cdc_flow(
    target="customer_history",
    source="customer_changes",
    keys=["customer_id"],
    sequence_by=col("sequence_num"),
    stored_as_scd_type=2,
    track_history_except_column_list=["city"]
)

This is an important enterprise optimization.

If a dimension has 200 columns but only 12 are historically meaningful, creating a new version for every operational metadata change can produce unnecessary history.

15. Full history vs selective history

Full history

Every relevant attribute change creates a new version.

Good for:

• regulatory dimensions;
• highly audited master data;
• complete historical reconstruction.

Selective history

Only business-critical columns create versions.

Good for:

• very wide dimensions;
• frequently changing technical attributes;
• reducing storage and version explosion.

The business must explicitly classify which attributes require historical semantics.

16. Late-arriving changes

Consider:

sequence	city
30	Bengaluru
20	Mumbai

If event 20 arrives after event 30, the target must still represent the correct logical history.

This is why sequence design is more important than ingestion timestamp.

A robust architecture defines:

1. source event ordering;
2. replay policy;
3. late-event expectations;
4. correction strategy;
5. downstream consistency guarantees.

17. Initial hydration

A common production migration is:

Existing source table
       |
       v
Initial historical load
       |
       v
SCD2 target
       |
       +----> ongoing CDC

Databricks documents using a once flow for initial hydration before ongoing CDC processing. citeturn272126search0

Do not treat initial load and ongoing CDC as two unrelated data models. They should produce the same target semantics.

18. Snapshot-based SCD2

When the source has no CDC feed, AUTO CDC FROM SNAPSHOT can compare ordered snapshots and derive changes.

Databricks documents this API for snapshot-based ingestion and supports SCD Type 1 or Type 2 processing. citeturn272126search5

Architecture:

Snapshot 1 --->+
               |
Snapshot 2 --->+--> change detection --> SCD2
               |
Snapshot 3 --->+

Snapshots must be processed in ascending version order.

19. SCD2 with Lakeflow Connect

Managed Lakeflow Connect ingestion can also expose history-tracking modes. SCD_TYPE_1 overwrites outdated records, while SCD_TYPE_2 preserves historical changes. citeturn272126search3turn272126search8

This gives architects an important choice:

Pattern	Best fit
AUTO CDC	Existing CDC event stream
AUTO CDC FROM SNAPSHOT	Periodic source snapshots
Lakeflow Connect SCD2	Managed connector ingestion
Custom MERGE	Specialized transformation/control requirements

Use the highest-level managed abstraction that still satisfies the requirements.

20. SCD2 in SAP landscapes

Typical pattern:

SAP S/4HANA
      |
      v
CDC / replication
      |
      v
Bronze change feed
      |
      v
SCD2 customer / supplier / material
      |
      +--> Finance
      +--> Procurement
      +--> Embedded analytics
      +--> Data products

Examples:

• customer sales-area assignment;
• supplier purchasing organization;
• material classification;
• cost-center ownership;
• employee organizational assignment.

The business question determines whether each attribute belongs in Type 1 or Type 2 history.

21. Effective date vs system sequence

Do not automatically equate source sequence with business-effective time.

Example:

• Business change effective: January 1
• Source transaction captured: January 5
• Databricks processes it: January 6

These are different timelines.

SCD2 implementations must distinguish between:

• business/effective time;
• source event ordering;
• ingestion/system time.

For simple dimensional history, one timeline may be enough. For complex corrections and audit requirements, a bitemporal model may be more appropriate. Databricks documents bitemporal AUTO CDC as a Beta capability. citeturn272126search9

22. Data quality invariants

Production SCD2 should enforce:

• one current version per business key;
• no overlapping validity intervals;
• deterministic sequencing;
• non-null business keys;
• valid start/end relationships;
• expected delete behavior;
• source-to-target reconciliation.

A conceptual invariant is:

for each key:
    exactly one row has __END_AT = NULL

For temporal correctness, adjacent versions should form a coherent timeline.

23. Version explosion

SCD2 can grow much faster than SCD1.

Suppose:

• 100 million entities;
• 4 history-producing changes per entity per year.

That can create hundreds of millions of historical versions over time.

Before implementing SCD2, estimate:

annual_versions
= entities
  × average_history_changes_per_entity

Then estimate storage, query workload, retention, and maintenance.

24. Performance engineering

Key considerations:

Reduce unnecessary versions

Track only attributes that genuinely require history.

Keep the table layout healthy

Use modern Delta layout techniques appropriate to the workload. Avoid blindly partitioning on high-cardinality keys.

Optimize point-in-time access

Understand whether consumers filter by business key, date, domain, or combinations of these.

Separate current and historical workloads when justified

A very large historical dimension can serve a dedicated history workload while a compact current-state projection serves high-concurrency BI.

Benchmark temporal joins

A point-in-time join can be substantially more expensive than an equality join.

25. Current-state projection pattern

A useful enterprise architecture is:

SCD2 History
     |
     +----> Current-state view/table
     |
     +----> Point-in-time analytics
     |
     +----> Audit / compliance

The current-state projection can expose only active records.

This prevents every BI user from repeatedly scanning the entire historical dimension when they only need current values.

26. Lab 1 — Build SCD2 with AUTO CDC

Create a CDC stream:

C100 | Delhi     | 10
C100 | Mumbai    | 20
C100 | Bengaluru | 30

Build an SCD2 target.

Verify:

Delhi     -> 10 to 20
Mumbai    -> 20 to 30
Bengaluru -> 30 to NULL

27. Lab 2 — Point-in-time reporting

Create:

• customer history;
• sales facts;
• invoice timestamp.

Write a query that returns the customer attribute that was valid when each invoice occurred.

Test invoices before, during, and after a customer attribute change.

28. Lab 3 — Selective history

Create a dimension with:

• customer_name;
• city;
• email;
• technical_update_timestamp.

Make only customer_name and email historical.

Then change city repeatedly and verify that city does not create unnecessary historical versions.

29. Lab 4 — Enterprise SCD2

Design a 300-million-row customer history with:

• 5 million daily source changes;
• late events;
• deletes;
• 20 historical attributes;
• 5 non-historical attributes;
• 15-minute freshness SLA;
• high BI concurrency.

Document:

1. business keys;
2. sequencing;
3. tracked columns;
4. initial hydration;
5. CDC processing;
6. temporal joins;
7. current-state projection;
8. storage/layout strategy;
9. monitoring;
10. replay/recovery.

30. Architect decision tree

Does the business need history?
          |
      +---+---+
      |       |
     NO      YES
      |       |
     SCD1   Does every change matter?
              |
          +---+---+
          |       |
         YES      NO
          |       |
        SCD2   Selective SCD2
                  |
          Are two timelines needed?
                  |
             +----+----+
             |         |
            NO        YES
             |         |
           SCD2    Bitemporal model

Do not introduce bitemporal complexity unless the business actually needs both business-time and system-time semantics.

31. Production checklist

• Business key defined.
• Historical attributes explicitly classified.
• Sequence column represents source event order.
• Initial hydration reconciled.
• CDC replay tested.
• Late-event behavior tested.
• Delete semantics documented.
• Current-row semantics defined.
• No overlapping versions.
• Exactly one active version per key.
• Point-in-time joins tested.
• Version-growth estimate completed.
• Selective history considered.
• Current-state projection considered.
• Data retention strategy defined.
• Unity Catalog permissions defined.
• Lineage and audit monitoring implemented.
• Recovery and full refresh tested.

32. Architect mental model

             ONE BUSINESS ENTITY
                     |
                     v
              ORDERED EVENTS
                     |
       +-------------+-------------+
       |             |             |
      V1            V2            V3
       |             |             |
     START         START         START
       |             |             |
      END           END           NULL
       |             |             |
       +-------------+-------------+
                     |
                     v
            COMPLETE HISTORY

SCD Type 2 is a temporal data contract, not just a table with an end-date column.

The architect must define identity, event ordering, historical attributes, effective-time semantics, correction behavior, storage growth, query patterns, and governance.

Further reading

• Databricks — Change data capture and snapshots
• Databricks — AUTO CDC APIs
• Databricks — Advanced AUTO CDC topics
• Databricks — Lakeflow Connect history tracking