Case Study 1: The $9,823 Dashboard

"Nobody bought anything. Nobody deployed anything. Somebody left a default alone."

Executive Summary

Kestrel's warehouse bill rose by roughly $9,800 a month over a six-week period in 2025. No new pipelines were built, no data volume changed materially, and no configuration was deliberately altered.

The cause was a single BI dashboard, created by an analyst, left on the tool's default one-minute auto-refresh, running a query that scanned 40 GB. It refreshed 43,200 times a month, all night, every night, showing a number that updates once a day at 06:00.

This case study is about cost attribution — specifically, why it took three weeks to find a single dashboard, and what the team put in place so the next one takes ten minutes. It is also about a category of waste that no code review can catch, because there is no code.

Skills applied: per-byte cost models (§8.5, §8.7); query tagging and the four day-one controls (§8.7); projection pushdown (§8.2); auditing refresh intervals against source freshness (§8.7).

Background

The bill. Kestrel's warehouse costs had been stable for eight months at roughly $4,100 a month. Over six weeks they rose to about $13,900 — a 239% increase, arriving gradually enough that the first two monthly reviews attributed it to growth.

What made it hard to see:

  • The increase was gradual, spread over the ramp-up as the dashboard was shared with more people and its auto-refresh ran in more browser sessions.
  • No query was individually expensive. $0.2274 per refresh does not appear on any "most expensive queries" list, which sorts by per-query cost.
  • Nothing was tagged. Queries arrived from the BI tool's service account, so every one of the 22 dashboards' queries looked identical in the query history.
  • The team's cost review looked at the total and at the top ten queries by cost, and this dashboard appeared in neither.

The Problem

The dashboard was built by a merchandising analyst in early October, for a genuine purpose: a category-level view of yesterday's revenue and units, refreshed each morning.

The query:

SELECT c.category_name, ch.channel_name,
       SUM(f.net_revenue_cents) / 100.0 AS revenue,
       SUM(f.quantity)                  AS units,
       COUNT(DISTINCT f.order_id)       AS orders
  FROM gold.fct_order_item f
  JOIN gold.dim_product   p ON p.product_key = f.product_key
  JOIN gold.dim_category  c ON c.category_id = p.category_id
  JOIN gold.dim_channel  ch ON ch.channel_key = f.channel_key
 WHERE f.date_key = CAST(strftime(CURRENT_DATE - 1, '%Y%m%d') AS INTEGER)
 GROUP BY 1, 2;

The query is fine. It filters to one day, it names its columns, and it aggregates. On a well-clustered table it should scan a single day's partition.

Two things made it scan 40 GB anyway, and both are worth examining because neither is obviously wrong:

1. The join to dim_product pulled in the whole dimension. dim_product is a Type 2 dimension with 62,000 rows and, after the join, the planner materialized a wide intermediate. Small, but not the 17,753 rows of one day's facts.

2. f.date_key = CAST(strftime(...)) — a function on the partition column. Chapter 1's incident, in a different engine, three hundred pages later. The planner could not evaluate the predicate against partition statistics at plan time, so it did not prune.

The dashboard's own behavior did the rest. The BI tool's default auto-refresh was 60 seconds, and the analyst had not changed it — there was no reason to, and the field was not on the screen they were using.

$$\frac{40 \times 10^{9}}{2^{40}} \times \$6.25 = \$0.2274 \text{ per refresh}$$

$$\$0.2274 \times 60 \times 24 \times 30 = \$9{,}823 \text{ per month}$$

⚠️ Failure Mode — The waste no code review can catch

Every control this book has proposed so far assumes there is code to review. This dashboard had no code in any repository. It was created in a web interface, configured with a dropdown, and shared with a link.

A growing share of a data platform's cost is generated by artifacts that live outside version control: BI dashboards, scheduled reports, notebook jobs, spreadsheet connectors, and reverse-ETL syncs. None of them pass through a pull request. None of them are subject to your testing standards. All of them run queries against your warehouse.

