Intelligent ETL processing system and method using machine learning-based automatic schema analysis and automatic SQL query generation

Abstract

The present invention relates to an AI-based ETL (Extract, Transform, Load) automation solution for data integration between heterogeneous databases, and more specifically, to an electronic device and a control method thereof that automatically analyzes a data schema using machine learning and automatically generates an executable SQL query based on the analyzed results.

Description

Technology Field

[0001] The present invention relates to an AI-based ETL (Extract, Transform, Load) automation solution for data integration between heterogeneous databases, and more specifically, to an electronic device and a control method thereof that automatically analyzes a data schema using machine learning and automatically generates an executable SQL query based on the analyzed results. Background Technology

[0002] Existing data integration technologies suffer from inefficiency and frequent human errors due to their reliance on manual mapping for large-scale column comparisons. Additionally, rule-based methods suffer from low mapping accuracy because it is difficult to grasp the contextual meaning of terms such as 'Phone' and 'Contact'.

[0003] In particular, there is a limitation in that the entire data pipeline is halted when schema drift occurs, resulting in excessive maintenance costs. Prior art literature

[0004] Domestic Publication 10-2006-0106641 A (2006.10.12) Domestic Publication 10-2022-0011066 A (2022.01.27) Domestic Publication 10-2010-0076953 A (2010.07.06) The problem to be solved

[0005] The objective of the present invention is to implement a system that reduces data mapping time through semantic analysis, accurately identifies the meaning of ambiguous columns through data pattern analysis, and provides a self-healing function that ensures pipeline continuity without administrator intervention even when schema changes occur. means of solving the problem

[0006] The present invention relates to an intelligent ETL (Extract, Transform, Load) processing system for performing data integration between heterogeneous databases, comprising: a memory for storing data; and a processor for executing a program stored in the memory; wherein the processor extracts metadata and actual data from a source database and performs semantic schema profiling; wherein, during the semantic schema profiling, it generates an integrated semantic vector by combining metadata analysis that converts the contextual meaning of column names into embedding vectors using a BERT (Bidirectional Encoder Representations from Transformers) model and pattern analysis that classifies data types based on the distribution of actual data using a KNN (K-Nearest Neighbors) classifier; and wherein it drives an intelligent mapping engine that calculates the cosine similarity between the generated integrated semantic vectors and infers the mapping relationship between the source and target databases by referring to a knowledge graph. We propose an intelligent ETL processing system configured to generate dynamic SQL scripts by translating the inferred mapping relationships into executable SQL code through a Seq2Seq AI model and performing dialect optimization to adjust query syntax according to the type of target database.

[0007] The processor may be configured to detect schema drift, including the addition or modification of columns in the source database, in real time; and, upon detection of the schema change, to perform a self-healing function that ensures the continuity of the data pipeline by recalculating mapping relationships through the intelligent mapping engine and updating and distributing the dynamic SQL script.

[0008] The above processor performs a self-learning feedback loop based on the Proximal Policy Optimization (PPO) reinforcement learning algorithm; and can be configured to improve domain-specific mapping accuracy by updating model weights in real time according to the user's acceptance or modification action regarding the generated mapping proposal.

[0009] The above processor may be configured to infer mapping relationships by utilizing a combination of a rollup technique that increases analysis efficiency by abstracting multiple associated columns into a higher-level concept and a drilldown technique that performs precise mapping at the level of detailed columns when the higher-level concept is matched.

[0010] The above system may be physically configured as a 3-Tier architecture in which a designer module that models ETL jobs based on a GUI; a server module that performs integrated control and AI computation processes; and an agent module responsible for actual data implementation in a distributed environment are integrated.

[0011] The above agent module can be configured to support an auto-scale function that automatically scales resources up and down according to system load in a Kubernetes or Docker environment. Effects of the invention

[0012] According to the present invention, data integration time can be reduced by approximately 80% compared to the existing manual mapping method through AI automation.

[0013] In addition, it ensures uninterrupted pipeline continuity by realizing a self-healing function that recalculates mappings and updates SQL for changed schemas through Change Data Capture (CDC) technology, which immediately detects column additions and changes in the source database based on logs. Brief explanation of the drawing

[0014] Other aspects, features, and benefits of specific preferred embodiments of the present invention, as described above, will become more apparent from the following description in conjunction with the accompanying drawings. FIG. 1 is a block diagram illustrating the overall configuration of an intelligent ETL processing system according to one embodiment of the present invention. Figure 2 illustrates the detailed operation of a semantic profiling module for understanding the meaning of source data. Figure 3 illustrates the process of forming a mapping relationship between a source and a target based on extracted vector information. Figure 4 illustrates the process of generating an SQL query optimized for a target database based on confirmed mapping information. Figure 5 illustrates the process in which the system interacts with the user, continuously evolves, and responds to changes. It should be noted that in the drawings above, similar reference numbers are used to illustrate identical or similar elements, features, and structures. Specific details for implementing the invention

[0015] The intelligent ETL processing system according to an embodiment of the present invention is a next-generation solution that automates the processes of data extraction, processing, and loading, and is implemented by an electronic device comprising a processor and memory. The memory stores instructions for performing machine learning-based schema analysis and SQL generation logic, which are core technologies of the present invention, and the processor executes programs stored in the memory to process overall operations for performing data integration between heterogeneous databases. The present invention aims to provide an autonomous operating system that overcomes the inefficiency and human error associated with existing manual mapping methods and can flexibly respond even to schema changes.

[0016] FIG. 1 is a block diagram illustrating the overall configuration of an intelligent ETL processing system according to one embodiment of the present invention.

[0017] The system architecture of the present invention operates in a 3-Tier structure in which three main modules, NF Designer, NF Server, and NF Agent, are organically integrated to realize efficient and integrated data management.

[0018] The above-mentioned NF Designer is an Integrated Development Environment (IDE) tool dedicated to ETL development implemented by the processor, providing users with an intuitive development and debugging interface in a Windows environment. Users can freely combine over 900 standardized components via drag-and-drop in a GUI-based development window, thereby visually modeling complex data pipelines and business logic. The processor stores the job designs and metadata created by the user in a shared repository to perform version control and configuration management, thereby establishing an environment where multiple developers can collaborate.

