ETL automatic data quality maintenance method and system, electronic equipment and medium

Abstract

The invention discloses an ETL automatic data quality maintenance method and system, electronic equipment and a medium, and relates to the technical field of data processing, and the method comprises the following steps: writing data from different data sources into the same data theme of message-oriented middleware to construct homologous data; generating a global unique data identifier based on the data offset range of the homologous data, synchronously distributing the homologous data to the main path processing channel and the bypass detection channel, and constructing double-path data association mapping based on the global unique data identifier; the main path processing channel monitors a detection result of the bypass detection channel based on double-path data association mapping, and if data detection is normal, consumption data in the main path processing channel is submitted to a data bin; and if the data detection is abnormal, positioning and loading a pre-configured repair strategy according to the abnormal detection information, and updating the homologous data by using the consumption data repaired by the repair strategy, thereby comprehensively improving the timeliness, reliability and operation and maintenance efficiency of data quality management.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to an automated ETL data quality inspection method, system, electronic device, and medium. Background Technology

[0002] In existing data warehouse technologies, data quality assurance in ETL (Extract, Transform, Load) processes typically employs a post-processing or serial processing model. This means data is first transformed and loaded, then verified through a separate quality control step. This model tightly couples quality control logic to the main data processing chain, creating bottlenecks that severely restrict the throughput and real-time performance of the data pipeline. Furthermore, the detection and remediation stages are often disconnected, with systems often only performing checks or relying on delayed manual intervention, failing to achieve automated closed-loop processing of problematic data. In addition, traditional remediation strategies are mostly based on predefined, fixed rules, lacking the flexibility to execute complex business logic or intelligent reasoning, and struggling to cope with real-world scenarios involving diverse data sources and complex problem types. This serial, rigid, and slow-feedback mechanism leads to late data error detection, high remediation costs, and the easy contamination of the data warehouse by erroneous data, ultimately affecting the accuracy and timeliness of downstream data analysis and decision-making. Therefore, there is an urgent need for a data quality inspection and maintenance solution that enables parallel processing and detection, supports intelligent closed-loop remediation, and provides real-time early warning capabilities.

[0003] The information disclosed in the background section is only intended to enhance the understanding of the background of this application, and therefore may include information that does not constitute prior art known to those skilled in the art. Summary of the Invention

[0004] The purpose of this invention is to provide an automated ETL data quality inspection method, system, electronic device, and medium. By constructing a collaborative technical architecture of "dual streams from the same source, delayed submission, and dynamic repair", it overcomes the inherent defects of existing ETL data quality processing solutions, such as performance bottlenecks, delayed repair, and rigid strategies, and comprehensively improves the timeliness, reliability, and operational efficiency of data quality governance.

[0005] In a first aspect, one technical solution provided in the embodiments of the present invention is: an automated ETL data quality inspection method, comprising the following steps: Write data from different data sources into the same data topic in the message middleware to build data from the same source; A globally unique data identifier is generated based on the data offset range of the same source data. The same source data is synchronously distributed to the main processing channel and the bypass detection channel, and a dual-channel data association mapping is constructed based on the globally unique data identifier. The main processing channel monitors the detection results of the bypass detection channel based on the dual-channel data association mapping. If the data detection is normal, the consumed data in the main processing channel is submitted to the data warehouse. If the data detection is abnormal, the abnormal information is used to locate and load the pre-configured repair strategy, and the consumed data repaired by the repair strategy is used to update the same source data.

[0006] As a preferred embodiment, the step of writing data from different data sources into the same data topic of the message middleware to construct data from the same source includes the following steps: The system receives raw data from heterogeneous data sources through a data access interface; it then performs format parsing and standardization preprocessing on the raw data to form data records with a unified data structure. Serialize the data records and publish them to the same data topic that was created beforehand in the message middleware; The data subject serves as the sole data source consumed by both the main processing channel and the bypass detection channel.

[0007] As a preferred embodiment, generating a globally unique data identifier based on the data offset range of the same source data includes the following steps: Listen to the same data streams of the same data topic in the message middleware, extract continuous data segments from the same data streams according to the preset micro-batch division rules, and record the start and end offsets of the continuous data segments. Extract the data topic identifier and data segment generation timestamp, and combine them with the start offset and end offset to construct basic identifier elements; A fixed-length, globally unique data identifier is generated by performing encryption operations on the basic identifier elements using a hash algorithm.

