ETL Approach to Database Migration

The most popular approach to database migration is known as "extract, transform, load" (ETL) technique. Extraction phase is where schemas, constraints and data are extracted from the original database; transformation is where all extracted objects are transformed for storing in the proper format; loading phase where schemas, constraints and data are loaded into the final target database.

The first part of an ETL process involves extracting all database objects from the source system. In many cases, this represents the most important aspect of ETL, since extracting database objects correctly sets the stage for the success of subsequent parts of migration process. The most appropriate formats for intermediate schema export are XML and JSON. For purpose of temporary storage for data the comma separated values format is the best choice. For complicated databases the extraction process must include validation rules in order to pass correct database objects to the next phase of migration process.

Transformation stage applies number of rules to the extracted database objects in order to prepare them for loading into the target database. The most important functions of transformation are mapping types that do not have direct equivalent, cleaning data in order to make it complied with destination rules, handle character sets that are available in source system but may not be in the target.

Below are examples of transformation that may be required while migrating database between different systems:

• Some DBMS treat empty values as NULL while other distinguish these differences
• Boolean type is defined as synonym to bit (0,1) in particular DBMS but on other systems it may be defined as enum("t","f") that requires appropriate conversion
• Usually foreign keys require regular indexes on influenced columns of child table, however some DBMS may require unique index for the same purpose
• Different systems apply distinguished limitations to number of columns, indexes, length of primary keys and others

Finally, the load phase imports schemas, constraints and data to the destination database. Depending on the requirements it may overwrite or update existing data that is frequently done on a daily, weekly, or monthly basis. In case of update existing tables, it is necessary to disable constraints and triggers in order to preserve data integrity and increase performance of ETL process. More complex systems may also require history and audit trail of all changes being made to the database.

The challenges mentioned above indicate that database migration may be tedious and complicated procedure, especially for large data warehouses. Handling this procedure manually may cause data loss or corruption issues due to human factor. It is reasonable to use special database migration software in order to automate the process and to eliminate possible errors. Intelligent Converters company provides solutions to migrate databases between all popular systems such as PostgreSQL, MySQL, Oracle, SQL Server, Firebird, Microsoft Access, FoxPro, SQLite and corresponding SAAS platforms.

Have questions? Contact us