Data warehouse table regression test difference detection method, system and computer storage medium

Abstract

This invention provides a method, system, and computer storage medium for regression testing discrepancy detection in data warehouse tables. The method includes: constructing a data warehouse production system and a grayscale system, connecting the grayscale system to the production data source; running the code to be tested in the grayscale system to process the production data source and generate a grayscale data table; using the grayscale data table and the corresponding production data table as comparison data sources to determine the verification range and comparison fields; utilizing big data computing capabilities to identify multi-dimensional data differences between the two, including anomalies such as duplication, missing data, and field inconsistencies; determining the impact range of changes in the current release version based on the differences, and comparing it with the expected range to determine whether it affects historical version requirements data, thus completing the regression testing verification. This invention replaces traditional use case-based regression testing with consistency verification between grayscale and production results, achieving automated, full-scale discrepancy detection under massive data, significantly improving data warehouse development efficiency and data quality.

Description

Technical Field

[0001] This invention relates to the field of data warehousing, and more specifically, to a method, system, and computer storage medium for detecting differences in regression testing of data warehouse tables. Background Technology

[0002] In the construction of offline or real-time data warehouses, data development is a complex cyclical process encompassing logical design, code coding, data testing, and deployment. Among these, data testing is the core step in ensuring the accuracy and logical consistency of the deployed data, directly determining the quality of the data warehouse's external services.

[0003] The existing data warehouse testing system is mainly divided into unit testing and regression testing. Unit testing focuses on verifying the logical correctness of the current requirement change point; regression testing focuses on verifying whether the existing data corresponding to the historical version requirements remains accurate after the introduction of new logic, and is a key barrier to prevent production data accidents.

[0004] As data warehouse operations become increasingly complex, traditional regression testing faces significant technical challenges. Because data warehouse tables typically contain massive amounts of data, testers struggle to achieve full business coverage of the data by writing test cases. On one hand, current testers often lack sufficient familiarity with the business logic of historical versions, leading to blind spots in test case design. On the other hand, regression testing is extremely labor-intensive, and manual sampling is prone to missing tests, potentially causing release incidents in the production environment.

[0005] To address the aforementioned pain points, existing technologies have proposed some automated testing solutions. However, in-depth analysis reveals the following significant limitations: Testing solutions based on test case expansion: For example, invention patent CN202210061230.3 discloses a big data testing method, device, electronic device, and storage medium. First, target business data from the business system is extracted to the data warehouse ODS layer. After ensuring that the data table structure of the target business data is consistent with the data table structure of the business data extracted to the data warehouse ODS layer, the business data from the data warehouse ODS layer is extracted to the data warehouse ADS layer through the data warehouse DW layer and tested based on newly generated successful test cases. When the data test passes, the business data extracted from the data warehouse ADS layer to the upper interface layer of the data warehouse is pushed to the target application system. A consistency verification test is performed between the pushed business data and the target business data to obtain the consistency verification test results. The above solutions are essentially still "test case driven" models, and their effectiveness highly depends on the coverage of the initial test seeds. When faced with hundreds of millions of real data rows at the data warehouse level, the generated use cases still cannot exhaust all possible abnormal scenarios, making it difficult to achieve full logical coverage of the data warehouse table data.

[0006] Patent document CN202311417925.1 discloses a data warehouse end-to-end testing method, apparatus, equipment, and storage medium. This method involves pre-building an end-to-end testing environment based on a data warehouse end-to-end testing request, acquiring the data to be tested and the corresponding expected results, predicting the test results corresponding to the data to be tested using the pre-built end-to-end testing environment, and finally verifying the expected and test results to achieve direct testing of data within the data warehouse. This solution focuses on integrated testing methods for the entire data warehouse chain. Such solutions typically focus on the topology and integrity of data flow, but lack precision in fine-grained comparisons of single data models and specific field definitions. They cannot quickly locate data deviations at the field level of a single table, making it difficult to meet the requirements of high-precision regression verification.

