An adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data linkage

Abstract

The application discloses a kind of based on multi-source heterogeneous data link's adaptive ETL data cleaning and integrated processing method, it is related to data integration technical field.Initial quality baseline is constructed by obtaining the metadata characteristics of multi-source heterogeneous data, periodically collect real-time statistical characteristics and generate offset vector compared with baseline, dynamically adjust cleaning strategy according to offset vector and trigger safety switching;Based on field semantic information and data flow distribution characteristics, candidate link relationship is constructed and is given confidence index, according to confidence index, linked data records are graded and routed and integrated and combined, and integrated quality evaluation is fed back to cleaning strategy, link verification information is fed back to candidate link relationship construction, form closed loop two-way feedback;The application realizes the adaptive adjustment of ETL cleaning strategy and the automatic discovery of heterogeneous data link.

Description

Technical Field

[0001] This invention relates to the field of data integration technology, specifically an adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links. Background Technology

[0002] ETL, as a core technology for building data warehouses and data lakes, is widely used in multi-source heterogeneous data integration scenarios in fields such as finance, e-commerce, energy, and government. Existing ETL technologies typically employ a combination of preset static cleaning rules and fixed link mappings in the cleaning and integration process of multi-source heterogeneous data. Cleaning and integration are considered complete when the data distribution drops to a preset threshold. However, directly applying this method to ETL processing of multi-source heterogeneous data presents the following technical bottlenecks:

[0003] The null value rate, numerical distribution, or data format of data sources frequently undergo abrupt changes during operation. Existing static cleaning rules cannot detect these real-time changes in statistical characteristics. When a rule becomes invalid, manual intervention is required to modify the configuration and restart the task. It cannot automatically trigger dynamic adjustments to the cleaning strategy based on real-time statistical characteristics of the data. The field link relationships between different data sources are maintained using static mapping tables. When the upstream table structure undergoes field renaming, addition, deletion, or type changes, the mapping cannot adapt automatically, and the ETL task directly reports an error and is interrupted. Manual reconfiguration and pipeline restart are required, making dynamic self-maintenance of link mapping impossible. The cleaning and integration adopt a serial independent architecture, completing single-source cleaning first and then merging multiple sources. The cleaning stage cannot use the correlation constraints between multiple source data to back-verify the rationality of the cleaning results, and data conflicts discovered in the integration stage cannot be fed back to the cleaning stage to trigger strategy correction. Decision errors in the two stages cannot form bidirectional feedback and collaborative verification. When there are conflicts in multi-source data, static priority is used to determine which data source to adopt, which cannot reflect the actual quality changes of each data source during operation. There is a lack of hierarchical processing mechanism for uncertain data with intermediate link confidence, which can only be forcibly merged or directly discarded, resulting in unreliable integration results or information loss. Summary of the Invention

[0004] The purpose of this invention is to provide an adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links, in order to solve the technical problems in the existing ETL process, such as the cleaning strategy being unable to perceive changes in data distribution, the heterogeneous data field linking relationship being unable to automatically adapt to changes in table structure, the cleaning and integration stages being independent and unable to form bidirectional feedback, and the lack of a hierarchical processing mechanism for intermediate confidence data.

[0005] The objective of this invention can be achieved through the following technical solutions:

[0006] This application provides an adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links, including the following steps:

[0007] Obtain metadata features from multi-source heterogeneous data and construct an initial quality baseline;

[0008] During the ETL task, the real-time statistical characteristics of the current data stream are periodically collected and compared with the initial quality baseline to generate an offset vector that characterizes the degree of data distribution shift.

[0009] Based on the offset vector, the execution parameters in the current cleaning strategy are dynamically adjusted and the data stream is cleaned; when the offset vector exceeds the preset allowable range, a safe switching process for the cleaning operator is triggered.

[0010] Based on the semantic information of the fields in the metadata features, and extracting the data flow distribution features from the real-time statistical features, candidate link relationships between multi-source heterogeneous data are constructed. Link matching is performed on multi-source heterogeneous data records according to the candidate link relationships, and a confidence index is assigned to each candidate link relationship.

[0011] Based on the confidence index, the linked data records are subjected to hierarchical routing processing and integration merging to generate integration quality evaluation information and link verification information. The integration quality evaluation information is used to update the cleaning strategy and feed it back to the step of dynamically adjusting the cleaning strategy. The link verification information is used to optimize the candidate link relationship and feed it back to the step of constructing the candidate link relationship.

[0012] The beneficial effects of this invention are as follows:

[0013] This invention solves the technical problem that static cleaning rules cannot perceive changes in data distribution and require manual task restart by constructing a multi-dimensional offset vector and dynamically adjusting the cleaning strategy and safe switching, thereby realizing automatic perception and lossless adaptive adjustment of the cleaning strategy.

[0014] By using a dual screening method based on semantic similarity and distribution similarity, and weighted by a confidence index, candidate link relationships are automatically constructed. This solves the technical problem that static mapping cannot adapt to field changes and ETL task interruptions, and enables automatic discovery and dynamic maintenance of link relationships.

[0015] By integrating quality evaluation feedback to cleaning strategy and link verification feedback to link construction, a two-way closed-loop feedback is formed, which solves the technical problems of cleaning and integration being serially independent and decision-making errors being unable to be collaboratively verified, and achieves collaborative optimization of the two stages.

[0016] By using confidence-driven three-level hierarchical routing and asynchronous supplementary verification, the technical problems of static priority failing to reflect changes in data source quality and intermediate data lacking hierarchical processing are solved, enabling differentiated integration and avoiding information loss. Attached Figure Description

[0017] To better understand and implement this application, the technical solution is described in detail below with reference to the accompanying drawings.

[0018] Figure 1 A schematic diagram of the overall process of the adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links provided in this application;

[0019] Figure 2 A schematic diagram of the dynamic adjustment and safe switching process of the offset vector-driven cleaning strategy provided in this application;

[0020] Figure 3 A schematic diagram illustrating the candidate link relationship construction, hierarchical routing, and bidirectional feedback process provided in this application. Detailed Implementation

[0021] To further illustrate the technical means and effects adopted by the present invention to achieve its intended purpose, exemplary embodiments will be described in detail below, examples of which are illustrated in the accompanying drawings. In the following description, when referring to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application.

[0022] The terminology used in this application is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. The singular forms “a,” “the,” and “the” used herein are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any and all possible combinations of one or more of the associated listed items.

[0023] The following detailed description of the specific implementation methods, features, and effects of the present invention, in conjunction with the accompanying drawings and preferred embodiments, is provided in detail.

[0024] Example 1

[0025] Please see Figures 1-3 This embodiment provides an adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links, including the following steps:

[0026] S1. Obtain metadata features of multi-source heterogeneous data and construct an initial quality baseline;

[0027] First, the controller connects to multiple heterogeneous data sources; these data sources include relational databases (such as MySQL, PostgreSQL), NoSQL databases (such as MongoDB, HBase), distributed file systems (such as HDFS), and message queues (such as Kafka); each data source contains several data tables, each data table contains several fields, and each field has metadata information such as field name, data type, field length, comment description, whether it is a primary key or foreign key, the identifier of the data source to which it belongs, and the name of the table or collection to which it belongs;

[0028] The controller iterates through the metadata storage of each data source and extracts the metadata features of each field through JDBC connection drivers, API interfaces, or metadata crawling. Specifically, for relational databases, the controller executes SQL queries to obtain field-level metadata; for NoSQL databases, the controller obtains field structure information through the database's native API; and for HDFS file systems, the controller obtains field definitions by reading the file header schema or external metadata storage.

