Avion vs Avstar Export Formats: A Side-by-Side Comparison

A traffic team that runs both an Avion log-out and an Avstar API export quickly learns that the two systems disagree about almost everything except the airing itself: one pipe-delimits with no header and splits a broadcast date across two positional columns, the other emits quoted RFC 4180 CSV with a named header and a single timezone-aware timestamp. When those two shapes have to converge on one canonical record — the shape scheduling, playout, and billing actually join on — every disagreement becomes a migration hazard. This guide is a field-by-field comparison of the Avion and Avstar export formats for traffic engineers evaluating a migration path, and it sits inside Avion & Avstar Ingestion Pipelines, the parent architecture that defines how vendor exports become validated traffic data. It is written for two readers at once: traffic managers who need the plain-language reasoning behind each format difference, and Python developers who need a deployable comparison model with strict typing and Pydantic v2 validation.

The two formats are already covered individually — the delimiter grammar and control records are dissected in Parsing Avion Export Formats, and the boundary contract that both must satisfy is defined in Schema Validation with Pydantic for Traffic Data. What this page adds is the contrast: where the formats encode the same fact differently, where one carries information the other only implies, and where a naive one-to-one field copy silently corrupts a spot. Every mapping decision here resolves toward the canonical traffic field data dictionary, so Avion and Avstar are treated as two dialects of the same underlying spot schema rather than two unrelated file types.

Concept & Data Model #

The comparison is built on three entities. AvionRecord models a single parsed row of an Avion export; AvstarRecord models a single parsed row of an Avstar export; FieldMapping is the declarative bridge that names, for one canonical field, which source field feeds it in each format and how the value must be transformed. Keeping the mapping as data — not as branching code — is what lets the same comparison drive both the migration path and the reconciliation path that follow this guide.

The eight axes on which the formats diverge are worth naming precisely, because each is a distinct class of bug when crossed carelessly. Delimiter and quoting: Avion is pipe-delimited with no quoting — an interior pipe is backslash-escaped — while Avstar is comma-separated with RFC 4180 double-quoting, so a comma inside an advertiser name is data in one format and a field boundary in the other. Header conventions: Avion prefixes the file with a #HDR control record and relies on documented column positions; Avstar carries a named header row, so a column reorder is invisible in Avion and self-describing in Avstar. Date/time and timezone: Avion splits an airing into an MMDDYY date and an HHMM 24-hour time in implicit station-local time; Avstar emits one ISO 8601 timestamp with an explicit UTC offset. House number and spot ID: Avion zero-pads house_no to eight digits and keeps ISCI separate; Avstar carries a variable-length house_number string alongside a dedicated ad_id/ISCI column. Daypart codes: Avion uses single-letter station codes; Avstar uses canonical daypart names. Rate and billing: Avion stores rate as integer cents with an implicit currency; Avstar stores a decimal amount with an explicit currency column. Character encoding: Avion is CP1252/Latin-1; Avstar is UTF-8. Null handling: an Avion empty field or * sentinel means “absent”, while Avstar distinguishes an unquoted empty (null) from a quoted "" (present-but-empty).

The same eight axes, side by side, with the concrete representation each format uses:

Figure — The comparison matrix: ten format dimensions where Avion and Avstar diverge, each resolved by a FieldMapping into one canonical record. Amber rows mark differences that are semantic, not merely syntactic.

The two amber rows — timezone and null handling — are the ones that bite hardest, because they are semantic differences masquerading as formatting. An Avion time with no offset is not merely formatted differently from an Avstar timestamp; it is missing information that only the station’s configured timezone can supply. Likewise, an Avion empty field cannot distinguish “no billing code” from “billing code deliberately blanked”, whereas Avstar can. Those are the mismatches that a diff has to detect, and they are treated in depth in Reconciling Field Differences Between Avion and Avstar.

Implementation Approach #

Three design decisions shape a comparison layer that has to survive a real migration rather than a one-off spreadsheet diff.

Model both formats explicitly, never parse into dict. It is tempting to read each export into a loose dictionary and compare keys. That collapses the very type differences the comparison exists to surface — an Avion rate_cents of 425000 and an Avstar rate of 4250.00 look “equal enough” to a stringly-typed diff and are catastrophically unequal to billing. Both formats therefore parse into distinct Pydantic v2 models, AvionRecord and AvstarRecord, whose validators encode each format’s rules (zero-padding, offset-awareness, currency presence) at the boundary. This is the same boundary-validation discipline established in Schema Validation with Pydantic for Traffic Data.