Three defenses, and they are structurally different from code review:

  1. Tag every query with its origin. The BI tool, the dashboard id, the user. Without this, cost attribution is guesswork and you cannot name the thing that costs money.
  2. Set a per-query byte limit at the service-account level, so the tool physically cannot run a query that scans more than a threshold. This is the control that would have made the problem visible on day one, as a failed dashboard rather than an invoice.
  3. Audit refresh intervals against source freshness, on a schedule. A dashboard refreshing more often than its data updates is pure waste and is trivially detectable once you can see both numbers.

The Analysis

Three weeks, and the sequence is instructive because the first two weeks were spent looking in the wrong place.

Week 1: the pipelines. The team assumed a transformation had become expensive — that is where their cost intuition pointed, correctly in general (§8.7's ranking puts scheduled transformation compute first). They audited every dbt model's warehouse usage. All were within 8% of their historical cost.

Week 2: the total, broken down. They pulled the warehouse's usage views and grouped by user.

 user                        | scan_tib | share
-----------------------------+----------+-------
 svc_bi_tool                 |   1,384  | 76.1%     <- everything from the BI tool
 svc_dbt                     |     301  | 16.5%
 svc_datascience             |      98  |  5.4%
 (interactive users)         |      36  |  2.0%

76% of scanning came from a single service account shared by all twenty-two dashboards. That narrowed it to "the BI tool" and no further, because every query looked the same.

This is the moment the missing control cost them a week. Without tagging, the granularity of your cost analysis is the granularity of your service accounts.

Week 3: the query history. They exported every query the BI service account had run in 24 hours and grouped by query text hash:

 query_hash | executions | total_tib | cost_usd
------------+------------+-----------+----------
 a3f8...    |      1,440 |     53.2  |   332.50
 7c21...    |         96 |      2.1  |    13.13
 b940...    |         24 |      1.8  |    11.25
 ...

1,440 executions in one day. That is exactly 60 × 24, which is a number that identifies its own cause: something is running every minute.

From there it was ten minutes. Match the query text to the dashboard, open the dashboard, look at the refresh setting.

🔎 Read the Plan — Sort by total, not by per-query cost

The team's monthly cost review looked at "the ten most expensive queries." This dashboard's query cost $0.2274 and never appeared.

Sort by executions × cost_per_execution, not by cost_per_execution. A cheap query run constantly beats an expensive query run occasionally, and cost dashboards default to the second because it is the more dramatic list.

The three groupings worth having as standing reports:

Group by Finds
Total cost per query hash cheap queries run constantly ← this incident
Total cost per tag / dashboard / pipeline who to talk to
Executions per hour, by query hash anything on a suspicious cadence — 24, 288, 1,440

That third one is a genuinely good detector. Execution counts that are exact multiples of 24 are schedules, and 1,440 in a day is a minute-level cadence that almost nothing legitimately needs.

The Decision

Four changes, in the order they were implemented.

1. Query tagging, enforced at the connection level. The BI tool was reconfigured to pass the dashboard identifier and the requesting user as query tags on every query. Ten minutes of configuration; it is the change that makes every future investigation take ten minutes instead of three weeks.

2. A per-query byte limit on the BI service account. Set at 50 GB. Any dashboard query attempting to scan more fails with a clear error naming the limit.

This provoked the only real debate. A failing dashboard is visible to a stakeholder and a $9,823-a-month dashboard is not, and the team chose visible failure — the same "fail loudly, not plausibly" call as Chapter 2's Case Study 1. Two dashboards failed in the first week; both were genuinely scanning too much and both were fixed.

3. The query was fixed. Two changes:

-- was:  WHERE f.date_key = CAST(strftime(CURRENT_DATE - 1, '%Y%m%d') AS INTEGER)
-- now:  the parameter is computed by the BI tool and passed as a literal,
--       so the planner sees a constant it can match against partition stats.
 WHERE f.date_key = :yesterday_date_key

and the dim_product join was narrowed to the two columns it needed. Scan dropped from 40 GB to 340 MB — a factor of 118.

4. Refresh intervals audited against source freshness. A scheduled job compares each dashboard's refresh interval against the maximum _loaded_at cadence of its underlying tables, and flags any dashboard refreshing more than four times more often than its data changes.

The audit found six dashboards refreshing more often than their data updated. Together they accounted for a further $1,180 a month.

💸 Cost Check — What the four changes returned

Change Monthly saving Effort
Refresh interval 1 min → 1 hour on the offending dashboard $9,659 30 seconds
Query fix (118× less scanned) included above; also 118× faster 1 hour
Five other over-refreshing dashboards $1,180 2 hours
Byte limit catching two more $340 20 minutes
Total ~$11,179/month under a day

Annualized: roughly $134,000, against a platform whose total bill had been $4,100 a month before this started.

The uncomfortable part of that table is the first row. Thirty seconds of work, worth $9,659 a month, and it took three weeks to locate. The entire value of the tagging change is that it converts the three weeks into ten minutes — the fix was never the hard part.

What Happened

Eighteen months on:

  • The warehouse bill returned to $4,300 a month and has tracked data volume since.
  • The byte limit has fired eleven times. Nine were genuine over-scans caught before they ran. Two were legitimate large queries that needed a raised limit, granted per-query with a note.
  • The refresh audit runs monthly and has flagged three new dashboards.
  • Query tagging is now required for any tool connecting to the warehouse, checked when access is granted. This is the change the team considers most valuable, and it never saved a dollar directly.

Two findings worth carrying.

The analyst did nothing wrong. They built a useful dashboard using the tool as configured. The default was one minute, the field was not visible on the screen they used, and nothing in the system told them the choice had a price. Treating this as a training problem would have produced a memo; treating it as a systems problem produced a byte limit and a tag.

The cost review process was the actual failure. It looked at the total and at the top ten queries by unit cost, and this incident was invisible to both. A review that sorts by aggregate cost per query hash would have surfaced it in month one.

Lessons

  1. A growing share of platform cost is generated outside version control. Dashboards, notebooks, scheduled reports, reverse-ETL syncs. No pull request, no review, and they all run queries.

  2. Without query tagging, your cost analysis granularity equals your service-account granularity. That cost a week.

  3. Sort by total cost per query, not per-query cost. A cheap query run 1,440 times beats an expensive query run twice, and cost dashboards default to the wrong list.

  4. Execution counts that are multiples of 24 are schedules. 1,440 a day is a minute-level cadence almost nothing needs, and it identifies its own cause.

  5. A per-query byte limit converts an invisible cost into a visible failure. Two dashboards failed in week one; both were genuinely wrong.

  6. Audit refresh intervals against source freshness. Six more dashboards, $1,180 a month, found by comparing two numbers nobody had put side by side.

  7. A function on the partition column defeats pruning in every engine. This is Chapter 1's incident recurring in a different system.

  8. The fix took thirty seconds; finding it took three weeks. Invest in the finding, not the fixing.

Questions for Discussion

  1. The first two weeks were spent auditing pipelines, which §8.7's cost ranking says is where the money usually is. Was that a reasonable place to start? What would have redirected them sooner?

  2. The byte limit makes dashboards fail visibly. Argue the other side: what is the case for letting an expensive dashboard run and catching it in review instead? Under what circumstances is that right?

  3. Query tagging "never saved a dollar directly" and is considered the most valuable change. How would you justify a control like that to a manager who wants to see the return?

  4. The refresh audit flags dashboards refreshing more than 4× more often than their data updates. Why 4× and not 1×? What legitimate reason is there to refresh more often than the data changes?

  5. The analyst used the tool as configured and the default was one minute. Whose responsibility is a default — the vendor's, the person who set up the tool, or the person who used it? What is the general principle?

  6. Estimate how many artifacts outside version control run queries against a warehouse at a 200-person company. How would you inventory them, and what would you do with the inventory?

  7. This incident and Chapter 1's $3,840 Spark job are the same root cause in different engines. State that cause in one sentence, and write the single check that would catch both.