[0008] As a preferred embodiment, the step of extracting continuous data segments from the same source data stream according to a preset micro-batch partitioning rule and recording the start and end offsets of the continuous data segments includes the following steps: Configure micro-batch partitioning rules, which include data volume threshold rules and time window threshold rules; When the cumulative consumed data volume of the same source data stream monitored in real time reaches the data volume threshold rule, or when the current consumption time reaches the time window threshold rule, the data segment interception operation is triggered. The current offset of the same source data stream at the trigger time of the interception is used as the end offset of the continuous data segment, and the end offset of the last interception is used as the start offset of this interception. The continuous data segment interception is completed and the start offset and end offset are recorded synchronously.

[0009] As a preferred embodiment, the step of synchronously distributing data from the same source to the main processing channel and the bypass detection channel, and constructing a dual-channel data association mapping based on a globally unique data identifier, includes the following steps: Enabling the dual-path parallel consumption mode enables the main processing channel and the bypass detection channel to receive the same continuous data segment and bind the globally unique data identifier corresponding to the continuous data segment, thereby obtaining the data consumption status of the main processing channel and the data detection status of the bypass detection channel respectively. The globally unique data identifier, the data offset range of continuous data segments, the data consumption status, and the data detection status are associated and stored in the decision control center to construct a dual-path data association mapping with the globally unique data identifier as the key and the data offset range, data consumption status, and data detection status as the values.

[0010] As a preferred embodiment, the step of locating and loading a pre-configured repair strategy based on the detected anomaly information, and updating the source data with the repaired consumption data using the repair strategy, includes the following steps: In response to the data detection anomaly status reported by the bypass detection channel, the corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The detection anomaly information includes at least the error type, anomaly field, failure record offset, and data segment association information. The pre-configured repair strategy library is matched with the error type and exception field to locate the corresponding repair solution. The repair solution includes high-code scripts, rule templates or AI inference models. The repair scheme is executed to repair the abnormal data segments and generate repaired consumption data; The consumption data is rewritten to the same data topic of the message middleware to update the same source data.

[0011] As a preferred embodiment, an automated ETL data quality inspection method further includes: if the repair strategy fails to execute, determining a graded early warning strategy based on the degree of data anomaly detection, including the following steps: When the repair plan fails, the repair failure information will be associated with a globally unique data identifier in the decision control center; The corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The parsed error type, anomaly impact range and failure frequency are input into the pre-configured early warning rule engine. The anomaly severity level is calculated based on the predefined severity scoring rules in the early warning rule engine. The corresponding early warning level and notification channel are matched according to the level of abnormality.

[0012] Secondly, an embodiment of the present invention also provides a technical solution: an automated ETL data quality inspection system, applicable to the automated ETL data quality inspection method as described in the first aspect, comprising: The data acquisition module is used to receive raw data from heterogeneous data sources, perform standardized processing on it, and write it into the same data topic of the message middleware to construct data from the same source. The main processing module is used to perform data transformation processing and manage data consumption status; The bypass detection module is used to perform quality detection and generate data detection status. The coordination and control module is used to generate a globally unique data identifier based on the data offset range, receive and associate the data consumption status from the main processing module and the data detection status from the bypass detection module to construct a dual-path data association mapping. The decision execution module is used to monitor the status changes in the dual-path data association mapping and trigger the data submission or repair process based on the data detection status. The intelligent repair engine locates and executes repair strategies based on detected anomaly information and feeds back the repaired data to the data acquisition module. The alarm notification module is used to receive quality inspection anomalies and repair failures, and after hierarchical aggregation, issue early warning notifications through preset channels.

[0013] Thirdly, one technical solution provided in this embodiment of the invention is: an electronic device, including a memory and a processor, wherein the memory stores a computer program, and the processor, when calling the computer program in the memory, implements the steps of the ETL automated data quality inspection method as described in the first aspect.

[0014] Fourthly, one technical solution provided in this embodiment of the invention is: a storage medium, characterized in that: the storage medium stores computer-executable instructions, and when the computer-executable instructions are loaded and executed by a processor, the steps of an ETL automated data quality inspection method as described in the first aspect are implemented.

[0015] The present invention has at least the following substantial beneficial effects: (1) In response to the technical bottleneck of low throughput and high latency caused by the serial coupling of existing ETL data processing and quality detection, this application first standardizes the heterogeneous data source and injects it into the message middleware to form the same source data. Then, it generates a globally unique identifier based on the data offset range to achieve accurate association of dual-path data. Finally, it removes the process blockage through the parallel consumption mode of main path processing and bypass detection, and realizes the uncoupled synchronous execution of data processing and quality detection, which significantly improves the throughput and real-time performance of the data pipeline and builds a low-latency, high-throughput ETL data processing link.