[0007] Patent document CN202311589302.2 discloses a data warehouse table detection method and apparatus, data management system, equipment, and storage medium. It determines the impact fields of the changes to the data warehouse table relative to the benchmark table by obtaining the differences between the first development script of the benchmark data warehouse table before the change and the second development script of the data warehouse table to be tested after the change. Then, using a pre-trained generative model, based on the structural information of the benchmark data warehouse table, the data warehouse table to be tested, the data source table, and the impact fields, it generates at least one test SQL query statement. This at least one test SQL query statement is then executed on the benchmark data warehouse table, the data warehouse table to be tested, and the data source table. Based on the at least one test result, it determines whether the data warehouse table to be tested passes the detection, thus obtaining the detection result. This scheme proposes to analyze the differences between the first and second scripts to determine the scope of impact and then generate test SQL judgment results. This scheme falls under the category of "static analysis," inferring the impact by analyzing the SQL code logic. However, data warehouse environments contain a large number of UDFs (User-Defined Functions), implicit type conversions, and complex environmental parameters. Even small differences in code logic can produce unpredictable deviations under massive data processing conditions. Relying solely on script parsing cannot be completely equivalent to verifying actual execution results, and there is a technical risk of "false alarms" or "missed alarms".

[0008] In summary, existing data warehouse testing technologies mostly focus on "test case simulation" or "script parsing," lacking a comprehensive comparison method that directly verifies the consistency between gray-scale and production environment results. How to achieve automated, high-precision, and comprehensive difference detection of offline and real-time data warehouse single-model data without relying on manually writing complex historical test cases is a pressing technical challenge in the field of data warehouse quality assurance. Summary of the Invention

[0009] To address the shortcomings of existing technologies, the purpose of this invention is to provide a method, system, and computer storage medium for detecting differences in data warehouse table regression testing.

[0010] A method for detecting differences in regression testing of data warehouse tables according to the present invention includes the following steps: Environment setup steps: Based on the data warehouse operating environment, build a data warehouse production system and a data warehouse grayscale system, and connect the data warehouse grayscale system to the production data source of the data warehouse production system; Result generation steps: Based on the type of the data warehouse task to be tested, run the code to be tested in the data warehouse grayscale system to perform calculations and processing on the production data source and generate the corresponding grayscale data table. Task configuration steps: Respond to the verification task configuration command, specify the grayscale data table and the corresponding production data table as the data sources to be verified, and determine the verification data range and comparison fields; The difference identification steps are as follows: Schedule and execute the verification task, use the big data computing capabilities based on big data frameworks such as Spark to identify the multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identify abnormal data; the big data computing capabilities are adapted to the scenarios of full loading, distributed row-by-row verification and parallel parsing comparison of massive data tables in the data warehouse.

[0011] Verification of closed-loop steps: Based on the differences in the multidimensional data, determine the scope of impact of the changes in this release version and compare it with the expected scope to determine whether it affects the historical version's requirement data, thereby completing the regression test verification.

[0012] Preferably, if the data warehouse task to be tested is an offline data warehouse task, then the result generation step includes: Run the development and testing code in the data warehouse grayscale system to directly generate offline grayscale data tables.

[0013] Preferably, if the data warehouse task to be tested is a real-time data warehouse task, then the result generation step includes: After the development and testing of the data warehouse grayscale system is completed, data backtracking is initiated according to the set time range, real-time message queue data is output and stored as a real-time grayscale data table. The message queue of the task output of the data warehouse production system is obtained synchronously and written to the corresponding production data table.

[0014] Preferably, the identification of anomalous data in the difference identification step includes: The system identifies whether there are duplicate data entries in the table based on the preset primary key field and marks them as duplicate exceptions.

[0015] Preferably, the step of identifying anomalous data in the difference identification step further includes: Based on the primary key field, row-by-row content validation is performed to identify over-type anomalies that exist in the grayscale data table but are missing in the production data table, and lack-type anomalies that exist in the production data table but are missing in the grayscale data table.

[0016] Preferably, the step of identifying anomalous data in the difference identification step further includes: Compare the values ​​of each comparison field under the same primary key row. If the values ​​are inconsistent, mark it as a "different" exception.

[0017] Preferably, if the comparison field contains a large field in JSON format, the identification process includes: Obtain the original content of the large JSON field in the grayscale data table and the production data table; Parse and iterate through the large JSON field to obtain all the key values ​​contained within it; Compare the value content corresponding to the same key one by one. If the values ​​are inconsistent, record it as a "different" exception to eliminate false alarms caused by the variable position of the key within the JSON.

[0018] Preferably, the verification closed-loop step includes: Write all identified abnormal data into the abnormal details result table, and summarize and generate an abnormal summary result report; By statistically analyzing the number of difference entries in each field of the anomaly summary report, it can be determined whether the multidimensional data differences are within the preset boundaries of the current version's requirement changes.

