Build an Azure SQL Sales Analytics Mart

Clean messy sales data and publish a trusted Azure SQL reporting view.

Introduction

30 Second Summary

A sales total can look convincing even when bad records have quietly distorted it. One duplicated customer can turn a business decision into an expensive guess.

In this project, you will build a compact sales analytics mart in Azure SQL Database. You will transform deliberately messy staging data into a curated model before publishing a reusable regional sales view.

What You'll Build

Picture running your sales pipeline in the browser to watch a suspicious 760.00 total become a defensible 320.00 regional report.

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

  • A browser-hosted SQL workspace where you can run T-SQL through Query editor (preview) to see database results immediately.
  • A visible duplicate-join failure that reports 760.00, followed by a trusted 320.00 result after your quality rules take effect.
  • A reusable regional sales report showing daily revenue plus a running total for North and South.
  • Secret Mission: Build a stored procedure that reports all five staging defects before blocking the batch with a quality gate.

Are there any prerequisites?

Basic SQL or data concepts are the only expected background. You also need an internet-connected desktop or laptop.

The guide walks you through Microsoft Edge plus every required Azure setup task.

Before We Start

Before the hands-on work begins, this checkpoint helps you commit to building a trustworthy sales mart with Azure SQL Database. Your goal is to identify who needs the regional sales report and why accurate revenue matters to them.

Set Up Azure and Open Your SQL Workspace

Your regional sales report needs a safe database before you can test any transformation. A browser-based workspace keeps this setup focused on data work.

In this step, you will use Microsoft Edge to create an Azure SQL Database through the Microsoft Azure portal. You will finish by proving that Query editor (preview) can reach the database.

In this step, get ready to:
  • Install or open Microsoft Edge.
  • Sign in to Azure with an active subscription.
  • Create a free-offer database and verify the browser connection.
Install or open Microsoft Edge

Microsoft Edge gives you one browser path for the Azure portal across Windows, macOS, and Debian-based Linux. Choose the tab for your operating system.

macOS

  • Open the Microsoft Edge download page in your current browser.
  • Choose Download for macOS.
  • Open the downloaded installer.
  • Complete the installer prompts.
  • Press Cmd+Space to open Spotlight.
  • Type Microsoft Edge and press Enter to open it.

Windows

  • Press the Windows key to open the search bar.
  • Type Microsoft Edge and press Enter to open the installed browser.
  • Open the Microsoft Edge download page in your current browser if Microsoft Edge is unavailable.
  • Follow the Windows download path on the page.
  • Open the downloaded installer.
  • Complete the installer prompts.
  • Repeat the search-bar steps to open Microsoft Edge.

Linux

This installation path covers Debian-based Linux distributions.

  • Open the Microsoft Edge download page in your current browser.
  • Choose Download for Linux (.deb).
  • Open the downloaded package.
  • Complete the software installer prompts.
  • Open your application menu.
  • Select Microsoft Edge.

You should see a Microsoft Edge window with an address bar ready for the Azure portal.

Having Trouble Opening Edge?

Check that you downloaded the installer for your operating system. Restart the browser after installation if it does not appear in search.

Ask for help with your exact installation issue: Help me install or open Microsoft Edge on my operating system.

Activate your Azure subscription

An active Azure subscription gives the portal somewhere to place your database resources. You can use the Azure free-account path when you need a new subscription.

Signing in involves account credentials. Keep those details inside Microsoft's sign-in flow.

  • Visit the Azure free account page in Microsoft Edge if you need an Azure account or subscription.
  • Complete the account setup prompts.
  • Open the Azure portal in Microsoft Edge.
  • Sign in with your Azure account.
  • Search for Subscriptions in the portal search bar.
  • Open the subscription list.
  • Select the subscription you plan to use.

You should see that the selected subscription is active. This confirms Azure can create the resources for your lab.

Cannot Confirm an Active Subscription?

Make sure the account shown in the portal matches the account used for your subscription. Complete the free-account setup if the subscription list is empty.

Get help with the state shown in your portal: Help me find or activate my Azure subscription.

Create the free database and prove the connection

A resource group keeps this lab's Azure resources together. The logical server manages database access while Azure SQL Database stores the sales mart.

Keep This Lab at Zero Cost

The database stays at zero cost while your usage remains within the monthly free-offer allowances. The free-offer banner and Cost summary provide the confirmation before deployment.

The auto-pause option stops database use after the free limit is reached. Paid continuation remains outside this project.

  • Open the Azure SQL hub in Microsoft Edge.
  • Select Start free in the database creation pane.

You should see the database creation page with a Free offer applied! banner.

  • Confirm that the Free offer applied! banner is visible.
  • Choose your active subscription in the Subscription field.
  • Select Create new under Resource group.
  • Enter sales-analytics-rg as the resource group name.

The resource group dialog now shows the name that contains every Azure resource created by this project.

  • Select OK to create the resource group.
  • Enter sales-analytics-db in the Database name field.
  • Select Create new under Server.

Finding an available server name can take a few tries because every Azure SQL logical server needs a globally unique name. The portal checks availability as you type.

  • Enter a globally unique value in the Server name field: your unique SQL server name.
  • Select an available Location.

You should see the portal accept your server name as available.

  • Select Use SQL authentication as the authentication method.
  • Enter azureuser in the Server admin login field.
  • Create a password that satisfies the on-screen requirements.
  • Repeat the same password in the Confirm password field.