[0019] The above NF server serves as a hub responsible for intelligent monitoring and control of the entire system, and the processor executes ETL scheduling, real-time integrated monitoring, and job execution prediction functions through it. In particular, the NF server provides a web-based management console to enable users to check the system status anytime and anywhere and respond immediately in the event of a failure, and ensures the availability of the monitoring server itself through High Availability (HA) clustering technology.

[0020] The physical transfer and processing of actual data are performed by the NF agent. The processor controls multi-agent-based distributed execution through the agent and transmits job execution information, logging data, and hardware resource usage collected from each agent to the NF server in real time. This entire system supports both on-premise and cloud environments, and in particular, provides scalability optimized for large-scale data processing by supporting an Auto Scale In-Out function that dynamically allocates resources according to load in Docker and Kubernetes environments.

[0021] Figure 2 illustrates the detailed operation of a semantic profiling module for understanding the meaning of source data.

[0022] Figure 2 illustrates the detailed operation and data flow of a semantic profiling module for identifying the semantic nature of source data. The processor extracts metadata and actual sample data from source data sources (RDBMS, NoSQL, File, etc.) and analyzes them through an artificial intelligence model to overcome the limitations of simple text matching.

[0023] Specifically, the processor runs a BERT (Bidirectional Encoder Representations from Transformers) model to perform contextual analysis of column names. For example, even if column names in the database are abbreviated or ambiguously named, such as 'USR_PHN' or 'CT_NUM', the BERT model identifies the contextual meaning of the abbreviation based on its pre-trained natural language understanding capabilities and converts it into a high-dimensional embedding vector.

[0024] Simultaneously, the processor performs data pattern analysis using a K-Nearest Neighbors (KNN) classifier to complement the accuracy of metadata analysis. The processor analyzes the statistical distribution and format of actual incoming data values ​​(e.g., '010-4567-8900') and automatically classifies the column as a 'Korean mobile phone number' type based on the inherent patterns of the data, even if the column name lacks meaningful information.

[0025] The processor generates a Unified Semantic Vector by combining the metadata analysis results and pattern analysis results, which serves as foundational data for intelligent mapping based on the actual meaning rather than the external names of the data. Additionally, the processor applies Rollup technology to abstract multiple related columns into a single higher-level concept. This efficiently manages the dimensions of the analyzed data and reduces computational complexity during mapping inference, thereby enhancing the profiling performance of the entire system.

[0026] Figure 3 illustrates the process of forming a mapping relationship between a source and a target based on extracted vector information.

[0027] The processor analyzes the Unified Semantic Vector generated in the aforementioned step in a high-dimensional space to derive optimal data associations. Specifically, the processor performs a Cosine Similarity algorithm to calculate vector proximity between each column of the source database and candidate columns of the target database, and quantifies the semantic similarity of the two data through the cosine value of the angle between the two vectors. For example, even if the 'm_phone' column of the source system and the 'Contact_Number' column of the target system do not match at all in terms of text, if the similarity between the two vectors is calculated to be 98% as a result of semantic vector analysis, the processor automatically proposes them as valid mapping candidates.

[0028] At this time, the processor goes beyond simple 1:1 probabilistic matching and performs relationship inference between databases by referencing a knowledge graph stored in memory. Through the knowledge graph, the processor identifies the relationship between the primary key (PK) and foreign key (FK) of the source system and, based on this, logically identifies the complex n:m mapping structure between the source and the target. Furthermore, during the inference process, the processor performs a primary matching by grouping multiple related sub-columns into a single higher-level concept (e.g., the integrated concept of 'address') using rollup technology. Once the consistency of the higher-level concept is confirmed, it undergoes a multi-stage verification procedure that activates drilldown technology to precisely map to each detailed column unit (e.g., city / county / district, detailed address). This intelligent inference process fundamentally prevents human errors that occurred when comparing over 1,000 columns in the existing manual method, and consequently provides significant efficiency by reducing the overall mapping construction time by more than 80%.

[0029] Figure 4 illustrates the process of generating an SQL query optimized for a target database based on confirmed mapping information.

[0030] Referring to Fig. 4, the processor controls a dynamic SQL generation process that converts confirmed mapping logic into executable SQL code.

[0031] The processor controls a Seq2Seq (Sequence-to-Sequence) AI model to convert logically derived mapping logic into physical code. The Seq2Seq model receives mapping data between a source and a target as an input sequence, learns data transformation rules, and performs a query translation step that outputs SQL code in a form that can be immediately executed in the target database.

[0032] In this process, the processor activates an internal template engine to automatically perform dialect optimization that reflects the unique syntactic characteristics of the target database. For instance, even for the same data type conversion task, it selectively combines the optimal syntax for each database, such as applying the 'TO_CHAR' function in an Oracle environment, calling the 'CAST' function in a MySQL environment, and applying the 'TRY_TO_NUMBER' function in a Snowflake environment. Furthermore, to minimize system load that may occur when the generated SQL script is executed, the processor creates an optimized execution structure that utilizes indexes or tunes the execution plan to prevent unnecessary full-table scans. Through this, the present invention not only accelerates the speed of data integration but also simultaneously achieves economic benefits by preventing the waste of cloud resources and reducing operating costs.

[0033] Figure 5 illustrates the process in which the system interacts with the user, continuously evolves, and responds to changes.

[0034] As shown in FIG. 5, the present invention includes a self-learning feedback loop and a self-healing mechanism.

[0035] The processor updates the model's weights based on the Proximal Policy Optimization (PPO) reinforcement learning algorithm. It enables the poetry to learn human intent by granting a positive reward when the user accepts the AI's mapping proposal and a negative penalty when they manually correct it. Through this reinforcement learning cycle, domain-specific accuracy increases as usage time grows.

[0036] In addition, the processor utilizes Change Data Capture (CDC) technology to immediately detect schema drift, such as the addition or modification of columns in the source database, based on logs. When a schema change is detected, the processor performs a self-healing function that prevents pipeline interruptions and ensures data flow continuity without administrator intervention by automatically recalculating mappings and updating SQL through an AI engine to deploy new queries.

[0037] According to the present invention, through AI-based automation, it provides an environment that eliminates simple, repetitive tasks for data engineers and allows them to focus on high-value-added tasks, and possesses excellent industrial applicability that reduces implementation time by 80% in line with the growth of the global ETL market.