[0016] (2) In response to the problem of rigidity and lack of automation in traditional repair strategies, this application relies on a globally unique identifier to accurately extract structured information such as error type and abnormal fields from the decision control center, and matches customized repair solutions such as high-code scripts and AI inference models. The repaired data is rewritten into the same source data theme to complete the iteration, realizing the fully automated closed-loop governance from anomaly detection to intelligent repair and data update, greatly reducing the cost of manual intervention and effectively adapting to the diverse quality problems of complex heterogeneous data sources; (3) In response to the lack of accurate graded early warning and the lag in operation and maintenance response in existing technologies, this application binds the repair failure information with a globally unique identifier to fully trace the abnormal context. The early warning rule engine quantifies and scores the error type, scope of impact and other dimensions to determine the abnormal level, and matches the corresponding early warning level and notification channel. This achieves accurate judgment and targeted early warning of the degree of abnormality, significantly improves the timeliness and pertinence of operation and maintenance response, and reduces the risk of erroneous data propagation.

[0017] The above description of the invention is merely an overview of the technical solution of the present invention. In order to better understand the technical means of the present invention and to implement it in accordance with the contents of the specification, and to make the above and other objects, features and advantages of the present invention more apparent and understandable, specific embodiments of the present invention are described below. Attached Figure Description

[0018] Other features, objects, and advantages of the invention will become more apparent from the following detailed description of non-limiting embodiments with reference to the accompanying drawings. The drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings.

[0019] Figure 1 This is a flowchart of an ETL automated data quality inspection method according to Embodiment 1 of the present invention.

[0020] Figure 2 This is a flowchart of an ETL automated data quality inspection method according to Embodiment 2 of the present invention.

[0021] Figure 3 This is a schematic diagram of an ETL automated data quality inspection system according to Embodiment 3 of the present invention. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only one preferred embodiment of this invention and are only used to explain this invention. They do not limit the scope of protection of this invention. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0023] Before discussing the exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although the flowcharts describe the operations (or steps) as sequential processes, many of the operations (or steps) can be performed in parallel, concurrently, or simultaneously. Furthermore, the order of the operations can be rearranged. The process can be terminated when its operation is completed, but it may also have additional steps not included in the figures; the process may correspond to a method, function, procedure, subroutine, subroutine, etc.

[0024] The existing ETL data quality assurance field faces three core technical barriers: First, the serial architecture barrier, where data processing and quality inspection are deeply coupled, making parallel execution impossible, resulting in low throughput and high latency in the data pipeline, failing to meet real-time processing requirements; second, the intelligent repair barrier, where traditional repair strategies rely on fixed rules, lacking flexible repair capabilities adapted to complex heterogeneous data sources, and detection and repair are disconnected, failing to form an automated closed loop; and third, the collaborative early warning barrier, where dual-path data lacks accurate correlation markers, making it difficult to quantify and assess the degree of anomalies, and the early warning mechanism lacks hierarchical adaptability, leading to delayed operational response. These technical barriers result in erroneous data easily contaminating the data warehouse, high repair costs, and poor decision-making timeliness. Based on this, this implementation plan is proposed.

[0025] Example 1: As Figure 1 As shown in the figure, one technical solution provided in this embodiment of the invention is: an automated ETL data quality inspection method, comprising the following steps: S100: Write data from different data sources into the same data topic of the message middleware to build same-source data.

[0026] It should be noted that, to address the issue of inconsistent data baselines between the main and bypass processing paths due to differences in format and structure of heterogeneous data sources, this embodiment provides an optional implementation: constructing homogeneous data by writing data from different data sources into the same data topic of the message middleware. The specific implementation includes the following steps: The system receives raw data from heterogeneous data sources through a data access interface; it then performs format parsing and standardization preprocessing on the raw data to form data records with a unified data structure. Serialize the data records and publish them to the same data topic that was created beforehand in the message middleware; The data subject serves as the sole data source consumed by both the main processing channel and the bypass detection channel.

[0027] It is understood that this embodiment receives raw data from heterogeneous data sources such as MySQL databases, HDFS file systems, and third-party APIs through a general data access interface. The raw data then undergoes format parsing and standardization preprocessing. For example, the "user_name" field in MySQL and HDFS is standardized to "user_name"; the string date "2025 / 01 / 01" is converted to the standard date format "2025-01-01"; GBK encoded data is converted to UTF-8 encoding; and non-core redundancy such as "remarks" is removed. The fields are used to form data records with completely unified field structure, data type, and encoding format. Finally, the standardized data records are published to a pre-created and unique data topic (such as "etl_unified_data") in message middleware such as Kafka using JSON serialization. This data topic serves as the only data source consumed by both the main processing channel and the bypass detection channel. Through the technical principle of same-source dual-stream distribution, seamless adaptation of heterogeneous data sources is achieved, and the consistency of dual-process data is ensured. This avoids the distortion of detection results caused by differences in data benchmarks and lays a reliable data foundation for subsequent parallel processing and accurate association mapping.

