Automating ETL for a Global Food Packaging Manufacturer
OnPoint Insights partnered with a leading food packaging manufacturer to modernize their data infrastructure and enhance operational visibility. By streamlining data pipelines and building user-friendly dashboards, we enabled more informed decision-making across supply chain and production functions. The engagement focused on driving efficiency, improving reporting accuracy, and supporting the client’s growth through scalable analytics solutions.
Lancaster Airport (LNS) is a public-use airport located four miles (6 km) north of the central business district of Lancaster, a city in Lancaster County, Pennsylvania. Lancaster Airport is a general aviation airport and offers various services, including fuel, maintenance, and aircraft rental. It also hosts several aviation-related businesses, such as flight schools, aircraft sales, and aviation consulting. The Lancaster Airport Authority manages the airport and is responsible for its operation and development. The Lancaster Airport Authority is a governmental entity created by Lancaster County in 1961 to develop and operate the airport.
The Challenge
A leading manufacturer of innovative food packaging solutions needed to centralize and streamline data flows from multiple systems to support advanced reporting and analytics. The project involved navigating complex Oracle EBS databases and required building a scalable, future-proof ETL framework. Key challenges included:
- Understanding and mapping multi-source data flow requirements
- Adapting to frequently changing database schemas
- Designing reusable and maintainable transformation logic
- Ensuring robust monitoring and auditability
Our Solution
OnPoint Insights implemented a modern ETL architecture using Azure Data Factory, Logic Apps, and SQL Server. The solution featured:
- Requirement Workshops: Collaborated with client teams to define the complete data flow and design a unified architecture.
- Data Model Design: Developed dimensional models (star/snowflake) to organize facts and dimensions, ensuring analytic scalability.
- Oracle Integration: Mapped and extracted data from complex Oracle schemas, creating efficient queries for staging and transformation.
- Dynamic Pipelines: Built configurable pipelines using ADF parameters to dynamically adapt to schema changes in source and target systems.
- ETL Control Framework: Designed a metadata-driven control table to orchestrate task execution, define dependencies, and manage runtime configurations.
- Stored Procedures & Logging: Developed modular stored procedures with built-in logging and error handling. Enabled traceability of transformations at every step.
- Automation & Alerts:Integrated Logic Apps to trigger email alerts for job statuses, failures, and pipeline execution logs.
- Audit Logging: Created a dedicated pipeline to capture and store audit logs, enhancing transparency and compliance.
Results
The deployed solution provided the client with:
- 30% reduction in manual ETL oversight through automation and smart control logic
- Improved data integrity and trust with built-in validation and logging mechanisms
- Future-ready architecture adaptable to ongoing business and data changes
- Accelerated reporting through clean, structured, and reliable data delivery
Technologies Used
WordPress
Php
HTML
CSS
Javascript
Bootstrap
Python
Value Delivered To The Customer
- The seamless UI and interactive homepage facilitated smooth navigation for Lancaster’s customers, resulting in an increased customer conversion rate for the client.
- The updated design delivered real-time flight status updates, weather reports, and a fully compatible user interface for all devices.
- A user-friendly content management system that facilitates smooth updates to content.
Technologies Used
- WordPress
- HTML
- CSS
- PHP
- Python
- Javascript
Azure Logic Apps
Azure data factory
Oracle EBS database
SQL Server Management Service
The Results
- The seamless UI and interactive homepage facilitated smooth navigation for customers, resulting in an increased customer conversion rate for the client.
- A user-friendly content management system that facilitates smooth updates to content.
- The OnPoint Insights team developed an agenda and minutes section that is not only user-friendly on the front end but also backend-friendly for easy updates.
- The updated design will deliver real-time flight status updates, weather reports, and a fully compatible user interface for all devices.
Take Away
This case study showcases how OnPoint Insights Insight assisted a prominent local entity like Lancaster to develop a comprehensive website, aiming to bolster their engagement with the community and establish a compelling online presence.