End-to-end health
System Overview
2.66M
Source Records
master + transactional
2.52M
Sales Rows (raw)
dirty source
2.31M
Clean Sales
91.6% passed
212K
Quarantined
tagged, not lost
14
Brands
from 39 spellings
Load health — sales pipeline
91.6%
PASS 2,307,607 rows conformed to star
QUARANTINE 212,393 routed with a reason
SLA nightly load · SSIS → SSAS process
Record volumes by domain
→ open Business dashboard (master + transactional)Medallion architecture · OneLake
Bronze → Silver → Gold
Sources
SAP · SQL Server · LIMS · WMS
- Sales 2.52M
- Inventory · Delivery
- Production 2.7K · BOM 22K
- governed master
→
Bronze
Raw Delta · no cleaning
- NiFi / Data Factory
- all-string landing
- lineage columns
- full history
→
Silver
dbt · cleaned & conformed
- type + date parse
- UoM/channel std
- dedup + FK check
- quarantine queue
→
Gold
Star · Direct Lake
- surrogate keys
- SCD2 history
- DAX semantic model
- Power BI + Copilot
Phase 1 builds this on-prem (SQL Server star, SSIS loads, SSAS cube). Phase 2 lifts the same model to Fabric — see Phase 1 → 2 tab.
Quarantine, tagged not dropped
Data Quality
Sales quarantine — 212,393 rows (8.4%)
Routed to
dq.SalesQuarantine with a reason code — never silently dropped.Source defects handled
Money as text (R../ZAR..) | ~25% | strip → decimal |
| Non-ISO / placeholder dates | ~26% | multi-format parse |
| UoM spelling variants | ~20 | → EA/PK1/PK2/PLT |
| Brand spelling variants | 39 → 14 | standardise |
| Double-posted invoices | 20,000 | dedup natural key |
| Shared barcodes | 4 GTINs | resolve → primary |
Master-data integrity
Barcode Health
4
Shared GTINs
1 code → many SKUs
9
Variants Affected
wrong scan at POS
100%
Detection Recall
vs injected truth
100%
Registered
none deleted
Collision resolution
Two distinct defects: (1) invalid check-digit / length /
0000000 placeholder, and (2) collisions — one GTIN on multiple variant articles. usp_Detect_BarcodeCollisions registers each shared GTIN, maps it to a deterministic primary (lowest variant code), flags the rest GTINIsShared=1 for MDM re-barcoding. EAN-13 on EA, GTIN-14 on PK1/PK2, SSCC on pallets.Slowly changing dimensions
SCD2 Audit Health
88
Current Variants
IsCurrent=1
20
Versioned Variants
pack / label change
60
Versioned Customers
credit / channel change
2×
Price Epochs
2025 → 2026 list
Type-2 history pattern
Each change expires the current row (
EffectiveTo set, IsCurrent=0) and inserts a new version (Version+1, new surrogate key, IsCurrent=1). Facts join on the surrogate key, so a sale in 2024 reflects the product/customer/price as it was then — point-in-time correctness. Barcodes carry EffectiveFrom/To for re-barcoding history.Build on-prem first, migrate later
Phase 1 (on-prem) → Phase 2 (Fabric)
SSIS · Biml packagesstage → articles → facts → cube
→
Data Factory + NiFiCDC · OneLake landing
SQL Server star + stg + dqenforced FKs · SCD2
→
OneLake LakehouseBronze / Silver / Gold Delta
T-SQL load procsbarcode · UoM · quarantine
→
dbt models + testssame rules, versioned
SSAS Tabular cubeprocessed nightly
→
Direct Lake modelreads Delta live
Model, cleaning rules, barcode logic, SCD2 and DAX are built once in Phase 1 and reused in Phase 2 — migration is a re-platform, not a rewrite.