SCD Types Explained
Slowly Changing Dimensions define how a table handles updates. BoltPipeline enforces your SCD policy automatically — you just write SQL.
Background
What is a Slowly Changing Dimension?
In data warehousing, a Slowly Changing Dimension (SCD) describes how a dimension table handles changes to its source data over time. The three questions every dimension designer must answer are:
Do you overwrite?
Replace the old value with the new one — no history preserved.
Do you keep history?
Add a new row for each change, keeping all previous versions.
Do you lock?
Reject all changes — the original value is the correct one forever.
BoltPipeline supports three SCD types — 0, 1, and 2 — and enforces whichever policy you set for each step. You do not need to write merge logic, hash comparisons, or end-dating SQL. BoltPipeline handles all of it automatically during pipeline execution.
SCD Type 0 — Fixed
Rows are written once and never updated
SCD Type 0 is for reference data that must never change after it is first loaded. BoltPipeline will reject any attempt to update or delete rows in a Type 0 table. The original insert is the authoritative record.
Use it for
- ›Country and currency code tables
- ›Product category hierarchies that never restructure
- ›Static mapping and lookup tables
- ›Any reference data where the source of truth is the original load
Avoid when
- ›Source corrections need to propagate to the warehouse
- ›Rows need to be soft-deleted or marked inactive
- ›You need to reflect real-world changes over time
SCD Type 1 — Overwrite
Always reflects the latest value; no history preserved
SCD Type 1 is the most common type. When a source record changes, BoltPipeline updates the existing row in place. The previous value is replaced — there is no historical trail. Use this when only the current state of a dimension matters.
What happens when a customer changes their address
Before update
After update (SCD 1)
The old address is gone. If you need to know the customer's address last quarter, you cannot — use SCD 2 for that.
Use it for
- ›Customer address, email, phone number (current only)
- ›Employee department or title (current only)
- ›Product price or status (latest value)
- ›Any dimension where only “right now” matters
Avoid when
- ›Reports need to reflect “state as of” a past date
- ›Regulators require an audit trail of changes
- ›Historical analysis depends on the old values
SCD Type 2 — Full History
Every version of a row is preserved with effective date ranges
SCD Type 2 is used when you need a full audit trail of every change to a dimension over time. When a source record changes, BoltPipeline closes the current row (recording when it became inactive) and inserts a new active row. Each row has an effective date range, letting you query the state of any dimension at any point in history.
Same customer, three versions tracked
| customer_id | region | effective_from | effective_to | active |
|---|---|---|---|---|
| C001 | East | 2022-01-01 | 2023-06-14 | No |
| C001 | West | 2023-06-15 | 2024-11-02 | No |
| C001 | Central | 2024-11-03 | ∞ | Yes |
BoltPipeline manages effective_from, effective_to, and active flag automatically. You just write the SELECT.
Use it for
- ›Customer lifetime value and cohort analysis
- ›Regulatory and compliance reporting
- ›Historical “as of” snapshots for any date
- ›Employee tenure and career progression
Consider trade-offs when
- ›The table is very high-volume and changes extremely frequently
- ›BI queries always want current state only — SCD 1 is simpler
- ›Storage cost is a hard constraint
Decision guide
Choosing the right SCD type
| Question | Type 0 | Type 1 | Type 2 |
|---|---|---|---|
| Should the original value be preserved forever? | Yes | No | No |
| Does the table change frequently? | No | Yes | Yes |
| Do you need to query historical states? | No | No | Yes |
| Is audit trail or compliance required? | No | No | Yes |
| Are you optimizing for storage / simplicity? | Yes | Yes | No |