Data quality monitoring method, system, device and storage medium for data warehouse

Abstract

The present application relates to a kind of data warehouse data quality monitoring method, system, equipment and storage medium.The data stored in data warehouse is the target result data obtained by data extraction, calculation of target source table in business database by ETL task, the data quality monitoring method includes: the check rule of the ETL task is generated based on the sample data table of the ETL task;Monitoring the data change information of business database;Based on the check rule of the ETL task, multi-level consistency check is carried out;And before the target result data is output from data warehouse, baseline detection is carried out on the target result data.The present application realizes the full-link quality monitoring of data, improves the data quality in data warehouse, improves the timeliness of data problem discovery, and reduces the labor cost.

Description

Technical Field

[0001] This invention relates to the field of data warehouse technology, and particularly to a data quality monitoring method, system, device, and storage medium for a data warehouse. Background Technology

[0002] A data warehouse is the core foundation for enterprise digital data analysis and intelligent decision-making. It integrates various scattered and heterogeneous raw business data to provide standardized, high-quality data support for upper-level data dashboards, business reports, and data analysis applications. In the data warehouse construction process, ETL (Extract-Transform-Load) is the core data processing flow. It extracts, cleans, transforms, standardizes, integrates, and loads multi-source raw business data into the warehouse, transforming messy and heterogeneous raw data into standardized, structured data assets directly usable for analysis, ultimately generating standardized result tables. The ETL result table, as the core output of the ETL task, is a critical node in the data warehouse's data flow. Its data quality directly determines the accuracy and effectiveness of subsequent data analysis, business statistics, and operational decisions. Therefore, efficient, accurate, and end-to-end data quality monitoring is crucial for ensuring the stable operation of the data warehouse.

[0003] Currently, the industry primarily employs two methods for data quality monitoring of data warehouse ETL tasks. The first is the offline post-verification mode, which involves performing batch offline data verification on the final result table after the entire ETL task has been completed and the result table has been generated. This method is typically scheduled daily, with only one full data quality verification performed per day. The second is the single-point pre-verification mode, which pre-sets fixed verification rules at a single, fixed stage of the ETL process, performing quality verification only on the data processed at that current stage, thus achieving data quality control only at a local node.

[0004] The aforementioned data quality monitoring solutions each have certain shortcomings as the data volume increases. For example, the offline post-verification mode requires waiting for all ETL tasks to complete before the verification process can begin, resulting in a fixed time delay in verification scheduling, delayed detection of data problems, and significant loss of data timeliness. The single-point pre-verification mode, on the other hand, cannot achieve end-to-end cross-stage data consistency verification. Data is generated from raw data in the business database, undergoes access transmission, multiple rounds of ETL calculations and processing, and is finally pushed to the application layer. It involves multiple systems and multiple rounds of data transformation and processing, resulting in numerous nodes and complex data flow links throughout the entire chain. Because the single-point pre-verification mode only achieves partial verification of a single stage or the final result table, it cannot compare the consistency between downstream displayed data and upstream raw data. Therefore, issues such as upstream-downstream data deviation and data distortion are difficult to detect in a timely manner, easily leading to mismatches between upper-layer displayed data and lower-layer raw business data, causing data statistical distortion.

[0005] Furthermore, under the existing mechanism, data operations personnel need to manually sort out the field structure of newly added business data tables, analyze the data characteristics of each field one by one, and manually configure the corresponding quality verification rules according to the business scenario. In application scenarios where the number of enterprise data tables is large and data is frequently iterated, manual configuration is labor-intensive, costly, and inefficient. Moreover, manual operation is easily affected by subjective experience and operational negligence, which can easily lead to omissions.

[0006] Furthermore, when data reports and dashboards exhibit data anomalies, relying solely on the personal experience of data development and operations personnel to manually investigate and verify data flow layer by layer from the downstream application layer to the upstream original business database is cumbersome, time-consuming, and reduces the efficiency of data problem repair. This affects the stable and efficient operation of the data warehouse system and cannot meet the routine high-quality operation and maintenance needs of enterprises with massive amounts of data. Summary of the Invention

[0007] To address the technical problems existing in the prior art, this invention proposes a data quality monitoring method, system, device, and storage medium for data warehouses, which can perform end-to-end quality monitoring of data, improve the timeliness of data problem detection, and reduce labor costs.

[0008] To address the aforementioned technical problems, according to one aspect of the present invention, a data quality monitoring method for a data warehouse is provided, wherein the data stored in the data warehouse is target result data obtained by ETL tasks from extracting and calculating data from target source tables in a business database, and the data quality monitoring method includes: The validation rules for the ETL task are generated based on the sample data table of the ETL task. Monitor data change information in the business database; Multi-level consistency checks are performed based on the verification rules of the ETL task; and Baseline detection is performed on the target result data before it is output from the data warehouse.

[0009] Optionally, the steps for monitoring data change information in the business database include: Subscribe to table structure change events and table data change events in the business database operation log; Obtain table structure change information based on received table structure change events; and Retrieve table data change information based on received data change events.

[0010] Optionally, after obtaining table structure change information based on the received table structure change event, the process further includes: The table structure change information is classified into alert levels to obtain the corresponding alert levels and alert information; and The warning information is distributed through the channels corresponding to the warning level.

[0011] Optionally, after obtaining table data change information based on the received data change event, the process further includes: Perform anomaly detection on table data change information; and In response to the detection result indicating abnormal changes in table data, an early warning message is generated and distributed through the corresponding channels.

[0012] Optionally, after generating the validation rules for the ETL task based on the sample data table of the ETL task, the validation rules are determined as candidate rules, and the method further includes: Within the first preset time window, candidate rules are validated based on the actual validation rules; Evaluate whether the candidate rules meet the preset confidence conditions; and In response to the candidate rule satisfying the confidence condition, a verification rule for multi-level consistency verification of the ETL task is determined based on the candidate rule.

