Broadcast Traffic Scheduling Automation

Reconciling Field Differences Between Avion and Avstar

This guide solves one exact task: when the same spot appears in both an Avion export and an Avstar export, deterministically diff the two rows into the canonical shape, classify each field difference as a match, a purely syntactic representation gap, or a genuine semantic mismatch, and route only the semantic conflicts to review. It is the reconciliation procedure under Avion vs Avstar Export Formats: A Side-by-Side Comparison, itself part of Avion & Avstar Ingestion Pipelines. It matters for audit because a broadcaster running both systems during a cutover cannot bill twice or bill differently for one airing — a spot that reconciles cleanly can be trusted to billing, while a semantic conflict is a revenue discrepancy that must be resolved before the log closes.

The hard part is that most differences between an Avion and an Avstar row are not disagreements at all — they are the same fact written two ways. 1930 station-local and 2026-07-14T23:30:00Z describe one instant; 425000 cents and 4250.00 dollars are one rate; P and PRIME are one daypart. A naive string diff flags all of these as conflicts and buries the traffic desk in noise. The reconciliation therefore normalizes both rows into the canonical shape first, using the same widening rules as the migration path, and only then compares — so that representation differences vanish and only true value differences remain, checked against the canonical traffic field data dictionary.

Prerequisites

  • Python 3.11+ — for the X | None union syntax and standard-library zoneinfo used to normalize Avion station-local times.
  • Pinned dependencies — pydantic==2.7.1 for the canonical models; the reconciler itself needs only the standard library (decimal, datetime, zoneinfo, logging, enum).
  • Both source rows keyed to one spot — a matched pair (AvionRecord, AvstarRecord) resolved to the same airing, joined on the house number per the spot schema.
  • A station timezone table — the same IANA-zone config the migration uses, so the Avion side normalizes to the identical UTC instant.
  • A rate-tolerance policy — an explicit Decimal tolerance (typically zero) for how much two rates may differ before the gap is called semantic, held in config rather than hard-coded.

Step-by-Step Implementation

The reconciler runs as a pure function over a matched pair: normalize both rows into CanonicalSpot, compare field by field, classify each difference, and emit a ReconciliationReport whose verdict is RECONCILED only when no field carries a semantic conflict.

Step 1 — Structured logging and the diff classification model

Goal: emit timestamp | level | module | spot_id audit lines and fix the vocabulary the reconciler uses to classify every field — the classification is the compliance artifact that says why a spot did or did not reconcile.

python 
from __future__ import annotations

import logging
from datetime import datetime, timezone
from decimal import Decimal
from enum import Enum
from typing import Optional

from pydantic import BaseModel

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
)
logger = logging.getLogger("avion_avstar.reconcile")


def _log(level: int, spot_id: str, msg: str) -> None:
    logger.log(level, "%s | %s", spot_id, msg)


class DiffClass(str, Enum):
    MATCH = "match"           # identical canonical values
    SYNTACTIC = "syntactic"   # same canonical value, different source form
    SEMANTIC = "semantic"     # genuinely different canonical values — a conflict


class FieldDiff(BaseModel):
    field: str
    avion_value: Optional[str]
    avstar_value: Optional[str]
    classification: DiffClass


class ReconciliationReport(BaseModel):
    house_number: str
    verdict: str                       # "RECONCILED" | "CONFLICT"
    diffs: list[FieldDiff]
    decided_at: datetime

Step 2 — Normalize both rows into the canonical shape

Goal: collapse representation differences before any comparison. The Avion side is widened (station timezone applied, cents to dollars, daypart letter resolved, implicit currency made explicit); the Avstar side is already canonical. After this step, a syntactic-only difference produces two equal canonical values.

python 
AVION_DAYPART_CODES: dict[str, str] = {
    "O": "OVERNIGHT", "M": "MORNING", "D": "DAYTIME",
    "E": "EARLY_FRINGE", "P": "PRIME", "L": "LATE",
}


class CanonicalSpot(BaseModel):
    house_number: str
    aired_at: datetime          # UTC
    daypart: str
    rate: Decimal               # dollars
    currency: str
    billing_code: Optional[str]


def normalize_avion(row: dict[str, str], station_tz: str,
                    default_currency: str = "USD") -> CanonicalSpot:
    from datetime import time
    from zoneinfo import ZoneInfo
    hh, mm = int(row["air_time"][:-2]), int(row["air_time"][-2:])
    naive = datetime.combine(datetime.strptime(row["air_date"], "%m%d%y").date(),
                             time(hh % 24, mm))
    aired = naive.replace(tzinfo=ZoneInfo(station_tz)).astimezone(timezone.utc)
    code = row["daypart_code"].strip().upper()
    return CanonicalSpot(
        house_number=row["house_no"].strip(),
        aired_at=aired,
        daypart=AVION_DAYPART_CODES.get(code, f"UNKNOWN:{code}"),
        rate=(Decimal(row["rate_cents"]) / Decimal(100)).quantize(Decimal("0.01")),
        currency=default_currency,
        billing_code=(row.get("billing_code") or "").strip() or None,
    )


