SCD Type 2 is often seen as the "correct" way to model data history. You get full change tracking and no data loss.
But there's something we don't call out enough: SCD2 inherently duplicates data — current snapshot plus historical records.
Now, what if you also had to support both OLTP (fast transactional updates) and OLAP (analytical queries) in the same system?
Naturally, the instinct is to avoid additional duplication. That's where things get interesting.
The tradeoff
Trying to minimize duplication often forces a compromise:
- You move away from a pure SCD2 model
- The schema becomes more complex
- Queries get harder to reason about
You can't optimize history, performance, and duplication at the same time. Something has to give.
In many cases, avoiding duplication doesn't remove complexity — it just pushes it into query logic, constraints, or schema design. The complexity doesn't disappear; it migrates.