How to Query Slowly Changing Data With SQL

Data rarely stays the same.

A customer’s address can change. An employee can move to another department. A product can change price or category. A company can update its customer segment. If your database only stores the latest version of each record, it can become difficult to answer questions about what was true in the past.

This is where slowly changing data becomes important.

In data warehousing and analytics, Slowly Changing Dimensions (SCDs) are commonly used to track changes to descriptive attributes over time. SQL can then be used to retrieve the current version, previous versions, or the version that was valid at a particular point in time.

For example, imagine an employee who originally worked in the Sales department:

Employee: 101
Department: Sales

Six months later, the employee moves to Marketing:

Employee: 101
Department: Marketing

If we overwrite Sales with Marketing, we know where the employee works now, but we lose the historical information.

A slowly changing data model allows us to keep both versions.

In this guide, you’ll learn how to query slowly changing data with SQL, including practical examples using date ranges, window functions, current records, historical records, and point-in-time analysis.

What Is Slowly Changing Data?

Slowly changing data refers to information whose values change over time, usually at a relatively low frequency compared with transactional data.

Examples include:

• Customer addresses
• Customer segments
• Employee departments
• Product categories
• Sales territories
• Supplier information
• Account classifications
• Pricing attributes

Consider this customer record:

Customer ID	Name	Segment
1001	Sarah	Standard

Later, Sarah becomes a premium customer.

The new state is:

Customer ID	Name	Segment
1001	Sarah	Premium

There are two possible approaches.

Overwrite the old value

UPDATE customers
SET segment = 'Premium'
WHERE customer_id = 1001;

This is simple, but the previous value is lost.

Keep historical versions

Instead, we can store:

Customer ID	Segment	Start Date	End Date
1001	Standard	2025-01-01	2026-05-14
1001	Premium	2026-05-15	NULL

Now we can determine both the customer’s current segment and their historical segment.

This approach is particularly useful in analytical systems.

Slowly Changing Dimensions and SQL

In dimensional data warehousing, slowly changing dimensions are commonly categorized into different types.

The most frequently discussed are:

• Type 0
• Type 1
• Type 2
• Type 3

The SQL needed to query the data depends heavily on how changes are stored.

Type 0: Keep the Original Value

Type 0 does not update the attribute after it is initially recorded.

For example:

Customer ID	Original Country
1001	Nigeria

Even if the customer later moves, the original country remains unchanged.

This is useful when the original value itself is important.

There is usually no complicated historical query required because the original value is intentionally preserved.

Type 1: Overwrite the Value

Type 1 simply replaces the old value.

Suppose we have:

Customer ID: 1001
Segment: Standard

The customer becomes Premium:

UPDATE customer_dimension
SET segment = 'Premium'
WHERE customer_id = 1001;

The table now contains:

Customer ID	Segment
1001	Premium

The advantage is simplicity.

The disadvantage is that historical values are unavailable.

Type 1 is therefore appropriate when historical changes are not required for analysis.

Type 2: Keep Full History

Type 2 is one of the most useful approaches when you need to track historical changes.

Instead of replacing the old record, you create a new version.

A typical table might contain:

CREATE TABLE customer_dimension (
    customer_id INT,
    customer_name VARCHAR(100),
    segment VARCHAR(50),
    valid_from DATE,
    valid_to DATE,
    is_current BOOLEAN
);

Example data:

Customer ID	Segment	Valid From	Valid To	Is Current
1001	Standard	2025-01-01	2026-05-14	FALSE
1001	Premium	2026-05-15	NULL	TRUE

The valid_from and valid_to columns define the period during which each version is valid.

The is_current column makes it easy to identify the latest record.

Querying the Current Record

One of the most common questions is:

What is the customer’s current segment?

If is_current is maintained correctly, the query is straightforward:

SELECT
    customer_id,
    customer_name,
    segment
FROM customer_dimension
WHERE is_current = TRUE;

This returns only the active version.

For customer 1001:

1001 | Sarah | Premium

Querying All Historical Versions

To see every version of a customer’s record:

SELECT
    customer_id,
    customer_name,
    segment,
    valid_from,
    valid_to
FROM customer_dimension
WHERE customer_id = 1001
ORDER BY valid_from;

The result might look like:

Customer ID	Segment	Valid From	Valid To
1001	Standard	2025-01-01	2026-05-14
1001	Premium	2026-05-15	NULL

