The Bill Nobody Read

A second-hand marketplace runs a BI tile called Live GMV today — one euro figure, top-left of the trading dashboard, returns one row, renders in two seconds. On 21 July someone changed the dashboard’s auto-refresh from on load to every 5 minutes. No deploy, no migration, no review — a dropdown.

On 30 August finance forwarded the warehouse invoice: $4,665.37 for 1–30 August, against $408.78 in June. Time to detection: 40 days.

Band test on daily spend: pre-change (1–20 Jul) range \$12.78–\$16.36/day, mean \$14.83. Post-change mean \$152.99/day — 0/41 days inside the band, a 10.3× step.

You are billed for bytes scanned — not rows returned, not seconds elapsed, not question difficulty. The tile returned one row and was billed for 96,623,272,675 bytes per refresh ($0.5492 per look at a number).

cost = bytes_scanned_per_run  ×  runs_per_period  ×  price_per_byte
       └ layout ┘                └ demand ┘          └ contract ┘

Root cause 1 — the partition key was the loader’s clock

The table was PARTITION BY DATE(ingested_at) (when the pipeline wrote the row); every query filters event_ts (when the thing happened). No predicate on event_ts can prove an ingested_at partition is irrelevant, so the engine reads all 364 partitions, every time.

This is Lesson 04’s logical-run-date rule relocated into the storage layout: partition by the column readers filter on — event time — not by the loader’s wall clock.

Root cause 2 — the BI tool’s SELECT *

The tool wraps user SQL in SELECT * FROM (...). Of 443 bytes per row, user_agent alone is 98 (22% of every scan) and no dashboard renders it. Lesson 01’s columnar-storage advantage, given straight back.

ml_listing_features and dash_tile_live_gmv read an identical 89.99 GiB per run. One runs 30×/month ($15.78, 0.34%); the other 8,640× ($4,546.03, 97.47%). An expensive query is not a slow query. Frequency is a multiplier; duration is not. And frequency is set outside engineering — BI dropdowns, Slack alerts, polling apps — so it never appears in a pull request.

Two honest asterisks: – BigQuery bills a 10 MiB minimum per table, per query, so the true saving is 9,215×, not 29,281×. Know your platform’s floor. – Clustering prunes blocks, not rows. Purchases are 2.10% of rows but land in ~7.5% of blocks. And --dry_run reports the unclustered upper bound — pessimistic on clustered tables, exact on partitioned ones. Estimate with dry run; measure with total_bytes_billed.

August bill: as built $4,664.23 → turn off the ML job $4,648.45 → repartition + cluster + name columns $118.72 (39.3× cheaper, refresh interval untouched). Annualised: \$55,840 → \$1,444.

Also: storage was $1.14 — 0.02% of the bill. Storage is never the problem.

Before the change, at 24 runs/day, the tile cost $11.25 of a $14.83 daily bill = 75.9% — essentially all of it waste. The bill did not break on 21 July. It had been three-quarters waste for eleven months; the refresh change merely raised it above the threshold at which somebody reads the invoice. The question for the team is not who changed the dropdown — it is what else is quietly 75% waste, just under the threshold?

Why nothing caught it for 40 days

• It never failed → the DAG stayed green (Lesson 07: dependency success ≠ data success).
• It was fast → no latency alert.
• The output was correct → every Lesson 06 quality test passed.
• Cost is measured monthly, in arrears, by a different department, unattributed per query.

Cost is a data-quality dimension you have no test for. Give it an owner. On BigQuery: INFORMATION_SCHEMA.JOBS_BY_PROJECT, group by query, sort by SUM(total_bytes_billed). Snowflake: ACCOUNT_USAGE.QUERY_HISTORY. Redshift: SYS_QUERY_HISTORY.

Ingestion-time partitioning queried on event time · partition column wrapped in a function · partition bound from a subquery · over-partitioning (hourly = past the 10,000-partition ceiling) · clustering a column nobody filters · cluster keys used out of prefix order · no require_partition_filter. The last one plus a project-level maximum_bytes_billed are the two cheapest pieces of governance in a data platform — Lesson 08’s “enforce at the boundary”, applied to cost.

1. Don’t scan the raw table at all — pre-aggregated table / materialised view. 100–10,000×. Needs nobody’s permission.
2. Bytes per run — partition, cluster, name columns, bound dates. 10–2,000×. A DDL change.
3. Runs per period — refresh intervals, caching, deduplicating dashboards. 2–20×. Needs a meeting.
4. Price per byte — reservations vs on-demand. 1.2–3×. Needs finance. Do this last, or you buy capacity to run waste faster.

bytes scanned · partition pruning · clustering (block-level) · column pruning · ingestion time vs event time · require_partition_filter · maximum_bytes_billed · cost attribution

All figures produced by bill.py, shipped in full as an appendix inside the artifact. No RNG, no seed — deterministic modular arithmetic, so a reader’s run reproduces every number to the cent. Verified by extracting the source back out of the rendered HTML and diffing the output against the published figures (zero differing keys).