Build Incremental SQL Server ETL

Build an audited SQL Server pipeline that incrementally loads warehouse changes.

Introduction

30 Second Summary

Copying every customer record again can look harmless until the same row arrives twice. One duplicate can stop the load and leave you unsure what was saved.

In this project, you will build a reliable customer-data pipeline in SQL Server. The pipeline loads only new or changed rows while preserving a trustworthy record of every run.

What You'll Build

You'll see an incremental ETL run load only changed customers into the warehouse, backed by a completed audit record.

By the end of this project, you'll have:

  • A working warehouse load that copies three customer rows into a dimension you can query immediately.
  • An incremental loading demo powered by Change Tracking. You'll update one customer and add another customer before seeing only those affected rows processed.
  • A failure and recovery proof where invalid data leaves the warehouse untouched. After correcting the source row, you'll recover cleanly and prove idempotency with a successful zero-change rerun.
  • Secret Mission: Convert a deleted source customer into a soft deactivation that preserves warehouse history.

Are there any prerequisites?

You need access to an existing SQL Server instance with permission to create and alter a database. You should also understand basic SELECT, INSERT, and UPDATE statements.

Before We Start

This checkpoint defines the engineering goal before any hands-on work begins. You are building a reliable incremental SQL Server warehouse pipeline with safe recovery and traceable runs.

Set Up Your SQL Workspace

Your pipeline needs a dependable place to save each script. It also needs a live connection where every T-SQL result is visible.

Visual Studio Code gives you a cross-platform workspace. The SQL Server (mssql) extension connects that workspace to your existing SQL Server instance.

In this step, get ready to:
  • Install Visual Studio Code with the SQL Server extension.
  • Connect Visual Studio Code to your existing SQL Server instance.
  • Run a saved query that returns the current database name.
Prepare Visual Studio Code

Extensions add tools for specific development workflows. The MSSQL extension adds a connection interface plus support for running SQL queries.

Why Visual Studio Code?

Visual Studio Code runs on Windows, macOS, and Linux. That gives this project one workflow across operating systems.

SQL Server Management Studio remains compatible on Windows. The extension keeps the primary project path cross-platform.

Your next path depends on whether Visual Studio Code is already installed.

✔️ Visual Studio Code is installed

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your application search.
  • On Linux, open your distribution's application launcher.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.

Visual Studio Code is ready for the SQL Server extension.

ⓧ I need to install Visual Studio Code

macOS

  • Open the official Visual Studio Code download page.
  • Select the .dmg download that matches your Mac.
  • Open the downloaded .dmg file.
  • Drag Visual Studio Code.app to the Applications folder.
  • Press Cmd+Space to open Spotlight.
  • Type Visual Studio Code into Spotlight.
  • Press Enter to open Visual Studio Code.

Windows

  • Open the official Visual Studio Code download page.
  • Select the User Installer for your Windows system.
  • Run the downloaded installer.
  • Follow the installer prompts to complete the installation.
  • Press the Windows key to open search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.

Linux

  • Open the official Visual Studio Code download page.
  • Select the .deb package for Debian or Ubuntu.
  • Select the .rpm package for Red Hat, Fedora, or SUSE.
  • Run the downloaded package with your distribution's package installer.
  • Use your application launcher to open Visual Studio Code.
  • Select the Extensions icon in the Activity Bar.
  • Enter mssql in the Extensions search box.

The search results include the extension used for this project.

  • Select SQL Server (mssql) from the search results.
  • Select Install on the extension page.

Good progress. The MSSQL icon now appears in the Activity Bar with a Connections view for your database sessions.

Missing the MSSQL icon?

  • Confirm that the installed extension is SQL Server (mssql).
  • Reload Visual Studio Code if the extension page still shows Install.
  • Ask for targeted help with the extension installation. Help me troubleshoot why the SQL Server (mssql) extension is not appearing in Visual Studio Code.
Connect to your SQL Server instance

A saved connection lets each query editor send T-SQL to your existing server. The Connection dialog collects the server details plus the matching authentication method.

  • Select the MSSQL icon in the Activity Bar.
  • Select Add Connection under Connections.

The Connection dialog opens with fields for your existing SQL Server instance.

  • Keep Input type set to Parameters.
  • Enter your existing instance address in Server name.

The dialog now targets the server that hosts your database.

This flow keeps your credentials out of every SQL file.

  • Choose the Authentication type that your SQL Server instance uses.
  • Enter the requested credentials for that authentication method.
  • Select Test connection.

A successful test confirms that the current server details can establish a database session.

  • Select Connect.

That is the sensitive part handled. Your server now appears under Connections in the MSSQL view.

Connection not completing?

  • Confirm that Server name matches the address used by your existing SQL tools.
  • Confirm that Authentication type matches the login configured for your instance.
  • Check that the SQL Server instance is running before testing again.

Need help narrowing it down? Help me diagnose my SQL Server connection from the MSSQL extension.

Create and test the query file

A dedicated folder keeps the project's scripts together. The first file confirms that your editor can execute a saved query against the active connection.

  • Select File from the Visual Studio Code menu.
  • Select Open Folder....

Your operating system's folder picker opens.

  • Choose your Desktop as the location.
  • Select New Folder.

A field appears for the new folder's name.

  • Enter retail-ops-pipeline as the folder name.
  • Select the new retail-ops-pipeline folder.

The folder picker now points to the project folder that stores every SQL script.

  • Select Open on macOS or Select Folder on Windows and Linux.
  • Select Yes, I trust the authors to enable all features in the workspace. if the Workspace Trust dialog appears.

Your Explorer sidebar now shows retail-ops-pipeline as the workspace folder.

  • Select the Explorer icon in the Activity Bar.
  • Select New File... in the Explorer view.

A file-name field opens inside the project folder.

  • Enter connection-check.sql as the file name.
  • Add the current database check to connection-check.sql by pasting this query:
SELECT DB_NAME() AS CurrentDatabase;

What does this query do?

  • The DB_NAME() function returns the name of the database used by the active connection.
  • The CurrentDatabase alias gives the result column a clear label.
  • Press Cmd+S (macOS) or Ctrl+S (Windows and Linux) to save connection-check.sql.
  • Choose the saved server from the connection control above the editor if the file shows no active connection.

Before you run the query, what database name do you expect the active connection to return?

  • Press Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows and Linux) to open the Command Palette.
  • Select MS SQL: Execute Query.

