Modernizing architecture to tackle challenges
As the Insight team worked with the restaurant chain to identify areas of improvement, three primary challenges became clear. First and foremost, the rapid migration had left the organization with several disparate sources of data, which led to major gaps in reporting. Secondly, manual collection and aggregation processes were prone to human error and made it nearly impossible for employees to generate accurate reports.
Furthermore, to curb the high costs of database storage, the IT team relied on frequent data erasure — wiping their history daily, then reloading the past 60 days of data. This process resulted in inaccurate historical data that made it difficult to gain long-term insights for improved decision-making.
To address these challenges, the decision was made to consolidate data and optimize reporting using a data lake and modern data warehouse model.
From July 2019 to December 2019, an Insight team of two data engineers and one data architect began transforming the organization’s data architecture and consolidating the various sources into a singular ingestion pipeline. They worked closely with the restaurant chain’s CIO, applications services director and BI manager to understand the objectives for data, reporting and security, and share updates as the project progressed.
The client had an existing data lake, but usage was limited to one or two specific processes, and it lacked the containerization needed to support broader reporting. Insight helped redefine the layers of the data lake to meet the new requirements. This new architecture provided a more cost-effective solution for data storage and cleansing.
An enterprise data warehouse was then established in Azure as the central repository for current and historical data, enabling users to generate reports more easily and accurately. Azure Databricks was selected as the preferred Extraction, Transformation and Loading (ETL) tool used to pull data from disparate sources into the consolidated ingestion layer. Performance testing against a pure Azure Data Factory (ADF) ETL solution revealed Databricks had the performance benefits required to meet the client’s Service Level Agreements (SLAs).
Throughout the project, Insight collaborated with our partners at Microsoft to ensure the recommended architecture and toolsets aligned with Microsoft best practices.
Insight teammates also built out an auditing log to capture record counts and load times at each step of the ingestions process. This was an entirely new capability that would enable the client’s IT team to identify discrepancies and pinpoint issues from day to day.