Skip to content
LLD Learn/Distributed & Large-Scale Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Time-Series Data, Rollups & Derived Metrics

4 min read

You'll learn to

  • -Explain a rollup as a pre-computed summary, and count how many rows it saves
  • -Say why raw data and rollups are kept together, each for a different question
  • -Store scoring weights and disqualifying rules as data, with a worked example

Telemetry is data that machines send about themselves, such as a spacecraft reporting hundreds of sensor readings every second. It produces far more raw data than most questions need. This chapter builds on the market-data time-series pattern from the previous module and adds what to do when scanning raw rows stops being fast enough: pre-computed summaries called rollups.

An Analogy: The Weather Station and the Newspaper

A weather station may record the temperature every second, which is millions of numbers a month. The newspaper does not print them. It prints the daily high, low and average. Anyone planning a picnic needs the summary, not the million readings. A rollup is that daily summary, worked out once and kept ready.

The Problem: Raw Data Is Too Fine for Most Queries

A dashboard showing "average hull temperature over the last 30 days, by hour" does not need every individual reading. Scanning them at query time is a huge amount of work for a question that only needs 720 hourly averages. The fix is to compute and store those averages ahead of time, once, instead of recomputing them on every page load. A bucket is one slice of time, here an hour, that readings are grouped into.

Raw readings, plus a pre-computed hourly rollup
CREATE TABLE sensor_readings (          -- raw, high-frequency data
    id BIGINT PRIMARY KEY,
    sensor_id INTEGER NOT NULL,
    value DECIMAL(10, 4) NOT NULL,
    recorded_at TIMESTAMP NOT NULL
);

CREATE TABLE sensor_hourly_rollups (    -- pre-aggregated, queried by dashboards
    sensor_id INTEGER NOT NULL,
    hour_bucket TIMESTAMP NOT NULL,     -- truncated to the hour
    avg_value DECIMAL(10, 4) NOT NULL,
    min_value DECIMAL(10, 4) NOT NULL,
    max_value DECIMAL(10, 4) NOT NULL,
    reading_count INTEGER NOT NULL,
    PRIMARY KEY (sensor_id, hour_bucket)
);
-- populated by a periodic job aggregating the last hour's raw readings, e.g.:
-- INSERT INTO sensor_hourly_rollups
-- SELECT sensor_id, date_trunc('hour', recorded_at), AVG(value), MIN(value), MAX(value), COUNT(*)
-- FROM sensor_readings WHERE recorded_at >= :hour_start AND recorded_at < :hour_end
-- GROUP BY sensor_id, date_trunc('hour', recorded_at);
One sensor reporting twice a second for 30 days
5,184,000
raw rows to scan for the dashboard
720
hourly rollup rows to read instead
7,200
times fewer rows

Run the demo to see a rollup being built. It generates three hours of readings, groups them into hourly buckets, and keeps the average, minimum, maximum and count of each.

Press Run: 21,600 raw rows become 3 rollup rows

This is a deliberate trade of storage and write-time work for read-time speed. It is the same trade as the fan-out-on-write feed in the denormalization chapter, applied to summaries instead of copies.

Rollups Supplement Raw Data, They Do Not Replace It

Rollups answer "what was the trend?" but not "what was the exact reading at 3:47:12.5 am?". Aggregation destroys that detail. Most real systems keep both. Raw data is kept for a shorter window, or archived to cheaper storage, for precise investigation. Rollups are kept much longer for trend analysis, because they are dramatically smaller.

Weighted Scores as Configurable Data

A planet habitability score combines many measurements, such as oxygen, gravity and radiation. Think of a school grade where homework counts 20 percent and the exam 80 percent, and where missing too many classes fails you regardless of the marks. The score is worth storing instead of recomputing on every read. The harder question is where the weights live. Hardcoding "oxygen counts for 15 percent" in code means a deploy for every tweak. The "rules as data" idea from the transactional module applies again.

Weights, uncertainty, and dealbreakers as data, not hardcoded logic
CREATE TABLE measurement_types (        -- the weighting rules, as data
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,                 -- 'atmospheric_oxygen', 'surface_gravity', ...
    weight DECIMAL(4, 3) NOT NULL,      -- all weights across types should sum to 1.0
    is_dealbreaker BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE planet_measurements (      -- one row per planet per measurement type
    id INTEGER PRIMARY KEY,
    planet_id INTEGER NOT NULL REFERENCES planets(id),
    type_id INTEGER NOT NULL REFERENCES measurement_types(id),
    value DECIMAL(10, 4) NOT NULL,
    uncertainty DECIMAL(5, 4)           -- e.g. +/-0.08 - a damaged probe reads less confidently
);

CREATE TABLE habitability_criteria (    -- pass/fail thresholds, also data
    type_id INTEGER NOT NULL REFERENCES measurement_types(id),
    max_allowed_value DECIMAL(10, 4)    -- e.g. radiation must stay under this to not disqualify
);
-- composite score = SUM(measurement_score * weight) across all measurement_types,
-- UNLESS any is_dealbreaker measurement fails its habitability_criteria threshold,
-- in which case the planet is disqualified regardless of how high the weighted sum is

The demo computes a score both ways. The first planet has scores 0.8, 0.6 and 1.0, so with weights 0.5, 0.3 and 0.2 the score is 0.4 + 0.18 + 0.2 = 0.78. The second planet would score a perfect-looking total, but its radiation is a dealbreaker that fails its threshold, so it is disqualified outright.

Press Run: a weighted score, and a dealbreaker that overrides it

The uncertainty column records how trustworthy a reading is, so a damaged probe's value is honestly shown as less reliable instead of being treated like a precise one.

Say the retention trade-off aloud without being asked: "raw data for N days, rollups indefinitely". It shows you have thought about the whole lifecycle of the data, not just how to compute a rollup once.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design Endurance Telemetry and the Planet Habitability Database in the LLD Lab's Interstellar act.

Ready to Build This?

A rollup table exists to make a query like "totals per day/week/month" cheap - SQL Lab's "The Spike" (GROUP BY with strftime) is that exact query written by hand against raw rows, the work this chapter's pre-aggregation is designed to avoid doing over and over.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!