The confirmation field should accept the matching value. Keep this credential private because it controls administrator access to the database.

  • Store the administrator login and password in your password manager.
  • Select OK to finish the logical server settings.
  • Select Development for the Workload environment.
  • Select Auto-pause the database until next month for the free-limit behavior.

The database is now configured to pause when it reaches the monthly free limit.

  • Confirm that the Cost summary card shows an estimated monthly cost of zero.
  • Select Review + create.

The review page should confirm that the configuration passed validation.

  • Select Create to start the deployment.

Database deployment can take several minutes. The portal may remain on the deployment screen while Azure creates the resource group, logical server, and database.

  • Wait for the deployment to complete.
  • Open sales-analytics-db from the deployment summary.

The database Overview page confirms that your Azure SQL Database is ready.

The last setup task connects the browser workspace with SQL authentication. Use the administrator credentials you stored during server creation.

  • Select Query editor (preview) from the database resource menu.
  • Choose SQL authentication on the sign-in screen.
  • Enter azureuser in the Login field.
  • Enter your stored server administrator password in the Password field.
  • Select OK to connect.

If the logical server blocks your current IP address, the sign-in area shows an allowlist link. This is a normal firewall check for a new connection.

  • Select the Allowlist IP... link if the firewall blocks your IP address.
  • Wait up to five minutes for the firewall change to take effect.
  • Select Connect again.

The blank query area means your browser is connected and ready to run Transact-SQL.

  • Paste this connection check into the query area as 00_connection_check.sql:
SELECT SYSDATETIMEOFFSET(), DB_NAME(), ORIGINAL_LOGIN();

What Does This Query Check?

  • The SYSDATETIMEOFFSET() value proves that Azure SQL executed the query.
  • The DB_NAME() value identifies the database receiving the query.
  • The ORIGINAL_LOGIN() value identifies the authenticated login.

Before you run the query, which database name and administrator login do you expect the Results pane to return?

  • Select Run above the query area.

You should see one row in the Results pane. It contains the current timestamp with its UTC offset, the current database name, and the authenticated login.

Query Did Not Return a Result?

Confirm that Query editor shows a connected session for sales-analytics-db. Reconnect with the server administrator credentials if the session has closed.

If the firewall still blocks the connection, repeat the IP allowlist action and wait for the change to apply.

Share the result you see for targeted help: Help me troubleshoot my Azure SQL Query editor connection.

✔️ Awesome, I've got everything!

Your connection check is working. Keep Query editor open for the next step.

ⓧ I'd like to double check the full code

Compare your connection check with this complete file.

SELECT SYSDATETIMEOFFSET(), DB_NAME(), ORIGINAL_LOGIN();

Your Azure SQL workspace is connected and taking queries. Next, you will load deliberately messy sales data and expose the inflated revenue result.

Stage Messy Sales Data and Expose a Bad Result

Your browser-based Azure SQL Database workspace is connected. The sales mart now needs source-shaped data that preserves real-world defects.

A staging layer accepts imperfect rows before curation. Duplicate business keys can multiply join matches, which can make a wrong revenue total look believable.

In this step, get ready to:
  • Create two staging tables with nullable columns for source defects.
  • Load customer and sales rows with duplicates, missing values, and an orphan reference.
  • Run a naive join that reports inflated revenue of 760.00.
Create the staging tables

Staging tables preserve data in a source-like shape before any quality rules are applied. Nullable columns allow incomplete records to land so you can inspect their impact.

  • In Query editor (preview) from earlier, create a fresh query for 01_staging_and_sample_data.sql.
  • Add the customer staging definition by pasting this statement:
CREATE TABLE dbo.stg_customers
(
    source_row_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NULL,
    updated_at DATETIME2 NOT NULL
);

What does this table store?

  • The source_row_id column identifies each incoming row even when its business key is duplicated.
  • The customer_id column holds the business key used to connect customers with sales.
  • The nullable region column allows an incomplete customer row to enter the staging layer.
  • Save the query as 01_staging_and_sample_data.sql.
  • Select only the CREATE TABLE dbo.stg_customers statement.
  • Select Run.

Good start. Your customer staging table now accepts complete source rows plus a missing region.

Customer table did not get created?

A repeated run can fail because dbo.stg_customers already exists. A partial selection can fail if the closing parenthesis is missing.

Select the complete statement from CREATE TABLE through its closing semicolon before trying again.

Help me troubleshoot the customer staging table.

  • Add the sales staging definition below the customer table statement:
CREATE TABLE dbo.stg_sales
(
    source_row_id INT NOT NULL PRIMARY KEY,
    sale_id INT NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NULL
);

What does the sales table allow?

  • The sale_id column holds the source system's sale identifier.
  • The customer_id column can contain a value that has no matching staged customer.
  • The nullable amount column lets a sale arrive without a usable revenue value.
  • Save 01_staging_and_sample_data.sql.
  • Select only the CREATE TABLE dbo.stg_sales statement.
  • Select Run.

You should see the statement finish without an error. Both staging tables are now ready to receive the test fixture.

Sales table did not get created?

Confirm that the selected statement starts with CREATE TABLE dbo.stg_sales. Make sure its final closing parenthesis is included.

