A data verification method, device and equipment for data warehouse task debugging and a storage medium
By writing debugging data into an independent table and performing SQL comparison during data warehouse task debugging, the problem of data verification interfering with the production environment is solved, achieving efficient and reliable multi-dimensional data verification, and improving the efficiency of data warehouse task debugging and the reusability of verification rules.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- GUANGZHOU PINWEI SOFTWARE CO LTD
- Filing Date
- 2025-12-30
- Publication Date
- 2026-06-02
AI Technical Summary
During the debugging of existing data warehouse tasks, the data verification methods are prone to interfering with the production environment. The verification rules are scattered and non-standardized, making them difficult to reuse and resulting in low execution efficiency.
By writing debugging data into a temporary independent table and converting preset data verification test cases into SQL comparison statements, multi-dimensional difference comparisons are performed based on the SQL comparison statements, including data integrity, accuracy, consistency and incremental data comparison, comparison difference results are generated, and a hierarchical alarm mechanism is established.
It achieves physical isolation between debugging data and production data, improves the reliability and efficiency of data verification, supports multi-dimensional custom verification logic, and improves the automated execution efficiency of data warehouse task debugging and the reusability of verification rules.
Smart Images
Figure CN122132276A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of data debugging technology, and more specifically, to a data verification method, apparatus, device, and storage medium for data warehouse task debugging. Background Technology
[0002] In the field of data debugging technology, data warehouses, as core data storage and analysis carriers, directly impact the reliability of business decisions and subsequent data applications through the accuracy of data during task debugging. Data warehouse task debugging requires verification of intermediate calculations and final output data to ensure that the debugged task can stably adapt to the production environment.
[0003] Existing debugging data verification methods are prone to causing debugging operations to interfere with the production environment, and the verification rules are mostly fragmented manual judgments or non-standardized configurations, which are difficult to reuse and have low execution efficiency.
[0004] Therefore, how to perform efficient and reliable data verification during the data warehouse task debugging phase in order to improve the debugging efficiency of data warehouse tasks is an issue that needs attention. Summary of the Invention
[0005] In view of the above problems, this application provides a data verification method, apparatus, device and storage medium for data warehouse task debugging, so as to improve the efficiency and reliability of data verification in the data warehouse task debugging stage, thereby improving the efficiency of data warehouse task debugging.
[0006] To achieve the above objectives, the following specific solutions are proposed:
[0007] A data verification method for debugging data warehouse tasks includes:
[0008] Obtain debugging data generated from data warehouse task debugging based on raw data, and write the debugging data into a temporarily created independent table;
[0009] Convert the preset data validation test cases into SQL comparison statements;
[0010] Based on the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference result.
[0011] Optionally, the method further includes:
[0012] When abnormal data is found in the comparison difference results, the abnormal data is displayed.
[0013] Optionally, the SQL comparison statement describes a data integrity dimension;
[0014] Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including:
[0015] Based on the data integrity dimension described in the SQL comparison statement, the debug data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the field comparison difference results.
[0016] Optionally, the SQL comparison statement describes the data accuracy dimension;
[0017] Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including:
[0018] Based on the data accuracy dimension described in the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the numerical comparison difference result.
[0019] Optionally, the SQL comparison statement describes the data consistency dimension;
[0020] Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including:
[0021] Based on the data accuracy dimension described in the SQL comparison statement, the debugging data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the data consistency comparison difference results.
[0022] Optionally, based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including:
[0023] Based on the SQL comparison statement, the first incremental data of the debugging data in the independent table is compared with the second incremental data of the benchmark data obtained in the production environment based on the original data to obtain the incremental data comparison difference result. The first incremental data is the difference data between the debugging data and the original data, and the second incremental data is the difference data between the benchmark data and the original data.
[0024] Optionally, the SQL comparison statement describes multiple comparison dimensions;
[0025] Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including:
[0026] Based on each comparison dimension described by the SQL comparison statement, the debugging data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the comparison difference result under that comparison dimension.
[0027] The method also includes:
[0028] If the number of comparison difference results containing abnormal data is less than the first preset number of abnormalities, a level one alarm is issued.
[0029] If the number of comparison difference results containing abnormal data is not less than the first preset number of abnormalities and less than the second preset number of abnormalities, then a level two alarm is issued.
[0030] If the number of comparison difference results containing abnormal data is not less than the second preset number of abnormalities, a level three alarm will be issued.
[0031] A data verification device for data warehouse task debugging includes:
[0032] The debugging data writing unit is used to obtain debugging data generated by debugging data warehouse tasks based on raw data, and write the debugging data into a temporarily created independent table;
[0033] The SQL comparison statement creation unit is used to convert preset data verification test cases into SQL comparison statements;
[0034] The difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the SQL comparison statement, and obtain the comparison difference result.
[0035] Optionally, the device may also include:
[0036] An abnormal data display unit is used to display the abnormal data when there is abnormal data in the comparison difference results.
[0037] Optionally, the SQL comparison statement describes the data integrity dimension; the difference comparison unit includes:
[0038] The data integrity difference comparison unit is used to compare the field differences between the debug data in the independent table and the benchmark data obtained in the production environment based on the original data, based on the data integrity dimension described by the SQL comparison statement, and to obtain the field comparison difference result.
[0039] Optionally, the SQL comparison statement describes the data accuracy dimension; the difference comparison unit includes:
[0040] The data accuracy difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the data accuracy dimension described by the SQL comparison statement, and obtain the numerical comparison difference result.
[0041] Optionally, the SQL comparison statement describes the data consistency dimension; the difference comparison unit includes:
[0042] The data consistency difference comparison unit is used to compare the debug data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the data accuracy dimension described by the SQL comparison statement, and to obtain the data consistency comparison difference result.
[0043] Optionally, the difference comparison unit includes:
[0044] The incremental data difference comparison unit is used to compare the first incremental data of the debugging data in the independent table with the second incremental data of the benchmark data obtained in the production environment based on the original data, based on the SQL comparison statement, to obtain the incremental data comparison difference result. The first incremental data is the difference data between the debugging data and the original data, and the second incremental data is the difference data between the benchmark data and the original data.
[0045] Optionally, the SQL comparison statement describes multiple comparison dimensions; the difference comparison unit includes:
[0046] A multi-dimensional difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on each comparison dimension described by the SQL comparison statement, to obtain the comparison difference result under that comparison dimension.
[0047] The device also includes:
[0048] A first-level alarm unit is used to issue a first-level alarm if the number of comparison difference results containing abnormal data is less than a first preset number of abnormalities.
[0049] The secondary alarm unit is used to issue a secondary alarm if the number of comparison difference results containing abnormal data is not less than the first preset number of abnormalities and less than the second preset number of abnormalities.
[0050] The three-level alarm unit is used to issue a three-level alarm if the number of comparison difference results containing abnormal data is not less than the second preset number of abnormalities.
[0051] A data verification device for data warehouse task debugging includes a memory and a processor;
[0052] The memory is used to store programs;
[0053] The processor is used to execute the program and implement the various steps of the data verification method for data warehouse task debugging as described above.
[0054] A storage medium storing a computer program, which, when executed by a processor, implements the various steps of the data verification method for debugging data warehouse tasks as described above.
[0055] By employing the above technical solution, this application obtains debugging data generated during data warehouse task debugging based on raw data, writes the debugging data into a temporarily created independent table, converts preset data verification test cases into SQL comparison statements, and compares the debugging data in the independent table with the baseline data obtained from the raw data in the production environment based on the SQL comparison statements to obtain the comparison difference results. Therefore, storing the debugging data generated during data warehouse task debugging in an independent temporary table achieves physical isolation between the debugging data and the production environment baseline data, avoids interference from debugging operations on production data, and improves the reliability of data verification during the data warehouse task debugging phase. The standardized SQL comparison statements support multi-dimensional custom verification logic, improving the reusability of verification rules and the efficiency of automated execution, thereby improving the efficiency of data warehouse task debugging. Attached Figure Description
[0056] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the scope of this application. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings:
[0057] Figure 1 This application provides a schematic diagram of a data verification process for data warehouse task debugging.
[0058] Figure 2A schematic diagram of a device structure for implementing data verification in data warehouse task debugging is provided in an embodiment of this application;
[0059] Figure 3 This is a schematic diagram of the structure of a device for implementing data verification for data warehouse task debugging, provided in an embodiment of this application. Detailed Implementation
[0060] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0061] The proposed solution can be implemented based on a terminal with data processing capabilities, such as a computer, cloud, or server.
[0062] Next, combined Figure 1 The data verification method for debugging data warehouse tasks in this application may include the following steps:
[0063] Step S110: Obtain the debugging data generated by debugging the data warehouse task based on the original data, and write the debugging data into a temporarily created independent table.
[0064] The debugging data may include intermediate data and output data generated during the debugging of data warehouse tasks on the original data.
[0065] It is understandable that directly comparing debugging data with production data during the data warehouse task debugging phase could potentially interfere with the production environment data. Therefore, it is necessary to write the debugging data into a temporarily created independent table to achieve physical isolation between the debugging data and the production data, avoid interference from debugging operations with the production environment data, and ensure the integrity and traceability of the debugging data.
[0066] Specifically, the data warehouse task debugging process is first triggered based on the original business data in the data warehouse debugging scenario, generating full debugging data covering intermediate calculation results and final output results of the task execution. An independent table, physically isolated from the production environment data table, is automatically created temporarily. The field structure and data types of this independent table are completely consistent with the corresponding production table, ensuring accurate data dimension matching. The debugging data is then completely written to this independent temporary table according to a preset standardized format to avoid the risk of debugging data directly accessing the production environment baseline data, preventing debugging operations from tampering with, contaminating, or interfering with the performance of production data.
[0067] Step S120: Convert the preset data verification test cases into SQL comparison statements.
[0068] Specifically, test cases are essentially the carriers of data validation rules. SQL comparison statements are standardized SQL statements derived from validation test cases, specifically used to automate the comparison between debug data and benchmark data; they are the core of data validation execution. Test case materials can come from real-world scenarios such as data warehouse operations, business applications, and historical experience.
[0069] Use cases derived from business rules: Since data warehouse data ultimately serves business decisions, the data correctness standards at the business level are the core basis for use cases. Business metric definitions: such as "user count statistics must exclude test accounts" and "order amount = unit price × quantity + shipping cost," can be transformed into use cases like "verify that the number of valid users in the temporary table matches the production table" and "verify that the order amount calculation logic conforms to the business formula." Business constraints: such as "order status is only allowed to be 'pending payment', 'paid', or 'cancelled'" and "customer mobile phone number format must be 11 digits," can be transformed into use cases like "verify that the order status in the temporary table has no illegal values" and "verify that the mobile phone number field format is compliant."
[0070] Use cases derived from data warehouse technical specifications: Field-level constraints: such as "User ID is not empty", "Creation time format is YYYY-MM-DD HH:MM:SS", and "Amount field is of type decimal (10,2)", can be converted into use cases for field integrity and format consistency. Data integration rules: such as "Incremental synchronization tasks only synchronize newly added data of the current day" and "Duplicate records need to be deleted after data cleaning", can be converted into use cases for "Verifying that the time range of incremental data in the temporary table is compliant" and "Verifying that there are no duplicate records in the temporary table".
[0071] Step S130: Based on the SQL comparison statement, compare the debugging data in the independent table with the baseline data obtained from the original data in the production environment to obtain the comparison difference results.
[0072] Among them, the benchmark data obtained from raw data in the production environment can represent benchmark data in a formal operating state, or data that is actually generated in the business scenario and can be directly used for business decision-making or subsequent analysis.
[0073] Specifically, when performing difference comparisons, the intermediate data of the debug data must be compared with the intermediate data of the benchmark data, and the output data of the debug data must be compared with the output data of the benchmark data. The difference comparison logic between the debug data and the benchmark data must follow the rules described by the SQL comparison statement.
[0074] The data verification method for data warehouse task debugging provided in this embodiment obtains debugging data generated during data warehouse task debugging based on original data, writes the debugging data into a temporarily created independent table, converts preset data verification test cases into SQL comparison statements, and compares the debugging data in the independent table with the baseline data obtained in the production environment based on the original data based on the SQL comparison statements to obtain the comparison difference results. Therefore, storing the debugging data generated during data warehouse task debugging in an independent temporary table achieves physical isolation between the debugging data and the production environment baseline data, avoids interference from debugging operations on production data, and improves the reliability of data verification during the data warehouse task debugging phase. The standardized SQL comparison statements support multi-dimensional custom verification logic, improving the reusability of verification rules and the efficiency of automated execution, thereby improving the efficiency of data warehouse task debugging.
[0075] In some embodiments of this application, the process of comparing the debugging data in an independent table with the baseline data obtained from the original data in the production environment based on the SQL comparison statement to obtain the comparison difference result is further described. This process may include the following various situations:
[0076] The first approach is to compare the field differences between the debug data in the independent table and the baseline data obtained from the original data in the production environment, based on the data integrity dimensions described in the SQL comparison statement, and obtain the field comparison difference results.
[0077] Specifically, taking the e-commerce data warehouse order data debugging task as an example, this SQL comparison statement explicitly defines the core dimensions of integrity verification. For example, it verifies that the required fields related to orders (user ID, order number, payment amount, creation time, payment status) in the independent table and the production environment baseline table are completely consistent, and that each required field has no null values and the field length conforms to the preset specifications. Based on this SQL statement, the system first matches the field structure of the independent table and the baseline table to confirm whether the independent table is missing any required fields from the baseline table; then it checks the non-nullability of each required field in the independent table row by row, counting the number of null records. For example, if the user ID in the baseline table has no null values, and there are 8 records with null user IDs in the independent table, then a difference is marked. The system verifies the compliance of field length and data type. For example, if it detects that 3 order numbers are only 15 digits long and 2 payment amounts are integers instead of decimals, then these are all judged as differences. Finally, it integrates various issues such as missing fields, null values, and non-compliant formats to generate a field comparison difference result that includes the difference type, the number of records involved, and the specific field names.
[0078] The second approach is to compare the numerical differences between the debug data in the independent table and the baseline data obtained from the original data in the production environment, based on the data accuracy dimension described in the SQL comparison statement, and obtain the numerical comparison difference results.
[0079] Specifically, taking the monthly sales data debugging task of an e-commerce data warehouse as an example, the SQL comparison statement clearly defines the numerical verification rules, such as "based on the order number, link the independent table and the production baseline table, verify that the actual payment amount, product sales volume, and discount amount of a single order are completely consistent, and the statistical deviation of the total monthly sales is ≤0.1%". First, using the order number as the unique primary key, the corresponding records in the two tables are accurately matched, and the core numerical fields are compared row by row: for example, due to the omission of the discount calculation logic during debugging, it was found that the actual payment amount of 3 orders in the independent table was 10 yuan and 20 yuan less than that in the baseline table, respectively, and the product sales volume of 5 orders was counted as 1 more item due to data synchronization; then the total monthly sales are calculated, and the statistical result of the independent table is 5.283 million yuan, while that of the baseline table is 5.289 million yuan, with a deviation of 0.11%, which exceeds the threshold. Finally, the results are integrated to form a numerical comparison difference result that includes the difference order number, abnormal fields, specific difference values, and statistical deviation rate.
[0080] The third approach is to compare the debug data in the independent table with the baseline data obtained from the original data in the production environment based on the data accuracy dimension described by the SQL comparison statement, based on the data accuracy dimension described by the SQL comparison statement, to obtain the data consistency comparison difference results.
[0081] Specifically, taking the user information synchronization debugging task in an e-commerce data warehouse as an example, the SQL comparison statement clearly defines the consistency verification rules, such as "using the user ID as the unique primary key, verify that the core user information (name, desensitized mobile phone number, account status code) in the independent table and the production baseline table are completely consistent, and that the user's membership level matches the level coding logic in the associated member table." First, the corresponding records in the two tables are accurately associated using the user ID, and the consistency of basic information is compared field by field. It was found that 12 user account status codes were different (the independent table still showed "03 - Pending Activation", while the baseline table had been updated to "04 - Frozen"). Then, the cross-table association logic was verified, and it was detected that 8 user membership levels in the independent table were marked as "VIP3", but there were no corresponding level code records in the associated member table, which was determined to be an association consistency anomaly. Finally, the results are integrated to form a consistency comparison result containing the different user IDs, abnormal fields, and details of the association logic deviation.
[0082] Fourthly, when the comparison object is incremental data, the difference can be compared between the first incremental data of the debugging data in the independent table and the second incremental data of the benchmark data obtained from the original data in the production environment based on the SQL comparison statement to obtain the incremental data comparison difference result.
[0083] The first incremental data represents the difference between the debug data and the original data. The second incremental data represents the difference between the baseline data and the original data.
[0084] Specifically, taking the daily order increment debugging task in an e-commerce data warehouse as an example, the SQL comparison statement will be limited to the rule of "filtering data for the current day based on order creation time, using the order number as the unique identifier". First, the first incremental data in the independent table and the second incremental data in the production baseline table are extracted separately. Then, the completeness of fields, accuracy of values, and consistency of status are compared between the two according to the SQL rules. For example, it was found that 3 records of the first incremental data were missing the "logistics tracking number" field, and 2 orders had payment statuses that were inconsistent with the second incremental data. Finally, a comparison result containing the difference in order numbers, abnormal fields, and incremental data types is generated.
[0085] Fifthly, when the SQL comparison statement only describes multiple comparison dimensions, the debugging data in the independent table can be compared with the baseline data obtained from the original data in the production environment based on each comparison dimension described by the SQL comparison statement to obtain the comparison difference result under that comparison dimension.
[0086] Specifically, taking the quarterly order comprehensive debugging task of e-commerce data warehouse as an example, the SQL comparison statement will simultaneously specify the verification rules for three core dimensions: data integrity, accuracy, and consistency. For example, "using the order number as the unique primary key, the integrity dimension verifies that the required fields such as order number, user ID, and actual payment amount have no empty values and the format is compliant; the accuracy dimension verifies that the statistical deviation of actual payment amount, discount amount and quarterly order total is ≤0.1%; and the consistency dimension verifies that the association logic between the order status code and the logistics status table, and the matching relationship between the user's region code and the region table are all compliant." Based on the SQL statement, discrepancies were compared along the following dimensions: First, in terms of completeness, the comparison detected 6 orders in the independent table lacking the "logistics tracking number" field, and 4 order numbers whose format did not conform to the 16-character standard. Next, in terms of accuracy, a row-by-row comparison revealed discrepancies in the actual payment amount calculation for 8 orders; the total quarterly order amount in the independent table was 12.568 million yuan, while the baseline table showed 12.583 million yuan, a deviation of 0.12%, exceeding the threshold. Finally, in terms of consistency, the comparison identified 10 orders with a status code of "shipped" but no corresponding logistics record in the logistics status table, and 5 user region codes that did not match the region table. Detailed discrepancy results were generated for each comparison dimension, including the anomaly type, number of records involved, specific fields, and deviation details for each dimension.
[0087] In some embodiments of this application, after the comparison results of debugging data and benchmark data are obtained by comparing the differences in the foregoing embodiments, the comparison results can be intelligently analyzed to identify abnormal data and visualize the abnormal data.
[0088] Specifically, if there are abnormal data such as numerical deviations, field mismatches, or logical validation failures in the comparison results, the specific details of the abnormal data can be displayed intuitively through a visualization panel (such as a list of difference data, deviation trend charts, and field-level comparison views). This supports drill-down queries and root cause tracing of abnormal data, ensuring that debugging personnel can quickly locate and handle data problems.
[0089] Furthermore, considering the presence of anomalous data in the comparison results across multiple dimensions, indicating a significant difference between the debugging data and production data, and suggesting potential anomalies in the production process, the data verification method for data warehouse task debugging provided in this application may further include a tiered alarm mechanism based on anomalous data. Specifically, this may include:
[0090] Level 1 Alarm: If the number of comparison difference results containing abnormal data is less than the first preset number of abnormalities, a Level 1 alarm will be issued.
[0091] Understandably, a Level 1 alarm indicates that the difference between the debugging data and the production environment baseline data is small, reflecting a minor anomaly in the production process.
[0092] Level 2 Alarm: If the number of comparison difference results containing abnormal data is not less than the first preset number of abnormalities and less than the second preset number of abnormalities, a Level 2 alarm will be issued.
[0093] Understandably, a level 2 alarm indicates a moderate degree of difference between the debug data and the production environment baseline data, reflecting a moderate anomaly in the production process.
[0094] Level 3 Alarm: If the number of comparison difference results containing abnormal data is not less than the second preset number of abnormalities, a Level 3 alarm will be issued.
[0095] Understandably, a Level 3 alarm indicates a significant difference between the debug data and the production environment baseline data, reflecting a serious anomaly in the production process.
[0096] The data verification method for data warehouse task debugging provided in this embodiment sets graded thresholds based on the number of discrepancies in the comparison results of abnormal data. This objectively quantifies the severity of anomalies under multi-dimensional comparisons, avoiding over-warning of minor issues or neglect of serious issues caused by a single alarm mode. This enables debugging personnel to quickly identify the urgency of problems. Secondly, the graded design clarifies different response priorities: Level 1 alarms correspond to minor anomalies, which can be investigated according to the regular process; Level 2 alarms indicate moderate risks, requiring faster processing; Level 3 alarms point to serious anomalies, which may indicate production process vulnerabilities and can trigger emergency response mechanisms, significantly improving problem handling efficiency and reducing debugging cycles and labor costs.
[0097] The data verification device for data warehouse task debugging provided in the embodiments of this application is described below. The data verification device for data warehouse task debugging described below and the data verification method for data warehouse task debugging described above can be referred to in correspondence.
[0098] See Figure 2 , Figure 2 This is a schematic diagram of a device for data verification in data warehouse task debugging, as disclosed in an embodiment of this application.
[0099] like Figure 2 As shown, the device may include:
[0100] The debugging data writing unit 11 is used to obtain debugging data generated by debugging data warehouse tasks based on raw data, and write the debugging data into a temporarily created independent table.
[0101] SQL comparison statement creation unit 12 is used to convert preset data verification test cases into SQL comparison statements;
[0102] The difference comparison unit 13 is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the SQL comparison statement, and obtain the comparison difference result.
[0103] Optionally, the device may also include:
[0104] An abnormal data display unit is used to display the abnormal data when there is abnormal data in the comparison difference results.
[0105] Optionally, the SQL comparison statement describes the data integrity dimension; the difference comparison unit includes:
[0106] The data integrity difference comparison unit is used to compare the field differences between the debug data in the independent table and the benchmark data obtained in the production environment based on the original data, based on the data integrity dimension described by the SQL comparison statement, and to obtain the field comparison difference result.
[0107] Optionally, the SQL comparison statement describes the data accuracy dimension; the difference comparison unit includes:
[0108] The data accuracy difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the data accuracy dimension described by the SQL comparison statement, and obtain the numerical comparison difference result.
[0109] Optionally, the SQL comparison statement describes the data consistency dimension; the difference comparison unit includes:
[0110] The data consistency difference comparison unit is used to compare the debug data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the data accuracy dimension described by the SQL comparison statement, and to obtain the data consistency comparison difference result.
[0111] Optionally, the difference comparison unit includes:
[0112] The incremental data difference comparison unit is used to compare the first incremental data of the debugging data in the independent table with the second incremental data of the benchmark data obtained in the production environment based on the original data, based on the SQL comparison statement, to obtain the incremental data comparison difference result. The first incremental data is the difference data between the debugging data and the original data, and the second incremental data is the difference data between the benchmark data and the original data.
[0113] Optionally, the SQL comparison statement describes multiple comparison dimensions; the difference comparison unit includes:
[0114] A multi-dimensional difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on each comparison dimension described by the SQL comparison statement, to obtain the comparison difference result under that comparison dimension.
[0115] The device also includes:
[0116] A first-level alarm unit is used to issue a first-level alarm if the number of comparison difference results containing abnormal data is less than a first preset number of abnormalities.
[0117] The secondary alarm unit is used to issue a secondary alarm if the number of comparison difference results containing abnormal data is not less than the first preset number of abnormalities and less than the second preset number of abnormalities.
[0118] The three-level alarm unit is used to issue a three-level alarm if the number of comparison difference results containing abnormal data is not less than the second preset number of abnormalities.
[0119] The data verification device for data warehouse task debugging provided in this application embodiment can be applied to data verification devices for data warehouse task debugging, such as terminals: mobile phones, computers, etc. Optionally, Figure 3 The hardware structure block diagram of the data verification device for data warehouse task debugging is shown. (Refer to...) Figure 3 The hardware structure of the data verification device for data warehouse task debugging may include: at least one processor 1, at least one communication interface 2, at least one memory 3, and at least one communication bus 4;
[0120] In this embodiment of the application, the number of processor 1, communication interface 2, memory 3, and communication bus 4 is at least one, and processor 1, communication interface 2, and memory 3 communicate with each other through communication bus 4;
[0121] Processor 1 may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention.
[0122] Memory 3 may include high-speed RAM, and may also include non-volatile memory, such as at least one disk storage device;
[0123] The memory stores a program, which the processor can call. The program is used for:
[0124] Obtain debugging data generated from data warehouse task debugging based on raw data, and write the debugging data into a temporarily created independent table;
[0125] Convert the preset data validation test cases into SQL comparison statements;
[0126] Based on the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference result.
[0127] Optionally, the refined and extended functions of the program can be found in the description above.
[0128] This application embodiment also provides a storage medium that can store a program suitable for execution by a processor, the program being used for:
[0129] Obtain debugging data generated from data warehouse task debugging based on raw data, and write the debugging data into a temporarily created independent table;
[0130] Convert the preset data validation test cases into SQL comparison statements;
[0131] Based on the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference result.
[0132] Optionally, the refined and extended functions of the program can be found in the description above.
[0133] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.
[0134] The various embodiments in this specification are described in a progressive manner. Each embodiment focuses on the differences from other embodiments. The various embodiments can be combined as needed, and the same or similar parts can be referred to each other.
[0135] The above description of the disclosed embodiments enables those skilled in the art to make or use this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A data verification method for debugging data warehouse tasks, characterized in that, include: Obtain debugging data generated from data warehouse task debugging based on raw data, and write the debugging data into a temporarily created independent table; Convert the preset data validation test cases into SQL comparison statements; Based on the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference result.
2. The method according to claim 1, characterized in that, Also includes: When abnormal data is found in the comparison difference results, the abnormal data is displayed.
3. The method according to claim 1, characterized in that, The SQL comparison statement describes the data integrity dimension; Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including: Based on the data integrity dimension described in the SQL comparison statement, the debug data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the field comparison difference results.
4. The method according to claim 1, characterized in that, The SQL comparison statement describes the dimensions of data accuracy; Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including: Based on the data accuracy dimension described in the SQL comparison statement, the debugging data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the numerical comparison difference result.
5. The method according to claim 1, characterized in that, The SQL comparison statements describe the dimensions of data consistency. Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including: Based on the data accuracy dimension described in the SQL comparison statement, the debugging data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the data consistency comparison difference results.
6. The method according to claim 1, characterized in that, Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including: Based on the SQL comparison statement, the first incremental data of the debugging data in the independent table is compared with the second incremental data of the benchmark data obtained in the production environment based on the original data to obtain the incremental data comparison difference result. The first incremental data is the difference data between the debugging data and the original data, and the second incremental data is the difference data between the benchmark data and the original data.
7. The method according to any one of claims 1-6, characterized in that, The SQL comparison statement describes multiple comparison dimensions; Based on the SQL comparison statement, the debug data in the independent table is compared with the baseline data obtained in the production environment based on the original data to obtain the comparison difference results, including: Based on each comparison dimension described by the SQL comparison statement, the debugging data in the independent table is compared with the benchmark data obtained in the production environment based on the original data to obtain the comparison difference result under that comparison dimension. The method also includes: If the number of comparison difference results containing abnormal data is less than the first preset number of abnormalities, a level one alarm is issued. If the number of comparison difference results containing abnormal data is not less than the first preset number of abnormalities and less than the second preset number of abnormalities, then a level two alarm is issued. If the number of comparison difference results containing abnormal data is not less than the second preset number of abnormalities, a level three alarm will be issued.
8. A data verification device for data warehouse task debugging, characterized in that, include: The debugging data writing unit is used to obtain debugging data generated by debugging data warehouse tasks based on raw data, and write the debugging data into a temporarily created independent table; The SQL comparison statement creation unit is used to convert preset data verification test cases into SQL comparison statements; The difference comparison unit is used to compare the debugging data in the independent table with the benchmark data obtained in the production environment based on the original data, based on the SQL comparison statement, and obtain the comparison difference result.
9. A data verification device for data warehouse task debugging, characterized in that, Including memory and processor; The memory is used to store programs; The processor is used to execute the program to implement each step of the data verification method for data warehouse task debugging as described in any one of claims 1-7.
10. A storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements each step of the data verification method for data warehouse task debugging as described in any one of claims 1-7.