[0013] Optionally, the validation rules include table field validation rules, and the steps for generating the validation rules for the ETL task based on the sample data table of the ETL task include: Collect field metadata from the sample data table; Multiple data entries for each field are sampled to obtain multiple sample values ​​for that field; Based on the data type of the fields, statistical features of the corresponding type are calculated for multiple sample values ​​of each field; and The primary key of the validation rule is determined based on the field metadata, and the rule parameters and parameter values ​​are determined based on the statistical feature calculation results.

[0014] Optionally, the verification rules include table data volume verification rules, and the steps of generating the verification rules for the ETL task based on the sample data table of the ETL task include: Count the number of data rows in the sample data table corresponding to each execution of the ETL task within the second preset time window; or count the number of data rows in the sample data table for each execution of the preset number of ETL task executions; and The parameter values ​​in the data volume validation rules are determined based on the number of rows counted.

[0015] Optionally, the sample data table includes a target source table for the ETL task and a target result table for storing target result data.

[0016] Optionally, the verification rules include aggregated metric verification rules, and the step of generating the verification rules for the ETL task based on the sample data table of the ETL task includes: Collect metadata from the aggregated metric fields in the ETL task results table; Statistically analyze the aggregated metrics field data for each ETL task executed within the third preset time window; Data feature values ​​of aggregated indicator fields based on preset time windows for statistical analysis of aggregated indicator fields; The primary key of the validation rule is determined based on the metadata of the aggregated indicator field, and the rule parameters and parameter values ​​are determined based on the data feature values.

[0017] Optionally, performing multi-level consistency checks on the ETL task based on the verification rules includes: The amount of data in the target table of the ETL task is verified to obtain the verification result of the data amount in the target table; the target table is the target source table and / or the result table. The target data in the target table of the ETL task is validated to obtain the validation results of the target table field data. The target table is the target source table and / or the result table. The aggregated metric fields in the ETL task result table are validated to obtain the validation results of the aggregated metrics.

[0018] Optionally, in response to an anomaly in the verification result at any level, a multi-level root cause investigation can be performed.

[0019] Optionally, the steps for multi-level root cause investigation include: Determine whether data change information has been monitored; In response to the detected data change information, it was determined that the abnormal verification result was caused by changes in the business database. In response to the absence of monitored data changes, retrieve the ETL task status; In response to the ETL task being in a failed state, the reason for the abnormal verification result is determined to be an abnormal computation task. In response to the ETL task being in a successful state, the computational logic information of the ETL task is obtained and it is determined whether the computational logic information of the ETL task is the same as the computational logic information of the sample data table. In response to the discrepancy between the computational logic information of the ETL task and the computational logic information of the sample data table, the cause of the abnormal verification result is determined to be a change in the computational logic. In response to the fact that the computational logic information of the ETL task is the same as the computational logic information of the sample data table, an early warning message is generated and released through a preset channel.

[0020] Optionally, the step of baseline detection on the target result data includes: Calculate the baseline value of the target result data in the fourth preset time window; Calculate the error between the target result data and its baseline value; and in response to the error satisfying the error condition corresponding to the handling method, perform corresponding handling on the target result data, wherein the handling method includes one or more of the following: adding a warning mark to the target result data, blocking the output of the target result data, and generating corresponding warning information.

[0021] Optionally, the method further includes: creating a monitoring and handling tracking process, which includes multiple processing nodes, and distributing early warning information based on the preset processing rules of the processing nodes.

[0022] Optionally, the method further includes the step of determining a sample data table: in response to the first successful completion of the ETL task, the target source table of the first successfully completed ETL task and the target result table storing the target result data are determined as the sample data table.

[0023] Optionally, the target source table of the first successfully completed ETL task is determined as a candidate sample data table, and the method further includes: verifying whether the target source table is a newly created data table in the business database; in response to the target source table being a newly created data table in the business database, the target source table is determined as a sample data table.

[0024] Optionally, when verifying whether the target source table is a newly created data table in the business database, the data table creation event of the business database is captured. In response to the capture of the data table creation event information, and the data table identifier in the data table creation event information is the same as the target source table, the target source table is determined as the sample data table.

[0025] According to another aspect of the present invention, the present invention also provides a data quality monitoring system for a data warehouse, comprising: The rule creation module is configured to generate validation rules for the ETL task based on the sample data table of the ETL task. The data access monitoring module is configured to monitor data change information in the business database. A multi-level verification module is configured to perform multi-level consistency verification based on the verification rules of the ETL task; The output monitoring module is configured to perform baseline detection on the target result data before the target result data of the ETL task is output from the data warehouse.

[0026] According to another aspect of the present invention, an electronic device is also provided, comprising a processor and a memory, wherein a set of computer program instructions is stored in the memory, and the aforementioned data quality monitoring method is implemented when the processor executes the set of computer program instructions in the memory.

[0027] According to another aspect of the present invention, the present invention also provides a computer-readable storage medium, wherein a computer program instruction set is stored on the computer-readable storage medium, and the computer program instruction set performs the aforementioned data quality monitoring method when executed by a processor.

[0028] According to another aspect of the present invention, the present invention also provides a computer program product comprising a computer program instruction set, which, when executed by a processor, implements the aforementioned data quality monitoring method.

[0029] This invention enables end-to-end quality monitoring of data, eliminating the need for manual creation of verification rules, reducing labor costs, and ensuring timely detection and handling of data issues, thereby improving the data quality and operational stability of the data warehouse. Attached Figure Description

