Building Robust Incremental Loads in MS-SQL for Power BI and Fabric

1. What “Robust” Incremental Load Really Means

Incremental load is more than “load only yesterday’s data”. For Power BI and Fabric, a robust pattern usually means:

• Idempotent: Running the same load twice produces the same result.
• Deterministic: Logic is based on clear rules (keys, timestamps, version numbers), not assumptions.
• Recoverable: Failures can be re-run without manual patching.
• Auditable: You can answer “what changed, when, and by whom?”

We’ll focus on a typical pattern:

• Staging table receives raw deltas.
• MERGE applies them to a core table.
• Change Tracking (or similar) drives what needs to be loaded.
• Error handling and logging wrap the whole thing.

2. Designing Your Incremental Load Tables

2.1 Core conventions

Before writing MERGE, get the table design right.

For each core table that feeds Power BI/Fabric:

• Primary key: A stable business key or surrogate key.
• Row versioning: Either:
  • ModifiedDate (datetime/datetime2), or
  • RowVersion (rowversion) column.
• Soft delete flag (optional but useful): e.g. IsDeleted BIT.
• Audit columns:
  • CreatedOn, CreatedBy
  • ModifiedOn, ModifiedBy

Example core table:

CREATE TABLE dbo.Customer
(
    CustomerID       INT            NOT NULL PRIMARY KEY,
    CustomerName     NVARCHAR(200)  NOT NULL,
    Email            NVARCHAR(200)  NULL,
    IsDeleted        BIT            NOT NULL CONSTRAINT DF_Customer_IsDeleted DEFAULT (0),
    ModifiedDate     DATETIME2(3)   NOT NULL,
    CreatedDate      DATETIME2(3)   NOT NULL,
    ModifiedBy       SYSNAME        NULL,
    CreatedBy        SYSNAME        NULL
);

2.2 Staging table design

Staging should be:

• Narrow: Only columns needed for the MERGE.
• Ephemeral: Truncated or replaced each run.
• Tagged: With batch metadata.

Example staging table for deltas:

CREATE TABLE stg.Customer_Delta
(
    BatchID          UNIQUEIDENTIFIER NOT NULL,
    CustomerID       INT              NOT NULL,
    CustomerName     NVARCHAR(200)    NOT NULL,
    Email            NVARCHAR(200)    NULL,
    OperationType    CHAR(1)          NOT NULL, -- 'I','U','D'
    SourceModifiedOn DATETIME2(3)     NOT NULL,
    LoadInsertedOn   DATETIME2(3)     NOT NULL DEFAULT (SYSUTCDATETIME())
);

OperationType is optional if you infer changes via timestamps, but it’s handy when your upstream system already flags inserts/updates/deletes.

3. MERGE Patterns for Incremental Loads

3.1 Basic MERGE template

A minimal MERGE from stg.Customer_Delta to dbo.Customer:

MERGE dbo.Customer AS tgt
USING (
    SELECT CustomerID,
           CustomerName,
           Email,
           OperationType,
           SourceModifiedOn
    FROM stg.Customer_Delta
    WHERE BatchID = @BatchID
) AS src
    ON tgt.CustomerID = src.CustomerID

WHEN MATCHED AND src.OperationType IN ('U') THEN
    UPDATE SET
        tgt.CustomerName = src.CustomerName,
        tgt.Email        = src.Email,
        tgt.ModifiedDate = src.SourceModifiedOn,
        tgt.ModifiedBy   = @UserName

WHEN MATCHED AND src.OperationType IN ('D') THEN
    UPDATE SET
        tgt.IsDeleted    = 1,
        tgt.ModifiedDate = src.SourceModifiedOn,
        tgt.ModifiedBy   = @UserName

WHEN NOT MATCHED BY TARGET AND src.OperationType IN ('I','U') THEN
    INSERT (CustomerID, CustomerName, Email, IsDeleted,
            CreatedDate, ModifiedDate, CreatedBy, ModifiedBy)
    VALUES (src.CustomerID, src.CustomerName, src.Email, 0,
            src.SourceModifiedOn, src.SourceModifiedOn, @UserName, @UserName)
;

Key points:

• Idempotence: If the same batch is re-run, results don’t break, because you always update based on key.
• Soft delete: OperationType = 'D' sets IsDeleted rather than physically deleting.

3.2 Avoiding classic MERGE pitfalls

