"The Bill Always Comes" — Cost and Performance Engineering

Skim Card

One thing to remember: a data bill is bytes, requests and idle hours, so meter them per query, run and table before you resize anything.

• Price the same metered month two ways: per byte it was USD 1,032; a 16-vCPU cluster would sit busy 2.1% of its hours.
• One dashboard tile was 94.5% of the per-byte bill (an upper bound): a function on event_ts made Trino read the whole fact.
• Full rebuilds and full-table tests cost more every night as history grows: 90% of the daily run’s bytes.
• Scope tests to the run’s days and keep a weekly full test: a scoped grain test can’t see a duplicate whose twin is older.
• A catalog drop is not a delete: 11.73 GiB, 43% of the buckets, sat in folders no table owned.
• Report unit costs (per run, per view, per live GB) as FOCUS-shaped rows with tags, so they join the provider’s bill.

Use …	When …
Per-byte pricing (Athena, BigQuery on-demand)	Spiky, small, well-pruned queries; cap bytes per query
Per-second warehouses with auto-suspend	Bursty, business-hours load; mind the minimum per resume
Always-on self-hosted cluster	Busy most hours, or the queue matters more than the bill

Verified against: Trino 484, Apache Iceberg 1.12, dbt-core 1.12.6 + dbt-trino 1.11.0, SeaweedFS 4.51, FOCUS 1.4, AWS Price List (us-east-1) · vendor docs · 29 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Farah’s budget form had one data-platform line: “2027 run cost per month, and per report.”

“Sixteen vCPUs, always on, times the hourly rate,” Rohan said. “Storage is pennies.”

“That’s the price of the box,” Ingrid said. “The bill is what the platform does with it: bytes it scans, bytes it keeps, requests, idle hours. Meter those first. Then find out who’s spending them.”

The Quick Map

Concepts, with use cases

Cost drivers, by architecture

A cost driver is the metered quantity a bill multiplies by a price. Data platforms have five: scan bytes (per-byte engines such as Athena and BigQuery on-demand), compute time (vCPU-, credit- or slot-seconds, idle included until something suspends), requests (per GET, PUT and LIST on object storage), storage (GB-months, cheaper in colder tiers that charge minimum durations and retrieval fees) and egress (bytes leaving a region or cloud). Which one dominates depends on the architecture, not the work: the same queries are a scan bill on Athena and an idle-hours bill on an always-on cluster.

What breaks without it. You optimise the wrong meter: a smaller cluster doesn’t help a per-byte bill; pruning saves little on an idle cluster. Larkspur: Step 4 prices one month both ways; egress isn’t measured, but three countries mean computing in each data’s region and replicating gold aggregates, not raw events. Elsewhere: a hospital tiers imaging studies untouched for 90 days and pays a fee on the rare re-read; a video platform’s biggest line is egress.

Unit economics and FinOps

FinOps makes engineering teams own cloud spend with finance: visibility (who spent what), allocation (tags, rules for shared costs), optimisation. Unit economics divides spend by a business output: per report, dashboard view, pipeline run or TB stored. Showback shows teams their cost; chargeback bills it.

What breaks without it. “USD 1,000 a month” can’t be judged; “USD 0.027 per store-dashboard view, USD 0.00005 per finance view” can. Shared costs nobody owns grow fastest. Larkspur: Step 4’s unit costs; storage stays shared until a tag assigns it. Elsewhere: a SaaS company tracks infrastructure cost per active customer.

FOCUS: one schema for every bill

The FinOps Open Cost and Usage Specification (FOCUS) is a common schema for billing data. Version 1.4, ratified on 4 June 2026, has four datasets: Cost and Usage, Contract Commitment, and the new Invoice Detail and Billing Period. Its Cost and Usage rows carry four costs (ListCost, list unit price × PricingQuantity; ContractedCost; BilledCost, as invoiced; EffectiveCost, after discounts and commitments), a ChargeCategory (Usage, Purchase, Tax, Credit, Adjustment), a ServiceCategory from a fixed list (Storage, Analytics, Compute…), consumed and pricing quantities with their units, and Tags, a JSON key-value object. Custom columns carry an x_ prefix; 1.4 removed ProviderName for ServiceProviderName. Providers’ exports were at 1.2 or 1.3 on 29 October 2026, several in preview (Landscape).

What breaks without it. Every bill has its own schema, so joining three clouds and an internal showback is bespoke ETL. Larkspur: the model writes FOCUS-shaped rows (a column subset, not a conformant dataset) tagged data_product. Elsewhere: a multi-cloud bank reconciles AWS, Azure and Snowflake invoices in one table, Part 21’s reconciliation applied to the bill.

