End-to-end pipeline
Bronze → Silver → Gold
2.91M
Source Records
all domains, dirty
2.02M
Sales Rows
raw FactSales
1.84M
Clean Sales
91.4% passed
173K
Quarantined
tagged, not lost
11
Brands
from 39 spellings
Medallion architecture · OneLake
Sources
SAP · SQL Server · LIMS · WMS
- FactSales 2.0M
- Inventory 0.8M
- Production 5.5K
- BOM 44K · QC 22K
→
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 schema · Direct Lake
- surrogate keys
- DimDate (fiscal)
- DAX semantic model
- Power BI + Copilot
Manufacturing — ingredients to product
BOM per 1,000 L · 6 filling lines · 3 shifts
Sales by channel (clean Gold)
18,000+ Western Cape outlets · DSD / van-sales / wholesale
Phase 1 on-prem estate
SQL Server DW ≈ 4.2 TB on-prem
The data is deliberately dirty
Before → After Cleaning
Categorical consolidation
Brand spellings39 → 11 ✓
UoM codes (EA/CS/DSP)21 → 3 ✓
Channel spellings18 → 3 ✓
FactSales quarantine — 173,346 rows tagged
Bad / unparseable date84,851
Non-positive qty48,412
Orphan customer FK22,630
Duplicate invoice14,739
Qty outlier2,714
Legacy
dw.vSales silently dropped these. Silver tags & routes them to stewards.Defects measured in the raw extracts
| Table | Defect | Rate | Silver fix |
|---|---|---|---|
| DimProduct | Invalid / missing GS1 barcode | ~18% | check-digit validation + flag |
| DimProduct | NBS placeholder 0000000 | 16% | → NULL |
| DimProduct | Whitespace / casing in name | 15% | trim + collapse + title-case |
| DimCustomer | Non-ISO date formats | 35% | multi-format parse → DATE |
| DimCustomer | Missing VAT number | 18% | flag vat_missing |
| FactSales | Money as text (R12.06) | 25% | strip currency → decimal |
| FactSales | Non-ISO dates | 26% | multi-format parse |
| FactSales | Negative / zero qty | 2.5% | quarantine |
| FactSales | Orphan customer FK | 1.2% | quarantine |
| FactSales | Double-posted invoices | 16,000 | dedup on natural key |
SAP master data · GS1 standards
Product Master — EA / CS / DSP
DimProduct (clean) — UoM hierarchy with valid GS1 barcodes
| SKU | Brand | Description | UoM | Base Qty | Barcode Type | Barcode | Valid | Sellable | Orderable |
|---|---|---|---|---|---|---|---|---|---|
| SKU000001 | Coca-Cola | Coca-Cola 330ml Can | EA | 1 | EAN-13 | 6001000000001 | ✓ | Y | Y |
| SKU000002 | Coca-Cola | Coca-Cola 330ml Can Case 24 | CS | 24 | GTIN-14 | 16001000000008 | ✓ | N | Y |
| SKU000086 | Sprite | Sprite 330ml Can | EA | 1 | EAN-13 | 6001000000086 | ✓ | Y | Y |
| SKU000166 | Bonaqua | Bonaqua Sparkling 500ml PET | EA | 1 | EAN-13 | 6001000001664 | ✓ | Y | Y |
| SKU000130 | Monster | Monster Energy Green 500ml Can | EA | 1 | EAN-13 | 6001000001305 | ✓ | Y | Y |
| SKU000131 | Monster | Monster Energy Green Display 6 | DSP | 6 | GTIN-14 | 26001000001302 | ✓ | Y | Y |
| SKU000204 | Powerade | Powerade Mountain Blast 500ml | EA | 1 | EAN-13 | 6001000002043 | ✓ | Y | Y |
| SKU000218 | Cappy | Cappy Orange 1L Tetra | EA | 1 | EAN-13 | 6001000002180 | ✓ | Y | Y |
Portfolio: Coca-Cola · Sprite · Fanta · Stoney · Bonaqua · Powerade · Cappy · Monster · Powerplay · Predator · Schweppes
EA · consumer unit
6001000000001
EAN-13 · scanned at till · is_sellable=1
CS · master carton
16001000000008
GTIN-14 · indicator 1 · orderable only · is_sellable=0
DSP · display / shrink
26001000001302
GTIN-14 · indicator 2 · sellable multipack
Master-data defect
Barcode Collisions
8
Shared Codes
1 barcode → many SKUs
17
SKUs Affected
wrong item at POS
13%
Invalid / Missing
bad check digit, 0000000
100%
Registered
none deleted
One barcode, several products — POS rings up the wrong item
| Shared Barcode | # Products | Primary SKU (winner) | Colliding products | Action |
|---|---|---|---|---|
| 6001000000544 | 3 | SKU000123 | Fanta Orange 330ml PET · Schweppes Lemonade 330ml Can · Fanta 330ml PET | re-barcode 2 |
| 6001000000322 | 2 | SKU000025 | Coca-Cola 1L PET · Coca-Cola Zero 500ml PET | re-barcode 1 |
| 6001000000391 | 2 | SKU000090 | Sprite 500ml Can · Bonaqua Still 1.5L PET | re-barcode 1 |
| 6001000000728 | 2 | SKU000037 | Coca-Cola 2L Can · Bonaqua Still 1L PET | re-barcode 1 |
| 6001000001046 | 2 | SKU000046 | Coca-Cola No Sugar 330ml PET · Schweppes Soda Water 1L Can | re-barcode 1 |
| 6001000000773 | 2 | SKU000174 | Powerade Mountain Blast 750ml · Powerade Naartjie 500ml | re-barcode 1 |
Detected by
dq.usp_Detect_BarcodeCollisions; the DW maps the shared code to a deterministic primary SKU (orderable first, then lowest SKU). Losers are flagged BarcodeIsShared=1 and queued to MDM — nothing is deleted.Two separate barcode defects
Collisions are the dangerous one — the code is "valid" but points at the wrong product.
Resolution flow
1 · scan stg.Product for EA codes used by >1 SKU
2 · register clash in
dq.BarcodeCollision3 · pick primary (orderable → lowest SKU)
4 · DimProduct: set
BarcodeIsShared, PrimarySKUForBarcode5 · facts join via primary → no double-count
6 · MDM re-barcodes the losers with new GS1 codes
Gold layer · Direct Lake
Star Schema
Build on-prem first, migrate later
Phase 1 (on-prem) → Phase 2 (Fabric)
Phase 1 — build & operate on-prem (SQL Server · SSIS · SSAS)
SQL Server DW (
stg → dw star schema → dq quarantine) · SSIS Biml-generated packages orchestrate nightly loads · T-SQL procs clean & resolve barcodes · SSAS Tabular cube (DAX + RLS) · SSRS reports. This is the working current state.
SSIS · Biml packages00_Master → stage → dims → facts → process cube
→
Data Factory + Apache NiFicloud pipelines · CDC · streaming
SQL Server DW · stg/dw/dqenforced FKs · SCD cols · on-prem
→
OneLake Lakehouse + WarehouseDelta · Bronze/Silver/Gold
T-SQL load procsset-based clean · barcode resolution · quarantine
→
dbt models + testssame rules, version-controlled
SSAS Tabular cubeVertiPaq import · processed nightly
→
Direct Lake semantic modelreads Delta live · no processing
DAX measures + RLS roleTotal Net Sales · Sales YoY% · YTD
→
Same DAX, ported 1:1the payoff of doing Phase 1 right
SSRS · SQL Agentpaginated PDFs · scheduled jobs
→
Power BI + Copilot · Data Activatorself-service · NL query
The dimensional model, cleaning rules, barcode logic and DAX are built once in Phase 1 and reused in Phase 2 — migration becomes a re-platform, not a rewrite.
Gold · cleaned
FactSales — sample rows
1,842,654 clean transaction lines (of 2,016,000 raw)
| Sale ID | Date | Customer | Channel | Region | Product | UoM | Qty | Unit R | Disc | Net Sale R | Order Type |
|---|---|---|---|---|---|---|---|---|---|---|---|
| SO001600001 | 2024-05-14 | USave Wynberg | Retail | Cape Flats | Sprite 330ml Can | EA | 50 | 3.68 | 0% | 184.00 | Telesales |
| SO001600002 | 2024-05-01 | Makro Paarl | Wholesale | Northern Suburbs | Coca-Cola Zero 2L Case 6 | CS | 2080 | 126.97 | 2.5% | 257,495.16 | Wholesale |
| SO001600003 | 2024-02-05 | Dis-Chem Tableview | Retail | Cape Flats | Bonaqua Sparkling 500ml | EA | 5 | 3.94 | 7.5% | 18.22 | DSD |
| SO001600009 | 2024-08-22 | Spur Somerset West | HoReCa | Helderberg | Fanta Orange 500ml | EA | 120 | 5.12 | 5% | 583.68 | Van Sales |
| SO001600014 | 2024-11-30 | Shoprite Bellville | Retail | Northern Suburbs | Coca-Cola 2L Case 6 | CS | 340 | 61.40 | 10% | 18,788.40 | Wholesale |
| SO001600021 | 2024-03-18 | Monster Tavern Athlone | HoReCa | Cape Flats | Monster Energy 500ml | EA | 72 | 22.80 | 0% | 1,641.60 | DSD |
Money parsed from text (
R… / ZAR…), dates conformed, net_sale recomputed where source was null. Bad rows live in fact_sales_quarantine.