[0030] The preferred embodiments of the present invention will now be described in further detail with reference to the accompanying drawings, wherein: Figure 1 This is a schematic diagram of a data system architecture according to an embodiment of the present invention; Figure 2 This is a flowchart of a data warehouse data quality monitoring method according to an embodiment of the present invention; Figure 3 This is a flowchart of the verification rule generation process for an ETL task according to an embodiment of the present invention; Figure 4 This is a flowchart of a method for processing event change information in a business database according to an embodiment of the present invention; Figure 5 This is a flowchart of a root cause investigation method for a verification anomaly message according to an embodiment of the present invention; Figure 6 This is a block diagram illustrating the principle of a data warehouse data quality monitoring system according to an embodiment of the present invention; and Figure 7 This is a schematic diagram of the hardware structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0031] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0032] In the following detailed description, reference can be made to the accompanying drawings, which form part of this application and illustrate specific embodiments of the present application. In the drawings, similar reference numerals describe substantially similar components in different figures. Specific embodiments of the present application are described in sufficient detail below to enable those skilled in the art to implement the technical solutions of the present application. It should be understood that other embodiments may also be utilized, or structural, logical, or electrical changes may be made to the embodiments of the present application.

[0033] See Figure 1 , Figure 1 This is a schematic diagram of a data system architecture according to an embodiment of the present invention. In this embodiment, the data system includes an ETL scheduling system 100, a business database 200, and a data warehouse 300. The ETL scheduling system 100 can be hosted by a terminal device, while the business database 200 and data warehouse 300 are hosted by one or more servers, respectively. Data in the business database 200 is stored, for example, in the form of data tables. For a recruitment service platform, its business database 200 may include, for example, a recruitment database, a resume database, a company database, etc., and the data tables may include, for example, a table of all job seekers, a table of all recruiting companies, a table of all HR personnel, a table of all job postings, a job application table, a table of job seekers who have submitted resumes, etc. The ETL scheduling system 100 is used to manage and schedule ETL tasks. Each ETL task has a corresponding target business database, a target source table, computational logic, a result table for storing task results, the storage address of its data warehouse, and execution conditions, etc. Once an ETL task is registered and configured in the ETL scheduling system 100, the ETL scheduling system 100 executes the ETL task based on the configured execution conditions, extracts and calculates the data in the target source table according to the calculation logic, and then stores the results in the result table of the data warehouse 300.

[0034] See Figure 2 , Figure 2 This is a flowchart of a data warehouse data quality monitoring method according to an embodiment of the present invention, which specifically includes the following steps: Step S1: Generate the verification rules for the ETL task based on the sample data table of the ETL task.

[0035] Step S2: Monitor data change information in the business database.

[0036] Step S3: Perform multi-level consistency verification based on the verification rules of the ETL task.

[0037] Step S4: Before the target result data is output from the data warehouse, baseline detection is performed on the target result data.

[0038] In this embodiment, the data changes of the data source of the ETL task are monitored, and multi-level consistency checks are performed on the ETL task. Baseline detection is performed before the result data is output from the data warehouse, realizing full-link quality monitoring of the data. The verification rules are automatically generated based on the sample data table, eliminating the need for manual generation, thus reducing labor costs and enabling timely detection of data problems, improving the timeliness of problem detection.

[0039] See Figure 3 , Figure 3This is a flowchart illustrating the generation of validation rules for an ETL task according to an embodiment of the present invention. In this embodiment, when a new ETL task is added, the target source table and the target result table storing the target result data of the new ETL task are respectively determined as sample data tables. In one embodiment, when a new ETL task is added to the ETL scheduling system 100, an "Enable Smart Monitoring" switch is configured for the task, and a messaging system, such as a Kafka system, is connected. When the new ETL task is successfully completed for the first time, as a message producer, a message is published to a Topic in the Kafka system, such as rule_engine_trigger, for example: {"trigger":"ETL_FIRST_SUCCESS","db":"recruit_db","table":"job_apply","target_db":"dw","target_table":"dwd_job_apply","row_count":52341, "time":"2026-06-08 02:15:00"}. The Kafka system acts as a message broker, receiving, storing, and transmitting published messages. The data quality monitoring system of this invention, acting as a message consumer, subscribes to the Topic, generates verification rules upon receiving a trigger message, and then performs data quality verification. To verify the quality of data generated by an ETL task, the source and result tables of the ETL task are first used as sample data tables to generate corresponding verification rules. Then, the data from subsequent ETL tasks is verified based on these verification rules. In another embodiment, for ETL tasks of the same type, verification rules generated from other ETL tasks can be used.

[0040] In this embodiment, three types of verification rules are generated based on the sample data table, specifically including the following steps: Step S11: Collect the field metadata of the sample data table.

[0041] Step S12: Determine whether the sample data table is a result table. Since the field values ​​in the target source table are data that has not undergone ETL calculation, while the aggregated metric field values ​​in the result table are data that has undergone ETL calculation, their verification logic is different. Therefore, in this embodiment, after collecting the field metadata, it is determined whether the current sample data table is a result table. If it is not a result table, proceed to step S13. If it is a result table, proceed to step S16.

[0042] Step S13: Sample multiple data points for each field to obtain multiple sample values ​​for the field.

[0043] Step S14: Based on the type of field data, perform statistical feature calculations of the corresponding type for multiple sample values ​​of each field.

[0044] Step S15: Generate field value validation rules. This involves determining the primary key of the validation rules based on the field metadata, and determining the rule parameters and parameter values ​​based on the statistical feature calculation results.

[0045] Step S16 involves dividing the collected fields into a set of original fields and a set of aggregated fields. The result table typically includes two types of data: one is data from the source data that can be used as an indicator on its own, and the other is aggregated indicator data. Additionally, some result tables only include aggregated indicator data. Therefore, for non-aggregated indicator data, return to step S13 for processing, and for aggregated indicator fields, proceed to step S17.

