ISP 500K User Case — Consolidating a Fragmented Data Stack

Product

The client was an internet service provider in Gurgaon, India, with close to 500,000 paying users. Reporting spanned six business domains - users, vendors, churn and retention, revenue, inventory and plans, and support tickets - across 50M+ rows and 40+ tables.

Problem

The data lived across three separate databases - MySQL, Postgres, and MongoDB - built for transactional work, not reporting. Every report meant 2-3 analysts running a subset of 50-70 queries by hand, pulling results into Excel, and merging them manually on shared columns. Two things compounded the pain: queries could run for hours against databases never designed for large analytical scans, and user IDs differed across databases, requiring an encryption/decryption translation step before any two datasets could even be joined. Excel regularly crashed under the load. Reports were late, and leadership was deciding on numbers that were already stale.

My role

Owned the data consolidation and analytics architecture end to end - partnered with the client's engineering team on the migration pipelines, and independently designed and built all five production SQL models.

Scope

Data consolidation across three databases and 40+ tables, migration into ClickHouse, and production SQL model design for five business verticals. Roughly five months, working directly with the client's engineering team.

Approach

Diagnosed the root cause first: the source databases were built for fast single-record reads and writes, not the wide, cross-domain scans reporting actually needed - so queries were slow by design, and no single database could join across the other two. Migrated the relevant data into ClickHouse, a columnar store built for exactly this workload - reading only the columns a report needs instead of full rows, compressing repetitive fields, and executing across many cores in parallel.

Migration alone was not enough. Raw consolidated tables still meant every analyst re-deriving the same joins, including the ID translation, by hand each time. So the second half of the work was modeling: five production SQL models, each roughly 1,500 lines, one per vertical (revenue, support tickets, churn, inventory, users and onboarding). Each model solved the cross-database joins and ID translation once, producing a single query-ready table per vertical instead of a fresh manual merge every time.

Outcome

• Reporting went from hours of manual query-running and Excel merging to near-instant queries against a single model
• 12+ analyst hours saved daily, redirected from running-and-merging into actual analysis
• One source of truth per vertical - reports stopped quietly disagreeing with each other, since the join logic was defined once and reviewed, not rebuilt per analyst
• Faster leadership decisions, working off current data instead of a lagged, hand-assembled snapshot

The stack, before and after