PB

PenBev · Data & Systems Health

DASHBOARD 1 OF 2 · PIPELINE & DATA-QUALITY HEALTH · SQL SERVER → FABRIC MEDALLION
● pipeline healthy · 91.6% clean
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

Sales (transactional)
2.52M
Delivery
59,681
Inventory
48,640
BOM (ingredients)
21,952
Production
2,744
Expense postings
1,152
→ 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%)

Bad date
105,182
Qty ≤ 0
60,531
Orphan customer
28,188
Duplicate
18,492
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 variants39 → 14standardise
Double-posted invoices20,000dedup natural key
Shared barcodes4 GTINsresolve → 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.
DASHBOARD 1 OF 2 · Data & Systems Health · 2.66M source records · 91.6% clean · governed master + dirty transactional · medallion on OneLake