[0039] In addition, a processor according to one embodiment of the present invention may further include the following features.

[0040] The processor identifies the privacy protection level of the column based on the generated integrated semantic vector, and if the identified protection level is determined to be a high-risk column exceeding a threshold, controls the Seq2Seq AI model to automatically insert a function to encrypt or mask the data of the column when generating the dynamic SQL script. In this process, the determination of sensitivity and protection level, and the calculation of the threshold, are performed through the following sophisticated numerical model and control procedure.

[0041] The processor calculates sensitivity scores for individual columns by combining the results of metadata context analysis through a BERT model and the results of actual data pattern analysis through a KNN classifier. Specifically, the processor defines base sensitivity by calculating the distance between the extracted integrated semantic vector (V_{total}) and a predefined set of personal information reference vectors (V_{ref}).

[0042] The sensitivity score (S) is calculated by the following [Equation 1].

[0043] [Mathematical Formula 1]

[0044]

[0045] The first term, α·CosineSim(V_{m}, V_{pii}), is an indicator that measures the semantic similarity of metadata. Here, V_{m} is a context vector generated by passing a source column name (e.g., 'ADDR\_01') through a BERT model, and V_{pii} is a vector of standard identifiers predefined for the concept of 'Address' [cite: 587, 588]. Cosine similarity (CosineSim) measures similarity using the angle (θ) between two vectors, and the formula is as follows.

[0046]

[0047] This operation is controlled to assign a high score if the column names belong to the same semantic category, such as 'Address', 'ADDR', etc., even if the spelling does not perfectly match.

[0048] The second term, β·PatternMatch(D, P_{pii}), is a probability score based on the shape of the actual data. D represents the distribution of the extracted sample data, and P_{pii} is the personal information pattern dataset referenced by the KNN classifier. The PatternMatch function outputs a probability value (between 0 and 1) calculated via the KNN algorithm regarding how similar the input data D is to a specific personal information pattern (e.g., phone number, resident registration number, etc.). This enables the identification of sensitivity based on the content of the actual data, even when the column name is set to a meaningless name such as 'COL_1'.

[0049] The above processor, depending on the characteristics of the analysis environment, has a weighting coefficient α and It dynamically adjusts . In systems with strict metadata management, the context analysis weight α is set high, and in manually built unstructured systems, the data pattern weight is The judgment reliability is maintained by setting it high. An execution example in which specific technical values ​​are input into the above [Equation 1] is described in detail as follows. Assume a data processing environment where a specific column name in the source database is 'USER_IDENT' and the actual sampled data is in the form of '010-1234-5678'.

[0050] Assume that the processor calculates the cosine similarity between the vector V_m, generated by encoding the text 'USER_IDENT' with a BERT model, and the standard 'phone number' identifier vector V_{pii}, and that the result is calculated as 0.85. At the same time, the KNN classifier of the processor analyzes the sample data '010-1234-5678' and calculates the probability of matching with the previously stored Korean mobile number system pattern as 0.98. At this time, if the weight coefficients are assigned as α = 0.4 and β = 0.6 according to the system settings, the final sensitivity score S is determined as 0.928 (92.8%) through the calculation process of 0.4 * 0.85 + 0.6 * 0.98.

[0051] The processor compares the quantified sensitivity score in this manner with a pre-set security threshold in real time, and if the calculated score is determined to be in a high-risk group exceeding the threshold, it definitively classifies the corresponding column as personal information. Subsequently, the processor controls a Seq2Seq AI model to automatically insert encryption or masking functions into the corresponding column during the dynamic SQL script generation phase, thereby fundamentally preventing security incidents that may occur during the heterogeneous data integration process. This series of numerical model-based control procedures is a key feature of the present invention that enables autonomous security control based on clear technical grounds, even in ambiguous data environments.

[0052] Furthermore, the processor performs an advanced numerical control process that goes beyond simple morphological similarity analysis to quantitatively analyze the statistical sparsity of individual records subject to data processing, thereby determining the optimal protection level based on the risk of re-identification. The processor conducts an exhaustive survey of the frequency of specific data values ​​within a sample data set extracted from a source database; it determines that the stronger the unique attribute of the data, the higher the likelihood that the corresponding column functions as a quasi-identifier capable of identifying a specific individual, and dynamically adjusts the protection strength upward.

[0053] The risk of re-identification (R) is evaluated through [Equation 2], which is a key calculation formula for calculating the risk of re-identification (R). Equation 2 is a statistical index of the sensitivity of the data that combines the specificity of individual data with the information entropy of the entire sample group.

[0054] [Mathematical Formula 2]

[0055]

[0056] The first term, 1 / {Freq(x_i)}, represents the 'inverse frequency index' for a specific data value x_i. Here, Freq(x_i) is the number of times the corresponding data value appears within the entire sample dataset; as the frequency of occurrence in the denominator decreases—that is, as the data becomes sparser—the result of this term increases exponentially. This implies that in the case of a unique value appearing only once within the dataset, the inverse frequency index reaches its maximum value of 1, acting as a factor that maximizes the risk of re-identification.

[0057] The second term, ∑_{i=1}^{N}, is an operation that sums the inverse frequency indices of each record for the total number of extracted samples N, and derives the average 'specificity density' of the entire column through the 1 / N operation, which divides this by the total number N.

[0058] The last term, log_2(N), is a normalization coefficient derived from the concept of maximum entropy in information theory, and serves to correct the weight of identifiability that may occur as the size (N) of the sample group increases on a logarithmic scale. Consequently, the processor determines that the higher the R value calculated by multiplying all these terms, the more unique characteristics the data in the corresponding column contains without overlap with one another, and accordingly controls the strengthening of the masking strength or the forced insertion of an aggregate function to guarantee K-anonymity.

[0059] A specific example of execution in which actual technical figures are entered into the above [Mathematical Formula 2] is as follows.

[0060] Assume an environment where the total number of sample data (N) subject to data processing is 1,000. If the column to be analyzed is 'Residence Area', and the data value 'Seoul' (x_i) appears a total of 200 times, the inverse frequency index of that value is calculated to be very low at 1 / 200 = 0.005. On the other hand, if the column to be analyzed is 'Vehicle Number' and a specific license plate value (x_j) appears only once, the inverse frequency index of that value becomes the maximum at 1 / 1 = 1.0.

