Skip to content
Hands-on IT IT Drills

Day 8: Data – costreport

Goal: build a CLI costreport that analyzes a cloud cost CSV and reports as Markdown · Duration: approx. 120 min · Prerequisite: Day 7

Briefing

  • csv and json from the standard library are enough for most ops data.
  • Never money as float: Decimal for parsing, stored as an int in cents.
  • sqlite3 is a full-fledged SQL database in a file – without a server.
  • polars computes column-oriented and lazily; we build the same analysis twice and have a test check that both compute the same result.
  • Broken lines are counted and reported, not silently swallowed.
kosten.csv --> csv.DictReader --> CostRow --> sqlite3 GROUP BY --+
     |                                                           +--> Summary
     +-----> polars scan_csv --> group_by/agg -------------------+       |
                                                              Markdown / JSON

Drill 1: Project and lab data

cd ~/py-lab
uv init -q --package --name costreport tag08-costreport
cd tag08-costreport && mkdir tests
uv add -q polars typer
uv add -q --dev pytest ruff
cat > gen_data.py <<'EOF'
"""Erzeugt kosten.csv: ein Monat Cloud-Kosten (Lab-Daten, reproduzierbar)."""

import csv
import random
from datetime import date, timedelta

rng = random.Random(42)
SERVICES = {"EC2": 120, "S3": 15, "RDS": 60, "EKS": 45, "CloudWatch": 8, "Lambda": 3}
ACCOUNTS = {
    "prod": ("platform", 1.0),
    "staging": ("platform", 0.3),
    "data": ("data", 0.6),
}