[0028] S200: Generate a globally unique data identifier based on the data offset range of the same source data, synchronously distribute the same source data to the main processing channel and the bypass detection channel, and construct a dual-path data association mapping based on the globally unique data identifier.

[0029] It should be noted that, to address the issues of difficulty in accurately associating the same data segments and the lack of globally unified identifiers in dual-path parallel processing mode, this embodiment provides an optional implementation: generating globally unique data identifiers based on the data offset range of data from the same source. The specific implementation includes the following steps: Listen to the same data streams of the same data topic in the message middleware, extract continuous data segments from the same data streams according to the preset micro-batch division rules, and record the start and end offsets of the continuous data segments. Extract the data topic identifier and data segment generation timestamp, and combine them with the start offset and end offset to construct basic identifier elements; A fixed-length, globally unique data identifier is generated by performing encryption operations on the basic identifier elements using a hash algorithm.

[0030] Understandably, this embodiment constructs a data stream from the same data topic (e.g., "etl_unified_data") within a message listening middleware (e.g., Kafka), and extracts continuous data segments according to preset micro-batch segmentation rules (e.g., a data volume threshold of 1000 records or a time window of 1 minute). For example, when the cumulative consumed data volume reaches 1000 records, the starting offset of the data segment is recorded as 1000 and the ending offset as 2000, thus achieving ordered segmentation and range definition of the data. Then, the data topic identifier (e.g., "etl_unified_data") and the data segment generation timestamp (e.g., "20250520143000") are extracted and used together with the aforementioned starting offset of 1000 and ending offset of 2000 to construct a basic identifier element set, ensuring the uniqueness and relevance of the identifier elements. Finally, the SHA-256 hash algorithm is used to encrypt the basic identifier elements, mapping the variable-length element set to a fixed 64-bit string (e.g., "7a3f9d···") using a hash function, generating a globally unique data identifier. By segmenting rules to solidify the data range, integrating multi-dimensional elements to ensure identifier differentiation, and using hash algorithms to guarantee the uniqueness and fixed length of the identifier, the system ultimately achieves accurate association and globally unique traceability of the same data segment in dual channels, providing a reliable identifier foundation for subsequent dual-channel data association mapping, abnormal data tracing, and repair.

[0031] It should be noted that, in order to solve the problem of rigid segmentation methods for data streams from the same source and unclear definition of data segment ranges, which leads to difficulties in accurate synchronization of dual-path processing, this embodiment provides an optional embodiment: extracting continuous data segments from the data stream from the same source according to a preset micro-batch partitioning rule and recording the start and end offsets of the continuous data segments. The specific implementation includes the following steps: Configure micro-batch partitioning rules, which include data volume threshold rules and time window threshold rules; When the cumulative consumed data volume of the same source data stream monitored in real time reaches the data volume threshold rule, or when the current consumption time reaches the time window threshold rule, the data segment interception operation is triggered. The current offset of the same source data stream at the trigger time of the interception is used as the end offset of the continuous data segment, and the end offset of the last interception is used as the start offset of this interception. The continuous data segment interception is completed and the start offset and end offset are recorded synchronously.

[0032] It is understood that this embodiment configures micro-batch partitioning rules, including data volume threshold rules (such as setting a single batch data volume threshold of 1000 records to adapt to high-throughput scenarios) and time window threshold rules (such as setting a time window threshold of 1 minute to adapt to low-frequency scenarios), allowing users to flexibly choose to enable them according to business needs; and then monitors the consumption status of the same source data stream in the message middleware in real time. When the cumulative consumed data volume reaches 1000 records (meeting the data volume threshold rule), or when 1 minute has passed since the last truncation (meeting the time window threshold rule), a data segment truncation operation is triggered when either condition is met, thus avoiding data loss during high throughput. Resources are idle when there is backlog or low frequency; finally, the current offset of the same source data stream at the trigger time (e.g., the offset at the trigger time is 1000) is used as the end offset of this data segment, and the end offset of the last interception (the last end offset at the initial interception is 0, and subsequent interceptions directly inherit the previous end offset, for example, the previous end offset is 1000, and the current starting offset is 1000) is used as the starting offset of this interception, and the continuous data segment interception is completed and recorded synchronously (e.g., the initial segment is 0-1000, and the current offset is 1800 when the next time is triggered due to the time window, then it is recorded as 1000-1800). By covering different data transmission scenarios with dual rules, balancing data segment integrity and truncation timeliness with an "OR logic" trigger mechanism, and achieving continuous and non-overlapping division of data segments through offset inheritance relationships, the system ultimately achieves flexible adaptation, precise definition, and orderly segmentation of data streams from the same source. This provides clear and consistent data range support for subsequent construction of globally unique identifiers and precise association of dual-path data, ensuring the synchronization of dual-path processing and the accuracy of data traceability.

