Type 1 overwrites the existing row, Type 2 adds a new row with versioning, and Type 3 stores a limited history in additional columns.
1. Type 1 (Overwrite)
- Detect change with a simple WHERE clause.
- Execute UPDATE dim_customer SET address = 'New Addr' WHERE customer_key = 123;
- No historical rows; downstream fact tables see the latest value.
2. Type 2 (Add Row)
- Use surrogate key (customer_sk) and effective dates.
- ```sql
INSERT INTO dim_customer (customer_sk, customer_key, name, address, effective_from, effective_to, is_current)
SELECT NEXTVAL('dim_customer_seq'), 123, 'Acme Corp', 'New Addr', CURRENT_DATE, '9999-12-31', TRUE
FROM dim_customer
WHERE customer_key = 123 AND is_current = TRUE;
UPDATE dim_customer SET effective_to = CURRENT_DATE - INTERVAL '1 day', is_current = FALSE
WHERE customer_key = 123 AND is_current = TRUE;
```
- Preserves full change history; queries filter on effective_from/effective_to.
3. Type 3 (Limited History)
- Add “previous” columns (e.g., address_prev).
- ```sql
UPDATE dim_customer
SET address_prev = address,
address = 'New Addr',
address_change_dt = CURRENT_DATE
WHERE customer_key = 123;
```
- Only the immediate prior value is kept; useful for “current vs prior period” reports.
| Feature | Type 1 | Type 2 | Type 3 |
|---|---|---|---|
| History depth | None | Full | One previous |
| Row count impact | None | Increases proportionally to changes | Slight |
| Query complexity | Simple equality | Date‑range joins (effective_from/effective_to) | Select current or previous column |
| Typical use | Correcting errors, low‑latency loads | Auditing, regulatory compliance, trend analysis | Dashboard showing current vs prior period |
Decision checklist
- Do you need to audit every change? → Type 2.
- Is only the last value needed for a “trend‑over‑last‑period” view? → Type 3.
- Can you tolerate loss of historical values? → Type 1.
- Consider ETL performance: Type 2 adds inserts; Type 1 only updates; Type 3 updates same row.