PB

PenBev · Fabric Migration

PENINSULA BEVERAGES · COCA-COLA BOTTLER · CAPE TOWN · BUILD ON-PREM → MIGRATE TO FABRIC
Phase 1: SQL Server · SSIS · SSAS → Phase 2: Microsoft Fabric
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

Conc.
3.6 kg
Sugar
108 kg
CO₂
6.5 kg
Water
985 L
Flavour
2.1 kg
BOM per 1,000 L · 6 filling lines · 3 shifts

Sales by channel (clean Gold)

Retail
R3.58b
Whole
R866m
HoReCa
R679m
18,000+ Western Cape outlets · DSD / van-sales / wholesale

Phase 1 on-prem estate

SSIS
185
SSRS
340
Tables
214
Cubes
11
SQL Server DW ≈ 4.2 TB on-prem
The data is deliberately dirty

Before → After Cleaning

Categorical consolidation

Brand spellings39 → 11 ✓
raw
39
clean
11
UoM codes (EA/CS/DSP)21 → 3 ✓
raw
21
clean
3
Channel spellings18 → 3 ✓
raw
18
clean
3

FactSales quarantine — 173,346 rows tagged

173K 8.6% of rows
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

TableDefectRateSilver fix
DimProductInvalid / missing GS1 barcode~18%check-digit validation + flag
DimProductNBS placeholder 000000016%→ NULL
DimProductWhitespace / casing in name15%trim + collapse + title-case
DimCustomerNon-ISO date formats35%multi-format parse → DATE
DimCustomerMissing VAT number18%flag vat_missing
FactSalesMoney as text (R12.06)25%strip currency → decimal
FactSalesNon-ISO dates26%multi-format parse
FactSalesNegative / zero qty2.5%quarantine
FactSalesOrphan customer FK1.2%quarantine
FactSalesDouble-posted invoices16,000dedup on natural key
SAP master data · GS1 standards

Product Master — EA / CS / DSP

DimProduct (clean) — UoM hierarchy with valid GS1 barcodes

SKUBrandDescriptionUoMBase QtyBarcode TypeBarcodeValidSellableOrderable
SKU000001Coca-ColaCoca-Cola 330ml CanEA1EAN-136001000000001✓YY
SKU000002Coca-ColaCoca-Cola 330ml Can Case 24CS24GTIN-1416001000000008✓NY
SKU000086SpriteSprite 330ml CanEA1EAN-136001000000086✓YY
SKU000166BonaquaBonaqua Sparkling 500ml PETEA1EAN-136001000001664✓YY
SKU000130MonsterMonster Energy Green 500ml CanEA1EAN-136001000001305✓YY
SKU000131MonsterMonster Energy Green Display 6DSP6GTIN-1426001000001302✓YY
SKU000204PoweradePowerade Mountain Blast 500mlEA1EAN-136001000002043✓YY
SKU000218CappyCappy Orange 1L TetraEA1EAN-136001000002180✓YY
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# ProductsPrimary SKU (winner)Colliding productsAction
60010000005443SKU000123Fanta Orange 330ml PET · Schweppes Lemonade 330ml Can · Fanta 330ml PETre-barcode 2
60010000003222SKU000025Coca-Cola 1L PET · Coca-Cola Zero 500ml PETre-barcode 1
60010000003912SKU000090Sprite 500ml Can · Bonaqua Still 1.5L PETre-barcode 1
60010000007282SKU000037Coca-Cola 2L Can · Bonaqua Still 1L PETre-barcode 1
60010000010462SKU000046Coca-Cola No Sugar 330ml PET · Schweppes Soda Water 1L Canre-barcode 1
60010000007732SKU000174Powerade Mountain Blast 750ml · Powerade Naartjie 500mlre-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
17 SKUs
Bad check digit
~7%
0000000 / blank
~6%
Letter O for 0
few
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.BarcodeCollision
3 · pick primary (orderable → lowest SKU)
4 · DimProduct: set BarcodeIsShared, PrimarySKUForBarcode
5 · facts join via primary → no double-count
6 · MDM re-barcodes the losers with new GS1 codes
Gold layer · Direct Lake

Star Schema

FACT SALES · 1.84M sale_id (PK) date_key · product_key customer_key · promo_key qty · unit_price discount_pct · net_sale litres_sold dq_status = OK DIM DATE date_key (PK) year/quarter/month fiscal_year (Jul-Jun) is_weekend DIM PRODUCT · 242 product_key (PK) brand · category uom (EA/CS/DSP) barcode + valid_flag pack_ml · base_qty is_sellable/orderable DIM CUSTOMER · 18K customer_key (PK) channel (3 std) region · area credit_limit vat_missing_flag DIM PROMOTION promo_key (PK) promo_no (ZWAK) bonus_buy_type start/end_date status FACT PRODUCTION · 5.5K production_id (PK) line · shift · sku planned/actual_qty oee_pct · waste_qty brix · co2_vol → FACT_BOM (44K) → FACT_QUALITY (22K) FACT INVENTORY · 806K movement_id (PK) mov_type GR/GI/TRF/ADJ qty · stock_value depot · batch_no source: WMS/SAP
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 IDDateCustomerChannelRegionProductUoMQtyUnit RDiscNet Sale ROrder Type
SO0016000012024-05-14USave WynbergRetailCape FlatsSprite 330ml CanEA503.680%184.00Telesales
SO0016000022024-05-01Makro PaarlWholesaleNorthern SuburbsCoca-Cola Zero 2L Case 6CS2080126.972.5%257,495.16Wholesale
SO0016000032024-02-05Dis-Chem TableviewRetailCape FlatsBonaqua Sparkling 500mlEA53.947.5%18.22DSD
SO0016000092024-08-22Spur Somerset WestHoReCaHelderbergFanta Orange 500mlEA1205.125%583.68Van Sales
SO0016000142024-11-30Shoprite BellvilleRetailNorthern SuburbsCoca-Cola 2L Case 6CS34061.4010%18,788.40Wholesale
SO0016000212024-03-18Monster Tavern AthloneHoReCaCape FlatsMonster Energy 500mlEA7222.800%1,641.60DSD
Money parsed from text (R… / ZAR…), dates conformed, net_sale recomputed where source was null. Bad rows live in fact_sales_quarantine.
PenBev · Peninsula Beverages · Microsoft Fabric Migration Reference Project · 2.91M source records · generated dirty, cleaned via medallion architecture