[0033] It should be noted that, in order to address the problem of the lack of a unified basis for the association between data segments and dual-path states in the dual-path parallel processing mode, and the resulting information dispersion leading to the main path's inability to accurately monitor the bypass detection results, this embodiment provides an optional implementation: synchronously distributing data from the same source to both the main path processing channel and the bypass detection channel, and constructing a dual-path data association mapping based on a globally unique data identifier. The specific implementation includes the following steps: Enabling the dual-path parallel consumption mode enables the main processing channel and the bypass detection channel to receive the same continuous data segment and bind the globally unique data identifier corresponding to the continuous data segment, thereby obtaining the data consumption status of the main processing channel and the data detection status of the bypass detection channel respectively. The globally unique data identifier, the data offset range of continuous data segments, the data consumption status, and the data detection status are associated and stored in the decision control center to construct a dual-path data association mapping with the globally unique data identifier as the key and the data offset range, data consumption status, and data detection status as the values.

[0034] It is understood that this embodiment enables a dual-path parallel consumption mode, allowing the main processing channel and the bypass detection channel to simultaneously subscribe to the same data topic of the message middleware, synchronously receive continuous data segments with an offset range of 0-1000, and bind the corresponding globally unique data identifier (such as "7a3f9d..." generated by SHA-256 encryption) to the data segment. At the same time, the main path data consumption status (such as "consumption completed") and the bypass data detection status (such as "detection normal" or "field missing anomaly") are obtained respectively. Then, the globally unique data identifier, data segment offset range (0-1000), main path consumption status, and bypass detection status are associated and stored in the decision control center (such as Redis), constructing a key-value pair dual-path data association mapping with the globally unique data identifier as the key and the remaining information as the value. By using a globally unique identifier to achieve precise binding of dual-path data segments and by integrating dispersed status information through centralized association storage, the main path can quickly query the corresponding detection status through the identifier. Ultimately, this achieves precise association of dual-path data and centralized status management, ensuring the accuracy and timeliness of the main path's monitoring of the bypass detection results, and providing efficient data support for subsequent data submission or repair process decisions.

[0035] S300: The main processing channel monitors the detection results of the bypass detection channel based on the dual-channel data association mapping. If the data detection is normal, the consumption data in the main processing channel is submitted to the data warehouse. If the data detection is abnormal, the pre-configured repair strategy is located and loaded according to the detection abnormality information. The consumption data repaired by the repair strategy is then used to update the same source data.

[0036] It should be noted that, in order to address the problems of rigidity in traditional repair strategies, the disconnect between detection and repair processes, and the inability to achieve accurate and automated repair for different anomaly types, this embodiment provides an optional implementation: Based on the detected anomaly information, a pre-configured repair strategy is located and loaded; the consumer data repaired using the repair strategy is then used to update the source data. The specific implementation includes the following steps: In response to the data detection anomaly status reported by the bypass detection channel, the corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The detection anomaly information includes at least the error type, anomaly field, failure record offset, and data segment association information. The pre-configured repair strategy library is matched with the error type and exception field to locate the corresponding repair solution. The repair solution includes high-code scripts, rule templates or AI inference models. The repair scheme is executed to repair the abnormal data segments and generate repaired consumption data; The consumption data is rewritten to the same data topic of the message middleware to update the same source data.

[0037] Understandably, in this embodiment, in response to anomaly reports from the bypass detection channel, complete anomaly information is obtained from the decision control center based on a globally unique data identifier (such as "7a3f9d..." corresponding to the data segment with offset 0-1000). This information includes the error type (such as "field missing" or "data format error"), the abnormal field (such as "user_phone"), the offset of the failed record (such as 800-850), and data segment association information, enabling precise anomaly localization. Then, a pre-configured repair strategy library is matched according to the error type and abnormal field. For example, for "user_phone field missing", a Pyth algorithm is matched. The script uses a high-level code (calling the operator's API to complete the mobile phone number based on the user's ID number), matches rule templates for "incorrect date format (YYYY / MM / DD to YYYY-MM-DD)," and uses an AI inference model (calculating reasonable values ​​based on historical data of the same user) to match "hidden outliers," achieving precise adaptation of the repair solution. Subsequently, the corresponding repair solution is executed to specifically process the abnormal data segment with an offset of 800-850, generating standardized and complete repaired consumption data. Finally, the repaired data is rewritten to the same data topic in the message middleware, overwriting the original abnormal data segment to update the same-source data. By using a globally unique identifier to achieve precise source tracing of abnormal information, by using multiple types of repair solutions to cover different abnormal scenarios, and by using data write-back to achieve iterative optimization of the same-source data, this system ultimately achieves fully automated closed-loop governance from anomaly detection to precise repair and data update, significantly reducing the cost of manual intervention, effectively adapting to diverse quality issues of complex heterogeneous data sources, and ensuring the accuracy and completeness of inbound data.

