a
Scope
Multi-Source Data Ingestion
Ingests structured CSV data from two distinct source companies — a parent company (pre-aggregated monthly order data) and a child company (daily transactional order files from an S3 landing zone) — into a unified Delta Lake catalog.
Medallion Architecture (Bronze ? Silver ? Gold)
Three-layer Delta Lake architecture. Bronze stores raw ingested data with file metadata. Silver applies data quality rules, transformations, and cross-table joins. Gold holds curated, analytics-ready dimension and fact tables.
Data Quality & Transformation
Systematic data quality enforcement at Silver layer: duplicate removal, city typo correction, product name spell-fixing, title-case standardisation, date format normalisation across multiple formats, and price validation with negative value handling.
Full Load & Incremental Load
Separate pipelines for initial full load (all historical data) and daily incremental load (new files only from the landing zone). Incremental uses a staging table pattern with MERGE operations to upsert only changed or new records into Silver and Gold.
Cross-Company Fact Consolidation
Child company daily order data is aggregated to monthly granularity then merged with parent company monthly data using Delta MERGE (upsert). The resulting unified gold fact table covers sales across both entities with a shared product and customer schema.
Enriched Gold View & BI Dashboard
A denormalised SQL view (`vw_fact_orders_enriched`) joins the Gold fact table with all dimensions — date, customer, product, and gross price — computing total revenue (sold_quantity × price_inr). Powers a Power BI FMCG sales dashboard.
Medallion Layer Structure — fmcg Catalog
Bronze
Raw Ingestion Layer
Raw CSV files loaded as-is with file metadata (_metadata.file_name, file_size) and read_timestamp. Delta format with Change Data Feed enabled.
bronze.customers
bronze.products
bronze.gross_price
bronze.orders
bronze.staging_orders
Silver
Cleansed & Enriched Layer
Data quality rules applied. Duplicates removed, typos corrected, dates normalised, prices validated. Products joined with gross_price. Orders joined with product_code.
silver.customers
silver.products
silver.gross_price
silver.orders
silver.staging_orders
Gold
Analytics-Ready Layer
Business-level entities. Dimensions promoted to gold. Child monthly aggregated + parent merged into unified fact. Enriched view for BI.
gold.dim_customers
gold.dim_products
gold.dim_gross_price
gold.dim_date
gold.sb_fact_orders
gold.vw_fact_orders_enriched
b
Features & Functionality
| Notebook / Module | Key Functionality | Techniques Used | Status |
|---|---|---|---|
| setup_catalog | Creates the fmcg catalog and Bronze / Silver / Gold schemas in Databricks Unity Catalog | SQL DDL via %sql magic; Unity Catalog namespace structure | Done |
| utilities | Shared schema name constants (bronze_schema, silver_schema, gold_schema) used across all notebooks via %run | %run directive; Databricks notebook utilities | Done |
| dim_date_table_creation | Generates a complete date dimension table with year, month, quarter, month name, and month_start_date for time-intelligence joins | PySpark date sequence generation; Gold table write | Done |
| customers_data_processing | Bronze ingestion ? Silver: drop duplicates on customer_id, trim whitespace in customer_name, correct city name typos via mapping dictionary | dropDuplicates, F.trim, F.when/F.otherwise mapping; Delta overwrite | Done |
| products_data_processing | Bronze ingestion ? Silver: deduplicate on product_id, fix initcap on category, fix "Protien" ? "Protein" spelling, add division column via category mapping | F.initcap, F.regexp_replace (case-insensitive), F.when multi-branch mapping | Done |
| pricing_data_processing | Bronze ingestion ? Silver: normalise month field across 4 date formats, validate and fix gross_price (negative ? positive, non-numeric ? 0), join with products to attach product_code | F.coalesce + F.try_to_date multi-format parsing; regex numeric validation; inner join enrichment | Done |
| full_load_fact | Bulk ingest all landing CSVs ? Bronze (append). Transform to Silver: filter null order_qty, clean customer_id, strip weekday prefix from dates, parse multi-format dates, deduplicate, join with products. Write to Gold fact table. Aggregate child daily data to monthly and MERGE with parent. | dbutils.fs.mv (landing ? processed); DeltaTable MERGE upsert; F.trunc monthly aggregation; groupBy + agg | Done |
| incremental_load_fact | Processes only new landing files daily. Writes to Bronze (append) and a staging table (overwrite). Applies same transformations as full load on staging data only. MERGE staging into Silver and Gold. Moves processed files to archive folder. | Staging table pattern; DeltaTable MERGE on composite key; file archival via dbutils.fs.mv | Done |
| Enriched Gold View | CREATE OR REPLACE VIEW joining fact_orders with dim_date, dim_customers, dim_products, and dim_gross_price. Computes total_amount_inr = sold_quantity × price_inr (year-matched price join). | SQL denormalised view; year-based price join; derived metric column | Done |
| Power BI Dashboard | FMCG sales dashboard connected to the Gold enriched view. Visualises sales by product, category, division, market, channel, customer, and time period. | Power BI; Databricks connector; pre-built PDF dashboard deliverable | Done |
Data Quality Transformations at Silver Layer
Customers
1
Drop duplicate rows on customer_id2
Trim leading/trailing whitespace from customer_name3
Correct city typos via mapping (Bengaluruu ? Bengaluru, Hyderbad ? Hyderabad, NewDelhi ? New Delhi)Products
1
Drop duplicate rows on product_id2
Apply F.initcap to standardise category casing3
Fix "Protien" ? "Protein" (case-insensitive regex) in product_name and category4
Derive division column from category (e.g. Energy Bars ? Nutrition Bars)Gross Price
1
Normalise month field across 4 date formats (yyyy/MM/dd, dd/MM/yyyy, yyyy-MM-dd, dd-MM-yyyy) using F.coalesce + F.try_to_date2
Validate gross_price: convert negatives to positive, set non-numeric to 03
Inner join with silver.products to attach product_codeOrders (Fact)
1
Filter out rows with null order_qty2
Clean customer_id: non-numeric values replaced with sentinel "999999"3
Strip weekday prefix from dates ("Tuesday, July 01, 2025" ? "July 01, 2025")4
Parse dates across 4 formats using F.coalesce + F.try_to_date5
Deduplicate on composite key: order_id + date + customer_id + product_id + qtyc
Pictorial Process Flow
?
Source Files
CSV landing in S3 (daily / full)
?
Bronze
Raw ingest + file metadata
?
Silver
DQ rules · transforms · joins
?
Gold
Dims + merged fact table
?
Merge
Child + Parent unified
Dashboard
Power BI FMCG view
1
Catalog & Schema Setup
Run setup_catalog.ipynb to create the fmcg Unity Catalog with Bronze, Silver, and Gold schemas. Load shared schema constants via utilities.ipynb using %run.
2
Dimension Processing — Customers, Products, Pricing
Each dimension notebook reads CSVs from S3 into Bronze Delta tables. Silver transformations apply DQ rules (deduplication, typo correction, date/price normalisation). Products notebook also derives the division column from category mapping.
3
Date Dimension Generation
dim_date_table_creation generates a complete date spine with year, quarter, month name, short name, and month_start_date — used for time-intelligence joins in the enriched view.
4
Full Load — Child Company Fact Orders
All daily order CSVs from the S3 landing zone are ingested into Bronze (append mode). After DQ transformations (null filter, customer_id cleaning, multi-format date parsing, deduplication) and product_code enrichment via join, data is written to Silver. Gold fact table created on first run. Files archived from landing/ to processed/.
5
Cross-Company Merge (Child + Parent)
Child daily Gold data is grouped to monthly granularity (F.trunc "MM"). The monthly aggregated child data is then Delta-MERGEd with the parent company's existing monthly fact data using composite key matching (date + product_code + customer_code). The result is a single unified Gold fact table covering both companies.
6
Incremental Load (Daily Pipeline)
New daily CSVs arriving in the landing zone are processed by incremental_load_fact.ipynb. A staging table isolates only the new data. The same transformations are applied to staging only, then MERGE operations upsert into Silver and Gold — avoiding full reprocessing of historical data.
7
Enriched Gold View & Power BI Dashboard
vw_fact_orders_enriched denormalises the Gold fact table by joining all four dimensions (date, customer, product, gross_price with year-based price matching). Derives total_amount_inr as sold_quantity × price_inr. Power BI connects directly to this view for the FMCG sales dashboard.
d
Data Flow Diagram
Source Inputs
Parent Company: dim_customers.csv
Parent Company: dim_products.csv
Parent Company: dim_gross_price.csv
Parent Company: fact_orders.csv (monthly)
Child Company: customers.csv
Child Company: products.csv
Child Company: gross_price.csv
Child Company: orders_YYYY_MM_DD.csv (daily, S3 landing)
Incremental: orders_2025_12_*.csv (new daily files)
Databricks Pipeline
PySpark · Delta Lake · Unity Catalog
? Bronze: Raw ingest + metadata
? Silver: DQ rules + enrichment
? Staging: Incremental isolation
? Delta MERGE (upsert)
? Change Data Feed enabled
? File archival (landing ? processed)
? Monthly aggregation (F.trunc)
? Cross-company consolidation
? Silver: DQ rules + enrichment
? Staging: Incremental isolation
? Delta MERGE (upsert)
? Change Data Feed enabled
? File archival (landing ? processed)
? Monthly aggregation (F.trunc)
? Cross-company consolidation
Outputs
fmcg.gold.dim_customers
fmcg.gold.dim_products (+ division)
fmcg.gold.dim_gross_price
fmcg.gold.dim_date
fmcg.gold.sb_fact_orders (unified)
gold.vw_fact_orders_enriched (view)
Derived metric: total_amount_inr
Archived processed files (S3)
Power BI FMCG Sales Dashboard (PDF)
Gold Layer Schema — Key Fields
dim_customers
customer_code · customer · market · platform · channel
dim_products
product_code · division · category · product · variant
dim_gross_price
product_code · price_inr · year
dim_date
date_key · year · quarter · month_name · month_short_name · month_start_date
sb_fact_orders
date · product_code · customer_code · sold_quantity
vw_fact_orders_enriched
All dims joined + total_amount_inr (sold_qty × price_inr)
e
Technology Stack
Platform
Databricks
Unity Catalog; Databricks notebooks with %run, dbutils, widgets; cluster-managed Spark runtime
Unity Catalog
Processing
Apache Spark (PySpark)
DataFrame API for all transformations; F.coalesce, F.when, F.regexp_replace, F.trunc, F.try_to_date, Window functions
PySpark 3.x
Storage Format
Delta Lake
ACID transactions; Change Data Feed; DeltaTable MERGE for upserts; schema evolution with mergeSchema; time travel capability
Delta 3.x
Catalog
Unity Catalog
Three-level namespace: catalog.schema.table; fmcg catalog with bronze / silver / gold schemas
3-level namespace
Cloud Storage
Amazon S3
Source data landing zone (s3://sportsbar-final/); file archival pattern using dbutils.fs.mv (landing/ ? processed/)
S3 · dbutils.fs
Ingestion Pattern
_metadata Auto-Loader
_metadata.file_name and _metadata.file_size captured at Bronze for full data lineage and audit traceability
_metadata cols
Load Strategy
Full + Incremental Load
Separate notebooks for initial full load (all history) and daily incremental via staging table pattern; MERGE upserts at Silver and Gold
MERGE · Staging
Dashboarding
Power BI
Connected to vw_fact_orders_enriched Gold view; FMCG sales dashboard covering product, category, market, channel, and time dimensions
Databricks connector
Language
Python + SQL
PySpark for transformations; %sql magic cells for DDL and view creation; mixed-language notebooks throughout
Python 3 · SQL