Proof of Concept · Data Engineering & eCommerce Analytics

Databricks
eCommerce Analysis Pipeline

A complete multi-currency global eCommerce analytics pipeline built on Databricks and Delta Lake. It ingests six raw dimension and fact datasets from a Unity Catalog Volume, applies a Bronze ? Silver ? Gold medallion architecture with rich data quality transformations per entity, derives revenue metrics in local and INR-normalised currency across seven countries, and delivers a fully denormalised Gold view powering Databricks AI/BI Genie exploration and dashboard analysis.

Bronze ? Silver ? Gold · 6 Notebooks 7-Currency FX Conversion to INR 7 Countries · Region Mapping Databricks AI/BI Genie Exploration Unity Catalog Volumes · Delta Lake
a

Scope

Six-Source Data Ingestion via Unity Catalog Volumes
Six raw CSV datasets (brands, category, products, customers, date, order_items) are read from a Unity Catalog Volume path (/Volumes/ecommerce/source_data/raw/). Explicit schemas defined per source to catch anomalies at ingest — no inferSchema shortcut used.
Medallion Architecture with Entity-Specific DQ
Each of the six entities passes through its own Bronze ? Silver ? Gold transformation path. Silver applies entity-specific data quality rules: typo correction, case standardisation, unit stripping, anomaly mapping, negative value fixing, and multi-format date parsing.
Multi-Currency FX Conversion to INR
Order items carry prices in seven currencies (INR, AED, AUD, CAD, GBP, SGD, USD). A fixed-rate FX lookup DataFrame is joined at the Gold layer to compute sale_amount_inr — a normalised INR revenue metric enabling cross-country revenue comparison on a single scale.
Global Customer Dimension with Region Mapping
Customers span seven countries: India, Australia, United Kingdom, United States, UAE, Singapore, and Canada. The Gold customer dimension enriches raw state codes with a country-aware region label (e.g. MH ? West in India, FL ? South in US, VIC ? SouthEast in Australia).
Derived Revenue Metrics at Gold
The Gold fact table derives four calculated fields per line item: gross_amount (qty × unit_price), discount_amount (gross × discount_pct / 100, ceiling-rounded), net_amount (gross - discount + tax), and net_amount_inr (net × FX rate). Coupon usage flagged as a binary coupon_flag column.
AI/BI Genie Exploration & Dashboard
A fully denormalised Gold view (fact_transactions_denorm) joins the fact table with the date and product dimensions, exposing all analytical fields including hour_of_day. This powers Databricks AI/BI Genie for natural language business questions and a BI dashboard for revenue, product, and customer analysis.
Medallion Layer Structure — ecommerce Catalog
Bronze
Raw Ingestion Layer
Explicit-schema CSV reads from Unity Catalog Volumes. File path and ingest timestamp captured per record. Delta overwrite with mergeSchema.
bronze.brz_brands
bronze.brz_category
bronze.brz_products
bronze.brz_customers
bronze.brz_date
bronze.brz_order_items
Silver
Cleansed & Enriched Layer
Entity-specific DQ transformations per table. Type casting, anomaly correction, deduplication, case normalisation, unit stripping, and spelling fixes applied.
silver.slv_brands
silver.slv_category
silver.slv_products
silver.slv_customers
silver.slv_date
silver.slv_order_items
Gold
Analytics-Ready Layer
Joined, enriched, and metric-derived tables. FX conversion to INR. Region mapping on customers. Denormalised view for BI and Genie.
gold.gld_dim_products
gold.gld_dim_customers
gold.gld_dim_date
gold.gld_fact_order_items
gold.fact_transactions_denorm (view)
7 Countries · Customer Geography
???? India
MH · GJ · RJ · KA · TN · TS · AP · KL · UP · WB · DL
???? Australia
VIC · WA · NSW · QLD
???? United Kingdom
ENG · WLS · NIR · SCT
???? United States
MA · FL · NJ · CA · NY · TX
???? United Arab Emirates
AUH · DU · SHJ
???? Singapore
SG
???? Canada
BC · AB · ON · QC · NS
b

Features & Functionality