[0038] Example 2: As a supplementary example to Example 1, without changing the original technical logic of Example 1, this example further provides a supplementary example, such as... Figure 2 As shown, the specific execution is performed after step S300: S400. If the repair strategy fails, a graded early warning strategy is determined based on the degree of data anomaly detection, specifically including the following steps: When the repair plan fails, the repair failure information will be associated with a globally unique data identifier in the decision control center; The corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The parsed error type, anomaly impact range and failure frequency are input into the pre-configured early warning rule engine. The anomaly severity level is calculated based on the predefined severity scoring rules in the early warning rule engine. The corresponding early warning level and notification channel are matched according to the level of abnormality.

[0039] Understandably, to address the issues of inaccurate quantification of anomalies after repair strategy failures, delayed operational responses due to limited early warning methods, and consequently, the spread of erroneous data, this embodiment addresses the failure scenarios of anomaly data repair (such as the failure of a mobile number completion repair scheme for the offset 0-1000 data segment). It binds the "repair script execution timeout" failure information to a globally unique identifier for that data segment (e.g., "7a3f9d...") to ensure the uniqueness of anomaly tracing. Based on this globally unique data identifier, complete anomaly detection information is obtained from the decision control center, parsing out the error type (e.g., "field missing," weighted 0.4), the scope of anomaly impact (e.g., "core transaction module, involving 100 user data entries," weighted 0.3), and the failure frequency ("3 consecutive repair failures," weighted 0.3). These dimensional parameters are input into a pre-configured early warning rule engine, and the system calculates the severity score according to predefined severity scoring rules (anomaly severity score = error type weight). ×Type coefficient + Impact range weight × Range coefficient + Failure frequency weight × Frequency coefficient, for example, if the field missing type coefficient is 0.8, the core module range coefficient is 1.0, and the 3 failure frequency coefficient is 0.9, the calculated score is 0.4×0.8+0.3×1.0+0.3×0.9=0.89), and the score is mapped to the "Severe" anomaly level; as shown in Table 1, the graded early warning strategy comparison table is shown. Finally, according to the "Severe" level, the corresponding early warning level (Level 3 early warning) and notification channel (Enterprise WeChat emergency push + SMS) are matched, and early warning information containing a globally unique identifier, anomaly details and processing priority is pushed simultaneously. The globally unique identifier enables complete traceability of anomaly information, the multi-dimensional weighted scoring enables accurate quantification of anomaly degree, and the binding of level and channel enables targeted push of early warning. In the end, the scientific judgment of anomaly degree and the reasonable allocation of operation and maintenance resources are realized, which greatly improves the timeliness and pertinence of anomaly response and effectively curbs the further spread of erroneous data.

[0040] Table 1. Comparison of Tiered Early Warning Strategies Abnormality score range Warning level Notification channels [0,0.3) Level 1 Warning Internal message + corporate email (including details of the issue and handling suggestions) [0.3,0.7) Level 2 warning Internal messages + corporate email + SMS (simultaneously pushing globally unique identifiers and scope of impact) [0.7,1] Level 3 Warning Emergency push notifications via WeChat / DingTalk, SMS, and corporate email (including issue priority and emergency response plan) Example 3: Another technical solution provided in this embodiment of the invention is: an automated ETL data quality inspection system, applicable to an automated ETL data quality inspection method as described in Example 1 or Example 2, comprising: The data acquisition module is used to receive raw data from heterogeneous data sources, perform standardized processing on it, and write it into the same data topic of the message middleware to construct data from the same source. The main processing module is used to perform data transformation processing and manage data consumption status; The bypass detection module is used to perform quality detection and generate data detection status. The coordination and control module is used to generate a globally unique data identifier based on the data offset range, receive and associate the data consumption status from the main processing module and the data detection status from the bypass detection module to construct a dual-path data association mapping. The decision execution module is used to monitor the status changes in the dual-path data association mapping and trigger the data submission or repair process based on the data detection status. The intelligent repair engine locates and executes repair strategies based on detected anomaly information and feeds back the repaired data to the data acquisition module. The alarm notification module is used to receive quality inspection anomalies and repair failures, and after hierarchical aggregation, issue early warning notifications through preset channels.

