Grocery Store Sim

Project Description

The Grocery Store Simulation is a full-stack data engineering project that demonstrates a complete OLTP to OLAP pipeline through a self-running mock grocery store. The system simulates a 14-hour store day with realistic customer traffic patterns, including rush hour spikes, loyalty vs walk-in customers, and automatic inventory reordering, generating hundreds of transactions per simulated day against a normalized PostgreSQL transactional schema with 8 tables covering products, inventory, employees, customers, transactions, and purchase orders. At the end of each simulated day, a custom ETL pipeline migrates the transactional data into a star schema data warehouse, transforming raw OLTP records into a fact_sales table surrounded by dimension tables for time, product, customer, employee, and payment method, with pre-calculated revenue, cost, and profit measures optimized for analytical queries. Built with Python and PostgreSQL, the project includes a Flask-powered live transaction feed showing real-time inserts as they happen, a crash recovery system using state persistence to handle interruptions cleanly, and a suite of analytical SQL queries covering daily revenue trends, category performance, rush hour analysis, and customer spend comparisons. The simulation runs continuously and headlessly on a Raspberry Pi Zero 2 W acting as a mock server, with the OLAP database accessible remotely for reporting, demonstrating practical implementation of data warehousing concepts including ETL design, dimensional modeling, and the separation of transactional and analytical workloads across a real distributed system.