Quantified Self at Scale: Processing Millions of Wearable Metrics with ClickHouse 🚀

Source: Dev.to

Introduction

Are you a data nerd who tracks every heartbeat, step, and sleep stage? If you own an Oura Ring and an Apple Watch, you’re sitting on a goldmine of high‑frequency biometric data. As your Quantified Self journey grows, a simple CSV or a standard relational database starts to crawl. When you’re dealing with millions of sensor readings, you need a speed demon.

In this guide we dive deep into data engineering for personal health, exploring why ClickHouse is the ultimate choice for time‑series health data and how to build a pipeline that delivers sub‑second insights. We’ll cover high‑performance columnar storage, efficient schema design for time‑series analytics, and real‑time visualization.

Data Ingestion Pipeline

graph TD
    A[Oura Ring API] -->|JSON| B(Python Ingestion Engine)
    C[Apple Watch Export] -->|XML/CSV| B
    B -->|Pandas/Batch| D{ClickHouse DB}
    D -->|SQL Aggregation| E[Apache Superset]
    D -->|Analysis| F[Jupyter Notebooks]
    style D fill:#f96,stroke:#333,stroke-width:4px

Why ClickHouse for Wearable Metrics

When you have millions of rows of heart‑rate data (sampled every few minutes or even seconds), traditional databases like PostgreSQL struggle with analytical aggregations. ClickHouse shines because:

• Columnar Storage – reads only the columns you need (e.g., just heart_rate).
• Data Compression – 10 million rows can shrink to a fraction of their original size.
• Vectorized Execution – processes data in chunks, making queries like “average heart rate per month” nearly instantaneous.

Schema Design

In a high‑performance setup, schema design is everything. We’ll use the MergeTree engine, the workhorse of ClickHouse.

CREATE TABLE IF NOT EXISTS health_metrics (
    event_time DateTime64(3, 'UTC'),
    metric_name LowCardinality(String),
    value Float64,
    source_device LowCardinality(String),
    user_id UInt32,
    -- Tags for extra metadata (e.g., 'sleep', 'workout')
    tags Array(String)
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, metric_name, event_time)
SETTINGS index_granularity = 8192;

Why this works

• LowCardinality(String): saves space for repetitive values such as metric_name (“HeartRate”, “SPO2”).
• PARTITION BY: speeds up deletions and organizes data by month.
• ORDER BY: determines physical sorting on disk, allowing ClickHouse to locate specific metrics for specific users lightning‑fast.

Ingestion with Python

We use the clickhouse-connect library for a high‑performance interface. Instead of inserting row by row (a ClickHouse anti‑pattern), we batch our data.

import clickhouse_connect
import pandas as pd

# Connect to our local ClickHouse instance via Docker
client = clickhouse_connect.get_client(host='localhost', port=8123, username='default')

def ingest_wearable_data(df: pd.DataFrame):
    """
    Ingests a Pandas DataFrame into ClickHouse.
    Expects columns: ['event_time', 'metric_name', 'value', 'source_device', 'user_id', 'tags']
    """
    print(f"🚀 Ingesting {len(df)} rows of data...")

    # ClickHouse loves batches!
    client.insert_df('health_metrics', df)

    print("✅ Ingestion complete.")

# Example: Processing Oura Sleep Data
# df = pd.read_json('oura_sleep_data.json')
# ingest_wearable_data(df)

Sample Query: HRV During Sleep

SELECT
    toStartOfDay(event_time) AS day,
    avg(value) AS avg_hrv,
    quantile(0.9)(value) AS p90_hrv
FROM health_metrics
WHERE metric_name = 'HRV'
  AND tags HAS 'sleep'
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Even with 50 million rows, this query typically returns in under 50 ms. 🏎️

Scaling Considerations

While the setup works great locally, production‑grade health data platforms must handle:

• Schema evolution
• Multi‑tenancy
• Complex join patterns between sleep and activity metrics

For more production‑ready examples and advanced data‑engineering patterns—such as integrating AI‑driven health insights or building event‑driven architectures—check out the deep‑dive articles on the WellAlly Tech Blog. It’s a fantastic resource for engineers bridging the gap between “it works on my machine” and “it works for millions of users.”

Visualization with Apache Superset

To make the data human‑readable, connect Apache Superset to your ClickHouse instance:

1. Add a new Database connection using the clickhouse:// driver.
2. Create a Time‑series Chart.
3. Use event_time as your temporal column.

Boom! You now have a professional‑grade health dashboard that rivals the Oura and Apple Health apps themselves.

Conclusion

Quantifying yourself shouldn’t be limited by your tools. By moving from legacy databases to ClickHouse, you unlock the ability to query years of biometric data in the blink of an eye.

What are you tracking? Glucose levels, HRV, or perhaps your coding productivity metrics? Drop a comment below and let’s talk data!

If you enjoyed this tutorial, don’t forget to ❤️ and 🦄. For more advanced tutorials on data pipelines and system architecture, visit the WellAlly Blog.