Fiaz AnjumData · BI · Transformation
Let's Connect

Case Study 01 · Gourmet Pakistan

A Medallion-Architecture Enterprise Data Warehouse on ClickHouse

One governed analytics platform across three manufacturing units: CDC ingestion from ERP and operational systems, dbt-modeled gold datasets, and Superset decision dashboards.

  • ClickHouse EDW
  • Airbyte CDC
  • dbt
  • Apache Superset
  • 3 manufacturing units
Organization
Gourmet Pakistan
Role
BI & Data Engineering Analyst
Period
Jan 2026 – Present
Sector
Manufacturing (FMCG)

Overview

A multi-site manufacturer needed one trusted view of finance, procurement, inventory and sales across its Lahore, Faisalabad and Multan units. I designed and built an Enterprise Data Warehouse on ClickHouse using a Bronze–Silver–Gold Medallion architecture. It ingests ERP and operational data through Airbyte CDC, transforms it with dbt, and serves it to Apache Superset decision dashboards with role-based access.

Business problem

  • Operational and financial data was spread across separate source systems (ERPNext/GERP, GSSM and RMS) and three manufacturing units.
  • Procurement, inventory and sales questions needed consistent definitions and up-to-date figures, not one-off extracts.
  • Heavy analytical queries over large ledger and inventory datasets needed a platform built for analytical performance.
  • Management needed a dashboard estate with controlled access and visibility into how it was actually being used.

Architecture

  1. Sources

    • ERPNext / GERP
    • GSSM
    • RMS
    • 3 manufacturing units

  2. Ingestion

    • Airbyte CDC
    • Incremental sync
    • Python pipelines

  3. Bronze

    • Raw CDC tables
    • ClickHouse
    • Full history

  4. Silver

    • dbt staging
    • Deduplication
    • Conformed keys

  5. Gold

    • Finance
    • Procurement
    • Inventory
    • Sales

  6. Serving & BI

    • Apache Superset
    • RBAC
    • KPI card system
    • Usage analytics
Conceptual architecture: Sources → Ingestion → Bronze → Silver → Gold → Serving & BI. Simplified; no confidential system details.

My responsibilities

  • End-to-end EDW design: layer boundaries, naming conventions, data flow and documentation for management and technical teams.
  • CDC ingestion design with Airbyte from ERPNext/GERP, GSSM and RMS.
  • dbt modeling of BI-ready gold datasets for finance, procurement, inventory and sales.
  • Dashboard design and development in Apache Superset, including a reusable HTML/CSS KPI card design system.
  • Query performance engineering on ClickHouse.
  • Access governance (RBAC) and adoption monitoring.
  • Support for Python pipelines and an MLflow (Docker) environment for demand forecasting.

Technologies

  • ClickHouse
  • Airbyte (CDC)
  • dbt
  • ERPNext / GERP
  • SQL
  • Python
  • Apache Superset
  • deck.gl
  • MLflow
  • Docker

Implementation

  1. 01

    Ingest with CDC

    Airbyte CDC connections replicate ERP and operational tables into Bronze, preserving change history and avoiding full reloads.

  2. 02

    Standardize in Silver

    dbt staging models clean, type-cast, deduplicate and conform keys across source systems and manufacturing units.

  3. 03

    Model the Gold layer

    Subject-area dbt models produce BI-ready datasets for finance, procurement, inventory and sales, with shared business definitions.

  4. 04

    Optimize for analytics

    Heavy queries were refactored with pre-aggregation CTEs and ARRAY JOIN expansion to resolve ClickHouse memory-limit errors.

  5. 05

    Serve decision dashboards

    Superset dashboards: Procurement Control Tower, RM/PM ABC Inventory & Value, Finished Goods Inventory, Sales Analytics (actual vs. target vs. AOP) and Supply Chain Geo (deck.gl choropleth, MTD/YTD, freight).

  6. 06

    Govern and measure adoption

    RBAC in Superset, plus a Dashboard Usage & Access Analytics dashboard built from Superset metadata.

Outcomes

  • 3Manufacturing units on one EDW
  • 3Source systems integrated via CDC
  • 4Gold subject areas (finance, procurement, inventory, sales)
  • 6Superset decision & governance dashboards

Figures as documented in my CV.

Business impact

  • Procurement can see supplier performance, cost impact and savings ((PY Rate − CY Rate) × CY Qty), plus Local vs. Import spend, in one control tower.
  • Inventory exposure is visible by value and quantity, in-hand vs. in-transit, with ABC classification and days-of-coverage.
  • Sales and supply chain teams track actual vs. target vs. AOP and territory-level performance with freight analysis.
  • Leadership gets governed access and measurable adoption of the analytics estate.

Demo

Interactive demo: procurement saving analysis

Synthetic data

The Procurement Control Tower measures savings as (PY Rate − CY Rate) × CY Qty. Adjust the illustrative values:

Saving / (cost impact)PKR 300,000
Rate change−5.95%
StatusSaving

Synthetic values for demonstration only. No company data is shown.

This case study describes my role and documented results. Architecture is shown conceptually, and no confidential data, system details or client information is disclosed.

NextHBS — Statistics Centre Abu Dhabi

Business & Project Collaboration

Have a Data, Analytics or Digital Transformation Project?

Looking to modernize your data infrastructure, build an enterprise data warehouse, automate business reporting, or implement intelligent business analytics?

Let's discuss your requirements and explore how I can help turn your business challenges into practical technology solutions.

Discuss Your Project Send a Project Inquiry Connect on LinkedIn

Engagements I can discuss

  • Enterprise Data Engineering Projects
  • Data Warehouse & Lakehouse Implementation
  • Power BI & Superset Development
  • Business Intelligence Consulting
  • ERP Data Integration
  • Reporting Automation
  • Digital Transformation Initiatives
  • Analytics Architecture & Strategy
  • Technical Consulting & Collaboration

Inquiries are handled personally and confidentially. I take on engagements as an independent professional, not as a registered consulting firm.