Forum Discussion

jubinsoni's avatar
jubinsoni
Icon for Memorable Member rankMemorable Member
6 days ago
Solved

Warehouse SCD Type 2 loads: MERGE or INSERT/UPDATE pattern for large dimensions?

Hi everyone,

I am designing a Type 2 slowly changing dimension load in a Fabric Warehouse for a dimension with a few million rows. Two options on the table:

  • A single MERGE that closes changed rows and inserts new versions.
  • Two statements: an UPDATE that closes the current row for changed keys, followed by an INSERT of the new versions.

A sketch of the two-statement version:

UPDATE d
SET d.valid_to = SYSUTCDATETIME(), d.is_current = 0
FROM dbo.dim_customer AS d
JOIN stg.customer AS s ON s.customer_id = d.customer_id
WHERE d.is_current = 1 AND s.row_hash <> d.row_hash;

INSERT INTO dbo.dim_customer (customer_id, name, row_hash, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.row_hash, SYSUTCDATETIME(), NULL, 1
FROM stg.customer AS s
LEFT JOIN dbo.dim_customer AS d ON d.customer_id = s.customer_id AND d.is_current = 1
WHERE d.customer_id IS NULL OR d.row_hash <> s.row_hash;

Questions:

  • Is MERGE considered reliable enough in the Warehouse for SCD2, or do people prefer the explicit two-step approach?
  • How do you wrap the two statements so a failure between them does not leave the dimension half updated?
  • Any guidance on small file and compaction concerns when the table is updated frequently?

Thanks in advance.

warehouse
  • jubinsoni's avatar
    jubinsoni
    5 days ago

    Thank you v-kathullac​ and Aditya_VNB​. Here is the summary of the approach from this thread.

    Short answer: use the two-step UPDATE + INSERT pattern inside one explicit transaction, with a single timestamp for the whole batch.

    BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @now datetime2 = SYSUTCDATETIME();   -- one timestamp for the whole batch

    UPDATE d SET d.valid_to = @now, d.is_current = 0
    FROM dbo.dim_customer AS d
    JOIN stg.customer AS s ON s.customer_id = d.customer_id
    WHERE d.is_current = 1 AND s.row_hash <> d.row_hash;

    INSERT INTO dbo.dim_customer (customer_id, name, row_hash, valid_from, valid_to, is_current)
    SELECT s.customer_id, s.name, s.row_hash, @now, NULL, 1
    FROM stg.customer AS s
    LEFT JOIN dbo.dim_customer AS d ON d.customer_id = s.customer_id AND d.is_current = 1
    WHERE d.customer_id IS NULL OR d.row_hash <> s.row_hash;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
    MERGE vs UPDATE+INSERT -> both supported; two statements are easier to maintain and troubleshoot
Atomicity              -> one transaction with TRY/CATCH and ROLLBACK, no half-updated dimension
Timestamps             -> capture SYSUTCDATETIME() once, reuse for valid_to and valid_from
Staging                -> exactly one row per business key; dedupe first
Volume                 -> process only changed keys (row hash), avoid frequent small writes
Concurrency            -> table-level locking in the Warehouse, so avoid concurrent writers
Validation             -> after load, each key has exactly one current row
Next step              -> benchmark against MERGE on realistic data volumes

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

3 Replies

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support
    6 days ago

    Hi jubinsoni​ ,

    Thank you for reaching out to Microsoft Fabric Community Forum, Below are the few points which can resolve your issue. Let us know if you need any further assistance. For a Type 2 SCD implementation in Fabric Warehouse, I recommend the two-step UPDATE + INSERT approach.

    • MERGE vs. UPDATE + INSERT: Both are supported, but UPDATE + INSERT makes SCD2 logic easier to maintain, troubleshoot, and validate.
    • Transaction handling: Wrap both statements in a single transaction using BEGIN TRANSACTION, COMMIT, and TRY...CATCH with ROLLBACK. This prevents partial updates if either statement fails.
    • Consistent timestamps: Capture SYSUTCDATETIME() once and reuse it for both valid_to and valid_from.
    • Data validation: Ensure staging contains only one record per business key and each key has only one current record after loading.
    • Performance: Process changes in batches, compare row hashes to avoid unnecessary updates, and avoid frequent small write operations.
    • Compaction: Fabric Warehouse handles background maintenance and compaction automatically. Manual Lakehouse OPTIMIZE or VACUUM commands are not required.
    • Concurrency: Avoid simultaneous loads against the same dimension to prevent duplicate current records or conflicting updates.

    Use the transactional UPDATE + INSERT pattern as the starting point, then benchmark it against MERGE with realistic data volumes to determine which performs better in your Warehouse environment.

    Documentation:

    Thanks,
    Chaithanya.

  • Aditya_VNB's avatar
    Aditya_VNB
    New Member
    6 days ago

    Hi jubinsoni​,

    I would personally go with UPDATE + INSERT wrapped in an explicit transaction. For SCD2 it keeps the logic very clear and makes troubleshooting/restarts easier.

    Fabric Warehouse supports ACID transactions, so BEGIN TRAN / COMMIT / ROLLBACK can make both statements atomic. I’d also capture SYSUTCDATETIME() once into a variable and use the same timestamp to expire the old version and create the new one.

    At a few million rows, I’d pay more attention to deduplicating staging, only processing genuinely changed business keys, and avoiding concurrent writes. Fabric Warehouse uses table-level locking, which is relevant for frequent update workloads.

    So for me: two explicit statements, one transaction, one batch timestamp.

  • jubinsoni's avatar
    jubinsoni
    Icon for Memorable Member rankMemorable Member
    5 days ago

    Thank you v-kathullac​ and Aditya_VNB​. Here is the summary of the approach from this thread.

    Short answer: use the two-step UPDATE + INSERT pattern inside one explicit transaction, with a single timestamp for the whole batch.

    BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @now datetime2 = SYSUTCDATETIME();   -- one timestamp for the whole batch

    UPDATE d SET d.valid_to = @now, d.is_current = 0
    FROM dbo.dim_customer AS d
    JOIN stg.customer AS s ON s.customer_id = d.customer_id
    WHERE d.is_current = 1 AND s.row_hash <> d.row_hash;

    INSERT INTO dbo.dim_customer (customer_id, name, row_hash, valid_from, valid_to, is_current)
    SELECT s.customer_id, s.name, s.row_hash, @now, NULL, 1
    FROM stg.customer AS s
    LEFT JOIN dbo.dim_customer AS d ON d.customer_id = s.customer_id AND d.is_current = 1
    WHERE d.customer_id IS NULL OR d.row_hash <> s.row_hash;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
    MERGE vs UPDATE+INSERT -> both supported; two statements are easier to maintain and troubleshoot
Atomicity              -> one transaction with TRY/CATCH and ROLLBACK, no half-updated dimension
Timestamps             -> capture SYSUTCDATETIME() once, reuse for valid_to and valid_from
Staging                -> exactly one row per business key; dedupe first
Volume                 -> process only changed keys (row hash), avoid frequent small writes
Concurrency            -> table-level locking in the Warehouse, so avoid concurrent writers
Validation             -> after load, each key has exactly one current row
Next step              -> benchmark against MERGE on realistic data volumes

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.