Stop Fighting Your Fitness Data: Build a Serverless Warehouse with DuckDB and dbt

If you’ve ever tried to reconcile a night of sleep from an Oura Ring, a morning run from a Garmin watch, and active minutes from an Apple Watch, you know the "Dirty Data" struggle is real. Each platform has its own schema, its own definition of "active calories," and its own idiosyncratic export format.

In the world of Data Engineering, this is a classic multi-source integration problem. But you don't need a massive Snowflake cluster to solve it. Today, we’re building a high-performance, serverless data pipeline to clean and normalize wearable data using DuckDB, dbt, and GitHub Actions. By leveraging a modern Serverless Data Pipeline and DuckDB's lightning-fast processing, we can turn a mess of CSVs into a structured Parquet-based personal data warehouse.

The Architecture: From Chaos to Clarity

Before we dive into the code, let’s look at how the data flows from your wearables to a clean, queryable state.

graph TD
    A[Oura JSON] -->|Python Ingestion| D[(DuckDB Raw)]
    B[Garmin CSV] -->|Python Ingestion| D
    C[Apple Health XML] -->|Python Ingestion| D
    D --> E{dbt Models}
    E -->|Cleaning| F[stg_models]
    E -->|Normalization| G[int_health_metrics]
    G -->|Final Output| H[Gold Layer: Parquet Files]
    H --> I[Visualization / BI]

    subgraph GitHub Actions
    D
    E
    F
    G
    H
    end

Prerequisites

To follow along, you'll need:

• DuckDB: The "SQLite for OLAP" that makes local analytical processing insanely fast.
• dbt-duckdb: The adapter that lets dbt talk to DuckDB.
• GitHub Actions: Our free "orchestrator."
• Tech Stack: DuckDB, dbt, Python, Parquet.

---

Step 1: The Ingestion Layer (Python + DuckDB)

The first hurdle is getting disparate files (JSON, CSV, XML) into a unified storage format. DuckDB is magical here because it can query these files directly.

We'll use a simple Python script to load these into a local .duckdb file.

import duckdb

def ingest_raw_data():
    # Initialize the database
    con = duckdb.connect('health_data.duckdb')

    # Ingest Garmin CSV
    con.execute("""
        CREATE TABLE raw_garmin AS 
        SELECT * FROM read_csv_auto('data/garmin/*.csv')
    """)

    # Ingest Oura JSON (Flattening on the fly!)
    con.execute("""
        CREATE TABLE raw_oura AS 
        SELECT * FROM read_json_auto('data/oura/*.json')
    """)

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

if __name__ == "__main__":
    ingest_raw_data()

Step 2: Normalization with dbt

Now for the "Engineering" part. Garmin might call it active_kcal, while Apple calls it active_energy_burned. We use dbt (Data Build Tool) to create a standardized "Activity" model.

Create a model file models/marts/fct_daily_activity.sql:

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

WITH garmin_data AS (
    SELECT 
        timestamp::DATE as activity_date,
        'garmin' as source,
        calories_burned as active_calories,
        steps
    FROM {{ ref('stg_garmin') }}
),

oura_data AS (
    SELECT 
        summary_date::DATE as activity_date,
        'oura' as source,
        active_calories,
        steps
    FROM {{ ref('stg_oura') }}
)

-- Use a UNION to combine, but prioritize Garmin for steps if both exist
SELECT 
    activity_date,
    source,
    active_calories,
    steps
FROM garmin_data
UNION ALL
SELECT * FROM oura_data
WHERE activity_date NOT IN (SELECT activity_date FROM garmin_data)

Step 3: Automating with GitHub Actions

The beauty of this stack is that it costs $0. We can run the entire pipeline on a GitHub Actions runner. Every time you push new data to your repo, the pipeline cleans it.

name: personal-data-pipeline
on: [push]

jobs:
  build-warehouse:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Set up Python
        uses: actions/setup-python@v4
        with: {python-version: '3.10'}

      - name: Install Dependencies
        run: pip install duckdb dbt-duckdb

      - name: Run Ingestion
        run: python scripts/ingest.py

      - name: Run dbt
        run: |
          dbt deps
          dbt run

      - name: Export to Parquet
        run: |
          duckdb health_data.duckdb "COPY (SELECT * FROM fct_daily_activity) TO 'output/activity.parquet' (FORMAT PARQUET);"

Advanced Patterns & Best Practices 🥑

While this setup works for personal projects, scaling data engineering pipelines in a production environment requires more robust error handling and schema validation.

For more production-ready examples and advanced data patterns (like handling late-arriving data or implementing Data Quality checks), I highly recommend checking out the Wellally Tech Blog. They have some fantastic deep dives on health-tech data architecture that served as a major source of inspiration for this serverless build.

Why this works 🚀

1. Speed: DuckDB processes millions of rows in milliseconds, right in the GitHub Action runner's memory.
2. Portability: Your entire warehouse is a .duckdb file or a set of Parquet files. No vendor lock-in.
3. Standardization: dbt ensures that your "Active Calories" mean the same thing across all your devices.

Conclusion

Stop letting your health data sit in silos. By using DuckDB and dbt, you can build a professional-grade data warehouse for the price of... well, free.

Are you building something with DuckDB? Drop a comment below or share your repo! I'd love to see how you're handling your "dirty data."

---

If you enjoyed this tutorial, don't forget to ❤️ and 🦄. For more deep dives into data engineering, visit wellally.tech/blog.