A few months ago, we were tasked with migrating the data workflow of a property management company, which was heavily based on Excel Sheets, into a modern Data Warehouse. In this blog, I will explain what were the requirements in order to consider this project a success, how we planned and executed the migration, and the final product that we delivered.
Their old stack
As mentioned, the client is a property management company with dozens of properties under their watch. Clients delegate all the management of the properties to them, from finding tenants to making sure they are maintained when needed. Their old stack heavily relied on Excel sheets that had to be manually handled by the team. The data sources would come mainly from their financial application, Exact Online, and their property management system, Wunderbricks. The Excel sheets had dozens of calculations that allowed them to obtain insights on the current state of the company and allowed them to make decisions in the near future.
In the end, the product should be able to be used internally for their daily operations as well as shown to their clients with the proper data filtering/masking so that clients access only information relevant to them.
Requirements
Of course, such a process implies lots of data that has to be moved around and analyzed. Some of the major points to keep track of are financial information (rents, invoices, maintenance…), building occupancy and forecasting of the following months, among other things. After having meetings with the stakeholders, we agreed on a few final dashboards that would help them on tracking and acting on data on a daily basis, replacing their decentralized set of tools. The agreed upon requirements were the following:
- Automate ingestion for financial and property management data.
- Orchestrate ingestion and data transformation to be able to analyze fresh dashboards every morning.
- Migrate the semi-manual forecasting process in excel to the warehouse so that it is seamlessly integrated in the data pipeline.
- Create dashboards specific for property management (Rent Roll, Occupancy…) and financial data (Profit & Loss, Balance sheet) that would allow them to drill down to the detailed data if needed, as well as a summary page with the most important information for each of the CVs and clients that they manage.
- Create some sort of GUI application that allows them to centrally manage which data is shown according to certain periods of time, as well as adding comments to inform their clients about the buildings they own.
Architecture overview
We decided to use Snowflake as the central data warehouse, since its pay-per-use policy and completely managed architecture is ideal for small to medium companies looking to modernize their workflows. On top of that, we would use dbt to perform the data transformation from raw to final. Dbt is a personal favorite of mine, since it allows me to develop using dbt-core, while the clients will be able to manage their scheduling via dbt Cloud in an easy to use interface. For the dashboards, Tableau was chosen as this fit their reporting needs the best.
For the ingestion, we took advantage of the native application environment available in Snowflake. I have been in the team that has developed several connectors that help out in the ingestion process of data from several third parties and, luckily, we had already developed and published the Exact Online Connector, which would seamlessly extract data from the client’s Exact Online environment and load it into their Snowflake account. However, we would still need to load the property management data that they had in Wunderbricks (Salesforce). Fortunately, there is already a connector published in the Snowflake marketplace that could do this.
Finally, I would create a small Streamlit application using the Streamlit-in-Snowflake functionality, which meant that the people from the company would not have to log into another tool/system in order to manage this and they would be able to do it just by logging in Snowflake.
Process
With the plan already laid down, it was time to start the migration. We had agreed to have it done in about 10 days, which meant we would have to work intensely!
I started by setting up all necessary tools. I created a Snowflake account and a dbt account for them. In one morning, the system already had SSO available for users at the client that could login with their Microsoft Account, plus the dbt project completely set up and an account available for what would be the maintainer. Then, thanks to the already mentioned Snowflake Marketplace, I could install the two ingestion applications that would connect to Exact Online and Wunderbricks and set them up to fetch data from their account. After a bit of extra setting up, at the end of the day, a full modern data warehousing solution that could load data from their sources into a centralized database on a schedule was completely set up, ready to treat and analyze data in the following days.
The next days I was in close contact both with the client’s team and my colleague that would create the dashboards, analyzing which columns were needed in the final tables and how to obtain them from the raw data. This was the time that I had to delve more into financial data in my professional life, and I am happy to say that I learned a lot of new things. Some examples include how to handle transactions in order to properly obtain a summarized Profit and Loss table and a Balance Report table, how hierarchies can be used in order to group different General Ledger accounts into coherent topics, and how linking this hierarchy with the dashboards of Profit and Loss and Balance Report could improve readability of important KPIs.
Finally, I created the small Streamlit application that would allow them to toggle which data would be shown to their clients. Since I already had planned for this it was easy to create it and integrate it with the current data pipeline. In the end, the data pipeline would use this data to decide which rows to filter in the final tables, to ensure that Tableau would only show the correct data.
After that, we just had to make sure that everything was properly documented and that enough time was left to perform an adequate handover, leaving our client with a fully automated data workflow that resulted in actionable dashboards. All this in just a couple weeks!
Final conclusions
In the end, all the objectives were accomplished as agreed. Our client has been able to forget about manual work in Excel Sheets, and just sit back and enjoy a fully automated system that provides data in beautiful, actionable dashboards that, in turn, can be shared with their clients.
We have maintained close contact with the company, making sure to check on questions or small issues that could have appeared, and even extending our time with them to add even more features to their system.
