Untangling the Data Mess: A Cloud-Based Data Warehouse Solution for a Food Service Company

Our client, a food service company involved in meat processing had been struggling to modernise its Data Architecture. It struggled with its on premise IT stack and had significant issues with Electronic Orders Integration and unreliable API connections with its suppliers and customers. They were using on premise oversubscribed and cost-prohibitive SQL Server along with legacy PostGres DB as their Data warehouse, Informatica for ETL (processing using chronological order) and Cognos for reporting, which was cumbersome and lacked advanced functionalities.

User queries were slow and often timing out after several hours. Most of the data extractions run by business overnight would fail. Storage was extremely limited due the cost of scaling databases that combined storage with compute, forcing business to consume only subsets of company data.

They had maintained their own servers and network since inception and maintaining on-premise servers and networks had become limiting, especially as businesses was growing and required more scalability, flexibility, and reliability.

Maintaining optimal temperature conditions is critical to ensure product quality and safety. Historically, this company has faced challenges in efficiently monitoring and managing temperature levels across its facilities and distribution channels, leading to occasional product spoilage and quality issues. They were also missing out on using high-load processing machine efficiency data generated by IoT sensors. The solution architecture was not suitable for IoT sensors real-time analytics.

They evaluated several options and finalised on Azure Cloud Services and QlikSense as their platforms of choice. They needed a partner to move the workloads into cloud. The ageing on-premise infrastructure resulted in decreased productivity and frustration among users.

Migration of outdated on-premise data assets into cloud along with IoT sensors high-load processing data was a challenge. There was little or no documentation available for incoherent data pipelines Cloud technologies required specialized skills that were not readily available within this business.

Addressing these challenges required careful planning, collaboration, and expertise to ensure a smooth and successful cloud migration for our client's business. Transitioning to cloud-based data solutions offers greater scalability and flexibility compared to traditional on-premises data warehouses.

Our Data Engineering team have thoroughly analyzed the client’s needs and requirements. The project was delivered in several drops to migrate securely into the cloud with no business disruptions. IoT sensor data from the food processing machines was collected at regular intervals and loaded to Azure Event Hub and the unstructured data was dumped into Azure Data lake. Processed data was pushed to the Azure Warehouse. A real-time dashboard in QlikSense was created to improve reporting and visualization.

Our team also monitored data pipelines, storage systems, and processing workflows for performance, reliability, and scalability by efficiently collecting metrics, logs, and alerts to identify bottlenecks, anomalies, and opportunities for optimization. We continuously iterated and improved data engineering processes based on feedback and evolving requirements.