[0019] A data warehouse table regression test difference detection system according to the present invention includes the following modules: Environment building module: used to build a data warehouse production system and a data warehouse grayscale system based on the data warehouse operating environment, so that the data warehouse grayscale system can be connected to the production data source of the data warehouse production system; Result generation module: Based on the type of data warehouse task to be tested, the code to be tested is run in the data warehouse grayscale system to perform calculations on the production data source and generate the corresponding grayscale data table. Task configuration module: Used to respond to verification task configuration instructions, specify the grayscale data table and the corresponding production data table as the data source to be verified, and determine the verification data range and comparison fields; Difference identification module: used to schedule and execute verification tasks, use big data computing capabilities to identify multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identify abnormal data; Verification closed-loop module: used to determine the scope of impact of changes in this release version based on the differences in the multidimensional data, and compare it with the expected scope to determine whether it affects the requirement data of historical versions, thereby completing the regression test verification.

[0020] According to the present invention, a computer storage medium is provided thereon, on which a computer program is stored, which, when executed by a processor, implements the above-described method for detecting differences in regression testing of data warehouse tables.

[0021] Compared with the prior art, the present invention has the following beneficial effects:

[0022] 1. This invention solves the problem of difficult regression testing of offline and real-time data warehouse tables, simplifies existing data warehouse release regression testing, improves the efficiency of data warehouse data R&D testing, and ensures the quality of data released from the data warehouse.

[0023] 2. This invention utilizes the full consistency verification of the operation results between the grayscale system and the production system, directly replacing the traditional manual sampling inspection mode based on test cases. This greatly simplifies the regression testing path of the data model, significantly shortens the data release cycle, and improves R&D efficiency.

[0024] 3. This invention can accurately capture minor logical deviations, including primary key conflicts, inconsistent field values, and JSON structure changes, effectively avoiding production release accidents caused by incomplete logical coverage and ensuring extremely high accuracy in data release.

[0025] 4. By comparing the actual differences with the expected scope of changes, testers can objectively determine whether the current change exceeds the limits or accidentally touches historical data based on the difference report without having to delve into the business details of historical versions. This reduces the dependence of regression testing on the business proficiency of personnel and solves the technical risks caused by the gap in historical requirements knowledge.

[0026] 5. This invention boasts broad compatibility and precise discrepancy tracing. For different development flows in offline and real-time data warehouses, this invention provides adapted grayscale result generation mechanisms and supports fine-grained validation of large unstructured JSON fields. By generating multi-dimensional reports containing summaries and details, technical personnel can quickly locate specific primary key values ​​and corresponding field discrepancies, achieving precise tracing and rapid remediation of data issues. Attached Figure Description

[0027] Other features, objects, and advantages of the present invention will become more apparent from the following detailed description of non-limiting embodiments with reference to the accompanying drawings:

[0028] Figure 1 This is a flowchart illustrating the steps involved in the regression test difference detection method for data warehouse tables.

[0029] Figure 2 and3 This is a schematic diagram illustrating the testing and verification of newly created data for the data warehouse table regression test difference detection method.

[0030] Figure 4 This is a schematic diagram illustrating the scheduling and execution of the difference detection method for regression testing of data warehouse tables.

[0031] Figure 5 The flowchart shows the verification steps of the data warehouse table regression test difference detection method.

[0032] Figure 6 This is a schematic diagram summarizing the verification and comparison results of the difference detection method for regression testing of data warehouse tables.

[0033] Figure 7 This is a schematic diagram showing the detailed verification and comparison results of the regression test difference detection method for data warehouse tables.

[0034] Figure 8 This is a flowchart of the offline data warehouse development and testing process for a data warehouse table regression test difference detection method.

[0035] Figure 9 This is a flowchart of the real-time data warehouse development and testing process for a data warehouse table regression test difference detection method.

[0036] Figure 10-13 This is a schematic diagram illustrating the steps of an example of adding large-area information to a store information table using a regression test difference detection method for data warehouse tables. Detailed Implementation

[0037] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several changes and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention.

