Compare

After you have run the job and the data replication process has started, you might want to check that the data in source and target Locations is in sync. For that, Fivetran Local Data Processing has the Compare feature. It allows you to compare data in two or more locations (databases) in a Channel. It compares the table structures as well as the data they contain. You can compare a single source location against multiple target locations.

The benefits of using Local Data Processing Compare feature are the following:

Compare will ensure that no data is missing, for example due to a human error
Compare allows to compare an entire database (all tables) or specific tables only
Compare works heterogeneously (across a variety of databases). This means that your source can be compared against the target location(s) no matter if they are the same or different database types

The following methods are available for executing Compare:
via UI – see
via CLI – see
via API – see /api/latest/hubs/{hub}/channels/{channel}/compare in .

Compare Types

If your source table has been pre-populated with data, there are two types of Compare you can choose from:

Bulk Compare: Local Data Processing calculates the checksum for each table in the channel and compares these checksum to report whether the replicated tables are identical.
Row-by-Row Compare: Local Data Processing extracts the data from a source (read) location, compresses it, and transfers the data to a target (write) location(s) to perform a Row-by-Row Compare. Each individual row is compared to produce a 'diff' result. For each detected difference, an SQL statement is written: an insert, update, or delete.

You can set a Compare type in the CLI using of or in the UI enable Bulk Compare by selecting the option.

Online Compare

You can choose to perform online compare, which is a live Compare between locations with rapidly changing data. While performing a compare, if the online compare option is defined, Local Data Processing processes the changes that occur during the compare and does not miscount them as differences. You can define the online compare in CLI using of or in the UI by selecting the option.

Direct File Compare

Local Data Processing also allows you to perform the Compare on the file locations using the Direct File Compare method, which is performed against a file location. This Compare method is a faster alternative for file compare via Hive External Tables and also helps to avoid compare mismatches caused by data type coercion through Hive deserializer.

During direct file Compare, Local Data Processing reads and parses (deserialize) files directly from the file location instead of using the HIVE external tables (even if it is configured for that location). In direct file compare, the files of each table are sliced and distributed to prereader sub tasks. Each prereader subtasks reads, sorts and parses (deserialize) the files to generate compressed(encrypted) intermediate files. These intermediate files are then compared with the database on the other side.

The number of prereader subtasks used during direct file Compare can be configured using the compare option (CLI ).
The location to store the intermediate files generated during Compare can be configured using the location property .

To perform a direct file compare:

against a source file location, action with parameter should be defined.
against a target file location, action with parameter should be defined.

Limitations

Direct file Compare does not support Avro, Parquet or JSON file formats.
Direct file Compare is not supported if action with parameter is defined on a file location involved in the compare.
Direct file Compare is not supported when the channel is a 'blob' file channel. A blob file channel has no table information and simply treats each file as a sequence of bytes without understanding their file format.
Direct file Compare for XML files requires each XML file to contain a single table.

Slicing

Sometimes, the amount of data that the Compare job needs to process is too big. In this case, you can choose to divide the table into a few batches and process them in parallel. In Local Data Processing, this is achieved via the functionality. By configuring Slicing, you can divide your database table into a few pieces that will be processed in parallel saving you a lot of time.

Local Data Processing suggests a few types of slicing, each fitting best for a specific business case. For more information about slicing types and when it is best to use them, refer to the article.