EOS: Migrating an On-Premise DWH

Solution Approach and Solution Architecture

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:

1. Inventory of migration objects (SQL scripts and views)
2. Implementation of a generator (shell script) based on Exasol dictionary tables for:
   a) Exasol table export to S3
   b) table imports from Redshift to S3
   c) Generate table DDLs for the Redshift database
3. Creation of a “translation list” to replace EXASOL SQL with Redshift SQL
4. Testing the speed  of a data migration, which turned out to have a reasonable range of around 6 hours, thus eliminating the need for complex data migration using CDC or AWS Snowball.
5. Implementation of a test framework for a detailed comparison of Exasol data (tables & views) with Redshift data. Detailed statistics (COUNT(*), COUNT(distinct column), MIN, MAX, AVG, LENGTH) were collected and compared for each table/view here. This test framework was based on data dictionaries, and therefore generated automatically.
6. Continuous translation of SQL scripts and views, accompanied by a comparison of data from both databases.
7. Parallel operation of the two DWH using the test framework to eliminate residual errors in migration over a period of approx. 4 weeks.

Learnings

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.