Faster dashboards, better AUM insights, zero chaos with smarter data engineering

This privately held investment management firm specializes in a comprehensive suite of investment products, risk management solutions, and advisory services. Managing funds for investors and institutions worldwide, the company employs associates globally.

The firm faced inconsistent data definitions across business units and lacked a unified semantic layer needed for aligned executive reporting. Their existing system, driven by Azure Autosys and SQL Server jobs, often delivered data as late as early afternoon, slowing performance and preventing executives from accessing the morning insights required for timely decision-making.

The executive dashboard, used to monitor assets under management (Assets Under Management) across mutual funds, fixed income, and equity vehicles, also struggled to distinguish organic asset value changes from redemption driven movements due to limited historical depth and slow refresh cycles.

At the same time, the firm needed to migrate critical datasets from Azure to AWS while improving dashboard performance, standardizing definitions, and ensuring consistent analytics across portfolio categories and time horizons.

To stabilize and modernize the ecosystem, the team structured the work around data replication from Azure to AWS, coordinating workflows across platforms and delivering in two-week sprints. Early in the effort, timeline risks were identified, and an additional sprint was added to ensure accuracy and completeness.

A key pillar of the approach was pre-compiling more than 50 million rows of historical data and generating a generic, pre-aggregated snapshot model. This enabled Power BI to support fast year-to-date and 30-day look-back reporting without reprocessing large volumes of raw data.

Portfolio category changes were handled through SCD (slowly changing dimensions), ensuring executives could analyze performance consistently even as product structures evolved. This set the foundation for a unified semantic layer and faster, more reliable reporting.

As the team migrated data from Azure to AWS, they simultaneously uncovered and resolved significant discrepancies in the existing system. What began as a two-table migration expanded into replication of eight complex data sources, each requiring targeted fixes—from table repairs and configuration corrections to feature enhancements that now support smoother future migrations.

A long-standing Spark overflow issue, caused by large decimal precision common in financial datasets, was eliminated by implementing a more reliable decimal number system. Additional performance bottlenecks, such as memory constraints and excessive small file handling, were addressed by adopting dbt models and Delta tables, reducing end-to-end processing time from three hours to just 57 minutes.

The migration pipeline spanned Autosys scheduling, Kubernetes Spark on Azure, and EMR within AWS. Along the way, the team resolved tagging issues that prevented dbt target tables from being exposed properly in the Databricks Unity Catalog, an area with limited client documentation.

Event-driven DAGs were another source of recurring failures due to missing dependency awareness. The team built a new dependency-checking tool that identifies upstream delays and intelligently retries, replacing the flawed existing tool and transferring full ownership of the new approach to the client’s engineers. This shared library is now being used by other teams in the org to address their needs.

With these improvements, analysts can now perform deeper cash-flow and AUM, analysis at the portfolio level, including both as-is and as-of reporting, even for terminated portfolios. Ongoing work is extending these capabilities to support high-volume cash-flow calculations, which are inherently more complex than AUM.