Compare

Compare a target with its source, on demand or on a schedule, to find rows that are missing, extra or different.

A compare checks every row of every selected table at a target against the source, while the pipeline keeps streaming. It tells real drift apart from changes that are still on their way, and lists the keys of the rows that differ so you can repair them. Compare works for every source, capture mode and target.

Before you begin

• The pipeline is streaming: not loading, restarting, paused or in a dry run. Only a streaming pipeline can show which differences are changes still in flight.
• The agent that runs the pipeline is online.
• Every table has a primary key, or a unique index that SQL Flow can use as its key.

Run a compare

1. Open the pipeline and select the Compare tab.
2. If the pipeline has more than one target, choose it in Target.
3. Select Run compare.

The button shows Compare running… until the run ends. The run appears at the top of History, and its detail opens below.

With the CLI, the command waits for the result and sets its exit code: 0 clean, 3 drift, 4 unconfirmed or refused tables, 1 failed.

sqlflow pipeline compare <pipeline_name> --target <target_id> --diff-out <file>

Replace <target_id> with the target's id as the Target list shows it, or leave out --target to compare the primary target. --diff-out saves every differing key to <file>. Add --no-wait to start the run and return.

What a compare reads

A compare reads every row on both sides; it doesn't sample. It compares checksums of groups of rows first and reads row by row only where they differ. A row that differs because the target hasn't applied a recent change yet is read again once the pipeline has caught up, and counts as drift only if it still differs. Rows still unsettled after the last re-check are reported as in flight.

A compare's reads aren't rate-limited, and each run reports the source queries it used. On a large table, run it in a quiet period or on a schedule.

The compare uses the target's table and column names, and applies the target's row filter, output columns and masks to the source rows before it compares them.

Schedule compares

Under Run compare, tick Scheduled compare and choose how often: every hour, every 6 hours, daily or weekly. A new pipeline's schedule is on, daily.

• A scheduled compare checks the pipeline's primary target. With the all-tables automatic repair policy, it checks every target.
• Archived pipelines are skipped.
• A scheduled run that can't start is skipped, and the tab shows Last scheduled attempt skipped: <code>: <reason>.

With the CLI, the interval can be any number of minutes from 15 to 43,200:

sqlflow pipeline compare-schedule <pipeline_name> --enabled true --interval-minutes 240

Read the results

Each run ends with one verdict:

While a run is going, History shows comparing, or needs attention when it is blocked. A run that ends with an error shows failed. The History list shows when each run was requested, whether it was manual or scheduled, its verdict, and its drift rows, in-flight rows and source queries. With the CLI, sqlflow pipeline compare-history <pipeline_name> lists the same runs and the schedule.

Select a run to see its detail:

• One row per table with its status (match, drift, unconfirmed or refused), the source and target row counts, and the Missing, Extra, Differ and In flight counts.
• Diff sample (keys + reason): up to 200 rows, each with its kind (missing in target, extra in target or value mismatch), the table, the key values and the reason, such as the column that differs.
• download diff (NDJSON): every differing key. The CLI equivalent is sqlflow pipeline compare-diff <pipeline_name> <run_id> --out <file>.

When retained runs confirm drift, the tab shows "Data drift: the target differs from the source", with the number of differing rows. A clean compare of another target doesn't clear it. Repair the rows, then compare that target again.

A drift verdict also raises the alert Data drift: target differs from source (error), and a failed run raises Compare failed (warning). See Alerts.

Limits

Settings

The CLI can change how a run reads. The tab uses the defaults.

Raise the re-check rounds or delay if a busy pipeline often ends unconfirmed.

Troubleshooting

Pipeline isn't streaming

The pipeline is <status>. Compare needs it streaming: only a streaming pipeline can show which differences are changes still in flight.

Start or resume the pipeline, and wait for the initial load to finish.

Another compare is still running

Compare <id> of this pipeline is still running.

Only one compare runs at a time. Wait for it to finish.

See also

• Repairing drift
• Automatic repair policy
• Pipeline detail
• Pipelines commands