[0046] Step S17: Calculate the aggregated metric field data for each execution of an ETL task within the preset time window.

[0047] Step S18: Calculate the data feature values ​​of each aggregated indicator field in the preset time window.

[0048] Step S19: Generate aggregated metric validation rules. Specifically, the primary key of the validation rule is determined based on the metadata of the aggregated metric fields, and the rule parameters and parameter values ​​are determined based on the data feature values. In one embodiment, the aggregated metric validation rule includes a preset time window and the average value of the data feature values ​​within the preset time window. That is, the rule parameters include the time window, the "average value," and the variance value.

[0049] The field metadata in step S11 includes, for example, field names (such as column names), data types, whether they are nullable, field comments, and key attributes. Taking the job application (job_apply) table as the source table as an example, its field names might include "Salary," "Education," "Status," "Application Time," and "Application Identifier." Data types might be numbers, times, or enumerations. Key attributes indicate whether it's an index key or a primary key field. Taking the result table as an example, the field names in the result table might be the same as the field names in the source table used as metrics, and typically include aggregated metric fields, such as job application conversion rate, interview conversion rate, daily job applications, etc.

[0050] In one embodiment, the field structure information table of a sample data table in the business database is queried by calling the API (JDBC) of the business database. For example, the job application (job_apply) table stores data, and the table field structure information of the job_apply table is stored in the INFORMATION_SCHEMA.COLUMNS table. Then, the field structure information is read from the INFORMATION_SCHEMA.COLUMNS table by calling JDBC.

[0051] In step S13, since a single field in a data table contains many rows of data, and some data tables can have tens of thousands or even millions of rows, this invention only samples a portion of the data as a sample. In one embodiment, random sampling is used, but sampling can also be done according to certain rules. For example, based on the amount of data in the table and the required sample data, sampling can be performed at certain intervals. For instance, 10,000 values ​​for the "Salary" field, 10,000 values ​​for the "Education" field, 10,000 values ​​for the "Application Time" field, and 10,000 values ​​for the "Application Identifier" field can be randomly selected from the job_apply table.

[0052] For non-aggregated indicator fields in the result table or fields in the source table, in one embodiment, in step S14, when performing statistical feature calculations of the corresponding type on multiple sample values ​​of each field, the data type of the field metadata is first identified. This invention pre-sets corresponding statistical calculation methods for field values ​​of different data types. For example, for numeric field values, it is necessary to calculate their null rate, mean, standard deviation, 1st percentile (P1), and 99th percentile (P99), etc. For enumerated types, all different types and null rates are recorded. For time types, the null rate, most recent time, and earliest time are calculated. Furthermore, each field needs to be determined whether it is a primary key type. Then, based on the data type in the metadata, calculations are performed according to the configured statistical calculation methods. For example, the following are the statistical feature calculation results for "Salary," "Education," "City Code," "Application Time," and "Application Identifier": "Salary": type=NUMERIC, null_rate=0.77%, mean=15230.50, stddev=8450.30, p1=3500, p99=65000.

