Data Warehouse Cost Optimization Is a Routing Problem

Data warehouse cost optimization usually starts with a tooling question, such as which dashboard, which cost tool or which setting. We think the more useful starting point is a routing question. Who sees the number, and can that person change the query?

In most organisations the answer is no. Finance sees the invoice, and engineers see the query plan, and the two never meet until someone asks why the bill doubled.

This post covers why cost regressions travel on the wrong pipe, what the invoice path lacks, how one storage choice changed a query's price twelve times in a vendor's own example, how each warehouse meters work, the routing change we would make, and when it is not worth the effort.

A Cost Regression Is a Slow Query Billed in Dollars

We often explain it with two queries. Take one that normally runs in 200 milliseconds and suddenly takes two seconds. Monitoring fires, an engineer gets paged, and a fix such as a missing index or a reverted schema change usually ships within hours.

Now take a query that used to scan 40 GB and starts scanning 4 TB. Latency may barely move, yet resource use has grown a hundredfold. It is the same kind of event, with the same usual causes, such as a plan that flipped, a partition filter that got lost, or a SELECT * added in a hurry.

The difference is only where the signal goes. The first regression produces a page. The second produces a line item on an invoice weeks later, so the person who can see the number cannot read the plan, and the person who can read the plan never sees the number.

What the Invoice Path Lacks That the Alert Path Has

We would never accept three properties of the invoice path in an alerting channel. Any one of them would disqualify a channel for latency regressions, and we tolerate all three for cost.

1. Latency

Feedback on warehouse cost is measured in weeks, while feedback on latency is measured in seconds. A plan regression is hard to debug when the evidence is a month old, because the data, the schema and the code have all moved on.

2. Attribution

An alert knows which service and which owner it concerns. An invoice usually knows an account or a cost centre. Without a link from the spend to a query and from the query to a repository, nobody can say whose change it was.

3. Actionability

The reader of a finance dashboard has no standing to open a terminal and rewrite a query. The person who could rewrite it has no standing to see the invoice. The channel works as designed, and the design separates the problem from its fix.

One Storage Choice Changed a Query's Price Twelve Times

The size of the gap is easy to underestimate. Amazon Athena's pricing page charges $5 per terabyte scanned and works through a single-column query on a 3 TB table in three storage formats.

Storage format	Data scanned	Query cost
Uncompressed text	3 TB	$15.00
GZIP compressed	1 TB	$5.00
Parquet with compression	0.25 TB	$1.25

We find the spread striking, because the same answer costs twelve times as much in the first row as in the last. Compression gives a threefold saving and reading only one column gives a fourfold saving, and both come from a layout decision that is rarely revisited after the first load.

Billing floors matter too. The same page states a 10 megabyte minimum per query, so a high volume of tiny queries carries a cost floor that no clever SQL can remove. Only a change in architecture, such as batching or caching, lowers it.

Every Warehouse Bills a Different Meter

Whichever engine we run, the lever sits with engineering and the number lands with finance. What changes is the meter, and therefore the evidence an engineer needs to attach to a cost case.

Engine	What the bill follows	Evidence to capture per query
Amazon Athena	Bytes scanned, with a 10 MB minimum	Data scanned and the table layout
Google BigQuery on-demand	Bytes read by the query	Bytes billed and the columns referenced
Snowflake	Credits while a warehouse is running	Elapsed time, bytes scanned and partitions scanned against total

For the on-demand engines, spend is set by what the storage layout lets the engine skip. Google's BigQuery cost guidance says queries are billed according to the number of bytes read, and that a LIMIT clause on a non-clustered table does not change the amount of data read. A SELECT * with a limit still pays for the full scan.

For credit-based engines, spend follows duration. Snowflake's compute cost documentation states that "Warehouses are only billed for credit usage while running," so a slow or repeated query costs money in proportion to the time it keeps a warehouse awake.

Data Warehouse Cost Optimization Starts With Routing the Signal

We know cost tools can rank tables and users by spend, and we think that part is the easy half. The half that changes the bill is organisational, because every expensive query needs a path to a person who can read its plan. We would build that path in four links.

1. Attribute Spend to the Query

