Payroll Vendor Feed Creates Duplicate Job Changes

Load Only replays unconditionally - it was never going to protect you. The options:

1. Switch to Load and Resolve with a defined resolve key (for example Employee ID plus effective date). A replayed row collapses onto the existing transaction instead of creating a second one. Cost: you must define and test which record wins when two genuinely differ, and an incorrect resolve rule silently discards a real correction.
2. Pre-query before load. Have the integration read existing Change Job transactions for the incoming employee IDs and drop rows that already match. Cost: runtime, and a race window if anything else writes between the query and the load.
3. Reject at the source. Require a file-level or batch identifier from the vendor and refuse files you have already processed. Cheapest and most robust, but depends on a third party's delivery timeline.
4. Make the file idempotent by content - hash the row and skip identical ones. Reasonable middle ground; still needs somewhere to store the seen set.

Do not forget the 1,900 already-created duplicates. They are real transaction history now. Correcting them is another data load with its own audit trail and its own business process consequences - plan and approve it, don't script a delete.

Sequencing. Organizations are referential dependencies of workers. Either run the org load as an earlier step in the same integration with an explicit dependency and failure gate, or split into two integrations run in order. A worker row that references an org which isn't there yet should fail the batch, not be written.

Validation. Add a resolve step that maps the vendor's org identifier to a Workday reference ID and fails the row when it cannot resolve, rather than defaulting. The failure should surface in the integration event - right now the 40 rows succeeded with bad data, which is the worst outcome.

Root cause is the mapping table, not the integration. The vendor's table lags a day, so an org created today cannot be referenced until tomorrow. Two durable options:

• Reference by Workday ID, not by name, so the tenant is the source of truth and the vendor carries an opaque key.
• Stage the mapping refresh ahead of the worker feed and hold the worker file until the org mapping is confirmed current. A one-day delay in a nightly feed is cheaper than 40 misassigned workers.

Also check the effective date on those 40 rows. If they were written with a past effective date, correcting them later leaves a visible wrong period in reporting and in the workers' history.

Treat it as a data migration, not a spreadsheet upload.

1. Decide the effective-date strategy first. Historical assignments written with past effective dates create job history and, depending on configuration, Change Job business process instances. Confirm whether the load will trigger business processes, and if so who is in the approval queue and how you clear it without generating 22,000 legitimate-looking approvals.
2. Split it. Run by region or by supervisory org in batches sized so a failure is diagnosable and re-runnable. A single 22,000-row file that fails at row 19,000 wastes the day.
3. Test in Sandbox on a subset that deliberately includes edge cases: workers with multiple jobs, workers who have terminated, orgs that no longer exist, and orgs that changed name since the historical date. Compare the resulting job history against a sample of ten known-good workers before running the rest.
4. Run validate before load on the full file, resolve every error to zero or an accepted exclusion, and record the accepted exclusions.
5. Reconcile after. A headcount-by-supervisory-org report for the historical periods before and after the load - the delta should exactly match your file.
6. Confirm the rollback story before you start. A load that triggers BPs is not cleanly reversible; if you need a reversal plan, that's a reason to reduce the scope of the load.