[0029] The controller organizes the metadata features of all fields obtained in the above manner according to a preset data structure and stores them in the controller's metadata knowledge base. The metadata knowledge base is implemented using a graph database or a relational database. Each field is a node, recording its field name, data type, field length, comment description, data source identifier, and table name or collection name, etc. The nodes are connected through association relationships.

[0030] The controller constructs an initial quality baseline for each field based on the extracted metadata features; the construction process of the initial quality baseline is as follows:

[0031] For numerical fields, the controller collects sample data for that field within an initial time window. The length of the initial time window is preset by the user based on the business characteristics of the data source. In this embodiment, the most recent 7 days or the most recent 10,000 records are selected to ensure that the sample has sufficient statistical representativeness. The controller calculates the mean, standard deviation, maximum value, minimum value, null value rate, and unique value rate of the sample data, and uses these statistics as the initial baseline characteristics of the field. Taking a sales amount field "amount" as an example, the controller calculates its sample mean as 523.67, standard deviation as 89.23, maximum value as 1200.50, minimum value as 5.00, null value rate as 0.02%, and unique value rate as 89.5%.

[0032] For string fields, the controller collects sample data of the field within an initial time window and calculates the average length, maximum length, minimum length, null value rate, unique value rate, and common string pattern distribution of the sample data. The string pattern distribution is obtained by classifying and statistically analyzing strings using regular expressions, for example, matching mobile phone numbers as the "mobile phone number" pattern and email addresses as the "email address" pattern. Taking a user name field "user_name" as an example, the controller calculates its average length to be 5.2 characters, maximum length to be 12 characters, minimum length to be 2 characters, null value rate to be 0.15%, and unique value rate to be 92.3%.

[0033] For date and time fields, the controller collects sample data of the field within an initial time window and calculates the date range, null value rate, and unique value ratio of the sample data. Taking an order date field "order_date" as an example, the controller calculates its date range as 2025-01-01 to 2025-06-30, with a null value rate of 0.01% and a unique value ratio of 45.6%.

[0034] The controller organizes the baseline features of all fields according to a preset field order and feature order to construct an initial quality baseline. The preset field order is as follows: first, group by data source; within the same data source, group by table name or collection name; within the same table, arrange the fields according to their physical order in the table. The preset feature order is as follows: for numeric fields, mean, standard deviation, maximum value, minimum value, null value rate, and unique value ratio; for string fields, average length, maximum length, minimum length, null value rate, unique value ratio, and pattern distribution entropy; for date and time fields, date range, null value rate, and unique value ratio. This order setting aims to ensure that the dimensional arrangement of the offset vector is deterministic and reproducible. The initial quality baseline is stored in the controller's baseline database in JSON format or database table form, containing the field identifier, field name, data source, data type, and corresponding multiple benchmark statistical features for each field.

[0035] This step establishes an initial reference standard for data quality by extracting metadata features from multi-source heterogeneous data and constructing an initial quality baseline, laying a quantitative foundation for the subsequent perception and detection of data distribution shifts.

[0036] S2. During the ETL task, the real-time statistical characteristics of the current data stream are periodically collected and compared with the initial quality baseline to generate an offset vector that characterizes the degree of data distribution shift.

[0037] During the ETL task, the controller triggers a real-time statistical feature acquisition once according to a preset acquisition cycle. The acquisition cycle is preset by the user based on the arrival rate and stability requirements of the data stream, typically triggered once every 1000 records processed, or once every 5 minutes. In this embodiment, the statistical feature acquisition is triggered once every 2000 records processed.

[0038] For each field in the current data stream batch, the controller calculates real-time statistical characteristics in the same way as in step S1: for numeric fields, it calculates the mean, standard deviation, maximum value, minimum value, null value rate, and unique value ratio of the current batch; for string fields, it calculates the average length, maximum length, minimum length, null value rate, unique value ratio, and string pattern distribution of the current batch; for date and time fields, it calculates the date range, null value rate, and unique value ratio of the current batch.

[0039] The controller compares the real-time statistical characteristics of the current data stream with the corresponding baseline characteristics in the initial quality baseline constructed in step S1, and calculates the relative deviation rate of each statistical characteristic of each field:

[0040] Specifically, for the mean characteristic of numerical fields, the relative deviation rate Calculate using the following formula:

[0041] in, This is the baseline mean of this field in the initial quality baseline. This is the real-time average of this field for the current batch. For a very small positive number, in this embodiment we take... Used to prevent when Division by zero exception when the value is zero;

[0042] For the standard deviation feature of numerical fields, the relative deviation rate Calculate using the following formula:

[0043] in, This is the baseline standard deviation of this field in the initial quality baseline. This is the real-time standard deviation of this field for the current batch. It is a very small positive number;

[0044] For the null value rate characteristic, the relative deviation rate Calculate using the following formula:

[0045] in, This is the baseline null value rate for this field in the initial quality baseline. This represents the real-time null value rate of this field in the current batch. It is a very small positive number.

[0046] For the string average length feature, the relative deviation rate Calculate using the following formula:

[0047] in, This is the baseline average length of this field in the initial quality baseline. This represents the real-time average length of this field for the current batch. It is a very small positive number;

[0048] For the uniqueness ratio characteristic, the relative deviation rate Calculate using the following formula:

[0049] in, The ratio of the baseline unique values ​​for this field in the initial quality baseline. This represents the real-time unique value ratio of this field in the current batch. It is a very small positive number;

[0050] In the above formula for calculating the relative deviation rate, add [the following to the denominator] The purpose is to prevent division by zero anomalies when the baseline eigenvalue is zero; The value of is much smaller than the order of magnitude of normal statistical characteristics and will not have a substantial impact on the calculation results;

[0051] The controller arranges the calculated relative deviation rates according to the preset field and feature order in step S1 to construct a multidimensional offset vector; assuming a total of There are 10 fields, each field has 100 fields. If the statistical features are such that the dimension of the offset vector is... Its expression is:

[0052] ;in, Indicates the first The field number The relative deviation rate of the statistical characteristics. The value range is 1 to , The value range is 1 to ;

[0053] The controller stores the calculated offset vector in memory as an input parameter for dynamically adjusting the cleaning strategy in step S3.

[0054] It should be noted that the above calculation method of relative deviation rate is based on normalized relative measure. Its advantage is that features of different dimensions can be uniformly mapped to the relative deviation rate scale for comparison. When the initial baseline value of a certain statistical feature is zero, the controller directly sets the offset of that dimension to the preset maximum offset value (100% in this embodiment) to ensure that the subsequent judgment logic will not fail due to the division by zero anomaly.

[0055] In addition, the controller performs field structure consistency verification every time it performs real-time statistical feature acquisition. Specifically, the controller compares the actual set of fields appearing in the current batch of data stream with the set of fields recorded in the initial quality baseline to detect whether there are any added fields, missing fields, or changes in field types.

[0056] When a field is detected to exist in the initial quality baseline but not appear in three consecutive batches in the current data stream, the controller determines that the field has been deleted by the upstream data source, removes the field from the current cleaning strategy, and no longer performs cleaning operations such as null value filling, outlier removal, and format validation on the field. The field deletion event and its execution time are recorded in the system log.

[0057] When a new field not recorded in the initial quality baseline is detected in the current data stream, the controller automatically collects sample data of the new field within the range of the most recent 7 days or the most recent 10,000 records. It calculates its statistical characteristics such as mean, standard deviation, null rate, and string pattern distribution in the same way as step S1, constructs the initial baseline features of the new field, and dynamically adds them to the quality baseline. At the same time, the new field is included in the cleaning strategy and link relationship construction scope of subsequent batches. The initial cleaning parameters use the system default configuration (mean filling and outlier removal of 3 times the standard deviation for numerical data, mode filling and strict format validation for string data).