[0053] "Education": type=ENUM, null_rate=0.88%, values=[Bachelor's, Associate's, Master's, High School, Doctoral, Secondary Vocational School, Junior High School].

[0054] “Status”: type=ENUM, null_rate=0, values=[0,1,2,3].

[0055] "Application Time": type = DATETIME, null_rate = 0, min = 2024-01-15, max = 2026-06-07.

[0056] "Application Identifier": type=NUMERIC, null_rate=0, column_key=PRI.

[0057] In step S15, the primary key of the validation rule is determined based on the field metadata, and the rule parameters and parameter values ​​are determined based on the statistical feature calculation results. For example, the following validation rule is generated based on the statistical feature calculation results of the aforementioned five fields: Salary Range Rules: {"min":3500,"max":65000,"allow_null":true,"max_null_rate":0.02} ENUM education: {"values":["Bachelor's Degree","Associate's Degree","Master's Degree","High School","Doctor's Degree","Vocational School","Junior High School"],"allow_null":true} State enumeration rules (ENUM status): {"values":[0,1,2,3],"allow_null":false} Application time range rules (TIME_RANGE apply_time): {"min":"2024-01-01 00:00:00","max_offset_days":1} Uniqueness rules for apply_id: {"allow_null":false} As can be seen from the above examples, in this embodiment, the primary key name of the validation rule is determined based on the field name and type in the field metadata. The parameters in the rule are statistical items or concatenations of statistical items, and the calculation result is used as the rule parameter value. Through the above steps S13 to S15, the non-aggregated indicators or field value validation rules of the source table for each sample data table are obtained.

[0058] Since data volume is also a very important verification indicator, this invention also includes data volume verification rules. Specifically, it includes the following steps S191 and S192.

[0059] Step S191: Count the number of rows in the sample data table for each ETL task executed within a preset time window. For example, count the number of rows processed in the sample data table for each ETL task executed within a week. Alternatively, count the number of rows processed during a preset number of ETL task executions, such as counting the number of data rows processed in each of 100 consecutive ETL tasks.

[0060] Step S192: Generate data volume verification rules. This involves determining the parameter values ​​in the data volume verification rules based on the counted number of rows. For example, the data volume verification rule is a row count (ROW_COUNT) rule: {"auto_calc":true,"window_days":7,"sigma":3}.

[0061] That is, the system automatically calculates the number of rows, using 7 days of statistical data as a baseline, with a standard deviation of sigma=3. sigma=3 means mean ± 3 × standard deviation. For example, if the average number of rows over the last 7 days is 10,000 and the standard deviation sigma=500, then the allowable range of rows is approximately: 10,000 ± 3 × 500, or 8,500 to 11,500.

[0062] In a further embodiment, after generating the verification rules, a rule verification process is also included. For example, after generating the verification rules, they are written into a rule table, and the verification rules are marked as candidate rules by setting them to a "learning" state. The data structure in the rule table used to store a rule includes a rule ID, the identifiers of the tables and fields involved, the rule type, rule parameters, rule status, and confidence level. For newly generated verification rules, the rule status is set to "learning". This invention sets a verification time window, such as 7 days. Within the time window, the rules marked as "learning" are verified multiple times. The confidence level of the rule is calculated and written into the rule table each time. When the confidence level meets the condition, the rule is marked as "activated," and at this time, the rule is put into actual data quality verification. If the confidence level does not meet the condition after the time window is completed, the rule is marked as "reviewed," and a corresponding warning message is generated and distributed to relevant personnel through pre-set channels for manual processing.

[0063] In the process of verifying candidate rules, the corresponding data (such as the corresponding data table, fields, etc.) is first verified using the candidate rules to obtain candidate verification results, and then the actual verification results of the same data are obtained. The hit rate of the candidate verification results is calculated based on the actual verification results. Taking one row of data in the data table as the statistical unit, the formula for calculating the daily hit rate is shown in the following formula (1-1): Daily hit rate = Number of rows satisfying the rule constraints / Total number of rows for the day (1-1) In this framework, the rule constraints in the hit determination conditions for different rule types are as follows: RANGE: The parameter values ​​in the candidate validation results are within the range of [min, max]. ENUM: The parameter values ​​in the candidate validation results are in the enumeration set; NULL_RATE: The actual null value rate of the candidate validation results is less than or equal to the upper threshold. UNIQUENESS: No duplicate values ​​were found in the candidate validation results; TIME_RANGE: The time in the candidate verification result is within the valid range; ROW_COUNT: The number of rows in the candidate verification results is within the range of baseline ± 3σ.

[0064] Similarly, by using the methods described above, all generated verification rules are verified to obtain applicable verification rules.

[0065] This invention employs a three-level monitoring system to cover the entire data warehouse generation process during quality control. (See also...) Figure 2 In step S2, the system monitors data changes in the business database based on message subscriptions and performs corresponding monitoring operations according to the received data change information. In one embodiment, the system monitors data changes in the business database based on message subscriptions. For example, it subscribes to table structure change events and table data change events in the business database operation log. Currently, a mainstream business database is MySQL, which stores and manages structured data. The binary log (Binary Log) is a binary format log file generated by the MySQL database when performing write operations (such as INSERT, UPDATE, DELETE) or structure change operations (such as CREATE, ALTER, DROP). Each record contains information such as event type, execution time, affected tables, and data changes. The event types include DDL and DML. DDL events are table structure change events, indicating that a table schema change has occurred in the data source, such as table creation or deletion, deletion, modification, addition of fields in the table, primary key changes, field type changes, and comment changes. DML events are table data change events, such as row insertion and deletion.

[0066] In one embodiment, a MySQL source is created in Apache Flink CDC (Change Data Capture). This source is the data source to be monitored, for example, specifying a particular database and tables within that database, to monitor various add, modify, and delete operations in the binary log. In this embodiment, the monitored event types include both DDL and DML events.

[0067] See Figure 4 , Figure 4 This is a flowchart illustrating a method for processing event change information in a business database according to an embodiment of the present invention. Specifically, it includes the following steps: Step S21: Determine the event type of the change event information. If it is a table structure change event (DDL), proceed to step S22. If it is a table data change event (DML), proceed to step S24.

[0068] Step S22: Classify the table structure change information into warning levels to obtain the corresponding warning levels and warning information. For example, the system has the following pre-configured warning strategies: The levels and their basis are as follows: 1. When the table structure change information includes any one of the following: field deletion, type narrowing, and primary key change, the warning level is P0, and the warning information is distributed to relevant personnel via telephone and WeChat. 2. When the table structure change information includes the addition or renaming of fields, the warning level is P1, and the warning information is distributed to relevant personnel through WeChat group messages. 3. When the table structure change information includes field type expansion or comment change, the warning level is P2, and the warning information is distributed to relevant personnel via email.

[0069] Step S23: Distribute the warning information according to the channel corresponding to the warning level, and then end the processing flow.

[0070] Step S24 involves anomaly detection of table data change information. For example, it checks whether the amount of data deleted or added within a preset time window (e.g., 5 minutes) exceeds a threshold. If it does, an anomaly is confirmed. For instance, if the amount of deleted data exceeds 10 times the historical average, it may indicate a mistaken operation, such as batch deletion of data, which is considered anomaly. Similarly, if the amount of added data exceeds 5 times the historical average, it may indicate duplicate imports, which should also be considered anomaly. Another example is checking for a sudden increase in the primary key conflict rate. If the primary key conflict rate exceeds a threshold, it may indicate data replay, which should also be considered anomaly. These are just examples; corresponding detection items and judgment conditions can be configured according to actual circumstances.

[0071] Step S25: Determine if the table data is abnormal. If the table data is abnormal, proceed to step S26. If the table data is not abnormal, end the processing flow.

[0072] Step S26: Generate early warning information and distribute the early warning information according to the corresponding channels.

[0073] Furthermore, when determining the validation rules and the sample database, database change information can be referenced to verify whether the determined sample database is a new table. For example, when the source table of the first successfully completed ETL task is identified as a candidate sample table, it is checked whether the target source table is a newly created table in the business database. Specifically, it is determined whether a table creation event in the original business database has been captured, and whether the table identifier in the captured table creation event information is the same as that of the target source table. If the table identifier in the table creation event information is the same as that of the target source table, then the target source table is identified as the sample table. This further ensures the accuracy of using the correct sample table when generating validation rules.

[0074] See Figure 2 In step S3, when performing multi-level consistency verification on the ETL task based on the verification rules, the source table and target table of the ETL task are verified separately. Taking the source table as an example, the source table is verified based on the verification rules obtained from the source table sample, including the verification of data volume and field data. For example, firstly, the allowed row count range is calculated based on the average number of rows in the last 7 days and the allowable standard deviation in the data volume verification rules, and then the number of data rows in the current table is counted. It is determined whether the counted row count is within the allowed row count range; if it is, the verification passes. If it exceeds the allowed row count range, it indicates that the data volume of the currently verified data table is abnormal. Similarly, for field data, based on the rule parameters and parameter values ​​of the corresponding fields in the verification rules, it is determined whether the field data in the table meets the parameter values ​​in the rules. For example, if the rule parameter for a field is false (i.e., null values ​​are not allowed), but a field in the current data table has a null value, it indicates that the current field data is abnormal.

[0075] The validation of the results table also includes the validation of the aggregated indicator field. Based on the "average" and variance values ​​in the validation rules, the allowed numerical range is calculated. Then, the current aggregated indicator field value is compared with the numerical range. If the current aggregated indicator field value is within the numerical range, the validation is passed, indicating that the current field data is abnormal.

[0076] Furthermore, this invention also performs root cause analysis based on multi-source data when anomalies occur during the verification process. Specifically, see [link to documentation]. Figure 5 , Figure 5This is a flowchart of a root cause investigation method for a verification anomaly message according to an embodiment of the present invention. Specifically, it includes: Step S31: Determine whether data change information in the business database has been monitored. If data change information in the business database has been monitored, then in step S32, determine that the reason for the abnormal verification result is a change in the underlying business database. If no data change information in the business database has been monitored, then proceed to step S33.

[0077] Step S33: Obtain the ETL task status. This involves reading the status of the corresponding ETL task from the ETL scheduling system. The ETL scheduling system stores the result status of each ETL task for each execution.

[0078] Step S34: Determine if the ETL task status is failed. If the read ETL task status is failed, then in step S35, determine that the reason for the abnormal verification result is a computation task abnormality. If the ETL task status is successful, then proceed to step S36.

[0079] Step S36: Obtain the computational logic information of the ETL task.

[0080] Step S37 determines whether the ETL task's computational logic information is the same as the computational logic information of the sample data table. If the ETL task's computational logic information is different from the sample data table's computational logic information, then in step S38, it is determined that the reason for the abnormal verification result is a change in computational logic. If the ETL task's computational logic information is the same as the sample data table's computational logic information, it means that the cause of the abnormality cannot be determined based on the current information, and then step S39 is executed.

[0081] Step S39: Generate early warning information and release the early warning information through a preset channel.

[0082] Typically, computation logic is configured when an ETL task is created. When the computation logic needs to be changed, the original configuration information is retained for ease of management, and different computation logic is identified by version. Therefore, the version number can be used to identify whether the computation logic of the ETL task for the sample data table when creating the validation rules is the same as the computation logic of the current ETL task. Thus, in step S36, the version number of the ETL task's configuration information can be used to represent the computation logic information.

[0083] See Figure 2In step S4, before outputting the ETL task result data (such as a result table or a portion of the result table) from the data warehouse, in one embodiment, the present invention first determines the corresponding data as the target result data based on the range of data to be output. Then, it calculates the baseline value of the target result data for a second preset time window, for example, calculating the 7-day average as the baseline value, and then calculates the error between the target result data and its baseline value. The present invention sets multiple processing methods and corresponding error satisfaction conditions. For example, when the error is less than a first error threshold, it can be selected to simply indicate it in the output data; when the error is greater than a second error threshold, the output of the target result data is blocked, and a corresponding warning message is generated. Therefore, after calculating the error between the target result data and its baseline value, the error is compared with the corresponding error threshold, and the corresponding processing method is selected.

[0084] This invention generates various early warning messages during the entire monitoring process. To facilitate the management and processing of these messages, it also creates a monitoring and handling tracking workflow, which includes multiple processing nodes. Early warning messages are distributed based on pre-defined processing rules at each node. For example, a three-tiered processing node structure is used, with each node including the responsible person's phone number, email address, internal social media account, and processing time limit. When an early warning message times out at the first processing node, the phone number, email address, and internal social media account of the responsible person at the second processing node are retrieved, and the early warning message is sent to that responsible person through this channel. For ease of management, the monitoring and handling tracking workflow is presented in a visual dashboard format.

[0085] On the other hand, the present invention also provides a data quality monitoring system in a data warehouse, see [link to relevant documentation]. Figure 6 , Figure 6This is a block diagram illustrating the principle of a data warehouse data quality monitoring system according to an embodiment of the present invention. The data quality monitoring system of this embodiment includes a rule creation module 1, a data access monitoring module 2, a multi-level verification module 3, and an output monitoring module 4, wherein the arrows in the diagram indicate the data flow direction. Data in the business database 200 is extracted, transformed, and calculated by ETL tasks and then stored in the data warehouse 300, and output from the data warehouse 300 to applications or dashboards when needed. The data access monitoring module 2 monitors data change information in the business database 200; the rule creation module 1 generates verification rules for the ETL tasks based on sample data tables of the ETL tasks; the multi-level verification module 3 performs multi-level consistency verification based on the verification rules of the ETL tasks; and the output monitoring module 4 performs baseline detection on the target result data of the ETL tasks before outputting it from the data warehouse 300. Thus, the data quality monitoring system of the present invention constructs a three-layer, full-link data quality monitoring system of "access-computation-output," with the data access monitoring module 2 acting as the access layer, sensing changes in the structure and data of the data tables in the business database 200. The middle layer is the computation layer, where validation rules are automatically generated by the rule creation module 1 and validated by the multi-level validation module 3. Finally, the output layer performs checks on the data before it is output from the data warehouse. This invention achieves end-to-end data quality monitoring, eliminating the need for manual validation rule creation, reducing labor costs, and ensuring timely detection and handling of data issues, thereby improving the data quality and operational stability of the data warehouse.

[0086] According to another aspect of the invention, the invention also provides an electronic device, see [link to relevant documentation]. Figure 7 , Figure 7 This is a schematic diagram of the hardware structure of an electronic device according to an embodiment of the present invention. The electronic device can be implemented as a server or management terminal device, including a processor 601 and a memory 602. The memory 602 stores a program instruction set, and the aforementioned data quality monitoring method is implemented when the processor 601 executes the program instruction set in the memory 602.

[0087] Specifically, the processor 601 may include a central processing unit (CPU), an application specific integrated circuit (ASIC), or one or more integrated circuits that can be configured to implement the embodiments of the present invention.

[0088] Memory 602 may include mass storage for data or instructions. For example, and not limitingly, memory 602 may include a hard disk drive (HDD), floppy disk drive, flash memory, optical disk, magneto-optical disk, magnetic tape, or Universal Serial Bus (USB) drive, or a combination of two or more of these. Where appropriate, memory 602 may include removable or non-removable (or fixed) media. Where appropriate, memory 602 may be internal or external to the integrated gateway disaster recovery device. In a particular embodiment, memory 602 is non-volatile solid-state memory.

[0089] The memory may include read-only memory (ROM), random access memory (RAM), disk storage media devices, optical storage media devices, flash memory devices, and electrical, optical, or other physical / tangible memory storage devices. Therefore, typically, memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., memory devices) encoded with software including computer-executable instructions, and when the software is executed (e.g., by one or more processors), it is operable to perform the data quality monitoring method provided by this invention.

