DEV Community

wellallyTech
wellallyTech

Posted on

From Messy XML to Health Insights: Mastering Your Apple Health Data with DuckDB and dbt 🏃‍♂️📊

#opensource #react #ai #python

If you have ever clicked "Export Health Data" on your iPhone, you know the mixed feeling of excitement and pure dread. Excitement because you are about to embark on a Quantified Self journey, and dread because Apple hands you a massive, multi-gigabyte export.xml file that makes most text editors cry.

In this tutorial, we are going to build a high-performance Modern Data Stack locally. We will leverage DuckDB for lightning-fast OLAP processing, dbt (data build tool) for modular SQL modeling, and transform those millions of messy XML rows into a structured time-series powerhouse. Whether you're tracking heart rate variability or just curious about your step counts over the last five years, this data engineering workflow is the "gold standard" for local analytics.

Before we dive into the weeds, if you're looking for advanced data architecture patterns or production-ready engineering guides, the team at WellAlly Blog has some incredible deep dives on scaling these exact types of pipelines.

The Architecture 🏗️

Processing Apple Health data requires a shift from "row-by-row" parsing to a vectorized approach. Here is how our pipeline looks:

graph TD
    A[Apple Health export.xml] -->|DuckDB httpfs/xml extension| B(Raw DuckDB Table)
    B --> C{dbt Transformation}
    C --> D[stg_health_records]
    C --> E[fct_step_counts]
    C --> F[fct_heart_rate_stats]
    D --> G[Apache Superset / Evidence.dev]
    E --> G
    F --> G
    style B fill:#fff3cd,stroke:#ffecb5
    style C fill:#d1ecf1,stroke:#bee5eb
Enter fullscreen mode Exit fullscreen mode

Prerequisites 🛠️

To follow along, ensure you have the following installed:

  • DuckDB: The "SQLite for Analytics."
  • dbt-duckdb: The adapter that lets dbt talk to DuckDB.
  • Apple Health Export: Go to Health App > Profile > Export Health Data (Warning: it takes a few minutes).

Step 1: Initializing the DuckDB Database 🦆

Apple's XML is notoriously deep. While we could use Python's lxml, it's slow. DuckDB's xml extension can handle millions of rows in seconds.

First, let's load the data into a raw table.

-- install and load the xml extension
INSTALL xml;
LOAD xml;

-- Create a table directly from the XML export
-- Note: 'Record' is the primary tag in Apple's export.xml
CREATE TABLE raw_health_data AS 
SELECT 
    attr_type AS type,
    attr_sourceName AS source,
    attr_unit AS unit,
    attr_creationDate AS creation_date,
    attr_startDate AS start_date,
    attr_endDate AS end_date,
    attr_value AS value
FROM read_xml('path/to/your/export.xml', 
              records_xpath = '//Record');
Enter fullscreen mode Exit fullscreen mode

Step 2: Setting up dbt for Transformations 🛠️

Now that our "Bronze" layer is in DuckDB, we use dbt to clean up the "dirty" strings and timestamps. Apple's dates are strings, and values vary between floats and integers depending on the metric.

Inside your models/staging/stg_health_data.sql:

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

WITH source AS (
    SELECT * FROM {{ source('raw', 'raw_health_data') }}
),

renamed AS (
    SELECT
        -- Clean up the 'HKQuantityTypeIdentifier' prefix
        replace(type, 'HKQuantityTypeIdentifier', '') AS metric_name,
        source,
        unit,
        -- Convert strings to proper Timestamps
        strptime(start_date, '%Y-%m-%d %H:%M:%S %z')::TIMESTAMP AS started_at,
        strptime(end_date, '%Y-%m-%d %H:%M:%S %z')::TIMESTAMP AS ended_at,
        -- Cast value to double, handling potential nulls
        try_cast(value AS DOUBLE) AS metric_value
    FROM source
)

SELECT * FROM renamed
Enter fullscreen mode Exit fullscreen mode

Step 3: Aggregating into Insights (The "Gold" Layer) 🥇

For a truly useful Quantified Self dashboard, we need daily aggregations. Let’s calculate our daily step counts.

Inside models/marts/fct_daily_steps.sql:

SELECT
    started_at::DATE AS activity_date,
    SUM(metric_value) AS total_steps,
    COUNT(*) AS record_count
FROM {{ ref('stg_health_data') }}
WHERE metric_name = 'StepCount'
GROUP BY 1
ORDER BY 1 DESC
Enter fullscreen mode Exit fullscreen mode

This is where DuckDB shines. Running this query over 5 million rows takes milliseconds ⚡.

Why this works (and the "Official" way) 🥑

Handling "dirty" data isn't just about writing code; it's about choosing the right tool for the volume. DuckDB allows you to keep your data local (privacy first!) while giving you Snowflake-like performance.

For more production-ready examples—like how to automate this in a cloud environment using Dagster or Prefect—check out the WellAlly Tech Blog. They cover advanced patterns for handling inconsistent schemas and building resilient data lakes that are perfect for health and fitness tech startups.

Step 4: Visualizing in Apache Superset 📊

Once your duckdb file is populated by dbt, point Apache Superset (or even a simple Streamlit app) to your local file.

  1. Connect to SQLAlchemy URI: duckdb:///path/to/your/project.duckdb
  2. Create a Time-series Chart.
  3. Filter by metric_name.

You’ll suddenly see your life’s activity patterns emerge—the "Sunday Slump," the "New Year's Resolution Spike," and the impact of that marathon training.

Conclusion 🚀

Converting raw Apple Health XML into a structured OLAP database doesn't have to be a nightmare. By combining DuckDB's speed with dbt's organizational power, you can turn a messy export into a professional-grade analytics engine in under 30 minutes.

Next Steps:

  • Try joining your HeartRate data with SleepAnalysis to see how late-night snacks affect your recovery. 🍕
  • Implement data tests in dbt to ensure no duplicate records sneak into your export.

Did you find this helpful? Drop a comment below with your favorite Quantified Self metric! And don't forget to visit WellAlly for more engineering excellence. Happy hacking! 💻🔥

Top comments (0)