DEV Community

Beck_Moulton
Beck_Moulton

Posted on

From Chaos to Clarity: Building a Personalized "Quantified Self" Dashboard with dbt and Superset

#sql #analytics #dataengineering #airflow

We live in an era of "Self-Tracking." Between your Garmin watch tracking your VO2 Max, MyFitnessPal logging your macros, and specialized sleep trackers monitoring your REM cycles, we are generating gigabytes of personal health data. But here is the problem: this data is siloed.

If you want to see how your late-night pizza (MyFitnessPal) affects your deep sleep (Garmin), you’re stuck flipping between three different apps. In this tutorial, we will apply professional Data Engineering principles to your personal life. We’ll build a standardized Quantified Self data mart using dbt modeling, PostgreSQL, and Airflow, turning fragmented "dirty" data into a high-performance health dashboard. Whether you're a data enthusiast or a fitness nerd, mastering this health data ETL pipeline is a fantastic way to learn modern data stack (MDS) fundamentals. 🚀

The Architecture: How the Data Flows

Before we dive into the code, let's look at the "big picture." We aren't just moving data; we are transforming it from raw JSON blobs into a clean, star-schema warehouse.

graph TD
    A[Garmin API / CSV] -->|Extract| E(Airflow / Python)
    B[MyFitnessPal] -->|Extract| E
    C[Sleep Tracker] -->|Extract| E
    E -->|Load| F[(PostgreSQL Raw Schema)]
    F -->|Transform| G{dbt}
    G --> H[Staging Models]
    H --> I[Mart: Daily Health Metrics]
    I --> J[Apache Superset Dashboard]
    style G fill:#f96,stroke:#333,stroke-width:2px
Enter fullscreen mode Exit fullscreen mode

Prerequisites

To follow along, you’ll need:

  • PostgreSQL: Our storage engine.
  • dbt-core: For data transformation.
  • Apache Superset: For visualization.
  • Apache Airflow: For orchestration (we'll focus on the logic today).

Step 1: Landing the Raw Data

First, we ingest our data into a raw schema in PostgreSQL. Most health apps export data in JSON or CSV. Let's assume we have a table called raw_garmin_activities and raw_nutrition_logs.

-- Example of what our raw data looks like
CREATE SCHEMA raw;
CREATE TABLE raw.garmin_steps (
    data JSONB
);
Enter fullscreen mode Exit fullscreen mode

Step 2: The Magic of dbt (Data Modeling)

This is where the heavy lifting happens. We need to clean the "dirty" data. Garmin might give us "active_kilocalories," while MyFitnessPal gives us "calories_consumed." We need a unified language.

A. Creating Staging Models

In models/staging/stg_garmin_steps.sql, we flatten the JSON and cast types.

-- models/staging/stg_garmin_steps.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'garmin_steps') }}
)
SELECT
    (data->>'calendarDate')::DATE AS activity_date,
    (data->>'totalSteps')::INT AS step_count,
    (data->>'totalDistance')::FLOAT / 1000 AS distance_km,
    (data->>'activeCalories')::FLOAT AS calories_burned
FROM source
Enter fullscreen mode Exit fullscreen mode

B. Building the "Quantified Self" Fact Table

Now, we join our sources into a single "Fact" table. This is what our dashboard will query.

-- models/marts/fct_daily_health_metrics.sql
{{ config(materialized='table') }}

WITH steps AS (
    SELECT * FROM {{ ref('stg_garmin_steps') }}
),
nutrition AS (
    SELECT 
        log_date,
        total_calories,
        protein_grams
    FROM {{ ref('stg_myfitnesspal_logs') }}
)

SELECT
    s.activity_date,
    s.step_count,
    s.calories_burned,
    n.total_calories AS calories_consumed,
    (n.total_calories - s.calories_burned) AS net_energy_balance
FROM steps s
LEFT JOIN nutrition n ON s.activity_date = n.log_date
Enter fullscreen mode Exit fullscreen mode

Step 3: Orchestrating with Airflow

To keep your dashboard fresh, we use Airflow to trigger our dbt models every morning at 6 AM.

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

with DAG('health_data_pipeline', start_date=datetime(2023, 1, 1), schedule_interval='@daily') as dag:

    extract_garmin = BashOperator(
        task_id='extract_garmin',
        bash_command='python /scripts/fetch_garmin.py'
    )

    dbt_run = BashOperator(
        task_id='dbt_run',
        bash_command='cd /dbt_project && dbt run'
    )

    extract_garmin >> dbt_run
Enter fullscreen mode Exit fullscreen mode

Step 4: Visualizing in Apache Superset

Once dbt run completes, your fct_daily_health_metrics table is ready.

  1. Connect Superset to your PostgreSQL instance.
  2. Create a new dataset from the fct_daily_health_metrics table.
  3. Build a Mixed Chart: Bar chart for calories_consumed vs. calories_burned and a line chart for step_count.

The "Official" Way to Scale 🥑

Building a local dashboard is a great start, but when you begin dealing with real-time biometric streams or multi-user environments, you need more robust architectural patterns.

For more production-ready examples and advanced data engineering patterns—like handling late-arriving dimensions in health data or optimizing dbt incremental models—I highly recommend checking out the deep-dive articles at WellAlly Tech Blog. They cover the nuances of building resilient data platforms that go far beyond basic SQL.

Conclusion: Data is the New Vitamin

By centralizing your health data, you move away from "feeling" healthy to "knowing" your health status. You’ve just built a mini-Data Warehouse!

Next Steps:

  • Try adding a "Sleep Score" from an Oura ring API.
  • Use dbt tests to ensure your step_count is never negative.
  • Share your dashboard screenshot in the comments below! 📈

Happy coding, and stay healthy! 🏃‍♂️💻

Top comments (0)