Make the mapping declarative and canonical-first. Rather than a wall of if source == "avion" branches, the comparison is driven by a table of FieldMapping rows keyed on the canonical field name. Each row names the Avion source field, the Avstar source field, the transform that lifts each into canonical units, and a semantic_note for differences that a transform cannot fully resolve. Adding a field to the canonical data dictionary means adding one row, not editing two parsers.

Normalize toward the richer format, not the older one. When two formats disagree, the migration must not lose information. Avstar carries strictly more than Avion — an explicit offset, an explicit currency, a distinct null — so the canonical target is modelled on Avstar’s richness, and the Avion side is widened to fill it (a station timezone is applied to produce an offset; an implicit USD is made explicit). Downgrading Avstar to Avion’s shape would be lossy and irreversible, which is exactly the trap the migration guide is built to avoid.

Production Python Implementation #

The module below models both records with Pydantic v2, encodes the format rules as validators, and exposes a FieldMapping table plus a to_canonical reducer. It uses strict type hints throughout and structured logging in the traffic-ops format timestamp | level | module | spot_id.

from __future__ import annotations

import logging
from datetime import datetime, time, timezone
from decimal import Decimal
from enum import Enum
from typing import Callable, Optional
from zoneinfo import ZoneInfo

from pydantic import BaseModel, Field, field_validator

# --- Structured logging: timestamp | level | module | spot_id ---------------
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
)
logger = logging.getLogger("avion_avstar.compare")


def _log(level: int, spot_id: str, msg: str) -> None:
    # Every audit line is greppable by the house number that identifies the spot.
    logger.log(level, "%s | %s", spot_id, msg)


class Daypart(str, Enum):
    # Canonical daypart vocabulary both formats must resolve into.
    OVERNIGHT = "OVERNIGHT"
    MORNING = "MORNING"
    DAYTIME = "DAYTIME"
    EARLY_FRINGE = "EARLY_FRINGE"
    PRIME = "PRIME"
    LATE = "LATE"


# Avion ships single-letter station codes; Avstar ships canonical names already.
AVION_DAYPART_CODES: dict[str, Daypart] = {
    "O": Daypart.OVERNIGHT,
    "M": Daypart.MORNING,
    "D": Daypart.DAYTIME,
    "E": Daypart.EARLY_FRINGE,
    "P": Daypart.PRIME,
    "L": Daypart.LATE,
}


class AvionRecord(BaseModel):
    """One parsed Avion row: pipe-delimited, positional, station-local time."""

    house_no: str                          # 8-digit zero-padded
    air_date: str                          # MMDDYY as exported
    air_time: str                          # HHMM 24-hour, station-local
    daypart_code: str                      # single letter, e.g. "P"
    rate_cents: int = Field(ge=0)          # integer cents, currency implicit
    billing_code: Optional[str] = None     # empty or "*" sentinel means absent

    @field_validator("house_no")
    @classmethod
    def _padded_house(cls, v: str) -> str:
        # Avion pads to 8 digits; a short value signals a truncated export.
        digits = v.strip()
        if not digits.isdigit() or len(digits) != 8:
            raise ValueError(f"house_no '{v}' is not an 8-digit Avion identifier")
        return digits

    @field_validator("billing_code", mode="before")
    @classmethod
    def _empty_is_none(cls, v: Optional[str]) -> Optional[str]:
        # Avion cannot distinguish blank from absent: "*" and "" both collapse.
        if v is None:
            return None
        stripped = v.strip()
        return None if stripped in ("", "*") else stripped


class AvstarRecord(BaseModel):
    """One parsed Avstar row: RFC 4180 CSV, named header, ISO 8601 timestamp."""

    house_number: str                      # variable-length string
    aired_at: datetime                     # ISO 8601, timezone-aware
    daypart: Daypart                       # canonical name
    rate: Decimal = Field(ge=0)            # decimal amount
    currency: str = "USD"                  # explicit ISO 4217 code
    billing_code: Optional[str] = None     # unquoted empty = null

    @field_validator("aired_at")
    @classmethod
    def _offset_aware(cls, v: datetime) -> datetime:
        # An Avstar timestamp without an offset is a malformed export, not local.
        if v.tzinfo is None:
            raise ValueError("aired_at must carry an explicit UTC offset")
        return v.astimezone(timezone.utc)


class CanonicalSpot(BaseModel):
    """The single shape both formats converge on (dictionary-aligned)."""

    house_number: str
    aired_at: datetime                     # normalized to UTC
    daypart: Daypart
    rate: Decimal                          # dollars, never cents
    currency: str
    billing_code: Optional[str]
    source_format: str                     # "avion" | "avstar" for lineage


