Hamish Gray

Delivered Google Cloud Platform (GCP) data lake and cloud data analytics solution for analytics at Powerco.

Using an Agile methodology delivering product owner requirements, the GCP platform has incrementally added functionally and value previously unattainable data.

Engaged directly with a range of people across Powerco from marketing to billing, operations and asset management strategy, the platform is now widely relied upon across the business.

With a focus on config over code, the platform was able to onboard other "less experienced" developers who could design and implement their own data pipelines for their business area. This allowed the core team to focus on improving the platform, adding features that others could leverage.

The application loaded and parsed (via Pandas and Python) any text-based file into generic structures in a SQL server database. Users would provide basic information such as file name, type, and location, in the form of configuration and the system would consume and then parse into a basic tabular format.

This allowed the loading an analysis of multiple files, all relating to the same subject area without code. The application became the cornerstone of a number of "hack days" where users would bring their data and their required outcomes. Because the data loaded, automated time could be spent on analysis and exploration.

The tool led to a number of business outcomes included a 450K error found in pricing.

A Python and Flask app that combined billing and pricing information to produce immutable and auditable inputs for pricing modeling.

As part of an audit requirement, key inputs used to calculate pricing information were required to include lineage and logging on inputs. Using Python, a Flask app was built that would accept and record user inputs and gather data from source systems. A number of business rules were then implemented to create datasets that were used as input into the pricing model.

This allowed the auditor to fully track how inputs were created, what data was used, and the logic applied.

Initially, the project began with a batch optimization program. Changing the delivery of key business reports from 4 pm to 8 am. This involved a 150-hour data migration process to redesign a key fact table.

In doing this a number of inconsistencies in the data were discovered which would require reloading of data. However, as the source system was part of a fast flow underwriting system access to the database was only available from 4 am to 6 am each day. This required a careful recapture process.

New products and risks were constantly being added to the platform; these were required to be modeled and delivered for actuarial analysis. To support better analysis a semantic layer was created including the creation of key business measures of strike rates and leakage (discounting).

Lastly, a sustainable support model was created and operations were handled off-shore.