How to troubleshoot data quality issues in single-table batch synchronization - DataWorks

How it works

DataWorks Data Integration uses a parallel processing and plugin-based architecture to achieve efficient and stable data synchronization.

Parallel execution model (job and task)

To maximize data throughput, a synchronization task uses a two-level execution structure:

Job: A running instance of a synchronization task.
Task: The smallest execution unit of a job. A job is split into multiple tasks that can run concurrently on one or more machines.

Each task is responsible for processing an independent data shard. This parallel processing mechanism significantly improves overall data synchronization efficiency.

Plugin-based data flow (reader and writer)

Within each task, the data flow is orchestrated by a reader plugin and a writer plugin connected by an in-memory buffer:

reader plugin: Connects to the source data store, reads the data, and pushes it to the internal buffer.
writer plugin: Consumes data from the buffer and writes it to the destination data store.

Note

The reader plugin and writer plugin strictly adhere to the native protocols and constraints, such as data types and primary key restrictions, of their respective data sources. Therefore, the final synchronization behavior and data consistency depend on the implementation of the source and target systems.

Troubleshoot writer-side data consistency

The Data Integration writer plugin writes data from the source to the destination. Each destination data source type has a corresponding writer plugin. The writer plugin submits data to the destination by using JDBC or the data source's SDK based on the configured write mode, including conflict resolution strategies.

Note

The actual write results and data content at the destination depend on the write mode and the constraints of the destination table.

If you encounter issues with data quality, such as record counts or data content, after a data synchronization task completes, review the following common writer-side issues.

Cause	Description	Solution
Improperly configured write mode	The writer plugin writes source data to the destination using the selected write mode. If the source data conflicts with the destination table's constraints, it can result in failed inserts (dirty data), silent discards, or record replacements.	Select the correct write mode for your use case. For more information, see Appendix: Write modes for relational databases.
Dirty data threshold reached	The amount of dirty data, caused by issues like data type mismatches or oversized content, exceeds the configured threshold, causing the task to fail and preventing some data from being written.	Identify the cause of the dirty data and resolve the issue. Alternatively, determine if the dirty data can be tolerated and ignored. Note If your task cannot tolerate dirty data, you can modify the dirty data threshold in Task Configuration > Channel Configuration. For more information about how to configure the dirty data threshold, see codeless UI configuration. For more information about what constitutes dirty data, see Data Integration.
Querying data too early	Data is queried before the synchronization task completes. For some data sources, such as Hive and MaxCompute (configurable), data may be partially or completely unavailable until the task finishes.	Always verify the data in the target table after you confirm that the synchronization task instance has run successfully.
Missing node dependency	If no explicit Directed Acyclic Graph (DAG) dependency is configured between a downstream analysis task and an upstream synchronization task, the downstream task may start prematurely and read incomplete data.	In DataStudio, configure an explicit parent-child node dependency between upstream and downstream tasks. Avoid using weak dependencies, such as `max_pt`.
Multiple synchronization tasks write to the same table or partition concurrently, causing interference.	Unsafe concurrent execution of synchronization tasks. For MaxCompute and Hologres, if two tasks write to the same partition and are configured to truncate the partition before writing, the second task may clear the data written by the first task. For relational databases, if pre-SQL or post-SQL statements are configured, the SQL statements from a second task can interfere with the data written by the first task.	If the conflict is caused by multiple periodic instances of the same node, you can configure a self-dependency to ensure that the next instance starts only after the previous one is complete. Avoid designing tasks that write to the same destination concurrently.
Task not configured for idempotent execution	The task is not idempotent, meaning multiple runs produce different results. Rerunning the task can cause duplicate data to be inserted or data to be overwritten incorrectly.	1. Whenever possible, design the task to be idempotent. For example, use the `REPLACE INTO` mode. 2. If you cannot make the task idempotent, exercise caution when you rerun it and configure success alerts to prevent unnecessary retries.
Incorrect partition expression	In MaxCompute, most data tables are partitioned, and the partition values are often DataWorks scheduling parameters like $bizdate. Common errors include: The scheduling parameter is not correctly replaced. As a result, data is written to a partition with a literal name like ds=$bizdate instead of the actual business date, such as ds=20230118. A downstream query uses a partition expression that is not evaluated correctly, causing the query to read data from the wrong partition.	Check the variable expressions in the data synchronization task. Verify that the scheduling parameter configuration is correct and that the runtime parameters in the task instance evaluate to the expected values.
Data type or time zone mismatch	Inconsistent data types or time zone settings between the source and destination can cause data to be truncated, converted incorrectly, or result in discrepancies during comparisons.	Confirm the differences in data type and time zone between the source and destination. Decide whether to accept the current state or modify the data type and time zone parameters at the destination.
Destination data changed by other processes	If other applications modify the destination data source concurrently, its content may become inconsistent with the source data.	Ensure that no other processes write to the destination table during the synchronization window. If concurrent writes are expected behavior, you must accept the resulting data discrepancies.

