Time-Series Data, Rollups & Derived Metrics
You'll learn to
- -Design a schema for high-frequency sensor/IoT data that pre-aggregates into rollup tables instead of scanning raw rows
- -Model derived/weighted scores as their own versioned table rather than recomputing them from scratch on every read
High-frequency telemetry - a spacecraft reporting hundreds of sensor readings per second - produces far more raw data than most queries actually need to look at directly. This chapter builds on the market-data time-series pattern from the previous module, adding the specific technique for when raw-row scanning stops being fast enough: pre-computed rollups.
The Problem: Raw Data Is Too Fine-Grained for Most Queries
A dashboard showing "average hull temperature over the last 30 days, by hour" does not need to scan every individual sensor reading from those 30 days at query time - that is a massive, unnecessary amount of row-scanning for a question that only needs hourly averages. The fix is computing and storing those hourly averages ahead of time, once, rather than recomputing them from raw data on every dashboard load.
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);The dashboard query against sensor_hourly_rollups now touches one row per sensor per hour instead of potentially thousands of raw readings - a direct, deliberate trade of storage and write-time computation for read-time speed, the same fundamental trade-off as the denormalization chapter's fan-out-on-write feed, applied here to aggregation instead of duplication.
Raw Data Retention: Rollups Don't Replace Raw Storage, They Supplement It
Rollups answer "what was the trend," but they cannot answer "what was the exact reading at 3:47:12.5am" - that level of detail is destroyed by aggregation. Most real systems keep both: raw data for a shorter retention window (or archived to cheaper storage) for precise forensic queries, and rollups retained far longer for trend analysis, since rollups are dramatically smaller.
Weighted Composite Scores as Configurable Data, Not a Single Formula
A planet habitability score, computed from a weighted combination of many measurements (atmospheric oxygen, surface gravity, radiation, and dozens more), is itself a derived value worth storing rather than recomputing on every read - but the harder design question is where the weights themselves live. Hardcoding "oxygen counts for 15% of the score" in application code means every weight tweak needs a deploy; the same "rules as data" instinct from the transactional-modeling module's commission-tiers example applies here too.
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 isThis is the same shape as the commission-tiers example, generalized: a measurement_types table holding the configurable rule (weight, and whether failing it disqualifies the whole planet outright), and planet_measurements holding the actual observed values plus their uncertainty - so a damaged probe's reading is honestly represented as less trustworthy, not silently treated the same as a precise one.
Naming the retention trade-off explicitly - "raw data for N days, rollups indefinitely" - is worth doing unprompted; it shows you've thought about the full lifecycle of the data, not just how to compute the 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.