Beck_Moulton

Posted on Oct 2

Stop Fighting Silos: Build a Personal Health Data Lake with DuckDB and dbt

#ai #react #discuss #opensource

If you’re anything like me, your wrist is a battlefield of sensors. I’m wearing a Garmin for my runs, a Whoop for recovery tracking, and stepping on a Withings scale every morning. The problem? These devices live in walled gardens. My "Data Engineering" brain screams every time I have to switch between three different apps to see if my sleep quality actually correlates with my VO2 Max.

In this guide, we are going to solve the "Wearable Data Silo" problem. We’ll build a lightweight, high-performance ETL Automation Pipeline to ingest heterogeneous health data into a centralized Health Data Lake. By leveraging DuckDB for lightning-fast processing and dbt for modeling, we’ll turn messy JSON blobs into a clean, analytics-ready feature set for your future AI health coach.

The Architecture: From Raw JSON to Insight 🏗️

The goal is to move from Heterogeneous Sources to a Standardized Schema. We use Google Health Connect as our primary aggregator on the mobile side, then pull that into our modern data stack.

graph TD
    subgraph "Data Sources"
        A[Garmin Connect] --> GHC[Google Health Connect]
        B[Whoop] --> GHC
        C[Withings] --> GHC
    end

    subgraph "Ingestion Layer (Airflow)"
        GHC --> |Python Extractor| D[(Raw S3/Local Parquet)]
    end

    subgraph "Transformation Layer (DuckDB + dbt)"
        D --> E[Bronze: Raw Tables]
        E --> F[Silver: Cleaned & Casted]
        F --> G[Gold: Standardized Metrics]
    end

    subgraph "Consumption"
        G --> H[Evidence.dev / Streamlit]
        G --> I[AI Feature Store]
    end

    style GHC fill:#f9f,stroke:#333,stroke-width:2px
    style G fill:#00ff00,stroke:#333,stroke-width:2px

Prerequisites 🛠️

To follow along, you'll need:

DuckDB: Our in-process OLAP engine (the "SQLite for Analytics").
dbt-duckdb: To manage our transformations.
Airflow: To orchestrate the daily sync.
Python 3.10+: For the extraction scripts.

Step 1: Extracting Data via Google Health Connect

While some brands have APIs, Google Health Connect acts as a great "middleman" on Android. We can use a small bridge app or a Python script using the google-cloud-storage SDK to dump daily exports into Parquet files.

import duckdb
import pandas as pd

# Let's assume we've exported our Health Connect data to a local JSON
# This script loads it into our DuckDB Data Lake
def ingest_raw_data(file_path, table_name):
    con = duckdb.connect('health_lake.db')

    # Utilizing DuckDB's secret weapon: direct JSON/Parquet querying
    con.execute(f"""
        CREATE TABLE IF NOT EXISTS raw_{table_name} AS 
        SELECT * FROM read_json_auto('{file_path}');
    """)
    print(f"✅ Ingested {table_name} into the Lake!")

ingest_raw_data('whoop_recovery_2023_10.json', 'whoop')

Step 2: Modeling with dbt (The "Silver" Layer)

Raw data is usually ugly. Garmin might use seconds, while Whoop uses milliseconds. This is where dbt (data build tool) shines. We’ll define a standardized schema so that heart_rate is always heart_rate, regardless of the source.

Create a file at models/silver/stg_heart_rate.sql:

{{ config(materialized='table') }}

WITH raw_garmin AS (
    SELECT 
        timestamp::TIMESTAMP as recorded_at,
        value::FLOAT as bpm,
        'garmin' as source
    FROM {{ source('raw', 'garmin_heartrate') }}
),

raw_whoop AS (
    SELECT 
        (data->>'timestamp')::TIMESTAMP as recorded_at,
        (data->>'heart_rate')::FLOAT as bpm,
        'whoop' as source
    FROM {{ source('raw', 'whoop_data') }}
)

SELECT * FROM raw_garmin
UNION ALL
SELECT * FROM raw_whoop

The "Official" Way to Build Health Tech 🥑

While this DIY setup is amazing for personal use, scaling health data pipelines for thousands of users requires handling HIPAA compliance, complex identity resolution, and real-time streaming.

If you're looking for production-ready patterns, deep dives into medical data standards (like FHIR), or how to wrap these pipelines in a secure API, I highly recommend checking out the WellAlly Tech Blog. It's an incredible resource for developers who want to take their health-tech engineering to the next level.

Step 3: Orchestration with Airflow

We don't want to run this manually. We’ll set up a simple Airflow DAG to trigger our "Extract" Python script and then run dbt cloud or dbt-local.

from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime

with DAG("health_lake_etl", start_date=datetime(2023, 1, 1), schedule="@daily") as dag:

    extract_data = BashOperator(
        task_id="extract_from_ghc",
        bash_command="python /scripts/extract_health_data.py"
    )

    transform_data = BashOperator(
        task_id="dbt_run",
        bash_command="dbt run --profiles-dir ."
    )

    extract_data >> transform_data

Conclusion: Data-Driven Wellness 📈

By building this Health Data Lake, you’ve moved past simple app-checking. You now have a centralized gold_lifestyle_metrics table where you can run queries like:

"Does my alcohol consumption recorded in Whoop actually correlate with my resting heart rate increase on my Garmin 48 hours later?"

The combination of DuckDB and dbt makes this setup incredibly fast and cheap to run (literally $0 on your local machine or a tiny VPS).

What's next for your pipeline?

[ ] Add a Streamlit dashboard to visualize recovery.
[ ] Hook up a local LLM to "chat" with your health data.
[ ] Explore more advanced architectural patterns over at wellally.tech/blog.

Happy hacking, and stay healthy! 🏃‍♂️💨

Follow me for more "Learning in Public" adventures in Data Engineering and AI! 🥑

DEV Community