DEV Community

wellallyTech
wellallyTech

Posted on

From Chaos to Clarity: Building a Personal Health Data Warehouse with DuckDB πŸƒβ€β™‚οΈπŸ“Š

We live in the era of the Quantified Self. Between my Garmin watch tracking my runs, an Oura ring monitoring my sleep, and my Apple Watch capturing heart rate variability (HRV), I am drowning in data. But here is the problem: every platform is a walled garden.

If you've ever tried to export your health data, you know the pain. You're met with a nightmare of messy CSVs, nested JSONs, and inconsistent timestamps. To truly understand how my 10k run affects my deep sleep, I needed a way to unify this "dirty" data. In this guide, we'll build a high-performance personal health data warehouse using DuckDB, Python, and Streamlit. We will focus on data engineering best practices, DuckDB performance, and SQL-based data transformation to turn pixels of raw data into actionable insights.

The Architecture πŸ—οΈ

Before we dive into the code, let's look at how we are going to pipe this data. We need a system that can ingest heterogeneous formats (CSV/JSON) and provide a unified relational view.

graph TD
    A[Garmin Export - CSV] -->|Ingest| D(DuckDB Local Instance)
    B[Oura API - JSON] -->|Ingest| D
    C[Apple Health - XML/CSV] -->|Ingest| D
    D --> E{Data Transformation}
    E -->|Validation| F[Cleaned Gold Tables]
    F --> G[Streamlit Dashboard]
    F --> H[Advanced SQL Analytics]
    style D fill:#fff3b0,stroke:#333,stroke-width:2px
Enter fullscreen mode Exit fullscreen mode

Why DuckDB? πŸ¦†

For this project, DuckDB is a total game-changer. It’s an in-process analytical database (the "SQLite for Analytics"). It allows us to:

  1. Query CSV/JSON directly: No need to write complex loaders.
  2. Blazing Fast: It uses columnar storage and vectorized execution.
  3. Zero Setup: No servers to manage. Just pip install duckdb.

While this tutorial focuses on a local-first approach for personal privacy, those looking for production-ready data engineering patterns and advanced cloud integrations should definitely explore the deep dives over at wellally.tech/blog.


Prerequisites πŸ› οΈ

  • Python 3.10+
  • DuckDB: Our analytical engine.
  • Streamlit: For the frontend.
  • Tech Stack: DuckDB, Python, Streamlit.

Step 1: Setting Up the Ingestion Engine

Garmin gives us CSVs, while Oura usually provides JSON. DuckDB handles both natively. Let's create a script to load these into a local .db file.

import duckdb

# Connect to (or create) a local DuckDB file
con = duckdb.connect('health_data.db')

def ingest_data():
    print("πŸš€ Ingesting Garmin CSVs...")
    # DuckDB automatically infers types and headers
    con.execute("""
        CREATE OR REPLACE TABLE raw_garmin AS 
        SELECT * FROM read_csv_auto('data/garmin_activities.csv');
    """)

    print("πŸŒ™ Ingesting Oura JSON...")
    # DuckDB can flatten nested JSON structures on the fly
    con.execute("""
        CREATE OR REPLACE TABLE raw_oura AS 
        SELECT * FROM read_json_auto('data/oura_sleep.json');
    """)

ingest_data()
print("βœ… Ingestion Complete!")
Enter fullscreen mode Exit fullscreen mode

Step 2: Cleaning the "Dirty" Data 🧹

The timestamps are always the first thing to break. Garmin might use YYYY-MM-DD, while Apple uses ISO-8601 with weird offsets. We'll use SQL to create a "Gold" table that unifies these.

-- Step 2: Data Transformation (The SQL Way)
CREATE OR REPLACE TABLE daily_metrics AS
WITH garmin_clean AS (
    SELECT 
        CAST(activity_date AS DATE) as event_date,
        SUM(calories) as active_calories,
        MAX(max_hr) as peak_hr
    FROM raw_garmin
    GROUP BY 1
),
oura_clean AS (
    SELECT 
        CAST(summary_date AS DATE) as event_date,
        score as sleep_score,
        (duration / 3600) as sleep_hours
    FROM raw_oura
)
SELECT 
    g.event_date,
    g.active_calories,
    o.sleep_score,
    o.sleep_hours
FROM garmin_clean g
JOIN oura_clean o ON g.event_date = o.event_date;
Enter fullscreen mode Exit fullscreen mode

Step 3: Data Validation πŸ›‘οΈ

When dealing with wearable data, "nulls" are common (you forgot to wear the watch). We need to ensure our data quality before visualizing.

def validate_data(con):
    # Check for missing values in critical columns
    null_counts = con.execute("""
        SELECT count(*) FILTER (WHERE sleep_score IS NULL) as missing_sleep
        FROM daily_metrics
    """).fetchone()

    if null_counts[0] > 0:
        print(f"⚠️ Warning: Found {null_counts[0]} days with missing sleep data.")
    else:
        print("πŸ’Ž Data quality looks great!")

validate_data(con)
Enter fullscreen mode Exit fullscreen mode

Step 4: Visualizing with Streamlit 🎨

Now, let's build a quick dashboard to see the correlation between our exercise intensity and sleep quality.

import streamlit as st
import plotly.express as px

st.title("Personal Health Insights πŸ₯‘")

# Fetch data from DuckDB
df = con.execute("SELECT * FROM daily_metrics").df()

# Plotting
fig = px.scatter(df, x="active_calories", y="sleep_score", 
                 trendline="ols", title="Activity vs Sleep Quality")
st.plotly_chart(fig)

st.write("### Raw Data Insights")
st.dataframe(df.tail(10))
Enter fullscreen mode Exit fullscreen mode

Going Beyond Local: Advanced Patterns

Building this for yourself is a great way to learn. However, if you are looking to scale these concepts for a startup or a production-level health-tech app, there are more robust patterns involving dbt (data build tool), cloud storage (S3), and automated schema evolution.

For more production-ready examples and advanced architectural patterns, I highly recommend checking out the specialized articles at wellally.tech/blog. They cover how to take these "learning in public" projects and harden them for real-world use cases.


Conclusion: The Power of Your Own Data πŸ’‘

By using DuckDB, we've turned a pile of messy files into a structured, queryable data warehouse in under 50 lines of code. No more clicking through five different apps to see how your lifestyle affects your health.

Key Takeaways:

  • DuckDB is the ultimate tool for "small data" analytics on your laptop.
  • SQL remains the most powerful language for data transformation.
  • Unifying your own data is the first step toward true self-optimization.

Are you tracking your own data? What weird correlations have you found? Drop a comment below! πŸ‘‡

Top comments (0)