Overview
Data reconciliation periodically compares data between the source and target to promptly detect data inconsistencies. DataBuddy real-time synchronization tasks support two reconciliation rules based on the number of data records and data content (primary key), covering both full and incremental scopes, and provide alarm notifications and discrepancy detail viewing. This document describes the supported scope, configuration methods, and result viewing for data reconciliation.
Prerequisites
A real-time whole-database or database/table sharding synchronization task has been created.
If the "Verify data content" rule is used, the source table must contain a primary key or a unique index.
Use Limits
Limit | Description |
Task type | Real-time whole-database migration tasks / real-time sharded database and table migration tasks are supported |
Primary key | Data content validation supports only primary key-based reconciliation. Tables without a primary key or unique index are not supported. |
MongoDB | MongoDB sources do not support incremental validation (only full validation is supported). |
Number of tables in a single task | When the number of databases and tables involved in a task exceeds 5000, data validation cannot be configured per table (this does not affect the running of the overall synchronization task). |
Reconciliation period | The minimum reconciliation interval is 60 minutes. |
Difference details preview | A maximum of 10000 difference records can be previewed at a time, and only primary key fields are displayed. |
Resource consumption | Validation tasks and synchronization tasks share the same compute resources, consuming resource group resources. |
Target-side filter condition | The filter SQL for incremental validation is executed in Flink SQL and supports only Flink SQL syntax (for example, write the current date as ${YYYY-MM-dd} instead of CURRENT_DATE(), and use <> instead of != for inequality). |
Supported Links
Task type | Source End |
Whole-database migration task | MySQL,TDSQL-C MySQL,Oracle,MongoDB,Kafka |
Sharded database and table task | MySQL,TDSQL-C MySQL,Oracle |
Operation Steps
Step 1: Enabling Data Reconciliation
Data reconciliation is configured in the Data Reconciliation step of the task configuration process:
2. Turn on the Data Reconciliation switch. When it is off, no tables are reconciled. When it is on, all tables are reconciled uniformly according to the global reconciliation policy.
Step 2: Configuring a Global Reconciliation Policy
Parameter | Description | Required |
Reconciliation rule | Verify data record count / Verify data content. See the description below for details. | Yes |
Reconciliation field | Displayed only when Data Content Validation is selected. Currently, only primary key-based reconciliation is supported. | Depends on the rule |
Reconciliation scope | | Yes |
Reconciliation time | Reconciliation interval, minimum 60 minutes | Yes |
Reconciliation Rules
Rule | Action | Scenario |
Validate the number of data records | Directly compare the row counts of the source and target tables through the data source connection by using count(*). | Small data volume, focusing on total count consistency. |
Validate data content | Compare the primary key sets of the source and target ends. If the primary keys are inconsistent, the data is considered inconsistent. | Stable primary key and focus on whether each record is written to the database; source/target must have a primary key or unique index. |
Reconciliation Scope
Scope | Action | User input |
Full reconciliation | Reconcile full data between source / target tables | - |
Incremental reconciliation | Reconcile data filtered by specified conditions, suitable for time-based partitioning. | Source-side filter condition + target-side filter condition |
Incremental Reconciliation Note:
Fine-grained reconciliation is supported (for example, by hourly partitions), and grouping syntax can be used.
The source and target filter conditions must each comply with the SQL syntax of the corresponding data source.
If a field in the filter condition does not exist in the source or target, you need to modify it by table in "Single-Table Reconciliation". Otherwise, reconciliation for that table will fail.
Example (reconciliation by hourly partitions):
select count(1), partition from table where partition > ${yyyyMMddHHmmss-8H} group by partition
Note:
The filter SQL on the target side is executed in Flink SQL, and only Flink SQL syntax is supported:
Use ${YYYY-MM-dd} to express the current date. Do not write CURRENT_DATE().
Use <> for "not equal to". Do not write !=.
Reconciliation fails if the SQL does not comply with Flink SQL syntax.
Step 3: (Optional) Customizing Reconciliation by Table
A global policy applies to all tables in the task. If differentiated processing is required:
1. In the reconciliation step of the configuration process, click the single table that requires differentiated processing.
2. You can enable or disable data reconciliation for a single table, or modify the incremental reconciliation conditions for a single table.
Note:
Changes to single-table reconciliation take effect after the task is published and running. If a table requires different filter conditions or does not participate in reconciliation, adjust it completely before saving.
Step 4: (Optional) Configuring Reconciliation Alarms
The alarm rule page for real-time synchronization tasks supports configuring data reconciliation alarms. This takes effect only for tasks with data reconciliation enabled.
Alarm Metric | Description | Threshold |
Data reconciliation / Task failed. | The data reconciliation resident task failed to run. | Send N alarms every X minutes/hours/days upon failure. |
Data reconciliation / Reconciliation failed | Some tables in the reconciliation details are in the reconciliation failed state. | Send N alarms every X minutes/hours/days upon failure. |
Data reconciliation / Reconciliation time | The duration of a single data reconciliation exceeds the threshold. | Reconciliation time > X minutes/hours/days |
Data reconciliation / Number of inconsistent data records | The number of data discrepancy records in some tables in the reconciliation details is not 0. | Number of inconsistent data records > N |
Alarms are triggered for the number of inconsistent data records at table granularity. An alarm is triggered when the number of inconsistent records in any table exceeds the threshold.
Viewing Reconciliation Results
Real-time synchronization tasks perform continuous and periodic reconciliation based on the configured first reconciliation time and reconciliation interval.
Entries
Reconciliation Overview Metrics
Metric | Description |
Total Number of Tables | Number of tables involved in the synchronization task; in sharded database and table scenarios, counted by the number of tables on the destination side |
Number of Consistent Tables | Number of tables with identical row counts or primary keys between source and destination |
Number of Inconsistent Tables | Number of tables with differences between source and destination (number of differences ≠ 0) |
Number of Failed Reconciliations | Number of Tables That Failed During Reconciliation |
Number of Unreconciled Tables | Number of tables for which data reconciliation is not enabled |
Reconciliation Details
The reconciliation results are displayed row by row for each table in the details. For records that are reconciled based on primary keys and have the number of differences not equal to 0, view difference details is supported:
Item | Description |
Difference details preview limit | 10000 entries |
Display Fields | Display primary key fields only |
Reconciliation Task Statuses and Supported Operations
The reconciliation task is a resident task that runs automatically at the configured time interval:
Status | Description | Available Operations |
Reconciliation task not started | Not started | Running |
Reconciliation task running | Running | Stop. |
Reconciliation task stopped | Manually stopped | Running |
Reconciliation task failed | Startup failed or running failed | Running |
Reconciliation task in progress | Intermediate state | - |
Verification Result
After the operation is completed, you can confirm in the following ways:
1. In the Data Reconciliation overview, verify that the total number of tables and the counts of each category meet expectations, and that the number of unreconciled tables equals the number of tables without reconciliation configured.
2. New reconciliation records are generated periodically in Reconciliation Details.
3. When reconciliation alarms are configured, notifications can be received through the alarm channel when trigger conditions are met.
4. When a table-specific custom reconciliation configuration takes effect, the reconciliation rules for that table differ from the global rules when details are viewed.
FAQs
Q: Does Enabling Data Reconciliation Slow Down Task Performance?
A: There will be some impact. Data reconciliation needs to pull data from the source and target, which places a certain load on the data source. In addition, the reconciliation task and the synchronization task share the same computing resources, consuming resource group resources. Recommendations:
Do not set the reconciliation interval too short (minimum 60 minutes).
For large tables, use Verify Data Row Count. For small and medium tables, use Verify Data Content.
Resource group monitoring metrics include the resource usage of data reconciliation instances. Evaluate the resource group specification based on the actual load.
Q: How Do I Troubleshoot a Single-Table Reconciliation Failure?
A: Common causes:
1. If a field in the filter condition does not exist in the source or target, adjust the incremental reconciliation condition for that table in the single-table reconciliation configuration to make the field name match.
2. The filter SQL on the target side uses non-Flink SQL syntax, such as CURRENT_DATE() and !=. Replace them with ${YYYY-MM-dd} and <>.
3. Incremental reconciliation is configured for MongoDB. MongoDB does not support incremental reconciliation. Change it to full reconciliation or disable reconciliation for this table.
4. The task involves more than 5000 tables and cannot be configured on a per-table basis. The current scale cannot support single-table reconciliation. Split the task into multiple tasks.
Q: Why Is the Number of Differences 0 but the Content Still Inconsistent for Primary Key-Based Reconciliation?
A: Yes. Currently, Verify Data Content only compares the primary key set, not other field values. If your business requires full-field consistency comparison, consider the following recommendations:
Or customize a comparison script through an SQL task. For details, see SQL Task. Q: What Should I Do If Only 10,000 Difference Details Are Displayed?
A: Difference details are only used to quickly locate difference samples. When the number exceeds 10000, consider the following recommendations:
1. Determine the cause of the inconsistency by using difference samples, such as missed DDL synchronization, omitted filter conditions, or write failures.
2. After fixing the synchronization task, restart the reconciliation once.
3. If full comparison is required, we recommend that you create a separate SQL task to write the source and target primary key sets to intermediate tables and then compute the difference.
Q: Can a Reconciliation Task Be Stopped Separately Without Affecting Data Synchronization?
A: Yes. The reconciliation task is a resident task. You can click Stop individually in Data Reconciliation, and this does not affect the main data synchronization task. After stopping, click Run to resume.
References