realtime-ecom-pipeline

byVivek

# Real-Time E-Commerce Data Pipeline — Detailed Project Specification The project will be designed as a **complete end-to-end Data Engineering system** for an e-commerce platform. The core idea is: > **E-commerce transaction data enters the system → gets extracted → validated → transformed → stored in PostgreSQL → analyzed with SQL → visualized in a dashboard → pipeline execution is orchestrated with Airflow.** We will keep the architecture realistic enough to discuss in an interview, while still being manageable as a portfolio project. --- # 1. Project Overview ### Project Name **Real-Time E-Commerce Data Pipeline & Analytics Platform** ### Objective Build a pipeline capable of processing e-commerce data such as: * Customers * Products * Categories * Orders * Order items * Payments * Inventory * Customer activity The system will transform raw data into structured relational data and provide business analytics through a dashboard. --- # 2. Overall Architecture The complete system will look like this: ```text ┌─────────────────────┐ │ DATA SOURCES │ │ │ │ REST API │ │ JSON │ │ CSV / Mock Events │ └──────────┬──────────┘ │ ▼ ┌─────────────────────┐ │ EXTRACT │ │ │ │ Python │ │ API Requests │ │ JSON Parsing │ └──────────┬──────────┘ │ ▼ ┌─────────────────────┐ │ RAW DATA │ │ │ │ JSON / CSV │ │ Raw Transactions │ └──────────┬──────────┘ │ ▼ ┌─────────────────────┐ │ VALIDATION │ │ │ │ Missing values │ │ Duplicates │ │ Data types │ │ Invalid records │ └──────────┬──────────┘ │ ▼ ┌─────────────────────┐ │ TRANSFORM │ │ │ │ Python + Pandas │ │ Cleaning │ │ Normalization │ │ Calculations │ └──────────┬──────────┘ │ ▼ ┌──────────────────────────────┐ │ POSTGRESQL │ │ │ │ Customers │ │ Products │ │ Categories │ │ Orders │ │ Order Items │ │ Payments │ │ Inventory │ │ Pipeline Logs │ └──────────────┬───────────────┘ │ ┌─────────────┴──────────────┐ │ │ ▼ ▼ ┌────────────────┐ ┌────────────────┐ │ SQL ANALYTICS │ │ AIRFLOW │ │ │ │ │ │ Revenue │ │ Scheduling │ │ Orders │ │ Dependencies │ │ Products │ │ Monitoring │ │ Customers │ │ Retries │ └───────┬─────────┘ └────────────────┘ │ ▼ ┌────────────────┐ │ DASHBOARD │ │ │ │ KPIs │ │ Charts │ │ Products │ │ Categories │ │ Customers │ │ Orders │ └────────────────┘ ``` --- # 3. Data We Will Work With We need realistic e-commerce data. We'll have **7 major data domains**. --- ## A. Customers Every customer has: | Field | Example | | ----------- | --------------------------------------------- | | customer_id | CUST00001 | | first_name | Rahul | | last_name | Sharma | | email | [rahul@example.com](mailto:rahul@example.com) | | gender | Male | | age | 27 | | city | Ahmedabad | | state | Gujarat | | country | India | | signup_date | 2025-04-12 | Example: ```text CUST00001 Rahul Sharma Ahmedabad Gujarat India ``` --- # 4. Product Catalog This is where your **T-shirt / shirt example** comes in. We should have realistic categories and products. ### Categories ```text Electronics Clothing Footwear Home & Kitchen Beauty Sports Books Accessories ``` ### Clothing ```text Men's Clothing ├── T-Shirts ├── Shirts ├── Jeans ├── Trousers ├── Jackets └── Hoodies Women's Clothing ├── T-Shirts ├── Tops ├── Jeans ├── Dresses ├── Jackets └── Hoodies ``` ### Example products ```text P1001 | Classic Cotton T-Shirt | T-Shirts | ₹699 P1002 | Premium Polo T-Shirt | T-Shirts | ₹999 P1003 | Slim Fit Casual Shirt | Shirts | ₹1,299 P1004 | Formal Oxford Shirt | Shirts | ₹1,599 P1005 | Regular Fit Jeans | Jeans | ₹1,899 ``` But we shouldn't limit it to clothing. ### Electronics ```text P2001 | Wireless Mouse P2002 | Mechanical Keyboard P2003 | USB-C Hub P2004 | Bluetooth Headphones P2005 | Wireless Earbuds ``` ### Footwear ```text P3001 | Running Shoes P3002 | Casual Sneakers P3003 | Formal Shoes P3004 | Sports Shoes ``` ### Home & Kitchen ```text P4001 | Electric Kettle P4002 | Coffee Maker P4003 | Non-Stick Pan P4004 | Water Bottle ``` This gives the dashboard enough variety to make the analytics meaningful. --- # 5. Product Table The database might contain: ```text products ``` with: ```text product_id product_name category_id brand description price cost_price stock_quantity rating created_at updated_at ``` Example: ```text P1001 Classic Cotton T-Shirt Clothing ₹699 Cost: ₹420 Stock: 156 Rating: 4.4 ``` The distinction between **selling price and cost price** lets us calculate profit. --- # 6. Categories Separate category table: ```text categories ``` Example: ```text category_id category_name parent_category ``` Data: ```text CAT01 | Electronics CAT02 | Clothing CAT03 | Footwear CAT04 | Home & Kitchen CAT05 | Beauty CAT06 | Sports CAT07 | Books CAT08 | Accessories ``` --- # 7. Orders This is the core transactional data. ```text orders ``` Fields: ```text order_id customer_id order_date order_status payment_status payment_method shipping_city shipping_state total_amount discount_amount tax_amount created_at ``` Example: ```text ORD10001 CUST00001 2026-09-01 DELIVERED PAID UPI Ahmedabad Gujarat ₹2,598 ₹200 ₹124 ``` --- # 8. Order Items One order can contain multiple products. Example: ```text ORD10001 1 × Wireless Mouse 1 × Mechanical Keyboard ``` Therefore we need: ```text order_items ``` Fields: ```text order_item_id order_id product_id quantity unit_price discount subtotal ``` Example: ```text OI001 ORD10001 P2001 2 ₹699 ₹50 ₹1,348 ``` This allows us to determine exactly **which products are selling**. --- # 9. Payments We'll maintain: ```text payments ``` Fields: ```text payment_id order_id payment_method payment_status amount transaction_date transaction_reference ``` Payment methods: ```text UPI Credit Card Debit Card Net Banking Cash on Delivery Wallet ``` Payment statuses: ```text SUCCESS FAILED PENDING REFUNDED ``` --- # 10. Inventory We'll also track stock. ```text inventory ``` Fields: ```text inventory_id product_id warehouse stock_quantity reserved_quantity reorder_level last_updated ``` Example: ```text Wireless Mouse Ahmedabad Warehouse Stock: 156 Reserved: 24 Reorder Level: 30 ``` This allows the dashboard to identify: > **Low-stock products** --- # 11. The Database The final PostgreSQL database will approximately look like: ```text ecommerce_db │ ├── categories │ ├── customers │ ├── products │ ├── orders │ ├── order_items │ ├── payments │ ├── inventory │ └── pipeline_logs ``` Relationships: ```text categories │ │ ▼ products │ │ │ │ ▼ ▼ inventory order_items │ ▼ orders │ ├──────────► customers │ └──────────► payments ``` --- # 12. Raw Data Before data enters PostgreSQL, we'll have raw data. Directory: ```text data/ │ ├── raw/ │ ├── customers.json │ ├── products.json │ ├── orders.json │ ├── order_items.json │ ├── payments.json │ └── inventory.json │ └── processed/ ├── customers.csv ├── products.csv ├── orders.csv └── order_items.csv ``` The raw data represents what we received from external systems. Processed data represents what our pipeline cleaned. --- # 13. Complete Project Directory I'd structure the repository like this: ```text real-time-ecommerce-pipeline/ │ ├── README.md ├── requirements.txt ├── .env.example ├── .gitignore ├── Dockerfile ├── docker-compose.yml │ ├── config/ │ └── config.yaml │ ├── data/ │ ├── raw/ │ │ ├── customers.json │ │ ├── products.json │ │ ├── orders.json │ │ ├── order_items.json │ │ ├── payments.json │ │ └── inventory.json │ │ │ └── processed/ │ ├── customers.csv │ ├── products.csv │ ├── orders.csv │ └── order_items.csv │ ├── database/ │ ├── schema.sql │ ├── seed_data.sql │ ├── indexes.sql │ └── analytics.sql │ ├── src/ │ ├── __init__.py │ │ │ ├── extraction/ │ │ ├── __init__.py │ │ └── api_client.py │ │ │ ├── transformation/ │ │ ├── __init__.py │ │ ├── clean_data.py │ │ └── transform_data.py │ │ │ ├── validation/ │ │ ├── __init__.py │ │ └── validate_data.py │ │ │ ├── loading/ │ │ ├── __init__.py │ │ └── load_postgres.py │ │ │ └── pipeline.py │ ├── airflow/ │ ├── dags/ │ │ └── ecommerce_pipeline.py │ │ │ └── logs/ │ ├── dashboard/ │ ├── app.py │ ├── queries.py │ └── components/ │ ├── kpis.py │ ├── charts.py │ └── tables.py │ ├── tests/ │ ├── test_extraction.py │ ├── test_transformation.py │ ├── test_validation.py │ └── test_database.py │ ├── logs/ │ └── pipeline.log │ └── docs/ ├── architecture.png ├── database-schema.png └── data-flow.png ``` The **`database/` directory is particularly important** because it gives you portable SQL files that can recreate the database. For example: ```text database/ ├── schema.sql ├── seed_data.sql ├── indexes.sql └── analytics.sql ``` Someone can essentially create the database and import these scripts. --- # 14. ETL Workflow Now the actual workflow. ## Step 1 — Extract Data comes from: ```text REST API ``` or our simulated e-commerce event source. Python retrieves: ```text Customers Products Orders Payments Inventory ``` --- ## Step 2 — Store raw data The response is saved as JSON. ```text API ↓ JSON ↓ data/raw/ ``` We don't immediately destroy the original data. --- # 15. Step 3 — Validate The validation layer checks: ### Missing IDs ```text customer_id = NULL ``` → invalid ### Invalid prices ```text price = -500 ``` → invalid ### Duplicate orders ```text ORD10001 ORD10001 ``` → duplicate ### Invalid status ```text order_status = "BANANA" ``` → invalid ### Invalid relationships ```text order.customer_id = C999999 ``` when C999999 doesn't exist. → invalid --- # 16. Step 4 — Transform The transformation layer performs: * Data type conversion * Standardization * Deduplication * Missing-value handling * Calculating totals * Formatting timestamps * Normalizing categories * Creating derived fields For example: ```text Raw: " ₹1,299 " ``` becomes: ```text 1299.00 ``` And: ```text "completed" "Completed" "COMPLETED" ``` becomes: ```text COMPLETED ``` --- # 17. Step 5 — Load Clean data goes into PostgreSQL: ```text customers products categories orders order_items payments inventory ``` --- # 18. Step 6 — Analytics SQL queries generate business metrics. For example: ### Revenue ```text Total Revenue ``` ### Orders ```text Total Orders ``` ### Average Order Value ```text Revenue / Number of Orders ``` ### Profit ```text Selling Price - Cost Price ``` ### Conversion If we include customer activity: ```text Orders / Visitors ``` --- # 19. Dashboard This is where the project becomes visually impressive. I'd divide it into **5 pages/tabs**. --- # Dashboard Page 1 — Executive Overview This is the main screen. ```text ╔══════════════════════════════════════════════════════════╗ ║ E-COMMERCE ANALYTICS ║ ╠══════════════════════════════════════════════════════════╣ ║ ║ ║ REVENUE ORDERS CUSTOMERS PROFIT ║ ║ ₹24.7M 18,492 8,721 ₹5.8M ║ ║ +12.4% +8.7% +5.2% +14.1% ║ ║ ║ ╠══════════════════════════════════════════════════════════╣ ║ ║ ║ REVENUE TREND ║ ║ ║ ║ ₹ ╱ ║ ║ │ ╱────────╯ ║ ║ │ ╱───────────╯ ║ ║ │ ╱────────╯ ║ ║ └────────────────────────────────────────────── ║ ║ Sep 1 Sep 5 Sep 10 Sep 15 Sep 20 ║ ║ ║ ╠══════════════════════════╦═══════════════════════════════╣ ║ TOP CATEGORIES ║ ORDER STATUS ║ ║ ║ ║ ║ Electronics ████████ ║ Delivered 68% ║ ║ Clothing ██████ ║ Processing 14% ║ ║ Footwear █████ ║ Shipped 11% ║ ║ Home ████ ║ Cancelled 7% ║ ╚══════════════════════════╩═══════════════════════════════╝ ``` ### KPI cards At the top: **Total Revenue** **Total Orders** **Total Customers** **Total Profit** **Average Order Value** **Products Sold** --- # 20. Dashboard Filters Users should be able to filter everything. ### Date ```text Today Last 7 Days Last 30 Days Last 90 Days Custom Range ``` ### Category ```text All Electronics Clothing Footwear Home & Kitchen Beauty Sports Books Accessories ``` ### Region ```text All India Gujarat Maharashtra Delhi Karnataka Tamil Nadu ... ``` ### Order Status ```text All Pending Processing Shipped Delivered Cancelled Returned ``` This makes the dashboard interactive rather than just a static collection of charts. --- # 21. Dashboard Page 2 — Sales Analytics This page focuses entirely on sales. ### Metrics ```text Revenue Orders Units Sold Average Order Value Profit Profit Margin ``` ### Charts **Revenue over time** Line chart. **Orders over time** Line/bar chart. **Revenue by category** Bar chart. Example: ```text Electronics ███████████████ ₹8.2M Clothing ███████████ ₹6.4M Footwear ████████ ₹4.8M Home & Kitchen █████ ₹3.1M Sports ███ ₹1.4M ``` --- # 22. Dashboard Page 3 — Product Analytics This is where your T-shirts, shirts, etc. appear. ### Top Products | Rank | Product | Category | Units | Revenue | | ---: | ---------------------- | ----------- | ----: | ------: | | 1 | Wireless Earbuds | Electronics | 1,284 | ₹1.54M | | 2 | Classic Cotton T-Shirt | Clothing | 1,120 | ₹782K | | 3 | Running Shoes | Footwear | 824 | ₹1.56M | | 4 | Mechanical Keyboard | Electronics | 693 | ₹1.38M | | 5 | Premium Polo T-Shirt | Clothing | 641 | ₹640K | ### Product filters ```text Category Brand Price Range Rating Stock Status ``` ### Product performance chart ```text Revenue │ │ █ │ █ █ │ █ █ █ │ █ █ █ █ └──────────────────── T-shirt Shoes Keyboard Earbuds ``` --- # 23. Low Stock Section The Product page should also show: ### Low Stock Products | Product | Current Stock | Reorder Level | Status | | ---------------------- | ------------: | ------------: | ------ | | Classic Cotton T-Shirt | 18 | 30 | Low | | Wireless Mouse | 22 | 40 | Low | | Running Shoes | 14 | 25 | Low | This creates a real business use case. --- # 24. Dashboard Page 4 — Customer Analytics This page focuses on customers. ### KPIs ```text Total Customers New Customers Returning Customers Average Customer Spend ``` ### Customer segmentation ```text New Customers Returning Customers High-Value Customers Inactive Customers ``` ### Top Customers | Customer | Orders | Spending | | ------------ | -----: | -------: | | Rahul Sharma | 31 | ₹84,200 | | Priya Patel | 27 | ₹72,450 | | Amit Shah | 24 | ₹69,820 | --- # 25. Customer Location We can visualize: ```text Orders by State ``` Example: ```text Maharashtra ███████████ Gujarat █████████ Karnataka ████████ Delhi ███████ Tamil Nadu ██████ ``` If we want a geographic map later, we can add one. --- # 26. Dashboard Page 5 — Orders & Operations This page focuses on operational performance. ### Order status ```text Pending Processing Shipped Delivered Cancelled Returned ``` ### Delivery information ```text Total Orders Delivered Orders Cancelled Orders Return Rate ``` ### Payment status ```text Successful Failed Pending Refunded ``` ### Payment method ```text UPI 42% Credit Card 25% Debit Card 18% COD 10% Wallet 5% ``` --- # 27. Pipeline Monitoring Page This is optional but **very good for a Data Engineering portfolio**. Instead of only showing business data, show the health of the data pipeline. ```text ╔══════════════════════════════════════════════╗ ║ PIPELINE MONITORING ║ ╠══════════════════════════════════════════════╣ ║ ║ ║ Last Run: 2026-09-02 22:00:05 ║ ║ Status: SUCCESS ║ ║ Duration: 42 seconds ║ ║ Records Read: 125,430 ║ ║ Records Loaded: 124,982 ║ ║ Invalid: 448 ║ ║ ║ ╠══════════════════════════════════════════════╣ ║ TASK STATUS DURATION ║ ║ ║ ║ Extract API SUCCESS 8 sec ║ ║ Validate Data SUCCESS 4 sec ║ ║ Transform Data SUCCESS 12 sec ║ ║ Load PostgreSQL SUCCESS 15 sec ║ ║ Run Analytics SUCCESS 3 sec ║ ╚══════════════════════════════════════════════╝ ``` This makes it obvious that this isn't merely a dashboard project. It's a **data pipeline with analytics on top**. --- # 28. Airflow Workflow Airflow controls the pipeline. The DAG: ```text START │ ▼ Extract Customers │ ▼ Extract Products │ ▼ Extract Orders │ ▼ Extract Payments │ ▼ Validate Data │ ▼ Transform Data │ ▼ Load Customers │ ▼ Load Products │ ▼ Load Orders │ ▼ Load Order Items │ ▼ Load Payments │ ▼ Update Inventory │ ▼ Run Analytics │ ▼ END ``` Some extraction tasks can run in parallel: ```text START │ ┌────────────┼────────────┐ ▼ ▼ ▼ Customers Products Orders │ │ │ └────────────┼────────────┘ ▼ Validate │ Transform │ Load │ Analytics │ END ``` --- # 29. Pipeline Frequency Because we're calling this **Real-Time E-Commerce Data Pipeline**, I'd design it to support two modes. ### Batch mode Pipeline runs every: ```text 15 minutes ``` or: ```text 1 hour ``` depending on configuration. ### Event mode New order events can be simulated: ```text ORDER_CREATED ORDER_PAID ORDER_SHIPPED ORDER_DELIVERED ORDER_CANCELLED ``` Example: ```json { "event_type": "ORDER_CREATED", "order_id": "ORD10231", "customer_id": "CUST0231", "timestamp": "2026-09-02T22:10:12" } ``` The system processes these events. We don't need Kafka for the first version; the architecture can be designed so Kafka could be introduced later. --- # 30. Data Quality This is an important Data Engineering component. We'll track: ```text Total Records Valid Records Invalid Records Duplicate Records Missing Values Failed Records ``` Example: ```text Records Received 125,430 Valid Records 124,982 Invalid Records 448 Duplicates 102 Missing Values 346 ``` This information can be stored in: ```text pipeline_logs ``` --- # 31. Incremental Loading This is something I'd definitely include because it makes the project more realistic. Instead of processing **every historical order every time**, the pipeline can process only new/updated records. Example: ```text Last pipeline run: 22:00 Current run: 22:15 ``` Only records created/updated between those timestamps are processed. Conceptually: ```text Database ↑ WHERE updated_at > last_successful_run ``` That is a very good interview topic. --- # 32. Error Handling The pipeline should not completely die because one record is bad. Example: ```text 100,000 records ↓ 99,700 valid 300 invalid ``` The valid records continue. Invalid records can be placed into: ```text data/errors/ ``` or an error table: ```text pipeline_errors ``` with: ```text error_id pipeline_run_id record_id error_type error_message timestamp ``` --- # 33. Docker Architecture Docker can contain: ```text docker-compose │ ├── PostgreSQL │ ├── Airflow │ ├── Dashboard │ └── Pipeline ``` This means the project becomes reproducible. --- # 34. Technologies ### Required ```text Python SQL PostgreSQL REST APIs JSON ETL/ELT Git Data Structures & Algorithms ``` ### Good-to-have ```text Pandas Airflow Docker Linux FastAPI AWS ``` I'd make the actual project stack: > **Python + PostgreSQL + SQL + Pandas + REST API + Airflow + Docker + Git + Linux** And potentially add: > **AWS S3** as the cloud component. --- # 35. What makes this a Data Engineering project? This distinction is important. The dashboard isn't the project. The **pipeline** is the project. The dashboard is simply the consumer of the processed data. ```text DATA │ ▼ Extraction │ ▼ Transformation │ ▼ Validation │ ▼ Loading │ ▼ PostgreSQL │ ┌──────┴───────┐ ▼ ▼ Analytics Dashboard ``` That's the central story. --- # 36. Final User Experience When someone opens the dashboard, they should be able to answer: ### "How is our business doing?" → Revenue → Orders → Profit → Customers ### "What are people buying?" → Products → Categories → Top sellers ### "Who are our customers?" → New customers → Returning customers → High-value customers → Geographic distribution ### "What's happening operationally?" → Pending orders → Shipping → Delivery → Returns → Payments ### "Is our inventory healthy?" → Stock levels → Low-stock products → Reorder requirements ### "Is our data pipeline healthy?" → Last pipeline run → Records processed → Failed records → Pipeline duration → Task status That's a **proper portfolio project**, not just "I connected a CSV to a dashboard." --- # Final project scope If we lock this design, the final system is essentially: ```text ┌──────────────────┐ │ REST API / JSON │ └────────┬─────────┘ │ ▼ ┌──────────────────┐ │ Python Extractor │ └────────┬─────────┘ │ ▼ ┌──────────────────┐ │ Raw Data │ └────────┬─────────┘ │ ▼ ┌──────────────────┐ │ Validation │ │ + Pandas │ └────────┬─────────┘ │ ▼ ┌──────────────────┐ │ Transformation │ └────────┬─────────┘ │ ▼ ┌──────────────────────────────┐ │ PostgreSQL │ │ │ │ Customers │ │ Products │ │ Categories │ │ Orders │ │ Order Items │ │ Payments │ │ Inventory │ │ Pipeline Logs │ └──────────────┬───────────────┘ │ ┌────────┴────────┐ │ │ ▼ ▼ SQL Analytics Airflow │ │ └────────┬────────┘ ▼ ┌───────────────┐ │ Dashboard │ │ │ │ Overview │ │ Sales │ │ Products │ │ Customers │ │ Operations │ │ Pipeline │ └───────────────┘ ``` (Give me information how to setup and run this project step by step in Readme.md and then normal readme.md profile that we use for Github upload )