Skip to content

README.md

Latest commit

 

History

History
126 lines (108 loc) · 5.99 KB

README.md

File metadata and controls

126 lines (108 loc) · 5.99 KB
Raw

InsightForge — Enterprise Data Warehouse & Analytics Platform

InsightForge is a production-grade Business Intelligence (BI) and Data Warehousing platform. It simulates an enterprise organization across multiple domains (Customers, Orders, Products, Payments, Marketing Campaigns, Support Tickets), cleans and transforms the transactional datasets into a Star Schema warehouse, runs automated data quality validations, and serves a modern glassmorphic executive dashboard with native CSV, Excel, and PDF reporting exports.

1. Architecture Overview

                                +-------------------+
                                |    FastAPI App    |
                                | (main.py / routes)|
                                +---------+---------+
                                          |
                        +-----------------+-----------------+
                        | serves HTML/CSS/JS                | queries database
                        v                                   v
             +---------------------+              +--------------------+
             |    Executive SPA    |              |     PostgreSQL     |
             | (index.html/app.js) |              |   (Star Schema)    |
             +---------------------+              +---------+----------+
                        ^                                   ^
                        | REST requests                     | loads
                        +-----------------+-----------------+
                                          |
                                 +--------+---------+
                                 |   ETL Pipeline   |
                                 |  (pipeline.py)   |
                                 +--------+---------+
                                          |
                                 +--------+---------+
                                 |  OLTP Simulator  |
                                 |  (generator.py)  |
                                 +------------------+

Warehouse Star Schema Layout:

  • Dimensions:
    • dim_customer: Demographics and location keys.
    • dim_product: Pricing and manufacturing cost bounds.
    • dim_date: Calendar attributes (quarters, months, weekend indicators).
    • dim_location: Geographic attributes (states, cities, zip codes).
    • dim_campaign: Budgets and duration start/end dates.
  • Facts:
    • fact_orders: Range-partitioned by order_date_key into yearly structures. Includes derived metrics (revenue, cost, margins).
    • fact_payments: Amount totals and status metrics.
    • fact_support: Customer ticket priority, categories, resolution time, and satisfaction scores.
    • fact_marketing: Impressions, clicks, and conversions metrics.

2. Ingestion & Quality Engine

During the ETL process, all data is validated, cleaned, and normalized:

  • Data Cleansing: Strings are trimmed, categories normalized to Title Case.
  • Deduplication: Resolves duplicate keys in batches, preserving the latest updates.
  • Type Validation: Active pattern check for email structures and phone formatting.
  • Null Handling: Fills missing string attributes with "Unknown" or handles optional date fields.
  • Referential Integrity: Python-scoped dimension lookups identify and isolate orphan records, routing statistics to validation errors in data_quality_metrics to ensure pipeline runs successfully without database constraints exceptions.
  • SCD Type 1: Overwrites target records in dimension tables to reflect current status, updating updated_at timestamps.

3. Deployment Guide

Local Setup

  1. Requirements: Python 3.10+, PostgreSQL 15+
  2. Virtual Environment: 
    python -m venv venv
.\venv\Scripts\activate
  3. Install Dependencies: 
    pip install -r backend/requirements.txt
  4. Environment Variables: Create a .env file or export: 
    DATABASE_URL=postgresql://postgres:postgres_secure_pass_123@localhost:5432/insightforge_dw
  5. Run FastAPI Server: 
    cd backend
python -m uvicorn main:app --reload
  6. Open Dashboard: Visit http://localhost:8000/ in your browser.

Docker Compose Setup

  1. Run compose command: 
    docker compose up --build -d
  2. Verify startup: Open http://localhost:8000/ to inspect live charts.

4. API Reference Documentation

Core Metadata & System Status

  • GET /health: Verifies system and database connection status.
  • GET /schema: Dynamic listing of the physical tables catalog, primary keys, indexes, and partition children.
  • GET /warehouse/stats: Warehouse statistics, including active record volumes and disk space size.

ETL & Data Quality Control

  • POST /etl/run: Trigger manual load. Optional parameter ?date=YYYY-MM-DD runs an incremental load.
  • GET /etl/status: Stats of the most recent load (extracted, loaded, failed records count).
  • GET /etl/history: Listing of past runs.
  • GET /quality: Cumulative DQ checks logs, checking success score percentage, and orphan details logs.

Dashboards Analytics

  • GET /api/dashboard/overview: High-level metrics (Revenue, AOV, Conversion rates, satisfaction scores).
  • GET /api/dashboard/sales: Product category distributions and sales timelines.
  • GET /api/dashboard/customers: Top spenders grids, customer geography, and cohort retention.
  • GET /api/dashboard/marketing: Funnel click-conversions statistics and campaign returns (ROI).
  • GET /api/dashboard/support: CSAT stars spread, priority donuts, and resolution times.
  • GET /api/dashboard/operations: Mapping coordinates for sales and loads statistics.

Reports Querying & Export

  • GET /api/reports/query: Fetch record rows based on filters:
    • domain: orders, payments, customers, products, campaigns, support.
    • start_date / end_date: YYYY-MM-DD.
    • limit (max 1000) / offset.
  • GET /api/reports/export/{format}: File download stream of the filtered dataset:
    • format: csv, excel, pdf.
    • Same query filters.