Normalize Pinnacle odds API data without losing its meaning

Two identical team names and two decimal prices are not enough to establish a comparison. The records must refer to the same event, settlement period, market rule, line, and selection. Treat normalization as preserving those meanings, not just renaming JSON properties.

The schema below is a proposed application design. It does not describe a universal feed format.

Separate identity from observation

Keep a canonical event table and a separate feed mapping table. A canonical event ID should remain stable when the feed changes its ID or a kickoff time is corrected. Retain the original source ID alongside every observation so you can audit a join later.

For a match candidate, compare sport, competition, participants, scheduled start, and home/away orientation. Use aliases to generate candidates, then check conflicts. Repeated matchups, rescheduled games, doubles teams, reserve squads, and neutral venues can defeat simple name matching. A confidence score can route a candidate for review; it should not silently convert uncertainty into identity.

Pinnacle-derived feeds show why the source ID matters. A live match can arrive as a parent matchup plus re-issued child matchups that share the same team names, and the same market can appear under two IDs at once, one current and one closing, at different prices. Key observations by the feed’s event ID, never by team names.

Keep unresolved mappings visible. Excluding them from a comparison while reporting the exclusion count is preferable to displaying a precise price difference for mismatched events.

Build the market key before the price column

Key element	Example meaning	Why it cannot be dropped
Event	Canonical fixture ID	The same teams can meet more than once
Period	Full game, first half, first set	Periods are different contracts
Market family	Moneyline, total, handicap	Prices refer to different outcome sets
Settlement rule	Regulation only, overtime included	Similar labels can settle differently
Line	Total 2.5, home handicap -0.5	Different lines are not interchangeable prices
Selection	Home, away, draw, over, under, participant	Reversing orientation creates false differences
Source	Feed and underlying bookmaker	An aggregator and its source are different identities

Do not infer overtime rules from a sport name or assign an undocumented default. An unknown settlement rule should block a comparison that depends on it. Distinguish unavailable, suspended, closed, and open markets; a missing value is not decimal odds of zero.

A minimal observation record

This is illustrative synthetic data, not a real feed response or measured observation:

Integration examples use placeholder endpoints. Set your provider’s API base URL and credentials, and verify its current schema before making a live request.

{
  "schemaVersion": 1,
  "canonicalEventId": "example-event-001",
  "feedId": "example-feed",
  "sourceEventId": "example-source-event",
  "bookmakerId": "example-bookmaker",
  "period": "full_game",
  "market": "total",
  "settlementRule": "regulation_only",
  "line": "2.5",
  "selection": "over",
  "decimalOdds": "1.95",
  "status": "open",
  "sourceUpdatedAt": null,
  "receivedAt": "2026-09-26T10:00:00Z",
  "sourceSequence": null,
  "mappingVersion": "example-v1"
}

Decimal strings make precision choices explicit. Parse them with a decimal-aware numeric representation when exact line identity matters. Normalize 2.50 and 2.5 to the same canonical line while retaining the original raw value in permitted provenance storage. Reject malformed prices, nonfinite numbers, and decimal odds at or below one.

The odds converter can demonstrate display formats; it does not resolve market identity. Keep the source representation if rounding would matter to later analysis.

Give each timestamp a job

sourceUpdatedAt should mean exactly what the source says it means. receivedAt records when your collector received the observation. A scheduled event start is neither of those. Represent timestamps with a timezone, such as RFC 3339 UTC values; a standardized string format does not establish clock accuracy. RFC 3339.

If no source-update time is available, preserve null. Do not fill it with receipt time and later call their difference feed latency. When you have a source timestamp, record its resolution and whether it applies to an individual selection, a bookmaker, or an entire snapshot.

Make ingestion idempotent

Define a deduplication key from the source, event, market, selection, and documented sequence or observation identity. If a feed has no durable sequence, content hashes can suppress identical repeats, but they cannot prove you received every intermediate change.

Keep a current-state view separate from the observation history. A current-state upsert answers what was last seen; an append-only observation table answers what the collector saw over time. Version the mapper so a corrected market definition can be recomputed without rewriting the meaning of old records.

Before showing a difference, require compatible market keys, valid prices, known status, acceptable observation age, and a reviewed mapping. Continue with historical data evaluation or the benchmark methodology depending on the product you are building.