Time-Series Data, Rollups & Derived Metrics
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.
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);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.
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.
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 isThe 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.
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.
Design Endurance Telemetry and the Planet Habitability Database in the LLD Lab's Interstellar act.
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.
No comments yet. Be the first to start the thread!