How Open Universities Australia modernized their knowledge platform and considerably diminished their ETL prices with AWS Cloud Growth Package and AWS Step Capabilities

This can be a visitor submit co-authored by Michael Davies from Open Universities Australia.

At Open Universities Australia (OUA), we empower college students to discover an unlimited array of levels from famend Australian universities, all delivered by on-line studying. We provide college students different pathways to realize their instructional aspirations, offering them with the pliability and accessibility to succeed in their educational targets. Since our founding in 1993, we have now supported over 500,000 college students to realize their targets by offering pathways to over 2,600 topics at 25 universities throughout Australia.

As a not-for-profit group, value is a vital consideration for OUA. Whereas reviewing our contract for the third-party software we had been utilizing for our extract, rework, and cargo (ETL) pipelines, we realized that we may replicate a lot of the identical performance utilizing Amazon Net Companies (AWS) providers comparable to AWS Glue, Amazon AppFlow, and AWS Step Capabilities. We additionally acknowledged that we may consolidate our supply code (a lot of which was saved within the ETL software itself) right into a code repository that may very well be deployed utilizing the AWS Cloud Growth Package (AWS CDK). By doing so, we had a possibility to not solely cut back prices but additionally to reinforce the visibility and maintainability of our knowledge pipelines.

On this submit, we present you ways we used AWS providers to exchange our current third-party ETL software, enhancing the workforce’s productiveness and producing a major discount in our ETL operational prices.

Our method

The migration initiative consisted of two foremost elements: constructing the brand new structure and migrating knowledge pipelines from the present software to the brand new structure. Typically, we’d work on each in parallel, testing one element of the structure whereas creating one other on the identical time.

From early in our migration journey, we started to outline a couple of guiding rules that we’d apply all through the event course of. These had been:

• Easy and modular – Use easy, reusable design patterns with as few shifting elements as doable. Construction the code base to prioritize ease of use for builders.
• Value-effective – Use sources in an environment friendly, cost-effective method. Purpose to reduce conditions the place sources are operating idly whereas ready for different processes to be accomplished.
• Enterprise continuity – As a lot as doable, make use of current code quite than reinventing the wheel. Roll out updates in phases to reduce potential disruption to current enterprise processes.

Structure overview

The next Diagram 1 is the high-level structure for the answer.

The next AWS providers had been used to form our new ETL structure:

• Amazon Redshift – A completely managed, petabyte-scale knowledge warehouse service within the cloud. Amazon Redshift served as our central knowledge repository, the place we’d retailer knowledge, apply transformations, and make knowledge out there to be used in analytics and enterprise intelligence (BI). Word: The provisioned cluster itself was deployed individually from the ETL structure and remained unchanged all through the migration course of.
• AWS Cloud Growth Package (AWS CDK) – The AWS Cloud Growth Package (AWS CDK) is an open-source software program growth framework for outlining cloud infrastructure in code and provisioning it by AWS CloudFormation. Our infrastructure was outlined as code utilizing the AWS CDK. Because of this, we simplified the best way we outlined the sources we needed to deploy whereas utilizing our most popular coding language for growth.
• AWS Step Capabilities – With AWS Step Capabilities, you may create workflows, additionally referred to as State machines, to construct distributed purposes, automate processes, orchestrate microservices, and create knowledge and machine studying pipelines. AWS Step Capabilities can name over 200 AWS providers together with AWS Glue, AWS Lambda, and Amazon Redshift. We used the AWS Step Perform state machines to outline, orchestrate, and execute our knowledge pipelines.
• Amazon EventBridge – We used Amazon EventBridge, the serverless occasion bus service, to outline the event-based guidelines and schedules that may set off our AWS Step Capabilities state machines.
• AWS Glue – A knowledge integration service, AWS Glue consolidates main knowledge integration capabilities right into a single service. These embrace knowledge discovery, fashionable ETL, cleaning, reworking, and centralized cataloging. It’s additionally serverless, which suggests there’s no infrastructure to handle. consists of the flexibility to run Python scripts. We used it for executing long-running scripts, comparable to for ingesting knowledge from an exterior API.
• AWS Lambda – AWS Lambda is a extremely scalable, serverless compute service. We used it for executing easy scripts, comparable to for parsing a single textual content file.
• Amazon AppFlow – Amazon AppFlow permits easy integration with software program as a service (SaaS) purposes. We used it to outline flows that may periodically load knowledge from chosen operational methods into our knowledge warehouse.
• Amazon Easy Storage Service (Amazon S3) – An object storage service providing industry-leading scalability, knowledge availability, safety, and efficiency. Amazon S3 served as our staging space, the place we’d retailer uncooked knowledge previous to loading it into different providers comparable to Amazon Redshift. We additionally used it as a repository for storing code that may very well be retrieved and utilized by different providers.

The place sensible, we made use of the file construction of our code base for outlining sources. We arrange our AWS CDK to discuss with the contents of a particular listing and outline a useful resource (for instance, an AWS Step Capabilities state machine or an AWS Glue job) for every file it present in that listing. We additionally made use of configuration recordsdata so we may customise the attributes of particular sources as required.