[0041] This embodiment achieves at least the following substantial technical effects: (1) This system builds a common source data foundation through the data acquisition module, the main processing module and the bypass detection module consume the common source data in parallel, and the coordination control module builds a dual-path association mapping based on the globally unique data identifier, realizing the decoupled synchronous execution of data processing and quality detection, significantly improving the data throughput and processing real-time performance of the system, and breaking the process blocking limitation of the traditional serial architecture; (2) This system monitors the abnormal state in the dual-path association mapping through the decision execution module, triggers the intelligent repair engine to accurately match the repair scheme based on the detected abnormal information, and the repaired data is rewritten into the same source data theme through the data acquisition module to complete the iteration, which greatly reduces the cost of manual intervention and effectively adapts to the diverse quality problems of heterogeneous data sources. (3) This system receives quality inspection anomalies and repair failure events through the alarm notification module. Combined with the globally unique data identifier and anomaly context information associated with the coordination and control module, the system performs anomaly classification and aggregation and pushes early warnings through preset channels. This achieves accurate judgment of the degree of anomaly and targeted notification, improves the timeliness and pertinence of operation and maintenance response, effectively curbs the risk of erroneous data propagation, and ensures the reliability of data warehouse data.

[0042] Example 4: One technical solution provided in this embodiment of the invention is: an electronic device, including a memory and a processor, wherein the memory stores a computer program, and the processor, when calling the computer program in the memory, implements the steps of an ETL automated data quality inspection method as described in Example 1 or Example 2.

[0043] Example 5: One technical solution provided in this embodiment of the invention is a storage medium, characterized in that: the storage medium stores computer-executable instructions, and when the computer-executable instructions are loaded and executed by a processor, the steps of an ETL automated data quality inspection method as described in Example 1 or Example 2 are implemented.

[0044] Through the above description of the embodiments, those skilled in the art will understand that, for the sake of convenience and brevity, only the division of the above functional modules is used as an example. In actual applications, the above functions can be assigned to different functional modules as needed, that is, the internal structure of the specific device can be divided into different functional modules to complete all or part of the functions described above.

[0045] In the embodiments provided in this application, it should be understood that the disclosed structures and methods can be implemented in other ways. For example, the structural embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another structure, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces, or indirect coupling or communication connection between structures or units, and may be electrical, mechanical, or other forms.

[0046] The units described as separate components may or may not be physically separate. A component shown as a unit can be one or more physical units; that is, it can be located in one place or distributed in multiple different locations. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0047] Furthermore, in the embodiments of this application, the functional units can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0048] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a readable storage medium. Based on this understanding, the technical solutions of the embodiments of this application, in essence, or the parts that contribute to the prior art, or all or part of the technical solutions, can be embodied in the form of a software product. This software product is stored in a storage medium and includes several instructions to cause a device (which may be a microcontroller, chip, etc.) or processor to execute all or part of the steps of the methods of the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0049] The specific embodiments described above are preferred embodiments of the ETL automated data quality inspection method, system, electronic equipment and medium of the present invention, and are not intended to limit the specific scope of the present invention. The scope of the present invention includes but is not limited to the specific embodiments described above. All equivalent changes made in accordance with the shape and structure of the present invention are within the protection scope of the present invention.

Claims

1. An automated ETL data quality inspection method, characterized in that: Includes the following steps: Write data from different data sources into the same data topic in the message middleware to build data from the same source; A globally unique data identifier is generated based on the data offset range of the same source data. The same source data is synchronously distributed to the main processing channel and the bypass detection channel, and a dual-channel data association mapping is constructed based on the globally unique data identifier. The main processing channel monitors the detection results of the bypass detection channel based on the dual-channel data association mapping. If the data detection is normal, the consumed data in the main processing channel is submitted to the data warehouse. If the data detection is abnormal, the abnormal information is used to locate and load the pre-configured repair strategy, and the consumed data repaired by the repair strategy is used to update the same source data.

2. The ETL automated data quality inspection method according to claim 1, characterized in that: The process of writing data from different data sources into the same data topic of the message middleware to construct data from the same source includes the following steps: The system receives raw data from heterogeneous data sources through a data access interface; it then performs format parsing and standardization preprocessing on the raw data to form data records with a unified data structure. Serialize the data records and publish them to the same data topic that was created beforehand in the message middleware; The data subject serves as the sole data source consumed by both the main processing channel and the bypass detection channel.