You will see one result row under CurrentDatabase. Its value is the current database name for your active SQL Server connection.

Query result not appearing?

  • Confirm that connection-check.sql is connected to the server listed in the MSSQL view.
  • Check that the query matches the reference below exactly.
  • Reconnect to the saved server if the editor reports that no connection is active.

Still stuck? Help me troubleshoot why DB_NAME() is not returning a result in the MSSQL extension.

✔️ Awesome, I've got everything!

Great. Double-check that you saved connection-check.sql inside retail-ops-pipeline.

ⓧ I'd like to double check the full code

SELECT DB_NAME() AS CurrentDatabase;

Your SQL workspace is ready to save scripts and show their results. Next, you will build the isolated retail lab and watch a naive warehouse reload fail.

Watch a Naive Reload Fail

Your Visual Studio Code workspace can now send T-SQL to SQL Server. Next, you will use that connection to create an isolated retail lab.

A naive full reload copies every source row each time it runs. Its first success cannot prove the load is restartable.

In this step, get ready to:
  • Build an isolated retail source with a constrained warehouse dimension.
  • Run the full-copy load twice to expose its missing state.
  • Clean up the dimension so the incremental pipeline starts from a known state.
Build the isolated retail lab

An isolated database keeps the experiment away from existing objects. Five schemas give each pipeline responsibility a clear home.

  • Select the New File icon in the Explorer toolbar for retail-ops-pipeline.
  • Enter 01_setup.sql as the file name.
  • Press Enter to create the file.
  • Attach the SQL Server connection from earlier using the connection control in the query editor.
  • Create the database guard and five schemas by pasting this code into 01_setup.sql:
USE master;
GO

IF DB_ID(N'RetailOpsLab') IS NOT NULL
BEGIN
    ;THROW 50000, N'RetailOpsLab already exists. Delete it or choose a fresh SQL Server instance before running setup.', 1;
END;
GO

CREATE DATABASE RetailOpsLab;
GO

USE RetailOpsLab;
GO

CREATE SCHEMA src;
GO
CREATE SCHEMA stage;
GO
CREATE SCHEMA dw;
GO
CREATE SCHEMA audit;
GO
CREATE SCHEMA etl;
GO

What does this setup create?

  • The existence guard stops the script before it can reuse an earlier RetailOpsLab database.
  • The CREATE DATABASE statement creates a safe home for the experiment.
  • The five schemas separate source data from staging data.
  • The remaining schemas separate warehouse objects from audit records and ETL logic.
  • Save 01_setup.sql.
  • Select all the current text from USE master; through the final GO.
  • Click Run Query in the query editor toolbar.

You will see the batches finish without an error. The new database now contains five empty schemas.

Database creation blocked?

Confirm that 01_setup.sql uses the active connection from earlier. Your SQL Server login also needs permission to create a database.

If the guard reports that the lab already exists, stop before continuing. Help me diagnose why RetailOpsLab cannot be created.

  • Add the source customer table to the end of 01_setup.sql by pasting this code:
CREATE TABLE src.Customer
(
    CustomerID INT NOT NULL,
    FullName NVARCHAR(100) NULL,
    City NVARCHAR(100) NOT NULL,
    IsActive BIT NOT NULL,
    CONSTRAINT PK_src_Customer PRIMARY KEY (CustomerID)
);
GO

What does the source table store?

  • The CustomerID primary key gives each operational customer one stable identifier.
  • The nullable FullName column leaves room for the quality failure you will test later.
  • The City column stores the customer location.
  • The IsActive column records whether the source customer is active.
  • Save 01_setup.sql.
  • Select the newly added CREATE TABLE src.Customer batch through its GO line.
  • Click Run Query in the query editor toolbar.

You will see the selected batch finish without an error. The operational source table is now ready for customer rows.

Source table not created?

Confirm that the selected batch includes its closing parenthesis. Check that the editor remains connected to the same SQL Server instance.

A schema-related error usually means the first batch did not finish. Help me troubleshoot the src.Customer table creation.

  • Add the constrained warehouse dimension to the end of 01_setup.sql by pasting this code:
CREATE TABLE dw.DimCustomer
(
    CustomerKey INT IDENTITY(1, 1) NOT NULL,
    CustomerID INT NOT NULL,
    FullName NVARCHAR(100) NOT NULL,
    City NVARCHAR(100) NOT NULL,
    IsActive BIT NOT NULL,
    LoadedAt DATETIME2(0) NOT NULL
        CONSTRAINT DF_dw_DimCustomer_LoadedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_dw_DimCustomer PRIMARY KEY (CustomerKey),
    CONSTRAINT UQ_dw_DimCustomer_CustomerID UNIQUE (CustomerID)
);
GO

What protects the dimension?

  • The identity-based CustomerKey gives each dimension row a warehouse key.
  • The LoadedAt default records the UTC load time.
  • The unique constraint named UQ_dw_DimCustomer_CustomerID prevents the same source customer from appearing twice.
  • Save 01_setup.sql.
  • Select the newly added CREATE TABLE dw.DimCustomer batch through its GO line.
  • Click Run Query in the query editor toolbar.

You will see the selected batch finish without an error. The empty dimension now enforces one row per source customer.

Dimension table not created?

Confirm that the table name starts with dw.. Check that every constraint name matches the code block.

If SQL Server reports an existing object, avoid rerunning the same table batch. Help me troubleshoot the dw.DimCustomer table creation.

  • Add the three source customers to the end of 01_setup.sql by pasting this code:
INSERT INTO src.Customer (CustomerID, FullName, City, IsActive)
VALUES
    (1, N'Amina Yusuf', N'Seattle', 1),
    (2, N'Marco Silva', N'Denver', 1),
    (3, N'Priya Shah', N'Atlanta', 1);
GO

What does this seed data provide?

These three rows give the source table a small repeatable dataset. Their stable customer identifiers make the second full copy easy to test.

  • Save 01_setup.sql.
  • Select the newly added INSERT INTO src.Customer batch through its GO line.
  • Click Run Query in the query editor toolbar.

You will see the insert finish without an error. The source now contains the three seed customers.

Seed insert rejected?

Confirm that the source table is empty before running this batch. A duplicate primary-key error means the seed batch already ran.

Check each value against the matching column order. Help me troubleshoot the src.Customer seed insert.

  • Add the source verification query to the end of 01_setup.sql by pasting this code:
SELECT CustomerID, FullName, City, IsActive
FROM src.Customer
ORDER BY CustomerID;
GO

