A major insurance operator's compliance reporting pipeline was running 10–12 hours daily with frequent failures. CALIGO overhauled the full architecture — shifting to parallel execution, rewriting 115 PL/SQL procedures — cutting average runtime to 1.5 hours.
A major insurance operator's compliance reporting pipeline — covering all active policies, customers, and annual transactions across all branches — was running with average daily batch durations of 10 to 12 hours, with frequent failures requiring manual intervention. The sequential execution architecture meant that independent workloads blocked one another unnecessarily, and accumulated inefficiencies across 127 source tables and 115 procedures in the PL/SQL layer compounded the problem over time. A pipeline of this duration and instability created an unacceptably narrow operational window for the compliance team and introduced daily uncertainty into a process where reliability was non-negotiable.
CALIGO conducted a systematic performance overhaul of the full pipeline, targeting execution architecture, query efficiency, and orchestration logic across both the PL/SQL and Informatica PowerCenter layers. The most impactful structural change was the shift from serial to parallel execution — analysing data dependencies across all pipeline stages and redesigning the Informatica orchestration to run independent workloads concurrently wherever the dependency graph allowed. At the PL/SQL level, all 115 procedures were optimised: queries were rewritten to ensure efficient execution plans, redundant subqueries eliminated, join strategies restructured, and unnecessary indexes removed. Table partitioning strategies and compression settings were reviewed and adjusted. Informatica workflows were reparameterised to align session configurations with actual data volumes and processing patterns.
Average daily batch runtime dropped from 10–12 hours to approximately 1.5 hours — a reduction of over 85% — while pipeline stability improved markedly, with manual failure interventions becoming the exception rather than the routine. The compliance team now operates with a predictable, narrow daily processing window, restoring confidence in the pipeline's reliability and expanding the operational window available for downstream reporting and analytics. The optimisation was achieved without changes to the pipeline's functional scope — all 127 source tables, 115 procedures, historical snapshot writes, and data quality checks continue to execute within the new architecture.