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
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:
- Query CSV/JSON directly: No need to write complex loaders.
- Blazing Fast: It uses columnar storage and vectorized execution.
- 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!")
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;
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)
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))
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)