← All case studies

Data engineering · August 2025

Building an Enterprise Analytics Platform

Migrating an enterprise customer from legacy MongoDB to a modern real-time analytics platform — schema design, the dashboard layer, and the warehouse underneath.

8 min read

Building an Enterprise Analytics Platform

Date: August 23, 2025

From Legacy MongoDB to Modern Real-Time Dashboard

Screen_Recording_2025-03-11_at_10.34.04_AM.mov

Executive Summary

I architected and developed a comprehensive analytics platform that transforms a fragmented data ecosystem into a unified, real-time business intelligence solution. This project involved building an end-to-end ETL pipeline to migrate and synchronize data from MongoDB to PostgreSQL (Supabase), coupled with a modern React-based analytics dashboard that serves as the primary decision-making tool for monitoring agreements, claims, and dealer performance in the warranty management industry.

The Challenge

The organization was operating with siloed data spread across multiple MongoDB collections, with no unified view of critical business metrics. Key challenges included:

Technical Architecture

1. ETL Pipeline Design

I designed a sophisticated ETL system that handles the complete data lifecycle:

graph TD;
    subgraph "**TecAssured**"
        sftp["Daily Backups (SFTP)"]
    end
    
    subgraph "**MongoDB**"
        agreements_mdb["Agreements"]
        claims_mdb["Claims"]
        dealers_mdb["Dealers"]
        contracts_warehouse_all_2024-03-25_mdb["Contracts"]
    end

    subgraph "**Supabase**"
        agreements["Agreements"]
        claims["Claims"]
        dealers["Dealers"]
        contracts["Contracts"]
        processed_md5s["Processed Md5s"]
        processed_claims_timestamps["Processed Claims"]
    end

    sftp --> agreements_mdb;
    sftp --> claims_mdb;
    sftp --> dealers_mdb;
    sftp --> contracts_warehouse_all_2024-03-25_mdb;

    agreements_mdb --> agreements;
    claims_mdb --> claims;
    dealers_mdb --> dealers;
    contracts_warehouse_all_2024-03-25_mdb --> contracts;

    dealers -- linked via DealerUUID --> agreements;
    agreements -- linked via AgreementID --> claims;
    agreements -- incremental tracking --> processed_md5s;
    
    claims -- modified claims --> processed_claims_timestamps;
    
    contracts -- linked via contract_nbr --> agreements;

Incremental Processing Strategy

Data Quality & Transformation

Key Technical Decisions:

// Example of the deduplication logic
const normalizedPayeeId = payeeId.toString().trim().toLowerCase();
const dealerUUID = `${normalizedPayeeId}-${mongoId}`;

2. Database Architecture

Migrated from a document-based MongoDB structure to a relational PostgreSQL schema with:

Core Tables:

Performance Optimizations:

3. Frontend Analytics Platform

Built a comprehensive React/TypeScript dashboard featuring:

Technical Stack:

Key Features Developed:

Dashboard Overview

Advanced Filtering System

Data Tables with Intelligence

Implementation Highlights

1. Scalability Solutions

Batched Processing

const BATCH_SIZE = 500;
// Process agreements in batches to prevent memory overflow
for (let i = 0; i < agreements.length; i += BATCH_SIZE) {
    const batch = agreements.slice(i, i + BATCH_SIZE);
    await processAgreementBatch(batch);
}

Query Optimization

2. Data Integrity Measures

Foreign Key Constraints

Audit Trail

3. User Experience Enhancements

Performance Metrics

Responsive Design

Business Impact

Quantifiable Results

  1. Operational Efficiency
  1. Data Quality
  1. Business Intelligence

Strategic Advantages

Decision Making

Scalability

Technical Innovations

1. Smart Caching Strategy

Implemented a multi-tier caching system:

2. Real-Time Synchronization

Built WebSocket connections for:

3. Advanced Analytics Functions

Created 15+ PostgreSQL functions for complex calculations:

-- Example: Revenue growth calculation
CREATE FUNCTION calculate_revenue_growth(
    current_start DATE,
    current_end DATE,
    previous_start DATE,
    previous_end DATE
) RETURNS TABLE (
    current_revenue NUMERIC,
    previous_revenue NUMERIC,
    growth_rate NUMERIC
)

Lessons Learned

Technical Insights

  1. Data Migration Complexity: Transforming document-based data to relational requires careful planning of relationships and constraints
  2. Performance at Scale: Pagination and incremental processing are essential for large datasets
  3. User Adoption: Investing in UX/UI significantly impacts platform adoption rates

Architectural Decisions

  1. Choosing Supabase: Provided instant REST APIs, real-time subscriptions, and built-in authentication
  2. React Query vs Redux: React Query's caching strategy proved superior for server-state management
  3. TypeScript Adoption: Caught 200+ potential runtime errors during development

Conclusion

This project demonstrates the successful transformation of a legacy data system into a modern, scalable analytics platform. By combining robust ETL processes, intelligent database design, and intuitive frontend interfaces, I created a solution that not only solves immediate business needs but provides a foundation for future growth and innovation.

The platform now serves as the organization's single source of truth, processing millions of records daily while providing sub-second query responses. Most importantly, it has transformed how the business operates, moving from reactive to proactive decision-making through real-time insights and predictive analytics.

Technical Appendix

Technology Stack

Performance Metrics

Quick Start & Installation

This section explains how to install dependencies, configure environment variables, and run the ETL script locally or as a scheduled job. (Message me privately for detailed instructions)

  1. Clone the Repo
git clone https://github.com/rashidazarang/hwg-analytics-hub
  1. Create a .env file with credentials:
MONGODB_URI="mongodb+srv://user:pass@cluster.mongodb.net/?retryWrites=true&w=majority"
SUPABASE_URL="https://<project>.supabase.co"
SUPABASE_SERVICE_ROLE="<service-role-key>"
  1. Install Dependencies
npm install
  1. Run the ETL Script
node etl.js

A mix of what’s on my mind, what I’m learning, and what I’m going through.

Co-created with AI. 🤖

More about me

My aim is to live a balanced and meaningful life, where all areas of my life are in harmony. By living this way, I can be the best version of myself and make a positive difference in the world.

Professionally, I focus on the design and implementation of cognitive infrastructure: systems that make complex enterprise data usable through AI-powered tools and human-like interaction.

About me →

Similar blog posts

Untitled

Social

X

Youtube

Behance

Medium

Contact

Linkedin

Schedule a Call

Email

Resources

Github

Press

Sitemap

RSS Feed

← All case studies