Automated ETL Script Generation via ML Column Mapping

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

The manual development of ETL scripts for copying data from operational databases to reporting databases is time-consuming and error-prone due to the complexity of relational database schemas and the need for up-to-date knowledge of the operational database schema.

Innovation Solution

A database management tool that automatically generates ETL scripts by parsing plain text instructions, comparing column names, generating a graphical representation of the schema, and using machine learning to convert instructions into executable code for data extraction and loading between databases.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If manual ETL script development is used, then flexibility and control are maintained, but the process becomes time-consuming and error-prone

Engineering Contradiction:

Improveaccuracy of ETL scriptsVSAvoidtime to develop ETL scripts

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system enables self-service automated generation of ETL scripts by parsing plain text instructions and automatically converting them into executable code. The machine learning model autonomously maps source columns to destination columns based on similarity analysis, eliminating the need for manual script writing while ensuring accuracy through automated validation.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The patent replaces the manual mechanical process of ETL script development with an automated machine learning system. The system uses natural language processing and machine learning algorithms to automatically generate, validate, and execute ETL scripts, substituting human effort with intelligent automation that reduces both time and errors.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Measurement precision

If complex schema analysis is performed manually, then accurate data mapping is achieved, but the process becomes difficult and time-consuming

Engineering Contradiction:

Improvecolumn name matching accuracyVSAvoidcomplexity of schema analysis process

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The system introduces a machine learning model as an intermediary between the source and destination database schemas. This intermediary automatically analyzes the complex schema relationships, calculates similarity indices between column names, and performs intelligent mapping, simplifying the complex analysis process while maintaining high precision through automated validation.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent transforms the complex schema analysis problem into a parameter-based similarity calculation task. By converting column name matching into a quantitative similarity index calculation with configurable thresholds, the system simplifies the analysis process while maintaining accuracy through parameter optimization and automated adjustment.

Inventive Principle:

Principle #35Parameter changes

3Productivity

If automated script generation is implemented, then time and errors are reduced, but the system complexity increases

Engineering Contradiction:

Improvedata transfer efficiencyVSAvoidcomplexity of automated generation system

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The automated system is segmented into distinct functional modules: plain text instruction parsing, machine learning-based column mapping, similarity index calculation, threshold-based filtering, and script generation. This modular segmentation manages system complexity by making each component independent and manageable while collectively achieving high productivity in data transfer operations.

Inventive Principle:

Principle #1Segmentation

4Reliability

If frequent data updates are performed from operational to reporting database, then data freshness is maintained, but the load on operational database increases

Engineering Contradiction:

Improvedata freshness in reporting databaseVSAvoidload on operational database

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary analysis of schema changes before executing data transfers. By detecting schema changes in advance and pre-generating appropriate ETL scripts, the system enables efficient data updates that maintain freshness while minimizing operational database load through optimized transfer operations and selective data extraction.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS11321285B2Automatic database script generation for copying data between relational databases

Publication Date: 2022.05.03 BANK OF AMERICA CORP

AI summary

An apparatus includes a memory and processor. The memory stores a graphical representation of a first database that includes source tables and columns. The graphical representation includes nodes associated with source tables and columns. The processor receives an instruction to transfer data from the first database to columns of a second database specified by destination column names. The processor identifies a subset of source columns such that a similarity score for each is greater than a threshold. The similarity score indicates a degree of similarity between a specific destination column name and either the assigned source column name or a related term. The processor uses the subset to generate a pruned graphical representation that includes a subset of nodes. The processor uses this to generate executable code configured to copy data from the first database, determined from the pruned graphical representation, into the second database.