Day 8: Data – costreport
Goal: build a CLI
costreportthat analyzes a cloud cost CSV and reports as Markdown · Duration: approx. 120 min · Prerequisite: Day 7
Briefing
csvandjsonfrom the standard library are enough for most ops data.- Never money as
float:Decimalfor parsing, stored as anintin cents. sqlite3is a full-fledged SQL database in a file – without a server.polarscomputes 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.csvExpected:
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")
EOFExpected:
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))
EOFExpected (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))
EOFExpected (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
Float64is exact for amounts with two decimal places thanks toround(). With more places (e.g. AWS prices with 10 decimals), usepl.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)
EOFWhy? 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 -6Expected:
# 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 -3Expected:
.......... [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
unassignedas 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.