What does this query verify?

The query reads every seeded customer in identifier order. Its result proves that the source side of the lab is ready.

  • Save 01_setup.sql.
  • Select the newly added SELECT CustomerID batch through its GO line.
  • Click Run Query in the query editor toolbar.

You will see Amina Yusuf in Seattle, Marco Silva in Denver, and Priya Shah in Atlanta. The result contains customer identifiers 1 through 3.

Missing a seed customer?

Confirm that you ran the complete seed insert before the verification query. Check that the query reads from src.Customer.

A blank result means the insert batch did not complete. Help me find the missing seed rows.

✔️ Awesome, I've got everything!

Great. Double-check that you saved 01_setup.sql after the source query.

ⓧ I'd like to double check the full code

USE master;
GO

IF DB_ID(N'RetailOpsLab') IS NOT NULL
BEGIN
    ;THROW 50000, N'RetailOpsLab already exists. Delete it or choose a fresh SQL Server instance before running setup.', 1;
END;
GO

CREATE DATABASE RetailOpsLab;
GO

USE RetailOpsLab;
GO

CREATE SCHEMA src;
GO
CREATE SCHEMA stage;
GO
CREATE SCHEMA dw;
GO
CREATE SCHEMA audit;
GO
CREATE SCHEMA etl;
GO

CREATE TABLE src.Customer
(
    CustomerID INT NOT NULL,
    FullName NVARCHAR(100) NULL,
    City NVARCHAR(100) NOT NULL,
    IsActive BIT NOT NULL,
    CONSTRAINT PK_src_Customer PRIMARY KEY (CustomerID)
);
GO

CREATE TABLE dw.DimCustomer
(
    CustomerKey INT IDENTITY(1, 1) NOT NULL,
    CustomerID INT NOT NULL,
    FullName NVARCHAR(100) NOT NULL,
    City NVARCHAR(100) NOT NULL,
    IsActive BIT NOT NULL,
    LoadedAt DATETIME2(0) NOT NULL
        CONSTRAINT DF_dw_DimCustomer_LoadedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_dw_DimCustomer PRIMARY KEY (CustomerKey),
    CONSTRAINT UQ_dw_DimCustomer_CustomerID UNIQUE (CustomerID)
);
GO

INSERT INTO src.Customer (CustomerID, FullName, City, IsActive)
VALUES
    (1, N'Amina Yusuf', N'Seattle', 1),
    (2, N'Marco Silva', N'Denver', 1),
    (3, N'Priya Shah', N'Atlanta', 1);
GO

SELECT CustomerID, FullName, City, IsActive
FROM src.Customer
ORDER BY CustomerID;
GO
Run the first full copy

The simplest warehouse load copies every current source row into the dimension. One successful result can hide how the same logic behaves during a restart.

  • Select the New File icon in the Explorer toolbar for retail-ops-pipeline.
  • Enter 02_naive_load.sql as the file name.
  • Press Enter to create the file.
  • Attach the SQL Server connection from earlier using the connection control in the query editor.
  • Add the first full-copy load to 02_naive_load.sql by pasting this code:
USE RetailOpsLab;
GO

INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
SELECT CustomerID, FullName, City, IsActive
FROM src.Customer;

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;
GO

What does the first load do?

  • The USE RetailOpsLab batch directs the remaining statements to the lab database.
  • The insert copies every current source customer into the dimension.
  • The final query returns the warehouse rows in customer order.
  • Save 02_naive_load.sql.
  • Select all the current text in 02_naive_load.sql.
  • Click Run Query in the query editor toolbar.

You will see three rows in dw.DimCustomer. Your first warehouse load now works.

First load not showing three rows?

Confirm that the selected script starts with USE RetailOpsLab;. Check that the setup query returned all three source customers.

If the dimension already contains rows, avoid running this first batch again. Help me troubleshoot the first naive warehouse load.

  • Add the controlled second full-copy attempt to the end of 02_naive_load.sql by pasting this code:
BEGIN TRY
    INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
    SELECT CustomerID, FullName, City, IsActive
    FROM src.Customer;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ExpectedReloadFailure;
END CATCH;
GO

What does the controlled attempt do?

  • The TRY block repeats the same full-copy insert against the populated dimension.
  • The CATCH block returns the database message through ExpectedReloadFailure if SQL Server rejects the insert.
  • Save 02_naive_load.sql.
  • Select the newly added BEGIN TRY batch through its GO line.

Before you run the second load, do you expect SQL Server to accept the same three customer identifiers again?

  • Click Run Query in the query editor toolbar.

You will see a controlled duplicate-key message naming UQ_dw_DimCustomer_CustomerID. This failure is intentional.

Why did the reload fail?

The full-copy statement cannot distinguish existing customers from new customers. The unique constraint protects the dimension by rejecting duplicate identifiers.

A production load needs a saved position or another state-aware rule. That state makes the load idempotent across retries.

Did the second load succeed?

Confirm that the first load still has three rows in dw.DimCustomer. The duplicate test needs those existing rows.

Check that the second insert still copies all rows from src.Customer. Help me diagnose why the duplicate-key test did not fail.

Clean up and confirm the empty dimension

The rejected statement leaves the original three warehouse rows intact. Clearing those rows gives the next pipeline stage a known starting point.

  • Add the cleanup and row-count check to the end of 02_naive_load.sql by pasting this code:
TRUNCATE TABLE dw.DimCustomer;

SELECT COUNT(*) AS DimensionRowsAfterCleanup
FROM dw.DimCustomer;
GO

What does the cleanup prove?

  • The TRUNCATE TABLE statement removes every row from the dimension.
  • The count query returns the remaining row total through DimensionRowsAfterCleanup.
  • Save 02_naive_load.sql.
  • Select the newly added TRUNCATE TABLE dw.DimCustomer; batch through its GO line.

Before you run the cleanup, what row count should remain after the dimension is cleared?

  • Click Run Query in the query editor toolbar.

You will see DimensionRowsAfterCleanup return 0. The source still retains its three seed customers.

Dimension count above zero?

Confirm that the selected batch starts with TRUNCATE TABLE dw.DimCustomer;. Check that the count query reads from the same table.

If another query is using the dimension, wait for that query to finish before retrying. Help me troubleshoot the dimension cleanup.

✔️ Awesome, I've got everything!

Excellent. Your controlled failure is captured in 02_naive_load.sql. The dimension is empty for the next step.