[0058] When the data type of a field is detected to be inconsistent with the type recorded in the initial quality baseline (e.g., changing from string to numeric), the controller marks the field as a type change pending confirmation, suspends automatic cleaning of the field, and reports the change information to an alarm. The operations and maintenance personnel then confirm whether the change is the expected behavior. After confirmation by the operations and maintenance personnel, the controller re-collects sample data for the field and reconstructs the baseline features, resuming automatic cleaning. The results of the field structure consistency verification, together with the real-time statistical features, are used as components of the offset vector for dynamic adjustment of the cleaning strategy in step S3.

[0059] This step involves periodically collecting real-time statistical features and comparing them item by item with the initial quality baseline. The relative deviation rate of each feature is calculated and organized into a multi-dimensional offset vector, which realizes the digital and quantitative perception of data distribution offset and provides a quantitative basis for the dynamic adjustment of subsequent cleaning strategies.

[0060] S3. Based on the offset vector, dynamically adjust the execution parameters in the current cleaning strategy and perform cleaning on the data stream; when the offset vector exceeds the preset allowable range, trigger the safe switching process of the cleaning operator;

[0061] The controller acquires the offset vector generated in step S2 and compares the offset of each dimension of the offset vector with the preset first threshold vector and second threshold vector dimension by dimension; the first threshold vector and second threshold vector have the same dimension as the offset vector, i.e., all are... Dimension, where each dimension corresponds to the upper limit of the acceptable offset of a statistical feature of a field;