Help me troubleshoot the sales staging table.

Load the messy sales fixture

A useful quality exercise needs predictable defects. These rows model repeated business keys plus incomplete records that can occur in operational data.

  • Add the customer fixture below the table definitions:
INSERT INTO dbo.stg_customers
    (source_row_id, customer_id, customer_name, region, updated_at)
VALUES
    (1, 1, N'Amina Retail', N'North', '2026-08-01T09:00:00'),
    (2, 1, N'Amina Retail', N'North', '2026-08-20T09:00:00'),
    (3, 2, N'Bright Stores', N'South', '2026-08-15T09:00:00'),
    (4, 3, N'Coastal Shop', NULL, '2026-08-18T09:00:00');

Which customer defects are present?

  • Customer 1 appears twice with different updated_at values.
  • Customer 3 has a NULL region.
  • The unique source_row_id values preserve both versions of the duplicated customer.
  • Save 01_staging_and_sample_data.sql.
  • Select only the customer INSERT statement.
  • Select Run.

The statement should complete without an error. Four customer source rows are now stored in dbo.stg_customers.

Customer rows did not load?

Check that dbo.stg_customers was created before this insert. A repeated insert can also fail because each source_row_id must remain unique.

Help me troubleshoot the customer insert.

  • Add the sales fixture below the customer insert:
INSERT INTO dbo.stg_sales
    (source_row_id, sale_id, customer_id, sale_date, amount)
VALUES
    (1, 1001, 1, '2026-08-21', 120.00),
    (2, 1001, 1, '2026-08-21', 120.00),
    (3, 1002, 2, '2026-08-22', 200.00),
    (4, 1003, 3, '2026-08-23', 80.00),
    (5, 1004, 99, '2026-08-24', 50.00),
    (6, 1005, 2, '2026-08-25', NULL);

Which sales defects are present?

  • Sale 1001 appears twice under different source rows.
  • Sale 1004 refers to customer 99 even though that customer is absent from the customer staging table.
  • Sale 1005 has a NULL amount.
  • Save 01_staging_and_sample_data.sql.
  • Select only the sales INSERT statement.
  • Select Run.

Your staging layer now contains six sales rows. The deliberately messy fixture is ready for its first business query.

Sales rows did not load?

Confirm that dbo.stg_sales exists. If you already ran the insert successfully, running it again conflicts with the existing source_row_id values.

Help me troubleshoot the sales insert.

Expose the inflated revenue result

An INNER JOIN returns one row for every matching pair. Repeated business keys can therefore turn one logical sale into several joined rows.

  • Append the detailed join query below the sales insert:
SELECT
    s.sale_id,
    s.customer_id,
    c.customer_name,
    c.region,
    s.amount
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id
ORDER BY s.sale_id, c.source_row_id;

What does this query inspect?

The query connects each staged sale to every staged customer row with the same customer_id. The selected columns make repeated matches visible in the result set.

  • Save 01_staging_and_sample_data.sql.

Before you run this query, how many times do you think sale 1001 will appear?

  • Select only the detailed join query.
  • Select Run.

In the Results pane, you should see sale 1001 four times.

Why does one sale appear four times?

The staging data contains two sales rows with sale_id 1001. It also contains two matching customer rows for customer_id 1.

The join produces every matching combination. Two sales rows multiplied by two customer rows create four result rows.

Do you see fewer joined rows?

Confirm that both insert statements completed successfully. Check that the join condition uses c.customer_id = s.customer_id.

Help me troubleshoot the detailed join result.

  • Append the final revenue query below the detailed join query:
SELECT SUM(s.amount) AS naive_revenue
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id;

What does this query calculate?

The query adds every non-null amount produced by the join. It applies no duplicate rule or completeness rule before calculating revenue.

  • Save 01_staging_and_sample_data.sql.

Before you run this total, do you expect it to match the distinct valid sales?

  • Select only the final revenue query.
  • Select Run.

You should see one row with naive_revenue equal to 760.00. That result is deliberately wrong.

How does the total reach 760.00?

  • The repeated customer and sale rows produce four matches worth 120.00 each. Those matches contribute 480.00.
  • Sale 1002 contributes 200.00.
  • Sale 1003 contributes 80.00.
  • The orphan sale has no matching customer, so the inner join excludes it.
  • The missing amount does not increase the sum.

Do you see a different revenue total?

Confirm that both insert statements ran exactly once. Check that the final query joins the tables using customer_id without filtering any rows.

Help me troubleshoot the naive revenue result.

✔️ Awesome, I've got everything!

Excellent. Your staging tables now reproduce the inflated revenue result, so save the completed query before moving on.

ⓧ I'd like to double check the full code

Compare both saved SQL files with these complete versions.

SELECT SYSDATETIMEOFFSET(), DB_NAME(), ORIGINAL_LOGIN();

What should this file contain?

The connection-check file contains the original verification query from the previous step. Keep it unchanged.

CREATE TABLE dbo.stg_customers
(
    source_row_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NULL,
    updated_at DATETIME2 NOT NULL
);

CREATE TABLE dbo.stg_sales
(
    source_row_id INT NOT NULL PRIMARY KEY,
    sale_id INT NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NULL
);

INSERT INTO dbo.stg_customers
    (source_row_id, customer_id, customer_name, region, updated_at)
