How I’d build an airline pricing analysis product
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.
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=24000A 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.
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
Keep source timestamps; compact small files.
Export: format, destination and release policy.
Analytics: batch completion and late-input audit.
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.
Market monitor
27 Oct 2026 · 08:10 UTC snapshot
- 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 →Review queue
Grouped by market / productI’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.
A → B · November 17
21 days before departure · competitor discount product
- 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 dayEvidence behind the review
Source, product and freshness- Decision3/3 confirm the move
- Tracked productDiscount economy
- Comparable coverage40/40 departure dates
- Materiality limitAt least $10
- Price basisUSD · tax included
matched 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.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.