ⓧ I'd like to double check the full code

USE RetailOpsLab;
GO

INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
SELECT CustomerID, FullName, City, IsActive
FROM src.Customer;

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;
GO

BEGIN TRY
    INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
    SELECT CustomerID, FullName, City, IsActive
    FROM src.Customer;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ExpectedReloadFailure;
END CATCH;
GO

TRUNCATE TABLE dw.DimCustomer;

SELECT COUNT(*) AS DimensionRowsAfterCleanup
FROM dw.DimCustomer;
GO

You have proved that a successful first copy does not make a reload restartable. Next, you will give the pipeline a change feed and a durable synchronization baseline.

Establish a Change Tracking Baseline

The duplicate-key test proved that repeatedly copying every source row cannot recover cleanly. Your empty dimension is ready for a safer starting point.

Microsoft SQL Server Change Tracking supplies changed keys after a saved version. Snapshot isolation keeps the initial warehouse rows aligned with that saved version.

In this step, get ready to:
  • Enable Change Tracking with automatic cleanup.
  • Create staging storage plus pipeline audit tables.
  • Load the initial dimension inside a snapshot transaction.
Enable Change Tracking and snapshot isolation

Change Tracking records which source keys changed after a synchronization version. The two-day retention window gives this lab time to process those changes before automatic cleanup removes old tracking information.

Why Change Tracking?

This pipeline needs the latest customer values for each changed key. Change Data Capture stores more detailed history for use cases that need previous values.

Change Tracking fits an incremental warehouse synchronization with less storage overhead. The source table remains responsible for the current customer values.

  • In the Visual Studio Code Explorer sidebar, select the retail-ops-pipeline folder.
  • Click the New File icon in the Explorer toolbar.
  • Enter 03_enable_and_initialize.sql as the file name.
  • Add the database-level settings plus table-level tracking by pasting this code:
USE master;
GO

ALTER DATABASE RetailOpsLab
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

ALTER DATABASE RetailOpsLab
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE RetailOpsLab;
GO

ALTER TABLE src.Customer
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
GO

What does this configuration do?

  • The first database setting enables Change Tracking with a retention period of 2 DAYS.
  • The AUTO_CLEANUP = ON setting removes tracking information outside the retention window.
  • The ALLOW_SNAPSHOT_ISOLATION setting permits consistent snapshot transactions.
  • The table setting tracks row changes without storing a per-column change mask because TRACK_COLUMNS_UPDATED = OFF.
  • Save 03_enable_and_initialize.sql.
  • Highlight the code from USE master through the final GO.
  • Execute the selected batches with the query editor control.

You should see every selected batch complete without an error. RetailOpsLab can now track committed changes to src.Customer.

Database settings not applying?

Confirm that your connected account can alter RetailOpsLab. Database-level settings require sufficient permission on the SQL Server instance.

Check that the selected code starts in master before it alters the database. Help me troubleshoot the Change Tracking setup.

Create staging and audit tables

A staging table holds each extracted batch while the loader processes it. Audit logging separates the saved synchronization state from the history of individual runs.

  • Place the cursor after the final GO in 03_enable_and_initialize.sql.
  • Add the transient customer-change storage by pasting this code:
CREATE TABLE stage.CustomerChanges
(
    CustomerID INT NOT NULL,
    SysChangeVersion BIGINT NOT NULL,
    SysChangeOperation NCHAR(1) NOT NULL,
    FullName NVARCHAR(100) NULL,
    City NVARCHAR(100) NULL,
    IsActive BIT NULL,
    CONSTRAINT PK_stage_CustomerChanges PRIMARY KEY (CustomerID)
);
GO

What does this table store?

  • The CustomerID column identifies the affected source row.
  • The SysChangeVersion column stores the version associated with that change.
  • The SysChangeOperation column records the tracked operation.
  • The remaining columns hold the current customer values used by the warehouse load.
  • Save 03_enable_and_initialize.sql.
  • Highlight the new CREATE TABLE stage.CustomerChanges batch.
  • Execute the selected batch with the query editor control.

The batch should complete without an error. The pipeline now has a dedicated table for one set of customer changes.

Stage table not created?

Confirm that the preceding batch switched the connection context to RetailOpsLab. Check that the schema name is stage.

A previous partial run may have created the table already. Help me diagnose the staging-table failure.

  • Add the persisted synchronization state below the staging table by pasting this code:
CREATE TABLE audit.PipelineState
(
    PipelineName NVARCHAR(100) NOT NULL,
    LastSyncVersion BIGINT NOT NULL,
    LastSuccessAt DATETIME2(0) NULL,
    CONSTRAINT PK_audit_PipelineState PRIMARY KEY (PipelineName)
);
GO

What does the state table preserve?

  • The PipelineName column gives each pipeline one state record.
  • The LastSyncVersion column is the durable high-water mark for the next extraction.
  • The LastSuccessAt column records when that baseline last advanced successfully.
  • Save 03_enable_and_initialize.sql.
  • Highlight the new CREATE TABLE audit.PipelineState batch.
  • Execute the selected batch with the query editor control.

The state-table batch should complete without an error. RetailOpsLab can now preserve a synchronization version across separate pipeline runs.

State table not created?

Confirm that audit is the schema name. Check that PipelineName is the primary-key column.

If the object already exists from a partial run, avoid executing the same creation batch again. Help me inspect the pipeline state table.

  • Add the per-run audit history below the state table by pasting this code:
CREATE TABLE audit.PipelineRun
(
    RunID BIGINT IDENTITY(1, 1) NOT NULL,
    PipelineName NVARCHAR(100) NOT NULL,
    StartedAt DATETIME2(0) NOT NULL
        CONSTRAINT DF_audit_PipelineRun_StartedAt DEFAULT SYSUTCDATETIME(),
    CompletedAt DATETIME2(0) NULL,
    Status VARCHAR(10) NOT NULL,
    LastSyncVersion BIGINT NULL,
    NextSyncVersion BIGINT NULL,
    RowsStaged INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsStaged DEFAULT (0),
    RowsInserted INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsInserted DEFAULT (0),
    RowsUpdated INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsUpdated DEFAULT (0),
    ErrorMessage NVARCHAR(4000) NULL,
    CONSTRAINT PK_audit_PipelineRun PRIMARY KEY (RunID)
);
GO

