How to create a Slowly Changing Dimension Type 1 in Data Modeling (Kimball)

João Barros 01 de September de 2026 4 min read

Learn how to implement a Slowly Changing Dimension Type 1 (SCD Type 1) in Data Modeling (Kimball) to update dimension attributes when it is not necessary to keep history. This technique is useful to correct or overwrite values (e.g., contacts, addresses) and simplifies ETL and reporting.

Prerequisites

Basic knowledge of SQL (SELECT, INSERT, UPDATE).
A relational database (e.g., SQL Server, PostgreSQL) to test against.
Source data with a business key and attributes that can change.
A simple ETL tool (e.g., SQL scripts, SSIS, Azure Data Factory).

Step 1: Identify the dimension and the Type 1 attributes

Choose the dimension that will be updated without keeping history. Examples: Customer, Product (name), Supplier contact. Decide which attributes will be overwritten (Type 1) and which, if any, require different handling.

Step 2: Design the dimension table

Create the dimension table in a star schema with a surrogate key and the business key. A temporal validity column (start_date/end_date) is not required for SCD Type 1.

CREATE TABLE dim_customer (
  customer_sk INT IDENTITY(1,1) PRIMARY KEY,
  customer_id VARCHAR(50) UNIQUE, -- business key
  customer_name VARCHAR(200),
  email VARCHAR(200),
  city VARCHAR(100),
  country VARCHAR(100),
  last_updated DATETIME
);

Step 3: Prepare the source data

Read the source data (e.g., staging table or feed). Typically you have a staging table with a recent snapshot. Pattern: staging_customer contains customer_id and current attributes.

-- Exemplo de staging
CREATE TABLE staging_customer (
  customer_id VARCHAR(50),
  customer_name VARCHAR(200),
  email VARCHAR(200),
  city VARCHAR(100),
  country VARCHAR(100)
);

Step 4: Compare and identify changes (INSERT vs UPDATE)

Use MERGE (or JOIN + UPDATE/INSERT) to distinguish new records to insert and existing records to update. For SCD Type 1, existing rows are updated to reflect the new values.

-- Exemplo usando MERGE (SQL Server / compatível)
MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_id = src.customer_id
WHEN MATCHED AND (
     ISNULL(tgt.customer_name,'') <> ISNULL(src.customer_name,'')
  OR ISNULL(tgt.email,'') <> ISNULL(src.email,'')
  OR ISNULL(tgt.city,'') <> ISNULL(src.city,'')
  OR ISNULL(tgt.country,'') <> ISNULL(src.country,'')
) THEN
  UPDATE SET
    customer_name = src.customer_name,
    email = src.email,
    city = src.city,
    country = src.country,
    last_updated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (customer_id, customer_name, email, city, country, last_updated)
  VALUES (src.customer_id, src.customer_name, src.email, src.city, src.country, GETDATE());

Step 5: Handle sensitive columns and business rules

If there are rules (e.g., do not overwrite email if it is null) apply additional conditions in the MERGE/UPDATE. For critical attributes, validations and logging help trace unexpected changes.

-- Exemplo: não sobrescrever email se src.email IS NULL
WHEN MATCHED AND (
  (ISNULL(tgt.customer_name,'') <> ISNULL(src.customer_name,''))
  OR (src.email IS NOT NULL AND ISNULL(tgt.email,'') <> src.email)
)
THEN UPDATE SET ...

Step 6: Record changes and auditing

Even when not preserving history in the dimension, it is useful to maintain an audit table with before/after or an ETL log to track changes and enable debugging.

CREATE TABLE dim_customer_audit (
  audit_id INT IDENTITY(1,1) PRIMARY KEY,
  customer_id VARCHAR(50),
  changed_at DATETIME,
  changed_by VARCHAR(100),
  change_type VARCHAR(10), -- 'INSERT' ou 'UPDATE'
  old_values JSON, -- ou formato apropriado
  new_values JSON
);

-- Inserir registo no processo ETL sempre que houver UPDATE ou INSERT

Verify the result

Confirm that new customers were inserted and that existing customers have attributes updated without duplicating surrogate keys. Examples of checks:

Count records: SELECT COUNT(*) FROM dim_customer;
Check changes: SELECT * FROM dim_customer WHERE last_updated > DATEADD(hour,-1,GETDATE());
Check integrity: unique business keys and no duplicates.

Conclusion

Implementing a Slowly Changing Dimension Type 1 is a common and straightforward task in Data Modeling (Kimball): it updates attributes without preserving history, reduces complexity and improves query performance. Next steps: automate the ETL job, add tests and consider SCD Type 2 if you need history. Tip: always document the overwrite rules to avoid surprises in reports.