[0061] Assuming that all data in the 'Vehicle Number' column consists of unique values, the calculation process for the re-identification risk (R) is as follows.

[0062]

[0063] Conversely, for the 'Residence Area' column where all data is identical as 'Seoul' (Freq=1000), the risk is calculated as follows.

[0064]

[0065] The processor compares the calculated risk indices of 9.965 (high risk) and 0.009 (low risk) to control the application of a high-strength anonymization algorithm to the vehicle number column and the performance of a standard transfer procedure to the residential area column. This numerical-based determination of protection levels serves as a technical basis that overcomes the limitations of simple keyword matching methods and mathematically proves and responds to the actual data exposure risk.

[0066] In addition, the processor passes the calculated R value as an input variable to the PPO reinforcement learning algorithm, and is controlled to maintain an optimized security policy by autonomously relearning the threshold and weights in the event of a re-identification incident in an actual operating environment or an additional security request from an administrator.

[0067] Furthermore, the processor calculates and determines the threshold serving as a criterion for determining whether to perform protection measures not as a simple fixed constant, but as an adaptive dynamic threshold (T_{final}) that reflects the legal regulatory environment of the physical path through which the data travels in real time. This calculation of the dynamic threshold is a core control procedure of the present invention that mathematically quantifies the geographical locations of the source and target where data integration takes place, as well as the strength of data protection regulations in the respective countries, and reflects this in the security logic.

[0068] The processor determines the final decision threshold (T_{final}) using [Equation 3] below.

[0069] [Mathematical Formula 3]

[0070]

[0071] The first term, T_{base}, is a base threshold set during the system design phase, representing the basic sensitivity value at which the security device begins to operate in a general data environment where no legal regulations exist. The processor typically defines this value as a real number between 0 and 1 (e.g., 0.70), and serves as an initial setting value where security checks are relaxed as the value is higher and tightened as the value is lower.

[0072] ∑W_{comp} is the sum of the cumulative weights of global compliances that the data processing task must adhere to. The processor refers to a legal regulation mapping table stored in memory to extract individual weights (W_{comp}) defined according to the types of regulations of the regions through which the data passes (e.g., European GDPR, US CCPA, Korean Personal Information Protection Act, etc.) and sums them. The denominator, 100, is a normalization constant used to convert the sum of the regulatory weights into a percentage and a correction factor between 0 and 1.

[0073] The second term, the operation (1 - …), acts as the 'security sensitivity amplification factor,' which is the technical core of this formula. As the sum of legal regulatory weights, ∑W_{comp}, increases, the total result inside the parentheses decreases; consequently, this causes the final threshold, T_{final}, to converge to a value lower than the original T_{base}. A lower threshold implies the technical meaning that even data columns with minute sensitivities (S) that were previously not classified as security targets are now all captured as security targets.

[0074] A specific example of execution in which actual technical figures are input into the above [Equation 3] is as follows. It is assumed that a data processing operation environment is set so that the data migration path is from 'Korea' to 'European Union (EU)'. Assuming that the base threshold (T_{base}) based on the initial settings of the processor is 0.80 (80%), the compliance weight (W_{comp}) can be calculated as follows. First, W_{comp1} = 30 is assigned to the EU-GDPR, which is Compliance 1, as the regulatory intensity is very high, and W_{comp2} = 15 is assigned to the Domestic Personal Information Protection Act, which is Compliance 2, according to the strengthened regulatory requirements. In this case, the cumulative weight sum (∑W_{comp}) is calculated as 30 + 15 = 45. By substituting these actual values ​​into [Equation 3] to calculate the final dynamic threshold, T_{final} = 0.80 * (1 - 45 / 100) = 0.80 * 0.55 = 0.44. The processor establishes the calculated final threshold of 0.44 (44%) as the security judgment criterion for the corresponding task. If the semantic sensitivity score (S) of a data column is measured as 0.50, a security function would not have been inserted in an unregulated environment (T_{base}=0.80); however, since it exceeds the threshold (T_{final}=0.44) lowered by this adaptive control logic, automatic masking is performed immediately. Through this series of procedures, the processor autonomously incorporates security requirements by country and regulation into the design phase of the data pipeline, which serves as the most precise numerical basis for determining whether to insert a security function into the SQL query generated by the Seq2Seq AI model.

[0075] The processor activates an AI-based dynamic query generation process if, as a result of comparing and analyzing the protection level determined based on the calculated sensitivity score (S) and re-identification risk (R) with the final judgment threshold (T_{final}) derived by reflecting the legal regulations of the loading region, the column is determined to belong to the 'high-risk' group. Specifically, the processor constructs an input sequence by dynamically adding not only simple mapping information but also a special tag (Security Attribute Tag) defining the security attributes of the column to the encoder input of the Seq2Seq AI model. The security attribute tag is, for example<MASK_HIGH> ,<ENCRYPT_AES256> ,<K-ANONYMITY_3> It can be implemented in the form of a token that specifies the type and strength of de-identification, such as, and the encoder converts this tag information into a numerical vector and includes it in the context vector.

[0076] The decoder of the Seq2Seq model, which receives the context vector generated by the encoder, is controlled by the processor to generate a masking statement optimized for the system environment of the target database by interacting in real-time with the dialect optimization module. If the processor identifies the target database as Oracle, it generates a query that asterisks (*) or transforms only specific parts of the data using the REGEXP_REPLACE function, which supports complex regular expressions. On the other hand, if the target database is MySQL or MariaDB, it designs the query structure to perform efficient masking operations by replacing strings at specific locations using the INSERT function or by combining the REPLACE function. Furthermore, in cloud data warehouse environments such as Snowflake, the processor performs dialect optimization that reflects the unique security libraries and syntactic characteristics provided by each database engine, such as directly defining the MASKING POLICY or inserting a one-way encryption statement using the SHA2 function.

[0077] This series of processes is characterized by being performed autonomously without user intervention based on the semantic analysis results of the processor. That is, the processor identifies the semantic nature of the data to recognize security threats and automatically generates and distributes executable code to respond to them, thereby fundamentally preventing the occurrence of security blind spots caused by human intervention. After the data migration work is completed, the processor retrospectively runs a Proximal Policy Optimization (PPO) reinforcement learning algorithm to continuously enhance the level of intelligence of the system. The processor collects user feedback on the generated masking results, grants a positive reward if the user performs an 'Accept' action to accept the AI's security suggestion as is, and generates a negative penalty if the user performs a 'Correction' action to manually change the masking range or method.

