Scaling Zenith E-Commerce

The Problem (Including Startup Brief)

Zenith E-Commerce is a fast-growing online retail startup that experienced explosive growth but struggled with scaling its internal analytics. The existing data environment was fragmented, slow, and lacked the necessary structure to answer critical, high-level business questions.

The Data Engineering team needed to transition from raw, operational data to a secure, query-optimized, and scalable data warehouse. The core problem was the lack of a unified, performance-driven analytics architecture to track revenue trends, manage inventory risk, and identify high-value customers. Without a solution, the business faced inaccurate reporting, costly manual data processes, and inevitable stock-outs.

The Solution

The solution centered on implementing a modern, cloud-native data stack:

1. Snowflake Data Warehouse: Used as the central, high-performance platform for all data storage and processing.

2. Star Schema Data Modeling: Transformed fragmented raw data into a clean Star Schema with a central FACT_SALES table and dimension tables (DIM_CUSTOMERS, DIM_PRODUCTS, DIM_DATES).

3. Security & Governance (RBAC): Implemented Role-Based Access Control (RBAC) to ensure secure, least-privilege access for BI tools and analysts.

4. Complex SQL & Connectivity: Developed complex $\text{SQL}$ queries (using CTEs and multi-table joins) and configured Power BI connectivity to securely consume the refined data model.

The Result

The project successfully delivered a robust analytics platform that empowered executives and operational teams with immediate, accurate insights.

The key results driven by the SQL analysis and Power BI visualizations include:

• Identified Critical Inventory Risk: Pinpointed the highest-revenue product with stock critically low (8 units remaining), allowing the operations team to avert potential revenue loss.

• Customer Value Segmentation: Identified the Most Valuable Customer (MVC), Adriana Knox, who generated over $91.8K in lifetime revenue, enabling the development of targeted loyalty programs.

• Geospatial Targeting: Pinpointed specific cities (e.g., Molinafort, MH) where high-value customers are concentrated, optimizing geo-targeted marketing spend.

• Operational Benchmarking: Confirmed the seasonal peak, with Q4 2024 generating $770.4M in revenue, establishing clear benchmarks for future capacity planning.

• Payment Strategy: Revealed that in high-revenue regions, Bank Transfer and PayPal transactions are the preferred methods, guiding optimization efforts for the checkout funnel.

This architecture provides Zenith E-Commerce with the foundation needed to maintain its high-growth trajectory and transition from reactive reporting to proactive, data-driven decision-making.

CLICK ANY LINK BELOW TO VIEW DELIVERABLES

(1). CLICK HERE TO VIEW THE PROJECT ON GITHUB

(2). CLICK HERE TO VIEW POWER BI REPORT (PDF)

(3). CLICK HERE TO VIEW SCREENSHOTS OF QUERIES ON SNOWFLAKES