Working on airline pricing taught me that the difficult part starts before the chart: two prices can refer to different products, be observed at different times, or include different taxes. I’d build the product around making those differences inspectable.

Here’s how I’d approach it from scratch. The design draws on what I learned, with generic component names and a fictional example: A → B on November 17, observed October 27, twenty-one days before departure. Our economy fare is $240; a competitor offers discount economy at $220 and regular economy at $270. An airline’s revenue-management team uses this evidence when choosing which prices to offer.

1. Collect once, keep the evidence

I’d store collection definitions in Postgres: route, departure range, cabin, trip type, country of sale, sources, freshness limit and delivery deadline. The system expands each one into dated queries, merges duplicates, and keeps a separate table of which collections own each query. That way two teams asking for the same public search share one supplier call.

Each provider gets its own queue, a rate limit and a cap on parallel requests. Workers fetch the replies, and a parser for each provider turns them into one common offer format. I’d retain raw replies in object storage for parser replay, with normalized offers in a short-lived Redis cache. Reusing a cached result must preserve its original observation time.

Collect → normalizeA → B · November 17
Watchlist 140 dates × 2 sourcesWatchlist 2same public searches80 source queries2 follow our departureFresh cachesource time + TTLProvider queuerate + inflight capParseNormalized offersOur airlineeconomy$240Competitordiscount$220Competitoreconomy$270A → B · Nov 1720 reuse60 fetchraw reply → replay logMerge ownership, not evidence.Watchlist 140 dates × 2 sourcesWatchlist 2same public searches80 source queries2 follow our departure20 cache hitskeep source timestamp60 source fetchesrate + inflight capParse + validateA → B · November 17 · 3 offersOur airlineeconomy$240Competitordiscount$220Competitoreconomy$270One shared plan80 physical queries
160 assignments → 80 queries = 20 cached + 60 fetched → 120 offers
One normalized offer
route=A-B · departure=2026-11-17 · cabin=economy · trip=OW · stops=0 · sale=US
source=airline-site · observed_at=08:05Z · currency=USD · tax=included · total_minor=24000
Merged watchlists share source calls; cached replies retain their original observation times.

A retry checks prior success and the deadline before choosing a substitute. Invalid currency or an unparseable price goes into a rejection log. It must never become a zero-dollar fare.

2. Build an offer table with a precise grain

The central table would contain one itinerary offer, from one source, at one observation time. I’d keep monetary amounts as decimal values or integer minor units, along with currency, tax basis, product group, booking class and fare restrictions. A normalized product group maps different airline bundle names onto a comparison category; it does not prove identical benefits.

A batch job joins offers to collection ownership, run metadata, exchange rates, city mappings and product mappings. It keeps the UTC observation time separate from the customer’s local reporting date. I’d partition the Parquet files by customer and reporting date, cluster them by route, and compact small files, rather than creating a partition for every search.

Curate → materializeA → B · November 17
Raw repliesone source responseRun ownershipcustomer / local dayFX · city · product maps+airlinegroupUSD centsOur airlineeconomy24000Competitordiscount22000Competitoreconomy27000Curated offersA → B · Nov 17 · 21 days outCustomer exportformat / release delayComparison viewcell × airline × productHistory tabledeparture × observationPartition by customer / day; cluster by route.Raw repliesone source responseRun ownershipcustomer / local dayJoin FX · city · product mapsairlinegroupUSD centsOur airlineeconomy24000Competitordiscount22000Competitoreconomy27000A → B · Nov 17 · 21 days outExportformat / delayComparecell × pairHistorydate × time

Minimum fare per cell / airline / product

SELECT route, departure, stops, cabin, trip,
       stay_nights, country_of_sale, carrier,
       product_group, MIN(total_usd_minor) AS fare
FROM offers
WHERE customer = :customer AND reporting_day = :day
  AND collection_run = :run
GROUP BY 1,2,3,4,5,6,7,8,9;

Physical layout and readiness

curated offers
└ customer / reporting_day
└ route-clustered Parquet files

Keep source timestamps; compact small files.

Export: format, destination and release policy.
Analytics: batch completion and late-input audit.

Different table grains, one shared dataset. Exports and analytics branch after curation.

Customer exports get their own format, destination and release-delay policy. Analytics jobs track completion per batch and report late inputs. I’d avoid treating an export’s delivery time as the analytics completion signal.

3. Build a market view, then a detector

I’d group by route, departure, stops, cabin and trip type, including stay length for return trips. Within one collection run, compute minimum fares per cell, airline and product group. Convert currency on a declared basis; a return fare can be halved for a one-way equivalent, while retaining its trip and stay-length group.

The comparison rule matters: use the lowest product, match the same group, or use a cheaper alternative when it beats our fare. Store the selected product and rule with the result. Below, changing the rule changes a $20 disadvantage into a $30 advantage.

PRICING WORKSPACE · DESIGN PREVIEW

Market monitor

27 Oct 2026 · 08:10 UTC snapshot

40/40 dates comparable
Market A → BCabin EconomyTrip One waySale / currency US / USD · tax includedCompare Lowest available
Competitor cheaper
22/40
Current position
Dates to investigate
3
1 grouped pricing event
Selected source age
5 min
Observed 08:05 UTC

Fares by departure