def normalize_avstar(row: dict[str, str]) -> CanonicalSpot:
    # Avstar already carries an offset-aware timestamp and decimal rate.
    aired = datetime.fromisoformat(row["aired_at"]).astimezone(timezone.utc)
    return CanonicalSpot(
        house_number=row["house_number"].strip(),
        aired_at=aired,
        daypart=row["daypart"].strip().upper(),
        rate=Decimal(row["rate"]),
        currency=row["currency"].strip().upper(),
        billing_code=(row.get("billing_code") or "").strip() or None,
    )

Step 3 — Classify each field difference

Goal: compare the two canonical records field by field and label each result. The subtlety is distinguishing a syntactic difference (the source forms differed but the canonical values are equal) from a semantic one (the canonical values genuinely disagree). Only semantic differences are conflicts.

python 
def classify_field(
    name: str, avion_val: object, avstar_val: object,
    avion_raw: Optional[str], avstar_raw: Optional[str],
    rate_tolerance: Decimal = Decimal("0"),
) -> FieldDiff:
    # Rate comparison honours an explicit tolerance; everything else is exact.
    if name == "rate":
        equal = abs(Decimal(avion_val) - Decimal(avstar_val)) <= rate_tolerance
    else:
        equal = avion_val == avstar_val

    if equal and avion_raw == avstar_raw:
        cls = DiffClass.MATCH             # identical even in source form
    elif equal:
        cls = DiffClass.SYNTACTIC         # same fact, different representation
    else:
        cls = DiffClass.SEMANTIC          # a real conflict
    return FieldDiff(field=name, avion_value=avion_raw,
                     avstar_value=avstar_raw, classification=cls)


def reconcile(
    avion_row: dict[str, str], avstar_row: dict[str, str],
    station_tz: str, rate_tolerance: Decimal = Decimal("0"),
) -> ReconciliationReport:
    av = normalize_avion(avion_row, station_tz)
    st = normalize_avstar(avstar_row)
    sid = av.house_number

    diffs = [
        classify_field("aired_at", av.aired_at, st.aired_at,
                       avion_row["air_time"], avstar_row["aired_at"]),
        classify_field("daypart", av.daypart, st.daypart,
                       avion_row["daypart_code"], avstar_row["daypart"]),
        classify_field("rate", av.rate, st.rate,
                       avion_row["rate_cents"], avstar_row["rate"],
                       rate_tolerance=rate_tolerance),
        classify_field("billing_code", av.billing_code, st.billing_code,
                       avion_row.get("billing_code"), avstar_row.get("billing_code")),
    ]
    conflicts = [d for d in diffs if d.classification is DiffClass.SEMANTIC]
    verdict = "CONFLICT" if conflicts else "RECONCILED"
    _log(logging.INFO if not conflicts else logging.WARNING, sid,
         f"{verdict} | semantic={len(conflicts)} "
         f"syntactic={sum(d.classification is DiffClass.SYNTACTIC for d in diffs)}")
    return ReconciliationReport(house_number=sid, verdict=verdict,
                                diffs=diffs, decided_at=datetime.now(timezone.utc))
Avion and Avstar field reconciliation decision tree A matched Avion row and Avstar row for one spot are each normalized into the canonical shape, with the Avion side widened by applying the station timezone, converting cents to dollars, resolving the daypart letter, and making the currency explicit. The two canonical records feed a field-by-field comparison. For each field a decision asks whether the canonical values are equal within tolerance. If they are not equal, the field is classified SEMANTIC, a genuine conflict routed to review. If they are equal, a second decision asks whether the raw source forms were also identical: if yes the field is MATCH, if no the field is SYNTACTIC, meaning the same fact was written two ways and no action is needed. The report verdict is RECONCILED only when no field is semantic; otherwise it is CONFLICT. Avion row pipe · local · cents Avstar row CSV · UTC · decimal Normalize both → CanonicalSpot representation gone canonical equal? SEMANTIC conflict → review verdict = CONFLICT raw form equal? MATCH identical everywhere SYNTACTIC same fact, two forms no action no yes yes no

Figure — The reconciliation decision tree: both rows normalize to the canonical shape, then each field is classified. Unequal canonical values are SEMANTIC conflicts; equal values are MATCH or SYNTACTIC depending on whether the raw source forms also agreed.

Step 4 — Summarize a batch and route only conflicts

Goal: reconcile a set of matched pairs, count the verdicts, and hand the traffic desk only the reports carrying a semantic conflict — the syntactic and match reports are logged for the audit trail but need no human attention.

python 
def reconcile_batch(
    pairs: list[tuple[dict[str, str], dict[str, str]]],
    station_tz: str,
) -> list[ReconciliationReport]:
    reports = [reconcile(a, s, station_tz) for a, s in pairs]
    conflicts = [r for r in reports if r.verdict == "CONFLICT"]
    _log(logging.INFO, "BATCH",
         f"reconciled={len(reports) - len(conflicts)} conflicts={len(conflicts)}")
    return conflicts    # only these reach the traffic desk