[0090] In one example, the electronic device may also include a communication interface 603 and a bus 604. The processor 601, memory 602, and communication interface 603 are connected via the bus 604 and communicate with each other.

[0091] The communication interface 603 is mainly used to realize communication between various modules, devices, units and / or equipment in the embodiments of the present invention.

[0092] Bus 604 includes hardware, software, or both, that couples components of an online data traffic metering device together. For example, and not limitingly, the bus may include an Accelerated Graphics Port (AGP) or other graphics bus, an Enhanced Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), HyperTransport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an Infinite Bandwidth Interconnect, a Low Pin Count (LPC) bus, a memory bus, a Microchannel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-X) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local (VLB) bus, or other suitable buses, or combinations of two or more of these. Where appropriate, bus 604 may include one or more buses. While specific buses are described and illustrated in embodiments of the invention, the invention contemplates any suitable bus or interconnect.

[0093] The present invention also provides a computer-readable storage medium having computer program instructions stored thereon, which, when executed by a processor, implement any of the data quality monitoring methods described in the foregoing embodiments. The computer-readable storage medium can be any medium that can tangibly contain or store computer-executable instructions for use by or in conjunction with an instruction execution system, apparatus, or device. The storage medium can be a transient computer-readable storage medium or a non-transitory computer-readable storage medium. Non-transitory computer-readable storage media may include, but are not limited to, magnetic storage devices, optical storage devices, and / or semiconductor storage devices. Examples of such storage devices include, for example, magnetic disks, optical discs based on CD, DVD, or Blu-ray technology, and persistent solid-state storage such as flash memory and solid-state drives.