with open("kosten.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["date", "account", "service", "team", "cost_usd"])
    for day in range(30):
        d = date(2026, 9, 1) + timedelta(days=day)
        for account, (team, factor) in ACCOUNTS.items():
            for service, base in SERVICES.items():
                cost = base * factor * rng.uniform(0.8, 1.2)
                if d.day == 17 and service == "EC2" and account == "data":
                    cost *= 6  # vergessener GPU-Cluster
                w.writerow([d.isoformat(), account, service, team, f"{cost:.2f}"])
    w.writerow(["2026-09-30", "prod", "Support", "", "29.00"])  # ohne Team
    w.writerow(["2026-09-30", "prod", "EC2", "platform", "-50.00"])  # Gutschrift
    w.writerow(["2026-09-31", "prod", "S3", "platform", "1.00"])  # kaputtes Datum
    w.writerow(["2026-09-30", "data", "RDS", "data", "n/a"])  # kaputter Betrag
EOF
python3 gen_data.py && wc -l kosten.csv && head -3 kosten.csv && tail -4 kosten.csv

Expected:

     545 kosten.csv
date,account,service,team,cost_usd
2026-09-01,prod,EC2,platform,126.69
2026-09-01,prod,S3,platform,12.15
2026-09-30,prod,Support,,29.00
2026-09-30,prod,EC2,platform,-50.00
2026-09-31,prod,S3,platform,1.00
2026-09-30,data,RDS,data,n/a

Why? Real billing exports (AWS CUR, Azure Cost Export) have exactly these quirks: missing tags, credits, broken values. random.Random(42) makes the data reproducible – your numbers are identical to the ones in the book.

Save your progress? Sign up for free

Drill 2: Reading CSV – with Decimal and cents

uv run python -c 'print(0.1 + 0.2)'

Expected:

0.30000000000000004

Hence: parse the amount as Decimal, store it as whole cents.

cat > src/costreport/__init__.py <<'EOF'
"""costreport – Cloud-Kosten-CSV auswerten."""
EOF
cat > src/costreport/load.py <<'EOF'
"""CSV einlesen und validieren: Geld in Cent als int, nie als float."""

import csv
import logging
from dataclasses import dataclass
from datetime import date
from decimal import Decimal, InvalidOperation
from pathlib import Path

log = logging.getLogger(__name__)


@dataclass(frozen=True, slots=True)
class CostRow:
    day: date
    account: str
    service: str
    team: str
    cents: int


def parse_row(raw: dict[str, str]) -> CostRow:
    try:
        amount = Decimal(raw["cost_usd"])
    except InvalidOperation:
        raise ValueError(f"ungültiger Betrag {raw['cost_usd']!r}") from None
    return CostRow(
        day=date.fromisoformat(raw["date"]),
        account=raw["account"].strip(),
        service=raw["service"].strip(),
        team=raw["team"].strip() or "unassigned",
        cents=round(amount * 100),
    )


def read_costs(path: Path) -> tuple[list[CostRow], int]:
    """Gibt gültige Zeilen und die Zahl übersprungener Zeilen zurück."""
    rows: list[CostRow] = []
    skipped = 0
    with path.open(newline="", encoding="utf-8") as f:
        for lineno, raw in enumerate(csv.DictReader(f), start=2):
            try:
                rows.append(parse_row(raw))
            except (ValueError, KeyError) as exc:
                log.warning("Zeile %d übersprungen: %s", lineno, exc)
                skipped += 1
    return rows, skipped
EOF
uv run python - <<'EOF'
import logging
from pathlib import Path

from costreport.load import read_costs

logging.basicConfig(level=logging.WARNING, format="%(levelname)s %(message)s")
rows, skipped = read_costs(Path("kosten.csv"))
print(len(rows), "Zeilen,", skipped, "übersprungen")
print(rows[0])
print(sum(r.cents for r in rows) / 100, "USD")
EOF

Expected:

WARNING Zeile 544 übersprungen: day 31 must be in range 1..30 for month 9 in yea...
WARNING Zeile 545 übersprungen: ungültiger Betrag 'n/a'
542 Zeilen, 2 übersprungen
CostRow(day=datetime.date(2026, 9, 1), account='prod', service='EC2', team='plat...
14775.28 USD

Why? csv.DictReader returns a dict per line with the column names as keys. parse_row turns it into a typed CostRow or raises ValueError – the loop decides what happens with errors. start=2 counts the header line too, so that the line number matches the one in your editor.

Save your progress? Sign up for free

Drill 3: SQL with sqlite3

First the shared result type, then the SQL engine.

cat > src/costreport/summary.py <<'EOF'
"""Ergebnis-Datentyp, den beide Engines (sqlite3, polars) liefern."""

from dataclasses import dataclass


@dataclass(frozen=True)
class Summary:
    rows: int
    total_cents: int
    by_service: list[tuple[str, int]]  # absteigend nach Kosten
    by_team: list[tuple[str, int]]
    peak_day: tuple[str, int]  # (ISO-Datum, Cent)
EOF
cat > src/costreport/store.py <<'EOF'
"""Aggregation mit sqlite3: SQL aus der Standardbibliothek."""

import sqlite3
from collections.abc import Iterable

from costreport.load import CostRow
from costreport.summary import Summary

SCHEMA = """
CREATE TABLE IF NOT EXISTS costs (
    day TEXT NOT NULL, account TEXT NOT NULL, service TEXT NOT NULL,
    team TEXT NOT NULL, cents INTEGER NOT NULL
)
"""


def load_db(rows: Iterable[CostRow], path: str = ":memory:") -> sqlite3.Connection:
    conn = sqlite3.connect(path)
    conn.execute(SCHEMA)
    with conn:  # Transaktion: alles oder nichts
        conn.execute("DELETE FROM costs")
        conn.executemany(
            "INSERT INTO costs VALUES (?, ?, ?, ?, ?)",
            ((r.day.isoformat(), r.account, r.service, r.team, r.cents) for r in rows),
        )
    return conn


def group(conn: sqlite3.Connection, column: str, limit: int = -1) -> list[tuple]:
    if column not in {"service", "team", "day"}:  # nie Nutzereingaben ins SQL
        raise ValueError(column)
    sql = (
        f"SELECT {column}, SUM(cents) AS c FROM costs "
        f"GROUP BY {column} ORDER BY c DESC, {column} LIMIT ?"
    )
    return conn.execute(sql, (limit,)).fetchall()


def summarize(conn: sqlite3.Connection, top: int = 5) -> Summary:
    rows, total = conn.execute("SELECT COUNT(*), SUM(cents) FROM costs").fetchone()
    return Summary(
        rows=rows,
        total_cents=total or 0,
        by_service=group(conn, "service", top),
        by_team=group(conn, "team"),
        peak_day=group(conn, "day", 1)[0],
    )
EOF
uv run python - 2>/dev/null <<'EOF'
from pathlib import Path

from costreport.load import read_costs
from costreport.store import group, load_db, summarize

rows, _ = read_costs(Path("kosten.csv"))
conn = load_db(rows, "kosten.db")
print(group(conn, "team"))
print(summarize(conn, top=3))
EOF

Expected (shortened):

[('platform', 978298), ('data', 496330), ('unassigned', 2900)]
Summary(rows=542, total_cents=1477528, by_service=[('EC2', 719637), ('RDS', 34...

Why? Values always go into the SQL as ? parameters, never via f-string – otherwise SQL injection. Column names cannot be parameterized, hence the allowlist in group(). with conn: opens a transaction that is rolled back on an exception.

You can query the file kosten.db directly with the macOS sqlite3 CLI:

sqlite3 -header -column kosten.db "SELECT account, SUM(cents)/100.0 AS usd
  FROM costs GROUP BY account ORDER BY usd DESC"

Expected:

account  usd
-------  -------
prod     7540.87
data     4963.3
staging  2271.11

Save your progress? Sign up for free

Drill 4: The same with polars

cat > src/costreport/frames.py <<'EOF'
"""Dieselbe Auswertung mit polars: spaltenorientiert, lazy, schnell."""

from pathlib import Path

import polars as pl

from costreport.summary import Summary


def read_frame(path: Path) -> pl.LazyFrame:
    return (
        pl.scan_csv(path, schema_overrides={"cost_usd": pl.String, "date": pl.String})
        .with_columns(
            day=pl.col("date").str.to_date(strict=False),
            team=pl.col("team").fill_null("unassigned"),
            cents=(pl.col("cost_usd").cast(pl.Float64, strict=False) * 100)
            .round()
            .cast(pl.Int64),
        )
        .drop_nulls(["day", "cents"])  # kaputte Zeilen raus
        .select("day", "account", "service", "team", "cents")
    )


def count_raw(path: Path) -> int:
    return pl.scan_csv(path).select(pl.len()).collect().item()


def group(lf: pl.LazyFrame, column: str, limit: int | None = None) -> list[tuple]:
    df = (
        lf.group_by(column)
        .agg(pl.col("cents").sum())
        .sort(["cents", column], descending=[True, False])
        .head(limit)
        .collect()
    )
    return [(str(k), int(v)) for k, v in df.iter_rows()]


def summarize(path: Path, top: int = 5) -> Summary:
    lf = read_frame(path)
    rows, total = lf.select(pl.len(), pl.col("cents").sum()).collect().row(0)
    return Summary(
        rows=rows,
        total_cents=total,
        by_service=group(lf, "service", top),
        by_team=group(lf, "team"),
        peak_day=group(lf, "day", 1)[0],
    )
EOF
uv run python - <<'EOF'
from pathlib import Path

from costreport.frames import read_frame, summarize

print(read_frame(Path("kosten.csv")).collect().head(3))
print(summarize(Path("kosten.csv"), top=3))
EOF

Expected (shortened):

shape: (3, 5)
┌────────────┬─────────┬─────────┬──────────┬───────┐
│ day        ┆ account ┆ service ┆ team     ┆ cents │
│ date       ┆ str     ┆ str     ┆ str      ┆ i64   │
╞════════════╪═════════╪═════════╪══════════╪═══════╡
│ 2026-09-01 ┆ prod    ┆ EC2     ┆ platform ┆ 12669 │
...
Summary(rows=542, total_cents=1477528, by_service=[('EC2', 719637), ('RDS', 34...

Why? scan_csv doesn’t read anything right away but builds a plan (LazyFrame); only collect() executes it – polars optimizes in between, e.g. it reads only the columns it needs. strict=False turns broken values into null, drop_nulls throws them out. Same result as SQL – which is exactly what a test will check in a moment.

Warning: Going through Float64 is exact for amounts with two decimal places thanks to round(). With more places (e.g. AWS prices with 10 decimals), use pl.Decimal.

Question sqlite3 polars
Dependency none (standard library) approx. 170 MB installed
Strength persistence, ad-hoc SQL, concurrent access large files, Parquet, fast aggregation
Typical tool state, cache, small inventories billing exports, logs, analyses

Save your progress? Sign up for free

Drill 5: Markdown and JSON

cat > src/costreport/report.py <<'EOF'
"""Summary -> Markdown-Report oder JSON."""

import json

from costreport.summary import Summary


def usd(cents: int) -> str:
    return f"{cents / 100:,.2f} USD"


def table(title: str, items: list[tuple[str, int]], total: int) -> list[str]:
    lines = [f"## {title}", "", f"| {title} | Kosten | Anteil |", "|---|--:|--:|"]
    for name, cents in items:
        lines.append(f"| {name} | {usd(cents)} | {cents / total:.1%} |")
    return [*lines, ""]


def to_markdown(s: Summary, source: str, skipped: int = 0) -> str:
    day, day_cents = s.peak_day
    lines = [
        f"# Kostenreport {source}",
        "",
        f"- Gesamt: **{usd(s.total_cents)}** aus {s.rows} Zeilen",
        f"- Teuerster Tag: {day} mit {usd(day_cents)}",
    ]
    if skipped:
        lines.append(f"- Achtung: {skipped} ungültige Zeilen übersprungen")
    lines.append("")
    lines += table("Service", s.by_service, s.total_cents)
    lines += table("Team", s.by_team, s.total_cents)
    return "\n".join(lines)


def to_json(s: Summary) -> str:
    data = {
        "rows": s.rows,
        "total_cents": s.total_cents,
        "peak_day": dict([s.peak_day]),
        "by_service": dict(s.by_service),
        "by_team": dict(s.by_team),
    }
    return json.dumps(data, indent=2)
EOF

Why? report.py only knows Summary – not CSV, SQL or polars. This makes the report easy to test, and a third engine (e.g. DuckDB) wouldn’t need a single line changed here. {cents / total:.1%} formats directly as a percentage.

Save your progress? Sign up for free

Drill 6: The CLI

cat > src/costreport/cli.py <<'EOF'
"""CLI: costreport kosten.csv [--top 5] [--engine sqlite|polars] [-o report.md]"""

import logging
from enum import StrEnum
from pathlib import Path
from typing import Annotated

import typer

from costreport import frames, store
from costreport.load import read_costs
from costreport.report import to_json, to_markdown

app = typer.Typer(add_completion=False)


class Engine(StrEnum):
    sqlite = "sqlite"
    polars = "polars"


@app.command()
def main(
    csv_file: Annotated[Path, typer.Argument(exists=True, dir_okay=False)],
    top: Annotated[int, typer.Option(min=1, help="Top-N Services")] = 5,
    engine: Annotated[Engine, typer.Option()] = Engine.sqlite,
    out: Annotated[Path | None, typer.Option("-o", help="Report-Datei")] = None,
    as_json: Annotated[bool, typer.Option("--json")] = False,
    db: Annotated[str, typer.Option(help="SQLite-Datei (nur sqlite)")] = ":memory:",
) -> None:
    """Wertet eine Cloud-Kosten-CSV aus und schreibt einen Markdown-Report."""
    logging.basicConfig(level=logging.WARNING, format="%(levelname)s %(message)s")
    if engine is Engine.sqlite:
        rows, skipped = read_costs(csv_file)
        summary = store.summarize(store.load_db(rows, db), top)
    else:
        summary = frames.summarize(csv_file, top)
        skipped = frames.count_raw(csv_file) - summary.rows
    text = to_json(summary) if as_json else to_markdown(summary, csv_file.name, skipped)
    if out:
        out.write_text(text + "\n", encoding="utf-8")
        typer.echo(f"Report geschrieben: {out}", err=True)
    else:
        typer.echo(text)
EOF
cat > src/costreport/__init__.py <<'EOF'
"""costreport – Cloud-Kosten-CSV auswerten."""

from costreport.cli import app


def main() -> None:
    app()
EOF
cat > src/costreport/__main__.py <<'EOF'
from costreport import main

main()
EOF
cat >> pyproject.toml <<'EOF'

[tool.ruff.lint]
extend-select = ["E501", "B", "UP", "SIM"]

[tool.pytest.ini_options]
testpaths = ["tests"]
EOF
uv run costreport kosten.csv --top 2 --engine polars | head -6

Expected:

# Kostenreport kosten.csv

- Gesamt: **14,775.28 USD** aus 542 Zeilen
- Teuerster Tag: 2026-09-17 mit 899.85 USD
- Achtung: 2 ungültige Zeilen übersprungen

Why? StrEnum as the type turns --engine into a choice with validation and help text. exists=True lets typer check the file before your code runs. Logs and “Report geschrieben” (report written) go to stderr so that stdout stays cleanly pipeable.

Save your progress? Sign up for free

Drill 7: Tests – both engines must compute the same

cat > tests/conftest.py <<'EOF'
from pathlib import Path

import pytest

CSV = """\
date,account,service,team,cost_usd
2026-09-01,prod,EC2,platform,0.10
2026-09-01,prod,EC2,platform,0.20
2026-09-01,data,S3,data,5.00
2026-09-02,prod,RDS,,1.50
2026-09-02,prod,EC2,platform,-0.05
2026-09-31,prod,S3,platform,1.00
2026-09-02,data,RDS,data,n/a
"""


@pytest.fixture
def csv_file(tmp_path: Path) -> Path:
    path = tmp_path / "kosten.csv"
    path.write_text(CSV)
    return path
EOF
cat > tests/test_load.py <<'EOF'
from datetime import date

import pytest

from costreport.load import parse_row, read_costs

RAW = {"date": "2026-09-01", "account": "prod", "service": "EC2"}


def test_parse_row_uses_cents_and_default_team():
    row = parse_row(RAW | {"team": " ", "cost_usd": "12.345"})
    assert (row.day, row.team, row.cents) == (date(2026, 9, 1), "unassigned", 1234)


@pytest.mark.parametrize("cost", ["n/a", "", "NaN"])
def test_parse_row_rejects_bad_amounts(cost):
    with pytest.raises(ValueError):
        parse_row(RAW | {"team": "x", "cost_usd": cost})


def test_read_costs_skips_bad_lines(csv_file):
    rows, skipped = read_costs(csv_file)
    assert (len(rows), skipped) == (5, 2)
    assert sum(r.cents for r in rows) == 10 + 20 + 500 + 150 - 5  # kein Float-Fehler
EOF
cat > tests/test_engines.py <<'EOF'
import pytest

from costreport import frames, store
from costreport.load import read_costs


def test_sqlite_and_polars_agree(csv_file):
    rows, _ = read_costs(csv_file)
    via_sql = store.summarize(store.load_db(rows), top=2)
    via_polars = frames.summarize(csv_file, top=2)
    assert via_sql == via_polars
    assert via_sql.by_service == [("S3", 500), ("RDS", 150)]
    assert via_sql.peak_day == ("2026-09-01", 530)


def test_group_rejects_unknown_column(csv_file):
    conn = store.load_db(read_costs(csv_file)[0])
    with pytest.raises(ValueError):
        store.group(conn, "team; DROP TABLE costs")
EOF
cat > tests/test_cli.py <<'EOF'
import json

from typer.testing import CliRunner

from costreport.cli import app


def test_markdown_report_to_file(csv_file, tmp_path):
    out = tmp_path / "report.md"
    result = CliRunner().invoke(app, [str(csv_file), "-o", str(out)])
    assert result.exit_code == 0
    text = out.read_text()
    assert text.startswith("# Kostenreport kosten.csv")
    assert "2 ungültige Zeilen" in text
    assert "| unassigned | 1.50 USD | 22.2% |" in text


def test_json_with_polars(csv_file):
    result = CliRunner().invoke(app, [str(csv_file), "--engine", "polars", "--json"])
    assert json.loads(result.stdout)["total_cents"] == 675
EOF
uv run pytest -q && uv run ruff check . && uv run ruff format --check .

Expected:

.........                                                                [100%]
9 passed in 0.09s
All checks passed!
14 files already formatted

Why? test_sqlite_and_polars_agree compares two independent implementations – if one of them has a rounding or filtering error, the test turns red. The mini CSV in conftest.py contains every quirk of the real data, and tmp_path makes sure no test writes into the project folder.

Save your progress? Sign up for free

Drill 8: The tool is done

uv run costreport kosten.csv -o report.md --db kosten.db
head -12 report.md
uv run costreport kosten.csv --engine polars --json 2>/dev/null | jq '.by_team'
sqlite3 kosten.db "SELECT day, SUM(cents)/100.0 FROM costs
  WHERE service='EC2' AND account='data' GROUP BY day ORDER BY 2 DESC LIMIT 2"

Expected:

WARNING Zeile 544 übersprungen: day 31 must be in range 1..30 for month 9 in yea...
WARNING Zeile 545 übersprungen: ungültiger Betrag 'n/a'
Report geschrieben: report.md
# Kostenreport kosten.csv

- Gesamt: **14,775.28 USD** aus 542 Zeilen
- Teuerster Tag: 2026-09-17 mit 899.85 USD
- Achtung: 2 ungültige Zeilen übersprungen

## Service

| Service | Kosten | Anteil |
|---|--:|--:|
| EC2 | 7,196.37 USD | 48.7% |
| RDS | 3,483.11 USD | 23.6% |
{
  "platform": 978298,
  "data": 496330,
  "unassigned": 2900
}
2026-09-17|501.8
2026-09-20|85.55

The report shows the most expensive day, SQL finds the cause: EC2 in the data account on the 17th – the forgotten GPU cluster from gen_data.py. You can copy report.md straight into a ticket or a merge request.

Save your progress? Sign up for free

Checkpoint

#!/usr/bin/env bash
check() { if eval "$2" >/dev/null 2>&1; then echo "✔ $1"; else echo "✘ $1"; fi; }
cd ~/py-lab/tag08-costreport
check "Tests grün" "uv run pytest -q"
check "ruff sauber" "uv run ruff check . && uv run ruff format --check ."
check "Report hat Summe" "grep -q '14,775.28 USD' report.md"
check "Engines gleich" "diff <(uv run costreport kosten.csv --json) \
  <(uv run costreport kosten.csv --json --engine polars)"
check "SQLite-Datei befüllt" \
  "test \$(sqlite3 kosten.db 'SELECT COUNT(*) FROM costs') = 542"
check "kein float für Geld" "! grep -n 'float(' src/costreport/load.py"

Expected:

✔ Tests grün
✔ ruff sauber
✔ Report hat Summe
✔ Engines gleich
✔ SQLite-Datei befüllt
✔ kein float für Geld

Challenge

The report should automatically flag conspicuous days: all days whose costs exceed 1.5 times the daily median.

Hint: In frames.py, first group by day, then daily["cents"].median() and filter().

Solution

Append to src/costreport/frames.py:

def spikes(path: Path, factor: float = 1.5) -> list[tuple[str, int]]:
    """Tage, deren Kosten über factor x Median liegen."""
    daily = read_frame(path).group_by("day").agg(pl.col("cents").sum()).collect()
    limit = factor * daily["cents"].median()
    hits = daily.filter(pl.col("cents") > limit).sort("day")
    return [(str(d), int(c)) for d, c in hits.iter_rows()]

In src/costreport/cli.py, add usd to the import and insert before if out::

from costreport.report import to_json, to_markdown, usd
...
    if not as_json:
        text += "\n## Auffällige Tage (> 1,5 x Median)\n"
        for day, cents in frames.spikes(csv_file):
            text += f"\n- {day}: {usd(cents)}"

Append a test to tests/test_engines.py:

def test_spikes(csv_file):
    assert frames.spikes(csv_file) == [("2026-09-01", 530)]
uv run pytest -q && uv run costreport kosten.csv 2>/dev/null | tail -3

Expected:

..........                                                               [100%]
10 passed in 0.17s
## Auffällige Tage (> 1,5 x Median)

- 2026-09-17: 899.85 USD

Save your progress? Sign up for free

Enterprise Note

  • Real billing exports (AWS CUR 2.0, Azure, GCP BigQuery export) are Parquet and gigabytes in size: pl.scan_parquet() instead of CSV, stay lazy, read only the columns you need.
  • Track currencies and exchange rates explicitly; never add up amounts in different currencies.
  • Cost allocation lives on tags: report unassigned as a metric of its own and enforce mandatory tags via policy (SCP, Azure Policy, Kyverno).
  • SQLite is perfect for a tool, but not for concurrent writers over network drives; for shared data use PostgreSQL or a warehouse.
  • Reports with cost data can be confidential (contract prices, discounts) – clarify where they are stored and who receives them.