The levers

A lever is a choice that lowers a driver.

Under the Hood

How each billing shape meters

What lets the planner skip a file

Each data file’s manifest entry carries per-column lower and upper bounds; Trino turns a WHERE clause into a range on a column and skips files whose bounds can’t match. shapes ran the UK store tile with six ways of saying “these seven London days” against gold.fct_sales_line (135 files, one UTC day each), and date(event_ts) once more on the fact alone:

predicate                       read MiB  CPU s
date_key BETWEEN                     6.2   0.04
event_ts range (UTC)                 6.2   0.04
date(event_ts)                     103.4   0.40
date(event_ts AT TIME ZONE)        103.4   0.40
CAST(date_key AS varchar)          103.4   0.23
date_key IN (dim_date subquery)       6.2   0.02
date(event_ts), fact alone           5.5   0.01

Predicates on the stored column pruned (the IN subquery at run time, through dynamic filtering); functions of it read everything, even date(event_ts), which Trino did turn into a range on the fact alone but not in the tile’s join. The UTC range for British Summer Time matches date_key, the local business date the model stores (Part 5). Inside a file Trino reads only the columns a query needs, except that it reads a Parquet file under parquet.small-file-threshold (3 MB) whole (Part 13); on these 0.8 MB files even the session property parquet_small_file_threshold at 0B didn’t change the bytes (Step 5).

Where a lakehouse’s bytes go

Each object falls in one class, each with its own way out:

The last class has no Iceberg procedure: there’s no table to call it on. A drop without purge leaves the files; a purge hands deletion to the catalog’s background tasks, which Part 18 saw run out of S3 connections.

Hands-On

Companion: the every-record-counts folder of the companion-workspaces repository, tag every-record-counts/part-24. Core + Trino (with OPA; queries run as user lab), Parts 16–23’s tables and make dbt-install. SeaweedFS now serves Prometheus metrics, S3 requests by type included, on port 9327 (-metricsPort in compose.yaml).

make up-trino
P="uv run python -m scripts.part24_cost"
$P storage                                                      # Step 1
$P daily --day 2026-10-04; $P reads; $P model                   # Steps 2-4
$P shapes; $P pruning                                           # Step 5
$P incremental --day 2026-10-04                                 # Step 6
$P sweep; $P sweep --apply                                      # Step 7 (the first only lists)
$P model --after                                                # Step 8
uv run pytest -q tests/test_part24.py

Each step meters Trino’s per-query statistics (/v1/query/{id}) and the request counters. Steps 1 and 7 come from the sweep’s run; Steps 2–6 and 8 were re-run afterwards. A test run overwrote the pre-sweep file, so cost/measured/storage-before-sweep.json is the after-sweep classification plus the deleted folders (sweep.json); it reproduces Step 1 exactly.

Step 1: what the bytes are for

storage classifies every object in both buckets (Under the Hood), taking each table’s referenced paths from Trino’s $files, $all_entries, $all_manifests, $snapshots and $metadata_log_entries. Captured before the sweep:

89 tables and 19 views in the catalog; both buckets:
kind                  GiB  share  objects
current              4.77  17.4%    3,028
history              4.37  16.0%    3,352
metadata             0.64   2.3%   20,237
orphan               1.67   6.1%    5,015
owner-less          11.73  42.9%   28,022
other lab files      3.58  13.1%      861
raw zone files       0.58   2.1%    3,458
total               27.34          63,973
of which Larkspur's platform (raw, silver, gold, customer, dev): current 0.90, history 3.25, metadata 0.64, orphan 1.67, owner-less 11.73 GiB
owner-less by namespace: dev 7.87 GiB, gold 2.40 GiB, silver 1.45 GiB

(One line trimmed.) The platform’s tables read 0.90 GiB of the 18.18 GiB its namespaces hold. The model leaves out Season 1’s experiment namespaces.

Step 2: one daily run, metered

daily runs Part 19’s steps for 4 October, collecting every query Trino finishes (polled: the lab’s query.min-expire-age=2m forgets them fast) and charging each dbt statement to the table it writes, or to tests:

step          wall s  Trino CPU s  read MiB  written MiB  queries    GET   PUT  LIST
ingest           1.3          0.1       1.0          0.0        1     43    16     0
dbt build       26.5         20.9   1,288.8          4.0       55   2162    59     0

dbt build, per model:
model                        CPU s  read MiB  written MiB  queries
tests                         10.7     849.3          0.0       11
rpt_daily_net_revenue_v2       4.3     208.2          0.0        2
rpt_daily_net_revenue          3.4     104.8          0.0        2
fct_sales_line                 1.7     113.3          1.9        6