[0062] The method for determining the first and second threshold vectors is as follows: For each statistical feature of each field, historical fluctuation data of that feature under normal production conditions (containing at least 30 normal production days' samples) is collected in advance, and the standard deviation of its fluctuation distribution is calculated. The first threshold is set to 2 times the standard deviation, and the second threshold is set to 5 times the standard deviation. Taking the mean feature of a numerical field as an example, if the standard deviation of its historical mean is 0.05, then the first threshold is 0.10, and the second threshold is 0.25. When historical data is not available, empirical default values ​​can be used: the first threshold is 0.10, and the second threshold is 0.30. The basis for setting the above empirical default values ​​is: in typical ETL scenarios, statistical feature fluctuations within 10% are usually caused by normal random sampling differences in data and do not constitute a necessary condition for adjusting the cleaning strategy; fluctuations above 30% indicate that the data source may have undergone structural changes, requiring the triggering of operator switching. Fluctuations between these two are considered moderate anomalies and can be addressed through parameter adjustments.

[0063] The preset allowable range is defined by the first threshold vector and the second threshold vector: the offset is within the first threshold vector, the offset is between the first threshold vector and the second threshold vector, the offset is the range that needs parameter adjustment, and the offset is the range that needs operator switching when it exceeds the second threshold vector.

[0064] The controller executes the following decision logic:

[0065] Scenario 1: When the absolute value of the offset of all dimensions is lower than or equal to the corresponding first threshold in the first threshold vector, it is determined that the current data distribution is stable and no significant offset has occurred; the controller maintains the current cleaning rules and parameters unchanged, and the data stream is processed normally according to the original cleaning strategy.

[0066] Scenario 2: When the absolute value of the offset of at least one dimension is higher than the corresponding first threshold in the first threshold vector and lower than or equal to the corresponding second threshold in the second threshold vector, it is determined that the current data has a moderate offset; the controller automatically adjusts the adjustable parameters in the current cleaning rule according to the offset direction and offset magnitude of the dimension.

[0067] The adjustable parameters include the type of null filling strategy, the outlier fold threshold for outlier removal, and the strictness of format validation. The adjustment methods are as follows:

[0068] For the null value filling strategy: When the null value rate offset of a field is positive and exceeds the first threshold, it indicates that the null value rate of the field has increased significantly. The controller switches the null value filling strategy of the field from the default "mean filling" to "median filling" or "mode filling". The filling value is dynamically calculated based on the statistical characteristics of the current batch. Specifically, if the field is numeric, the median is used instead of the mean as the filling value; if the field is string, the mode is used as the filling value. When the null value rate offset is negative, the controller keeps the original filling strategy unchanged or reduces the filling priority.

[0069] For outlier removal thresholds: when the standard deviation offset of a numerical field is positive and exceeds the first threshold, it indicates that the data dispersion of that field has increased; the controller adjusts the outlier removal threshold from the default 3 times the standard deviation to 3.5 times or 4 times the standard deviation; when the standard deviation offset is negative, the controller adjusts the outlier removal threshold to 2.5 times the standard deviation; the 3 times standard deviation is the classic outlier detection criterion based on the normal distribution, and 2.5 times and 4 times are commonly used adjustment values ​​that tighten and relax the threshold by one level, respectively;

[0070] Regarding the strictness of format validation: When the string pattern distribution offset of a string field exceeds the first threshold, it indicates that the string format pattern of the field has changed significantly; the controller switches the strictness of format validation from "strict mode" to "relaxed mode", allowing the new string pattern to pass the validation; specifically, in strict mode, the string must completely match the preset regular expression; in relaxed mode, the string only needs to meet the basic format requirements; when the pattern distribution tends to stabilize (i.e., the pattern distribution offset of 3 consecutive batches falls back to within the first threshold), the controller restores the strictness to strict mode;

[0071] The controller performs cleaning operations on the current data stream according to the adjusted cleaning parameters, including null value filling, outlier removal, format validation, and deduplication.

[0072] Scenario 3: When the absolute value of the offset of at least one dimension is higher than the corresponding second threshold in the second threshold vector, it is determined that the current data distribution has drifted drastically, triggering the safe switching process of the cleaning operator;

[0073] The specific execution process of the secure handover procedure is as follows:

[0074] First, the controller selects candidate cleaning operators from a preset operator pool. The operator pool stores executable code and configuration parameters for various cleaning operators, including but not limited to: statistical Z-score anomaly detection operators, boxplot anomaly detection operators based on interquartile range, machine learning anomaly detection operators based on isolated forests, rule-based null value filling operators, regular expression-based format validation operators, fuzzy matching cleaning operators based on edit distance, and clustering-based DBSCAN consistency validation operators. Each operator in the operator pool has undergone functional and performance testing beforehand to ensure that it can be executed normally.

[0075] The controller matches the most suitable candidate cleaning operator from the operator pool based on the field type and offset characteristics of the field that is currently experiencing a sharp shift. Specifically, when the mean and standard deviation of a numerical field both shift sharply, the controller selects a box plot anomaly detection operator based on interquartile range as a candidate; when the pattern distribution of a string field shifts sharply, the controller selects a fuzzy matching cleaning operator based on edit distance as a candidate.

[0076] The controller selects candidate cleaning operators from the operator pool according to the following rules:

[0077] For numerical fields, when the mean offset exceeds the second threshold but the standard deviation does not, the Z-score anomaly detection operator is selected; when the standard deviation offset exceeds the second threshold (regardless of whether the mean is exceeded), the box plot anomaly detection operator is selected; when only the extreme value offset exceeds the second threshold, the box plot anomaly detection operator is also selected.

[0078] For string fields, when the pattern distribution offset exceeds the second threshold but the average length does not, the regular expression format verification operator is selected; when the average length offset exceeds the second threshold (regardless of whether the pattern distribution exceeds it), the edit distance fuzzy matching cleaning operator is selected; when the unique value ratio offset exceeds the second threshold, the DBSCAN consistency verification operator is selected.

[0079] When the null value rate offset exceeds the second threshold, the null value filling operator is selected uniformly.

[0080] When multiple fields trigger drastic offsets simultaneously, they are processed with priority in the following order: numeric fields first, string fields second, and null value rate last. If different fields point to different candidate operators, all candidate operators are deployed to an isolated verification environment, and the one with the lowest difference index is selected as the final switching target.

[0081] When none of the above conditions are met or cannot be matched, the isolated forest anomaly detection operator is selected as the default candidate.

[0082] After a candidate operator is selected, the controller reads the pass rate of the operator in the most recent 10 trial runs. If the pass rate is less than 60%, the next candidate operator is selected or the system jumps to the default candidate. If the pass rate is not less than 60%, the system enters the isolation verification and deployment phase.

[0083] The controller deploys candidate cleaning operators in an isolated verification environment that runs parallel to the main pipeline. The isolated verification environment is an independent memory space and computing thread that is completely isolated from the data flow processing logic of the main pipeline. Any abnormality of the candidate operators will not affect the normal operation of the main pipeline. The controller sends a copy of the data to be processed in the current batch to the isolated verification environment, where the candidate cleaning operators process the data copy.

[0084] The controller calculates the statistical characteristic difference between the processing results of the candidate cleaning operator and the processing results of the current cleaning operator; specifically, the controller calculates the mean, standard deviation, null value rate and unique value ratio of the data after processing by the two operators, and calculates the relative deviation of each statistic.

[0085] relative deviation of mean Calculate using the following formula:

[0086] in, The mean of the data after processing by the candidate cleaning operators. This represents the mean of the data processed by the current cleaning operator. It is a very small positive number;

[0087] Standard deviation relative deviation Calculate using the following formula:

[0088] in, The standard deviation of the data after processing by the candidate cleaning operator. The standard deviation of the data after processing by the current cleaning operator. It is a very small positive number;

[0089] Relative deviation of blank rate Calculate using the following formula:

[0090] in, The null value rate of the data after processing by the candidate cleaning operator. The null value rate of the data after processing by the current cleaning operator. It is a very small positive number;

[0091] Relative Deviation of Unique Value Ratio Calculate using the following formula:

[0092] in, The ratio of unique values ​​in the data after processing by the candidate cleaning operator. The ratio of unique values ​​in the data after processing by the current cleaning operator. It is a very small positive number;

[0093] The controller uses the weighted sum of the relative deviations as a measure of the degree of difference. :

[0094]

[0095] in, For the preset weighting coefficients, satisfy In this embodiment, the values ​​are 0.3, 0.3, 0.2, and 0.2 respectively. The weighting is based on the following: the mean and standard deviation are the core statistics of data distribution, reflecting the central tendency and dispersion of the data, and have the greatest impact on the cleaning effect. Therefore, each is assigned a weight of 30%. The null value rate and unique value ratio are auxiliary statistics, reflecting the integrity and diversity of the data, and each is assigned a weight of 20%.

[0096] When the difference index is lower than the preset tolerance value, the controller determines that the trial run has passed. The preset tolerance value is set by those skilled in the art based on the sensitivity requirements of downstream data analysis tasks to data consistency. In this embodiment, it is exemplarily set to 5%, and this value does not constitute a limitation on the scope of protection of this invention. After the trial run passes, the controller uses an atomic pointer remapping method to atomically switch the data stream pointer of the main pipeline from the memory address of the current cleaning operator to the memory address of the candidate cleaning operator. The atomic pointer remapping is a processor-level comparison and swap atomic operation. The switching process is completed within a single CPU cycle without interrupting the data stream processing. The controller then releases the memory resources occupied by the original operator.

[0097] The data stream pointer is an atomic pointer variable that points to the currently active operator context structure. This structure contains the operator code entry address, configuration parameters, and status flags. During switching, only the pointer's pointer is modified. After the switching instruction is issued, the controller marks the current operator as disabled but retains its context. Batches currently being processed continue to use the current operator by each thread until completion. After the switch, new batches are processed by the new operator, preventing data loss or duplication.

[0098] If the trial run fails, the controller will not perform the switch and will maintain the current operator. After the switch is completed, if the candidate operator has an anomaly, the controller will switch the atomic pointer back to the old operator address saved before the switch. New data will continue to be processed by the old operator, and data in transit that has entered the candidate operator processing flow will continue to be completed by each thread. At the same time, alarm information will be reported. If the integration quality index of 5 consecutive batches is not lower than the preset target after the switch, the switch is considered successful and the old operator resources will be released.

[0099] When the difference index is higher than or equal to the preset tolerance value, the controller determines that the trial run has failed, automatically discards the processing results of the candidate operator in the isolated verification environment, maintains the current cleaning operator to continue running, and reports alarm information to the upper-level operation and maintenance system, notifying the operation and maintenance personnel to manually intervene and check the changes in the data source.

[0100] This step achieves adaptive response of the cleaning strategy to changes in data distribution by dynamically adjusting and safely switching the cleaning parameters driven by the offset vector. Moderate offsets are addressed through parameter fine-tuning, while drastic offsets are addressed through hot operator switching. This combination ensures the robustness and continuity of the ETL cleaning process under varying degrees of data distribution changes.

[0101] S4. Based on the semantic information of the fields in the metadata features, and extracting the data flow distribution features from the real-time statistical features, construct candidate link relationships between multi-source heterogeneous data, perform link matching on multi-source heterogeneous data records according to the candidate link relationships, and assign a confidence index to each candidate link relationship.

[0102] Based on the semantic information of the fields in the metadata features obtained in step S1, and extracting the data flow distribution features from the real-time statistical features in step S2, the controller constructs candidate link relationships between multi-source heterogeneous data.

[0103] The specific process for constructing candidate link relationships is as follows:

[0104] First, the controller iterates through the fields in each data source and uses a pre-trained language model to encode the field names and annotation text into semantic vectors. In this embodiment, the pre-trained language model is Sentence-BERT, which encodes the input text into a 384-dimensional semantic embedding vector. For each field, the controller concatenates its field name and annotation text into a text input in the format "field name: annotation" (if the field has no annotation, only the field name is input), and encodes it into a semantic embedding vector through the pre-trained language model.

[0105] Taking an e-commerce scenario as an example, data source A's order table has a field "order_id" with the annotation "unique order identifier"; data source B's transaction table has a field "transaction_id" with the annotation "transaction serial number". The controller encodes both into semantic vectors, and the two should have a high degree of similarity in the semantic space because they both express the semantics of "unique identifier".

[0106] The controller calculates the semantic similarity between any two different source fields, specifically using cosine similarity as a metric.

[0107] ;

[0108] in, For the first Semantic embedding vectors of each field, For the first Semantic embedding vectors of each field, Represents the vector dot product. The Euclidean norm of a vector. For the first The first field and the second The semantic similarity between the fields, with values ​​ranging from 1 to 2. The closer the value is to 1, the more similar the semantics.

[0109] The controller filters field pairs with semantic similarity exceeding a preset threshold as semantic matching candidates; in this embodiment, the preset threshold is set to 0.75. The basis for setting this threshold is that in the semantic space of Sentence-BERT, the cosine similarity of synonymous fields is usually above 0.70, while the cosine similarity of irrelevant fields is usually below 0.50; setting it to 0.75 can effectively exclude irrelevant field pairs while maintaining a high recall rate.

[0110] For semantic matching candidate pairs, the controller further calculates the data distribution similarity between corresponding fields; specifically, the controller extracts the data distribution features of each field from the real-time statistical features of step S2: for numerical fields, the minimum value, maximum value, mean, standard deviation, and null value rate are extracted as distribution feature vectors; for string fields, the average length, null value rate, unique value ratio, and entropy value of the string pattern distribution are extracted as distribution feature vectors.

[0111] For pairs of numerical fields, a simplified version of the normalized bulldozer distance is used to measure distribution similarity; specifically, the calculation method is as follows: the numerical ranges of the two fields are divided into equal parts. Each interval (in this embodiment) (Take 10), and calculate the probability distribution of data within each interval. and Distribution similarity Calculate using the following formula:

[0112] ;

[0113] in, For the first field in the The probability distribution of data within each interval For the second field in the The probability distribution of data within each interval The total number of intervals, The range of values ​​is The closer the value is to 1, the more similar the distributions are;

[0114] For string field pairs, the Jaccard similarity metric is used to measure the similarity of the string pattern sets:

[0115] ;

[0116] in, and These are sets of string patterns for two fields, respectively. The Jaccard similarity score for a set of string patterns, with values ​​ranging from 1 to 2. ;

[0117] The controller identifies field pairs whose semantic similarity and data distribution similarity both exceed their respective preset thresholds as candidate link relationships. The preset threshold for semantic similarity ranges from 0.7 to 0.8 (0.75 in this embodiment), and the preset threshold for data distribution similarity ranges from 0.6 to 0.7 (0.65 in this embodiment). The data distribution similarity threshold of 0.65 is an exemplary configuration; those skilled in the art can adaptively adjust it within the range of 0.6 to 0.7 based on the field value distribution characteristics of the data source.

[0118] The controller performs link matching on multi-source heterogeneous data records according to the candidate link relationships. Specifically, for field pairs in the candidate link relationships, the controller performs an equi-join on the values ​​of the fields in data source A and data source B. If the data is of string type and there are spelling differences, edit distance similarity matching can also be used. When the similarity exceeds the threshold of 0.85, it is considered a match. After a successful match, candidate link record pairs are generated.

[0119] The controller assigns a confidence metric to each candidate link relationship, and the specific process is as follows:

[0120] A pre-trained language model is used to encode the names and annotation text of fields in each data source into semantic vectors. The semantic similarity is obtained by calculating the cosine similarity between the semantic vectors of different source fields. ;

[0121] Mutual information is used to calculate the data distribution similarity of different source fields. The mutual information is used to measure the statistical dependency between two fields, and its calculation formula is as follows:

[0122] ;in, and These are random variables in two fields, For fields The number of value categories, For fields The number of value categories, For fields Take the first One value and field Take the first The joint probability of each value For fields Take the first The marginal probability of each value For fields Take the first Marginal probabilities of each value; mutual information Normalization to Intervals are used as distribution similarity;

[0123] Historical matching accuracy of reading candidate link relationships The historical matching accuracy is recorded in the controller's link relationship database, reflecting the matching accuracy of the link relationship in historical integration tasks. The calculation method is the number of historically successfully matched records of the link relationship divided by the total number of matching attempts of the link relationship. In the initial state when there is no historical data, the historical matching accuracy takes the default value of 0.5.

[0124] The confidence index of each candidate link relationship is calculated by weighting and summing semantic similarity, distributional similarity, and historical matching accuracy. : ;

[0125] in, This is the semantic similarity weight coefficient. The distribution similarity weight coefficient, The historical matching accuracy weighting coefficients, the three satisfy the following: In this embodiment, we take The weight configuration is based on the following: semantic similarity is the most important factor, because field names and annotations directly reflect the business meaning of the fields and are the strongest signal of the link relationship; data distribution similarity is secondary, because even if two fields are semantically similar, if the data distributions are too different, the actual data of the two may not have a link relationship; historical matching accuracy is used as an auxiliary factor to correct the current judgment by using historical experience and to prevent misjudgment caused by occasional anomalies in semantic or statistical features.

[0126] It should be noted that the specific values ​​of the above weight coefficients are based on empirical tuning results in multi-source heterogeneous data linking scenarios. When the metadata quality of different data sources in the application scenario varies significantly, those skilled in the art can use methods such as grid search or Bayesian optimization to readjust the above weight coefficients on the validation set with link accuracy as the optimization objective, without the need for creative work.

[0127] This step constructs candidate link relationships through dual screening using semantic similarity and distributional similarity. It then assigns a confidence index by weighted fusion of semantic similarity, distributional similarity, and historical matching accuracy, thereby achieving automatic discovery and reliable quantification of link relationships between multi-source heterogeneous data. This provides a reliable basis for subsequent hierarchical routing and integration.

[0128] S5. Based on the confidence index, perform hierarchical routing processing and integration merging on the linked data records to generate integration quality evaluation information and link verification information. Use the integration quality evaluation information to update the cleaning strategy and feed it back to the step of dynamically adjusting the cleaning strategy. Use the link verification information to optimize the candidate link relationship and feed it back to the step of constructing the candidate link relationship.

[0129] The controller performs hierarchical routing processing on the linked data records based on the confidence index calculated in step S4.

[0130] The specific rules for hierarchical routing processing are as follows:

[0131] High-confidence data: The controller determines data records with a confidence index not lower than a first preset threshold (0.85 in this embodiment) as high-confidence data and directly sends them into the integration and merging process; the first preset threshold of 0.85 is an exemplary configuration, and those skilled in the art can adaptively adjust it around this value according to the trade-off requirements of accuracy and recall in the integration scenario; in the integration and merging process, the controller performs field mapping, conflict resolution and data fusion operations on the high-confidence data records to generate integrated data records and write them into the target data warehouse;

[0132] Low-confidence data: The controller classifies data records with a confidence index lower than the second preset threshold (0.50 in this embodiment) as low-confidence data, marks them as pending manual arbitration, and triggers the source-side re-examination process. The second preset threshold of 0.50 is set based on the fact that when the confidence level is lower than 0.50, the accuracy of the link relationship is usually lower than 80%, and the risk of automatic integration introducing errors is too high, requiring manual intervention. In the source-side re-examination process, the controller sends a data re-examination request to each source system, requiring the source system to provide the original data records again to verify the data accuracy. Low-confidence data is not written to the target data warehouse for the time being, and is supplemented manually or semi-automatically after manual confirmation or source-side re-examination is completed.

[0133] Intermediate confidence data: The controller determines data records with confidence indices between the second preset threshold and the first preset threshold (i.e., between 0.50 and 0.85) as intermediate confidence data, temporarily stores them in a buffer, and triggers an asynchronous supplementary verification process; the data records in the buffer do not participate in the aggregation calculation of the downstream data warehouse during the asynchronous supplementary verification period, and are appended to the target database in a delayed writing manner after the verification is passed;

[0134] The specific execution process of the asynchronous supplementary verification procedure is as follows:

[0135] The controller parses the source system identifiers involved in the temporary data records, reads the dynamic weight table of each source system, and sorts them from highest to lowest weight. The weights of each source system... Updated in real time according to the following rules:

[0136] in, For the first The current weights of the source system, For the first The basic weight of each source system (ranging from 0.5 to 1.0, preset by system importance: 1.0 for core business systems, 0.7 for auxiliary systems, and 0.5 for log systems). The time decay coefficient (0.01 in this embodiment, unit: ...) ), The distance from the current time to the th The time difference (in days) between the last successful data update of each source system. For the first The formula represents the historical pass rate of a source system. The physical meaning of this formula is: the closer a source system is to its most recent successful update and the higher its historical pass rate, the higher its data reliability and the higher its weight.

