SCD Type 1
A slowly changing dimension technique that overwrites an attribute with its new value. The dimension keeps one row per entity and no history, so every report, including reports on past periods, shows the current value.
Also: slowly changing dimension type 1 · Type 1 SCD · overwrite
On this page · 7 sections
- Category
- Data modeling
- Also known as
- slowly changing dimension type 1, Type 1 SCD, overwrite
- Goes deeper in
- Data Modeling
SCD Type 1 handles a changed attribute by replacing it. When a customer moves from New York to Chicago, the city on their dimension row is updated in place. The table still has one row per customer, the key does not change, and nothing records that the customer was ever in New York.
Because fact rows join to that single row, history is restated: last year's sales for the customer now report under Chicago. That is the correct result when the old value was wrong, such as a misspelled name or a miscoded category, and the wrong result when the business needs to know what was true at the time.
Type 1 is the simplest technique to build and query. It needs no effective dates, no current-row flag, and no surrogate key per version. It is also the default behavior of a dbt model materialized as a table or as an incremental model with a unique key, since both keep only the latest version of each row.
merge into dim_customers as d
using stg_customers as s
on s.customer_id = d.customer_id
when matched and (d.customer_name <> s.customer_name or d.city <> s.city) then
update set
customer_name = s.customer_name,
city = s.city,
updated_at = current_timestamp
when not matched then
insert (customer_id, customer_name, city, updated_at)
values (s.customer_id, s.customer_name, s.city, current_timestamp);Choosing Type 1 is a decision to discard history, and it cannot be reversed from the dimension alone. Once the old value is overwritten, the only way back is the source system or a snapshot taken before the change.
Most dimensions mix techniques. A customer table may overwrite the email address (Type 1) while tracking the pricing tier with history (Type 2). The choice is made per attribute, not per table.
- Overwriting an attribute that finance or sales reports by, then finding that last quarter's numbers moved.
- Comparing with <> when the column can be null. A null on either side makes the comparison unknown and the change is skipped; use IS DISTINCT FROM where the warehouse supports it.
- Forgetting that aggregate tables and extracts built on the old value are now stale and need to be rebuilt.
- What is the difference between SCD Type 1 and Type 2?
- Type 1 overwrites the attribute and keeps one row per entity, so history is lost. Type 2 closes the existing row and inserts a new one with its own effective dates, so every past value stays queryable. Use Type 1 for corrections and attributes nobody reports on historically, and Type 2 when a report must show what was true at the time.
- When should I use SCD Type 1?
- Use it for corrections to wrong data, for attributes where only the current value is ever needed, such as a contact email, and for very large dimensions where the cost of versioning every change outweighs the value of the history.
- Does SCD Type 1 need a surrogate key?
- Not for the technique itself, because there is one row per entity and the natural key identifies it. Many teams still add one so that the dimension can move to Type 2 later without changing the fact tables.
