Assessing Sales Data Quality with SQL

In the previous article, I mapped the main path through the AdventureWorks2025 sales data.

That gave me the tables, relationships, and grains needed for analysis. It didn’t prove that the data was trustworthy.

A valid foreign key doesn’t guarantee that a date makes business sense. A non-null value can still be misleading. Two records with different primary keys may represent the same real customer, while two businesses with the same name may be entirely separate.

Before creating reporting views or producing recommendations, I want to test the assumptions that later analysis will depend on.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

1. Building a Sales Analytics Solution with SQL: Project Overview
2. Understanding the Sales Data Landscape with SQL
3. Assessing Sales Data Quality with SQL ← You are here
4. Preparing Sales Data for Analysis with SQL
5. Analysing Customer Purchasing Behaviour with SQL
6. Measuring Product Performance with SQL
7. Analysing Regional Sales Performance with SQL
8. Time-Series Analysis with SQL
9. Using Advanced SQL to Generate Business Insights
10. Optimising SQL for Analytical Workloads
11. Designing an Analytics-Friendly Data Model
12. Completing a SQL Sales Analytics Project

Reproduce the Analysis

To reproduce this article, run the complete sales data quality assessment script against AdventureWorks2025 in SQL Server Management Studio. The script is read-only and contains every completeness, uniqueness, validity, consistency, and referential-integrity check used in the assessment.

The Business Problem

Adventure Works wants to use its operational data to understand customers, products, regions, and sales trends.

If the underlying records are incomplete or inconsistent, the resulting reports may still look polished. The problem usually becomes visible later, when totals don’t reconcile or somebody questions why a familiar customer has disappeared from a segment.

For this assessment, I grouped the checks into five areas:

• Completeness: are important values present when the business process requires them?
• Uniqueness: do identifiers and possible business keys contain unexpected repetition?
• Validity: do quantities, prices, discounts, and dates fall within sensible ranges?
• Consistency: do related calculations agree with each other?
• Referential integrity: do records connect to the customers, orders, offers, and products they claim to reference?

I also considered analytical coverage. A column can be perfectly valid but contain too little variation to answer a useful question.

Confirmed

Structural checks passed. No orphaned sales records, invalid ranges, or reconciliation differences were found.

Needs context

Expected nulls are meaningful. Salesperson values are absent on online orders by design, while status and comments have limited analytical coverage.

Needs a rule

Customer type needs interpretation. Some customer accounts reference both a person and a store.

Start with Expectations

A generic query that counts every null value is easy to write, but it doesn’t tell us which nulls are problems.

SalesOrderHeader.SalesPersonID is nullable because online orders don’t have a salesperson. ShipDate is nullable because an operational database may contain orders that haven’t shipped yet. Comment is optional because most orders may not need one.

The correct question is whether a value is missing when the relevant business situation requires it.

SELECT
    OnlineOrderFlag,
    SUM(CASE WHEN SalesPersonID IS NULL THEN 1 ELSE 0 END)
        AS NullSalesPersonID,
    SUM(CASE WHEN SalesPersonID IS NOT NULL THEN 1 ELSE 0 END)
        AS PopulatedSalesPersonID,
    COUNT(*) AS Orders
FROM Sales.SalesOrderHeader
GROUP BY
    OnlineOrderFlag
ORDER BY
    OnlineOrderFlag;

The result shows a clean business pattern:

There are 27,659 null values, but none of them represent missing salesperson data. The null is meaningful because it agrees exactly with the sales channel.

By contrast, Comment is null on all 31,465 orders. That isn’t a transactional error, but the column has no analytical value in this sample.

No orders are missing a customer, territory, or shipping date.

The order completeness and channel-context queries supply the null counts and salesperson cross-check discussed above.

Customer Completeness Needs a Business Rule

The customer model contains one of the more interesting findings.

Every Sales.Customer record references at least a person or a store, so there are no completely unidentified customer accounts. However, the fields aren’t mutually exclusive:

The 635 records with both references aren’t automatically defective. They tell us that a rule such as PersonID IS NOT NULL = individual customer would be unsafe.

For later analysis, I will treat a populated StoreID as a store customer and use the store name. All customer records have a usable name under that interpretation.

The customer-shape query produces the three record counts, while the usable-name check validates the working interpretation.

This is a business-definition issue rather than a cleansing task. Changing or deleting the records would damage valid source data. The right response is to standardise the interpretation in the analytical layer and document it.

Checking Uniqueness Beyond Primary Keys

Primary keys protect technical identifiers. They don’t necessarily prevent duplicate business records.

I checked several candidate keys and combinations:

• SalesOrderNumber
• customer AccountNumber
• the combination of order, product, and special offer on an order line

None contained duplicate groups.

The candidate-key checks return the duplicate-group counts for all three candidates.

Store names produced two repeated values:

• Friendly Bike Shop
• Sports Products Store

Deleting one record from each pair would be a poor response. Looking at their addresses shows that the names belong to stores in different cities, states, and even countries.

The repeated names are worth noting, but the surrounding evidence suggests separate businesses rather than simple duplicates.

The repeated-store-name investigation supplies the business identifiers and locations used to distinguish the records.

This is a useful reminder that a duplicate query identifies candidates for investigation. It doesn’t decide whether two real-world entities are the same.

Testing Numeric Ranges

Sales-order quantities, prices, and discounts have straightforward validity rules.

