TL;DR: Jag Method’s SQL Server data warehouse took 2.5 hours to rebuild and was failing daily, leaving the business flying blind on lead conversion performance for significant portions of each day. NxGN’s data engineering team redesigned the warehouse architecture rather than scaling hardware, reducing data warehouse build time to 30 minutes with zero failures. The optimisation restored near-real-time reporting, freed engineering resources from daily firefighting, and created headroom for the company to expand the warehouse scope into new business areas.
“Since joining the team, they have added immense value to our business. We see them as part of our team and not just another outsourced technology provider.”
Brendan Fry, Chief Technical Officer, JAG Web Marketing
Fast Facts
| Client | Jag Method (JAG Web Marketing) |
| Industry | Insurance / Lead Generation |
| Region | South Africa |
| Company size | Leading insurance lead generation company |
| NxGN service | Data warehouse architecture redesign and optimisation |
| Deployment timeline | Analysis, redesign, parallel validation, and cutover |
| Key result #1 | Data warehouse build time reduced from 2.5 hours to 30 minutes (80% faster) |
| Key result #2 | Daily build failures eliminated: zero failures post-optimisation |
| Key result #3 | Near-real-time lead conversion insights restored |
What Problem Was Jag Method Facing?
Jag Method operates in the insurance lead generation space, sourcing leads at the lowest possible cost and distributing them to insurance clients. Their competitive advantage depends on identifying which leads are most likely to convert and getting that intelligence to their clients before competitors can. Speed and accuracy in reporting aren’t nice-to-haves. They directly drive revenue.
The problem was their Microsoft SQL Server data warehouse. The full rebuild took 2.5 hours to complete. During that window, operational reporting was unavailable. The business was effectively flying blind for a significant portion of each day. Worse, the builds had become unreliable: what started as occasional failures had escalated to daily breakdowns, each requiring manual intervention to diagnose and restart.
The Trigger
The cascading effect was hitting the business directly. Teams couldn’t access up-to-date conversion data. Lead distribution decisions were being made on stale information. The core of Jag Method’s value proposition (optimising which leads to which insurance client) was being undermined by infrastructure that couldn’t keep up. Engineering resources that should have been used to build new capabilities were consumed by daily triage: diagnosing failures, restarting builds, and validating outputs.
The team had considered simply adding more hardware: more CPU, more memory, faster disks. But the underlying architecture hadn’t been designed to scale, and throwing resources at a structural problem would only delay the inevitable.
Why This Was Hard to Solve
The warehouse had accumulated technical debt over time. Query patterns that were efficient at a small scale had become bottlenecks as data volumes grew. Build dependencies were sequential, where they could have been parallel. Data transformations were being repeated unnecessarily across multiple stages. The problem wasn’t a single slow query. It was architectural, spread across the entire ETL pipeline, and built a dependency chain. Fixing it required a comprehensive redesign, not incremental tuning.
Why NxGN
NxGN’s data engineering team had the depth to analyse the full warehouse architecture (ETL pipelines, indexing strategies, build dependencies, transformation logic) and redesign it holistically rather than applying patchwork optimisations. NxGN was already embedded with Jag Method through the Automated Lead Distribution engagement, which meant the team understood the business context: that every hour of delayed reporting was an hour of lead distribution decisions made on stale data.
How NxGN Reduced Data Warehouse Build Time by 80%
NxGN performed a comprehensive architectural redesign of the data warehouse, addressing the structural issues rather than masking them with hardware.
Architecture Analysis and Root Cause Identification
The team performed a deep analysis of the warehouse structure and build process to identify where time and reliability were being lost. The analysis confirmed the issues were architectural, not hardware-related: sequential dependencies that could be parallelised, redundant transformations repeated across stages, and indexing strategies that hadn’t evolved with the data volumes.
ETL Pipeline Restructuring
NxGN restructured the Extract, Transform, Load pipeline to maximise parallel processing, eliminated redundant data transformations, optimised indexing strategies, and redesigned the build dependency chain to remove unnecessary sequential operations. The optimisation was comprehensive, touching every stage of the warehouse build process rather than targeting individual bottlenecks.
Parallel Validation
To ensure none of the changes introduced reporting inaccuracies, NxGN ran the optimised warehouse in parallel with the existing system during the transition period. Every output was validated against the original to confirm that the restructured architecture produced identical results. This parallel testing approach meant Jag Method could have confidence in the new system from day one, with zero risk of reporting discrepancies.
Technical detail: Optimisation approach
The warehouse redesign addressed four categories of architectural debt: build dependency ordering (converting sequential operations to parallel where no true data dependency existed), transformation redundancy (identifying and eliminating repeated calculations across ETL stages), indexing strategy (redesigning indexes to match actual query patterns at current data volumes rather than the original small-scale patterns), and pipeline efficiency (restructuring the ETL flow to minimise I/O operations and maximise in-memory processing where possible). The parallel validation phase ran both architectures simultaneously, comparing outputs at every reporting endpoint to confirm identical results before cutover. The redesigned architecture was also evaluated for growth headroom, ensuring that adding new data sources and transformations wouldn’t return the warehouse to its pre-optimisation performance characteristics.
What Were the Results?
| Metric | Before | After |
|---|---|---|
| Full data warehouse build time | 2.5 hours | 30 minutes. 80% reduction (5× faster) |
| Build reliability | Failing daily | Zero failures post-optimisation. 100% elimination |
| Reporting availability | Delayed, unreliable; business flying blind | Near-real-time; lead conversion data available when needed |
| Engineering time on firefighting | Significant daily triage (diagnose, restart, validate) | Minimal. Team freed for development work |
| Server resource consumption | High during 2.5-hour builds | Reduced; more efficient processing during shorter builds |
| Growth headroom | None; adding data sources would worsen performance | Created; warehouse scope expanded post-optimisation |
The 5× speed improvement transformed Jag Method’s operational capabilities. With the warehouse rebuilding in 30 minutes instead of 2.5 hours, reporting was available far earlier in the day. Business teams could see lead conversion data in near-real-time, enabling them to optimise distribution decisions while the data was still actionable.
The elimination of daily build failures had a compounding benefit. Engineering resources that had been consumed by triage were freed for development work, building new capabilities rather than keeping existing infrastructure alive.
The optimised architecture also created headroom for growth. The faster, more efficient design meant additional data sources and transformations could be added without returning to the original performance problems. Jag Method has since expanded the warehouse scope to optimise other areas of their business, a move that would have been impossible under the old architecture.
Frequently Asked Questions
Why did NxGN redesign the architecture rather than scaling hardware?
The analysis confirmed the issues were architectural, not resource-related. Sequential build dependencies, redundant transformations, and outdated indexing strategies were the root causes: problems that more CPU and memory would only partially mask while the underlying debt continued to compound. Architectural redesign solved the problem permanently and created growth headroom, where hardware scaling would have been a temporary fix with recurring costs.
How did NxGN ensure the redesigned warehouse produced identical results?
The optimised warehouse ran in parallel with the existing system during the transition period. Every reporting output was validated against the original to confirm identical results before cutover. This parallel testing approach meant zero risk of reporting discrepancies. The business could trust the new system from day one.
Can this approach work for data warehouses on other platforms?
The optimisation methodology (analysing build dependencies, eliminating transformation redundancy, restructuring ETL pipelines for parallelism, and redesigning indexing strategies) applies to any SQL-based data warehouse regardless of platform. The specific techniques adapt to the platform’s capabilities, but the analytical approach and architectural principles are consistent across SQL Server, PostgreSQL, Oracle, and cloud-native warehouses.
Data warehouse too slow or unreliable? Talk to our data engineers about what’s possible →