What does the run table capture?

  • The identity-based RunID distinguishes each loader execution.
  • The timestamps plus Status describe the run lifecycle.
  • The synchronization-version columns record the range considered by a run.
  • The row counters plus ErrorMessage provide evidence about the outcome.
  • Save 03_enable_and_initialize.sql.
  • Highlight the new CREATE TABLE audit.PipelineRun batch.
  • Execute the selected batch with the query editor control.

The audit-table batch should complete without an error. The next step can record a durable result for every loader execution.

Audit table not created?

Check that each default constraint has a unique name. Confirm that every opening parenthesis has a matching closing parenthesis.

A partial execution may have created audit.PipelineRun already. Help me diagnose the pipeline audit-table failure.

Initialize the dimension and save the baseline

The first synchronization needs source rows plus a version that marks their extraction point. A snapshot transaction commits both pieces as one consistent starting state.

  • Add the initial dimension load below the audit tables by pasting this code:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

DECLARE @InitialVersion BIGINT;
SET @InitialVersion = CHANGE_TRACKING_CURRENT_VERSION();

INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
SELECT CustomerID, FullName, City, IsActive
FROM src.Customer;

INSERT INTO audit.PipelineState
    (PipelineName, LastSyncVersion, LastSuccessAt)
VALUES
    (N'CustomerDimension', @InitialVersion, SYSUTCDATETIME());

COMMIT TRANSACTION;
GO

How does the baseline stay consistent?

  • Snapshot isolation gives the transaction one consistent view of the source rows.
  • The CHANGE_TRACKING_CURRENT_VERSION() value marks the latest committed Change Tracking transaction.
  • The dimension insert copies the three seed customers from src.Customer.
  • The state insert saves that version under CustomerDimension before the transaction commits.
  • Save 03_enable_and_initialize.sql.
  • Highlight the snapshot transaction from SET TRANSACTION ISOLATION LEVEL SNAPSHOT through its final GO.
  • Execute the selected transaction with the query editor control.

The transaction should complete without an error. The three source customers plus one synchronization baseline are now committed together.

Initial load not completing?

Confirm that dw.DimCustomer was empty after the naive-load cleanup. Existing customer IDs can trigger its unique constraint.

Check that snapshot isolation was enabled before this transaction started. Help me troubleshoot the baseline transaction.

Before you verify the result, predict how many dimension rows plus state rows the initialization should have created.

  • Add the end-of-step verification queries below the transaction by pasting this code:
SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       min_valid_version
FROM sys.change_tracking_tables;
GO

What do these checks prove?

  • The first result set proves that the snapshot initial load copied all three customers.
  • The second result set proves that CustomerDimension has a numeric LastSyncVersion plus a success timestamp.
  • The final result set proves that Change Tracking is enabled for the source customer table.
  • Save 03_enable_and_initialize.sql.
  • Highlight the three verification queries.
  • Execute the selected queries with the query editor control.

You should see customers 1 through 3 in the dimension. A separate result set should show one CustomerDimension row with a numeric synchronization version.

The Change Tracking result should name schema src plus table Customer. Its min_valid_version value should be numeric.

Verification results look incomplete?

Confirm that you executed the snapshot transaction before running the verification queries. The dimension plus state rows are created inside that transaction.

Check that the connection is using RetailOpsLab when you inspect the Change Tracking catalog. Help me diagnose the baseline verification.

✔️ Awesome, I've got everything!

Great. Double-check that you saved 03_enable_and_initialize.sql with every batch in order.

ⓧ I'd like to double check the full code

USE master;
GO

ALTER DATABASE RetailOpsLab
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

ALTER DATABASE RetailOpsLab
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE RetailOpsLab;
GO

ALTER TABLE src.Customer
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
GO

CREATE TABLE stage.CustomerChanges
(
    CustomerID INT NOT NULL,
    SysChangeVersion BIGINT NOT NULL,
    SysChangeOperation NCHAR(1) NOT NULL,
    FullName NVARCHAR(100) NULL,
    City NVARCHAR(100) NULL,
    IsActive BIT NULL,
    CONSTRAINT PK_stage_CustomerChanges PRIMARY KEY (CustomerID)
);
GO

CREATE TABLE audit.PipelineState
(
    PipelineName NVARCHAR(100) NOT NULL,
    LastSyncVersion BIGINT NOT NULL,
    LastSuccessAt DATETIME2(0) NULL,
    CONSTRAINT PK_audit_PipelineState PRIMARY KEY (PipelineName)
);
GO

CREATE TABLE audit.PipelineRun
(
    RunID BIGINT IDENTITY(1, 1) NOT NULL,
    PipelineName NVARCHAR(100) NOT NULL,
    StartedAt DATETIME2(0) NOT NULL
        CONSTRAINT DF_audit_PipelineRun_StartedAt DEFAULT SYSUTCDATETIME(),
    CompletedAt DATETIME2(0) NULL,
    Status VARCHAR(10) NOT NULL,
    LastSyncVersion BIGINT NULL,
    NextSyncVersion BIGINT NULL,
    RowsStaged INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsStaged DEFAULT (0),
    RowsInserted INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsInserted DEFAULT (0),
    RowsUpdated INT NOT NULL
        CONSTRAINT DF_audit_PipelineRun_RowsUpdated DEFAULT (0),
    ErrorMessage NVARCHAR(4000) NULL,
    CONSTRAINT PK_audit_PipelineRun PRIMARY KEY (RunID)
);
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

DECLARE @InitialVersion BIGINT;
SET @InitialVersion = CHANGE_TRACKING_CURRENT_VERSION();

INSERT INTO dw.DimCustomer (CustomerID, FullName, City, IsActive)
SELECT CustomerID, FullName, City, IsActive
FROM src.Customer;

INSERT INTO audit.PipelineState
    (PipelineName, LastSyncVersion, LastSuccessAt)
VALUES
    (N'CustomerDimension', @InitialVersion, SYSUTCDATETIME());

COMMIT TRANSACTION;
GO

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       min_valid_version
FROM sys.change_tracking_tables;
GO

That is the baseline locked in. Next, you will turn these building blocks into one audited incremental loader.

Build the Audited Incremental Loader

Your SQL Server lab now has a saved synchronization baseline. The next goal is a reusable incremental ETL loader that turns later source changes into measured warehouse work.

Change Tracking reports changed keys. Those keys need current source values before the warehouse can use them.

The loader validates its starting version before staging rows. Snapshot isolation gives each run a consistent view.