[0078] The processor executes a policy update based on the reward value calculated through the PPO algorithm, thereby optimizing the context analysis weight (α) and pattern analysis weight (β), which are the weight coefficients of the aforementioned mathematical formula 1, in real time. For example, if feedback accumulates indicating that patterns in actual data are more effective than column names (metadata) for identifying personal information in a specific domain, the processor gradually increases the value of β to strengthen the influence of pattern analysis, thereby improving the accuracy of security judgments in future data processing tasks. This self-learning feedback loop forms a domain-specific security model that is perfectly optimized for the data characteristics of the relevant company or institution over time, serving as the technical basis for the system to fully comply with security compliance on its own without the need for repetitive configuration work by an administrator. Consequently, the present invention dramatically improves the speed of data integration while simultaneously realizing a next-generation ETL environment with maximized security through the autonomous judgment of artificial intelligence and precision enhancement based on reinforcement learning.

[0080] The processor combines and analyzes the data complexity derived from the integrated semantic vector and the system's available resources in real time to calculate an optimal processing efficiency indicator for minimizing the completion time of the entire data processing task.

[0081] The above processor calculates the actual processing load of the system using not only the simple amount of data (D_v) but also the semantic complexity of the data (C_s) calculated through the semantic profiling above as a variable. As the semantic complexity increases (e.g., unstructured text, multiple join structure, etc.), the processing speed decreases relative to the same resource, so the following [Equation 4] is used as an indicator to compensate for this.

[0082] [Mathematical Formula 4]

[0083]

[0084] To describe in detail the equation E_{proc} = {D_v·√{C_s}} / {R_a·η_{agent}}, this formula quantifies the performance-to-resource ratio by using the 'actual load' that the system must handle as the numerator and the 'available processing capacity' possessed by the system as the denominator. D_v, included in the numerator, represents the total number of records to be processed or the total data volume, while C_s is an index that quantifies the column-specific entropy derived through BERT and KNN analysis in the previous step, as well as the complexity of data transformation operations (Join, regular expression processing, AI inference, etc.). Here, the application of the square root (√{·}}) operation to C_s is intended to stably reflect the characteristic of non-linear computational load increase as data complexity increases within the control logic, and to serve as a damping factor to prevent excessive resource expansion. R_a in the denominator term represents the total number of agent modules (Resource Count) that are currently assigned to and activated for data processing tasks, and η_{agent} is a standard processing performance coefficient per unit time of an individual agent that is predefined considering hardware specifications and network bandwidth.

[0085] The following is a specific example of execution where actual technical figures are entered into [Mathematical Formula 4].

[0086] In the first scenario, we assume a data processing task environment that transfers 1,000,000 records of simple log data. In this case, the data volume (D_v) is 1,000,000, and since it is a simple transfer, the semantic complexity exponent (C_s) is calculated as 1. Currently, 2 agents (R_a = 2) are in operation, and if the performance coefficient (η_{agent}) of an individual agent is 10,000, the data processing efficiency (E_{proc}) is calculated as {1,000,000·√{1}} / {2·10,000} = 50. The processor determines that this value is within the system performance maintenance range and controls the task to be performed without expanding additional resources.

[0087] In the second scenario, we assume a high-value-added data processing environment that processes 1,000,000 data records, but includes complex multi-joins and AI-based personal information masking operations. In this case, it is assumed that the semantic complexity index (C_s) increases dramatically to 16. Under the same resources (R_a = 2, η_{agent} = 10,000), the efficiency metric is calculated as {1,000,000·√{16}} / {2·10,000} = {4,000,000} / {20,000} = 200. The processor determines that the calculated efficiency metric of 200 significantly exceeds a set performance threshold (e.g., 100) and controls the number of agents (R_a) to be immediately expanded by activating the auto-scaling function to prevent delays in job completion time.

[0088] The processor inputs the calculated E_{proc} metric into a real-time feedback loop and optimizes resource allocation based on the C_s value, which changes in real-time even during task execution. Furthermore, the processor performs adaptive control to ensure memory availability by lowering the fetch size that the agent module reads from the source DB at once when the actual load of the numerator term is high, and to maximize overall processing speed by raising it when the load is low. This numerical model-based autonomous optimization procedure is a key technical achievement of the present invention that guarantees the cost efficiency of cloud infrastructure without human intervention while significantly improving the stability of large-scale data integration tasks.

[0089] The processor utilizes the data processing efficiency (E_{proc}) metric, calculated in real time by [Equation 4], as a key parameter for system resource management, and operates an autonomous optimization control loop to precisely adjust the quantity of agent modules and the load of individual tasks based on this. The processor constantly monitors whether the calculated E_{proc} value exceeds a predefined performance maintenance threshold range, and if the metric exceeds an upper threshold and a system bottleneck is predicted, it controls the processing capacity to be immediately expanded by issuing an Auto Scale command to the agent modules.

[0090] As a specific resource scaling control procedure, the processor calculates the additional computing power required when E_{proc} exceeds an upper threshold and provisions new agent server instances by integrating with a Kubernetes or Docker-based orchestration engine. During this process, as the value of R_a (number of active agents) in the denominator increases, the overall E_{proc} figure converges back into a stable range. Conversely, if the workload decreases and the E_{proc} metric remains below a lower threshold for an extended period, the processor determines that the current system is in a state of resource overload. It then controls the system to minimize cloud infrastructure operating costs by identifying idle or low-load agent nodes and stopping and reclaiming them.

[0091] A specific numerical example of such resource allocation control is as follows. Assume an environment where a large-scale data processing task is allocated with a total data throughput (D_v) of 10,000,000 and a semantic complexity (C_s) of 25. If there are 4 agents currently running (R_a = 4) and the performance coefficient (η_{agent}) of each agent is 10,000, the initial efficiency index E_{proc} according to [Equation 4] is calculated as (10,000,000 * √{25}) / (4 * 10,000) = 50,000,000 / 40,000 = 1,250. If the appropriate performance threshold of the system is set to 500, the processor determines that the current load exceeds the processing capacity by 2.5 times and executes a control command to immediately increase the number of agents (R_a) to 10 or more and lower the E_{proc} value to 500 or less.

