Situation
A firm specializing in interim management, advisory services, and investments works with middle-market companies across the business and investment lifecycle. During a phase of growth and expansion, the team recognized that its data infrastructure was becoming a constraint rather than an enabler.
The firm used a range of tools to gather, store, and process operational data, generating dashboards, reports, and data dumps to track performance. That environment was purpose-built for an earlier stage of the business: rigid, difficult to scale, and dependent on a small number of individuals who understood how it worked. Best practices were absent. As the firm grew, the limitations became acute: accurate performance tracking was hard, integrating new systems was slow, and leadership lacked confidence in the numbers they were seeing.
Objective
The engagement had five defined goals:
- Diagnose and replace a rigid, unscalable data warehouse environment burdened by key-man dependencies and absent best practices.
- Design and implement a modern, scalable data infrastructure capable of supporting current operations and future growth.
- Standardize KPIs and metrics to ensure consistent understanding of business performance across all reporting layers.
- Reduce data infrastructure costs by replacing expensive SaaS ETL tooling with custom Python and Airflow pipelines.
- Deliver a BI layer and self-serve analytics capability that gives operational teams reliable, governed access to performance data.
Approach
We began with a diagnostic exercise to map the existing technology environment and define an optimal target architecture. That assessment shaped every subsequent build decision.
IngestionApache Airflow and Python orchestrate and extract data from six core systems: Intapp (client management), Kantata (project management), Zenefits (HR), Sage Intacct (finance), Lever (recruiting), and Lattice (performance). SharePoint and Excel flat files are also ingested. All data lands into Snowflake’s Raw/Bronze layer as an unmodified source of truth.
Transformationdbt handles all business logic, cleansing, and aggregation through a structured medallion flow (Raw to Clean/Silver to Aggregate/Gold), running in mirrored Dev and Prod environments. Changes are tested in Dev before they touch live data. All transformation code lives in GitHub for version control and peer review.
Storage and Data ModelingSnowflake serves as the central cloud warehouse. The Prod environment applies SCD Type-2 or Type-4 logic to track how records change over time, which is critical for accurate historical reporting on headcount, deal stages, and project costs.
Reporting and Data MartsPower BI connects downstream to Consumer Data Marts (CDM), pre-aggregated, business-domain datasets for Enterprise KPIs, Opportunities, and Time Tracking. Every dashboard pulls from the same clean, governed data layer rather than running raw queries against the warehouse directly.
Gen AI LayerA conversational AI wrapper with a UI was built on top of the data models, giving senior management natural-language access to the underlying warehouse data. No SQL, no analyst in the middle.
Replacing the SaaS ETL tooling with custom Python and Airflow pipelines delivered direct cost savings alongside the architectural improvements.
Figure 1End-to-end architecture: six source systems ingest into Snowflake’s Raw/Bronze layer via Airflow and Python, pass through dbt-managed Clean/Silver and Aggregate/Gold layers in mirrored Dev and Prod environments, and serve Power BI through pre-built Consumer Data Marts.
Figure 2Executive KPI dashboard: revenue YTD, gross margin, utilization rate, revenue per FTE, and active pipeline (each with sparklines, prior-year deltas, and forecast comparisons) alongside a key indicators table with actuals, goals, and variance status by department.
Figure 3Financial performance dashboard: revenue bridge, gross-to-net bridge, budget variance by month, and accounts receivable/payable aging, giving leadership a single view of financial health across the business.
Figure 4Pipeline and sales dashboard: active pipeline by stage (sales funnel), win rate trend, open opportunity aging, partner-level metrics, and open opportunity value by stage and partner.
Impact
The Gen AI layer gave the C-suite and senior management the ability to interact directly with the data warehouse and interrogate business performance at a granular level, without routing requests through analysts. Questions that previously required a manual data pull now return in seconds.
Standardized KPIs and metrics established a single version of the truth. Whether a team member is reviewing a Power BI dashboard or pulling an Excel export, the numbers match and the definitions are consistent. That consistency eliminated recurring disputes over figures and reduced time spent reconciling reports.
With a robust, scalable architecture and governed data management processes in place, the firm integrated new systems without disruption. The infrastructure that was once a ceiling on growth became a foundation for it.