Beck_Moulton

Posted on Jul 31

Quantified Self: Stop Waiting for Excel! Build a High-Performance Biometric Dashboard with DuckDB & Apache Superset

#ai #python #opensource #discuss

Are you a "data hoarder" when it comes to your health? Between Apple Health exports, Oura Ring logs, and Garmin CSVs, I found myself sitting on nearly 10 million rows of biometric data. Trying to analyze a multi-year trend of Heart Rate Variability (HRV) or Resting Heart Rate (RHR) in Excel is a one-way ticket to "Application Not Responding" hell. 📉

In this tutorial, we are diving into the world of Quantified Self data engineering. We will leverage DuckDB—the Swiss Army knife of OLAP—and Apache Superset to build a lightning-fast, local-first biometric dashboard. We'll explore how to turn messy JSON/CSV exports into high-performance insights using dbt for modeling and DuckDB for compute. If you've been looking for a way to master Data Engineering for personal use, this is the ultimate "learn in public" project! 🚀

The Architecture: From Raw Export to Real Insights

Before we write a single line of SQL, let’s look at how the data flows. We want a system that is modular, fast, and stays entirely on our local machine (privacy first, right? 🥑).

graph TD
    A[Raw Data: Apple Health / Oura / Garmin] -->|CSV/JSON| B(DuckDB Storage)
    B --> C{dbt Transformation}
    C -->|Cleaned Views| D[DuckDB Analytical Layer]
    D --> E[Apache Superset / Grafana]
    E -->|Visualization| F[Personal Biometric Dashboard]

    style B fill:#fff,stroke:#333,stroke-width:2px
    style D fill:#fbbf24,stroke:#333,stroke-width:2px

Prerequisites 🛠️

To follow along, ensure you have the following in your tech stack:

DuckDB: Our ultra-fast in-process analytical database.
dbt-duckdb: For data modeling and transformations.
Apache Superset: For the "wow factor" visualizations.
Python 3.10+: To glue it all together.

Step 1: Ingesting the "Mess" with DuckDB

DuckDB is incredible because it can query CSV and JSON files directly without an ingestion step. Let’s say you have a massive heart_rate.csv from an Apple Health export.

Instead of waiting for a traditional DB to "load" the data, we can create a view instantly:

-- Create a view directly from a multi-million row CSV
CREATE VIEW raw_heart_rate AS 
SELECT 
    creationDate::TIMESTAMP as timestamp,
    value::DOUBLE as bpm
FROM read_csv_auto('data/apple_health_export/heart_rate.csv');

-- Check the 7-day rolling average of RHR
SELECT 
    date_trunc('day', timestamp) AS day,
    avg(bpm) OVER (
        ORDER BY timestamp 
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) as rolling_rhr
FROM raw_heart_rate
LIMIT 10;

Step 2: The dbt Transformation Layer

While raw SQL is cool, dbt (data build tool) allows us to treat our personal health data like a production warehouse. We'll use it to handle deduplication and calculate complex metrics like "Recovery Score" (HRV vs. Sleep Quality).

In your models/biometrics/stg_hrv.sql:

-- Clean and deduplicate HRV readings
WITH base AS (
    SELECT 
        timestamp,
        value as hrv_ms,
        device_id
    FROM {{ source('raw_data', 'hrv_export') }}
)
SELECT 
    date_trunc('hour', timestamp) as hour_bucket,
    avg(hrv_ms) as avg_hrv,
    count(*) as sample_count
FROM base
GROUP BY 1

Step 3: Visualizing with Apache Superset

Now for the fun part! Apache Superset connects to DuckDB using the duckdb_engine SQLAlchemy URI.

Start Superset (Docker is the easiest way).
Add a new Database.
Connection String: duckdb:///path/to/your/local_vault.duckdb

Once connected, you can create a "Health Command Center" showing your RHR trends, sleep cycles, and activity correlations. 📊

Why this beats "The Cloud" ☁️

For personal data, privacy is king. By using DuckDB, your data never leaves your laptop. For a deeper look at advanced data patterns and how to scale these pipelines for production environments, I highly recommend checking out the Official Wellally Blog. They have some incredible insights on high-performance data architectures that inspired this local-first approach.

The "Pro" Setup: Handling Large JSON 💾

If you are dealing with nested JSON (like Oura API exports), DuckDB’s JSON extension is a lifesaver. Here is how you flatten a complex health payload:

import duckdb

# Initialize DuckDB and load JSON extension
con = duckdb.connect('health_vault.db')
con.execute("INSTALL json; LOAD json;")

# Extracting nested sleep stages
query = """
SELECT 
    summary_date,
    json_extract_path(sleep_data, 'score')::INT as sleep_score,
    json_extract_path(sleep_data, 'levels.summary.rem.minutes')::INT as rem_min
FROM read_json_auto('data/oura_sleep_data.json')
"""

df = con.execute(query).df()
print(df.head())

Conclusion: Take Back Your Data 🥑

Building a personal biometric dashboard isn't just about the pretty charts; it's about mastering the OLAP workflow. Using DuckDB and Apache Superset, we've turned a pile of CSVs into a high-performance analytical engine that responds in milliseconds, not minutes.

Next Steps:

Export your health data (Apple Health, Garmin, or Google Fit).
Model it using dbt to find correlations between your caffeine intake and sleep quality.
Share your dashboard screenshots in the comments below!

If you enjoyed this "Learning in Public" project, don't forget to subscribe for more Data Engineering deep-dives. And for more production-ready examples of the modern data stack, head over to wellally.tech/blog.

Happy hacking! 💻🔥

DEV Community