This allows analysts to reconstruct how the customer’s attributes changed over time.

Finding the Version Valid on a Specific Date

This is where slowly changing data becomes especially useful.

Suppose you want to know:

What segment was customer 1001 in on March 1, 2026?

You can use the validity period:

SELECT
    customer_id,
    segment
FROM customer_dimension
WHERE customer_id = 1001
  AND DATE '2026-03-01' >= valid_from
  AND (
      DATE '2026-03-01' < valid_to
      OR valid_to IS NULL
  );

The query checks whether March 1 falls within the record’s valid period.

The result would be:

1001 | Standard

Even though the customer is now Premium, the historical query correctly identifies the segment that was valid at that time.

This is often called point-in-time querying.

Why Point-in-Time Queries Matter

Point-in-time analysis is important because using today’s attributes to analyze historical events can produce misleading results.

Imagine a customer purchased a product in January when they were classified as:

Standard

They became:

Premium

in July.

If you analyze the January transaction using the customer’s current classification, you might incorrectly attribute the transaction to the Premium segment.

Historical dimensions allow you to use the attribute that was actually valid when the transaction occurred.

For example:

SELECT
    s.sale_id,
    s.sale_date,
    s.customer_id,
    d.segment
FROM sales s
JOIN customer_dimension d
    ON s.customer_id = d.customer_id
   AND s.sale_date >= d.valid_from
   AND (
       s.sale_date < d.valid_to
       OR d.valid_to IS NULL
   );

This is a powerful pattern for historical analytics.

Querying Slowly Changing Data With Window Functions

Sometimes your table does not have valid_to or is_current.

Instead, you may only have change timestamps.

For example:

Customer ID	Segment	Changed At
1001	Standard	2025-01-01
1001	Premium	2026-05-15
1001	Enterprise	2026-08-20

We can use the LEAD() window function to determine when each version stopped being valid.

SELECT
    customer_id,
    segment,
    changed_at AS valid_from,
    LEAD(changed_at) OVER (
        PARTITION BY customer_id
        ORDER BY changed_at
    ) AS valid_to
FROM customer_changes;

The result becomes:

Customer ID	Segment	Valid From	Valid To
1001	Standard	2025-01-01	2026-05-15
1001	Premium	2026-05-15	2026-08-20
1001	Enterprise	2026-08-20	NULL

The last record has no next change, so its valid_to value is NULL.

This is one of the most useful SQL techniques for working with historical changes.

Finding the Latest Version With ROW_NUMBER()

Another common problem is finding the latest record for every entity.

Suppose we have:

customer_id | segment     | changed_at
------------+-------------+-----------
1001        | Standard    | 2025-01-01
1001        | Premium     | 2026-05-15
1002        | Standard    | 2025-03-10
1002        | Premium     | 2026-07-01

We can use ROW_NUMBER():

SELECT
    customer_id,
    segment,
    changed_at
FROM (
    SELECT
        customer_id,
        segment,
        changed_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY changed_at DESC
        ) AS rn
    FROM customer_changes
) x
WHERE rn = 1;

The query ranks each customer’s records from newest to oldest.

rn = 1 gives us the latest version.

Finding Previous Values With LAG()

LAG() is useful when you want to compare each version with the previous version.

SELECT
    customer_id,
    segment,
    changed_at,
    LAG(segment) OVER (
        PARTITION BY customer_id
        ORDER BY changed_at
    ) AS previous_segment
FROM customer_changes;

The result might be:

Customer ID	Segment	Changed At	Previous Segment
1001	Standard	2025-01-01	NULL
1001	Premium	2026-05-15	Standard
1001	Enterprise	2026-08-20	Premium

Now we can identify actual changes.

For example:

SELECT *
FROM (
    SELECT
        customer_id,
        segment,
        changed_at,
        LAG(segment) OVER (
            PARTITION BY customer_id
            ORDER BY changed_at
        ) AS previous_segment
    FROM customer_changes
) x
WHERE previous_segment IS NOT NULL
  AND segment <> previous_segment;

This can be useful when analyzing customer movements between segments.

Type 3: Store Previous and Current Values

Type 3 stores limited history rather than every historical version.

For example:

Customer ID	Current Segment	Previous Segment
1001	Premium	Standard