(Checks, publish, two small models and dbt’s own queries trimmed.) The fact is incremental and cheap; both report versions are rebuilt from all history and most tests check every row of fct_sales_line: 1,162 of the build’s 1,289 MiB, a share that grows daily.

Step 3: what people read

reads runs the four tiles in sql/part24/workload.sql three times each (medians):

query                 seconds  CPU s  read MiB  GETs  peak MiB
finance_revenue          0.05   0.00       0.0     1       0.0
uk_store_week            0.45   0.46     103.4   180       1.0
stock_yesterday          0.30   0.04       5.8    12       0.2
conversion_week          0.11   0.04      13.8     6       0.0

The UK store tile reads the whole fact for one week, because it asks for the London date this way:

WHERE s.country = 'GB'
  AND date(f.event_ts AT TIME ZONE 'Europe/London') BETWEEN DATE '2026-09-24' AND DATE '2026-09-30'

Step 4: a month, priced

model scales measured bytes and CPU by 50 (Part 14’s production factor; request counts follow the commit cadence, so stay) and assumes daily tile loads: 1,200 UK store, 300 stock, 60 conversion, 40 finance. Prices (cost/prices.json) are AWS us-east-1 list prices from AWS’s Price List API, checked on 29 October 2026: S3 Standard USD 0.023 per GB-month (first 50 TB), GETs USD 0.004 per 10,000, PUT/COPY/POST/LIST USD 0.005 per 1,000, Athena USD 5.00 per TB scanned, Fargate ARM USD 0.03238 per vCPU-hour and USD 0.00356 per GB-hour. Trino’s physical input is priced at Athena’s rate, as a yardstick:

one month at 50x the lab's volume, as measured; list prices checked cost/prices.json (2026-10-29)
line                               quantity unit              USD  share
scan: uk_store_week                   195.2 TB             976.09  94.5%
storage: owner-less                   629.6 GB-Months       14.48   1.4%
scan: stock_yesterday                   2.7 TB              13.73   1.3%
scan: daily run                         2.0 TB              10.16   1.0%
scan: conversion_week                   1.3 TB               6.53   0.6%
scan: finance_revenue                   0.0 TB               0.06   0.0%
total                                                    1,032.42

the same work as CPU: 243.8 vCPU-hours a month. A 16 vCPU / 64 GB cluster always on: USD 544.52; busy 2.1% of its hours (USD 11.36 if it ran only then)
unit costs: one daily run USD 0.3399; storage per live GB-month USD 0.4809 (list price 0.023)
  one finance_revenue view: USD 0.000050
  one uk_store_week view: USD 0.027185

(Ten lines and two unit costs trimmed.) One tile is the per-byte bill: at 50 loads a day it would still outcost all storage. On a cluster, the bill is idle hours: 16 vCPUs busy 2.1% of the month. Each live GB costs 21 times its list price. The scan figures are upper bounds: bytes scale but file counts don’t, so production’s files would be about 40 MB, above the small-file threshold, and Trino would read only the columns it needs. The ratio holds; the 94.5% is inflated. The lines are also written as FOCUS-shaped rows, and sql/part24/focus_by_product.sql sums them by tag in DuckDB:

│ store sales       │ 978.68 │   94.8 │
│ (shared: Storage) │  23.17 │    2.2 │
│ stock             │  13.77 │    1.3 │
│ revenue           │  10.26 │    1.0 │
│ conversion        │   6.54 │    0.6 │

Step 5: optimisation 1, let the filter prune

date_key holds each store’s local business date. pruning runs the tile as written, again with the small-file threshold at 0B, and on date_key:

query                 seconds  CPU s  read MiB  GETs  peak MiB
local date of ts         0.45   0.39     103.4   175       4.3
  same, threshold 0B     0.47   0.40     107.1   177       0.1
date_key                 0.22   0.02       6.2     2       0.1
same answer: True (201 stores, 129,896 lines); fct_sales_line has 135 data files
GETs per run, first run first: as written [137, 180, 175], on date_key [46, 2, 2]

The same 201 rows from a sixteenth of the bytes and a twentieth of the CPU. After its first run (46 GETs; later runs 2) the files came from memory, Trino’s coordinator cache for metadata files (fs.memory-cache.max-size 2% of heap; fs.memory-cache.max-content-length 8 MB by default, at most 15 MB; fs.memory-cache.ttl 1 h). The lab’s single node also scans (node-scheduler.include-coordinator=true), so ~48 MB of its 2.4 GB heap held the tile’s 6.2 MiB of data, never the unpruned 103 MiB. Workers in a real cluster lack this cache; theirs is the Alluxio-based file system cache (fs.cache.enabled).

