JUL 08, 2026/8 min readData Engineering

Managing Partial Updates with an SCD-1 Pipeline on Snowflake

Slowly changing dimensions (SCDs) are a foundational data modeling concept for tracking how records evolve over time. SCD Type 1, which overwrites existing data to reflect only the current state, remains one of the most critical patterns in modern data engineering because it keeps analytical systems lean, consistent and free from the complexity of historical versioning when point-in-time accuracy simply isn't required. Snowflake's Dynamic Tables have made this easier than ever with the QUALIFY ROW_NUMBER() pattern.

This works well until your data source introduces a major wrinkle: partial updates.

What happens when your source change data capture (CDC) feed sends an update for a row, but only includes values for the columns that actually changed? The remaining columns arrive as NULL. If you blindly overwrite your Dynamic Table with these partial records, you'll accidentally wipe out valid historical data.

In this post, we'll explore how to extend the standard SCD-1 pattern to handle these partial updates, as well as the inevitable challenges of schema evolution, ensuring your “current state” table remains both accurate and resilient to changes over time.

The standard SCD-1 pattern

First, let's revisit the foundation. The classic pattern for keeping the latest record per business key looks like this. When data changes in your application, a CDC tool logs every individual modification and appends it to a raw staging table. To transform this noisy, append-only stream into a clean SCD-1 table, a Snowflake Dynamic Table continuously deduplicates the logs by filtering out older timestamps and preserving only the absolute latest version for each unique ID.

Figure 1. Managing Partial Updates with a SCD-1 pipeline on Snowflake

A CDC tool captures every data change and appends it to a raw staging table. A Snowflake Dynamic Table then deduplicates that stream, keeping only the latest record per ID, to produce a clean SCD Type 1 table automatically.

Here is an example of a raw append-only log capturing the entire history of modifications a customers table:

customer_id	name	email	phone	cdc_updated_at
452	Alice	alice@email.com	555-0100	2026-01-15 10:00:00
718	Bob	bob@email.com	555-0200	2026-02-10 11:00:00
452	Alice	alice_new@email.com	555-0100	2026-03-22 14:30:00
718	Bob	bob@email.com	555-9999	2026-04-05 09:00:00

The Dynamic Table automatically drops the stale records, surfacing a compressed, up-to-date view of your users:

customer_id	name	email	phone	cdc_updated_at
452	Alice	alice_new@email.com	555-0100	2026-03-22 14:30:00
718	Bob	bob@email.com	555-9999	2026-04-05 09:00:00

The Dynamic Table defining this transformation is very simple. It handles out-of-order records seamlessly and can be easily extended to support soft deletes with additional AND NOT is_deleted condition.

CREATE OR REPLACE DYNAMIC TABLE dt ...
AS
SELECT * 
FROM raw_customers_cdc
QUALIFY ROW_NUMBER() OVER (  PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

The challenge: partial updates

The standard deduplication pattern breaks down when your CDC tool sends partial updates, meaning it only exports the specific columns that changed, leaving everything else as NULL.

Let's look at what happens when Alice updates her phone number on June 17th, but her name and email are omitted from the CDC payload.

A new partial record is appended to the stream. Because only the phone number changed, the other fields arrive empty:

customer_id	name	email	phone	cdc_updated_at
452	Alice	alice_new@email.com	555-0100	2026-03-22 14:30:00
452	NULL	NULL	555-1111	2026-06-17 12:00:00

If our Dynamic Table blindly selects the row with the latest timestamp using ROW_NUMBER(), the NULL values will overwrite your valid historical data. Alice's name and email are completely wiped out. To achieve a true SCD-1 state, your pipeline must be smart enough to coalesce the incoming change with the last known valid state.

To handle this, we need to shift from a simple QUALIFY approach to a merge-based logic that allows us to coalesce incoming partial data with our existing state.

Snowflake Dynamic Tables provide several ways of accomplishing this.

Standard Dynamic Tables

To address the partial update problem, we use a clever, declarative approach. We don't have to write complex logic; we just tell Snowflake how to assemble the final view.

LAST_VALUE(...) IGNORE NULLS: Think of this as a “sticky” filter. If we receive a new update, we keep it. If the update is blank (NULL), the system looks back at the record's history to find the last known good value and carries it forward.
QUALIFY: This is our “final cut.” After filling in all the blanks for Alice or Bob, it trims the history down so we only see the very latest, complete version of each person.

Here is the code tailored to our customer example. It treats Alice and Bob's partial updates as pieces of a puzzle, using their history to ensure no data is lost:

CREATE OR REPLACE DYNAMIC TABLE DT_CUSTOMERS_CURRENT ...
AS
SELECT
    CUSTOMER_ID,
    EVENT_TIMESTAMP,
    -- We "stick" the last known value for each attribute
    LAST_VALUE(NAME) IGNORE NULLS 
        OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS NAME,
    LAST_VALUE(EMAIL) IGNORE NULLS 
        OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS EMAIL,
    LAST_VALUE(PHONE) IGNORE NULLS 
        OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS PHONE
FROM RAW_CUSTOMERS_CDC
-- Finally, we pick only the most recent row for Alice and Bob
QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP DESC) = 1;

