From 5 Million Dirty Rows
to One Trusted Catalog

A consumer-packaged-goods data story — 5,014,409 catalog rows modelled on Libstar's public brand portfolio, cleaned by Azure Data Factory, served by Synapse serverless SQL, scored with machine learning, and reconciled to the rand across Qlik Sense, Excel and this page.

Case-study note: Libstar is a real JSE-listed CPG group, but every record here is synthetic — generated (seeded, reproducible) on Libstar's public brand/category model to demonstrate the pipeline without using any private data. Not affiliated with Libstar Holdings.
0raw rows generated (incl. planted duplicates)
0clean unique products after ADF
0rows quarantined — each with a reason
R1.17tntrailing 12-month revenue (synthetic)
0.71price-model R² · MAE R12.71

1 · More than a product list

CPG catalogs rot quietly. Prices arrive as "R 1,234.56" from one system and "ZAR1234" from another; weights get captured in grams instead of kilograms; brands are typed by hand; category codes from a previous org structure ("Groceries", "Snacks and confectionery") linger years after a restructure. None of it looks urgent — until mispriced SKUs erode margin, duplicate listings distort demand forecasts, and rollup reporting breaks exactly when a range review needs it.

This project demonstrates the governed fix: a pipeline that quantifies the mess instead of silently filtering it, resolves it with auditable rules, and publishes one set of numbers that every downstream consumer — the live Qlik Sense app, the Excel pack, the ML models, this page — reconciles to exactly.

2 · The pipeline: dirty in, trusted out

generate_dirty_products.py (5.1M rows, seeded mess)
        │  gzip CSV
        ▼
ADLS Gen2  raw/ ───────────► reference/ (brand map + dims)
        │
        ▼
Azure Data Factory · mapping data flow (8 vCores, ~6 min, ≈ R10/run)
  price/date/unit/boolean normalisation · brand lookup resolves misspelled
  brands AND legacy category names · dedupe (latest wins) · quarantine
        │  snappy parquet
        ▼
ADLS Gen2  curated/ ──► Synapse serverless SQL (rpt.* views, $0 idle)
        │
        ├──► Qlik Sense Cloud app (5 sheets, built via qlik-cli)
        ├──► scikit-learn (pricing · IsolationForest · KMeans)
        ├──► Excel report (formula-driven, reconciled)
        └──► Ebook + this data story
Data quality funnel
Figure 1 — 5,014,409 deduplicated rows in, 4,284,971 unique clean products out. Dates and prices are the biggest offenders.

3 · What the shelf looks like

Libstar's simplified operating model has two product groups. Ambient categories carry 77% of revenue (R904.1bn of R1,170.3bn), led by Spreads, Meal ingredients and Wet condiments; perishables — dairy, convenience meals, value-added meats, baby, fresh mushrooms — hold the rest. Margins cluster tightly around the 35% portfolio average, so revenue mix, not margin spread, is what moves the total.

Revenue by category
Figure 2 — Revenue by category, coloured by product group.
Margin by category
Figure 3 — Margin by category vs the portfolio average.

Category scorecard (full population)

CategoryGroupSKUsRevenue 12mAvg marginAvg price
SpreadsAmbient products526,813R160,395,514,14035.0%R87.63
Meal ingredientsAmbient products554,781R155,347,925,71535.0%R74.20
Wet condimentsAmbient products580,617R154,977,096,22035.0%R68.20
BeveragesAmbient products428,482R135,697,476,59235.0%R94.98
Dry condimentsAmbient products452,143R122,903,791,56035.0%R69.79
BakingAmbient products427,832R94,861,638,38535.0%R46.87
Convenience MealsPerishable products295,388R85,861,274,10535.0%R79.71
SnackingAmbient products311,271R79,899,697,15935.0%R62.37
Value-added MeatsPerishable products176,523R57,959,630,79035.0%R102.24
DairyPerishable products194,459R48,642,328,23735.0%R59.31
BabyPerishable products168,555R39,589,869,57235.0%R52.17
Fresh MushroomsPerishable products168,107R34,146,064,40535.0%R39.17
Group and channel mix
Figure 4 — Product-group split and the four routes to market.

4 · Brand power: three ways to win

Three brand solutions compete on the same shelf: Libstar's own brands (Lancewood, Denny Mushrooms, Cape Herb & Spice…), Principal brands represented in South Africa (Bonne Maman, Kikkoman, Tabasco, Maille), and private label for retailers. Principal brands price ~35% above comparable own-brand products; private label sits ~20% below — yet "Retailer Brands" is the single biggest revenue line (R103.7bn) because it spans all twelve categories.

Top brands
Figure 5 — Top 15 brands by revenue, coloured by brand solution.

Top 10 brands

BrandBrand solutionSKUsRevenue 12mAvg margin
Retailer BrandsPrivate label and dealer-own brands430,236R103,650,387,10435.0%
Finlar Fine FoodsLibstar Brands268,984R84,928,535,83035.0%
RialtoLibstar Brands269,175R81,006,558,21035.0%
Khoisan GourmetLibstar Brands267,412R79,563,401,19235.0%
GoldcrestLibstar Brands268,612R77,820,300,50335.0%
Cape FoodsLibstar Brands268,381R74,364,367,07035.0%
Dickon Hall FoodsLibstar Brands269,141R73,699,851,20335.0%
ChamonixLibstar Brands269,004R73,286,135,13135.0%
Montagu FoodsLibstar Brands269,449R72,784,041,44635.0%
Cape Coastal HoneyLibstar Brands134,815R40,291,885,41235.0%