MERGE has a reputation for subtle bugs when:

• The same key appears multiple times in the source.
• Constraints or triggers behave unexpectedly.
• Source filters are wrong.

Defensive patterns:

1. De-duplicate source before MERGE:

;WITH src AS
(
    SELECT
        CustomerID,
        MAX(SourceModifiedOn) AS SourceModifiedOn,
        MAX(OperationType)    AS OperationType,
        MAX(CustomerName)     AS CustomerName,
        MAX(Email)            AS Email
    FROM stg.Customer_Delta
    WHERE BatchID = @BatchID
    GROUP BY CustomerID
)
MERGE dbo.Customer AS tgt
USING src
    ON tgt.CustomerID = src.CustomerID
-- ... rest as before
;

2. Limit the MERGE scope to what changed:

• Filter staging by SourceModifiedOn > @LastSyncTime.
• Or by Change Tracking version (see next section).

3. Consider separate INSERT/UPDATE if your environment has MERGE-specific issues. The semantics are clearer:

-- Updates
UPDATE tgt
SET
    tgt.CustomerName = src.CustomerName,
    tgt.Email        = src.Email,
    tgt.ModifiedDate = src.SourceModifiedOn,
    tgt.ModifiedBy   = @UserName
FROM dbo.Customer tgt
JOIN stg.Customer_Delta src
    ON tgt.CustomerID = src.CustomerID
WHERE src.BatchID = @BatchID
  AND src.OperationType = 'U';

-- Inserts
INSERT dbo.Customer (CustomerID, CustomerName, Email, IsDeleted,
                     CreatedDate, ModifiedDate, CreatedBy, ModifiedBy)
SELECT
    src.CustomerID,
    src.CustomerName,
    src.Email,
    0,
    src.SourceModifiedOn,
    src.SourceModifiedOn,
    @UserName,
    @UserName
FROM stg.Customer_Delta src
LEFT JOIN dbo.Customer tgt
    ON tgt.CustomerID = src.CustomerID
WHERE src.BatchID = @BatchID
  AND src.OperationType IN ('I','U')
  AND tgt.CustomerID IS NULL;

4. Using Change Tracking to Drive Incrementals

Change Tracking is a lightweight feature that tells you which rows changed since a given version. It’s ideal for driving incremental loads that feed Power BI/Fabric.

4.1 Enabling Change Tracking

At database level:

ALTER DATABASE YourDb
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

At table level:

ALTER TABLE dbo.Customer
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

4.2 Reading changes since last sync

Maintain a control table for last sync version:

CREATE TABLE dbo.ETL_Control
(
    ProcessName   SYSNAME         NOT NULL PRIMARY KEY,
    LastSyncVersion BIGINT        NOT NULL,
    LastRunTime   DATETIME2(3)    NOT NULL
);

Example stored procedure to pull changes:

CREATE OR ALTER PROCEDURE etl.Customer_ExtractChanges
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LastVersion BIGINT;
    DECLARE @CurrentVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

    SELECT @LastVersion = LastSyncVersion
    FROM dbo.ETL_Control
    WHERE ProcessName = 'Customer';

    IF @LastVersion IS NULL
        SET @LastVersion = 0; -- first run

    -- Extract changes
    SELECT
        ct.CustomerID,
        c.CustomerName,
        c.Email,
        CASE
            WHEN c.CustomerID IS NULL THEN 'D'
            WHEN ct.SYS_CHANGE_OPERATION = 'I' THEN 'I'
            ELSE 'U'
        END AS OperationType,
        ISNULL(c.ModifiedDate, SYSUTCDATETIME()) AS SourceModifiedOn
    FROM CHANGETABLE (CHANGES dbo.Customer, @LastVersion) AS ct
    LEFT JOIN dbo.Customer c
        ON c.CustomerID = ct.CustomerID;

    -- Update control
    MERGE dbo.ETL_Control AS tgt
    USING (SELECT 'Customer' AS ProcessName, @CurrentVersion AS LastSyncVersion) AS src
        ON tgt.ProcessName = src.ProcessName
    WHEN MATCHED THEN
        UPDATE SET
            LastSyncVersion = src.LastSyncVersion,
            LastRunTime     = SYSUTCDATETIME()
    WHEN NOT MATCHED THEN
        INSERT (ProcessName, LastSyncVersion, LastRunTime)
        VALUES (src.ProcessName, src.LastSyncVersion, SYSUTCDATETIME());