[0038] like Figures 1 to 13As shown, a data warehouse table regression testing difference detection method provided by the present invention includes the following steps: environment construction step: constructing a data warehouse production system and a data warehouse grayscale system based on the data warehouse operating environment, and connecting the data warehouse grayscale system to the production data source of the data warehouse production system; result generation step: running the code to be tested in the data warehouse grayscale system according to the type of the data warehouse task to be tested, performing calculation processing on the production data source, and generating a corresponding grayscale data table; task configuration step: responding to the verification task configuration instruction, specifying the grayscale data table and the corresponding production data table as the data source to be verified respectively, and determining the verification data range and comparison fields; difference identification step: scheduling and executing the verification task, using big data computing capabilities to identify multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identifying abnormal data; verification closed-loop step: determining the scope of impact of the changes in this release version based on the multi-dimensional data differences, and comparing it with the expected scope to determine whether it affects the historical version requirement data, thereby completing the regression test verification.

[0039] Furthermore, if the data warehouse task under test is an offline data warehouse task, the result generation step includes: running development test code in the data warehouse grayscale system to directly generate an offline grayscale data table. If the data warehouse task under test is a real-time data warehouse task, the result generation step includes: after the development and testing of the data warehouse grayscale system is completed, initiating data backtracking according to a set time range, outputting real-time message queue data and storing it as a real-time grayscale data table; synchronously acquiring the message queue output by the data warehouse production system task and storing it as the corresponding production data table.

[0040] Furthermore, the abnormal data identification step in the difference identification process includes: identifying whether there are duplicate data entries in the table based on a preset primary key field, and marking them as duplicate anomalies. It also includes: performing row-by-row content verification based on the primary key field to identify "over" type anomalies that exist in the grayscale data table but are missing in the production data table, and "lack" type anomalies that exist in the production data table but are missing in the grayscale data table.

[0041] Furthermore, the abnormal data identification step in the difference identification process also includes: comparing the values ​​of each comparison field under the same primary key row; if the values ​​are inconsistent, they are marked as "different" anomalies. If the comparison fields contain large fields in JSON format, the identification process includes: obtaining the original content of the large JSON format field in the grayscale data table and the production data table; parsing and traversing the large JSON format field to obtain all the key values ​​contained within it; comparing the value content corresponding to the same key one by one; if the values ​​are inconsistent, they are recorded as "different" anomalies to eliminate false alarms caused by the non-fixed position of the key within the JSON.

[0042] Furthermore, the verification closed-loop step includes: writing all identified abnormal data into an abnormality detail result table and generating an abnormality summary result report; and determining whether the multidimensional data differences are within the preset boundaries of the current version requirement change by statistically analyzing the number of difference entries in each field of the abnormality summary result report.

[0043] This invention also provides a data warehouse table regression testing difference detection system, comprising: an environment construction module: used to construct a data warehouse production system and a data warehouse grayscale system based on the data warehouse operating environment, so that the data warehouse grayscale system is connected to the production data source of the data warehouse production system; a result generation module: used to run the code to be tested in the data warehouse grayscale system according to the type of the data warehouse task to be tested, perform calculation processing on the production data source, and generate a corresponding grayscale data table; a task configuration module: used to respond to the verification task configuration instruction, specify the grayscale data table and the corresponding production data table as the data source to be verified respectively, and determine the verification data range and comparison fields; a difference identification module: used to schedule and execute the verification task, use big data computing capabilities to identify multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identify abnormal data; and a verification closed-loop module: used to determine the scope of impact of the changes in this release version based on the multi-dimensional data differences, and compare it with the expected scope to determine whether it affects the historical version requirement data, thereby completing the regression test verification.

[0044] The present invention also provides a computer storage medium storing a computer program that, when executed by a processor, implements the above-described method for detecting differences in regression testing of data warehouse tables.

[0045] To further explain, such as Figure 1 As shown, the overall execution logic of the data warehouse table regression testing difference detection method of the present invention is as follows: First, based on the existing data warehouse operating environment, an independent data warehouse production system and a data warehouse grayscale system are built, which are interconnected by data source, so that the grayscale system can directly call the production data source of the production system and simulate production operation calculation; then, depending on whether the data warehouse task to be tested is offline or real-time, the code to be tested is run in the grayscale system to generate the corresponding grayscale data table; then, the verification task is configured through the data testing system, and the grayscale and production data tables to be compared and the verification range and fields are selected; next, the verification task is executed using big data computing capabilities to identify multi-dimensional data anomalies between the two tables; finally, based on the difference results, the impact range of version change is analyzed to determine whether historical version data has been affected, and the regression test verification is completed, forming a complete closed loop of "development-testing-verification-validation".

