Automation of data flows in a cloud data warehouse

• SECTOR: ENERGY
• Task: In this customer project, the aim was to make the existing data platform usable for several organizations rather than just one
• Team:
  • In total approx. 6 people consisting of POs, Scrum Masters, Data Engineers and DevOps experts
  • Various operation teams which shall use the established pipelines
• Project duration: +12 months

• Customization of existing data pipelines making them usable for multiple organizations
• Scaling: doubling the databases for each new organization, with additional organizations to be added steadily during the year.
• Robustness: a failure of the pipelines can lead to days of downtime for the analyses
• Dynamics in the pipelines must be created to avoid code duplication and keep the pipelines flexible
• Acceleration of the release & deployment processes in the data warehouse
• Modern technologies are already in use, which means that niche problems that have never been solved anywhere can be found.

• Improvement of release & deployment processes through CICD
• Modular, simple and scalable pipelines
• Fully automated provisioning with Terraform
• Replication of complete infrastructures for multiple organizations through Terraform
• Implementation of multiple config mappings enabling dynamic work with the same pipeline across different organizations
• Data integrity through a CDC mechanism using Apache Kafka

• Greater flexibility due to the modular structure of the cloud architecture
• Scalability through AKS (Kubernetes services on Azure) and auto-scaling in the cloud
• Robustness through the use of Infrastructure as Code (Terraform)
• Data integrity and synchronization using modern services such as CDC mechanisms and Apache Airflow
• Faster release cycles through the use of CICD and Azure DevOps pipelines
• Modern Secret Management through the use of Key Vaults and Infrastructure as Code

Special requirements from the customer
The first step in migrating the project was to ensure that, despite migrating the system, the old process cycle would remain intact and a transition to the new system would be as simple as possible. In a further step, the existing automations were equipped with dynamics and no longer tied to one sub-organization, but could be run for many – even several in one run.

Adjustment and construction of the pipelines
By building different release and deployment pipelines, the average time to deliver new features could be reduced significantly. During the development of the pipelines, care was always taken to realize a broad test coverage. Pipelines were built in a modular way so that different sub-teams used them individually depending on the specific use case. Nevertheless, the use of these pipelines had to remain very simple as different teams had to be able to use them.

Robust and modular cloud infrastructure
By using Terraform, the central overview of the cloud resources and infrastructure was clearly defined. Another benefit was the improvement of developer workflows and integration with various DevOps processes. This allowed the project to respond more quickly to different business requirements. The complete infrastructure could thus be made available to different organizations very quickly and the specific requirements of the sub-teams could be addressed in each case.

Project status and results
By optimizing various DevOps processes and building the pipelines, all sub-teams were significantly faster in testing, releasing and deploying the software. Thus, in-house departments were able to see results faster and agilely adapt the customer requirements in the sprints. Using Infrastructure as Code, a robust and modular cloud infrastructure could be created. By creating dynamics in pipelines, many organizations can be attached to the system within a short time and hardly require any manual work.

Cloud Infrastruktur:

• Azure

• Azure Kubernetes Service

• Snowflake

Daten Services:

• Snowflake

• Apache Airflow

• Azure Data Factory

CICD & IaC:

• Azure DevOps

• Azure Key Vault

• Azure Resource Groups

• Terraform

Software Development:

• • Python

  • dbt