Step 6: optimisation 2, pay for the days that changed

Both cover the run’s window: UTC days 3 and 4 October (business dates to 5 October). models/lab24/rpt_daily_net_revenue_incr.sql is the v2 report as an incremental model (delete+insert by country and date; vars run_start, run_end) that re-derives business dates from three days before the window (the days is_provisional can flip) to its end. The fact’s four data tests put --vars '{test_since: …}' in their where config, and the completeness test filters on it in its own SQL; without it, both check every row, as before:

            where: "event_ts >= TIMESTAMP '{{ var('test_since', '1970-01-01') }} 00:00:00 UTC'"  # Part 24

incremental builds it in dev, compares with production’s report, adds 1 to every net_minor, runs it incrementally and compares again:

                        wall s  Trino CPU s  read MiB  queries   GETs
report: rebuild            5.7          3.9     208.5        5    211
report: incremental        3.0          0.5      18.0       12    101
tests: every row           8.7          9.7     811.6       10   1114
tests: run window          3.6          0.4      85.4        7    240
rebuild vs production's report: 0 rows differ
after damaging all 378 rows and one incremental run: 363 rows still differ, 363 of them outside the window

The run put back exactly the window’s 15 rows and touched nothing else; report and tests read about 12 and 10 times fewer bytes, and stay flat as history grows. That proves the run rewrites its window, not that the window catches every late change: fct_refund is rebuilt whole each night, so a refund corrected after its date moves a business date outside the window, and so do Part 21’s restatements and re-decided dispositions. A backfill or restatement passes its own run_start/run_end; a weekly --full-refresh build, compared like the script’s report_diff, catches the rest.

Step 7: optimisation 3, storage nobody owns

The cause was the lab’s own code. Part 9’s drop_table dropped through the catalog, then deleted warehouse/<namespace>/<table>, the folder Polaris gives a table created without a location (PyIceberg’s, here). Trino sends its own location with a UUID suffix (iceberg.unique-table-location, default true), so every Trino-created table a re-run dropped stayed: Part 18’s dev rebuilds, plus older gold and silver generations (not traced). Dropped views left their metadata files too. drop_table now deletes the table’s own location, empty_dev a dev view’s folder, and tests prove both.

sweep is remove_orphan_files’ reachability check applied to the whole warehouse prefix: it deletes a folder only if no table references a file in it and nobody wrote to it within the grace period, refuses to delete anything if a reference query fails (an unread table’s files would look owner-less), then compacts SeaweedFS’s volumes. From the sweep’s run:

386 of 393 owner-less folders untouched for over 1:00:00; the biggest:
  dev/fct_sales_line                     3.12 GiB in 33 folders
  gold/fct_sales_line                    1.39 GiB in 4 folders
  silver/int_sales_lines                 1.38 GiB in 4 folders
deleted 386 folders: 26,794 objects, 11.30 GiB

bucket         volumes GiB  deleted, on disk GiB  worst volume   after vacuum: volumes, deleted
larkspur-lake        22.18                 10.47          91%           11.71     0.00
Docker disk: 6.9G free of 79.9G before, 16.3G free of 79.9G after

(Trimmed; owner-less fell to 0.43 GiB.) A second make ci after the fixes added no owner-less folder; the ten left predate them. Orphans grew to 2.81 GiB, a job for remove_orphan_files. The re-check also caught a query the first sweep skipped silently: Trino can’t read Part 8’s daily_store_sales entries (delete file pos is null), because its DuckDB-written position delete files carry bounds for file_path but none for pos. Harmless here: all 41 files its manifests ever listed sit in its own folder, which no sweep deletes. sweep now reads such history with PyIceberg, refusing only if that fails too; its dry run then passes.

In production, “owner-less” only means “not in this catalog”: sweep only a prefix this catalog alone writes (no Glue, Unity Catalog or second Polaris over it, no path-based tables, no other files). The hour of grace is a lab choice; Trino’s remove_orphan_files refuses less than seven days (iceberg.remove-orphan-files.min-retention), and production wants days, longer than its slowest uncommitted write.

Step 8: the month again

model --after replaces each optimised line with its own after-measurement and prices cost/measured/storage-after-sweep.json:

The per-byte bill falls 91%, modelled: the sweep is done and the tile fix is a one-line query change, but the incremental report exists only in dev and production’s run doesn’t pass test_since yet: deploy pending. The always-on cluster still costs USD 544.52, now busy 0.2% of its hours: pruning made it idler, not cheaper: the case for right-sizing and auto-suspend.