VALUES
    (1, 1, N'Amina Retail', N'North', '2026-08-01T09:00:00'),
    (2, 1, N'Amina Retail', N'North', '2026-08-20T09:00:00'),
    (3, 2, N'Bright Stores', N'South', '2026-08-15T09:00:00'),
    (4, 3, N'Coastal Shop', NULL, '2026-08-18T09:00:00');

INSERT INTO dbo.stg_sales
    (source_row_id, sale_id, customer_id, sale_date, amount)
VALUES
    (1, 1001, 1, '2026-08-21', 120.00),
    (2, 1001, 1, '2026-08-21', 120.00),
    (3, 1002, 2, '2026-08-22', 200.00),
    (4, 1003, 3, '2026-08-23', 80.00),
    (5, 1004, 99, '2026-08-24', 50.00),
    (6, 1005, 2, '2026-08-25', NULL);

SELECT
    s.sale_id,
    s.customer_id,
    c.customer_name,
    c.region,
    s.amount
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id
ORDER BY s.sale_id, c.source_row_id;

SELECT SUM(s.amount) AS naive_revenue
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id;

What should the completed file prove?

This file creates both staging tables. It loads every deliberate defect before exposing the duplicate join result of 760.00.

You have reproduced a believable revenue total that the staging defects inflated. Next, you will measure those defects and curate a model whose numbers can be trusted.

Diagnose and Curate Trustworthy Data

Your staging join has exposed an inflated revenue total of 760.00. The next goal is to measure the defects behind that result before any rows enter your reporting model.

Data profiling turns suspicious rows into defect counts. A dimensional model then protects the curated data with keys plus non-null columns.

In this step, get ready to:
  • Profile five categories of staging-data defects.
  • Create constrained customer dimension plus sales fact tables.
  • Load valid rows with deterministic ranking rules.
Profile the staging defects

A quality profile gives each known failure a measurable column. This query counts duplicate business keys, incomplete rows, plus sales whose customer does not exist.

  • Create 02_quality_profile.sql in your SQL workspace by replacing the current query text with the code below:
SELECT
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT customer_id
            FROM dbo.stg_customers
            GROUP BY customer_id
            HAVING COUNT(*) > 1
        ) AS duplicate_customers
    ) AS duplicate_customer_keys,
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT sale_id
            FROM dbo.stg_sales
            GROUP BY sale_id
            HAVING COUNT(*) > 1
        ) AS duplicate_sales
    ) AS duplicate_sale_keys,
    (
        SELECT COUNT(*)
        FROM dbo.stg_customers
        WHERE region IS NULL
    ) AS missing_region_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales
        WHERE amount IS NULL
    ) AS missing_amount_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales AS s
        LEFT JOIN dbo.stg_customers AS c
            ON c.customer_id = s.customer_id
        WHERE c.customer_id IS NULL
    ) AS orphan_sales;

What does this profile measure?

  • The grouped customer subquery counts business keys that appear more than once in dbo.stg_customers.
  • The grouped sales subquery counts business keys that appear more than once in dbo.stg_sales.
  • The null checks count customer rows without a region plus sales rows without an amount.
  • The left join counts sales whose customer_id has no matching staging customer.
  • Save 02_quality_profile.sql.

Before you run the profile, do you expect every defect category to contain a nonzero count?

  • Select Run in Query editor (preview).

The Results pane shows one row. Each of the five defect columns contains 1.

You now have evidence for every flaw behind the untrusted staging result.

Do the profile counts look different?

Confirm that both staging tables still contain the deliberately messy rows from 01_staging_and_sample_data.sql.

Check that the orphan condition uses c.customer_id IS NULL after the left join.

Help me compare my quality profile with the expected five defect counts.

Define the curated model

A customer dimension stores one trusted row per customer. Its primary key gives every customer a unique identity.

A sales fact stores measurable transactions. Its foreign key prevents a sale from referencing a customer outside the curated dimension.

  • Create a blank query named 03_curated_model.sql in your SQL workspace.
  • Add the customer dimension definition by pasting this code:
CREATE TABLE dbo.dim_customer
(
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NOT NULL
);

How does the dimension protect customers?

  • The PRIMARY KEY constraint prevents duplicate customer_id values.
  • The non-null region column prevents incomplete customer rows from entering the dimension.
  • Save 03_curated_model.sql.
  • Highlight the customer dimension statement.
  • Select Run.

The statement completes without an error. The empty dbo.dim_customer table is ready to receive valid customer rows.

Could not create the customer dimension?

A message about an existing object means the table was already created. Avoid running the same table statement twice.

Help me diagnose why the customer dimension statement failed.

  • Add the sales fact definition below the customer dimension by pasting this code:
CREATE TABLE dbo.fact_sales
(
    sale_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT FK_fact_sales_dim_customer
        FOREIGN KEY (customer_id)
        REFERENCES dbo.dim_customer (customer_id)
);

How does the fact table protect sales?

  • The sale_id primary key prevents duplicate sales from entering the fact table.
  • The FK_fact_sales_dim_customer foreign key requires every curated sale to reference an existing curated customer.
  • The non-null amount column blocks sales that cannot contribute a valid revenue value.
  • Save 03_curated_model.sql.
  • Highlight the sales fact statement.
  • Select Run.