A transaction keeps warehouse writes tied to state advancement. Audit logging preserves each final outcome.

In this step, get ready to:
  • Create a snapshot-isolated loader with a validated synchronization baseline.
  • Stage current insert or update values behind a data-quality gate.
  • Apply warehouse changes atomically with durable audit evidence.
Create the loader script

The new script belongs in your Visual Studio Code workspace from earlier. The etl.LoadCustomerDimension procedure creates its run record before opening the loading transaction.

  • In the Explorer sidebar for the retail-ops-pipeline folder, click the New File icon.
  • Enter 04_create_pipeline.sql as the file name.

You now have an empty 04_create_pipeline.sql file beside the earlier project scripts.

  • Add the audited loader to 04_create_pipeline.sql by copying the complete script from the second tab below:

✔️ Awesome, I've got everything!

Your 04_create_pipeline.sql file is ready for review.

ⓧ I'd like to double check the full code

USE RetailOpsLab;
GO

CREATE PROCEDURE etl.LoadCustomerDimension
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

    DECLARE @RunID BIGINT;
    DECLARE @LastSyncVersion BIGINT;
    DECLARE @NextSyncVersion BIGINT;
    DECLARE @MinValidVersion BIGINT;
    DECLARE @RowsStaged INT = 0;
    DECLARE @RowsInserted INT = 0;
    DECLARE @RowsUpdated INT = 0;

    INSERT INTO audit.PipelineRun (PipelineName, Status)
    VALUES (N'CustomerDimension', 'Running');

    SET @RunID = SCOPE_IDENTITY();

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT @LastSyncVersion = LastSyncVersion
        FROM audit.PipelineState
        WHERE PipelineName = N'CustomerDimension';

        SET @MinValidVersion =
            CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'src.Customer'));

        IF @MinValidVersion IS NULL
           OR @LastSyncVersion < @MinValidVersion
        BEGIN
            ;THROW 50001, N'The saved synchronization version is no longer valid. Reinitialize the pipeline.', 1;
        END;

        SET @NextSyncVersion = CHANGE_TRACKING_CURRENT_VERSION();

        DELETE FROM stage.CustomerChanges;

        INSERT INTO stage.CustomerChanges
            (CustomerID, SysChangeVersion, SysChangeOperation,
             FullName, City, IsActive)
        SELECT
            CT.CustomerID,
            CT.SYS_CHANGE_VERSION,
            CT.SYS_CHANGE_OPERATION,
            S.FullName,
            S.City,
            S.IsActive
        FROM CHANGETABLE
            (CHANGES src.Customer, @LastSyncVersion) AS CT
        LEFT OUTER JOIN src.Customer AS S
            ON S.CustomerID = CT.CustomerID
        WHERE CT.SYS_CHANGE_VERSION <= @NextSyncVersion
          AND CT.SYS_CHANGE_OPERATION IN (N'I', N'U');

        SET @RowsStaged = @@ROWCOUNT;

        IF EXISTS
        (
            SELECT 1
            FROM stage.CustomerChanges
            WHERE FullName IS NULL
        )
        BEGIN
            ;THROW 50002, N'Data quality failed: FullName is required.', 1;
        END;

        UPDATE D
        SET D.FullName = S.FullName,
            D.City = S.City,
            D.IsActive = S.IsActive,
            D.LoadedAt = SYSUTCDATETIME()
        FROM dw.DimCustomer AS D
        INNER JOIN stage.CustomerChanges AS S
            ON S.CustomerID = D.CustomerID
        WHERE D.FullName <> S.FullName
           OR D.City <> S.City
           OR D.IsActive <> S.IsActive;

        SET @RowsUpdated = @@ROWCOUNT;

        INSERT INTO dw.DimCustomer
            (CustomerID, FullName, City, IsActive)
        SELECT S.CustomerID, S.FullName, S.City, S.IsActive
        FROM stage.CustomerChanges AS S
        WHERE NOT EXISTS
        (
            SELECT 1
            FROM dw.DimCustomer AS D
            WHERE D.CustomerID = S.CustomerID
        );

        SET @RowsInserted = @@ROWCOUNT;

        UPDATE audit.PipelineState
        SET LastSyncVersion = @NextSyncVersion,
            LastSuccessAt = SYSUTCDATETIME()
        WHERE PipelineName = N'CustomerDimension';

        UPDATE audit.PipelineRun
        SET CompletedAt = SYSUTCDATETIME(),
            Status = 'Completed',
            LastSyncVersion = @LastSyncVersion,
            NextSyncVersion = @NextSyncVersion,
            RowsStaged = @RowsStaged,
            RowsInserted = @RowsInserted,
            RowsUpdated = @RowsUpdated
        WHERE RunID = @RunID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        UPDATE audit.PipelineRun
        SET CompletedAt = SYSUTCDATETIME(),
            Status = 'Failed',
            LastSyncVersion = @LastSyncVersion,
            NextSyncVersion = @NextSyncVersion,
            ErrorMessage = ERROR_MESSAGE()
        WHERE RunID = @RunID;

        THROW;
    END CATCH;
END;
GO

EXEC etl.LoadCustomerDimension;

SELECT RunID, Status, LastSyncVersion, NextSyncVersion,
       RowsStaged, RowsInserted, RowsUpdated, ErrorMessage
FROM audit.PipelineRun
ORDER BY RunID;
GO

How does the loader work?

  • The initial Running row captures a unique RunID before loading begins.
  • The minimum-valid-version check prevents the loader from using expired tracking history.
  • CHANGETABLE returns each changed customer key after the saved version.
  • The source join adds the current customer values to each tracked key.
  • The operation filter keeps I or U rows for this version of the loader.
  • The quality gate stops the transaction when a staged FullName is missing.
  • The success path updates the saved version within the loading transaction.
  • The catch path rolls back the loading transaction before recording the failed outcome.
  • Save 04_create_pipeline.sql.

The unsaved indicator beside 04_create_pipeline.sql disappears. Your complete loader script is now stored in the project folder.

Script not saving?

Confirm that 04_create_pipeline.sql appears inside the retail-ops-pipeline folder. Check that the file name ends with .sql.

If the editor still shows unsaved changes, save the active file again. Help me verify my SQL loader file before I run it.

Trace the staged data path

