Time-Series Databases and OLAP Warehouses

In one line

Time-series data (metrics, sensor readings, events) is append-heavy, queried by time range and tags, and downsampled as it ages: use TimescaleDB, InfluxDB, Prometheus or ClickHouse. Analytical data belongs in a columnar warehouse (BigQuery, Snowflake, Redshift, ClickHouse) modeled as star schemas with fact and dimension tables, loaded by ETL or ELT, with slowly changing dimensions preserving history.

0/7 · 0%

Think of it like this

A weather station's logbook. Every minute adds a line (append-only), people ask "what happened last Tuesday afternoon" (time range), and after a year nobody needs minute-level detail, so hourly averages are enough (downsampling).

Key ideas

01
Time-series design: timestamp + tags (device, region; indexed, low to medium cardinality) + fields (values). High-cardinality tags (user IDs as tags) explode series counts in Prometheus and InfluxDB.
02
Retention and downsampling: keep raw data for days or weeks, 1-minute rollups for months, hourly for years (continuous aggregates in TimescaleDB, materialized views in ClickHouse). Columnar compression makes time-series storage 10–20× smaller.
03
PostgreSQL vs specialised: TimescaleDB (a PostgreSQL extension with hypertables, compression, continuous aggregates) keeps SQL and joins; Cassandra suits huge write volumes with simple per-key reads; ClickHouse excels at analytical aggregations over billions of rows.
04
Warehouse modeling: fact tables hold measurable events at a declared grain (one row per order line) with foreign keys to dimensions (date, customer, product, store). Star schema: denormalised dimensions; snowflake schema: normalised dimensions (fewer duplicates, more joins).
05
ETL transforms before loading; ELT loads raw data then transforms inside the warehouse (dbt). Slowly changing dimensions: Type 1 overwrites, Type 2 adds a new row with valid_from/valid_to and a surrogate key so history reports use the attribute value at the time.

Code & diagrams

timescale.sqlsql

CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE reading (ts timestamptz NOT NULL, device_id int NOT NULL, temp double precision);
SELECT create_hypertable('reading', by_range('ts', INTERVAL '1 day'));

-- 1-minute rollup maintained incrementally
CREATE MATERIALIZED VIEW reading_1m WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket, device_id, avg(temp) AS avg_temp, max(temp) AS max_temp
FROM reading GROUP BY 1, 2;

SELECT add_retention_policy('reading', INTERVAL '14 days');     -- raw data
ALTER TABLE reading SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');
SELECT add_compression_policy('reading', INTERVAL '2 days');

star-schema.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Analytics for an e-commerce company

Finance and product teams need revenue by product category, city and month over 5 years, and customer cohort retention. Customers move cities, and reports must attribute revenue to the city at the time of purchase. Design the pipeline and model.

When it breaks

High-cardinality labels in a metrics TSDB

What you see

Adding user_id as a Prometheus label creates millions of series; memory explodes and queries time out.

Fix & prevent

Keep labels low-cardinality (endpoint, status, region); put per-user analysis in logs or a warehouse.

Explain it without notes

What problem does an SCD Type 2 dimension solve?

Practice

Why is columnar storage faster for SELECT sum(revenue) FROM fact_sales WHERE year = 2026?

Trade-offs

↔
Specialised time-series and columnar engines give huge compression and scan speed, at the cost of weak point updates and a separate pipeline.

Done when you can

I can design time-series retention and rollups and a star schema with SCD Type 2.

Command Palette