Repairing drift

Fix rows that differ between source and target by re-reading their keys, or reload one table while the others keep streaming.

SQL Flow repairs a target table by table while the pipeline keeps streaming, for every source, capture mode and target. Repair keys rewrites the rows you list from the source. Resync brings one whole table back in sync with the source. A compare run can queue the right repairs for you.

Before you begin

• The pipeline is streaming, on an online agent. Otherwise the repair is refused with "Start the pipeline first: a table is repaired while it streams."
• No target of the pipeline is paused. A repair covers all targets at once.
• With a SQL Server Native CDC source, the source login can run sys.sp_repltrans. Capture doesn't need it, but a repair is refused without it.

Repair the drift a compare found

1. On the Compare tab, select the run in History.
2. Select Repair drift. The button reads Repair drift and incomplete tables or Repair incomplete tables when the run has tables whose row list was cut short.

SQL Flow queues one key repair for each table with confirmed drift, using the keys from the run's diff, and a resync for each table whose row list was cut short. The tab confirms, for example: "Queued 2 table repairs (progress on the Tables tab); skipped dbo.Audit: …".

With the CLI, add --table to repair one table only:

sqlflow pipeline repair-drift <pipeline_name> <compare_run_id> --table <schema.table>

Repair listed keys

1. Open the pipeline's Tables tab.
2. On the table's row, select Repair keys….
3. Enter the keys in Repair keys of <table>, then select Repair keys.

For a one-column key, enter bare values, one per line or separated by commas, or as a JSON array:

17, 42, 99

For a key of several columns, enter one JSON object per line that names every key column:

{"OrderId": 7, "LineNo": 2}
{"OrderId": 7, "LineNo": 3}

One repair takes up to 100,000 keys; split a longer list.

With the CLI, pass the keys inline or from a file, and add --wait to wait for the result:

sqlflow pipeline repair-keys <pipeline_name> --table <schema.table> --keys-file <keys.txt> --wait

While the repair runs, the table's row shows its progress, such as 120 re-written, 3 absent at the source, of 123 keys. Other tables keep streaming. The target's apply pauses briefly while each batch of keys is written.

Resync a table

1. Open the pipeline's Tables tab.
2. On the table's row, select Resync.
3. Confirm with Resync table.

The confirmation says what happens: "This resynchronizes <table> from the source. Existing target rows can be updated or removed, and <table> may be incomplete until the resync completes. Other tables keep streaming."

SQL Flow chooses how to rewrite the table from the capture mode and the target. Either it repairs the table key by key and its rows stay on the target throughout, or it empties the table (the phase reads truncating) and copies it again, and the table is incomplete on the target until the resync completes. Both end with the table matching the source.

sqlflow pipeline resync <pipeline_name> --table <schema.table> --yes --wait

A resync is refused when:

• another repair of the same table is running;
• the resync would empty the table, and the table has, or is referenced by, a foreign key on the target;
• a schema review of the table is open.

Follow progress

The Tables tab lists every resync and key repair under Table repairs, with its state. A repair that fails or is refused shows a red alert under the heading, for example "Table repair: Resync of dbo.Orders failed: …". A table whose repair failed isn't exact on the target until it is repaired or resynced again.

With the CLI, list the pipeline's repairs and their progress:

sqlflow pipeline table-ops <pipeline_name>

Troubleshooting

Too many keys in one repair

<n> keys exceed the 100000-key limit of one repair; split the list.

Split the keys into several repairs.

Key is missing a primary-key column

a key omits primary-key column '<column>'

A JSON key object must name every key column of the table.

See also

• Compare
• Automatic repair policy
• Lifecycle
• Pipelines commands