[0137] The controller sorts the source systems from high to low weights, generating a priority sequence of the source systems;

[0138] The controller automatically generates completion query requests for high-weight source systems; the priority of the completion query request increases with the residence time of the data in the buffer, that is, the longer the residence time of the data record, the higher the completion priority is obtained.

[0139] When the supplementary query obtains valid data that is consistent with the existing data, the controller releases the data record from the buffer, marks it as verified, and appends it to the target database in a delayed writing manner. In order to avoid high-frequency writing from putting pressure on the target database, the controller temporarily stores the verified records in the write queue and writes them to the target database in batches every time a certain number is accumulated (100 records in this embodiment) or every preset time interval (10 seconds in this embodiment).

[0140] When a supplementary query fails to obtain valid data or the data still conflicts, the controller will downgrade the data record to await manual arbitration.

[0141] The controller tracks the integrity, consistency, and uniqueness of the integrated data. The preset quality targets for each target are as follows: integrity rate no less than 95%, consistency no less than 90%, and uniqueness no less than 98%. These preset quality targets are based on industry data quality assessment standards. A 95% integrity rate means that no more than 5 out of every 100 fields are empty; 90% consistency means that cross-source data achieves consistency across more than 90% of fields; and 98% uniqueness means that no more than 2 out of every 100 records have duplicate primary keys. The calculation methods for each target are as follows:

