The World of Data 2025
Experience the data event of the year!
From On-Premises to AWS Redshift: Successful DWH Migration with Vision
EOS Technology Solutions (TS) is the internal IT service provider of the EOS group. The OTTO group's enterprise is active in the field of receivables management. As a central service company, EOS TS offers a data platform to the companies of the German EOS subsidiary (EOS-DID). In addition to a data lake, this platform includes a DWH, an ETL tool, a BI solution and data-science services. The aim of this project was to migrate the on-premise data platform to the cloud.
This project encompassed all three phases of a migration process, from assessment through mobilization to actual migration.
Different migration strategies were pursued for each functional building block of the data platform:
In the following, we will discuss the replacement of the Exasol database with AWS Redshift in more detail.
DWH migration is always a complex undertaking if you decide against a lift-and-shift strategy and instead pursue re-factoring to a new database engine, as in this case. The following requirements must then be met:
In particular, the memory limitation of the existent DWH on Exasol (license and hardware capabilities of the on-premise server) was also a major factor prompting replacement with Redshift here.
A decision against a lift-and-shift approach was not clearly evident at the beginning of the project. In a first step, a benchmark was established with comparable data volumes and specific EOS-SQL queries, thus enabling an initial statement about adequate sizing of a Redshift. It turned out that a Redshift with about half the hardware capability (CPU & main memory) can measure up to the Exasol database. At that time, it was already clear that parts of the existent SQL scripts and views of the Redshift database needed to be optimized to achieve a performance comparable to that of the Exasol DB.
This can be explained primarily by architectural differences between the two databases (e.g. sort keys instead of indices, and different behaviour of query optimizers). For cost reasons, a larger variant of the Redshift DB was not an option, and in such cases, it is usually advisable to optimize the software (the SQLs) first. As a search for automation tools for migrating Exasol to Redshift did not yield satisfactory results, it was decided to perform the migration "manually". This was carried out in the following steps:
A DWH migration is a complex project from which we (b.telligent and EOS) have learned a lot:
Although such migrations can be packaged into standard procedures (assess, mobilize, migrate & modernize), it is ultimately the details of the existent system and dependencies between functional building blocks which require special attention and expenditure.
Cloud Migration Strategy
Development of a tailored migration strategy in three phases — Assess, Mobilize, Migrate
DWH Refactoring
Manual SQL translation and optimization for Redshift including test framework
CI/CD & Infrastructure as Code
Development of the complete infrastructure via Terraform (IaC) including VPC, IAM & Co
ETL Modernization
Migration and optimization of ETL processes (Ab Initio, DMS) and introduction of a new framework
BI Realignment
Replacing MicroStrategy with Power BI including data model adjustment
Data Security & Governance
Integrate Protegrity on AWS, align with ISO & governance teams
Performance boost: Load and query processes run around 30% faster on Redshift than before.
Significant cost reduction: The cloud infrastructure reduces running costs by around 50%.
Cloud readiness: Development of a scalable, secure and agile data platform as a basis for international development.
After a very intensive period of 8 months, all ETL processes (2,500 SQL scripts/views) were converted to Redshift, and day processing on Redshift was around 30% faster, with infrastructure costs (Exasol on AWS vs. Redshift) being around 50% lower. With this solution, EOS now has a DWH solution with the following features
A DWH is never really finished, which means that, after a migration with a rather minor development, the following points are now on the roadmap:
Tune in to the podcast now!
As an Advanced Partner of AWS, b.telligent supports its customers in the migration and setup of data platforms in the AWS cloud. More information here!
Want a handy PDF version of our success story? Whether you need it for yourself or to introduce the project to your team, download it now and explore the full success story. Enjoy reading!
Did our success stories spark your interest? If you're facing similar challenges in data, analytics and AI and look for expert support, let’s talk. A brief call can reveal how we can help you move forward.