Appendix: Write modes for relational databases

Protocol type	Write mode	Behavior on conflict	Behavior without conflict	When to use
General/MySQL Protocol	`insert into`	The insert fails and generates dirty data.	Inserts new data.	Append data for full or incremental synchronization without overwriting or modifying existing records.
	`replace into`	Replaces the old row by first deleting it and then inserting the new row.	Inserts new data.	Completely overwrite old records with the latest data.
	`insert into ... on duplicate key update`	Updates the old row by updating only specified fields with new data.	Inserts new data.	Update specific fields in a record while preserving others, such as a creation timestamp.
	`insert ignore into`	Ignores the new row without writing it or generating an error.	Inserts new data.	Insert only data that does not already exist and take no action on existing records.
PostgreSQL	`insert on conflict do nothing`	Ignores the new row without writing it or generating an error.	Inserts new data.	Insert only data that does not already exist and take no action on existing records.
	`insert on conflict do update`	Updates the old row by updating specified fields in the conflicting row with new data.	Inserts new data.	Update specific fields in a record while preserving others, such as a creation timestamp.
	`copy on conflict do nothing`	Discards conflicting rows. Uses the high-performance `COPY` protocol and ignores new data on conflict without generating dirty data.	Bulk inserts new data.	Efficiently append large batches of data while allowing duplicate records to be skipped.
	`copy on conflict do update`	Updates conflicting rows. Uses the `COPY` protocol and overwrites old data with new data on conflict.	Bulk inserts new data.	Efficiently synchronize large batches of data, overwriting old records with the latest data.
-	`merge into`	Not supported.

Troubleshoot reader-side data consistency

The Data Integration reader plugin connects to the source data store, extracts the data, and delivers it to the writer plugin. Each source data source type has a corresponding reader plugin. The plugin uses JDBC or the data source's SDK to extract data based on the configured extraction mode, which can include filters, tables, partitions, and columns.

Note

The actual read results depend on the data synchronization mechanism, changes to the source data, and the task configuration.

If you encounter issues with data quality, such as record counts or data content, after a data synchronization task completes, review the following common reader-side issues.

Cause	Description	Solution
Concurrent changes to source data	External applications may still be modifying the source data during the read operation. Therefore, the synchronization task captures a data snapshot at the time of reading, not the absolute latest state of the data. To enable parallel reading, a synchronization job is split into multiple tasks that issue independent database queries. Due to database transaction isolation, each task retrieves a data snapshot from a different point in time. As a result, the job cannot capture data changes that occur after all its tasks have started.	Accept this behavior as an expected outcome of high-throughput data synchronization. Rerunning the task may yield different results due to real-time changes in the source data.
Incorrect filter conditions	For MySQL, you can configure a `WHERE` clause to filter extracted data. If the clause contains a scheduling parameter, such as in `gmt_modify >= ${bizdate}`, a common error is incorrect parameter replacement. For example, the task might filter only one day of data when two days are required. For MaxCompute, when you read data from a partitioned table, variable expressions are often configured for partition parameters, such as `pt=${bizdate}`. These parameters can be easily misconfigured or fail to be replaced.	Check the scheduling variable expressions in the data synchronization task. Verify that the scheduling parameter configuration is correct and that the parameter values are replaced as expected at runtime.
Reader-side dirty data	A parsing failure occurs when reading source data. This is rare in structured databases but can happen with semi-structured data sources, such as CSV or JSON files in OSS. Format errors may cause some records to be skipped.	Check the task run log for parsing errors or format exceptions, then fix the source data files. Adjust the dirty data tolerance configuration.

Troubleshoot environment issues

Cause	Solution
Querying the wrong data source, table, or partition	In a DataWorks workspace in standard mode, development and production environment isolation is enforced. A single-table batch synchronization task uses the development data source in the development environment and the production data source in the production environment. When you compare data quantity and content, confirm which data source environment you are querying to avoid discrepancies. In production, online data sources often have corresponding staging or testing environments. The database used by the synchronization task may differ from the staging or testing database. When verifying data, check for these environmental differences. When you synchronize semi-structured data, multiple files are often involved. Confirm that you are reading from and writing to the complete set of files.
Upstream dependencies not met	If the data is generated periodically, such as by a recurring data synchronization task or merge task, ensure the upstream tasks that generate this data have run and completed successfully.

Note

As a general troubleshooting step, you can run the task multiple times to observe and compare the synchronization results. You can also try switching the source or destination data source for comparative testing. These tests can help you narrow down the problem.