5 · Where South Africa buys

Hover (or tap) a province. Gauteng leads (R343.8bn), then Western Cape and KwaZulu-Natal — sales concentrate in the five provinces where the business has production or distribution footprints.

R34.4bn
R343.8bn
Hover a province
12-month revenue—
Products stocked—
Average margin—

Routes to market

Retail and wholesale
R711.0bn
Food service
R206.6bn
Industrial and contract manufacturing
R137.5bn
Export
R91.8bn

6 · Data quality — where the numbers leak

The Data Factory flow rejected 729,438 rows (14.5%) — and can say exactly why, row by row. Because reject reasons ship into the same reporting layer as the clean data, data quality becomes a trend a team can manage down, not an invisible loss.

Reject reasonRows
bad_date328,279
bad_price230,750
unknown_brand127,137
bad_price + bad_date17,200
bad_date + unknown_brand9,759
unknown_brand + unknown_category8,039
bad_price + unknown_brand6,615
bad_date + unknown_brand + unknown_category657
bad_price + bad_date + unknown_brand516
bad_price + unknown_brand + unknown_category450
bad_price + bad_date + unknown_brand + unknown_category36
Reconciliation guarantee: the Qlik app, the Excel report, the ebook and this page all read the same aggregate layer. Revenue is R1,170,282,306,879 in all four — to the rand.

7 · Machine learning: pricing & anomalies

Pricing has learnable structure. A gradient-boosted model predicts shelf price from category, brand solution, channel, weight and rating with R² = 0.71 and a mean absolute error of R12.71 on a mean price of R71.69 — evidence that pricing follows the portfolio's architecture, exactly what a well-governed catalog should show.

Price anomalies
Figure 6 — IsolationForest anomalies vs the normal price–margin cloud.
Product segments
Figure 7 — Four KMeans segments: volume movers, premium niche, two mainstream clusters.
Why items get flagged, in plain language: the model scored one million products on price, cost, margin and weight together, and flagged the 2,000 whose combination is most unlike the rest — a heavyweight item priced like a small pack, an implausible margin, a luxury-priced commodity. These usually signal data-entry or pricing errors, not genuine premium products. Recommendation: route this list to category managers before the next price-list publication — correcting them before they reach shelves protects margin and avoids customer-facing pricing errors.

Top 10 to investigate first

ProductCategoryBrandPriceCostMarginScore
Classic Beef Burger Patties 250mlValue-added MeatsFinlar Fine FoodsR227.69R181.8220.1%-0.0801
Premium Orange Marmalade 750gSpreadsBonne MamanR260.87R208.6320.0%-0.0791
Everyday Hot Chocolate 250mlBeveragesRialtoR315.01R169.8146.1%-0.0760
Classic Strawberry Preserve 2kgSpreadsDickon Hall FoodsR193.94R154.8820.1%-0.0749
Select Chicken Nuggets 250gValue-added MeatsFinlar Fine FoodsR210.95R168.5820.1%-0.0749
Everyday Chicken Nuggets 500mlValue-added MeatsFinlar Fine FoodsR224.52R179.4420.1%-0.0749
Traditional Mac & Cheese Meal 1kgConvenience MealsMillennium FoodsR219.97R174.7020.6%-0.0748
Classic Instant Cappuccino 250mlBeveragesChamonixR281.27R155.0044.9%-0.0739
Organic Green Tea 500mlBeveragesChamonixR203.88R162.2320.4%-0.0732
Gourmet Apricot Jam 2kgSpreadsCape Coastal HoneyR236.00R186.4021.0%-0.0724

8 · Azure in production

Everything runs on a free-trial credit by design — no always-on compute anywhere in the chain.

ResourcePurposeIdle cost
stpargoparcels01ADLS Gen2 — raw / curated zones≈ $0.03/month
adf-pargoparcels-zaData Factory — cleaning data flow$0 (per-run billing)
syn-pargoparcelsSynapse serverless SQL endpoint$0 ($5/TB scanned)
pargo-trial-budget$200 budget · email alerts at 50/75/90%free

Kill-switch once all consumers are loaded: az group delete --name rg-pargoparcels --yes. The Qlik app (data loads in-app), the 36 MB .qvf export, the Excel pack, the ebook and this page all keep working after teardown.

9 · How it's built

LayerTools
Data generationPython (pandas, numpy) — seeded synthetic catalog, reproducible mess
StorageAzure Data Lake Storage Gen2 (raw / curated zones, Git LFS mirror in repo)
TransformationAzure Data Factory mapping data flows
ServingAzure Synapse Analytics serverless SQL
Machine learningscikit-learn — HistGradientBoosting, IsolationForest, KMeans
BIQlik Sense Cloud — app, measures and 5 sheets built programmatically via qlik-cli
Reportingopenpyxl (Excel) · python-docx (ebook) · matplotlib (charts + SA map) · this page
Ops & costAzure CLI, PowerShell, Cost Management budget alerts

Rebuild from a clean clone:

git lfs pull
python scripts/build_reference_dims.py && python scripts/build_brand_map.py
python scripts/generate_dirty_products.py          # ~15 min
adf\deploy_adf.ps1  →  run pl_clean_products  →  synapse\deploy_synapse.ps1
python ml/train_models.py && python scripts/make_charts.py
python scripts/build_excel_report.py && python scripts/build_ebook.py
python scripts/build_data_story.py                 # this page