Data Migration - Our Approach
The Development of an ETL (Extract, Transform, Load) Framework to:
Reduce and Reuse Code: Creating common components for use across various data transformations.
Manage Global Changes: For example - switching from test mode to 'real' mode (test mode may be required to mask real customer contact data for some tests), changing migration scope in one location only.
Complexity and Volume of Documentation Reduction: Minimize the complexity and volume of documentation needed to understand and maintain the ETL processes.
Importing Migration Rules: The functionality to import mapping spreadsheets, which helps in defining data transformation rules - the mappings between source and target data.
Separating data transformation and formatting: The generation of views/table valued functions that export transformed data in a vendor-defined format, when required.
Data Validation: The framework includes data validation capabilities, which follow vendor guidelines for validation, when available. This ensures the integrity and accuracy of transformed data.
To measure and track the quality of the ETL processes, the framework implements the following mechanisms:
Execution Logs: Maintains detailed execution logs, recording essential performance metrics of ETL procedures, such as the number of records processed and elapsed time taken for each operation.
Error Records: Captures and identifies failure reasons in ETL procedures, allowing efficient diagnosis and rectification of issues.
Validation Records: Tracks validation errors encountered during data passes. This mechanism helps ensure data accuracy and assists in addressing data quality concerns.