17. Cost and performance reasoning

Data system cost is dominated by four things: bytes scanned, bytes stored, bytes moved and compute hours. Performance work and cost work are therefore mostly the same work, and both start with arithmetic on the back of an envelope.

Where the money goes

• Compute: warehouse credits, cluster hours, GPU hours. Usually the largest line.
• Storage: priced per GB-month, varying by tier.
• Requests: object storage bills every GET, PUT and LIST, which hurts with millions of small files.
• Network egress: moving data across regions or out of a cloud is billed and often surprising.
• Managed services: connectors, orchestrators, vector databases, observability tools.
• Model APIs: priced per input and output token, which adds up fast in pipelines that call an LLM per document.

Back-of-envelope estimation

Before building, estimate the size of the data, the number of passes over it, the throughput available, and from those the time and cost.

Worked example, embedding a corpus. One million documents averaging 2,000 tokens is two billion tokens. API cost is then 2,000 × the price per million tokens; plug in the current price of your chosen model. Compare that with renting a GPU: tokens ÷ (tokens per second per GPU) gives GPU-seconds, multiplied by the hourly rate. The arithmetic takes five minutes and often decides the architecture.

Performance levers, in order of impact

1. Don’t read it. Partition pruning, column pruning, predicate pushdown and filtering early. The cheapest byte is one never read.
2. Read it efficiently. Columnar formats, good compression, right-sized files, data sorted by commonly filtered columns so min/max statistics can skip files.
3. Don’t recompute it. Incremental processing, materialized views, caching results keyed by input hash.
4. Move less. Avoid shuffles, use broadcast joins for small tables, keep compute in the same region as storage.
5. Parallelize well. Sensible partition counts, no skew.
6. Use the right hardware. Memory for big joins, GPUs only where the model runs, and cheaper interruptible (spot) instances for idempotent batch jobs that can restart.

The small files problem

Millions of kilobyte-sized files mean slow listings, per-request latency on every read, metadata bloat in table formats and poor compression. Streaming ingestion is a common culprit, since it writes many small files. Aim for files of roughly 128 MB to 1 GB, and run regular compaction jobs to merge small files.

Partitioning and clustering pitfalls

• Over-partitioning: partitioning by a high-cardinality column such as user_id creates millions of tiny partitions, which recreates the small files problem.
• Under-partitioning: one giant partition means every query scans everything.
• The rule of thumb: partition on a low-to-medium cardinality column that most queries filter on, usually a date. Within partitions, sort or cluster by the next most common filter columns. Iceberg’s hidden partitioning and newer clustering features in Delta and warehouses automate parts of this.

Warehouse habits that save money

• Never SELECT * on wide tables in a columnar engine; you pay for every column.
• LIMIT does not reduce bytes scanned in some engines; BigQuery, for example, bills the scan regardless.
• Auto-suspend idle warehouses and clusters.
• Tag queries and jobs by team and project, and set budgets with alerts.

AI-specific cost traps

• Idle GPUs from data loading bottlenecks (section 15) or forgotten notebooks.
• Re-embedding unchanged content: cache embeddings by content hash and model version.
• LLM calls in pipelines: use the smallest model that passes your quality bar for classification and filtering, use batch APIs where offered (often discounted), and use prompt caching for repeated prefixes.
• Vector index memory: quantize vectors and choose index types to fit RAM.
• Intermediate datasets kept forever: apply lifecycle rules to scratch and staging data.

Unit economics

Track cost per unit of value: cost per thousand predictions, per indexed document, per training run, per answered question. A total monthly bill tells you little; a unit cost tells you whether a change made things better.

Questions to ask

• How many bytes does this query or job actually scan, and how many does it need?
• What would this cost at ten times today’s data volume?
• Are we recomputing anything that has not changed?
• How many files does this table have, and what is their average size?
• What is the cost per prediction, per document or per answer?