As before, out-of-order records are handled seamlessly thanks to the declarative nature of this transformation.

This transformation is “stateless” — meaning it scans an entity's specific history in the base table rather than referencing the Dynamic Table's own current state. While this can work well for many use cases, it can be expensive if the base table is very large and the history of modified entities spans years in a massive table. If modified entities are short-lived, we recommend that the entity ID is a roughly monotonic column (such as UUID v7). By ensuring your IDs follow a roughly chronological order, the Dynamic Table can perform targeted, local scans instead of full-table traversals, keeping your data fresh and your compute costs low even as your customer base expands.

Custom incremental Dynamic Table

Alternatively, you can achieve this same result using a custom incremental Dynamic Table (Public Preview). This allows you to write your custom MERGE logic to define exactly how each new batch of data blends into your existing records. The performance payoff can be significant for very large workloads.

This approach is fully stateful, meaning it takes incoming updates and applies them directly to the Dynamic Table without ever needing to scan old historical data. Additionally, this approach supports more flexible schema evolution. If you need to add new columns in the future, you can update the table definition without requiring a full, resource-intensive reinitialization of the data.

Since the CDC log is insert-only, we use CHANGES(INFORMATION => APPEND_ONLY) since we don't expect any deletes.

CREATE OR REPLACE DYNAMIC TABLE DT_CUSTOMERS_CURRENT(
    CUSTOMER_ID      STRING,
    EVENT_TIMESTAMP  TIMESTAMP,
    NAME             VARCHAR,
    EMAIL            VARCHAR,
    PHONE            VARCHAR
)
...
REFRESH USING (
    MERGE INTO SELF AS tgt
    USING (
        SELECT
            CUSTOMER_ID,
            EVENT_TIMESTAMP,
            -- Deduplicate within the current batch, carrying over last known values
            LAST_VALUE(NAME) IGNORE NULLS 
                OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS NAME,
            LAST_VALUE(EMAIL) IGNORE NULLS 
                OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS EMAIL,
            LAST_VALUE(PHONE) IGNORE NULLS 
                OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS PHONE
        FROM RAW_CUSTOMERS_CDC CHANGES(INFORMATION=>APPEND_ONLY)
        QUALIFY ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP DESC) = 1
    ) src
    ON tgt.CUSTOMER_ID = src.CUSTOMER_ID
    WHEN MATCHED THEN UPDATE SET
        CUSTOMER_ID     = src.CUSTOMER_ID,
        EVENT_TIMESTAMP = src.EVENT_TIMESTAMP,
        -- Stateful merge: blend the new batch with existing target state
        NAME            = COALESCE(src.NAME,  tgt.NAME),
        EMAIL           = COALESCE(src.EMAIL, tgt.EMAIL),
        PHONE           = COALESCE(src.PHONE, tgt.PHONE)
    WHEN NOT MATCHED THEN 
        INSERT (CUSTOMER_ID, EVENT_TIMESTAMP, NAME, EMAIL, PHONE) 
        VALUES (src.CUSTOMER_ID, src.EVENT_TIMESTAMP, src.NAME, src.EMAIL, src.PHONE)
);