[0142] Completeness rate = Sum of non-null fields in the integrated data records / (Total number of records × Total number of fields in the target table)

[0143] Consistency = Sum of consistent cross-source fields in integrated data records / (Total number of records × Total number of cross-source fields)

[0144] Uniqueness = Number of primary key unique records in the integrated data record set / Total number of records

[0145] When the aforementioned completeness, consistency, or uniqueness indicators are lower than the preset quality target, the controller lowers the first threshold vector and the second threshold vector corresponding to each dimension of the offset vector:

[0146]

[0147]

[0148] in, and The first and second threshold vectors before the update. and The updated first and second threshold vectors are used; that is, the first and second thresholds on all dimensions are reduced by 20% simultaneously, making the cleaning strategy more sensitive to data distribution shifts; the 20% reduction adjustment is based on the following: the step size of each round of feedback adjustment should not be too large or too small, and a 20% step size can converge to a better threshold configuration within 3-5 batches based on experience; the controller feeds back the updated first and second threshold vectors to the dynamic adjustment cleaning strategy stage in step S3;

[0149] In addition, as another way to integrate quality evaluation information into the cleaning strategy, the controller automatically triggers a baseline recalibration process after every 50 batches of ETL processing are completed.

[0150] The specific execution process of baseline recalibration is as follows: The controller selects records that were judged as high-confidence data in step S5 and did not trigger manual arbitration after integration and merging from the processed data of the most recent 7 days or the most recent 50,000 records to form a recalibration sample set. After removing abnormal or manually corrected records, the baseline statistical characteristics such as the mean, standard deviation, null value rate, average length and string pattern distribution of each field are recalculated according to the same statistical method as in step S1, and used as candidate new baselines.

[0151] The controller updates the baseline using an exponentially weighted moving average method. The updated baseline is equal to the old baseline multiplied by 0.7 plus the candidate new baseline multiplied by 0.3 (with a learning rate of 0.3). This method merges the candidate new baseline with the currently stored old baseline, ensuring that the baseline can smoothly evolve with the long-term trend of the data while suppressing drastic jumps caused by single, occasional fluctuations.

[0152] The controller stores the merged and updated quality baseline in the baseline database, overwriting the original initial quality baseline, for use in the calculation of the offset vector in the S2 step of subsequent batches. It also records the timestamp of this recalibration and the main changes in the baseline before and after the update to the system log. When the number of high-confidence data records in the recalibration sample set is less than 1,000, the controller determines that the statistical confidence is insufficient, skips this recalibration, and maintains the original baseline unchanged.

[0153] The aforementioned 50-batch periodic batch recalibration can only achieve long-term baseline iteration. To address the baseline update lag issue in scenarios of slow drift and sudden structural drift, this embodiment simultaneously adds two sets of parallel update logic: lightweight baseline micro-update and operator switching emergency calibration.

[0154] For continuous moderate offset baseline micro-updates, if the offset vectors of three consecutive batches of an ETL task have only a moderate offset dimension and no dimension offset exceeding the second threshold, then after each batch is cleaned, the real-time statistical features of the current batch are extracted, and the baseline is fine-tuned using an exponentially weighted moving average with a learning rate of 0.02. The calculation formula is: updated baseline = old baseline × 0.98 + current batch statistical features × 0.02; the adjustment range of a single feature in a single field is constrained to not exceed 3%, and the fine-tuned baseline is stored in the baseline library in real time. The next batch S2 directly uses the new baseline to calculate the offset vector; when the integration completeness, consistency, and uniqueness indicators are lower than the preset quality target, the micro-update learning rate is simultaneously increased to 0.05 to accelerate the baseline adaptation to the current data distribution;

[0155] After the data in the emergency baseline calibration S3 step of the cleaning operator switching process experiences severe drift, and the candidate operator trial runs pass and atomic pointer switching is completed, there is no need to wait for 50 batch cycles. Instead, five consecutive batches of stable data streams are automatically collected after the switch to form a temporary sample set. When the total number of sample records is ≥500, lightweight baseline fusion update is performed, with a fusion weight of 0.6 for the old baseline and 0.4 for the new sample baseline. If there are fewer than 500 samples, only the operation log is recorded, and this emergency calibration is skipped. At the same time, the source of batch recalibration samples is optimized. Intermediate confidence data that has passed the verification through asynchronous supplementation and delayed writing to the target library can be included in the periodic batch recalibration sample set to avoid the inability to update the baseline for a long time due to insufficient qualified samples in multi-source data conflict scenarios.

[0156] The controller generates link verification information, which includes the number of successful matches, the number of failed matches, the number of cross-source numerical conflicts, and the asynchronous supplementary verification results for each candidate link relationship in this batch integration and merging process.

[0157] When cross-source numerical conflicts or asynchronous supplementary verification failures occur during the integration and merging process, the controller updates the historical matching accuracy of the link relationship based on the verification results of the current batch. Specifically, for records in this batch that were matched using a certain link relationship, the controller counts the number of records that were successfully matched. and the number of records that failed to match The historical match accuracy for updating this link relationship is:

[0158]

[0159] in, This represents the historical matching accuracy before the update. This is the updated historical match accuracy. This represents the total number of historical matching attempts. This represents the number of records that were successfully matched in this batch. This represents the number of records that failed to match in this batch. The physical meaning of this update method is: to take a weighted average of historical experience and the observation results of the new batch. The larger the cumulative number of historical matching attempts, the greater the impact of historical experience on the update results, and the smaller the impact of the observation results of the new batch.

[0160] The controller feeds back the updated historical matching accuracy to the candidate link relationship building stage in step S4;

[0161] This step implements a differentiated data integration strategy through confidence-driven hierarchical routing, and achieves closed-loop optimization of the two core processes of cleaning and linking through a two-way feedback mechanism.

[0162] It should be noted that steps S2 to S5 above are continuously executed in a loop during the ETL task; the real-time statistical feature collection of each batch triggers a new round of offset detection and cleaning strategy adjustment; the integration quality evaluation results of each batch are fed back to the cleaning strategy adjustment stage; the link verification information of each batch is fed back to the candidate link relationship construction stage; the baseline recalibration triggered by the cumulative integration quality evaluation of each batch makes the initial quality baseline smoothly updated as the data evolves over a long period of time; through the above iterative mechanism, this embodiment achieves continuous adaptive optimization of data cleaning and integration processing;

[0163] When the ETL task terminates or all data from all data sources have been processed, the controller outputs an integration completion signal, ending the adaptive ETL data cleaning and integration process.