3. The ETL automated data quality inspection method according to claim 1, characterized in that: The process of generating a globally unique data identifier based on the data offset range of the same source data includes the following steps: Listen to the same data streams of the same data topic in the message middleware, extract continuous data segments from the same data streams according to the preset micro-batch division rules, and record the start and end offsets of the continuous data segments. Extract the data topic identifier and data segment generation timestamp, and combine them with the start offset and end offset to construct basic identifier elements; A fixed-length, globally unique data identifier is generated by performing encryption operations on the basic identifier elements using a hash algorithm.

4. The ETL automated data quality inspection method according to claim 3, characterized in that: The step of extracting continuous data segments from the same data stream according to a preset micro-batch partitioning rule and recording the start and end offsets of the continuous data segments includes the following steps: Configure micro-batch partitioning rules, which include data volume threshold rules and time window threshold rules; When the cumulative consumed data volume of the same source data stream monitored in real time reaches the data volume threshold rule, or when the current consumption time reaches the time window threshold rule, the data segment interception operation is triggered. The current offset of the same source data stream at the trigger time of the interception is used as the end offset of the continuous data segment, and the end offset of the last interception is used as the start offset of this interception. The continuous data segment interception is completed and the start offset and end offset are recorded synchronously.

5. The ETL automated data quality inspection method according to claim 1 or 3, characterized in that: The step of synchronously distributing data from the same source to the main processing channel and the bypass detection channel, and constructing a dual-channel data association mapping based on a globally unique data identifier, includes the following steps: Enabling the dual-path parallel consumption mode enables the main processing channel and the bypass detection channel to receive the same continuous data segment and bind the globally unique data identifier corresponding to the continuous data segment, thereby obtaining the data consumption status of the main processing channel and the data detection status of the bypass detection channel respectively. The globally unique data identifier, the data offset range of continuous data segments, the data consumption status, and the data detection status are associated and stored in the decision control center to construct a dual-path data association mapping with the globally unique data identifier as the key and the data offset range, data consumption status, and data detection status as the values.

6. The ETL automated data quality inspection method according to claim 1, characterized in that: The step of locating and loading a pre-configured repair strategy based on the detected anomaly information, and then updating the source data with the repaired consumer data using the repair strategy, includes the following steps: In response to the data detection anomaly status reported by the bypass detection channel, the corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The detection anomaly information includes at least the error type, anomaly field, failure record offset, and data segment association information. The pre-configured repair strategy library is matched with the error type and exception field to locate the corresponding repair solution. The repair solution includes high-code scripts, rule templates or AI inference models. The repair scheme is executed to repair the abnormal data segments and generate repaired consumption data; The consumption data is rewritten to the same data topic of the message middleware to update the same source data.

7. The ETL automated data quality inspection method according to claim 1, characterized in that: Also includes: If the repair strategy fails, a tiered early warning strategy is determined based on the degree of data anomaly detection, including the following steps: When the repair plan fails, the repair failure information will be associated with a globally unique data identifier in the decision control center; The corresponding detection anomaly information is obtained from the decision control center based on the globally unique data identifier. The parsed error type, anomaly impact range and failure frequency are input into the pre-configured early warning rule engine. The anomaly severity level is calculated based on the predefined severity scoring rules in the early warning rule engine. The corresponding early warning level and notification channel are matched according to the level of abnormality.

8. An automated ETL data quality inspection system, applicable to the automated ETL data quality inspection method as described in any one of claims 1 to 7, characterized in that: include: The data acquisition module is used to receive raw data from heterogeneous data sources, perform standardized processing on it, and write it into the same data topic of the message middleware to construct data from the same source. The main processing module is used to perform data transformation processing and manage data consumption status; The bypass detection module is used to perform quality detection and generate data detection status. The coordination and control module is used to generate a globally unique data identifier based on the data offset range, receive and associate the data consumption status from the main processing module and the data detection status from the bypass detection module to construct a dual-path data association mapping. The decision execution module is used to monitor the status changes in the dual-path data association mapping and trigger the data submission or repair process based on the data detection status. The intelligent repair engine locates and executes repair strategies based on detected anomaly information and feeds back the repaired data to the data acquisition module. The alarm notification module is used to receive quality inspection anomalies and repair failures, and after hierarchical aggregation, issue early warning notifications through preset channels.

9. An electronic device, characterized in that: It includes a memory and a processor, wherein the memory stores a computer program, and the processor, when calling the computer program in the memory, implements the steps of an ETL automated data quality inspection method as described in any one of claims 1 to 7.

10. A storage medium, characterized in that: The storage medium stores computer-executable instructions, which, when loaded and executed by a processor, implement the steps of an ETL automated data quality inspection method as described in any one of claims 1 to 7.

Images