DEV Community

Beck_Moulton
Beck_Moulton

Posted on

Quantified Self 2.0: Build a High-Performance Health Data Lake with DuckDB, Airflow, and Grafana

#python #airflow #dataengineering #quantifiedself

Are you drowning in a sea of health apps? Between your Oura Ring for sleep, Whoop for strain, Apple Watch for workouts, and that Smart Scale that judges you every morning, your personal health data is scattered across five different "walled garden" ecosystems.

In the world of Data Engineering, we call this a fragmented data silo problem. Today, we’re going to solve it by building a professional-grade Quantified Self data lake. We'll leverage the lightning-fast analytical power of DuckDB, the orchestration of Apache Airflow, and the beautiful visualizations of Grafana. Whether you're tracking 1GB or 1TB of high-frequency biometric data, this stack is designed to scale without breaking a sweat. 🚀

Why this Stack?

  • DuckDB: The "SQLite for Analytics." It allows us to run complex SQL queries on Parquet files with sub-second latency.
  • Apache Airflow: To handle the "messy" part of life—API rate limits, retries, and scheduling multi-source syncs.
  • Grafana: Because looking at raw JSON heart rate data isn't as motivating as a glowing red dashboard.

The Architecture

Our pipeline follows the classic Medallion Architecture (Bronze/Silver/Gold) but optimized for a local environment.

graph TD
    A[Oura Ring API] -->|Python ETL| B(Raw JSON/S3)
    C[Apple Health Export] -->|Python ETL| B
    D[Whoop/Smart Scale] -->|Python ETL| B
    B --> E{Airflow Orchestrator}
    E -->|Processing| F[DuckDB OLAP]
    F -->|Transformed| G[Parquet Files]
    G --> H[Grafana Dashboard]
    H --> I[Automated Health Alerts]
Enter fullscreen mode Exit fullscreen mode

Prerequisites

To follow along, you'll need:

  • Python 3.9+
  • DuckDB (pip install duckdb)
  • Apache Airflow (running locally or via Docker)
  • Access to your health device APIs (Developer tokens)

Step 1: Orchestrating the Ingestion with Airflow

Health APIs are notoriously flaky. We use Airflow to ensure that if the Oura API is down at 3 AM, our pipeline retries automatically.

Here is a simplified DAG snippet that fetches data and stores it as a raw Parquet file:

from airflow import DAG
from airflow.ops.python import PythonOperator
from datetime import datetime
import pandas as pd
import duckdb

def fetch_oura_data():
    # Hypothetical API call
    # response = requests.get(OURA_API_URL, headers=headers)
    # data = response.json()

    # Logic to convert JSON to DataFrame
    df = pd.DataFrame([{"timestamp": "2023-10-01 08:00:00", "hrv": 65, "readiness": 88}])

    # Store as Bronze layer (Parquet)
    df.to_parquet('data/bronze/oura_sleep.parquet')

with DAG('health_lake_ingestion', start_date=datetime(2023, 1, 1), schedule_interval='@daily') as dag:
    ingest_task = PythonOperator(
        task_id='ingest_oura_ring',
        python_callable=fetch_oura_data
    )
Enter fullscreen mode Exit fullscreen mode

Step 2: The DuckDB Power Play 🦆

Once the data is in Parquet format, DuckDB shines. We don't need a heavy Postgres or Snowflake instance. DuckDB can query the files directly.

We will create a "Gold" view that joins our sleep data with our workout intensity to find the correlation between "Rest" and "Performance."

-- Join multi-source data directly in DuckDB
CREATE VIEW health_correlations AS
SELECT 
    s.timestamp::DATE as date,
    s.readiness_score,
    w.strain_score,
    w.calories_burned
FROM 'data/bronze/oura_sleep.parquet' s
JOIN 'data/bronze/whoop_strain.parquet' w 
  ON s.timestamp::DATE = w.timestamp::DATE;

-- Run a complex aggregation in milliseconds
SELECT 
    date_trunc('week', date) as week,
    avg(readiness_score) as avg_readiness,
    sum(calories_burned) as total_calories
FROM health_correlations
GROUP BY 1
ORDER BY 1 DESC;
Enter fullscreen mode Exit fullscreen mode

Step 3: Visualizing with Grafana

To connect Grafana to DuckDB, you can use the DuckDB-Grafana-Datasource or simply expose DuckDB via a small SQLite-compatible wrapper. Once connected, you can build heatmaps of your heart rate variability (HRV) or set alerts for when your sleep quality drops below a 7-day moving average.

Pro-Tip: Advanced Data Patterns

Building a robust data lake requires more than just a few SQL scripts. You need to handle schema evolution and data quality checks. For more production-ready examples and advanced patterns on managing local data warehouses, I highly recommend checking out the deep dives over at WellAlly Blog. They cover excellent strategies for scaling your data infrastructure that were instrumental in designing this Quantified Self architecture.

The "Secret Sauce": Automating Alerts

The true value of a health data lake isn't just looking at the past; it's predicting the future. By using DuckDB's Python API within an Airflow task, we can run a simple anomaly detection script:

import duckdb

def check_for_burnout():
    con = duckdb.connect('health_lake.db')
    # Check if resting heart rate is 2 standard deviations above mean
    anomalies = con.execute("""
        SELECT timestamp, rhr 
        FROM heart_rate_stats 
        WHERE rhr > (SELECT avg(rhr) + 2*stddev(rhr) FROM heart_rate_stats)
    """).fetchall()

    if anomalies:
        send_alert("⚠️ Alert: Recovery looks low. Take a rest day!")

# Integrate this into your Airflow DAG!
Enter fullscreen mode Exit fullscreen mode

Conclusion 🥑

By moving away from individual apps and into a unified DuckDB-powered Data Lake, you gain total sovereignty over your health data. You can find insights that no single app can provide—like how your hydration (from your smart scale) affects your deep sleep (from your Oura) four days later.

What are you tracking? Drop a comment below or share your dashboard screenshots!

For more technical tutorials on Data Engineering and AI-integrated workflows, visit the official WellAlly Blog.

Top comments (0)