SELECT
    SUM(CASE WHEN OrderQty <= 0 THEN 1 ELSE 0 END)
        AS InvalidOrderQuantity,
    SUM(CASE WHEN UnitPrice <= 0 THEN 1 ELSE 0 END)
        AS InvalidUnitPrice,
    SUM(
        CASE
            WHEN UnitPriceDiscount < 0 OR UnitPriceDiscount > 1 THEN 1
            ELSE 0
        END
    ) AS InvalidDiscount,
    MIN(OrderQty) AS MinimumOrderQuantity,
    MAX(OrderQty) AS MaximumOrderQuantity,
    MIN(UnitPriceDiscount) AS MinimumDiscount,
    MAX(UnitPriceDiscount) AS MaximumDiscount
FROM Sales.SalesOrderDetail;

The results are internally plausible:

• Order quantities range from 1 to 44.
• Unit prices are positive.
• Discounts range from 0% to 40%.
• 3,282 order lines have a non-zero discount.
• No header-level subtotal, tax, freight, or total-due amount is negative.

The numeric-range queries generate these minimum, maximum, discount, and negative-value checks.

Passing these checks doesn’t prove that every commercial value is correct. A price can be positive and still be attached to the wrong product. What it does show is that the data doesn’t contain basic range violations that would immediately undermine aggregation.

Testing Date Relationships

The order process gives us three useful sequence rules:

SELECT
    SUM(CASE WHEN DueDate < OrderDate THEN 1 ELSE 0 END)
        AS DueDateBeforeOrderDate,
    SUM(CASE WHEN ShipDate < OrderDate THEN 1 ELSE 0 END)
        AS ShipDateBeforeOrderDate,
    SUM(CASE WHEN ShipDate > DueDate THEN 1 ELSE 0 END)
        AS ShipDateAfterDueDate
FROM Sales.SalesOrderHeader;

All three checks return zero.

The date-sequence query supplies the three zero counts.

The database also contains check constraints preventing a due date or shipping date from preceding the order date. Those constraints provide useful protection at source, while the query makes the analytical expectation visible and checks whether shipping exceeded the promised due date.

In another system, I would also examine unusual processing durations and dates outside the expected reporting period. Here, the first task is to establish structural validity rather than declare a delivery-performance threshold without business context.

Reconciling Financial Values

AdventureWorks stores financial values at two grains:

• SalesOrderDetail.LineTotal represents line value after the line discount.
• SalesOrderHeader.SubTotal represents the sum of the order lines.
• TotalDue adds tax and freight to the subtotal.

That gives us two direct consistency tests.

WITH line_totals AS
(
    SELECT
        SalesOrderID,
        SUM(LineTotal) AS CalculatedSubTotal
    FROM Sales.SalesOrderDetail
    GROUP BY
        SalesOrderID
)
SELECT
    SUM(
        CASE
            WHEN ABS(header.SubTotal - line.CalculatedSubTotal) > 0.01
            THEN 1 ELSE 0
        END
    ) AS SubTotalMismatches,
    SUM(
        CASE
            WHEN ABS(
                header.TotalDue
                - (header.SubTotal + header.TaxAmt + header.Freight)
            ) > 0.01
            THEN 1 ELSE 0
        END
    ) AS TotalDueMismatches
FROM Sales.SalesOrderHeader AS header
INNER JOIN line_totals AS line
    ON line.SalesOrderID = header.SalesOrderID;

Both mismatch counts are zero. The maximum difference between the stored subtotal and the sum of its lines is also zero at four decimal places.

The financial-reconciliation query calculates both mismatch counts and the maximum subtotal difference.

This matters because later product analysis will aggregate line totals, while customer and order analysis may use header subtotals. The reconciliation confirms that those paths begin from the same commercial value.

Verifying Relationships

AdventureWorks2025 declares foreign keys across the core sales path, and none of the database’s foreign keys or check constraints are disabled or untrusted.

I still ran explicit anti-join checks for the relationships most likely to affect analysis:

• orders without order lines
• order lines without an order header
• orders without a customer
• order lines without the referenced product and special-offer combination
• offer-product records without a product

Every check returned zero.

The referential-integrity checks test the core analytical path, and the constraint-trust query confirms that SQL Server isn’t ignoring disabled or untrusted protections.

That may seem redundant when SQL Server already enforces the relationships. The explicit checks remain useful for two reasons.

First, they document which relationships the analysis depends on. Second, the same logic can be rerun if the data is later extracted into files, a staging layer, or another platform where the original constraints aren’t present.

The checks can move with the data even when the database protections don’t.

Valid Data Isn’t Necessarily Informative Data

The most important limitation isn’t a broken relationship. It is a lack of variation.

All 31,465 orders have status 5, which represents shipped. That means the dataset can’t support useful analysis of cancellation, rejection, backorder, or in-progress behaviour.

Similarly, every order comment is null. There is nothing to analyse for recurring notes, service issues, or operational exceptions.

The status result comes from the order-status profile, while the all-null comment count is included in the opening completeness query.

These columns are valid. They simply aren’t useful for the questions we might have hoped to ask.

AdventureWorks is a sample database designed to demonstrate a coherent business model. Its cleanliness is helpful for a reproducible portfolio, but it doesn’t reproduce all the ambiguity, entry errors, evolving definitions, and integration problems found in production data.

The value of this assessment is therefore the method and the documented baseline. If later work introduces staging tables, transformations, or a dimensional model, these checks can reveal whether we have accidentally weakened the quality that already exists at source.

Assessment Summary

Recommendations Before Analysis

The sales data is suitable for the next stage of the project, with several decisions carried forward:

No source records need to be “cleaned” or rewritten for the current sales scope. The work required is mostly about preserving meaning and making business definitions consistent.

Next in the Series

The next article will prepare the data for analysis.

I will turn the repeated joins and decisions from the first three articles into reusable SQL views, including consistent customer types, sales channels, product hierarchies, geography, dates, and financial measures. The goal will be to make later analysis simpler without hiding where the numbers come from.