The stage table separates change detection from warehouse loading. Each @@ROWCOUNT assignment captures evidence immediately after its matching write.

  • Locate CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'src.Customer')) in 04_create_pipeline.sql.
  • Read the following IF block to confirm that an expired baseline stops the run.
  • Trace the CHANGETABLE result through the LEFT OUTER JOIN into stage.CustomerChanges.
  • Confirm that the operation filter permits I or U rows.
  • Find the FullName IS NULL quality test.
  • Compare the warehouse update path with the NOT EXISTS insert path.
  • Locate the @@ROWCOUNT assignment after each loading statement.

Why separate updates from inserts?

Idempotency means a no-change rerun leaves warehouse rows untouched. The update path changes only customers whose values differ.

The insert path checks for an existing customer before creating a row. This prevents the duplicate-key failure from the naive reload.

Run the zero-change verification

The success path advances audit.PipelineState inside the transaction. A commit makes the warehouse result share one boundary with the new baseline.

The catch path rolls back unfinished work. It then records the failure against the original RunID.

Before you execute the script, do you expect the loader to stage any rows after the initial baseline?

  • Execute the entire 04_create_pipeline.sql file with MS SQL: Execute Query.

One audit row returns with Status set to Completed. The RowsStaged, RowsInserted, and RowsUpdated columns each show 0.

That is the core loader working. It can finish successfully when the source has no unprocessed changes.

Audit row not showing?

Confirm that you executed the entire file so the procedure creation batch and final audit query both ran. Check that your connection can access RetailOpsLab.

If the procedure cannot find its baseline, confirm that the earlier initialization script created the CustomerDimension state row. Help me diagnose my missing zero-row pipeline audit result.

Your loader now records a clean no-change run without rewriting the warehouse. Next, you will challenge it with invalid data before proving recovery and repeatability.

Prove Failure, Recovery, and Idempotency

Your SQL Server loader can now process tracked inserts plus updates inside one transaction. It also writes each outcome to an audit log.

This step tests whether those controls hold when source data violates the required-name rule. You will inspect the warehouse after the failure.

After correcting the source row, you will run the loader twice. The second run proves idempotency through a successful zero-row audit.

In this step, get ready to:
  • Trigger the intended quality failure with a missing customer name.
  • Inspect the rollback evidence.
  • Prove recovery through a corrected load plus a no-op rerun.
Trigger the quality failure

A data-quality gate protects the warehouse from invalid source values. The strongest test uses a committed source change that the loader must reject.

  • Click the New File icon in the Explorer sidebar from earlier.
  • Enter 05_test_pipeline.sql as the file name.
  • Confirm 05_test_pipeline.sql appears beside your earlier scripts.
  • Add the source changes to 05_test_pipeline.sql by pasting this code:
USE RetailOpsLab;
GO

UPDATE src.Customer
SET City = N'Chicago'
WHERE CustomerID = 2;

INSERT INTO src.Customer (CustomerID, FullName, City, IsActive)
VALUES (4, NULL, N'Boston', 1);
GO

What does this test data do?

  • The USE RetailOpsLab; statement directs the test to your isolated lab.
  • The update changes customer 2 from Denver to Chicago.
  • The insert adds customer 4 with a NULL name.
  • Save 05_test_pipeline.sql.
  • Select the first block through its final GO.
  • Run MS SQL: Execute Query.

The editor reports successful execution. The invalid row now exists in the source for the quality test.

Source changes not applied?

Confirm the query editor is connected to RetailOpsLab. If customer 4 already exists, the test block has already run.

Check that customer 2 exists in src.Customer. Help me diagnose why the source test changes did not run.

The source mutation is already committed when the loader starts. A safe failure must preserve that source evidence while rejecting its warehouse effects.

  • Add the controlled loader execution below the first block by pasting this code:
BEGIN TRY
    EXEC etl.LoadCustomerDimension;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ExpectedQualityFailure;
END CATCH;
GO

How is the failure captured?

  • The TRY...CATCH block keeps the expected failure inside the test script.
  • The procedure detects the missing FullName before it updates the dimension.
  • The ERROR_MESSAGE() result exposes the reason for the rejected run.
  • Save 05_test_pipeline.sql.

Before you run this block, decide whether the missing name will pass the quality gate.

  • Select the new BEGIN TRY block through its final GO.
  • Run MS SQL: Execute Query.

You will see Data quality failed: FullName is required. under ExpectedQualityFailure. This failure is intentional.

Quality failure not returned?

Check that you ran the source-change block first. Confirm the insert still uses NULL for FullName.

Confirm the executed statement calls etl.LoadCustomerDimension. Help me find why the intended quality failure did not appear.

Confirm the rollback boundary

A failed load is safe only when its synchronization baseline remains unchanged. The audit row is created before the loading transaction.

Its failure evidence survives the rollback. The warehouse should still reflect the last successful run.

  • Add the rollback inspection queries below the failure block by pasting this code:
SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT RunID, Status, RowsStaged, RowsInserted, RowsUpdated, ErrorMessage
FROM audit.PipelineRun
ORDER BY RunID;
GO

What do these queries prove?

  • The state query reveals whether LastSyncVersion moved during the failed load.
  • The dimension query reveals whether either pending source change reached the warehouse.
  • The run-history query confirms that the failed attempt remains available for diagnosis.
  • Save 05_test_pipeline.sql.

Before you inspect the results, predict whether the failed run changed the warehouse or its saved version.

  • Select the three inspection queries through their final GO.
  • Run MS SQL: Execute Query.

The state result retains the previous LastSyncVersion plus LastSuccessAt. The dimension still has three rows.

Customer 2 still shows Denver. Customer 4 is absent.

The latest audit entry has a Failed status plus the quality message. That is the protection test passed: the failed run left evidence without corrupting the warehouse.

Warehouse state changed?

Confirm you have not run the recovery block yet. The dimension should still show customer 2 in Denver.

Check that the latest failed audit row contains the name-quality message. Help me compare my rollback results with the expected protected state.

Recover and prove idempotency

Recovery reuses the saved baseline from the failed run. The next successful execution can still see every pending source change.

  • Add the correction plus both loader executions below the rollback queries by pasting this code:
UPDATE src.Customer
SET FullName = N'Devon Lee'
WHERE CustomerID = 4;

EXEC etl.LoadCustomerDimension;
EXEC etl.LoadCustomerDimension;
GO

How does this prove recovery?

  • The update gives customer 4 the valid name Devon Lee.
  • The first procedure call processes the pending city update plus customer insert.
  • The second procedure call immediately tests the same pipeline with no newer source changes.
  • Save 05_test_pipeline.sql.