[0046] The data warehouse operating environment of this invention always distinguishes between two major environments: the data warehouse production system and the data warehouse grayscale system. The data warehouse production system is used for the daily production operation task calculation of the data warehouse and is the core of production data generation and storage. The data warehouse grayscale system is dedicated to the use of technical personnel for data warehouse development and testing. Its data source can directly call the full data table of the production system and can completely simulate the operation calculation logic of the production system. It generates grayscale data tables in the grayscale environment that are consistent with the production calculation logic, providing a data foundation for subsequent dual-table verification.

[0047] The data testing system, as the core execution carrier of this invention, aims to achieve the following: 1. Analyze the overall differences between grayscale data and production data, including the total number of entries in both tables, the number of duplicate data entries, and the differences in the addition or subtraction of primary key rows between grayscale and production; 2. Analyze the differences in the content of fields in the same primary key row, including the number of differences and specific details of each field in the table; 3. Based on the data verification difference analysis, analyze the scope of the changes in this release version, determine whether the changes exceed the preset boundaries of this version, and whether they affect the historical version requirement data, so that regression testing can be completed without regressing to historical version requirement test cases.

[0048] In the grayscale data table generation step, based on the type of data warehouse task to be tested, there are two differentiated grayscale data table generation methods: offline data warehouse and real-time data warehouse. These methods are adapted to the offline data warehouse development and testing process and the real-time data warehouse development and testing process, respectively. The specific implementation is as follows: If the data warehouse task to be tested is an offline data warehouse task, combined with Figure 8 The offline data warehouse development and testing process involves the following steps: technical personnel directly complete the code development and unit testing of the offline data warehouse in the data warehouse grayscale system. After the development and testing are completed, the development and testing code is run in the data warehouse grayscale system to directly generate an offline grayscale data table. The structure and calculation logic of this data table are completely matched with the offline data table in the production system, except that the data generation environment is grayscale.

[0049] If the data warehouse task to be tested is a real-time data warehouse task, combined with Figure 9 The real-time data warehouse development and testing process requires two steps: After completing the code development and unit testing of the real-time data warehouse in the data warehouse grayscale system, the technical personnel set the data backtracking time range according to the business requirements of this test, start the data backtracking operation, generate real-time message queue data consistent with the production data range through backtracking, and write the message queue data into a real-time grayscale data table; at the same time, synchronously obtain the message queue data output by the corresponding real-time task in the data warehouse production system, and write it into the corresponding production data table to ensure that the grayscale and production data tables to be verified are completely consistent in terms of data time range and data source, and avoid the distortion of verification results due to data range deviation.

[0050] After generating the grayscale data table, a new data test and verification task needs to be created in the data testing system. The interface for this step is as follows: Figure 2 , Figure 3 As shown, the core is to select the data source to be verified, configure the verification range and comparison fields, and the specific implementation is as follows: Create a new verification task: Enter the task configuration interface of the data testing system, refer to... Figure 2 Enter basic information such as task name and responsible person, and initiate the command to create a new verification task. The system will then generate a blank verification task to be configured. Configure the data source to be verified: On the task configuration details page, complete the information configuration of Table 1 (grayscale data table) and Table 2 (production data table), including filling in the database name, table name, and data partition. The system will automatically load all field information of the data table and display attributes such as field English name, field Chinese name, field type, whether it is a primary key, and whether it is a large field (JSON format). Select the verification range and comparison fields: Select the data partitions to be verified and select the fields to be compared in the field list. This invention supports full field verification as well as custom field verification. You can also directly select large fields in JSON format. The system will automatically enable the fine-grained verification mode for large fields to achieve individual difference detection for each key within the large field. Save and submit the task: Select the comparison task execution system, confirm the configuration information is correct, and click "Save" to complete the configuration of the verification task. The configured task will then enter the task queue, waiting for scheduling and execution. Figure 2 You can view information such as the task's editing time, running status, and start and end times.

[0051] The configured verification task will be scheduled and executed by the data testing system. The task's running status can be viewed in real time within the system, such as... Figure 4 As shown, after the task is completed, you can view the task execution log and verification result report; the difference identification step is implemented based on big data computing capabilities, and the overall verification process is as follows: Figure 5 As shown, the core task is to complete the full data loading of the grayscale and production data tables and the multi-dimensional anomaly identification. The specific implementation steps are as follows: Data loading verification: The data testing system uses a big data computing framework to fully load the configured grayscale data table and production data table into the computing nodes of the testing system, providing a data foundation for subsequent row-by-row and field-by-field verification; Duplicate Data Identification (Duplicate Anomaly): First, the definition of the primary key field is clarified—the primary key field is one or more fields in a table that can uniquely identify a record (for example, the order number in an order table is a unique primary key field that can uniquely identify each order record). This invention, based on the preset primary key field, uses Spark SQL to perform primary key uniqueness verification on the grayscale table and the production table respectively. Specifically, Spark SQL is used to write a SQL query to count the number of times each primary key field value appears in the table. If the number of times a primary key field value appears is greater than 1, it is determined to be a duplicate data entry, marked as a duplicate anomaly, and the primary key value and the number of times the duplicate data appears are recorded.