Notebook Key Functionality Techniques Status
setup_catalog Creates ecommerce Unity Catalog with Bronze, Silver, and Gold schemas via %sql magic CREATE CATALOG / SCHEMA IF NOT EXISTS; USE CATALOG Done
1_dim_bronze Ingests brands, category, products, customers, and date CSVs from Unity Catalog Volume into five Bronze Delta tables with explicit schemas and file metadata Explicit StructType schemas; _metadata.file_path; Delta overwrite + mergeSchema Done
2_dim_silver Applies entity-specific DQ transformations: brands (trim, special-char strip, category_code anomaly mapping), category (dedup, uppercase), products (unit strip, comma?dot, uppercase codes, material spelling, negative rating fix), customers (country-aware region derivation) F.trim, F.regexp_replace, .replace() mapping, dropDuplicates, F.upper, F.abs, F.when multi-branch Done
3_dim_gold Builds Gold dimension tables: gld_dim_products (products × brands × category joined via CTE, COALESCE for nulls), gld_dim_customers (region enrichment via country-state map + UDF), gld_dim_date (passed through from silver) SQL CTE; LEFT JOIN with COALESCE; Python dict-to-UDF region mapping; country-aware state resolution Done
1_fact_bronze Ingests daily order_items CSVs from Volume landing folder into Bronze Delta table. All columns loaded as StringType to safely capture mixed-format anomalies before transformation All-StringType schema; landing folder path; _metadata capture; Delta overwrite Done
2_fact_silver Deduplicates on (order_id, item_seq). Cleans quantity ("Two"?2), unit_price ($-strip), discount_pct (%-strip), coupon_code (lower+trim), channel (web?Website, app?Mobile). Parses dt and order_ts across two date formats. Strips non-numeric from tax_amount. F.when literal replacement; F.regexp_replace; F.coalesce + F.to_timestamp multi-format; F.lower; F.trim Done
3_fact_gold Derives gross_amount, discount_amount (ceil), net_amount, date_id, coupon_flag. Joins FX rates DataFrame to compute net_amount_inr (ceiling-rounded). Selects clean final schema as gld_fact_order_items. F.ceil; FX rates broadcast join; F.date_format date_id; F.lit coupon flag; column rename to analytical aliases Done
fact_transactions_denorm (view) CREATE OR REPLACE VIEW joining gld_fact_order_items with gld_dim_date (date_id) and gld_dim_products (product_id). Adds extract(HOUR FROM transaction_ts) as hour_of_day for intraday analysis. SQL CREATE VIEW; three-table JOIN; EXTRACT(HOUR) derived column Done
Databricks AI/BI Genie Natural language query exploration on the Gold view. Questions include: monthly revenue trends, top 10 products by August revenue, biggest revenue day, avg revenue per customer by channel, customer distribution by country, and avg revenue per region. Databricks AI/BI Genie; NL-to-SQL on denorm view; pre-defined business questions Done
Data Quality Transformations at Silver Layer
Brands
1
Trim whitespace from brand_name (e.g. " NovaWave " ? "NovaWave")
2
Strip non-alphanumeric chars from brand_code via regexp_replace
3
Map category_code anomalies: GROCERY?GRCY, BOOKS?BKS, TOYS?TOY
Category
1
Drop duplicates on category_code
2
Uppercase all category_code values (ce ? CE, app ? APP)
Products
1
Strip "g" suffix from weight_grams and cast to Integer (e.g. "305g" ? 305)
2
Replace comma with dot in length_cm and cast to Float ("22,2" ? 22.2)
3
Uppercase category_code and brand_code
4
Fix material spelling: Coton?Cotton, Alumium?Aluminum, Ruber?Rubber
5
Convert negative rating_count to positive (F.abs)
Order Items (Fact)
1
Deduplicate on (order_id, item_seq)
2
Convert "Two" ? 2 in quantity; cast to Integer
3
Strip "$" from unit_price; cast to Double
4
Strip "%" from discount_pct; cast to Double
5
Lowercase + trim coupon_code
6
Map channel: web?Website, app?Mobile
7
Parse dt and order_ts across two timestamp formats via F.coalesce
8
Strip non-numeric chars from tax_amount; cast to Double
FX Rates Applied at Gold (as of 2025-10-15, base: INR = 1.00)
INR = 1.00· AED = 24.18· AUD = 57.55· CAD = 62.93· GBP = 117.98· SGD = 68.18· USD = 88.29
c

Pictorial Process Flow

