How to Create a Slowly Changing Fact Table Type 3 in Data Modeling (Kimball)

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

This tutorial explains how to create a Slowly Changing Fact Table Type 3 in Data Modeling (Kimball) to preserve limited historical values of attributes within the fact table itself. The pattern is useful when you need to compare a current value with a single previous value (for example, prior price) without using a full SCD Type 2 structure that duplicates rows. Here we show why, when to use it, and a practical set of steps and SQL examples to implement and validate the solution.

Prerequisites

Basic knowledge of Kimball Dimensional Modeling and SQL.
An environment with a relational database (for example SQL Server, PostgreSQL) for testing. Ideally with 10k–1M rows of test data to validate performance.
Examples of transaction and dimension data (dim_customer, dim_product, dim_date) and an idea of the change rate (for example, 2–10% of products change price per month).
ETL/ELT tools (for example, SQL scripts, Azure Data Factory, dbt) to automate updates.

Step 1: Understand the Slowly Changing Fact Table Type 3 pattern

An SCD Type 3 in the fact table stores a current attribute and a previous attribute (or N limited versions) within the fact itself. Instead of creating historical rows (Type 2) that increase row volume and complicate aggregations, Type 3 keeps columns such as price_current and price_previous. This is suitable for point-in-time variation analysis (for example, price difference between today and the last change) and for reports that do not require a full history with all prior versions.

Step 2: Choose which attributes to version

Select attributes that change occasionally and where only the last previous change matters: price, risk rating, eligibility status, active promo_code. Concrete examples: if 95% of transactions only need to compare current vs previous price, keeping price_current/price_previous reduces storage cost. Avoid Type 3 for long histories or legal audit requirements — in those cases, SCD Type 2 is more appropriate.

Step 3: Define the fact table schema

Create the fact structure with columns for the fact key, dimensional keys, measures and pairs of columns for versions (current/previous). Include timestamps to know when the change occurred and metadata for lineage. Index key columns and, if needed, partition by date_key for analytical queries.

CREATE TABLE fact_sales_scd3 (
  sale_id BIGINT PRIMARY KEY,
  customer_key INT REFERENCES dim_customer(customer_key),
  product_key INT REFERENCES dim_product(product_key),
  date_key INT,
  quantity INT,
  price_current NUMERIC(10,2),
  price_previous NUMERIC(10,2) NULL,
  price_changed_date TIMESTAMP NULL,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Consider indexes: CREATE INDEX idx_fact_sales_prod_date ON fact_sales_scd3(product_key, date_key);

Step 4: ETL logic to insert new sales

For each new transaction insert price_current with the active price and price_previous as NULL (or equal to price_current if you prefer local convention). In immutable event scenarios (each sale is unique) the first insertion keeps previous NULL. For batch loads, process in chunks (for example 10k-100k rows) and use transactions for consistency.

INSERT INTO fact_sales_scd3 (
  sale_id, customer_key, product_key, date_key, quantity, price_current
) VALUES (
  1001, 200, 300, 20230901, 2, 19.99
);

Step 5: ETL logic to update existing price (migrate current → previous)

When you detect that a product price changed and you want to update fact rows that represent ongoing positions, update transactionally: move price_current to price_previous, place the new price in price_current and record the change date. For scenarios with millions of affected rows, prefer batched operations and use MERGE to reduce contention.

-- Example with MERGE (PostgreSQL/SQL Server conceptual syntax)
BEGIN;

UPDATE fact_sales_scd3
SET price_previous = price_current,
    price_current = 24.99,
    price_changed_date = '2023-09-15',
    last_updated = CURRENT_TIMESTAMP
WHERE product_key = 300
  AND date_key <= 20230915
  AND (price_current IS DISTINCT FROM 24.99);

COMMIT;

Note: filter to avoid unnecessary updates and record metrics (number of rows affected). For massive changes, schedule windows outside peak hours.

Step 6: Handle conflicts and limited historical data

Define the policy when price_previous is already populated: overwrite with the most recent prior version (overwrite mode), keep a short chain with additional columns (price_prev2, price_prev3) or reject the change for audit purposes. Example: keeping up to 2 previous versions covers 99% of analysis needs in a scenario where 85% of queries only need one previous. Document the semantics clearly for analysts and implement tests that verify behavior in cases of multiple changes within 30 days.

Step 7: Example query for variance analysis

Typical queries with a Type 3 fact compare price_current and price_previous to calculate difference and percentage. Handle NULLs (first sale) using COALESCE or filters.

SELECT
  p.product_key,
  p.product_name,
  SUM(f.quantity) AS total_qty,
  AVG(f.price_current) AS avg_price_current,
  AVG(f.price_previous) AS avg_price_previous,
  (AVG(f.price_current) - AVG(COALESCE(f.price_previous, f.price_current))) AS avg_price_diff,
  CASE WHEN AVG(f.price_previous) IS NULL THEN NULL
       ELSE (AVG(f.price_current) - AVG(f.price_previous)) / AVG(f.price_previous) * 100 END AS pct_change
FROM fact_sales_scd3 f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.product_key, p.product_name;

Verify the result

Validate that price_current and price_previous are correct: compare samples before and after, run counts (for example, SELECT COUNT(*) WHERE price_previous IS NOT NULL) and validate that the number of rows affected matches expectations (for example, 50k rows updated). Create automated tests that simulate first insert, single change and subsequent change when a previous already exists. Monitor ETL performance and logs to detect regressions.

Conclusion

A Slowly Changing Fact Table Type 3 allows keeping limited versions of attributes within the fact itself, simplifying variance analysis without multiplying rows. Always assess whether Type 3 satisfies audit requirements or if you should migrate to Type 2. Next steps: automate the ETL logic (daily/hourly schedules), add alerts when the number of changes exceeds thresholds (for example >5%/day) and document the semantics of each current and previous column to avoid misinterpretation by analysts.