Wellington Electricity built a fully automated data pipeline using FME Flow to process daily registry files from electricity retailers, overcoming inconsistent file headers and eliminating manual steps. The solution now runs in-house, increasing efficiency, resilience, and control over critical data workflows.
Keeping the Lights On with Smarter Data Pipelines
Wellington Electricity manages the electricity distribution network for the Wellington region, relying on daily data from electricity retailers to keep their records accurate and up-to-date.
These retailer-supplied files, known as registry files, contain essential information about customer connections, meter details, and service changes. Each day, multiple registry files are delivered via SFTP (secure file transfer protocol), a standard method for exchanging sensitive data between systems.
The real challenge came with processing those files automatically. Although the columns in each file always appeared in the same order, the column names varied between retailers, making it difficult to build a reliable, automated workflow based on standard attribute headers.
Building a Resilient Workflow for Daily Data Delivery
To address this, the Wellington Electricity team set out to build an internal, end-to-end solution using FME Flow, automating the pipeline from file retrieval through to processing and publishing and removing the need for ongoing support from third-party providers.
They approached this in two parts: First, they built a workspace to connect to the SFTP server, identify the previous day’s files, and download them. This task now runs automatically each day. Next, a second workspace was developed to dynamically process each .csv file.
Rather than relying on the column headers, the workspace re-maps the data based on column position, effectively sidestepping the inconsistency of attribute names. Lastly, a change detection is run and updated values are written to a database after which the input files are archived.
As part of the setup, the team built in flexibility and resilience, separating out the retrieval and processing stages so they could easily re-run jobs if needed. All of this is orchestrated through FME Flow Automation, which handles scheduling, error notifications, manual triggers for re-processing when needed and email notifications for both success and failure events.
The result is a robust and adaptable system that can be managed entirely in-house
Tackling a solution of this scale with FME Flow was a new step for the team but the benefits were clear straight away. The pipeline runs smoothly, automatically publishing registry data to where it needs to go, with no manual touchpoints required.
This project is a strong example of how smart use of automation can unlock efficiency, agility, and long-term cost savings, even in the most data-heavy operations.
