The Challenge
As a regional nursing service, our customer is part of the total health organization which aims at satisfying the nursing needs of the patients and their local community. As part of the social security system, they rely on financial support take care of by our government.
The size of government intervention depends on 2 factors
- The budget requested at the beginning of a fiscal year;
- The accuracy of the budget request compared to the actual spend at the end of the year;
The latter factor is a challenge. Many unforeseen events happen during a fiscal year, outside of the control of our customer. Holidays, illness, scarcity in the labor market, and many other factors may lead to an inaccurate budget ask at the beginning of a cycle.
Since both over-as well as underestimating actual needs negatively impact the final payouts, our customer is looking to improve their re-forecasting process.
Our Approach
We deployed a lean and layered approach.
Setup a future-proof data platform
We helped the customer to migrate their current data warehouse stack into Snowflake and included extra indicators and flags that formerly only existed in local analyst workbooks. This was a phased approach, since an existing warehouse was deployed on Microsoft SQL Server and Microsoft SSIS. Our approach progressively lifted data products out of that warehouse and into the Snowflake data cloud.
Integrate external payroll data
One of the key drivers for this project was to integrate payroll data coming from an external payroll service provider. In our approach, we set up sharing between both parties using Snowflake’s native data sharing capabilities.
Accurately predict budget needs
Using machine learning to predict actual budget needs based on trend and seasonality of demand in caretakers’ working hours. Since under- or overestimating budget needs has a material impact on the operational budget of our client, the accuracy metric was an important project outcome.
Architectural Overview
The customer decided in favor of a Snowflake-centric architecture bolted onto the Azure infrastructure cloud. Their payroll provided did as well, as such payroll data was exchanged using Snowflake’s private data marketplace.
All data transformations are defined in dbt Cloud to be as efficient as possible using the Snowflake platform as a foundation. dbt takes care of the time-series forecasting model as well, since it is able to govern Python-based logic on top of Snowflake’s Snowpark capabilities.
Data from operational processes is made available through Fivetran’s data replication capabilities.
The Results
The results of this project are significant. The team of forecasting owners observes a material improvement in forecasting quality whereas HR leadership gained an improved level of transparency into processes.