?
Volume Source
6 CSV datasets from Unity Catalog Volume
?
Bronze
Raw ingest + explicit schema + file metadata
?
Silver
Entity-specific DQ + type casting + normalisation
?
Gold
Joined dims + FX metrics + denorm view
Genie + Dashboard
AI/BI NL queries + BI visualisation
1
Catalog & Schema Setup setup_catalog.ipynb creates the ecommerce Unity Catalog with Bronze, Silver, and Gold schemas. Source data sits in a Unity Catalog Volume at /Volumes/ecommerce/source_data/raw/ — accessible to all notebooks without external S3 credentials.
2
Bronze — Dimension Ingestion (1_dim_bronze) Five dimension CSVs (brands, category, products, customers, date) are read with explicit StructType schemas — keeping anomalous columns as StringType where data quality issues are known (e.g. weight_grams, length_cm). File path and ingest timestamp added per record. Written to six Bronze Delta tables with mergeSchema.
3
Bronze — Fact Ingestion (1_fact_bronze) order_items CSVs from the landing folder loaded with all-StringType schema to safely handle mixed-format values (currency symbols, word quantities like "Two", percentage signs). File metadata captured per row.
4
Silver — Dimension DQ Transformations (2_dim_silver) Each dimension runs its own transformation logic: brands (trim names, strip special chars from codes, remap anomalous category_codes); category (dedup, uppercase); products (unit stripping from weight, decimal fix on length, case normalisation, material spelling correction, abs on negative ratings); customers (region derivation from country+state maps).
5
Silver — Fact DQ Transformations (2_fact_silver) order_items cleaned: dedup on (order_id, item_seq); quantity word-to-int ("Two"?2); currency symbol and percent strip from prices; multi-format timestamp parsing for both dt and order_ts; channel label normalisation; tax_amount non-numeric stripping. All columns properly typed.
6
Gold — Dimension Joins & Region Enrichment (3_dim_gold) gld_dim_products built via SQL CTE joining products × brands × category with COALESCE for missing lookups. gld_dim_customers enriched with a region column derived from a country-aware state mapping dictionary converted to a PySpark UDF.
7
Gold — Revenue Metrics & FX Conversion (3_fact_gold) Four revenue metrics derived per order line: gross_amount, discount_amount (ceiling), net_amount, and net_amount_inr (via FX join + ceiling). date_id generated as yyyyMMdd integer for dimension joins. coupon_flag set. Final gld_fact_order_items written to Gold Delta table.
8
Denormalised View + Genie Exploration + Dashboard fact_transactions_denorm view joins gld_fact_order_items with gld_dim_date and gld_dim_products, adding hour_of_day. Databricks AI/BI Genie uses this view to answer natural language questions on revenue trends, top products, channel performance, and customer geography.
d

Data Flow Diagram

Source Inputs
brands.csv (brand_code · brand_name · category_code)
category.csv (category_code · category_name)
products.csv (product_id · sku · category · brand · dimensions · rating)
customers.csv (customer_id · phone · country_code · country · state)
date.csv (date dimension attributes)
order_items/landing/order_items_YYYY-MM-DD.csv
Fields: dt · order_ts · customer_id · order_id · item_seq · product_id · quantity · currency · unit_price · discount_pct · tax_amount · channel · coupon_code
FX rates dict (7 currencies ? INR)
Country-state region maps (7 countries)
eCommerce Pipeline
PySpark · Delta Lake · Unity Catalog
? Explicit schema enforcement
? Unity Catalog Volume reads
? Entity-specific DQ rules
? SQL CTE joins at Gold
? FX broadcast join
? Region UDF mapping
? Derived revenue metrics
? Delta overwrite + mergeSchema
Outputs
ecommerce.gold.gld_dim_products (enriched with brand + category names)
ecommerce.gold.gld_dim_customers (+ region column)
ecommerce.gold.gld_dim_date
ecommerce.gold.gld_fact_order_items (gross · discount · net · net_inr · coupon_flag · date_id)
ecommerce.gold.fact_transactions_denorm (denorm view + hour_of_day)
AI/BI Genie NL query results
BI Dashboard (revenue, product, customer, channel analytics)
Gold fact_order_items — Key Fields
Identifiers
date_id · transaction_date · transaction_ts · transaction_id · customer_id · seq_no · product_id
Order Attributes
channel · coupon_code · coupon_flag · unit_price_currency · quantity · unit_price · discount_percent
Derived Metrics
gross_amount · discount_amount · tax_amount · net_amount · net_amount_inr
Denorm View Adds
year · month_name · day_name · is_weekend · quarter · week · sku · category_name · brand_name · color · size · rating_count · hour_of_day
e

Technology Stack

Platform
Databricks
Unity Catalog; Databricks Notebooks with %sql magic and Python cells; cluster-managed Spark runtime
Unity Catalog
Processing
Apache Spark (PySpark)
DataFrame API; F.when, F.regexp_replace, F.coalesce, F.ceil, F.abs, F.upper, F.trim; StructType explicit schemas; broadcast join for FX rates
PySpark 3.x
Storage Format
Delta Lake
ACID transactions; Delta overwrite with mergeSchema across all 12 tables; time travel and audit capability
Delta 3.x
Data Access
Unity Catalog Volumes
Raw CSVs stored in Unity Catalog Volume (/Volumes/ecommerce/source_data/raw/) — governed, credential-free access from notebooks
UC Volumes
Query Layer
Spark SQL
%sql magic cells; CREATE OR REPLACE TABLE with CTE; CREATE OR REPLACE VIEW; EXTRACT(HOUR); COALESCE for null handling
SQL + PySpark
AI/BI
Databricks Genie
Natural language to SQL exploration on the Gold denorm view; pre-defined business questions for revenue, product, customer, and channel analysis
AI/BI Genie
Dashboard
Databricks BI Dashboard
Connected to fact_transactions_denorm Gold view; revenue trend, top products, channel comparison, customer geography visualisations
Databricks BI
Language
Python + SQL
PySpark for all transformations; %sql magic for DDL and view creation; Python dicts for FX rates and region mapping
Python 3 · SQL
Catalog
Unity Catalog
ecommerce catalog; three-level namespace (catalog.schema.table); 12 managed Delta tables across bronze/silver/gold schemas
3-level namespace