The statement completes without an error. The empty dbo.fact_sales table now enforces customer relationships plus valid amounts.

Could not create the sales fact?

Confirm that dbo.dim_customer was created first. The foreign key depends on its primary key.

Help me diagnose why the sales fact statement failed.

Load ranked rows and verify revenue

Common table expressions provide temporary ranked result sets for each load. The ROW_NUMBER() window function assigns a position within each repeated business key.

The ordering rules make each choice deterministic. Incomplete rows plus orphaned sales are filtered before the constrained tables receive them.

  • Add the ranked customer load below the table definitions by pasting this code:
;WITH ranked_customers AS
(
    SELECT
        customer_id,
        customer_name,
        region,
        ROW_NUMBER() OVER
        (
            PARTITION BY customer_id
            ORDER BY updated_at DESC, source_row_id DESC
        ) AS customer_rank
    FROM dbo.stg_customers
)
INSERT INTO dbo.dim_customer
    (customer_id, customer_name, region)
SELECT
    customer_id,
    customer_name,
    region
FROM ranked_customers
WHERE customer_rank = 1
  AND region IS NOT NULL;

How are customer rows selected?

  • The partition restarts the ranking for each customer_id.
  • The descending timestamp places the latest customer record at rank 1.
  • The descending source row ID breaks a timestamp tie consistently.
  • The final condition rejects the latest row when its region is missing.
  • Save 03_curated_model.sql.
  • Highlight the ranked customer load.
  • Select Run.

The completion message reports two affected rows for dbo.dim_customer.

Did the customer load fail?

A primary-key error usually means the load ran more than once. Keep one execution of the ranked customer insert.

Check that ORDER BY updated_at DESC, source_row_id DESC appears inside the customer ranking window.

Help me debug the ranked customer load.

  • Add the ranked sales load below the customer load by pasting this code:
;WITH ranked_sales AS
(
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,
        ROW_NUMBER() OVER
        (
            PARTITION BY sale_id
            ORDER BY source_row_id DESC
        ) AS sale_rank
    FROM dbo.stg_sales
)
INSERT INTO dbo.fact_sales
    (sale_id, customer_id, sale_date, amount)
SELECT
    rs.sale_id,
    rs.customer_id,
    rs.sale_date,
    rs.amount
FROM ranked_sales AS rs
INNER JOIN dbo.dim_customer AS c
    ON c.customer_id = rs.customer_id
WHERE rs.sale_rank = 1
  AND rs.amount IS NOT NULL;

How are sales rows selected?

  • The partition ranks every repeated sale_id as one group.
  • The descending source row ID keeps one predictable sale row at rank 1.
  • The inner join removes sales whose customer never entered the curated dimension.
  • The final null check removes sales without an amount.
  • Save 03_curated_model.sql.
  • Highlight the ranked sales load.
  • Select Run.

The completion message reports two affected rows for dbo.fact_sales.

Did the sales load fail?

A foreign-key error means a sale still references a customer outside dbo.dim_customer.

Confirm that the inner join occurs before the rank plus amount filters are applied.

Help me debug the ranked sales load.

Before you run the final check, which revenue total do you expect after the duplicate, incomplete, plus orphaned rows have been removed?

  • Add the final count plus revenue check below the sales load by pasting this code:
SELECT
    (SELECT COUNT(*) FROM dbo.dim_customer) AS customer_count,
    (SELECT COUNT(*) FROM dbo.fact_sales) AS sale_count,
    (SELECT SUM(amount) FROM dbo.fact_sales) AS curated_revenue;

What does the final check prove?

  • The customer count confirms how many valid dimension rows survived the quality rules.
  • The sales count confirms how many valid fact rows survived deduplication plus filtering.
  • The revenue sum shows the trusted business total produced by the curated model.
  • Save 03_curated_model.sql.
  • Highlight the final check query.
  • Select Run.

The Results pane shows customer_count as 2. It shows sale_count as 2.

The same row shows curated_revenue as 320.00. That trusted total is 440.00 lower than the naive staging result.

Do the final values differ?

Confirm that each insert completed exactly once. Repeated inserts can trigger primary-key failures before the final check runs.

Check that the customer load rejects null regions. Check that the sales load rejects null amounts plus unmatched customers.

Help me trace an incorrect curated total.

✔️ Awesome, I've got everything!

Your quality profile plus curated model are complete. Double check that all four SQL files are saved.

ⓧ I'd like to double check the full code

The four files below show the cumulative project state at the end of this step.

SELECT SYSDATETIMEOFFSET(), DB_NAME(), ORIGINAL_LOGIN();
CREATE TABLE dbo.stg_customers
(
    source_row_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NULL,
    updated_at DATETIME2 NOT NULL
);

CREATE TABLE dbo.stg_sales
(
    source_row_id INT NOT NULL PRIMARY KEY,
    sale_id INT NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NULL
);

INSERT INTO dbo.stg_customers
    (source_row_id, customer_id, customer_name, region, updated_at)
VALUES
    (1, 1, N'Amina Retail', N'North', '2026-08-01T09:00:00'),
    (2, 1, N'Amina Retail', N'North', '2026-08-20T09:00:00'),
    (3, 2, N'Bright Stores', N'South', '2026-08-15T09:00:00'),
    (4, 3, N'Coastal Shop', NULL, '2026-08-18T09:00:00');

