CSE Roadmap

Common tier · about 4 hours · 8 parts

Part 3 of 8 · 2 min read

APIs and data model

What is asked and what is stored.

Two APIs cover the three queries, since filters are just a parameter.

  • GET /v1/ads/{ad_id}/aggregated_count: takes a start and end minute and an optional filter id, returns a count.
  • GET /v1/ads/popular_ads: takes the window size M, the count N and a filter id, returns the top N ad ids.

Two kinds of data.

  • Raw data: one record per click. Huge, and not queried often.
  • Aggregated data: counts per ad per minute, plus a filter id so that counts can be split by country or user kind. A small table holds the top N for each window.

Keep both. Raw data is the backup and the base for recalculation, if a bug corrupts the counts. It moves to cold storage as it ages. Aggregated data is derived and small, and is tuned for fast queries.

Which database? Raw data is written heavily and read rarely. Relational databases struggle with the writes. NoSQL stores built for writes and time ranges, such as Cassandra, suit it. Another option is files in object storage (page 3) in a column format, with a size cap per file. The aggregated data is time-series in nature, written once a minute for each ad and read by dashboards, so the same kind of store works.

A worked example (made up). Ad 7 gets 12 clicks in minute 12:01 and 9 in minute 12:02. The aggregated table holds (ad 7, 12:01, filter none, 12) and (ad 7, 12:02, filter none, 9). A query for "last 2 minutes" adds the two rows to give 21.

Filters with a star schema. Pre-compute the counts for each filter you will allow, such as "US only". Each filter is an extra field, called a dimension, in the aggregated row. Reads are then fast, because the work is already done. The limit is that more filters make many more rows.

Aggregation

Aggregation combines many records into a summary, such as a count or a sum, for a group and a time slice.

Example: 12 click records for ad 7 in one minute become one row with count 12.

Check yourself: 1. Why keep raw data if it is large? 2. What does a star schema trade? 3. Which database kind suits raw clicks?