Particulars on particular patterns

Within the above structure Diagram 1, we confirmed a number of flows by which knowledge may very well be ingested or unloaded from our Amazon Redshift knowledge warehouse. On this part, we spotlight 4 particular patterns in additional element which had been utilized within the ultimate answer.

Sample 1: Knowledge transformation, load, and unload

A number of of our knowledge pipelines included important knowledge transformation steps, which had been primarily carried out by SQL statements executed by Amazon Redshift. Others required ingestion or unloading of information from the information warehouse, which may very well be carried out effectively utilizing COPY or UNLOAD statements executed by Amazon Redshift.

In step with our purpose of utilizing sources effectively, we sought to keep away from operating these statements from inside the context of an AWS Glue job or AWS Lambda operate as a result of these processes would stay idle whereas ready for the SQL assertion to be accomplished. As an alternative, we opted for an method the place SQL execution duties can be orchestrated by an AWS Step Capabilities state machine, which might ship the statements to Amazon Redshift and periodically examine their progress earlier than marking them as both profitable or failed. The next Diagram 2 exhibits this workflow.

Sample 2: Knowledge replication utilizing AWS Glue

In circumstances the place we would have liked to duplicate knowledge from a third-party supply, we used AWS Glue to run a script that may question the related API, parse the response, and retailer the related knowledge in Amazon S3. From right here, we used Amazon Redshift to ingest the information utilizing a COPY assertion. The next Diagram 3 exhibits this workflow.

Word: An alternative choice for this step can be to make use of Amazon Redshift auto-copy, however this wasn’t out there at time of growth.

Sample 3: Knowledge replication utilizing Amazon AppFlow

For sure purposes, we had been ready to make use of Amazon AppFlow flows instead of AWS Glue jobs. Because of this, we may summary a number of the complexity of querying exterior APIs immediately. We configured our Amazon AppFlow flows to retailer the output knowledge in Amazon S3, then used an EventBridge rule primarily based on an Finish Circulate Run Report occasion (which is an occasion which is revealed when a movement run is full) to set off a load into Amazon Redshift utilizing a COPY assertion. The next Diagram 4 exhibits this workflow.

By utilizing Amazon S3 as an intermediate knowledge retailer, we gave ourselves higher management over how the information was processed when it was loaded into Amazon Redshift, in comparison with loading the information on to the information warehouse utilizing Amazon AppFlow.

Sample 4: Reverse ETL

Though most of our workflows contain knowledge being introduced into the information warehouse from exterior sources, in some circumstances we would have liked the information to be exported to exterior methods as an alternative. This fashion, we may run SQL queries with advanced logic drawing on a number of knowledge sources and use this logic to help operational necessities, comparable to figuring out which teams of scholars ought to obtain particular communications.

On this movement, proven within the following Diagram 5, we begin by operating an UNLOAD assertion in Amazon Redshift to unload the related knowledge to recordsdata in Amazon S3. From right here, every file is processed by an AWS Lambda operate, which performs any obligatory transformations and sends the information to the exterior utility by a number of API calls.

Outcomes

The re-architecture and migration course of took 5 months to finish, from the preliminary idea to the profitable decommissioning of the earlier third-party software. A lot of the architectural effort was accomplished by a single full-time worker, with others on the workforce primarily aiding with the migration of pipelines to the brand new structure.

We achieved important value reductions, with ultimate bills on AWS native providers representing solely a small share of projected prices in comparison with persevering with with the third-party ETL software. Shifting to a code-based method additionally gave us higher visibility of our pipelines and made the method of sustaining them faster and simpler. Total, the transition was seamless for our finish customers, who had been capable of view the identical knowledge and dashboards each throughout and after the migration, with minimal disruption alongside the best way.

Conclusion

By utilizing the scalability and cost-effectiveness of AWS providers, we had been capable of optimize our knowledge pipelines, cut back our operational prices, and enhance our agility.

Pete Allen, an analytics engineer from Open Universities Australia, says, “Modernizing our knowledge structure with AWS has been transformative. Transitioning from an exterior platform to an in-house, code-based analytics stack has vastly improved our scalability, flexibility, and efficiency. With AWS, we are able to now course of and analyze knowledge with a lot sooner turnaround, decrease prices, and better availability, enabling speedy growth and deployment of information options, resulting in deeper insights and higher enterprise selections.”

Extra sources

---

In regards to the Authors

Michael Davies is a Knowledge Engineer at OUA. He has in depth expertise inside the schooling {industry}, with a specific give attention to constructing sturdy and environment friendly knowledge structure and pipelines.

Emma Arrigo is a Options Architect at AWS, specializing in schooling clients throughout Australia. She focuses on leveraging cloud know-how and machine studying to handle advanced enterprise challenges within the schooling sector. Emma’s ardour for knowledge extends past her skilled life, as evidenced by her canine named Knowledge.