Design an idempotent nightly full-vs-delta SIS-to-downstream roster synchronization job with soft-delete handling

Steps

1. Pull the full source-of-truth roster set (or a delta since the last successful run) keyed by a stable source identifier (e.g. the SIS's immutable student/staff ID), never by mutable fields like email or name.
2. Stage the incoming batch in a separate table/partition first, then diff it against the downstream system's current active record set by source ID to classify each record as new, changed, unchanged, or missing-from-source.
3. For records present downstream but absent from the latest full source pull, soft-delete them (set an inactive/deleted flag and timestamp) rather than hard-deleting, so historical references such as grades and enrollments remain valid.
4. Make each run idempotent by upserting on the source ID with a run/batch identifier and last-synced timestamp, so re-running the same day's job twice produces the same end state rather than duplicates or conflicts.
5. Run a full reconciliation pass periodically even if delta feeds are used intraday, since deltas alone cannot detect deletions — a record's absence is only knowable by diffing against a complete snapshot.
6. Log reconciliation summary counts (created/updated/soft-deleted/unchanged) per run so drift between SIS and downstream state is auditable.