This allows you to answer questions such as:

What was the customer’s previous segment?

But it does not preserve an unlimited history.

If the customer changes again, the previous value may be replaced.

Type 3 can therefore be useful when the business only needs a limited amount of historical context.

Handling NULL End Dates

A common design is to use NULL to represent the currently active record.

For example:

Segment	Valid From	Valid To
Standard	2025-01-01	2026-05-15
Premium	2026-05-15	NULL

When querying the current record, you can use:

WHERE valid_to IS NULL

When querying a historical date, remember to account for the NULL:

WHERE target_date >= valid_from
  AND (target_date < valid_to OR valid_to IS NULL)

This pattern is important because:

target_date < NULL

does not evaluate to TRUE.

The explicit OR valid_to IS NULL handles the current version correctly.

Be Careful With Date Boundaries

Historical queries can produce incorrect results if date boundaries are not defined consistently.

A common approach is to treat validity periods as:

[valid_from, valid_to)

This means:

• valid_from is inclusive
• valid_to is exclusive

For example:

Standard:
2026-01-01 → 2026-05-15

Premium:
2026-05-15 → NULL

At exactly 2026-05-15, Premium becomes the valid version.

That is why the query uses:

target_date >= valid_from
AND target_date < valid_to

rather than using <= valid_to.

Consistent boundary rules help prevent overlapping records.

Detecting Overlapping Historical Records

A well-designed Type 2 table should generally avoid overlapping validity periods for the same entity.

You can use LAG() to investigate potential problems.

For example:

SELECT
    customer_id,
    valid_from,
    valid_to,
    LAG(valid_to) OVER (
        PARTITION BY customer_id
        ORDER BY valid_from
    ) AS previous_valid_to
FROM customer_dimension;

You can then look for cases where the current valid_from occurs before the previous record ended.

SELECT *
FROM (
    SELECT
        customer_id,
        valid_from,
        valid_to,
        LAG(valid_to) OVER (
            PARTITION BY customer_id
            ORDER BY valid_from
        ) AS previous_valid_to
    FROM customer_dimension
) x
WHERE previous_valid_to IS NOT NULL
  AND valid_from < previous_valid_to;

This can help identify overlapping records.

Slowly Changing Data in Analytics

Slowly changing data becomes particularly important when historical attributes are used to group metrics.

Consider an employee whose department changes:

January → Sales
June   → Marketing

If you want to calculate January sales performance by department, you should associate January’s records with Sales.

A current-state-only table could incorrectly classify the employee’s entire history as Marketing.

This is why historical dimensions are common in analytical data models.

The goal is not simply to know:

What is true now?

It is also to know:

What was true when this event happened?

Common SQL Mistakes With Slowly Changing Data

1. Always selecting the latest record

Using only:

ORDER BY changed_at DESC

can give you the current state when you actually need historical state.

2. Ignoring the validity period

Joining only on:

customer_id

can cause historical transactions to match multiple versions.

3. Using the wrong date boundary

Using <= valid_to can create problems when one version ends on the same date another begins.

4. Assuming NULL behaves like a date

It does not.

Current records with NULL end dates need explicit handling.

5. Not considering duplicate timestamps

If two changes have the same timestamp, ROW_NUMBER() may not produce a deterministic result unless another ordering column is included.

For example:

ORDER BY changed_at DESC, change_id DESC

can provide a tie-breaker.

A Simple Mental Model

When working with slowly changing data, ask three questions:

1. What entity am I tracking?

For example:

customer_id
employee_id
product_id

2. What attribute changed?

For example:

segment
department
category

3. When was each version valid?

For example:

valid_from
valid_to

Once these three pieces are clear, most historical SQL queries become easier to construct.

Practical SQL Pattern to Remember

For a Type 2 dimension, this pattern is worth remembering:

SELECT
    fact.transaction_id,
    fact.transaction_date,
    dim.customer_id,
    dim.segment
FROM transactions fact
JOIN customer_dimension dim
    ON fact.customer_id = dim.customer_id
   AND fact.transaction_date >= dim.valid_from
   AND (
       fact.transaction_date < dim.valid_to
       OR dim.valid_to IS NULL
   );

This joins each transaction to the customer dimension version that was valid when the transaction occurred.

That is the core idea behind many historical data queries.

Frequently Asked Questions