[0094] This invention also provides a computer program product, comprising a set of computer program instructions, which, when executed by a processor, implement any of the data quality monitoring methods described in the foregoing embodiments. The computer program product includes, but is not limited to, application installation packages published on websites or in app stores, application plugins, and mini-programs that can run within certain applications.

[0095] It should be clarified that the present invention is not limited to the specific configurations and processes described above and shown in the figures. For the sake of brevity, detailed descriptions of known methods are omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present invention is not limited to the specific steps described and shown. Those skilled in the art can make various changes, modifications, and additions, or change the order of steps, after understanding the spirit of the present invention.

[0096] The above embodiments are for illustrative purposes only and are not intended to limit the invention. Those skilled in the art can make various changes and modifications without departing from the scope of the invention. Therefore, all equivalent technical solutions should also fall within the scope of the invention.

Claims

1. A data quality monitoring method for a data warehouse, wherein, The data warehouse stores target result data obtained by ETL tasks from extracting and calculating data from target source tables in the business database. The data quality monitoring method includes: The validation rules for the ETL task are generated based on the sample data table of the ETL task. Monitor data change information in the business database; Multi-level consistency checks are performed based on the verification rules of the ETL task; and Baseline detection is performed on the target result data before it is output from the data warehouse.

2. The method according to claim 1, wherein, The steps for monitoring data change information in the business database include: Subscribe to table structure change events and table data change events in the business database operation log; Obtain table structure change information based on received table structure change events; and Obtain table data change information based on the received table data change events.

3. The method according to claim 2, wherein, After obtaining table structure change information based on the received table structure change event, the process further includes: The table structure change information is classified into alert levels to obtain the corresponding alert levels and alert information; and The warning information is distributed through the channels corresponding to the warning level.

4. The method according to claim 2, wherein, After obtaining table data change information based on the received table data change event, the process further includes: Perform anomaly detection on table data change information; and In response to the detection result indicating abnormal changes in table data, an early warning message is generated and distributed through the corresponding channels.