[0164] This embodiment achieves refined perception of data distribution offset and adaptive adjustment of cleaning strategies through the construction and dimension-by-dimensional comparison of multi-dimensional offset vectors; it achieves automatic discovery and reliable measurement of multi-source heterogeneous data link relationships through dual screening of semantic similarity and distribution similarity and weighted confidence calculation; it achieves differentiated data integration strategies and closed-loop optimization of cleaning-linking through hierarchical routing processing and bidirectional feedback mechanisms; and it achieves lossless hot switching of cleaning operators through isolation verification and atomic pointer remapping in the secure switching process. The above technical means work together to effectively solve the technical problems of fixed cleaning strategies, difficulty in linking heterogeneous data, and lack of quality feedback in the traditional ETL process, and improve the automation level and quality assurance capability of data cleaning and integration processing; through three types of baseline update mechanisms—periodic batch recalibration, batch-by-batch micro-update, and emergency calibration for operator switching—the timing of various baseline update triggers is clearly defined, and a closed-loop link of data offset detection, baseline iteration, and adaptive cleaning is established, solving the defects of the original baseline update rules being ambiguous and update lag leading to limited adaptive effects.

[0165] Example 2 provides an adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links, suitable for data platform construction scenarios in small and medium-sized enterprises. The difference from Example 1 is as follows: In the perception stage, lightweight metadata collection and statistical feature extraction are used to reduce the sampling frequency; in the offset detection stage, a fixed threshold is used instead of a differentiated threshold vector; in the cleaning strategy adjustment stage, fixed parameter mapping is used instead of real-time calculation in Example 1, and the safety switching process is omitted; in the link relationship construction stage, lightweight semantic matching based on keywords is used instead of a pre-trained language model; a simplified weighting scheme is used for confidence calculation; hierarchical routing is simplified to a two-level routing; and feedback adjustment uses a fixed step size instead of the proportional adjustment in Example 1. Specifically, it includes the following steps:

[0166] S1. Collect core field metadata to build a simplified initial quality baseline;

[0167] The controller only collects metadata features of primary key fields, foreign key fields, and user-specified core business fields from each data source, ignoring the collection of metadata for non-core fields; for each core field, only the field name and data type metadata are extracted, omitting the collection of comment text.

[0168] For numeric fields, the initial quality baseline only includes the mean and the null value rate, omitting the standard deviation, maximum value, minimum value, and unique value ratio; for string fields, the initial quality baseline only includes the mean length and the null value rate, omitting the maximum length, minimum length, unique value ratio, and string pattern distribution; the initial time window is the most recent 3 days or the most recent 3000 records.

[0169] The controller stores simplified baseline features of all fields in the controller's baseline database, grouped by data source and table name.

[0170] S2. Reduce the sampling frequency to collect real-time statistical features and use a fixed threshold to determine the offset;

[0171] The collection cycle has been relaxed from triggering once every 2,000 records processed in Example 1 to triggering once every 10,000 records processed, or once every 30 minutes;

[0172] Real-time statistical features only calculate the mean and null value rate of each field (for numerical fields) or the average length and null value rate (for string fields); the formula for calculating the relative deviation rate of each statistical feature of each field is the same as in Example 1, but the offset determination uses a fixed threshold instead of the feature differentiation threshold in Example 1: the offset of all dimensions is uniformly compared with the globally fixed first threshold of 0.15 and the second threshold of 0.40;

[0173] The controller arranges the calculated relative deviation rates according to the preset field order and feature order in step S1 to construct a multidimensional offset vector.

[0174] S3. The cleaning strategy is adjusted by using fixed parameter mapping, omitting the safety switching process;

[0175] The controller obtains the offset vector generated in step S2 and compares the offset of each dimension of the offset vector with the globally fixed first threshold of 0.15 and second threshold of 0.40 dimension by dimension.

[0176] When the absolute value of the offset in all dimensions is less than or equal to the first threshold, the current cleaning rules and parameters remain unchanged.

[0177] When the absolute value of the offset in at least one dimension is higher than the first threshold and lower than or equal to the second threshold, a preset fixed mapping table is used to adjust the cleaning parameters, replacing the real-time calculation adjustment in Example 1. The fixed mapping table has three levels: when the offset is between 0.15 and 0.25, the null value filling strategy is switched from mean filling to median filling, and the outlier multiple threshold for outlier removal is adjusted from 3 times the standard deviation to 3.5 times the standard deviation; when the offset is between 0.25 and 0.40, the null value filling strategy is switched from mean filling to mode filling, and the outlier multiple threshold for outlier removal is adjusted to 4 times the standard deviation; when the offset exceeds 0.40, an alarm message is directly reported to the operation and maintenance system, and the current cleaning operator continues to run, omitting the isolation verification and security switching process in Example 1.

[0178] S4. Simplified semantic matching based on keywords is used to construct candidate link relationships, simplifying confidence calculation;

[0179] Semantic matching uses keyword matching instead of the pre-trained language model encoding in Example 1. Specifically, the controller performs word segmentation on the field names and annotation text of each field, removes stop words and extracts keyword sets, and calculates the semantic similarity between fields using Jaccard similarity. Jaccard similarity is equal to the intersection size of the keyword sets of two fields divided by the union size. The calculation of data distribution similarity is omitted, and candidate link relationships are filtered only based on semantic similarity, with a preset threshold of 0.6.

[0180] The controller performs link matching on multi-source heterogeneous data records according to the candidate link relationships, in the same way as in Example 1;

[0181] The confidence score calculation is simplified to be based solely on semantic similarity, omitting the weighted fusion of distributional similarity and historical matching accuracy. Once a link is successfully matched, the confidence score is equal to the semantic similarity score.

[0182] S5 is simplified to a secondary route and uses fixed-step feedback adjustment.

[0183] The hierarchical routing is simplified from three levels to two levels: data records with a confidence index not lower than the first preset threshold (0.7 in this embodiment) are judged as high-confidence data and directly sent to the integration and merging process; data records with a confidence index lower than the first preset threshold are judged as low-confidence data, marked as pending manual arbitration, and the source end re-examination process is triggered; the buffer temporary storage and asynchronous supplementary verification process for intermediate confidence data in Embodiment 1 is omitted.

[0184] The controller calculates the integrity, consistency, and uniqueness of the integrated data. The calculation method for each indicator is the same as in Example 1. The preset quality targets are: integrity rate not less than 90%, consistency not less than 85%, and uniqueness not less than 95%. When any of the above indicators is lower than the preset quality targets, the controller lowers the global fixed first threshold and second threshold used for offset determination by 0.05 each, making the cleaning strategy more sensitive to data distribution offset. The controller stores the updated thresholds in memory for offset determination in subsequent batches.

[0185] When cross-source numerical conflicts occur in the link relationship during the integration and merging process, the controller marks the conflict record as pending manual arbitration and updates the historical matching accuracy of the link relationship based on the verification results of the current batch. The specific update method is the same as in Example 1, and it is used as a reference for link matching in subsequent batches.

[0186] This embodiment significantly reduces the demand for computing resources, storage resources, and labeled data while ensuring the core ETL data cleaning and integration functions by adopting lightweight metadata collection, simplified statistical feature calculation, fixed threshold determination, keyword semantic matching, and fixed step size feedback adjustment. This makes the adaptive method adaptable to the data platform construction scenarios of small and medium-sized enterprises and low-configuration server or containerized deployment environments.

[0187] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention in any way. Although the present invention has been disclosed above with reference to preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art can make some modifications or alterations to the above-disclosed technical content to create equivalent embodiments without departing from the scope of the present invention. Any simple modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention without departing from the scope of the present invention shall still fall within the scope of the present invention.