Verification & Testing

Correct behaviour rests on two properties: representation differences never register as conflicts (syntactic gaps are absorbed by normalization), and genuine value disagreements are always caught (semantic gaps survive it). Both are assertable against fixture pairs drawn from real exports.

python 
station = "America/New_York"

# 1. Pure syntactic difference: same airing written two ways -> RECONCILED.
avion = {"house_no": "00088213", "air_date": "071426", "air_time": "1930",
         "daypart_code": "P", "rate_cents": "425000", "billing_code": "RCPRIME30"}
avstar = {"house_number": "00088213", "aired_at": "2026-07-14T23:30:00+00:00",
          "daypart": "PRIME", "rate": "4250.00", "currency": "USD",
          "billing_code": "RCPRIME30"}
report = reconcile(avion, avstar, station)
assert report.verdict == "RECONCILED"
assert {d.classification for d in report.diffs} == {DiffClass.SYNTACTIC,
                                                    DiffClass.MATCH}

# 2. Real semantic conflict: the two systems disagree on the rate -> CONFLICT.
avstar_bad = {**avstar, "rate": "3900.00"}   # $3,900 vs Avion's $4,250
report2 = reconcile(avion, avstar_bad, station)
assert report2.verdict == "CONFLICT"
rate_diff = next(d for d in report2.diffs if d.field == "rate")
assert rate_diff.classification is DiffClass.SEMANTIC

Because reconcile is a pure function of its two rows and the station timezone, replaying archived pairs during a billing audit reproduces byte-identical reports. Run the suite in CI against a golden fixture so a change to the normalization rules that would silently reclassify a conflict as syntactic — hiding a revenue discrepancy — fails the build. The field constraints both sides are checked against live in the canonical traffic field data dictionary.

Edge Cases & Failure Handling

  • DST-boundary instant. An Avion 0130 airing on a fall-back night normalizes to a specific UTC instant that may or may not match the Avstar timestamp. If Avstar’s offset resolves to the other side of the fold, the aired_at field is correctly flagged SEMANTIC rather than silently matched — the two systems genuinely recorded different instants, and that is a conflict worth surfacing. The full timezone treatment is in Migrating Avion Exports to Avstar Schemas.
  • Rate tolerance abuse. Setting rate_tolerance above zero to “reduce noise” hides real revenue discrepancies. Keep it at Decimal("0") unless a documented rounding policy justifies a specific sub-cent tolerance, and log the tolerance in the report so an auditor sees exactly what was permitted. A rate gap outside tolerance is always semantic.
  • Ambiguous Avion null vs Avstar empty. An Avion blank billing_code normalizes to None; an Avstar quoted "" also normalizes to None here, so they match — but the underlying meaning may differ (absent vs deliberately empty). Where billing depends on the distinction, compare the raw forms and treat a blank-versus-quoted-empty disagreement as semantic. This is the ambiguity the parent comparison flags as the sharpest difference between the formats.

FAQ

What is the difference between a syntactic and a semantic mismatch?

A syntactic mismatch is the same fact written two ways — 1930 local versus 23:30Z, or P versus PRIME — which vanishes once both rows are normalized to the canonical shape and needs no action. A semantic mismatch survives normalization: the canonical values genuinely disagree, like two different rates or two different airing instants, and it is a real conflict. Normalizing first, as described in Avion vs Avstar Export Formats: A Side-by-Side Comparison, is what lets the reconciler tell them apart instead of drowning the traffic desk in representation noise.

Why normalize both rows before diffing instead of comparing raw fields?

Because a raw diff flags every format difference as a conflict, and the vast majority of them are not. Comparing 425000 to 4250.00 character by character reports a mismatch that does not exist. Normalizing both sides into the canonical shape first — applying the widening rules from Migrating Avion Exports to Avstar Schemas — collapses representation so that only genuine value differences remain to be reviewed.

Should a syntactic difference ever be escalated?

Not to a human, but it is still logged. A run with an unusually high syntactic count can indicate a format-version drift worth investigating — for example, a station that changed its Avion daypart codes. The report retains every classification for the audit trail even though only semantic conflicts route to review, so the pattern is visible without adding manual work. Validate any suspected drift against the spot schema before changing the normalization rules.

How does reconciliation relate to as-run billing?

A spot with a RECONCILED verdict can be billed once from either source with confidence; a CONFLICT verdict is a revenue discrepancy that must be resolved before the log closes, because billing from an unreconciled pair risks double-posting or misstating revenue. Keeping the reconciler pure — two rows in, one report out — means the report is the single artifact a billing audit joins on, consistent with the field contract in the canonical traffic field data dictionary.

Related content

Up one levelAvion vs Avstar Export Formats: A Side-by-Side Comparison RelatedHow to Migrate Avion Exports to Avstar Schemas