Problem
Trivago, a hotel price comparison site, receives billions of prices every day from advertisers. Product teams wanted to show price context to users, for example the typical price in Berlin next month. Computing this live would add load to the application and hurt the user experience. The existing aggregated tables were stacked on top of each other as "temporary" fixes, so their aggregation logic was hard to trust.
Example from the article: a user asks "What is the average price for a one night double room stay in Amsterdam for January 2024, with breakfast included?" and gets the answer from a precomputed table.
Solution in brief
Trivago built Price Stream: a daily table of price aggregates, computed with SQL grouping sets over deduplicated historic prices after outlier rejection. It is streamed to the application through Kafka and a GraphQL gateway.
Questions this case answers
- How to precompute multi-dimensional aggregate statistics from billions of raw skewed records so a product can show context without live queries
- How to clean heavy-tailed values of outliers when the usual three-standard-deviation rule fails
- How to build aggregated price statistics from billions of historic prices to show price context to users?
- How to reject outlier prices when the price distribution is not normal?
Tools and methods
- Grouping sets (BigQuery) — many aggregation levels in one query.
- Outlier rejection — 3 standard deviations on log-transformed prices.
- Deduplication — lets 7 days of history fit in memory.
- Percentile metrics — 5th and 95th percentiles instead of min, average, max.
- Apache Kafka, GraphQL gateway — deliver aggregates to the front end.
Google or a one-time email code. No password.