class FieldMapping(BaseModel):
    """Declarative bridge for one canonical field across both formats."""

    canonical_name: str
    avion_field: Optional[str]             # None => Avion cannot supply it directly
    avstar_field: Optional[str]
    semantic_note: str                     # flags non-syntactic differences


# The comparison table: one row per canonical field. This is the artifact a
# migration reviewer reads to understand exactly where the formats diverge.
FIELD_MAP: tuple[FieldMapping, ...] = (
    FieldMapping(canonical_name="house_number", avion_field="house_no",
                 avstar_field="house_number",
                 semantic_note="Avion zero-pads to 8 digits; Avstar is free-form."),
    FieldMapping(canonical_name="aired_at", avion_field="air_date+air_time",
                 avstar_field="aired_at",
                 semantic_note="Avion is station-local with no offset; must apply "
                               "the station timezone to reach a UTC instant."),
    FieldMapping(canonical_name="daypart", avion_field="daypart_code",
                 avstar_field="daypart",
                 semantic_note="Avion single letter -> canonical name via lookup."),
    FieldMapping(canonical_name="rate", avion_field="rate_cents",
                 avstar_field="rate",
                 semantic_note="Avion integer cents -> decimal dollars (÷100)."),
    FieldMapping(canonical_name="currency", avion_field=None,
                 avstar_field="currency",
                 semantic_note="Avion currency is implicit; defaulted per station."),
    FieldMapping(canonical_name="billing_code", avion_field="billing_code",
                 avstar_field="billing_code",
                 semantic_note="Avion '*'/'' both mean null; Avstar '' can mean "
                               "present-but-empty. Blank is ambiguous from Avion."),
)


def avion_to_canonical(
    rec: AvionRecord,
    station_tz: str,
    default_currency: str = "USD",
) -> CanonicalSpot:
    """Widen an Avion row into the canonical shape without losing information."""
    sid = rec.house_no
    # Avion splits date + time and drops the offset; reconstruct the instant by
    # localizing to the station's configured zone, then normalizing to UTC.
    naive = datetime.combine(
        datetime.strptime(rec.air_date, "%m%d%y").date(),
        time(int(rec.air_time[:2]), int(rec.air_time[2:])),
    )
    aware = naive.replace(tzinfo=ZoneInfo(station_tz)).astimezone(timezone.utc)
    daypart = AVION_DAYPART_CODES.get(rec.daypart_code.strip().upper())
    if daypart is None:
        _log(logging.ERROR, sid, f"ERR_DAYPART_CODE | unknown '{rec.daypart_code}'")
        raise ValueError(f"unmappable Avion daypart code '{rec.daypart_code}'")
    canonical = CanonicalSpot(
        house_number=rec.house_no,
        aired_at=aware,
        daypart=daypart,
        rate=(Decimal(rec.rate_cents) / Decimal(100)).quantize(Decimal("0.01")),
        currency=default_currency,                        # implicit -> explicit
        billing_code=rec.billing_code,
        source_format="avion",
    )
    _log(logging.INFO, sid,
         f"avion->canonical | aired_at={aware.isoformat()} daypart={daypart.value}")
    return canonical


def avstar_to_canonical(rec: AvstarRecord) -> CanonicalSpot:
    """Avstar already carries canonical richness; map it straight across."""
    sid = rec.house_number
    canonical = CanonicalSpot(
        house_number=rec.house_number,
        aired_at=rec.aired_at,             # already UTC-normalized by validator
        daypart=rec.daypart,
        rate=rec.rate,
        currency=rec.currency,
        billing_code=rec.billing_code,
        source_format="avstar",
    )
    _log(logging.INFO, sid,
         f"avstar->canonical | aired_at={rec.aired_at.isoformat()}")
    return canonical

The two reducers are deliberately asymmetric: avion_to_canonical takes a station_tz and a default_currency because Avion cannot supply them, while avstar_to_canonical takes nothing extra because Avstar already carries every canonical fact. That asymmetry is the comparison — it is the precise list of what Avion under-specifies relative to Avstar, expressed as required arguments.

Validation & Edge Cases #

The format differences generate boundary conditions that a straight field copy mishandles. Each of the following is exercised by the implementation above.