END;

You can load this result into stg.Customer_Delta and then run your MERGE.

4.3 Change Tracking vs. timestamps

Change Tracking is usually better than naive timestamp filters when:

• You can’t trust source clocks.
• You need to detect deletes reliably.
• Multiple systems write to the same table.

Timestamps are simpler when:

• You control the source system.
• You just need “new or changed since last run” and don’t care about deletes.

5. Wiring Incremental Loads to Power BI and Fabric

5.1 Power BI incremental refresh with SQL

A common pattern:

1. Create a fact table in SQL that is already incremental (e.g. partitioned by date).
2. In Power BI, enable incremental refresh on the table.
3. Parameterize your SQL query to filter by date range.

Example parameterized query for Power BI:

SELECT
    f.DateKey,
    f.CustomerID,
    f.SalesAmount
FROM dbo.FactSales f
WHERE f.DateKey >= @RangeStart
  AND f.DateKey <  @RangeEnd;

Power BI will substitute @RangeStart and @RangeEnd during refresh.

5.2 Fabric Lakehouse / Warehouse

In Fabric, your MS-SQL patterns still apply, but:

• You may land data into Lakehouse tables first.
• Then use Warehouse (T-SQL) to apply MERGE logic.
• Power BI sits on top of Warehouse or Lakehouse.

Keep the same principles:

• Staging (raw) vs. core (modeled) tables.
• Idempotent MERGE.
• Change Tracking or versioning where supported.

6. Error Handling and Logging Around MERGE

MERGE is powerful, but you need guardrails. Wrap it in stored procedures with explicit error handling.

6.1 Basic TRY/CATCH pattern

CREATE TABLE dbo.ETL_Log
(
    LogID        INT IDENTITY(1,1) PRIMARY KEY,
    ProcessName  SYSNAME,
    BatchID      UNIQUEIDENTIFIER NULL,
    StepName     NVARCHAR(200),
    Status       NVARCHAR(20),   -- 'START','SUCCESS','FAIL'
    Message      NVARCHAR(4000),
    ErrorNumber  INT NULL,
    ErrorLine    INT NULL,
    LoggedAt     DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);

Stored procedure with logging:

CREATE OR ALTER PROCEDURE etl.Customer_LoadIncremental
    @BatchID UNIQUEIDENTIFIER,
    @UserName SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        INSERT dbo.ETL_Log (ProcessName, BatchID, StepName, Status, Message)
        VALUES ('Customer', @BatchID, 'MERGE', 'START', 'Starting Customer MERGE');

        BEGIN TRAN;

        -- MERGE or separate INSERT/UPDATE as shown earlier
        -- ...

        COMMIT TRAN;

        INSERT dbo.ETL_Log (ProcessName, BatchID, StepName, Status, Message)
        VALUES ('Customer', @BatchID, 'MERGE', 'SUCCESS', 'Customer MERGE completed successfully');
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRAN;

        INSERT dbo.ETL_Log
        (
            ProcessName, BatchID, StepName, Status, Message,
            ErrorNumber, ErrorLine
        )
        VALUES
        (
            'Customer', @BatchID, 'MERGE', 'FAIL', ERROR_MESSAGE(),
            ERROR_NUMBER(), ERROR_LINE()
        );

        THROW; -- re-throw to surface error to caller (e.g. Fabric pipeline)
    END CATCH;
END;

6.2 Practical error-handling tips

• Fail fast: Validate staging data (e.g. null keys, duplicates) before MERGE.
• Separate technical vs. business errors:
  • Technical: constraint violations, timeouts.
  • Business: unexpected codes, invalid references.
• Expose logs:
  • Surface ETL_Log in a simple Power BI report.
  • Filter by Status = 'FAIL' for quick triage.

7. Practical Takeaway: Start with One Table, End-to-End

Don’t try to redesign your whole warehouse at once. Pick one critical table that feeds Power BI or Fabric and:

1. Add proper keys, versioning, and audit columns.
2. Introduce a staging table and a clean MERGE (or separate INSERT/UPDATE) pattern.
3. Wrap it in a stored procedure with TRY/CATCH and logging.
4. Hook it into Power BI/Fabric with incremental refresh.

Once that path is stable and observable, you can replicate the pattern across other tables with far less risk and far more confidence.