Before you execute this block, decide whether the pending changes should survive the retry.

  • Select the correction block through its final GO.
  • Run MS SQL: Execute Query.

Both procedure calls finish without the quality error. The first call applies the pending changes.

The second call finds no newer changes. The final queries make that no-op visible.

Quality failure still returned?

Confirm the correction assigns Devon Lee to customer 4. Check that you selected the entire correction block before executing it.

Run the correction before either procedure call. Help me diagnose why the corrected incremental load still fails.

  • Add the final dimension plus audit checks below the recovery block by pasting this code:
SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT RunID, Status, LastSyncVersion, NextSyncVersion,
       RowsStaged, RowsInserted, RowsUpdated, ErrorMessage
FROM audit.PipelineRun
ORDER BY RunID;
GO

What does the final check cover?

  • The dimension result shows the corrected source state after recovery.
  • The state result shows the baseline from the latest successful execution.
  • The audit result separates the failed attempt from the successful incremental run plus the zero-change rerun.
  • Save 05_test_pipeline.sql.

Before you run the final check, predict which audit row should contain zero staged rows.

  • Select the final three queries through their last GO.
  • Run MS SQL: Execute Query.

The dimension now has four rows. Customer 2 shows Chicago.

Customer 4 shows Devon Lee in Boston. The state row now holds the successful version.

The audit history shows a Failed run followed by two Completed runs. The newest row has zero staged, inserted, plus updated rows.

Your pipeline has now survived bad data without corruption. It has also recovered exactly once before settling into a clean no-op.

Final counts look wrong?

Run the recovery block only once as a unit. Its first procedure call creates the successful incremental run.

Its second procedure call creates the no-op run. Help me compare my final dimension and audit results.

✔️ Awesome, I've got everything!

Your complete reliability test is saved in 05_test_pipeline.sql.

ⓧ I'd like to double check the full code

USE RetailOpsLab;
GO

UPDATE src.Customer
SET City = N'Chicago'
WHERE CustomerID = 2;

INSERT INTO src.Customer (CustomerID, FullName, City, IsActive)
VALUES (4, NULL, N'Boston', 1);
GO

BEGIN TRY
    EXEC etl.LoadCustomerDimension;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ExpectedQualityFailure;
END CATCH;
GO

SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT RunID, Status, RowsStaged, RowsInserted, RowsUpdated, ErrorMessage
FROM audit.PipelineRun
ORDER BY RunID;
GO

UPDATE src.Customer
SET FullName = N'Devon Lee'
WHERE CustomerID = 4;

EXEC etl.LoadCustomerDimension;
EXEC etl.LoadCustomerDimension;
GO

SELECT CustomerID, FullName, City, IsActive
FROM dw.DimCustomer
ORDER BY CustomerID;

SELECT PipelineName, LastSyncVersion, LastSuccessAt
FROM audit.PipelineState;

SELECT RunID, Status, LastSyncVersion, NextSyncVersion,
       RowsStaged, RowsInserted, RowsUpdated, ErrorMessage
FROM audit.PipelineRun
ORDER BY RunID;
GO

Secret mission

Handle Source Deletes Safely

Extend your incremental loader to preserve deleted customer history. You will convert a source deletion into a soft deactivation in the warehouse while keeping the existing transaction and audit behavior.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your resources available, pause your work, or delete the project entirely. This project adds no new service charge to your existing SQL Server instance, though RetailOpsLab continues using storage until you remove it.

Resources you used:

  • One local RetailOpsLab database containing the complete data lab.
  • Five project schemas: src, stage, dw, audit, and etl.
  • Four project tables: src.Customer, dw.DimCustomer, stage.CustomerChanges, and audit.PipelineState with audit.PipelineRun.
  • One stored procedure named etl.LoadCustomerDimension.
  • The Change Tracking metadata that supports incremental synchronization.
  • The local retail-ops-pipeline folder containing your seven .sql scripts.

Keep everything running

No action is needed while you continue experimenting. The loader runs only when you execute it.

  • Keep RetailOpsLab available for future pipeline tests.
  • Retain the saved synchronization baseline in audit.PipelineState.
  • Keep the retail-ops-pipeline folder as a reusable SQL reference.

Pause - I'll come back to this later

Pausing leaves the database state ready for your return. No scheduled activity runs in this project.

  • Stop executing etl.LoadCustomerDimension.
  • End the current SQL Server connection in Visual Studio Code.
  • Keep RetailOpsLab so its synchronization baseline remains available.
  • Retain the retail-ops-pipeline folder for your next session.

Delete - I don't want to use this again

Deleting the lab removes its warehouse data plus its audit history. Your existing SQL Server instance remains available.

For database removal:

  • Return to your connected query editor in Visual Studio Code.
  • Use the database selector in the query editor to switch the connection to master.
  • Close each other query editor that is connected to RetailOpsLab.
  • Remove the lab database by running this command:
DROP DATABASE RetailOpsLab;

What Does This Command Remove?

The DROP DATABASE command removes RetailOpsLab as one unit. Its five schemas disappear with the database.

The project tables disappear with their data. The stored procedure plus its Change Tracking metadata also disappear.

  • Refresh the database list for your SQL Server connection.
  • Confirm that RetailOpsLab is no longer listed.

For local script removal:

macOS

  • Use Finder to locate the retail-ops-pipeline folder.
  • Move the retail-ops-pipeline folder to the Bin.
  • Empty the Bin to remove the scripts from disk.
  • Confirm that retail-ops-pipeline no longer appears in its former location.

Windows

  • Use File Explorer to locate the retail-ops-pipeline folder.
  • Move the retail-ops-pipeline folder to the Recycle Bin.
  • Empty the Recycle Bin to remove the scripts from disk.
  • Confirm that retail-ops-pipeline no longer appears in its former location.

Nice Work!

Nice Work!

You did it! Your SQL Server warehouse now processes customer changes through an audited incremental ETL pipeline.

You've learned how to:

  • Turn a duplicate-key reload failure into a restartable process powered by Change Tracking. Persist a synchronization baseline so each run requests only unprocessed changes.
  • Reject invalid customer data inside a transaction protected by snapshot isolation. Preserve warehouse consistency through rollback.
  • Use durable audit records to prove recovery after correction. Confirm idempotency with a successful zero-row rerun.
  • Secret Mission: Convert tracked source deletes into soft deactivations that preserve warehouse history.

Ready to quiz yourself?