• DST-ambiguous local times. An Avion air_time of 0130 on a fall-back night maps to two distinct UTC instants. Localizing with ZoneInfo resolves it deterministically, but the safe practice is to log the pre-transform local value alongside the UTC result so an auditor can reconstruct the choice. Avstar sidesteps this entirely by carrying the offset in the export.
• Sports overrun past midnight. A spot exported by Avion as airing at 2515 (a station convention for 01:15 on the following broadcast day) is not a valid clock time. The parser must apply the broadcast-day rollover before combining date and time, or the airing lands 24 hours early. Avstar’s ISO timestamp already encodes the true calendar instant.
• Rate rounding. Avion integer cents divide cleanly, but the reverse migration — decimal dollars to cents — must reject any Avstar rate with sub-cent precision rather than silently truncating revenue. The canonical model keeps Decimal, never float, so 4250.00 never drifts to 4249.999….
• Encoding-corrupted advertiser names. A CP1252 0x92 right single quote in an Avion advertiser field becomes mojibake if decoded as UTF-8. Decode Avion bytes as CP1252 explicitly before parsing; never let the reader guess. This is the same defensive decoding covered in Parsing Avion Export Formats.
• Ambiguous null from Avion. A blank Avion billing_code is coerced to None, but that erases the distinction Avstar can express. When migrating, a blank Avion billing code should be quarantined for review rather than assumed absent, because a spot that airs without a billing code cannot be reconciled to revenue.

Integration Points #

Upstream, both parsers consume raw export bytes and emit AvionRecord / AvstarRecord instances. Downstream, the canonical reducer feeds one uniform CanonicalSpot stream to scheduling and billing, so nothing after the ingestion boundary needs to know which format a spot came from. The lineage is preserved on source_format, which is what an as-run or billing reconciliation joins on when it has to explain a discrepancy.

The wire contract emitted for each reconciled spot is deliberately small and carries the source lineage so a downstream consumer can trace any value back to the export that produced it:

{
  "message_type": "traffic.canonical_spot.v1",
  "house_number": "00088213",
  "aired_at": "2026-07-14T23:30:00Z",
  "daypart": "PRIME",
  "rate": "4250.00",
  "currency": "USD",
  "billing_code": "RCPRIME30",
  "source_format": "avion",
  "source_local_time": "2026-07-14T19:30:00-04:00"
}

The source_local_time field is not part of the canonical record proper; it is a lineage annotation that lets an auditor confirm the Avion station-local-to-UTC transform was applied correctly. Retaining it turns the hardest semantic difference — the missing timezone — into something a reviewer can verify rather than trust. The full field contract these messages satisfy lives in the canonical traffic field data dictionary, and the identifier semantics come from the spot schema.

Compliance & Audit Considerations #

A format migration is a compliance event, because every value that scheduling and billing depend on changes representation. Three obligations apply directly.

Reproducible transforms. Both reducers are pure functions of their inputs (Avion additionally of the station timezone). Re-running a migration against an archived export must produce byte-identical canonical records, which is what an FCC public-inspection reconstruction or a SOC 2 reproducibility review requires. Non-deterministic behaviour — a float rate, a guessed encoding, a system-clock timezone — breaks that guarantee and must be treated as a defect.

Billing lineage across the boundary. A spot that airs under Avion and bills after an Avstar cutover must reconcile to the same revenue. Preserving source_format and the pre-transform local time means a billing dispute can be traced to the exact export field, consistent with the practices in the canonical dictionary. Never discard the source export after migration; archive it immutably.

No silent widening of nulls. Because Avion cannot distinguish blank from absent, the migration must record which canonical values were inferred (implicit currency, applied timezone) rather than present in the source. Those inferences are auditable decisions, and burying them makes an as-run reconciliation impossible to defend.

Troubleshooting & Common Errors #

The two errors worth watching in a live migration are ERR_TZ_UNRESOLVED and ERR_NULL_AMBIGUOUS, because both stem from Avion carrying less information than the canonical shape needs. Neither is a parsing bug — the row parsed fine — which is why they surface only when the two formats are compared field by field. The reconciliation guide shows how to detect them before they reach billing.

Related #

• Avion & Avstar Ingestion Pipelines — the parent architecture that turns vendor exports into validated, canonical traffic data.
• Parsing Avion Export Formats — the delimiter grammar, control records, and CP1252 decoding that produce a clean AvionRecord.
• Migrating Avion Exports to Avstar Schemas — the field-by-field transform that lifts Avion rows into the richer Avstar-aligned canonical shape.
• Reconciling Field Differences Between Avion and Avstar — detecting semantic, not merely syntactic, mismatches between the two formats.
• Canonical Traffic Field Data Dictionary — the field contract both formats resolve toward, with types and constraints.