[0052] Primary key row addition / deletion difference identification: The system performs row-by-row content validation on the grayscale table and the production table based on the primary key field. ① It identifies entries that exist in the grayscale data table but have no corresponding primary key in the production data table and marks them as over type anomalies (grayscale has more data than production); ② It identifies entries that exist in the production data table but have no corresponding primary key in the grayscale table and marks them as lack type anomalies (grayscale has less data than production). Identification of differences in the content of the same primary key row fields (different anomaly): For row data in the gray table and the production table where the primary keys are completely matched, the numerical content of each field is compared. If the value of a certain field is inconsistent between the gray table and the production table, it is marked as a different anomaly, and the name of the field, the primary key value, and the specific values ​​on both the gray and production sides are recorded. Fine-grained difference identification of large fields in JSON format: If the comparison field contains a large field in JSON format, the system does not use the direct string comparison method (to avoid false alarms caused by the non-fixed position of the key inside the JSON). Instead, it first obtains the original content of the large field in the grayscale and production tables, then parses and traverses all the key values ​​inside the JSON, and compares the value content corresponding to the same key one by one. If the values ​​are inconsistent, it is recorded as a different exception. Abnormal data storage and aggregation: All abnormal data identified above (including four categories: duplicate, over, lack, and different) are written into the abnormal details result table in a unified format, recording detailed information such as abnormal type, field name, primary key value, gray table value, and production table value; at the same time, the data in the abnormal details result table is aggregated and statistically analyzed to generate an abnormal summary result table, which counts the number of difference items by abnormal type and field name, providing data support for subsequent change impact analysis.

[0053] After the verification task is completed, the data testing system will generate a complete verification result report, including an overview of the comparison results and detailed comparison results. Technical personnel can view the report through the system interface and then perform version change impact analysis and regression testing verification based on the results. The specific implementation is as follows: (a) Viewing execution results Overview of comparison results: such as Figure 6 As shown, this section displays the summary statistics of the two data source verifications. First, it displays the basic information of the comparison data tables, including table alias, table name, comparison partitions, and comparison primary keys. Then, it displays the core verification results, including table1_cnt (number of data entries in the grayscale table), table2_cnt (number of data entries in the production table), duplicate_cnt (number of duplicate data entries), over (number of redundant grayscale entries), lack (number of missing grayscale entries), and the number of difference entries in each field under the different type, clearly presenting the overall difference distribution of the full data. Comparison results details: such as Figure 7 As shown, this section displays detailed information on each type of anomaly, supporting precise queries by "result type" and "field name". The list displays the anomaly type, field name, primary key value, grayscale table (Table 1) value, and production table (Table 2) value. Technical personnel can directly locate specific rows of discrepancies to achieve precise traceability of data differences.

[0054] (ii) Verification of closed loop Based on the overview and details of the comparison results, the technical staff completed the impact analysis of the changes in this release version. The specific steps are as follows: The statistical analysis of the anomaly summary results report includes the number of difference entries in each field. Combined with the preset boundaries of the requirements change in this version, it is determined whether the identified multidimensional data differences are the expected differences caused by the version change. The key analysis focuses on the scope of the differences to determine whether there are any differences that exceed the scope of this version change, or whether there are any unexpected differences that affect the requirements data of historical versions. If all differences are expected differences caused by this version change and do not affect the historical version requirements data, then the regression test of the data warehouse table is deemed to have passed and production release can proceed; if there are unexpected differences or the scope of the change's impact exceeds the preset boundary, then the code to be tested is returned to the data warehouse grayscale system for optimization, and the detection method of this invention is re-executed until the regression test passes.

[0055] More specifically, this embodiment takes the offline data warehouse requirement of adding large area information to the store information table as an example, combined with the attached... Figure 10-13 The complete implementation process of this invention is described in detail to verify its practicality and effectiveness: (I) Background of the demand The **large region code (big_region_cd) and large region name (big_region_nm)** fields need to be added to the store information table brock_dim.t_shp_str_inf_ed in the data warehouse production system. Technical personnel need to complete the code development in the grayscale system and complete the regression test using the method of this invention to verify whether the change meets expectations and whether it affects historical data.