For simplicity, this specific transformation does not natively support out-of-order data. If an older batch arrives late, it could overwrite newer values. However, this is not hard to address: You can store a tracking timestamp next to each attribute in the Dynamic Table and compare those timestamps in the COALESCE logic below to ensure only the most recent data is applied.

Schema evolution

As your application grows, your data models may change. In Snowflake, you don't need to manually drop and recreate your tables to evolve their schema. Instead, you can use the CREATE OR ALTER DYNAMIC TABLE syntax. This command acts as an idempotent deployment mechanism: If the table doesn't exist, Snowflake creates it; if it does exist, Snowflake modifies it in place.

How your pipeline handles adding or deleting columns depends entirely on the type of Dynamic Table you deploy.

Evolving a standard Dynamic Table

Standard Dynamic Tables operate under view semantics, meaning the table's data must always exactly match what the query would produce if run against your entire source history. Consequently, adding a column forces Snowflake to fully recompute the dataset from scratch to maintain this historical consistency, a process that can be expensive for very large tables.

To add NEW_COLUMN to your pipeline, simply update the query to include the new column-tracking logic. When you run this command, Snowflake detects the new column in the select list, updates the table structure and triggers a background recomputation to populate it historically:

CREATE OR ALTER DYNAMIC TABLE DT_CUSTOMERS_CURRENT ...
AS 
SELECT
    ...
    -- Simply append the new column logic to the end of the query
    LAST_VALUE(NEW_COLUMN) IGNORE NULLS 
        OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS NEW_COLUMN
FROM RAW_CUSTOMERS_CDC ...;

Evolving a Dynamic Table with custom incremental logic

Because they are essentially a continuous MERGE or INSERT, Dynamic Tables with custom incremental logic do not have the notion of recomputing all the data. When you introduce a new column, existing rows are preserved as-is and the new column in these rows is filled with NULL. The next refresh seamlessly resumes processing from where it left off. It is up to you to make sure that MERGE will correctly handle uninitialized NULL values of newly added columns. In our case we use COALESCE, which accumulates the new attribute moving forward.

Similarly to SELECT-based Dynamic Tables, the transformation is updated with CREATE OR ALTER:

CREATE OR ALTER DYNAMIC TABLE DT_CUSTOMERS_CURRENT(
    ...,
    -- Explicitly add the new column to the schema definition
    NEW_COLUMN       VARCHAR
)
...
REFRESH USING (
    MERGE INTO SELF AS tgt
    USING (
        SELECT
            ...
            -- Include the new column in your batch deduplication logic
            LAST_VALUE(NEW_COLUMN) IGNORE NULLS
                OVER (PARTITION BY CUSTOMER_ID ORDER BY EVENT_TIMESTAMP) AS NEW_COLUMN
        FROM ...
    ) src
    ON tgt.CUSTOMER_ID = src.CUSTOMER_ID
    WHEN MATCHED THEN UPDATE SET
        -- Update the merge-coalesce block
        ...
        NEW_COLUMN = COALESCE(src.NEW_COLUMN, tgt.NEW_COLUMN) 
    WHEN NOT MATCHED THEN 
        -- Update the insert blocks to map the new column
        INSERT (..., NEW_COLUMN) VALUES (..., src.NEW_COLUMN)
);

Importantly, no recomputation of historical data is performed. The schema evolves purely as a forward-fill. If your business needs require retroactively backfilling older data, you can simply run a one-time manual DML update directly against the Dynamic Table.

Notes:

The same pattern applies when dropping a column.
New columns must be added to the end of the column list. Snowflake does not support inserting new columns in the middle of the Dynamic Table. Dropping columns has no such restrictions.

Conclusion

This walkthrough demonstrates both the simplicity of using Dynamic Tables for manageable datasets and the robust flexibility of using custom incremental logic in Dynamic Tables for complex, high-scale workloads. By leveraging these patterns, you can effectively handle partial updates, ensure data integrity and manage schema evolution with minimal operational overhead. As your data architecture evolves, these tools provide a scalable foundation for keeping your dimensions accurate, clean and up to date.

Learn more about the author

Vlad Lifliand

Principal Software Engineer