Case study 01 · Retail & Distribution

Enterprise Data Warehouse

A Kimball-modelled warehouse that replaced 30+ conflicting spreadsheets with one trusted source of truth.

Data Engineer — modelling, ETL development, reconciliation 5 months

Nightly load: 4h10m → 1h25m
Report query: 48s → 3s
Reports retired: 30+

Executive summary

Designed and delivered an enterprise data warehouse for a multi-store retail and distribution business. The solution consolidates ERP, point-of-sale, CRM and supplier data into a Kimball star schema with full SCD Type 2 history, automated reconciliation and a certified Power BI semantic model — giving finance, sales and operations one agreed version of revenue, margin and stock.

Business problem

Each department produced its own numbers from direct ERP extracts and Excel workbooks. Monthly revenue differed by up to 4% between finance and sales, and month-end reporting took four working days of manual consolidation.

No single definition of net revenue, margin or active customer
Heavy reporting queries running directly against the production ERP
No history of customer or product attribute changes for trend analysis
Manual Excel consolidation with no audit trail

Architecture design

A classic three-layer warehouse on SQL Server, orchestrated by Azure Data Factory with a self-hosted integration runtime for on-premises sources.

L1
Staging (stg)
1:1 copies of source tables, truncated and reloaded or incrementally appended per load. No business logic.
L2
Integration (int)
Cleansed, typed and conformed data; business keys mapped, row hashes computed for change detection.
L3
Presentation (dw)
Kimball star schemas with surrogate keys, SCD Type 2 dimensions and partitioned columnstore facts.
L4
Semantic (Power BI)
A certified dataset with governed measures, row-level security by region and incremental refresh.

Source systems

System	Technology	Volume	Cadence
Oracle ERP	Oracle 19c	~2.5M order lines / month	Nightly incremental
Point of Sale	SQL Server	~6M receipts lines / month	Nightly incremental
CRM	REST API (JSON)	~180K customers	Nightly full
Supplier feeds	CSV via SFTP	40+ files / week	Event-based

Data flow

01Extract
ADF copies changed rows using a LastModified watermark per table into stg.
02Validate
Row counts, null checks on business keys and schema drift checks run before any transformation.
03Integrate
Stored procedures standardise codes, map business keys and compute SHA-256 row hashes.
04Load dimensions
SCD Type 2 MERGE logic versions changed customers and products.
05Load facts
Facts resolve surrogate keys and switch into monthly partitions.
06Reconcile & publish
Source-to-warehouse totals are compared; on success the Power BI dataset refresh is triggered.

Data model

A bus-matrix driven design with conformed Date, Product, Store and Customer dimensions shared by sales and inventory processes.

Facts

FactSales
Grain: One row per order line per day
FactInventorySnapshot
Grain: One row per product per store per day
FactPurchaseOrders
Grain: One row per purchase order line

Dimensions

DimDate
Role-playing for order, ship and invoice dates
DimCustomer
SCD Type 2 on segment, city and country
DimProduct
SCD Type 2 on category and list price
DimStore
SCD Type 1 with region hierarchy
DimSupplier
SCD Type 1

ETL process

Metadata-driven extraction

A control table lists every source object, its load type and watermark column; one parameterised pipeline handles all of them.

Change detection

HASHBYTES over business attributes avoids column-by-column comparisons and makes SCD logic generic.

Late-arriving dimensions

Unknown members (-1) are inserted as inferred rows and enriched when the dimension record arrives.

Audit & logging

Every procedure writes rows read/inserted/updated, duration and run ID to etl.LoadLog.

Tech stack

Storage & compute: SQL Server 2019Clustered columnstoreTable partitioning
Orchestration: Azure Data FactorySelf-hosted IRStored procedures
Quality: Python reconciliationpandasAzure Monitor alerts
Consumption: Power BIDAXRow-level security
DevOps: GitAzure DevOpsSQL Database Projects

SQL example

SCD Type 2 load for DimCustomer (T-SQL)

sql

-- RowHash is computed in staging:
-- HASHBYTES('SHA2_256', CONCAT_WS('|', CustomerName, Segment, City, Country))
DECLARE @LoadDate DATETIME2(0) = SYSUTCDATETIME();

BEGIN TRANSACTION;

-- 1. Expire current rows whose attributes changed
UPDATE d
SET    d.ValidTo   = @LoadDate,
       d.IsCurrent = 0
FROM   dw.DimCustomer AS d
JOIN   stg.Customer   AS s
       ON s.CustomerCode = d.CustomerCode
WHERE  d.IsCurrent = 1
  AND  d.RowHash <> s.RowHash;