Departure date →
Our economyCompetitor discountNew move
$200$260$320$240$220Oct 28Nov 17Dec 6$200$260$320$240$220Oct 28Nov 17Dec 6
November 17: our $240 is $20 above the competitor.
Review map3 review · 37 no review
Oct 28: no reviewOct 29: no reviewOct 30: no reviewOct 31: no reviewNov 1: no reviewNov 2: no reviewNov 3: no reviewNov 4: no reviewNov 5: no reviewNov 6: no reviewNov 7: no reviewNov 8: no reviewNov 9: no reviewNov 10: no reviewNov 11: no reviewNov 12: no reviewNov 13: no reviewNov 14: no reviewNov 15: no reviewNov 16: no reviewNov 17: reviewNov 18: reviewNov 19: reviewNov 20: no reviewNov 21: no reviewNov 22: no reviewNov 23: no reviewNov 24: no reviewNov 25: no reviewNov 26: no reviewNov 27: no reviewNov 28: no reviewNov 29: no reviewNov 30: no reviewDec 1: no reviewDec 2: no reviewDec 3: no reviewDec 4: no reviewDec 5: no reviewDec 6: no reviewNov 17 selected · each cell is one departureOct 28: no reviewOct 29: no reviewOct 30: no reviewOct 31: no reviewNov 1: no reviewNov 2: no reviewNov 3: no reviewNov 4: no reviewNov 5: no reviewNov 6: no reviewNov 7: no reviewNov 8: no reviewNov 9: no reviewNov 10: no reviewNov 11: no reviewNov 12: no reviewNov 13: no reviewNov 14: no reviewNov 15: no reviewNov 16: no reviewNov 17: reviewNov 18: reviewNov 19: reviewNov 20: no reviewNov 21: no reviewNov 22: no reviewNov 23: no reviewNov 24: no reviewNov 25: no reviewNov 26: no reviewNov 27: no reviewNov 28: no reviewNov 29: no reviewNov 30: no reviewDec 1: no reviewDec 2: no reviewDec 3: no reviewDec 4: no reviewDec 5: no reviewDec 6: no reviewNov 17 selected · each cell is one departure

Review queue

Grouped by market / product
CONFIRMED PRICE MOVECompetitor cut discount faresA → B · Nov 17–19$15 below expected3 departure dates · 3 fresh checksInvestigate November 17 →
Other 37 datesNo confirmed material move
Configured benchmarkDiscount gap > $15: 22 matching datesRule matches are separate from anomalies.
Lowest available selects competitor discount at $220. Price-move reviews track the discount product against its own history.
Switch the comparison rule: the same flight changes position. Reviews track the discount product against its own history.

I’d detect unexpected movement against a context-aware forecast: the same market, product, weekday and days before departure. The prototype takes the median of eight matching historical weekdays and calibrates a range from fifty-six past walk-forward forecast errors. Today never helps set its own threshold.

Our competitor’s $220 offer is $15 below its $235 expected price. That differs from its $20 gap to us. I’d require a move of at least $10 or 3%, whichever is larger, confirmed by two of three fresh observations in the same direction, at least ten minutes apart. Stale data, a missing selected source or changed product scope stops the pricing decision. Duplicate cache reads cannot provide extra confirmations.

Configured fare/class benchmarks remain separate. A $15 gap rule can match a perfectly ordinary price; that alone should not create an anomaly.

4. Let the analyst inspect the move

I’d build two read paths: route prices across departure dates from partitioned Parquet, and one departure’s observation history from a time-series table. The API checks customer access, resolves an explicit observation cutoff, and returns the data with its coverage and comparison rule. A small Parquet response lets DuckDB-WASM handle browser-side filtering.

PRICING WORKSPACE · DESIGN PREVIEW

A → B · November 17

21 days before departure · competitor discount product

Confirmed price cut
Observed competitor
$220
3 fresh observations
Expected competitor
$235
Matched historical context
Unexpected movement
$15 lower
Confirmed · raw gap to us: $20

Price move in context

Same departure · one observation per day
ObservedExpected before observationPast-error range
$180$220$260$220Oct 13Oct 20Oct 27$180$220$260$220Oct 13Oct 20Oct 27
Today’s usual range: $231–$239. The $220 quote is outside this range.

Evidence behind the review

Source, product and freshness
  1. Decision3/3 confirm the move
  2. Tracked productDiscount economy
  3. Comparable coverage40/40 departure dates
  4. Materiality limitAt least $10
  5. Price basisUSD · tax included
07:35 UTC$220Fresh source observation
07:50 UTC$220Fresh source observation
08:05 UTC$220Fresh source observation
Ready for analyst reviewThree fresh, time-separated $220 quotes confirm the move below the expected range.
Published-fare lead: $21014-day purchase timing passes (21 days remain). Other fare rules, tax basis and seat availability are unverified.
Detector I’d buildmatched past → forecast error band → $10 / 3% materiality → 2 of 3 same-direction checks, 10+ min apartGET /history?route=A-B&departure=2026-11-17Forecast and error calibration exclude today. Missing, stale or changed product scope stops the decision.
Try one fresh check, a stale quote or a missing source. The price gap alone cannot confirm an alert.

To diagnose a move, I’d join shopping results to published fares and check the restrictions the data supports. Our $210 fare clears a fourteen-day advance-purchase check. Other restrictions, a comparable tax basis and seats available in that booking class—the fare’s inventory bucket—still need checking before anyone can treat $210 as sellable.

I’d audit planned, received, accepted, packaged and delivered work separately, comparing expected work with actual results. Failed jobs go to a holding queue where they can be inspected and retried, and supplier failures stay distinct from genuinely empty results. A missing competitor reply reduces coverage; it does not mean their price stayed unchanged.

I’d ship collection, exports and history first. Detection comes once I can reproduce the comparison behind a warning. That gives an analyst a useful first product—and gives me a way to debug it.