The Landscape

From vendor documentation, 29 October 2026.

Tips & Gotchas

Gotcha — your connection pool’s SELECT 1 is a billed query. Redshift Serverless bills pool health checks like any query. A tile that polls every minute keeps a Snowflake warehouse awake too, unless its query hits the result cache (persisted results last 24 hours).

Gotcha — a scoped test can miss a duplicate. Restricted to the run’s days, a grain test can’t see a copy whose twin is older than the window. Run the full test weekly, or on a restatement.

Tip — cap bytes per query. Trino’s query.max-scan-physical-bytes (session query_max_scan_physical_bytes) terminates a query when it reaches the limit; BigQuery’s maximum_bytes_billed fails it before it runs, without a charge. Snowflake’s resource monitors notify or suspend warehouses (not serverless features).

Gotcha — warm runs flatter. Step 5’s tile made 46 GETs cold, 2 warm. Price what production sees.

Gotcha — compaction undoes pruning on an unpartitioned table. Step 5 prunes because each file holds one UTC day. In the end-of-season run, Part 19’s nightly optimize merged the fact’s one-day data files (129 that day) into one, whose bounds match every date. Partition or sort the fact by day (Part 25, open item 2); Part 19’s tests now roll the table back.

Tip — attribute by the query, then add idle. dbt prefixes every statement with a comment carrying its node_id (visible in Trino’s query list). Per-query figures (Trino’s, Snowflake’s QUERY_ATTRIBUTION_HISTORY) leave idle out: report it as its own line, owned by whoever sized the cluster.

Troubleshooting

Design Drill

The question: “A security-analytics SaaS stores 2,000 customers’ firewall and identity logs in a cloud warehouse and runs detections and customer dashboards on them. The warehouse bill doubled in two months. Find out why and cut it by 40% without breaking detections.”

Clarify first

• How is it billed (per byte, credit-second, capacity), with what commitments?
• What changed: customers, ingest volume, detections, dashboards, retention?
• Can costs be attributed today by customer, detection, dashboard?
• Which detections have a latency promise, and how long must logs stay queryable?

The shape of a strong answer

• Attribute first: query history joined to the billing export (FOCUS where offered); rank cost per customer, detection and dashboard.
• Growth or waste? If cost per GB ingested held, the business grew. If it doubled, look at the usual three: a query that stopped pruning (a function on the timestamp), full rebuilds that should be incremental, warehouses that never suspend.
• Fix pruning at the model: partition and cluster on time and tenant, give dashboards a column to filter, cap bytes per query.
• Detections over incremental windows with a weekly full sweep; per-tenant daily rollups for dashboards.
• Separate warehouses for ingest, detections and dashboards: right-sized, auto-suspending, monitored.
• Retention by tier: 30 days hot, the rest cheaper; drop orphaned tables and clones. Prove the 40% per lever with the same meters.
• Commitments last, priced on the bill left once the waste is gone. Prevent the next doubling: per-tenant cost in a FOCUS table, budgets and anomaly alerts, byte caps checked in CI.

Trade-offs to say out loud

• Incremental detections can miss late logs; keep a lookback and a periodic full scan.
• Auto-suspend saves idle hours but costs cold caches.

Common weak answers

• “Buy a commitment” first: a discount on waste.
• Resizing before attributing: the 40% hides in one query.

Further Reading

1. FinOps Foundation, FOCUS specification v1.4 (Cost and Usage columns, changelog). https://focus.finops.org/focus-specification/
2. Trino 484, query management properties and file system cache. https://trino.io/docs/current/admin/properties-query-management.html and https://trino.io/docs/current/object-storage/file-system-cache.html
3. Snowflake, understanding compute cost. https://docs.snowflake.com/en/user-guide/cost-understanding-compute
4. Google Cloud, controlling BigQuery costs. https://docs.cloud.google.com/bigquery/docs/best-practices-costs
5. Apache Iceberg, maintenance. https://iceberg.apache.org/docs/latest/maintenance/

Story Closing

Rohan’s form had three numbers marked “modelled; deploy pending”: USD 91 a month at per-byte list prices, USD 0.10 per daily report, and a cluster “idle 99.8% unless it suspends”.

“And the tile?” Farah asked.

“Same answer, a sixteenth of the bytes. Someone had written the date the way I would have.”

Of the five promises from Part 2, this part protects timeliness: a run that reads only what changed can run as often as needed, and cheap checks never get switched off. Part 25 is the second design review: the whole batch platform defended end to end, and the quarter Farah signs off.

This is Part 24 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 2: Pipelines in Production.