[0092] In addition, the processor achieves throughput optimization and memory safety simultaneously by adaptively adjusting the fetch size, which is the unit of records that individual agents read from the source database at once, as well as the increase or decrease of instances. The processor predicts that if the semantic complexity (C_s) of the numerator term in the operation process of [Equation 4] is measured to be higher than a certain level, the memory occupancy and CPU occupancy time required for processing per record will be high. Accordingly, the processor reduces the fetch size of the agent module (e.g., from 5,000 records to 500 records) to prevent out-of-memory overflow accidents caused by insufficient heap memory.

[0093] Conversely, when processing data columns that have a simple structure and low computational load resulting in a low C_s value, the processor is controlled to maximize the fetch size (e.g., expanded to over 10,000 records), thereby reducing the number of network round-trips with the source DB and maximizing data loading speed. This precise control model based on numerical metrics enables self-optimization, allowing the system to tune its performance in response to changes in the external environment. Consequently, this serves as a strong technical basis for achieving an execution performance improvement of approximately 80% or more compared to existing rule-based ETL systems.

[0095] The processor analyzes the complexity of the data type derived from the integrated semantic vector and the total number of records included in the metadata of the source database to calculate the expected load of the corresponding data processing task (hereinafter referred to as 'data processing task' or 'Job'), and controls the auto-scale function to preemptively expand (pre-scaling) the number of instances of the agent module before the actual data processing task is executed based on the calculated expected load. Specifically, before the data processing task starts, the processor collects the total number of records (N) and data type information for each column from the system catalog or metadata repository of the source database, and calculates [Equation 5] to predict the system resource occupancy based on the collected information.

[0096] [Mathematical Formula 5]

[0097]

[0098] In the above [Equation 5], L_{exp} represents the expected load, and N is the total number of records to be extracted from the source database. W_{type} is a processing complexity weight for each data type identified during the semantic profiling phase, and is set to have a higher value as it includes large objects (LOBs) with complex structures or columns requiring regular expression conversion, compared to simple numeric types or strings. If the processor determines that the calculated expected load (L_{exp}) will exceed the processing capacity of the currently active agent module, it sends a control signal to the Kubernetes or Docker engine before actual data migration begins to perform preemptive resource allocation by securing the necessary number of instances in advance, thereby fundamentally preventing delays that occur during data processing.

[0099] Furthermore, the processor monitors the statistical distribution of actual data flowing in through the KNN classifier in real time to detect data drift, where the amount of change relative to the distribution of existing training data exceeds a set tolerance. Data drift refers to a phenomenon where the nature or value of data deviates from the existing statistical range due to changes in the operating environment of the source system; if left unchecked, this can lead to a decrease in the accuracy of semantic vector generation and mapping. As soon as data drift is detected, the processor forcibly triggers a policy update of the PPO (Proximal Policy Optimization) reinforcement learning algorithm, thereby controlling the integrated semantic vector generation model to be retrained to match the latest data distribution. Through this control procedure, the system autonomously maintains the validity of the analysis model and guarantees advanced mapping performance without separate administrator intervention, even if data characteristics change over time.

[0100] Furthermore, to verify whether the generated dynamic SQL script has logical defects, the processor creates virtual test data (synthetic data) based on mapping relationships inferred by the intelligent mapping engine, rather than actual data. This virtual test data is generated to perfectly mimic the schema structure of the source and target databases while strictly adhering to the boundary values ​​or data types of each column derived from semantic profiling. The processor applies the generated dynamic SQL script to the test data for execution and automatically performs a unit test to compare and verify the output result set with the predicted result values ​​logically derived by the intelligent mapping engine. If a discrepancy is detected between the two values, the processor determines that there is a defect in a specific syntax of the SQL script and activates self-healing logic to reconstruct the query or send a notification to the administrator, thereby preventing accidents where defective queries are executed in the actual production database and compromise data integrity.

[0102] The processor identifies country information or service target region information where the target database is physically located, refers to a global compliance database stored in memory to extract legal masking standard specifications for identifiers such as names, addresses, and resident registration numbers required by data protection regulations (e.g., GDPR, CCPA, personal information protection laws, etc.) of the identified region, and maps the semantic schema profiling results with the legal masking standard specifications to control the generation of the dynamic SQL script containing a masking function optimized for regional regulatory requirements.

[0103] The processor applies the generated dynamic SQL script to sample data in the source database to generate a virtual result dataset, calculates a re-identification risk that can infer a specific individual through combinations of anonymized columns within the virtual result dataset, and if the calculated re-identification risk exceeds a preset safety threshold, controls the query to be reconstructed by additionally inserting an aggregation or generalization function that guarantees K-anonymity or L-diversity into the SQL script instead of simple masking.

[0104] In this process, the processor calculates an intelligent de-identification necessity index (hereinafter referred to as 'DNI') by integrating numerical indicators calculated in previous steps to finally determine whether to apply advanced de-identification technology, and this is performed by [Equation 6] below.

[0105] [Mathematical Formula 6]

[0106]

[0107] In the above [Equation 6], S is the sensitivity score of the corresponding column calculated by the above [Equation 1], R is the re-identification risk of the corresponding sample dataset calculated by the above [Equation 2], and T_{final} is the dynamic threshold based on the corresponding regional regulation determined by the above [Equation 3]. The processor determines that if the product of the numerator term, sensitivity (S) and statistical risk (R), exceeds the denominator term, legally permissible threshold (T_{final}), and the DNI value exceeds 1.0, it cannot satisfy legal compliance with masking techniques that simply obscure part of the data.

[0108] To elaborate on the specific control procedure performed by the processor, the processor first identifies the IP address of the target server or location information included in the connection profile and retrieves the level of anonymization required by the country from the global compliance database in real time. The primary dynamic SQL script generated according to the extracted standard specifications is converted into a virtual result dataset having statistical characteristics similar to the actual environment, and the processor simulates a multidimensional attack scenario (e.g., combination attack) on the virtual result dataset to re-evaluate the possibility of personal identification through the combination of data.