INSERT INTO dbo.stg_sales
    (source_row_id, sale_id, customer_id, sale_date, amount)
VALUES
    (1, 1001, 1, '2026-08-21', 120.00),
    (2, 1001, 1, '2026-08-21', 120.00),
    (3, 1002, 2, '2026-08-22', 200.00),
    (4, 1003, 3, '2026-08-23', 80.00),
    (5, 1004, 99, '2026-08-24', 50.00),
    (6, 1005, 2, '2026-08-25', NULL);

SELECT
    s.sale_id,
    s.customer_id,
    c.customer_name,
    c.region,
    s.amount
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id
ORDER BY s.sale_id, c.source_row_id;

SELECT SUM(s.amount) AS naive_revenue
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id;
SELECT
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT customer_id
            FROM dbo.stg_customers
            GROUP BY customer_id
            HAVING COUNT(*) > 1
        ) AS duplicate_customers
    ) AS duplicate_customer_keys,
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT sale_id
            FROM dbo.stg_sales
            GROUP BY sale_id
            HAVING COUNT(*) > 1
        ) AS duplicate_sales
    ) AS duplicate_sale_keys,
    (
        SELECT COUNT(*)
        FROM dbo.stg_customers
        WHERE region IS NULL
    ) AS missing_region_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales
        WHERE amount IS NULL
    ) AS missing_amount_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales AS s
        LEFT JOIN dbo.stg_customers AS c
            ON c.customer_id = s.customer_id
        WHERE c.customer_id IS NULL
    ) AS orphan_sales;
CREATE TABLE dbo.dim_customer
(
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NOT NULL
);

CREATE TABLE dbo.fact_sales
(
    sale_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT FK_fact_sales_dim_customer
        FOREIGN KEY (customer_id)
        REFERENCES dbo.dim_customer (customer_id)
);

;WITH ranked_customers AS
(
    SELECT
        customer_id,
        customer_name,
        region,
        ROW_NUMBER() OVER
        (
            PARTITION BY customer_id
            ORDER BY updated_at DESC, source_row_id DESC
        ) AS customer_rank
    FROM dbo.stg_customers
)
INSERT INTO dbo.dim_customer
    (customer_id, customer_name, region)
SELECT
    customer_id,
    customer_name,
    region
FROM ranked_customers
WHERE customer_rank = 1
  AND region IS NOT NULL;

;WITH ranked_sales AS
(
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,
        ROW_NUMBER() OVER
        (
            PARTITION BY sale_id
            ORDER BY source_row_id DESC
        ) AS sale_rank
    FROM dbo.stg_sales
)
INSERT INTO dbo.fact_sales
    (sale_id, customer_id, sale_date, amount)
SELECT
    rs.sale_id,
    rs.customer_id,
    rs.sale_date,
    rs.amount
FROM ranked_sales AS rs
INNER JOIN dbo.dim_customer AS c
    ON c.customer_id = rs.customer_id
WHERE rs.sale_rank = 1
  AND rs.amount IS NOT NULL;

SELECT
    (SELECT COUNT(*) FROM dbo.dim_customer) AS customer_count,
    (SELECT COUNT(*) FROM dbo.fact_sales) AS sale_count,
    (SELECT SUM(amount) FROM dbo.fact_sales) AS curated_revenue;

Your curated model now turns messy staging rows into a defensible revenue total. Next up, you will publish that trusted data through a reusable regional reporting view.

Publish a Reusable Reporting Layer

The curated model in Azure SQL Database now holds two customers plus two valid sales totaling 320.00. Those trustworthy rows are ready for reporting.

Analysts need a stable business interface for daily regional revenue. A SQL view packages the approved join plus aggregation behind one reusable name.

In Query editor (preview), you will publish the view. You will then calculate a running total for each region.

In this step, get ready to:
  • Publish daily regional revenue through a reusable SQL view.
  • Calculate running revenue for each region.
  • Reconcile the trusted revenue with the naive staging result.
Create the regional sales view

A view gives analysts one approved route to the curated tables. The view definition must run as its own query batch.

  • Clear the query area in Query editor (preview) from earlier.
  • Create 04_reporting_view.sql in the query area by pasting this code:
CREATE OR ALTER VIEW dbo.vw_region_daily_sales
AS
SELECT
    c.region,
    f.sale_date,
    COUNT(*) AS sale_count,
    SUM(f.amount) AS revenue
FROM dbo.fact_sales AS f
INNER JOIN dbo.dim_customer AS c
    ON c.customer_id = f.customer_id
GROUP BY
    c.region,
    f.sale_date;

What does this view do?

  • The CREATE OR ALTER VIEW statement creates the reporting view or updates its existing definition.
  • The join connects each valid sale to its curated customer region.
  • The grouping produces one row for each region plus sale date.
  • The COUNT(*) expression counts valid sales in each group.
  • The SUM(f.amount) expression calculates trusted revenue for each group.
  • Select Run above the query area.

The batch should complete without an error. That is the reporting contract published as dbo.vw_region_daily_sales.

View batch not completing?

  • Confirm the query area contains only the view definition from 04_reporting_view.sql.
  • Make sure CREATE OR ALTER VIEW is the first statement in the batch.

Help me diagnose why the reporting view batch does not complete.

Calculate running revenue by region