Claims

1. An adaptive ETL data cleaning and integration processing method based on multi-source heterogeneous data links, characterized in that, Includes the following steps: Obtain metadata features from multi-source heterogeneous data and construct an initial quality baseline; During the ETL task, the real-time statistical characteristics of the current data stream are periodically collected and compared with the initial quality baseline to generate an offset vector that characterizes the degree of data distribution shift. Based on the offset vector, the execution parameters in the current cleaning strategy are dynamically adjusted and the data stream is cleaned. When the offset vector exceeds the preset allowable range, the safe switching process of the cleaning operator is triggered; Based on the semantic information of the fields in the metadata features, and extracting the data flow distribution features from the real-time statistical features, candidate link relationships between multi-source heterogeneous data are constructed. Link matching is performed on multi-source heterogeneous data records according to the candidate link relationships, and a confidence index is assigned to each candidate link relationship. Based on the confidence index, the linked data records are subjected to hierarchical routing processing and integration merging to generate integration quality evaluation information and link verification information. The integration quality evaluation information is used to update the cleaning strategy and feed it back to the step of dynamically adjusting the cleaning strategy. The link verification information is used to optimize the candidate link relationship and feed it back to the step of constructing the candidate link relationship.

2. The method according to claim 1, characterized in that, The process of comparing the data with the initial quality baseline to generate an offset vector characterizing the degree of data distribution shift specifically includes: The real-time statistical features of the current data stream are compared item by item with the corresponding benchmark features in the initial quality baseline. The relative deviation rate of each statistical feature of each field is calculated. The statistical features include the field null value rate, the mean value, the standard deviation value, the average string length, and the ratio of unique values. The calculated relative deviation rates are arranged according to the preset field order and feature order to construct a multi-dimensional offset vector. Each dimension of the offset vector corresponds to the degree of offset of a statistical feature of a field.

3. The method according to claim 1, characterized in that, The process of dynamically adjusting the execution parameters in the current cleaning strategy and performing cleaning on the data stream based on the offset vector specifically includes: The offset of each dimension in the offset vector is compared dimension by dimension with the preset first threshold vector and second threshold vector. When the absolute value of the offset of all dimensions is lower than or equal to the first threshold corresponding to the first threshold vector, the current cleaning rules and parameters remain unchanged. When the absolute value of the offset of at least one dimension is higher than the first threshold corresponding to the first threshold vector and lower than or equal to the second threshold corresponding to the second threshold vector, the adjustable parameters in the current cleaning rules are automatically adjusted according to the offset direction and offset magnitude of that dimension. The adjustable parameters include the type of null filling strategy, the outlier multiple threshold for outlier removal, and the strictness of format verification. When the absolute value of the offset of at least one dimension exceeds the second threshold corresponding to the second threshold vector, it is determined that the current data distribution has drastically shifted, triggering a safe switching process for the cleaning operator.

4. The method according to claim 1, characterized in that, The process of triggering the safe switching procedure of the cleaning operator specifically includes: Candidate cleaning operators are selected from a preset operator pool and deployed in an isolated verification environment parallel to the main pipeline to process copies of the current batch of data to be processed. The statistical characteristic difference between the processing results of the candidate cleaning operators and the current cleaning operator is calculated. If the difference is lower than a preset tolerance value, the trial run is considered successful. After successful trial run, an atomic pointer remapping method is used to atomically switch the data stream pointer of the main pipeline from the memory address of the current cleaning operator to the memory address of the candidate cleaning operator, and release the original operator resources without interrupting data stream processing. If the difference is higher than or equal to the preset tolerance value, the trial run is considered unsuccessful, the processing results of the candidate operators are automatically discarded, the current cleaning operator is maintained, and an alarm message is reported.

5. The method according to claim 1, characterized in that, The process of constructing candidate link relationships among multi-source heterogeneous data specifically includes: It iterates through the fields in each data source, uses a pre-trained language model to encode the field names and annotation text into semantic vectors, calculates the semantic similarity between any two different source fields, and selects field pairs with semantic similarity exceeding a preset threshold as semantic matching candidate pairs. For the semantic matching candidate pairs, the data distribution similarity between the corresponding fields is further calculated, and field pairs whose semantic similarity and data distribution similarity both exceed their respective preset thresholds are determined as candidate link relationships; The preset threshold for semantic similarity is between 0.7 and 0.8, and the preset threshold for data distribution similarity is between 0.6 and 0.

7.

6. The method according to claim 1, characterized in that, The process of hierarchical routing and integration of linked data records based on the confidence index specifically includes: Data records with a confidence level not lower than the first preset threshold are classified as high-confidence data and directly sent to the integration and merging process; data records with a confidence level lower than the second preset threshold are classified as low-confidence data, marked for manual arbitration, and the source end re-examination process is triggered; data records with a confidence level between the second preset threshold and the first preset threshold are classified as intermediate-confidence data, temporarily stored in a buffer, and an asynchronous supplementary verification process is triggered; the data records in the buffer do not participate in the aggregation calculation of the downstream data warehouse during the asynchronous supplementary verification period, and are appended to the target database in a delayed writing manner after the verification is passed; In addition, the integrity rate, consistency, and uniqueness indicators of the integrated and merged data are statistically analyzed; when the integrity rate, consistency, or uniqueness indicators are lower than the preset quality target, the first threshold vector and the second threshold vector corresponding to each dimension of the offset vector are lowered, and the updated first threshold vector and the second threshold vector are fed back to the step of dynamically adjusting the cleaning strategy.

7. The method according to claim 6, characterized in that, The process of triggering the asynchronous supplementary verification process specifically includes: Parse the source system identifiers involved in the temporarily stored data records, read the dynamic weight table of each source system and sort them from high to low weight; automatically generate completion query requests for high-weight source systems, with the priority of the completion query requests increasing with the residence time of the data in the buffer; when the completion query obtains valid data and is consistent with the existing data, release the data record from the buffer and append it to the target database in a delayed write manner; when the completion query fails to obtain valid data or the data still has conflicts, downgrade the data record to await manual arbitration.

8. The method according to claim 7, characterized in that, The dynamic weight table is updated in real time based on the last update timestamp and historical data integrity rate of each data source. The weight of each source system is calculated using a time decay function. The further the current time is from the last successful update time of the source and the lower the historical verification pass rate, the lower the weight of the source, and vice versa. After the weights of each source system are updated in real time according to the above rules, they are sorted from high to low weights, which serves as the dynamic basis for allocating the priority of completion query requests.

9. The method according to claim 1, characterized in that, The process of assigning confidence metrics to each candidate link relationship specifically includes: A pre-trained language model is used to encode the names and annotation text of fields in each data source into semantic vectors. Semantic similarity is obtained by calculating the cosine similarity between the semantic vectors of different source fields. Mutual information is used to calculate the data distribution similarity of different source fields. The data distribution features include the numerical range, mean, variance, null value rate, and string pattern distribution of the fields. The historical matching accuracy of each candidate link relationship is read. The semantic similarity, distribution similarity, and historical matching accuracy are weighted and summed to calculate the confidence index of each candidate link relationship. The weight coefficients of semantic similarity, distribution similarity, and historical matching accuracy are preset values, and the sum of the weights of the three is 1. Furthermore, when cross-source numerical conflicts or asynchronous supplementary verification failures occur during the integration and merging process of the link relationship, the historical matching accuracy of the link relationship is updated based on the verification results of the current batch, and the updated historical matching accuracy is fed back to the step of constructing candidate link relationships.

Images