[0109] If the re-identification risk (R) calculated at this time is very high, or if multiple columns with extremely high sensitivity scores (S) are included and the result value (DNI) of [Equation 6] above exceeds the threshold level of 1.0, the processor restarts the Seq2Seq AI model to fundamentally change the structure of the query. Specifically, it enforces a K-anonymity structure by inserting a categorization function that groups specific ages into age groups such as '20s' or '30s' instead of precise numerical values, or by reflecting an aggregate function in dynamic SQL that limits detailed addresses to the 'city / county / district' level. This control process numerically synchronizes the legal requirements of the target region with the risk level of the actual data, providing an advanced security control measure that maintains the efficiency of data integration while autonomously mitigating the risk of global regulatory violations.

[0110] Furthermore, the processor applies the execution result of the query to which these advanced de-identification measures have been applied back to [Equation 2] to finally verify whether the re-identification risk (R) has dropped within a safe range, and transmits only the optimal query that has been verified to the NF agent module to control the actual data implementation operation.

[0112] The processor can control the dynamic SQL generator to prevent out-of-memory overflow of the agent module in advance by analyzing the inclusion and frequency of large objects (LOBs) or complex string columns included in the source data through the integrated semantic vector to calculate the estimated memory occupancy per individual record, and variably determining the unit data extraction count (Fetch Size) of the dynamic SQL script inversely proportional to the calculated estimated memory occupancy.

[0113] The processor analyzes the type of data transformation operation derived through the semantic schema profiling to determine whether the job is a CPU-intensive operation (AI inference, complex formula processing, etc.) or an I / O-intensive operation (large-scale join, simple transfer, etc.), and, according to the determined operation characteristics, controls the preferential allocation of the job to a specific node among a plurality of agent nodes that possesses a hardware accelerator (GPU) or high-speed I / O device optimized for the operation.

[0115] The processor calculates the estimated memory occupancy (M_{rec}) per individual record by precisely analyzing the presence and frequency of large objects (LOBs) or complex string columns included in the source data through the integrated semantic vector. Specifically, the processor calculates the distribution probability of columns exceeding a threshold length among binary objects such as BLOBs (Binary Large Objects) and CLOBs (Character Large Objects), or variable-length strings (VARCHAR), by comparing the schema information of the source database with the sample data. Based on the calculated estimated memory occupancy, the processor variably determines the unit data extraction count (Fetch Size) of the dynamic SQL script so as not to exceed the available physical memory resources of the agent module, thereby ensuring system stability. At this time, the following [Equation 7] is utilized to quantitatively calculate the optimal unit data extraction count.

[0116] [Mathematical Formula 7]

[0117]

[0118] In the above [Equation 7], FS_{opt} is the Optimal Fetch Size, and Ω is the available memory capacity allocated to the agent module and immediately available for use. M_{rec} is the estimated memory occupancy per individual record calculated by the processor, and C_s is the semantic complexity index used in the operation of [Equation 4] in the previous step. For data processing tasks where the semantic complexity (C_s) is high and the computational load is large or the physical size of individual records (M_{rec}) is huge, the processor controls the dynamic SQL generator to proportionally reduce the number of extractions to prevent memory overflow (Out-Of-Memory) accidents that may occur in the agent module.

[0119] Furthermore, the processor analyzes the types of data transformation operations and their sequential flow derived from the semantic schema profiling to determine the operational characteristics of the corresponding data processing job. The processor exhaustively examines the attributes of individual components included in the job model and classifies the job as CPU-intensive if it involves AI-based semantic analysis logic, complex statistical formula processing, or large-scale encryption / decryption operations. Conversely, if the job primarily consists of join operations between large tables exceeding hundreds of millions of records, filtering of columns without indexes, or mass log migration to the file system, it is classified as I / O-intensive.

[0120] Based on the determination result, the processor controls the preferential allocation of the data processing task to a specific node among the multiple agent nodes registered with the NF server that possesses hardware resources optimized for the corresponding computational characteristics. The processor checks the hardware profile of each agent node in real time and, in the case of a task determined to be CPU-intensive, distributes the task to a specific agent node equipped with a hardware accelerator (GPU) or a high-clock multi-core processor. Conversely, in the case of a task determined to be I / O-intensive, it minimizes bottlenecks in the entire data integration pipeline by allocating the task to a node that exclusively secures high-speed I / O devices (such as NVMe SSDs) or broadband network channels.

[0121] Unlike the existing round-robin method, which simply considers the idle state of agents, this resource-aware job scheduling procedure provides the effect of maximizing processing performance in the process of integrating large-scale heterogeneous data by mathematically matching the semantic complexity of the data with the physical characteristics of the infrastructure. As a result, the processor combines securing memory stability according to [Equation 7] and strategic node placement based on computational characteristics to build an intelligent data integration environment that maintains optimal performance without manual intervention by an administrator.

[0123] The processor identifies a defect point where the execution result of the dynamic SQL script and the predicted value of the intelligent mapping engine are inconsistent based on the unit test results, determines whether the identified defect point is a syntax error resulting from dialect optimization or a mapping inference error, and controls the regeneration (self-correction) of a corrected SQL script with the defect removed by adjusting the input parameters of the Seq2Seq AI model or changing the optimization rules of the template engine according to the determination result. During the unit test process, the processor inputs sample data from the source database into the dynamic SQL and conducts an exhaustive examination to check whether the structure and data format of the actual output result set match the schema of the expected result set predefined by the intelligent mapping engine through semantic analysis. At this time, if the query execution itself fails due to the use of specific reserved words in the target database or violations of function syntax, it classifies this as a syntax error and immediately corrects the dialect mapping rules of the template engine. On the other hand, if the query executes normally but data inconsistency occurs between the source and target columns, this is classified as a mapping inference error, and a virtuous cycle control structure is formed to regenerate a query with the defects removed by readjusting the attention weights or input embedding parameters of the aforementioned Seq2Seq AI model.

[0124] The processor calculates the success rate and data coverage of the unit tests for multiple test cases, and if the calculated success rate falls short of a preset target value, it automatically blocks the deployment of the dynamic SQL script to the actual operating environment and controls the NF designer to notify the administrator of the failed test cases and the expected causes of query defects by visualizing them. Here, data coverage refers to the ratio of verified columns and records within the entire data pipeline, and the processor ensures integrity in the operating environment by granting execution rights to the agent module only when both the success rate and coverage satisfy critical performance requirements.