The view returns daily metrics. A window function can add a cumulative value without collapsing those daily rows.

  • Clear the view definition from the query area.
  • Create 05_reporting_query.sql in the query area by pasting this code:
SELECT
    region,
    sale_date,
    sale_count,
    revenue,
    SUM(revenue) OVER
    (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM dbo.vw_region_daily_sales
ORDER BY region, sale_date;

How does the running total work?

  • The PARTITION BY region clause starts an independent cumulative sequence for each region.
  • The ORDER BY sale_date clause sets the chronological order within each region.
  • The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame includes every regional row from the first date through the current date.
  • The final ordering makes the regional results easy to compare.

Before you run the reporting query, how do you expect each region's first running total to compare with its daily revenue?

  • Select Run above the query area.

In the Results pane, you should see North with revenue plus running revenue of 120.00. You should see South with revenue plus running revenue of 200.00.

Reporting rows look different?

  • Confirm that 05_reporting_query.sql reads from dbo.vw_region_daily_sales.
  • Confirm that dbo.dim_customer still contains two rows.
  • Confirm that dbo.fact_sales still contains two rows totaling 320.00.

Help me troubleshoot unexpected regional revenue results.

✔️ Awesome, I've got everything!

All six SQL scripts are ready. Your reporting layer now builds on the curated model from the earlier steps.

ⓧ I'd like to double check the full code

  • Compare each project script with the reference files below.
SELECT SYSDATETIMEOFFSET(), DB_NAME(), ORIGINAL_LOGIN();
CREATE TABLE dbo.stg_customers
(
    source_row_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NULL,
    updated_at DATETIME2 NOT NULL
);

CREATE TABLE dbo.stg_sales
(
    source_row_id INT NOT NULL PRIMARY KEY,
    sale_id INT NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NULL
);

INSERT INTO dbo.stg_customers
    (source_row_id, customer_id, customer_name, region, updated_at)
VALUES
    (1, 1, N'Amina Retail', N'North', '2026-08-01T09:00:00'),
    (2, 1, N'Amina Retail', N'North', '2026-08-20T09:00:00'),
    (3, 2, N'Bright Stores', N'South', '2026-08-15T09:00:00'),
    (4, 3, N'Coastal Shop', NULL, '2026-08-18T09:00:00');

INSERT INTO dbo.stg_sales
    (source_row_id, sale_id, customer_id, sale_date, amount)
VALUES
    (1, 1001, 1, '2026-08-21', 120.00),
    (2, 1001, 1, '2026-08-21', 120.00),
    (3, 1002, 2, '2026-08-22', 200.00),
    (4, 1003, 3, '2026-08-23', 80.00),
    (5, 1004, 99, '2026-08-24', 50.00),
    (6, 1005, 2, '2026-08-25', NULL);

SELECT
    s.sale_id,
    s.customer_id,
    c.customer_name,
    c.region,
    s.amount
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id
ORDER BY s.sale_id, c.source_row_id;

SELECT SUM(s.amount) AS naive_revenue
FROM dbo.stg_sales AS s
INNER JOIN dbo.stg_customers AS c
    ON c.customer_id = s.customer_id;
SELECT
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT customer_id
            FROM dbo.stg_customers
            GROUP BY customer_id
            HAVING COUNT(*) > 1
        ) AS duplicate_customers
    ) AS duplicate_customer_keys,
    (
        SELECT COUNT(*)
        FROM
        (
            SELECT sale_id
            FROM dbo.stg_sales
            GROUP BY sale_id
            HAVING COUNT(*) > 1
        ) AS duplicate_sales
    ) AS duplicate_sale_keys,
    (
        SELECT COUNT(*)
        FROM dbo.stg_customers
        WHERE region IS NULL
    ) AS missing_region_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales
        WHERE amount IS NULL
    ) AS missing_amount_rows,
    (
        SELECT COUNT(*)
        FROM dbo.stg_sales AS s
        LEFT JOIN dbo.stg_customers AS c
            ON c.customer_id = s.customer_id
        WHERE c.customer_id IS NULL
    ) AS orphan_sales;
CREATE TABLE dbo.dim_customer
(
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name NVARCHAR(100) NOT NULL,
    region NVARCHAR(50) NOT NULL
);

CREATE TABLE dbo.fact_sales
(
    sale_id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT FK_fact_sales_dim_customer
        FOREIGN KEY (customer_id)
        REFERENCES dbo.dim_customer (customer_id)
);

;WITH ranked_customers AS
(
    SELECT
        customer_id,
        customer_name,
        region,
        ROW_NUMBER() OVER
        (
            PARTITION BY customer_id
            ORDER BY updated_at DESC, source_row_id DESC
        ) AS customer_rank
    FROM dbo.stg_customers
)
INSERT INTO dbo.dim_customer
    (customer_id, customer_name, region)
SELECT
    customer_id,
    customer_name,
    region
FROM ranked_customers
WHERE customer_rank = 1
  AND region IS NOT NULL;

;WITH ranked_sales AS
(
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,
        ROW_NUMBER() OVER
        (
            PARTITION BY sale_id
            ORDER BY source_row_id DESC
        ) AS sale_rank
    FROM dbo.stg_sales
)
INSERT INTO dbo.fact_sales
    (sale_id, customer_id, sale_date, amount)
