Automated Cloud Data Migration with Schema Discovery
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The process of migrating data from an on-premise environment to a cloud-based environment is complex and time-consuming, often requiring manual effort and being prone to errors, especially when dealing with unstructured data and unsupported database objects, and involves challenges like schema creation, ETL conversion, and data type mapping.
Innovation Solution
A method and system that use Machine Learning to predict query performance and automate data migration by selecting optimal cloud locations, converting on-premise ETL to cloud-based ETL tools like AWS Glue, and performing SQL transformations, while identifying and obfuscating sensitive data, thus reducing manual effort and minimizing errors.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If manual data migration processes are used, then flexibility and adaptability are maintained, but time consumption and effort increase significantly
Solution Approach 1:
The system performs self-service by automatically discovering source database schemas, generating target schemas, and converting ETL code without requiring manual intervention. The automated schema discovery detects database objects, relationships, and data types, then generates corresponding target schemas and ETL conversion code autonomously, eliminating the need for manual schema creation and ETL development.
Solution Approach 2:
The system performs preliminary actions by pre-generating target database schemas and ETL conversion code before actual data migration begins. The automated schema discovery and ETL code generation occur in advance, preparing all necessary infrastructure and transformation logic beforehand, which accelerates the subsequent data migration process.
2Productivity
If automated tools are used for data migration, then productivity increases, but complexity of the migration system increases
Solution Approach 1:
The system achieves universality by creating a multi-functional automated migration platform that handles schema discovery, target schema generation, ETL code conversion, and data migration across multiple database types and cloud platforms. This single system performs what would otherwise require multiple separate tools and manual processes, reducing overall system complexity despite the advanced capabilities provided.
Solution Approach 2:
The system acts as an intermediary by introducing an automated schema discovery and ETL conversion layer between the source and target databases. This intermediary component handles the complexity of schema mapping and ETL transformation automatically, shielding users from the underlying complexity while enabling productive automated migration.
3Reliability
If comprehensive data validation and auditing are performed, then data quality and reliability improve, but processing time increases
Solution Approach 1:
The system maintains continuity of useful action by performing data validation, reconciliation, and auditing activities continuously throughout the migration process rather than as separate post-migration steps. The automated ETL code generation includes built-in validation logic, and reconciliation operations are executed continuously to ensure data quality, maintaining migration momentum while ensuring reliability.
Data Source
AI summary
Examples of systems and method for data transition are described. In an example, the present disclosure provides for automating the process of data movement from on premise to cloud, i.e., source Data warehouse (DWH) movement, ETL to cloud base DWH, ETL. The present disclosure provides for objects identification, metadata extraction, automated data type mapping, target data definition script creation, data extraction in bulk using source native optimized utilities, users and access control mapping to the target DWH, binary object movement, end-end audit report, and reconciliation reports.