[0056] (II) Implementation Steps Grayscale data table generation: Technicians modify grayscale tasks in the data warehouse grayscale system, add large area-related fields and calculation logic, and after running the development test code, output the grayscale data table hd_brock_dim.t_shp_str_inf_ed. This table is the grayscale version of the production table brock_dim.t_shp_str_inf_ed, which includes the newly added large area fields. Create a new verification task: Enter the data testing system and create a new verification task ( Figure 10 Enter the task name, responsible person, and other information to access the task configuration interface. Figure 11 Configure the grayscale table (Table 1: hd_brock_dim.t_shp_str_inf_ed) and the production table (Table 2: brock_dim.t_shp_str_inf_ed) respectively. Since the table has no partition field, select all fields to participate in the verification (including the newly added large area field), select the comparison task to execute the system and save the task. Run the verification task: In the data testing system, click "Run Verification Task" and wait for the task scheduling and execution to complete. The task running status interface will look like this. Figure 12 As shown, you can view the start and end times of the task, the person in charge, and the running status (execution successful). You can also click "View Results" and "Running Log" to get more detailed information. View and analyze the verification results: The verification results are as follows: Figure 13 As shown, the comparison results overview shows that both the grayscale table and the production table have 141,451 entries, with no duplicate, over, or lack anomalies. There are only three fields with different anomalies: fin_center_cd (2 differences), sw_str_cd (90 differences), and etl_time (141,451 differences). The comparison results details show the specific primary key values ​​and gray / production side values ​​for each difference field.

[0057] (III) Test Conclusion According to technical personnel analysis, the difference between fin_center_cd and sw_str_cd is a reasonable business change caused by the addition of large area information, and is considered an expected difference. etl_time is the ETL processing time of the data table, and the difference in the total data is due to the different running times of the gray table and the production table, which is a normal system difference. This version change did not produce any unexpected differences and did not affect the store information data of previous versions. The scope of the change fully meets the preset boundaries of the requirements of this version, and the regression test passed.

[0058] This embodiment replaces the traditional test case sampling mode with automated verification of all data, which improves testing efficiency and achieves full coverage verification of all data in the store information table, effectively ensuring the data quality of the data warehouse.

[0059] This invention also provides a data warehouse table regression testing difference detection system to implement the above-mentioned data warehouse table regression testing difference detection method. The system includes an environment construction module, a result generation module, a task configuration module, a difference identification module, and a verification closed-loop module. Each module corresponds one-to-one with the five steps of the above method, collaboratively achieving full automation of the data warehouse table regression testing process. Environment building module: used to build data warehouse production system and data warehouse grayscale system based on data warehouse operating environment, so that grayscale system can be connected to the production data source of production system; Result generation module: Used to run the code under test in the grayscale system to generate the corresponding grayscale data table according to the type of data warehouse task under test, adapting to both offline and real-time data warehouse types; Task configuration module: Used to respond to verification task configuration commands, completing the configuration of the data source to be verified, the verification range, and the comparison fields. Figure 2 , Figure 3 The user interface functions; The difference identification module is used to schedule and execute verification tasks, utilize big data computing capabilities to identify multi-dimensional data differences and mark anomalies, thereby achieving... Figure 5 The verification process; Verification closed-loop module: used to generate an overview and details of the comparison results ( Figure 6 , Figure 7 Based on the difference results, the impact of the version change was analyzed, and regression testing was completed to verify the results.

[0060] The present invention also provides a computer storage medium storing a computer program. When the program is executed by a processor, it can implement all the steps of the above-mentioned data warehouse table regression test difference detection method. It is applicable to various software and hardware environments for data warehouse development and has strong compatibility.

[0061] Those skilled in the art will understand that, besides implementing the system and its various devices, modules, and units provided by this invention in the form of purely computer-readable program code, the same functions can be achieved entirely through logical programming of the method steps, making the system and its various devices, modules, and units of this invention function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers. Therefore, the system and its various devices, modules, and units provided by this invention can be considered as a hardware component, and the devices, modules, and units included therein for implementing various functions can also be considered as structures within the hardware component; alternatively, the devices, modules, and units for implementing various functions can be considered as both software modules implementing the method and structures within the hardware component.

[0062] Specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Unless otherwise specified, the embodiments and features described in this application can be arbitrarily combined with each other.

Claims

1. A method for detecting differences in regression testing of data warehouse tables, characterized in that, Includes the following steps: Environment setup steps: Based on the data warehouse operating environment, build a data warehouse production system and a data warehouse grayscale system, and connect the data warehouse grayscale system to the production data source of the data warehouse production system; Result generation steps: Based on the type of the data warehouse task to be tested, run the code to be tested in the data warehouse grayscale system to perform calculations and processing on the production data source and generate the corresponding grayscale data table. Task configuration steps: Respond to the verification task configuration command, specify the grayscale data table and the corresponding production data table as the data sources to be verified, and determine the verification data range and comparison fields; Difference identification steps: Schedule and execute verification tasks, utilize big data computing capabilities to identify multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identify abnormal data; Verification of closed-loop steps: Based on the differences in the multidimensional data, determine the scope of impact of the changes in this release version and compare it with the expected scope to determine whether it affects the historical version's requirement data, thereby completing the regression test verification.

2. The data warehouse table regression test difference detection method according to claim 1, characterized in that, If the data warehouse task to be tested is an offline data warehouse task, then the result generation steps include: Run the development and testing code in the data warehouse grayscale system to directly generate offline grayscale data tables.

3. The data warehouse table regression test difference detection method according to claim 1, characterized in that, If the data warehouse task to be tested is a real-time data warehouse task, then the result generation steps include: After the development and testing of the data warehouse grayscale system is completed, data backtracking is initiated according to the set time range, real-time message queue data is output and stored as a real-time grayscale data table. The message queue of the task output of the data warehouse production system is obtained synchronously and written to the corresponding production data table.

4. The data warehouse table regression test difference detection method according to claim 1, characterized in that, The differential identification step includes identifying anomalous data, including: The system identifies whether there are duplicate data entries in the table based on the preset primary key field and marks them as duplicate exceptions.

5. The data warehouse table regression test difference detection method according to claim 4, characterized in that, The differential identification step also includes identifying anomalous data: Based on the primary key field, row-by-row content validation is performed to identify over-type anomalies that exist in the grayscale data table but are missing in the production data table, and lack-type anomalies that exist in the production data table but are missing in the grayscale data table.

6. The data warehouse table regression test difference detection method according to claim 1, characterized in that, The differential identification step also includes identifying anomalous data: Compare the values ​​of each comparison field under the same primary key row. If the values ​​are inconsistent, mark it as a "different" exception.

7. The data warehouse table regression test difference detection method according to claim 6, characterized in that, If the comparison field contains a large field in JSON format, the recognition process includes: Obtain the original content of the large JSON field in the grayscale data table and the production data table; Parse and iterate through the large JSON field to obtain all the key values ​​contained within it; Compare the value content corresponding to the same key one by one. If the values ​​are inconsistent, record it as a "different" exception to eliminate false alarms caused by the variable position of the key within the JSON.

8. The data warehouse table regression test difference detection method according to claim 1, characterized in that, The verification closed-loop steps include: Write all identified abnormal data into the abnormal details result table, and summarize and generate an abnormal summary result report; By statistically analyzing the number of difference entries in each field of the anomaly summary report, it can be determined whether the multidimensional data differences are within the preset boundaries of the current version's requirement changes.

9. A data warehouse table regression test difference detection system, characterized in that, Includes the following modules: Environment building module: used to build a data warehouse production system and a data warehouse grayscale system based on the data warehouse operating environment, so that the data warehouse grayscale system can be connected to the production data source of the data warehouse production system; Result generation module: Based on the type of data warehouse task to be tested, the code to be tested is run in the data warehouse grayscale system to perform calculations on the production data source and generate the corresponding grayscale data table. Task configuration module: Used to respond to verification task configuration instructions, specify the grayscale data table and the corresponding production data table as the data source to be verified, and determine the verification data range and comparison fields; Difference identification module: used to schedule and execute verification tasks, use big data computing capabilities to identify multi-dimensional data differences between the grayscale data table and the corresponding production data table, and identify abnormal data; Verification closed-loop module: used to determine the scope of impact of changes in this release version based on the differences in the multidimensional data, and compare it with the expected scope to determine whether it affects the requirement data of historical versions, thereby completing the regression test verification.

10. A computer storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the data warehouse table regression test difference detection method as described in any one of claims 1 to 8.

Images