KO | EN

GitLite

Minimalist Open Source Repository Search Engine

gitlite — search

#developer-tools #codex #typescript #ai-agents #claude-code #dsh #dsh-plugin #open-source #cli #deepseek-harness #macos #ai-agent

ETL-pipeline

★ 1 Open GitHub ↗

End-to-end data engineering pipeline on Databricks that consolidates sales data from a parent FMCG company and an acquired brand into a unified lakehouse, using PySpark, Delta Lake, and a Bronze-Silver-Gold medallion architecture with full + incremental loads and Delta MERGE upserts.

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# FMCG Multi-Source Sales Data Pipeline (Databricks Lakehouse)

An end-to-end data engineering pipeline built on Databricks that consolidates sales data from a parent FMCG company and a newly acquired sports-nutrition brand ("Sports Bar") into a single Unity Catalog lakehouse — using a Medallion (Bronze → Silver → Gold) architecture, Delta Lake, and PySpark.

## Overview

When a company acquires another brand, their data doesn't arrive in the same shape: different schemas, different data quality standards, different load frequencies. This project simulates that scenario — ingesting, cleaning, standardizing, and merging the acquired company's customer, product, pricing, and order data into the parent company's existing gold-layer data model, and serving the result through a BI dashboard.

## Architecture

![Architecture](https://raw.githubusercontent.com/VISHV-P-10/ETL-pipeline/main/resources/project_architecture.png)

- **Ingestion**: Raw CSV files land in S3 and are picked up by Databricks Lakeflow Jobs.
- **Bronze**: Raw data loaded as-is into Delta tables, with `read_timestamp`, `file_name`, and `file_size` metadata columns for traceability. Change Data Feed enabled for downstream tracking.
- **Silver**: Cleaned, deduplicated, and standardized data — see [Data Quality](#data-quality--transformations) below.
- **Gold**: Business-ready dimension and fact tables, standardized to match the parent company's data model, then merged into the parent's gold layer using Delta Lake `MERGE`.
- **Serving**: A denormalized reporting view (star schema join) powers a Databricks SQL dashboard.

Governed end-to-end with **Unity Catalog** (`fmcg` catalog, with `bronze`, `silver`, and `gold` schemas).

## Data Sources

| Source | Type | Grain | Load Pattern |
|---|---|---|---|
| Parent company — customers, products, pricing, orders | Full snapshot | Monthly | One-time full load |
| Child company (Sports Bar) — customers, products, pricing | Full snapshot | As-is | Full load |
| Child company (Sports Bar) — orders | Daily files | Daily | Incremental (full load for backfill + daily incremental job) |

Since the child company reports orders at daily grain but the parent's fact table is monthly, incremental loads recompute and re-aggregate the affected month(s) before merging — not just append new rows.

## Pipeline Structure

```
1_codes/
├── 1_setup/                        # Catalog/schema setup, date dimension, shared utilities
├── 2_dimension_data_processing/    # Customers, products, pricing (Bronze → Silver → Gold → merge with parent)
└── 3_fact_data_processing/         # Orders: full load + incremental load (Bronze → Silver → Gold → merge with parent)
```

Each dimension notebook follows the same pattern:
1. **Bronze** — read raw CSV, write to Delta with metadata columns
2. **Silver** — clean and transform (see below), align schema to the parent's data model
3. **Gold** — select final business columns, write to a child-scoped gold table (`sb_dim_*`)
4. **Merge with parent** — `MERGE INTO` the parent's gold dimension table (upsert on business key)

The orders/fact pipeline additionally uses a **landing → processed** file pattern (files are moved out of the landing zone once ingested) and staging tables to isolate each incremental batch before merging.

## Data Quality & Transformations

- Deduplication on natural keys (`customer_id`, `product_id`)
- Trimming whitespace and fixing inconsistent title-casing (e.g., `energy bars` → `Energy Bars`)
- Correcting known spelling errors (e.g., `Protien` → `Protein`)
- Standardizing inconsistent city name typos (e.g., `Hyderabadd`, `Bengalore`) against an allowed-values list
- Business-confirmed manual fixes for missing values (e.g., filling missing customer cities per a business-provided mapping)
- Multi-format date parsing (`yyyy/MM/dd`, `dd/MM/yyyy`, `yyyy-MM-dd`, `dd-MM-yyyy`) and stripping weekday prefixes from date strings
- Validating and correcting numeric fields (e.g., coercing invalid/negative gross prices to sane values)
- Deriving new attributes to match the parent schema (e.g., `division` mapped from `category`, `variant` extracted from product name, composite `customer` key built from name + city)

## Modeling

**Gold layer star schema:**
- `dim_date`, `dim_customers`, `dim_products`, `dim_gross_price` (dimensions)
- `fact_orders` (monthly grain: date, product, customer, sold quantity)

**Merge strategy**: All dimension and fact merges use Delta Lake's `MERGE INTO` (`whenMatchedUpdate` / `whenNotMatchedInsert`) keyed on business codes (`customer_code`, `product_code`), so pipelines are idempotent and safely re-runnable.

**Reporting view** (`vw_fact_orders_enriched`): denormalizes the fact table against all four dimensions and derives `total_amount_inr = sold_quantity * price_inr` for direct use in BI tools.

## Dashboard

A Databricks SQL dashboard (`2_dashboarding/fmcg_dashboard.pdf`) built on the reporting view, with filters for year, quarter, month, channel, and category, showing:
- Total revenue, quantity sold, unique customers, and average selling price
- Top products and top variants by revenue
- Revenue share by channel
- Monthly revenue trend
- Revenue by customer
- Price vs. quantity sold (product-level)

## Tech Stack

`Databricks` · `PySpark` · `Delta Lake` · `Unity Catalog` · `SQL` · `Databricks SQL Dashboards` · `Lakeflow Jobs`

## Repo Structure

```
0_data/                  # Sample source data (parent + child company, full & incremental)
1_codes/                 # Pipeline notebooks (setup, dimensions, facts)
2_dashboarding/          # Reporting view SQL + dashboard export
resources/                # Architecture diagram
```

ETL-pipeline

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README