Which successful sync attempts have no target record?

Define the decision and input records

A success log is a process observation, not proof that the required target state exists now. NOT EXISTS checks the actual target identity and DISTINCT prevents repeated success logs from multiplying review rows. Confirm the target export is complete and taken at an appropriate cut-off before treating a missing row as a failed write.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE import_rows(id INTEGER PRIMARY KEY, external_id TEXT, email TEXT, amount_cents INTEGER, business_date TEXT);
CREATE TABLE sync_attempts(id INTEGER PRIMARY KEY, external_id TEXT, outcome TEXT);
CREATE TABLE target_records(id TEXT PRIMARY KEY, external_id TEXT UNIQUE);
INSERT INTO import_rows VALUES (1,'X1','a@example.test',10000,'2026-10-01'),(2,'X2',' ',20000,'03/04/2026'),(3,'X3',NULL,NULL,NULL),(4,'X4','d@example.test',0,'2026-02-30');
INSERT INTO sync_attempts VALUES (1,'X1','success'),(2,'X2','success'),(3,'X3','failed');
INSERT INTO target_records VALUES ('T1','X1');

Run the query and compare the result

SELECT DISTINCT a.external_id FROM sync_attempts a
WHERE a.outcome='success' AND NOT EXISTS (
 SELECT 1 FROM target_records t WHERE t.external_id=a.external_id
) ORDER BY a.external_id;

X2 has a success attempt but no matching target record; X1 has T1 and passes. X3 failed and belongs in the failure queue rather than this discrepancy list. Investigate X2’s response and target history before retrying, because a delayed target snapshot can otherwise lead to a duplicate write.

Apply the result within its limits

This uses a complete synthetic target snapshot with unique external IDs. Eventual consistency, record deletion and partial exports can all create false discrepancies. Read-only reconciliation does not provide an idempotent retry implementation.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.