5. The method according to claim 1, wherein, After generating the validation rules for the ETL task based on the sample data table of the ETL task, the validation rules are determined as candidate rules. The method further includes: Within the first preset time window, candidate rules are validated based on the actual validation rules; Evaluate whether the candidate rules meet the preset confidence conditions; and In response to the candidate rule satisfying the confidence condition, the candidate rule is determined as the verification rule for multi-level consistency verification of the ETL task.

6. The method according to claim 1, wherein, The validation rules include table field validation rules. The steps for generating the validation rules for the ETL task based on the sample data table of the ETL task include: Collect field metadata from the sample data table; Multiple data entries for each field are sampled to obtain multiple sample values ​​for that field; Based on the data type of the fields, statistical features of the corresponding type are calculated for multiple sample values ​​of each field; and The primary key of the validation rule is determined based on the field metadata, and the rule parameters and parameter values ​​are determined based on the statistical feature calculation results.

7. The method according to claim 6, wherein, The verification rules include data volume verification rules. The steps for generating the verification rules for the ETL task based on the sample data table of the ETL task include: Count the number of data rows in the sample data table corresponding to each execution of the ETL task within the second preset time window; or count the number of data rows in the sample data table for each execution of the preset number of ETL task executions; and The parameter values ​​in the data volume verification rules are determined based on the number of rows counted.

8. The method according to claim 6 or 7, wherein, The sample data table includes the target source table of the ETL task and the target result table that stores the target result data.

9. The method according to claim 6, wherein, The verification rules include aggregated index verification rules. The steps for generating the verification rules for the ETL task based on the sample data table of the ETL task include: Collect metadata from the aggregated metric fields in the ETL task results table; Retrieve data for each aggregated metric field during each execution of an ETL task within the third preset time window; Based on the aggregated indicator field data, statistical analysis is performed on the data characteristic values ​​of each aggregated indicator field; and The primary key of the validation rule is determined based on the metadata of the aggregated indicator field, and the rule parameters and parameter values ​​are determined based on the data feature values.

10. The method according to claim 1, wherein, Multi-level consistency checks on the ETL task based on the aforementioned verification rules include: The amount of data in the target table of the ETL task is verified to obtain the verification result of the data amount in the target table; the target table is the target source table and / or the target result table. The target data fields in the target table of the ETL task are validated to obtain the validation results of the target table field data. The target table is the target source table and / or the target result table. The aggregated metric fields in the ETL task result table are validated to obtain the validation results of the aggregated metrics.

11. The method according to claim 10, wherein, In response to any abnormal verification result at any level, perform multi-level root cause investigation.

12. The method according to claim 11, wherein, The steps for multi-level root cause analysis include: Determine whether data change information has been monitored; In response to the detected data change information, it was determined that the abnormal verification result was caused by changes in the business database. In response to the absence of monitored data changes, retrieve the ETL task status; In response to the ETL task being in a failed state, the reason for the abnormal verification result is determined to be an abnormal computation task. In response to the ETL task being in a successful state, the computational logic information of the ETL task is obtained and it is determined whether the computational logic information of the ETL task is the same as the computational logic information of the sample data table. In response to the discrepancy between the computational logic information of the ETL task and the computational logic information of the sample data table, the cause of the abnormal verification result is determined to be a change in the computational logic; and In response to the fact that the computational logic information of the ETL task is the same as the computational logic information of the sample data table, an early warning message is generated and released through a preset channel.

13. The method according to claim 1, wherein, The steps for baseline detection of the target result data include: Calculate the baseline value of the target result data within the fourth preset time window; Calculate the error between the target result data and its baseline value; and In response to the error satisfying the error condition corresponding to the handling method, the target result data is handled accordingly, wherein the handling method includes one or more of the following: adding a warning mark to the target result data, blocking the output of the target result data, and generating corresponding warning information.

14. The method according to claim 3, 4, 12, or 13, wherein, Further, it includes: creating a monitoring and handling tracking process, which includes multiple processing nodes and distributing early warning information based on the preset processing rules of the processing nodes.

15. The method according to claim 1, wherein, The method further includes the step of determining a sample data table: in response to the first successful completion of the ETL task, the target source table of the first successfully completed ETL task and the target result table storing the target result data are determined as the sample data table.

16. The method according to claim 15, wherein, The method further includes: determining the target source table of the first successfully completed ETL task as a candidate sample data table; and verifying whether the target source table is a newly created data table in the business database. In response to the target source table being a newly created data table in the business database, the target source table is determined as a sample data table.

17. The method according to claim 16, wherein, When verifying whether the target source table is a newly created data table in the business database, the data table creation event of the business database is captured. In response to the capture of the data table creation event information, and the data table identifier in the data table creation event information is the same as the target source table, the target source table is determined as the sample data table.

18. A data quality monitoring system for a data warehouse, wherein, include: The rule creation module is configured to generate validation rules for the ETL task based on the sample data table of the ETL task. The data access monitoring module is configured to monitor data change information in the business database. A multi-level verification module is configured to perform multi-level consistency verification based on the verification rules of the ETL task; as well as The output monitoring module is configured to perform baseline detection on the target result data before the target result data is output from the data warehouse.

19. An electronic device comprising a processor and a memory, wherein the memory stores a set of computer program instructions, characterized in that, The data quality monitoring method for the data warehouse according to any one of claims 1-17 is executed when the processor executes the computer program instruction set on the memory.

20. A computer-readable storage medium, wherein, The computer-readable storage medium stores a set of computer program instructions, characterized in that, when the set of computer program instructions is executed by a processor, it performs the data quality monitoring method for the data warehouse according to any one of claims 1-17.

21. A computer program product comprising a computer program instruction set, characterized in that, When the computer program instruction set is executed by the processor, it performs the data quality monitoring method for the data warehouse as described in any one of claims 1-17.

Images