SELECT
    rs.sale_id,
    rs.customer_id,
    rs.sale_date,
    rs.amount
FROM ranked_sales AS rs
INNER JOIN dbo.dim_customer AS c
    ON c.customer_id = rs.customer_id
WHERE rs.sale_rank = 1
  AND rs.amount IS NOT NULL;

SELECT
    (SELECT COUNT(*) FROM dbo.dim_customer) AS customer_count,
    (SELECT COUNT(*) FROM dbo.fact_sales) AS sale_count,
    (SELECT SUM(amount) FROM dbo.fact_sales) AS curated_revenue;
CREATE OR ALTER VIEW dbo.vw_region_daily_sales
AS
SELECT
    c.region,
    f.sale_date,
    COUNT(*) AS sale_count,
    SUM(f.amount) AS revenue
FROM dbo.fact_sales AS f
INNER JOIN dbo.dim_customer AS c
    ON c.customer_id = f.customer_id
GROUP BY
    c.region,
    f.sale_date;
SELECT
    region,
    sale_date,
    sale_count,
    revenue,
    SUM(revenue) OVER
    (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM dbo.vw_region_daily_sales
ORDER BY region, sale_date;
Reconcile the trusted total

The reporting rows sum to 320.00. The naive staging join returned 760.00.

  • Add the North revenue of 120.00 to the South revenue of 200.00.
  • Compare the resulting 320.00 with the naive staging total of 760.00.

Where did the extra 440.00 come from?

  • The duplicate customer key made each matching sale join to two customer rows.
  • The duplicate sale key repeated the same sale before the staging join.
  • The latest-customer rule kept one row for each valid customer key.
  • The ranked-sale rule kept one row for each valid sale key.
  • The non-null filters removed incomplete customer plus sale rows.
  • The curated customer join rejected the orphan sale.

Before the final check, which regional values do you expect to see in the Results pane?

  • Return to 05_reporting_query.sql in Query editor (preview) from earlier.
  • Select Run above the query area.

You should see North with revenue plus running revenue of 120.00. You should see South with revenue plus running revenue of 200.00.

You now have a reusable reporting layer backed by 320.00 of curated revenue. Analysts can query regional results without rebuilding the transformation logic.

Secret mission

Turn the Checks into a Quality Gate

Your profiling query finds five defects, but it still depends on someone remembering to run it. In this mission, you will package those checks into a stored procedure that reports the defect counts and stops a bad batch.

Clean Up Your Resources

Clean Up Your Resources

Your Azure SQL Database uses the monthly free offer. Choose whether to keep it available, let it auto-pause, or delete its resource group.

Cost warning

Charges are possible only if the monthly allowances are exceeded and paid continuation is selected.

  • Keep Auto-pause the database until next month as the free-limit behavior.
  • Avoid selecting Continue using database for additional charges if you want to prevent paid usage.

Resources you used:

  • One Azure resource group in the Microsoft Azure portal.
  • One Azure SQL logical server containing the database.
  • Four tables named dbo.stg_customers, dbo.stg_sales, dbo.dim_customer, and dbo.fact_sales.
  • One reporting view named dbo.vw_region_daily_sales.
  • One stored procedure named dbo.usp_check_staging_quality.

Keep everything running

No action is needed. Choose this if you want to keep practising with the curated model or quality gate.

  • Leave the Azure resource group in place.
  • Return to the database's Overview page in the Azure portal.
  • Check Free monthly vCore amount to confirm how much of the monthly allowance remains.
  • Close the browser tab containing Query editor (preview) when you finish querying.
  • Keep 00_connection_check.sql through 07_run_quality_gate.sql available for future practice.

Pause - I'll come back to this later

Open query connections can prevent the serverless database from auto-pausing. Your database objects remain available while the connection is closed.

  • Close the browser tab containing Query editor (preview).
  • Leave the Azure resource group in place so the database can auto-pause according to its configuration.
  • Keep 00_connection_check.sql through 07_run_quality_gate.sql available for your return.

The closed query connection stops consuming activity that could keep the database awake.

Delete - I don't want to use this again

Deleting the resource group is a big step. It removes the logical server and database together with all four tables, the reporting view, and the quality-gate procedure.

  • Switch back to the Microsoft Azure portal tab from earlier.
  • Select Resource groups.
  • Select the project resource group you created.
  • Record its displayed name here: your Azure resource group name.
  • Select Delete resource group.
  • Type your Azure resource group name into the confirmation field.
  • Select Delete.

After deletion completes, you should no longer see the resource group in the Resource groups list. This confirms that the logical server and database have also been deleted.

Nice Work!

Nice Work!

You did it! You built a sales analytics mart in Azure SQL Database that turns messy staging rows into a trusted regional report.

Here's what you learned:

  • Reproduced a duplicate-join failure that inflated revenue from 320.00 to 760.00. This exposed why plausible totals need validation.
  • Applied data profiling to count duplicate keys, missing values, and orphaned records. Used CTEs with ROW_NUMBER() to load two valid customers and two valid sales into a curated dimension-and-fact model.
  • Published a regional reporting view that returns daily revenue with running totals for each region. The trusted reporting total is 320.00.
  • Completed the optional Secret Mission by building dbo.usp_check_staging_quality. The procedure reports all five defect counts before blocking the batch with error 50001.

Ready to quiz yourself?