How a Food Packaging Manufacturer Cut Manual ETL Oversight by 30%

A global food packaging manufacturer needed its supply chain and production data centralised for reporting, and most of it sat behind complex Oracle EBS schemas that kept changing. Every change meant rework, and keeping the pipelines running meant somebody watching them. OnPoint Insights built a metadata-driven ETL framework on Azure Data Factory and SQL Server, with parameterised pipelines, audit logging and automated alerting, which cut manual oversight by 30%.

30% less manual ETL oversight

Automation and a metadata-driven control framework removed close to a third of the hands-on supervision the pipelines used to need.

Schema changes absorbed, not escalated

Parameterized Data Factory pipelines adapt to changes in source and target schemas without a development cycle each time.

Every transformation traceable

Modular stored procedures with built-in logging, plus a dedicated audit pipeline, so a failure is located rather than hunted for.

The problem

The pipelines worked. Keeping them working was the job

An ETL platform is judged by what happens when something changes. A column gets renamed upstream, a job fails overnight, a transformation quietly starts producing the wrong number. If the answer to each of those is a person, the pipelines are not automated. They are supervised.

Our client needed data flows from multiple systems centralised to support proper reporting, and the main source was a complex Oracle EBS estate. Both source and target schemas changed frequently, which meant that anything built by hand would need rebuilding by hand.

What was getting in the way

  • Data flow requirements across multiple systems had to be understood and mapped before a reliable foundation could be built.
  • Source and target schemas were changing frequently, so the framework had to adapt without heavy manual intervention.
  • Transformation logic needed to be reusable across workflows rather than duplicated for each one.
  • The client needed monitoring, logging and audit mechanisms to track execution, identify failures and make the process transparent.

Our approach

Build the framework, not the pipelines

What we found

The schemas were going to keep changing, which meant a set of hand-built pipelines would have been obsolete on arrival. The only durable answer was to make pipeline behaviour configuration rather than code, so that a change upstream is something the framework reads rather than something a developer absorbs.

What we did

One architecture, agreed up front. Requirement workshops defined the complete data flow and a unified architecture aligned to the reporting the business actually needed.

A dimensional model built for analysis. Star and snowflake schemas organising facts and dimensions, so new reporting extends the model rather than requiring a new one.

A metadata-driven control framework. A control table orchestrates task execution, dependencies and runtime configuration, and parameterised Data Factory pipelines adapt dynamically to schema changes in source and target systems.

Logging, auditing and alerting built in, not bolted on. Modular stored procedures with logging and error handling at every step, a dedicated pipeline capturing audit logs, and Azure Logic Apps sending email alerts on job status and failures.

The choice we made

We built a metadata-driven framework rather than a pipeline per data flow. It is slower to start, and the first pipeline costs more than it would have done by hand. With schemas changing as often as these did, it means a change becomes a configuration update instead of a development ticket. The 30% reduction in manual oversight comes from that decision, not from automation on its own.

BUILT ON
  • Azure Logic Apps Azure Logic Apps
  • Azure Data Factory Azure Data Factory
  • Oracle Oracle
  • Azure SQL Database Azure SQL Database
  • Microsoft SQL Server Microsoft SQL Server

The results

What changed for the data team and the business

The pipelines stopped needing a minder, and the reporting on top of them became something the business could rely on.

A third less time spent watching jobs

The 30% reduction in manual ETL oversight is time the data team now spends building things rather than monitoring things that should run themselves.

Reporting the business trusts

Validation, logging and error handling at every step improved data integrity and raised confidence in the numbers reaching supply chain and production teams.

Schema changes stop being projects

The framework absorbs changes in source and target systems through configuration, so the platform keeps working as the systems around it move, which they will.

Let's Connect

Facing a similar challenge?

Talk to our data and AI team about where you are today and what good looks like. 30 minutes, no obligation.

Book a consultation
Professional customer service agent on a support call
More Case Studies
Next Case Study
Contact Us

Collaborate with us

We're here to answer your questions and help you find the right solution.

Client-oriented
Results-driven
Problem-solving
Transparent

"*" indicates required fields

This field is for validation purposes and should be left unchanged.