Move the computation to the data, and measure what it saves

short · 30 min · Objective 1.3

Task

Answer a question -- orders per month in 2025 -- two ways: by copying all the raw rows out of the database and counting them yourself, and by asking the database to count and returning only the answer. Compare how much data each approach moves.

Steps

  1. Export every row of the orders table from data/shop.db to lab/where/raw-export.csv, as a download of the whole table would.
  2. Run SELECT substr(order_date, 1, 7) AS month, COUNT(*) AS orders FROM orders GROUP BY month ORDER BY month and save the result to lab/where/monthly.csv with header month,orders.
  3. Record the size in bytes of both files in lab/where/sizes.txt as raw: N and summary: N.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

awk -F, 'NR>1 {n++; s+=$2} END {print n" months, "s" orders"}' lab/where/monthly.csv
grep -E '^(raw|summary): [0-9]+$' lab/where/sizes.txt
awk -F': ' '/^raw/ {r=$2} /^summary/ {s=$2} END {printf "summary is %.1f%% of the raw export%s", 100*s/r, ORS}' lab/where/sizes.txt

Twelve months totalling 2000 orders, and a summary under 1% of the size of the raw export. On a laptop the difference is trivial; in a cloud warehouse charged by the byte moved out, it is the difference between a small bill and a large one.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.