-- 2. Insert new customers and new versions of changed customers
INSERT INTO dw.DimCustomer
       (CustomerCode, CustomerName, Segment, City, Country,
        RowHash, ValidFrom, ValidTo, IsCurrent)
SELECT s.CustomerCode, s.CustomerName, s.Segment, s.City, s.Country,
       s.RowHash, @LoadDate, '9999-12-31', 1
FROM   stg.Customer AS s
LEFT JOIN dw.DimCustomer AS d
       ON d.CustomerCode = s.CustomerCode
      AND d.IsCurrent = 1
WHERE  d.CustomerKey IS NULL;

COMMIT TRANSACTION;

Python example

Source-to-warehouse reconciliation after every load

python

"""Compares daily sales totals between Oracle ERP and the warehouse."""
import os

import oracledb
import pandas as pd
import pyodbc

CHECKS = [
    {
        "name": "daily_net_sales",
        "source_sql": """
            SELECT TRUNC(order_date) AS d, SUM(net_amount) AS v
            FROM erp.sales_order_lines
            WHERE order_date >= TRUNC(SYSDATE) - 7
            GROUP BY TRUNC(order_date)""",
        "target_sql": """
            SELECT dd.FullDate AS d, SUM(f.NetAmount) AS v
            FROM dw.FactSales f
            JOIN dw.DimDate dd ON dd.DateKey = f.OrderDateKey
            WHERE dd.FullDate >= DATEADD(day, -7, CAST(GETDATE() AS date))
            GROUP BY dd.FullDate""",
        "tolerance": 0.001,  # 0.1 %
    },
]


def fetch(conn, sql: str) -> pd.DataFrame:
    df = pd.read_sql(sql, conn)
    df.columns = [c.lower() for c in df.columns]
    df["d"] = pd.to_datetime(df["d"]).dt.date
    return df.set_index("d")


def reconcile() -> list[dict]:
    source = oracledb.connect(
        user=os.environ["ERP_USER"],
        password=os.environ["ERP_PASSWORD"],
        dsn=os.environ["ERP_DSN"],
    )
    target = pyodbc.connect(os.environ["DW_CONNECTION_STRING"])
    failures = []

    for check in CHECKS:
        joined = (
            fetch(source, check["source_sql"])
            .join(fetch(target, check["target_sql"]), lsuffix="_src", rsuffix="_dw", how="outer")
            .fillna(0)
        )
        base = joined["v_src"].where(joined["v_src"] != 0, 1)
        joined["diff_pct"] = (joined["v_src"] - joined["v_dw"]).abs() / base

        for day, row in joined[joined["diff_pct"] > check["tolerance"]].iterrows():
            failures.append({
                "check": check["name"],
                "date": str(day),
                "source": float(row["v_src"]),
                "warehouse": float(row["v_dw"]),
            })
    return failures


if __name__ == "__main__":
    issues = reconcile()
    if issues:
        raise SystemExit(f"Reconciliation failed: {issues}")
    print("All reconciliation checks passed.")

Challenges

Challenge

Finance and sales used different definitions of net revenue.

Solution

Ran definition workshops, documented each KPI in a business glossary and implemented one governed measure in the semantic model.

Challenge

Fact loads blocked reporting for hours during the nightly window.

Solution

Loaded into a staging partition and used ALTER TABLE … SWITCH so the published fact is replaced in milliseconds.

Challenge

Orders arrived before their customer records existed in the CRM.

Solution

Inferred-member pattern: insert a placeholder dimension row, then update it in place when the real record arrives.

Performance improvements

Metric	Before	After	Change
Nightly end-to-end load	4h 10m	1h 25m	−66%
Sales report query (P95)	48 s	3.1 s	−94%
FactSales storage	212 GB rowstore	31 GB columnstore	−85%
Month-end close reporting	4 days	Same day	−3 days

Business impact

One governed definition of revenue, margin and active customer used by every department
Month-end reporting delivered on day one instead of day four
ERP production load reduced by moving reporting queries to the warehouse
Full attribute history enabled customer-segment migration analysis for the first time

Lessons learned

Agree on the grain and KPI definitions before writing a single line of ETL.
Reconciliation is a feature: business trust grew fastest once totals were visibly checked every day.
Partition switching and columnstore are the two highest-leverage SQL Server techniques for warehouses.

Success metrics

Reconciliation pass rate: 99.8%Daily source-to-warehouse checks
Reports consolidated: 30+Legacy Excel and ad-hoc extracts retired
Certified dataset users: 140Across finance, sales and operations
Revenue variance: < 0.1%Between departments, down from 4%

Next case studyAzure Data Factory Enterprise Pipeline