Reconciling Roster Deltas Between Systems

The exact task this guide solves is comparing a roster from one system against the same roster from another — a scheduling platform versus a crew-management system, or last night’s snapshot versus this morning’s — and producing a deterministic set of adds, removes, and changes that a pipeline can apply safely. Two sources of the same roster almost never agree byte-for-byte: one lags, one carries a manual override, one formats a timestamp differently. A pipeline that overwrites blindly loses the override or the newer data; one that appends everything duplicates assignments. The fix is a keyed diff, a conflict classification, and an authority-ranked merge. This page builds all three. It is part of the crew roster API integration section and produces the clean, deduplicated events the flight data ingestion pipeline needs.

Prerequisites

Python 3.11+ with typed models.
A stable business key per assignment — crew id plus pairing id, not a source-specific row id.
A source authority ranking — which system wins a conflict, and when a manual override outranks both.
UTC-normalised timestamps so a formatting difference is not mistaken for a change.

Diff on a business key, not a row id

The foundation of a correct reconciliation is keying each assignment on a business identity that is stable across systems — the crew member and the pairing — rather than a source-generated row id that differs between platforms. With a shared key, the diff reduces to set operations: keys only in the source are adds, keys only in the target are removes, and keys in both with differing content are changes. Everything downstream depends on that key being right.

Step 1 — Compute the keyed diff

Indexing each side by its business key turns the comparison into three set operations. Normalising the compared fields first ensures a cosmetic difference does not register as a change.

from dataclasses import dataclass


@dataclass(frozen=True)
class Assignment:
    crew_id: str
    pairing_id: str
    report_utc: str      # ISO-8601, UTC-normalised
    role: str

    @property
    def key(self) -> tuple[str, str]:
        return (self.crew_id, self.pairing_id)

    @property
    def content(self) -> tuple[str, str]:
        return (self.report_utc, self.role)


def diff(source: list[Assignment], target: list[Assignment]) -> dict:
    src = {a.key: a for a in source}
    tgt = {a.key: a for a in target}
    added = [src[k] for k in src.keys() - tgt.keys()]
    removed = [tgt[k] for k in tgt.keys() - src.keys()]
    changed = [(tgt[k], src[k]) for k in src.keys() & tgt.keys() if src[k].content != tgt[k].content]
    return {"added": added, "removed": removed, "changed": changed}

Verify: an assignment present only in the source appears in added; one whose report_utc differs between sides appears in changed; one identical on both sides appears nowhere — the diff is empty for agreeing rosters.

Step 2 — Classify each change as safe or conflicting

Not every change is safe to apply. A change the target has not been manually overridden on is safe; a change that would overwrite a local override is a conflict requiring the authority ranking. Carrying an overridden flag lets the classifier separate them.

@dataclass(frozen=True)
class TargetAssignment(Assignment):
    overridden: bool = False   # a manual local edit that must not be silently lost


def classify_change(target: TargetAssignment, incoming: Assignment) -> str:
    if target.overridden:
        return "conflict"       # incoming would clobber a manual override
    return "safe"

Verify: a change to a non-overridden target classifies as safe and applies automatically; a change to an overridden target classifies as conflict and is held for the authority merge rather than applied.

Step 3 — Merge conflicts by authority

Conflicts are resolved by a ranking that gives a manual override the highest authority, then the more trusted system, then recency. Encoding the ranking as data makes the merge policy explicit and auditable rather than buried in comparison logic.

AUTHORITY = {"manual_override": 3, "crew_mgmt": 2, "scheduling": 1}


def resolve_conflict(target: TargetAssignment, incoming: Assignment,
                     incoming_source: str) -> Assignment:
    target_rank = AUTHORITY["manual_override"] if target.overridden else AUTHORITY["crew_mgmt"]
    incoming_rank = AUTHORITY[incoming_source]
    # Higher authority wins; ties fall to the incoming (assumed newer) value.
    return target if target_rank > incoming_rank else incoming

Verify: an incoming scheduling change loses to a manual override on the target; an incoming crew_mgmt change beats a non-overridden scheduling target — the ranking decides deterministically, so the same inputs always merge the same way.

Reconciliation keys both rosters on the same business identity, diffs them into adds, removes, and changes, and resolves change conflicts through an explicit authority ranking that protects manual overrides.

Failure modes and troubleshooting

Diffing on a source row id. Row ids differ between systems, so every row looks added-and-removed. Remediation: key on a stable business identity.
Cosmetic differences as changes. A timestamp formatted differently registers as a change and churns the pipeline. Remediation: normalise compared fields to UTC ISO-8601 before diffing.
Silently clobbering overrides. Applying every incoming change overwrites manual edits. Remediation: classify changes against an overridden flag and route conflicts to the authority merge.
Non-deterministic merge. Resolving ties by wall-clock arrival makes the same inputs merge differently across runs. Remediation: rank by explicit authority, with a defined tie-break.
Applying removes blindly. A source that lags may omit a valid assignment, and treating it as a remove deletes real data. Remediation: require corroboration or a soft-delete before hard-removing.

Frequently Asked Questions

Why key the diff on a business identity?

Because a source-generated row id is meaningless across systems — the same assignment carries different ids in each. Keying on the crew member and pairing gives a stable identity both sides share, which is what makes the set-based diff correct.

How are manual overrides protected?

By an overridden flag and an authority ranking that places a manual override above any automated source. A change that would overwrite an override is classified as a conflict and only applied if the incoming source outranks a manual edit, which by policy it never does.

What if a source lags and omits assignments?

A lagging source can make a valid assignment look removed. Rather than hard-deleting on a single source’s omission, the pipeline requires corroboration or uses a soft delete, so a temporary lag does not erase real data.

Is the merge deterministic?

Yes. Conflicts are resolved by an explicit authority ranking with a defined tie-break, so identical inputs always produce the same merged result — which is what makes the reconciliation auditable and safe to replay.

Crew Roster API Integration — the parent section and connection patterns.
Handling Roster API Rate Limits and Retries in Python — how the rosters are fetched before reconciliation.
Data Schema Validation Rules — validating each assignment before it is diffed.
Flight Data Ingestion & System Sync — the pipeline this feeds.

Back to Crew Roster API Integration.