Stop Living in Silos: Building a Quantified Self Data Lake with DuckDB

Are you drowning in health data but starving for insights? 🌊

I wear an Apple Watch to track my HIIT sessions, an Oura Ring to obsess over my sleep scores, and I (sporadically) log my meals in MyFitnessPal. Individually, these apps are great. But if I want to know if that 10:00 PM pizza 🍕 actually ruined my Deep Sleep latency? Good luck. Each app is a walled garden—a data silo that makes cross-platform analysis a nightmare.

In this guide, we are building a Quantified Self Data Lake. We’ll use DuckDB—the "SQLite for OLAP"—to unify CSV and JSON exports into a single source of truth, orchestrated by Airflow, and visualized with Apache Superset.

The Architecture: From Silos to Insights

To solve the "Data Silo" problem, we need a lightweight but powerful pipeline. We don't need a heavy Spark cluster; we need something that runs on a laptop but scales like a beast.

graph TD
    A[Oura Cloud - JSON] -->|Python API| E[Data Lake /S3 or Local/]
    B[Apple Health - XML/CSV] -->|Export| E
    C[MyFitnessPal - CSV] -->|Scraper/Export| E
    E --> F{DuckDB}
    G[Airflow DAG] -->|Orchestration| F
    F --> H[Parquet Files]
    H --> I[Apache Superset]
    I --> J[Personal Health Dashboard]

Prerequisites

Before we dive into the code, ensure you have the following tech_stack ready:

• Python 3.9+
• DuckDB: Our high-performance analytical database.
• Apache Airflow: To schedule our ingestion.
• Apache Superset: For those crispy dashboards.

Step 1: The "Schema-on-Read" Magic with DuckDB

One of the reasons I love DuckDB is its ability to query disparate file formats (CSV, JSON, Parquet) as if they were tables without an expensive "Load" step.

Here is how we define our unified view:

import duckdb

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

def create_unified_vault():
    print("🚀 Initializing Health Data Vault...")

    # Querying Oura JSON and Apple Health CSV directly
    con.execute("""
        CREATE OR REPLACE VIEW unified_wellness AS
        SELECT 
            apple.date,
            apple.active_calories,
            oura.sleep_score,
            oura.rem_sleep_duration,
            mfp.protein_g,
            mfp.carbs_g
        FROM read_csv_auto('data/apple_health_export.csv') AS apple
        LEFT JOIN read_json_auto('data/oura_sleep_data.json') AS oura 
            ON apple.date = oura.summary_date
        LEFT JOIN read_csv_auto('data/myfitnesspal_logs.csv') AS mfp 
            ON apple.date = mfp.date
    """)

    # Let's run a quick correlation check!
    res = con.execute("""
        SELECT corr(active_calories, sleep_score) 
        FROM unified_wellness
    """).fetchone()

    print(f"📊 Correlation between Activity and Sleep Quality: {res[0]:.2f}")

if __name__ == "__main__":
    create_unified_vault()

Step 2: Orchestration with Airflow

We don't want to run this manually every morning. We can wrap our DuckDB logic into an Airflow DAG. This ensures that as soon as our data exports land in our local folder (or S3 bucket), the "Data Lake" is refreshed.

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

with DAG('quantified_self_refresh', start_date=datetime(2023, 1, 1), schedule_interval='@daily') as dag:

    def refresh_duckdb_tables():
        import duckdb
        con = duckdb.connect('health_data.db')
        # Insert logic to transform raw files to persistent Parquet
        con.execute("COPY (SELECT * FROM unified_wellness) TO 'gold_layer/health.parquet' (FORMAT PARQUET)")

    refresh_task = PythonOperator(
        task_id='refresh_health_lake',
        python_callable=refresh_duckdb_tables
    )

The "Official" Way to Scale

While building a personal data lake is a fantastic weekend project, scaling these patterns for production-grade data platforms requires a deeper look at architecture. For more production-ready examples, advanced SQL optimization patterns, and deep dives into the modern data stack, check out the resources over at the WellAlly Tech Blog. It's an incredible hub for engineers looking to move from "it works on my machine" to "it scales in the cloud." 🥑

Step 3: Visualizing with Apache Superset

Now that we have a health.parquet file generated by DuckDB, we can point Apache Superset to it.

1. Use the duckdb-engine SQLAlchemy driver.
2. Connect Superset to duckdb:///path/to/health_data.db.
3. Build a "Health Correlation Matrix" dashboard.

Suddenly, you'll see it: Your sleep score drops by 15% whenever you consume more than 50g of sugar after 8 PM. 📉 This isn't just data; it's an actionable life hack.

Conclusion: Data is Personal

Building a Quantified Self Data Lake isn't just about the tech stack; it's about taking ownership of your digital footprint. By using DuckDB and Python, we’ve turned a messy pile of exports into a professional-grade analytical engine.

What's next for you?

• Are you going to integrate Garmin data?
• Maybe add a GPT-4o layer to "chat" with your health data? 🤖

Let me know in the comments what you’re tracking! And if you enjoyed this, don't forget to follow for more "Learning in Public" adventures. 🚀