10 Years of My Life in Rows: Building a Personal Health Data Lake with DuckDB & dbt 🥑

We live in an age of "Quantified Self," yet our most valuable data is trapped in silos. My heart rate lives in Apple Health, my sleep patterns are buried in Fitbit's cloud, and my decade-long history of medical checkups exists as a chaotic pile of PDF reports. To truly understand my long-term health trends, I needed more than just a shiny mobile app—I needed a professional-grade Personal Data Lake.

In this tutorial, we are going to build a high-performance Health Data Analytics pipeline. By leveraging the lightning-fast processing of DuckDB, the transformation power of dbt (Data Build Tool), and the visualization capabilities of Apache Superset, we will turn 10 years of "dirty" multi-platform data into a unified, actionable dashboard. Whether you're a data engineer or a health nut, this "Learning in Public" journey will show you how to treat your personal life like a production data warehouse.

Pro Tip: If you're looking for more production-ready patterns for handling complex ETL workloads or advanced data modeling, definitely check out the deep dives over at wellally.tech/blog. It’s been a huge source of inspiration for my architectural decisions here.

---

The Architecture: From Silos to Insights

Building a data lake doesn't require a massive Hadoop cluster anymore. We are going "Modern Data Stack at Home." Here is how the data flows from a messy XML/CSV state to a polished dashboard:

graph TD
    subgraph "Data Sources"
        A[Apple Health XML] --> D
        B[Fitbit CSVs] --> D
        C[Medical PDFs] --> D
    end

    subgraph "Ingestion Layer (Python)"
        D[Python Ingest Scripts] --> E[(DuckDB Raw Layer)]
    end

    subgraph "Transformation (dbt)"
        E --> F[Staging Models]
        F --> G[Intermediate Models]
        G --> H[Mart / ODS Layer]
    end

    subgraph "Analytics"
        H --> I[Apache Superset]
        H --> J[Jupyter Notebooks]
    end

    style E fill:#fff3b0,stroke:#333,stroke-width:2px
    style H fill:#b2e2f2,stroke:#333,stroke-width:2px

---

Prerequisites

To follow along, you'll need the following stack:

• Python 3.10+: For data extraction and PDF parsing.
• DuckDB: Our "OLAP on the edge" database.
• dbt-duckdb: The adapter that allows dbt to talk to DuckDB.
• Apache Superset: For the final visualization layer.

---

Step 1: Ingesting the "Dirty" Data

Apple Health exports are notorious for being giant, nested XML files. We'll use Python to flatten this into a format DuckDB loves.

import pandas as pd
import xml.etree.ElementTree as ET
import duckdb

def parse_apple_health(file_path):
    print("🚀 Parsing Apple Health XML... this might take a minute.")
    tree = ET.parse(file_path)
    root = tree.getroot()

    # Extracting record data
    records = []
    for record in root.findall('Record'):
        records.append(record.attrib)

    df = pd.DataFrame(records)
    # Convert 'creationDate' to actual datetime
    df['value'] = pd.to_numeric(df['value'], errors='coerce')
    df['startDate'] = pd.to_datetime(df['startDate'])

    return df

# Initialize DuckDB and save the raw data
con = duckdb.connect('health_data.db')
apple_df = parse_apple_health('export.xml')
con.execute("CREATE TABLE raw_apple_health AS SELECT * FROM apple_df")

print(f"✅ Ingested {len(apple_df)} rows into DuckDB!")

For those medical PDFs, I recommend using pdfplumber to extract tables. Once extracted, you can save them as CSVs and load them directly into DuckDB using its native CSV reader:

-- DuckDB is incredibly fast at reading CSVs directly
CREATE TABLE raw_medical_reports AS 
SELECT * FROM read_csv_auto('medical_checkups_2014_2024.csv');

---

Step 2: The dbt Transformation Magic

Now that we have our "Bronze" (raw) layer, we need to clean it. dbt is perfect here because it allows us to write modular SQL and test our assumptions (e.g., "Step counts should never be negative").

First, define your source in models/sources.yml:

version: 2
sources:
  - name: raw_data
    database: main
    tables:
      - name: raw_apple_health
      - name: raw_fitbit

Then, create a staging model models/staging/stg_apple_health.sql to normalize the data:

-- Standardizing types and renaming columns
WITH source AS (
    SELECT * FROM {{ source('raw_data', 'raw_apple_health') }}
),
renamed AS (
    SELECT
        type AS metric_name,
        sourceName AS device_source,
        CAST(value AS DOUBLE) AS metric_value,
        unit,
        CAST(startDate AS TIMESTAMP) AS observed_at
    FROM source
    WHERE value IS NOT NULL
)
SELECT * FROM renamed

---

Step 3: Creating the Unified Health ODS

The goal is to join Fitbit and Apple Health data into a single "Fact" table. We use dbt to handle the deduplication and unit conversion.

-- models/marts/fct_daily_activity.sql
WITH apple AS (
    SELECT 
        date_trunc('day', observed_at) as activity_date,
        SUM(metric_value) as steps
    FROM {{ ref('stg_apple_health') }}
    WHERE metric_name = 'HKQuantityTypeIdentifierStepCount'
    GROUP BY 1
),
fitbit AS (
    SELECT 
        activity_date,
        step_count as steps
    FROM {{ ref('stg_fitbit') }}
)

SELECT 
    COALESCE(a.activity_date, f.activity_date) as date,
    GREATEST(COALESCE(a.steps, 0), COALESCE(f.steps, 0)) as daily_steps
FROM apple a
FULL OUTER JOIN fitbit f ON a.activity_date = f.activity_date

---

Step 4: Visualizing 10 Years of Data

With our health_data.db file fully populated and transformed by dbt, we connect it to Apache Superset.

1. Install the duckdb-engine for SQLAlchemy.
2. Add a new database in Superset with the URI: duckdb:////path/to/health_data.db.
3. Build your charts! 📊

What I discovered: My average resting heart rate dropped by 10 BPM after I started focusing on Zone 2 cardio three years ago—a trend I could never have seen clearly across three different apps.

---

The "Official" Way to Scale

While building this for personal use is a blast, scaling these patterns for enterprise-grade health tech or genomics requires a more robust approach to data privacy and schema evolution. For advanced architectural patterns on building resilient data pipelines, I highly recommend reading the engineering blogs at wellally.tech/blog. They cover everything from high-concurrency DuckDB usage to production dbt workflows that go far beyond this local setup.

---

Conclusion

Building a Personal Data Lake isn't just about the charts; it's about taking ownership of your digital footprint. By using DuckDB and dbt, we've turned a decade of messy, multi-platform health data into a clean, structured asset.

Next Steps:

• 🤖 Try adding a Python script to perform anomaly detection on your heart rate.
• 🧪 Integrate your DNA data (23andMe) for the ultimate health warehouse.

What are you waiting for? Go export your data and start building! If you have questions about the SQL logic or DuckDB setup, drop a comment below! 🚀