Appendix J: Cost Reference and Estimation Worksheets
Every dollar figure in this book comes from the rate card below. It is frozen so that the arithmetic is checkable; it is not a quote.
💸 Prices change and the method does not. Substitute your own rates into the worksheets. The worksheets are the appendix; the numbers are an example.
J.1 The Frozen Rate Card
# platform/cost/rates.yml -- ONE source of truth (ch33 section 33.10)
compute:
node_hour_r6i_8xlarge: 2.400 # USD per node-hour
storage:
s3_standard_gb_month: 0.023
requests:
get_per_1000: 0.0004
put_per_1000: 0.005
network:
egress_gb: 0.09
warehouse:
snowflake_credit: 2.00
medium_credits_per_hour: 4 # X-Small 1, Small 2, Medium 4, Large 8, X-Large 16
scan:
bigquery_per_tib: 6.25
streaming:
kafka_m5_large_broker_hour: 0.21 # plus $0.10/GB-month of topic storage
Four properties of this file matter more than its contents:
It is in the repository, so a rate change is a reviewable commit rather than a spreadsheet edit.
Every estimate imports it. cost_model.py, the PR cost bot, and the capacity model all read the same
YAML, so no two artifacts can disagree.
It carries a date. A rate card without one is a rumor within a year.
And warehouse sizes double. X-Small 1 credit/hour, Small 2, Medium 4, Large 8, X-Large 16 — so "bump the warehouse one size" is always "double the price," and it is worth saying that way.
J.2 Units, Because They Cost Money
| Written | Means | Bytes |
|---|---|---|
| GB (storage billing) | gibibyte | 1,073,741,824 |
| TiB (scan billing) | tebibyte | 1,099,511,627,776 |
| TB (this book's prose) | 1,000 GB | ~1.07 × 10¹² |
The industry writes GB and bills GiB. S3, GCS, and Blob all do it. BigQuery is explicit and
bills TiB. Nobody will correct you and the difference is real.
This book's convention, stated once so you can check any figure:
1 TiB = 1,024 GB
4,190 GB -> "4.19 TB" in prose
-> 4.09 TiB on a scan meter <- a 2.4% difference
Where it bites: a query estimated at "4 TB scanned" costs $25.00 if you meant TiB and $22.75 if you meant decimal terabytes. At Kestrel's volumes that is a rounding error. At a petabyte it is a salary.
J.3 Worksheet 1 — Node-Hours
For EMR, Dataproc, self-managed Spark, and anything billed per machine per hour.
nodes x wall-clock hours x $/node-hour = cost per run
cost per run x runs per period = cost per period
Worked, on Kestrel's nightly sessionization (ch1, ch33):
BROKEN 160 nodes x 10.0 h x $2.400 = $3,840.00 / night = $1,401,600.00 / yr
FIXED 24 nodes x 1.3 h x $2.400 = $74.88 / night = $27,331.20 / yr
────────
saving $3,765.12 / night = $1,374,268.80 / yr
ratio 51.3x
Three traps, in the order people fall into them:
Wall clock, not CPU time. A cluster of 160 machines idle for nine of its ten hours bills ten hours. Autoscaling changes the wall clock and not the bill (ch36 CS2) — it removes the latency signal you were unknowingly using to notice a cost regression.
The cluster spin-up counts. Three to six minutes on every run, and on a job that runs hourly that is 5–10% of the bill.
Instance families are not interchangeable. A memory-optimized node at 2× the price that finishes in 0.4× the time is cheaper; one that finishes in 0.6× the time is not. The only way to know is to run both, once.
🧪 Try It — price your own worst job
Open your orchestrator, sort last month's runs by duration, take the top one, and multiply. Most people have never done this for their own pipeline, and the number is usually larger than their guess by more than 5×.
J.4 Worksheet 2 — Credit-Seconds
For Snowflake, Databricks SQL, and anything billed per unit of compute per unit of time.
credits/hour (by size) x hours RUNNING x $/credit = cost
"Hours running" is not "hours querying," and that gap is the entire optimization.
Medium warehouse, 4 credits/h at $2.00 = $8.00/hour running
auto-suspend at 60s, queried 3 h/day : 4 x 3 x 30 x $2.00 = $720.00 / month
auto-suspend DISABLED, up 24 h/day : 4 x 24 x 30 x $2.00 = $5,760.00 / month
─────────
the cost of one unchecked setting $5,040.00 / month
$60,480.00 / year
Kestrel's REPORTING_WH was the second case for eleven months (ch33 §33.7). Fixing it took under a
minute and saved eight times what deleting every stored byte would have.
The minimum billing increment. Most platforms charge a 60-second minimum on resume, then per second. A warehouse resumed 400 times a day for 5-second queries bills 400 minutes, not 33. Under about 90 seconds of idle, staying up is cheaper than suspending — which is why auto-suspend should be set to roughly 60 seconds and not to 5.
Multi-cluster warehouses multiply. A Medium that scales out to four clusters is a Medium price times
four while it does. Check MAX_CLUSTER_COUNT before you check the query.
J.5 Worksheet 3 — Bytes Scanned
For BigQuery on demand, Athena, and Redshift Spectrum.
bytes scanned / 2^40 x $/TiB = cost per query
Worked, on the two Chapter 1 plans (ch33 §33.3):
BROKEN, full scan 4,300.0 GB -> 4.199 TiB x $6.25 = $26.25
FIXED, pruned 34.0 GB -> 0.033 TiB x $6.25 = $0.21
─────
126.5x
What is and is not free on this meter:
| Free | Charged |
|---|---|
LIMIT (does not reduce the scan) |
every byte of every column touched |
WHERE on a partition column |
WHERE on a non-partition column |
| CPU, joins, sorts, window functions | the scan that feeds them |
| cached results (24 h, exact text match) | the first run |
SELECT COUNT(*) on some engines |
SELECT * — always |
LIMIT 10 on a 4 TiB table costs $25.00. This is the single most expensive misconception in
analytics, and it is nearly universal among people who learned SQL on Postgres.
Slots versus on demand. On demand has no floor and no ceiling; committed slots have both. The crossover is roughly 200 TiB scanned a month — $1,250 on demand at $6.25/TiB — and it is worth computing rather than assuming, in both directions.
📐 Design Decision — the estimate that stopped a query
§33.6's dry run said 4,290 GB — 4.19 TiB — per run at daily refresh: $26.19 a run, $9,558 a year, for a question asked twice a quarter.
The replacement cost $0.21 a run, on demand: $1.68 a year, or 0.02% as much. Nothing was optimized. The query was not built.
J.6 Worksheet 4 — Storage
average GB stored over the month x $/GB-month = cost
"Average" is the word that matters: an object created on the 15th costs half a month, and versioning plus soft-delete means the bytes you think you removed are frequently still billing.
Worked, on Kestrel's 27.4 TiB (ch33 §33.4):
28,033 GB x $0.023 = $644.76 / month = $7,737.12 / year = 2.1% of the bill
The lifecycle ladder, with the caveat that decides it:
| Class | ~$/GB-mo | Retrieval | Minimum duration | Use when |
|---|---|---|---|---|
| Standard | 0.023 | free, instant | none | queried this quarter |
| Infrequent Access | 0.0125 | $0.01/GB | 30 days | queried a few times a year |
| Glacier Instant | 0.004 | $0.03/GB | 90 days | compliance, occasionally read |
| Glacier Deep Archive | 0.00099 | $0.02/GB, 12 h | 180 days | legal hold, never read |
The minimum-duration charge is what surprises people. An object moved to Deep Archive and deleted after 30 days bills 180 days. Lifecycle rules on short-lived data can cost more than no rules at all, and this is a real, common own-goal.
And the honest framing (ch33 §33.4): deleting every byte Kestrel stores saves $644.76 a month. Storage optimization is popular because it is easy, satisfying, and upsets nobody — and compute plus warehouse is $27,801.60 of the same bill.
The exception worth knowing: compaction and partitioning are storage changes with compute payoffs, and they are justified on the compute side.
J.7 Worksheet 5 — Requests and Egress
GETs / 1,000 x $0.0004 + PUTs / 1,000 x $0.005
GB leaving the region/cloud x $0.09
Requests are invisible until small files make them enormous.
Kestrel, one month
603,500,000 GET -> $241.40 of which 444,000,000 are small-file
22,000,000 PUT -> $110.00 amplification: $177.60
─────── $2,131.20 / year
$351.40
Egress is the line nobody budgets:
18,400 GB cross-region replication -> $1,656.00 / month <- 96.6% of it
640 GB partner extracts -> $57.60
───────── ──────────
19,040 GB $1,713.60 / month
Three shapes that generate egress you did not ask for:
Cross-AZ traffic inside a region, about $0.01/GB each way on AWS. A three-AZ Kafka cluster pays it on every replica, which is why an MSK bill exceeds its broker-hours.
NAT gateway, ~$0.045/hour plus ~$0.045/GB processed. A private-subnet job pulling from the internet pays twice.
Cross-region reads that look local. A query in us-east-1 against a bucket in us-west-2 is egress
on every run, and nothing in the SQL says so.
J.8 Kestrel's Bill, Decomposed
Every line below reduces to the rate card. This is the table cost_model.py --bill prints, expanded.
COMPUTE node-h $/month
hourly ingest cluster 4 x 18 h x 30 2,160 5,184.00
dev cluster (never stopped) 3 x 720 h 2,160 5,184.00
streaming consumers 2 x 720 h 1,440 3,456.00
nightly sessionization 24 x 1.3 h x 30 936 2,246.40
ML materialization 12 x 2.2 h x 30 792 1,900.80
ad-hoc and notebooks 376 902.40
────── ──────────
7,864 18,873.60 61.9%
WAREHOUSE credits $/month
REPORTING_WH Medium, 24 h x 30 2,880 5,760.00
ML_WH Large, 2.7 h x 30 648 1,296.00
ADHOC_WH Small, 8 h x 30 480 960.00
TRANSFORM_WH Medium, 3.8 h x 30 456 912.00
────── ──────────
4,464 8,928.00 29.3%
EGRESS 19,040 GB x $0.09 1,713.60 5.6%
STORAGE 28,033 GB x $0.023 644.76 2.1%
REQUESTS 603.5M GET + 22.0M PUT 351.40 1.2%
──────────
TOTAL 30,511.36
$366,136.32 / yr
Read the compute block top-down and the first two lines are the finding: the dev cluster costs the same as the entire hourly ingest cluster, and more than twice the nightly job the whole platform depends on.
Compute plus warehouse is $27,801.60 — 91.1%.
J.9 The Seven Wastes, With Their Arithmetic
waste.py (ch33 §33.7). Every row is one multiplication, and that is the point.
| Pattern | Arithmetic | $/month | $/year | Weeks |
|---|---|---:|---:|---:|
| Auto-suspend disabled | 4 cr × 21 idle h × 30 × $2.00 | 5,040.00 | 60,480.00 | 0.02 |
| Dev cluster never stopped | 3 × (720 − 176) h × $2.400 | 3,916.80 | 47,001.60 | 0.20 |
| Over-frequent dashboard refresh | 8,640 refreshes where 60 suffice | 2,059.20 | 24,710.40 | 0.10 |
| Cross-region egress | 18,400 GB × $0.09 | 1,656.00 | 19,872.00 | 1.00 |
| Raw JSON retained past need | lifecycle rule that was never written | 216.83 | 2,601.99 | 0.50 |
| Small-file GET amplification | 444,000,000 ÷ 1,000 × $0.0004 | 177.60 | 2,131.20 | 2.00 |
| Orphaned storage | buckets whose owners left | 158.70 | 1,904.40 | 1.00 |
| Total | | 13,225.13 | 158,701.56 | |
43.3% of the bill, and 83.3% of it is idle rather than inefficient — machines and warehouses that are up and doing nothing.
That ratio is the appendix's most transferable claim. Most cost work is not optimization. It is turning things off, and the top three rows take under a day between them.
Rank by $/year ÷ weeks, not by dollars:
auto-suspend disabled $60,480 / 0.02 wk = $3,024,000 per week of work
dev cluster $47,002 / 0.20 wk = $235,008
dashboard refresh $24,710 / 0.10 wk = $247,104
cross-region egress $19,872 / 1.00 wk = $19,872
small-file amplification $2,131 / 2.00 wk = $1,066
The last row is the one engineers most want to do, and it is worth 1/2,800th of the first.
J.10 Unit Economics
A total is not comparable to anything. A unit cost is.
platform cost $30,511.36 / month $366,136.32 / year
orders 200,000 / month 2,400,000 / year
GMV $182,000,000 / year
cost per order $0.1526
cost per $1,000 of GMV $2.01
platform as % of GMV 0.201%
Pick the denominator your business already uses. Finance thinks in GMV; operations thinks in orders; nobody thinks in node-hours. A number in their unit gets discussed; a number in yours gets filed.
Three unit costs worth tracking, and one worth ignoring:
| Track | Because |
|---|---|
| cost per order | it should fall as you grow; if it rises, find out why this quarter |
| cost per active pipeline | it catches pipelines nobody has retired |
| cost per team | it is the only number that makes the conversation happen (§33.5) |
| ~~cost per GB stored~~ | 2.1% of the bill; it will mislead you about where to work |
J.11 Growth: Fixed and Variable
"If we double, does the bill double?" is unanswerable from a total and easy from a split.
Kestrel: ~64% variable (scans, scheduled compute, egress, storage)
~36% fixed (orchestrator, dev, streaming, the always-on warehouse)
2x volume -> 0.36 + (0.64 x 2) = 1.64x the bill = $50,038.63 / month
-> unit cost x 1.64/2 = 0.82 = 18% cheaper per order
Two consequences that are not obvious:
Growth improves unit economics automatically, so a rising total bill during a growth quarter is not evidence of a problem. Report the unit cost alongside it or the conversation goes badly.
And the fixed share is where cost work has permanent leverage. A variable saving scales away as you grow; turning off the always-on warehouse is a saving that stays saved.
J.12 Rounding, and When It Matters
Compute from the exact figure and round for display. Never annualize a displayed number.
4.19 TiB x $6.25 = $26.1875 exact
-> $26.19 displayed
x 365, from the exact = $9,558.44 correct
x 365, from the displayed = $9,559.35 wrong by $0.91
$0.91 does not matter and the habit does. The same mistake at Kestrel's scale — annualizing a rounded per-night figure across 365 nights and 40 pipelines — drifts by hundreds of dollars and destroys the reconciliation that makes anyone trust the model (ch38).
Two rules that prevent every version of this:
Money in integer cents, everywhere, converted to dollars only for display. Floating-point dollars accumulate error that looks exactly like a real discrepancy (ch18, ch38).
And carry the units in the variable name. revenue_cents, scan_gb, node_hours. cost is a
variable that will eventually hold the wrong thing.
J.13 Commitments and Discounts
| Mechanism | Discount | Commitment | Risk |
|---|---|---|---|
| Spot / preemptible | 60–90% | none | interruption, any time |
| Savings Plans / CUDs | 20–40% | 1–3 years of spend | you shrink or migrate |
| Reserved instances | 30–60% | 1–3 years of a shape | the instance family dates |
| Warehouse capacity | 10–30% | annual credits | unused credits expire |
Spot is the one to reach for first, and the rule is simple: spot for anything replayable, on-demand for anything on the critical path. Kestrel runs sessionization on spot with an on-demand driver and a fallback, and Chapter 24's retry logic is what makes that safe.
The trap in the other three is the same trap. A commitment converts a variable cost into a fixed one (§J.11), which is precisely the cost you have the least leverage over afterwards. Commit to the floor of your usage, never the average, and re-derive that floor from the last twelve months rather than from the plan.
And read the bill in three forms before you commit: unblended (what each line actually cost), blended (averaged across an organization, and misleading for a single team), and amortized (up-front fees spread over the term, which is the only one that compares a commitment to on-demand honestly).
J.14 The Pre-Flight Checklist
Before shipping anything that runs on a schedule:
[ ] Which meter does this bill on? bytes / seconds / node-hours
[ ] What is the cost of ONE run? from the worksheet, not a guess
[ ] How many runs per year? hourly = 8,760; nightly = 365
[ ] What is the annual figure? this is the number you report
[ ] Does it scale with volume or with time? the section J.11 question
[ ] What turns it off? auto-suspend, TTL, an owner
[ ] Who pays, by tag? untagged is unattributed is waste
And two questions that have saved more money than any optimization in this book:
"How often is this actually read?" §33.6's query was refreshed daily and consulted twice a quarter.
"What happens if we don't build it?" The cheapest pipeline is the one that answers a question nobody was asking, and never gets written.
J.15 Numbers Worth Remembering
one node-hour, r6i.8xlarge $2.400
a Medium warehouse, one hour running $8.00
1 TiB scanned $6.25
1 GB stored, one month $0.023
1 GB out of the region $0.09
a warehouse left up 24/7 instead of 3 h $5,040 / month
one broken partition predicate $3,765 / night, $1,374,269 / year
rebuilding Kestrel's entire platform $198.96
storing every byte Kestrel has $644.76 / month
And the two ratios that end most arguments:
Compute plus warehouse is 91.1% of the bill; storage is 2.1% — a factor of more than 43, with the effort spent very nearly inverted.
43.3% of the bill was waste, and 83.3% of the waste was idle — not slow, not inefficient, just switched on with nobody using it.