[0125] The processor converts the success or failure of the unit test into an internal reward value of the PPO reinforcement learning algorithm and controls the intelligent mapping engine and the weights of the Seq2Seq AI model to be independently updated based on the internal reward value even in the absence of direct user feedback. To quantitatively manage this autonomous learning system, the processor calculates a System Reliability Index (hereinafter referred to as 'SRI') representing the overall reliability level of the system, which is performed by [Equation 8] below.

[0126] [Mathematical Formula 8]

[0127]

[0128] In the above [Equation 8], Success Rate and Coverage are the success probability and data verification range derived through the unit test, and DNI is the intelligent de-identification index calculated by the above [Equation 6]. The processor calculates a higher SRI value as the test completeness of the numerator term is high and the data risk (DNI) of the denominator term is low, which serves as an indicator of how safely and accurately the system is performing the current data processing task.

[0129] A specific example of execution in which actual technical figures are input into [Equation 8] is as follows. It is assumed that in a data processing task involving a specific personal information column, the success rate of the unit test performed by the processor is 0.98 (98%) and the verification coverage is 0.90 (90%). At the same time, it is assumed that the DNI value reflecting the de-identification complexity and risk of the task is calculated as 0.50 by [Equation 6].

[0130] By substituting these actual values ​​into [Equation 8], the integrated reliability index is calculated as follows.

[0131]

[0132] The processor compares the calculated SRI value of 1.764 with an internal confidence threshold (e.g., 1.50) of the system. If the SRI value exceeds the threshold, the processor determines that the current mapping and query generation logic is very stable, replaces the corresponding success with a strong static reward of PPO reinforcement learning, and solidifies the model weights. Conversely, if the Data Risk (DNI) spikes and the SRI value drops below the threshold, the processor considers there to be a potential defect even if the test success rate is high, and controls the model to strengthen self-correction logic or increase the model's exploration probability to explore a new mapping path.

[0133] The embodiments disclosed in this specification and drawings are merely examples to aid in understanding the technology and do not limit the scope of the invention. It is obvious to those skilled in the art that combinations between variations and embodiments based on the technical concept of the invention are possible. For example, some components of each embodiment may be combined with one another to be implemented by the system, server, or terminal of the invention.

[0134] The device and control method according to the present invention may be realized in hardware, software, or a combination thereof. In particular, a method for controlling a system may be implemented in the form of program instructions recorded on a computer-readable medium, said medium including ROM, RAM, CD-ROM, magnetic tape, optical data storage device, etc. Additionally, the program code may be distributed, stored, and executed on a networked computer system, and equivalents that are electronically transmitted through a medium such as a communication signal are also included within the scope of the present invention.

[0135] The method of the present invention may be implemented by a computer or a portable terminal comprising a control unit and a memory, and said memory is an example of a machine-readable storage medium suitable for storing instructions implementing the present invention. Accordingly, the true technical scope of protection of the present invention should be determined by the claims set forth below and their equivalents, rather than by the exemplary embodiments described above.

Claims

Claim 1 An intelligent ETL (Extract, Transform, Load) processing system for performing data integration between heterogeneous databases, comprising: a memory for storing data; a processor for executing a program stored in said memory; and an agent module controlled by said processor for performing actual data implementation in a distributed environment;The processor includes, wherein the processor extracts metadata and actual data from a source database to perform semantic schema profiling, and during the semantic schema profiling, generates an integrated semantic vector by combining metadata analysis that converts the contextual meaning of column names into embedding vectors using a BERT (Bidirectional Encoder Representations from Transformers) model and pattern analysis that classifies data types based on the distribution of actual data using a KNN (K-Nearest Neighbors) classifier, and defines a series of processes for transferring data from the source database to a target database as a data processing task, and the processor drives an intelligent mapping engine that calculates the cosine similarity between the generated integrated semantic vectors and infers mapping relationships between the source and target databases by referencing a Knowledge Graph, translates the inferred mapping relationships into executable SQL code through a Seq2Seq AI model, generates a dynamic SQL script by performing Dialect Optimization that adjusts query syntax according to the type of target database, and schema including the addition or modification of columns in the source database An intelligent ETL processing system configured to perform a self-healing function that ensures the continuity of the data pipeline by detecting schema drift in real time, and when schema drift is detected, recalculating mapping relationships through the intelligent mapping engine and updating and distributing the dynamic SQL script. Claim 2 delete Claim 3 In claim 1, the processor applies the generated dynamic SQL script to sample data in a source database to generate a virtual result dataset, calculates a re-identification risk capable of inferring a specific individual through combinations of anonymized columns within the virtual result dataset, and if the calculated re-identification risk exceeds a preset safety threshold, controls the query to be reconstructed by additionally inserting an aggregation or generalization function that guarantees K-anonymity or L-diversity into the SQL script instead of simple masking, and the processor checks the access level of the execution entity calling or executing the data processing operation in real time, and variably determines the anonymization strength even for the same personal information column based on the checked access level, wherein the dynamic SQL script with branching logic (such as a CASE-WHEN clause) inserted to perform full masking for low-authority users and enable partial masking or original querying for high-authority administrators An intelligent ETL processing system characterized by controlling to generate, wherein the processor calculates the amount of information (Entropy) according to the data distribution of the corresponding column from the integrated semantic vector, and assigns masking weights to automatically expand the masking range for columns with high information entropy due to strong data unique attributes even if they are of the same type of personal information, thereby controlling the possibility of information leakage due to data scarcity. Claim 4 An intelligent ETL processing system according to paragraph 3, wherein the processor analyzes the inclusion and frequency of large objects (LOBs) or complex string columns included in the source data through the integrated semantic vector to calculate the estimated memory occupancy per individual record, variably determines the unit data extraction count (Fetch Size) of the dynamic SQL script inversely proportional to the calculated estimated memory occupancy, and the system supports an auto-scale function that automatically expands and contracts the resources of the agent module. Claim 5 An intelligent ETL processing system according to claim 4, wherein the processor extracts the data type, maximum / minimum length, and acceptable regular expression pattern of the corresponding column from the integrated semantic vector, and, based on the extracted information, automatically generates a multidimensional test dataset including not only normal range data but also boundary values, null values, and outliers of invalid format.

Images