Record the cost of each statement in the unit its engine bills, either bytes scanned or warehouse time. Without a per-query number, spend stays a total, and a total cannot be assigned to anyone.

2. Attribute the Query to the Code

Tag every statement with the service and repository that issued it. Snowflake keeps this tag in the QUERY_HISTORY view as the QUERY_TAG column, next to BYTES_SCANNED and TOTAL_ELAPSED_TIME, so the tag and the cost sit on the same row. Other engines offer labels or comment conventions that do the same job.

3. Attribute the Code to a Team

Map the repository to an owner using whatever already exists, such as an OWNERS file or the on-call rotation. Inventing a new ownership register for cost alone rarely survives the first reorganisation.

4. Treat Sustained Drift as a Case

A cost increase that holds for several days goes through the same review path as a latency regression, with a deadline in weeks instead of minutes. The point is that it gets an owner and a closing note, not that it wakes anyone.

Each link is simple on its own. The value comes from having all four, because a gap at any link puts the signal back on the invoice path.

Treat Cost Drift as a Case, Not an Alert

The strongest objection to routing cost to engineers is alert fatigue. Cost drift is not a three in the morning event, and a stream of non-urgent pings would be ignored within a month. We agree with that objection, and it shapes the design.

An alert asks a person to go and investigate a mystery. A case should arrive with the evidence already attached, namely the query text, the plan before and after the change, the scan volume trend and the commit or deploy that sits closest to the step in the graph. Our guide to a Postgres query plan that changed with no deploy shows what that plan comparison looks like in practice.

Assembling that evidence by hand is the slow part, and it is where we would let software help. We would use dedicated AI agents that monitor production systems and investigate problems to gather the plan, the scan trend and the nearest change, and keep the decision on the fix with the engineer.

A Monthly Audit We Would Run With an Owner for Every Row

The routing change takes time to build. Until then, a monthly audit with three steps gets most of the benefit and needs nothing more than the warehouse's own history views.

• Rank tables by bytes scanned over the month.
• Rank users and service accounts by bytes scanned over the same window.
• Assign a named owner to the top three tables.

Almost every cost tool performs the first two steps. The third is the one that changes the bill, because it is purely organisational. It turns a ranking into a responsibility, and a team that owns a table can answer for the layout choices behind it.

Pair the audit with a hard limit where the engine supports one. BigQuery lets you set a maximum bytes billed per query, which turns a runaway scan into a failed query instead of a surprise charge. We cover the broader pattern of attaching a guardrail to every spending change in why a cost saving needs a reliability owner.

When Routing Cost to Engineers Does Not Pay Off

This process has a price of its own, and we would not impose it everywhere. A warehouse with a small, flat bill does not justify four attribution links, and the monthly audit alone is plenty.

It also fails when cost is driven by data volume growth rather than by query behaviour. If the business simply stores and scans more because it has more customers, a case per query will find nothing to fix. The right response there is a capacity and pricing conversation, which belongs to finance and engineering together.

The test we use asks if a single query or a single table explains most of a change. If it does, route it. If the increase is spread evenly, it is a budget question.

Five Questions to Ask on Monday

You can tell quickly if your organisation has a routing problem. We would ask these five questions of the team that runs the warehouse, and treat any hesitation as the answer.

1. Can you name the three most expensive queries this month?
2. Can you name the team that owns each of them?
3. If one of them tripled in cost next week, who would find out, and how long would it take?
4. Is any part of your storage layout still a default nobody has revisited?
5. When a plan regression doubles a query's cost without changing its latency, what in your system notices?

Our notes on observability cost as a hedge against bad investigations make the same point about telemetry spend, that a number only helps once it reaches someone who can act on it.

Give the Top Three Tables an Owner This Month

We think data warehouse cost optimization is mostly a routing problem. The levers are query plans and storage layouts, and they sit with engineers who rarely see the number that measures them.

Start with the monthly audit and name an owner for the top three tables, then build the four attribution links as time allows. When you want agents to assemble the plan change and scan trend for each case, Operate runs dedicated AI agents that investigate production problems with cited evidence and propose fixes for your team to review.