From "Dirty" Data to Actionable Insights: Building a Quantified Self Warehouse with DuckDB and dbt

If you've ever tried to export your health data from a Garmin watch, an Oura ring, or an Apple Watch, you know the "Quantified Self" dream quickly turns into a data engineering nightmare. You’re met with a chaotic mix of nested JSONs, weirdly formatted CSVs, and XML files that look like they were designed in the 90s.

To build a truly scalable data warehouse for personal health metrics, we need a robust Modern Data Stack approach. In this guide, we’ll solve the "dirty data" problem using DuckDB for lightning-fast local processing and dbt (data build tool) for professional-grade data modeling. By the end of this tutorial, you'll transform fragmented lifestyle logs into a unified dashboard using Superset, turning raw pixels of data into clear health trends.

💡 Looking for production-ready patterns? This tutorial covers the fundamentals of health data modeling. For more advanced architectural patterns and deep dives into AI-integrated data engineering, check out the official blog at wellally.tech/blog.

---

🏗 The Architecture: From Raw Logs to Analytics

Before we write any code, let's look at how the data flows. We use a Medallion Architecture (Bronze/Silver/Gold) to ensure our data remains traceable and clean.

graph TD
    subgraph "Data Sources"
        A[Garmin Connect JSON] 
        B[Apple Health XML]
        C[Oura Ring CSV]
    end

    subgraph "Ingestion (Python/DuckDB)"
        D[Python Loader] --> E[(DuckDB: RAW Layer)]
    end

    subgraph "Transformation (dbt)"
        E --> F[stg_health__garmin]
        E --> G[stg_health__oura]
        F --> H[fct_daily_activity]
        G --> H
    end

    subgraph "Visualization"
        H --> I[Apache Superset]
    end

    style E fill:#f9f,stroke:#333,stroke-width:2px
    style H fill:#00ff00,stroke:#333,stroke-width:2px

---

🛠 Prerequisites

To follow along, make sure you have the following installed:

• Python 3.10+
• DuckDB: The "SQLite for Analytics."
• dbt-duckdb: The adapter that lets dbt talk to DuckDB.
• Apache Superset: For the final dashboard (Docker is recommended).

pip install dbt-duckdb duckdb pandas

---

Step 1: Ingesting "Dirty" Data with Python

Health data is notoriously inconsistent. Garmin might give you distance in meters, while Apple Health uses kilometers or miles. Our first step is to load these files into a raw schema in DuckDB.

import duckdb
import pandas as pd

# Initialize our local warehouse
con = duckdb.connect('health_dw.duckdb')

# Create a raw schema
con.execute("CREATE SCHEMA IF NOT EXISTS raw;")

# Load a messy Garmin CSV (Example)
garmin_df = pd.read_csv('garmin_activities.csv')
con.execute("CREATE TABLE raw.garmin_data AS SELECT * FROM garmin_df")

# Load Oura JSON
con.execute("CREATE TABLE raw.oura_data AS SELECT * FROM read_json_auto('oura_sleep.json')")

print("✅ Raw data ingested into DuckDB!")

---

Step 2: Modeling with dbt (The Silver Layer)

Now for the magic. We use dbt to clean the data. Instead of writing messy SQL scripts, we define models.

First, let's create a staging model (models/staging/stg_garmin.sql) to cast types and handle nulls:

-- models/staging/stg_garmin.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'garmin_data') }}
),

renamed AS (
    SELECT
        activity_id::VARCHAR AS activity_id,
        (activity_date::TIMESTAMP) AS activity_at,
        UPPER(activity_type) AS activity_type,
        -- Convert meters to kilometers
        ROUND(distance_m / 1000, 2) AS distance_km,
        duration_sec / 60 AS duration_minutes,
        calories_kcal AS calories
    FROM source
    WHERE activity_id IS NOT NULL
)

SELECT * FROM renamed

---

Step 3: The Unified "Gold" Table

The goal of our Quantified Self project is to have one table where we can see our "Daily Readiness." We combine Oura (Sleep) and Garmin (Activity) data.

-- models/marts/fct_daily_wellness.sql
WITH daily_activity AS (
    SELECT 
        CAST(activity_at AS DATE) AS date_day,
        SUM(distance_km) AS total_km,
        SUM(calories) AS active_calories
    FROM {{ ref('stg_garmin') }}
    GROUP BY 1
),

sleep_data AS (
    SELECT 
        day::DATE AS date_day,
        sleep_score,
        hrv_average
    FROM {{ ref('stg_oura') }}
)

SELECT
    a.date_day,
    a.total_km,
    a.active_calories,
    s.sleep_score,
    s.hrv_average,
    -- Custom health metric
    (s.sleep_score * 0.6 + (a.active_calories / 1000) * 0.4) AS readiness_index
FROM daily_activity a
LEFT JOIN sleep_data s ON a.date_day = s.date_day

---

🥑 Professional Insight: Why this matters?

Standardizing health data is the "Hello World" of modern data engineering. By using DuckDB, you get enterprise-level analytical power (columnar storage, vectorized execution) on your local machine without the cost of Snowflake or BigQuery.

For those looking to scale this into a production environment—perhaps for a health-tech startup or an automated coaching platform—you'll need to consider CI/CD for your dbt models and data quality testing (Great Expectations). You can find more production-ready examples and advanced dbt macros over at wellally.tech/blog.

---

Step 4: Visualizing in Superset

1. Connect Superset to your health_dw.duckdb file using the duckdb:// SQLAlchemy URI.
2. Point it to the fct_daily_wellness table.
3. Create a Mixed Chart: Bar chart for total_km and a Line chart for readiness_index.

Now, you can finally see if that extra 5km run actually tanked your sleep quality the next day! 📉

---

Conclusion

Building a Quantified Self data warehouse doesn't require a massive cloud budget. With the combination of DuckDB and dbt, you can turn "dirty" CSVs into a structured, professional-grade analytics engine.

Next Steps:

1. Automate: Use GitHub Actions to run your ingestion script daily.
2. Expand: Add data from MyFitnessPal or Apple Health exports.
3. Refine: Check out WellAlly's technical blog for deeper insights on data modeling and AI integration.

Happy hacking! 🚀 If you built something cool with your health data, drop a comment below or share your Superset screenshots!