Jacob Wiseberg

I wanted a running record of how much disk, bandwidth, and compute my own homelab actually uses over time instead of guessing from whatever a dashboard shows me in the moment. That turned into a small, genuinely useful excuse to build an ETL pipeline just for myself.

Why not just use a monitoring tool

There are plenty of off-the-shelf options for this, and I actually tried a couple first. They were all either too heavy for a single small box or modeled around metrics I didn’t care about while burying the two or three numbers I actually wanted. I didn’t need alerting or fancy dashboards; I wanted a clean table I could query myself and eventually chart.

Extract

The extraction side is deliberately boring: small scripts that read from whatever source has the data and write it out as plain JSON lines.

import psutil, json, time

def extract_system_stats():
    return {
        "timestamp": time.time(),
        "cpu_percent": psutil.cpu_percent(interval=1),
        "disk_used_gb": psutil.disk_usage('/').used / 1e9,
        "net_sent_mb": psutil.net_io_counters().bytes_sent / 1e6,
    }

with open("raw/system_stats.jsonl", "a") as f:
    f.write(json.dumps(extract_system_stats()) + "\n")

A cron job runs this every five minutes. Nothing clever, which is the point — the extraction layer should be the most boring part of any pipeline, because it’s the part that runs constantly and can’t afford to be fragile.

Transform

The transform step is where the actual thinking happens: deduplicating, computing deltas instead of raw cumulative counters, and resampling down to hourly averages so months of data don’t balloon out of control.

import pandas as pd

df = pd.read_json("raw/system_stats.jsonl", lines=True)
df["timestamp"] = pd.to_datetime(df["timestamp"], unit="s")
hourly = df.set_index("timestamp").resample("1h").mean()
hourly["net_sent_delta_mb"] = hourly["net_sent_mb"].diff().clip(lower=0)

That .clip(lower=0) line exists because of a bug I hit in week one: counters reset on reboot, and a naive diff produced enormous negative spikes that threw off every chart until I noticed and handled it explicitly.

Load

For something this size, a single SQLite file was the right call over standing up a real database server:

import sqlite3

conn = sqlite3.connect("homelab_stats.db")
hourly.to_sql("system_stats", conn, if_exists="append", index=True)

No server to maintain, trivially backed up by copying one file, and fast enough for years of hourly data at this volume.

What the exercise actually taught me

None of the individual pieces were hard. What I hadn’t appreciated before building this myself is how much of real ETL work is edge-case handling rather than the core transform logic: counter resets, missing data points when a script fails to run, timezone handling when resampling, deciding what “append” should mean when a batch gets rerun after a failure. The happy path is five lines. The pipeline that survives contact with reality is the other ninety.

I’ve got a small dashboard against the SQLite file now, mostly for my own curiosity about whether my habits around backups and media processing are actually efficient. The bigger win was finally having hands-on intuition for a category of problem I’d only ever seen from the consuming side before.