The CFO sent one Slack message on a Tuesday in May. A screenshot of a Looker tile — a single revenue trend, weekly — and a question: which queries had fired this in the last seven days, and what had each one cost. The data lead opened a doc to answer. He closed it twenty minutes later. Nobody on the team could trace one tile to its compute.
What follows is the teardown of an audit the founder ran on an India D2C platform’s BigQuery estate in a prior role — pre-Replatform. The patterns are the post; the engagement context is the anchor.
(Engagement details and dollar figures here come from the founder’s prior-role work on an India D2C platform. Replatform’s methodology codifies these patterns; the audit numbers below are the originals.)
Two days later the founder was inside the warehouse.
The on-demand line was reading $1.18M a month, plus another $94K in storage nobody had touched since 2023. The data lead was a good engineer — Snowflake background, came over in 2022, knew dbt cold. He had also inherited thirteen Looker dashboards and a Fivetran graph that fanned out into a hundred views. The audit took six weeks.
The patterns were not surprising. The combination was.
What follows is the teardown. Seven patterns, but really seven faces of the same antagonist — every dollar found in the audit was a dashboard inheriting a developer habit nobody had time to question. I’m writing them up in the order they revealed themselves, not in order of dollar impact, because the order matters: you can’t fix #4 if you haven’t admitted #7.
Chapter 1 — The dashboards nobody owned
The first thing asked for was the list of dashboards and their owners. They sent us thirteen dashboards. Five had named owners. Eight had a Slack handle that no longer existed.
Two of the orphaned eight refreshed every fifteen minutes against the same fact table — a 4.2 TB partitioned-by-day, clustered-by-merchant_id table. The refresh was set up in 2022 for a now-deprecated mobile-team daily standup. The standup had moved to a different tool eighteen months ago. The dashboards kept refreshing.
This pattern alone — orphaned dashboards still firing scheduled queries — accounted for roughly $14,000 a month. (I want to anchor that, because nothing in this post is going to be more demoralising than realising that someone is paying $14K a month for a chart nobody opens. The fix was a single afternoon: kill them, watch for a complaint, never get one.)
The diagnostic is dull and you should run it on your warehouse this week:
SELECT
user_email,
destination_table.table_id,
ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
COUNT(*) AS query_count,
MIN(creation_time) AS first_seen,
MAX(creation_time) AS last_seen
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND job_type = 'QUERY'
AND statement_type = 'SELECT'
GROUP BY 1, 2
HAVING tib_billed > 1
ORDER BY tib_billed DESC;
Sort by tib_billed, look for service accounts with no human owner, look for refresh cadences that don’t match any human ritual. Most teams find at least one cron job that lost its purpose between Q4 and the next reorg.
Chapter 2 — The CTE that hid the partition
The most expensive single query in their warehouse cost $1,500 a day. It looked like this in shape (I’m reproducing the structure, not the table names):
WITH recent_orders AS (
SELECT * FROM orders
)
SELECT merchant_id, SUM(gmv)
FROM recent_orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY merchant_id;
The orders table was partitioned by order_date. Beautifully. The partition filter is right there in the WHERE clause. The query plan, however, was scanning every partition since 2019.
Here’s what nobody had time to question: when a partition filter sits outside the CTE and the CTE materialises the full table, the planner does not push the predicate down through every shape of CTE. In this query, BigQuery’s optimiser couldn’t push the date filter into recent_orders because the CTE used SELECT *, which the optimiser treated as a contract — give me every column for every row, then we’ll filter. The fix was four characters:
WITH recent_orders AS (
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
The query went from 4.8 TB scanned to 142 GB scanned. From $24 a run to roughly 71 cents. (Felipe Hoffa has been telling people to look at bytes processed for a decade. He’s right. Look at bytes processed.)
The pattern beneath: predicate pushdown is not a guarantee. It is a hope. Every CTE is a potential planner blocker, every view-on-view is a potential planner blocker, and the only diagnostic that matters is INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_bytes_billed. Compare it to what you think the query should scan. The gap is your bill.
Chapter 3 — The joins that touched every column
About a third of their dbt models were SELECT * joins between two wide tables. The reasoning was defensible: downstream models inherited the columns and the dbt graph stayed flexible. The cost was that every materialisation read every column.
BigQuery is columnar. You pay for the columns you reference, not the rows. A 240-column fact table joined SELECT * to a 90-column dimension table is a 330-column scan — even if the downstream report uses six of them.
The eleven worst offenders got rewritten to explicit column lists. The materialisation cost on those models dropped from about $9,400 a month to about $1,200. Nobody noticed downstream because the columns nobody was using were, in fact, not being used.
(The reason SELECT * survives in dbt projects is sociological, not technical. It is defensible during development. Nobody removes it during cleanup because removing columns from a published model feels like a breaking change. It is a breaking change, but it is a breaking change you can ship in twenty minutes if you have a column-usage map. The founder built one for them in week three of the audit.)
Chapter 4 — The Looker extracts we forgot to expire
Persistent Derived Tables. PDTs. The name itself sounds like a thing you build once and forget — and that is exactly what had happened. Twenty-eight PDTs across the project, fourteen of them set to rebuild nightly, six set to rebuild hourly, the rest with cadences nobody could explain.
Eight of the twenty-eight were rebuilding tables that nothing currently queried. They had been built to support dashboards that had since moved to direct queries. Storage and rebuild compute on those eight: roughly $6,800 a month.
This is the chapter where I want to say something that vendor sales engineers will not. Looker’s PDT mechanic is great for the cases it was designed for and a tax for everything else. If the PDT is materialising a query that runs three times a day from one dashboard, the math almost never works out — you are paying to rebuild a result that gets read less often than it gets refreshed. Default to direct queries with the right partition + cluster combo until you have measured that the PDT is cheaper.
Eight PDTs got killed in week four. Nobody noticed.
Chapter 5 — The Fivetran graph that fanned out
Their Fivetran was syncing 38 sources. Eleven of them were syncing at the highest cadence Fivetran offered (every fifteen minutes), inherited from a vendor default during the original setup. Three of those eleven landed in tables that fed weekly reports.
The downstream effect was worse than the sync cost. Every Fivetran sync triggered a chain of dbt models. Models built on synced tables rebuilt every fifteen minutes. Models on those models rebuilt every fifteen minutes. By the time you walked four hops down the graph, you had a model that took ninety seconds to rebuild and was rebuilding ninety-six times a day to serve a weekly report.
Sync frequency dropped on twenty-two of the thirty-eight sources. The downstream dbt graph quieted by roughly 64% in compute-hours. Bill impact: about $31,000 a month, more than half of which was downstream-of-the-sync compute, not Fivetran itself.
(I do not have a strong opinion on Fivetran as a product. I have a strong opinion that every managed connector ships with a default cadence and that default is almost never the cadence you actually need. Read the cadence column in your connectors page this afternoon. Half of you will find something embarrassing.)
Chapter 6 — The reservation that was never sized
This is the chapter where I stop being clever and admit something.
For the first four weeks of the audit, the working assumption was that the team should move to a capacity-based reservation. Their on-demand spend was $1.18M a month. A reasonably sized BigQuery Editions reservation — Enterprise edition, 2,000 baseline slots, autoscaling — would have cost roughly $480K a month at list price, and probably $360K with a one-year commit. The math was so obvious it felt insulting to even write the slide.
Except in week five we actually modelled their query concurrency. Their workload was not flat. It was three big spikes a day — the 6am dashboard refresh wave, the noon CFO-ping wave, the 10pm batch — separated by long flat valleys. A 2,000-slot reservation would sit idle for fourteen hours a day. A 500-slot reservation would queue everything during the spikes. The autoscaler would help, but autoscaler ramp-up is not instant, and on this workload the spike-to-spike interval was long enough that autoscaler-scaled slots would settle back down before the next spike, which meant they would re-pay the warmup cost three times a day.
The team modelled it eight different ways. The honest answer was: a reservation would have saved them roughly $90K a month against on-demand at list, which is real money, but fixing the seven patterns first would save them more than three times that — and once the patterns were fixed, the reservation math changed completely. Their post-audit workload was small enough that on-demand was the right answer for another quarter, and a reservation only made sense again at the next scale jump.
This is the pattern no one fixes, because it is the pattern that requires you to admit your sizing instincts were wrong. Most teams who switch to a reservation do so because their on-demand bill scared a CFO, not because their workload told them to. The reservation is then sized against the scared-CFO version of the workload, locked in for a year, and the team spends the next eleven months optimising the workload that no longer needs the reservation it is paying for.
Size the reservation against the workload you want, not the workload that’s embarrassing you today. If you can’t tell the difference, you are not ready to commit.
Chapter 7 — The data lead who could not answer the CFO
Everything above is a symptom. Chapter 7 is the disease.
When the CFO sent that Slack message, the data lead could not, in twenty minutes or twenty hours, answer the question which queries had fired this tile in the last seven days, and what had each one cost. Not because the data was hidden — INFORMATION_SCHEMA had everything — but because nobody had ever joined a Looker tile ID to a BigQuery job_id to a dollar figure. The attribution chain was broken in three places.
This is the gap that hid the other six patterns. You cannot fix what you cannot attribute. You cannot have the conversation with the dashboard owner if you cannot tell them their dashboard costs $4,100 a month. You cannot kill the orphaned PDT if you cannot prove nothing reads from it. You cannot drop the Fivetran cadence if you cannot trace the downstream models.
Three breaks in a four-node chain — the reason no one could answer the CFO’s question in under twenty minutes.
The attribution layer landed in week one. It took four days. It was not clever — it was a dbt source on INFORMATION_SCHEMA.JOBS_BY_PROJECT, a dbt source on Looker’s query_history view, a join key on client_query labels, and a daily materialisation that mapped tile → query → bytes → dollars. Once that existed, every other chapter wrote itself.
If you take one thing from this post: build the attribution layer first. Every audit that skips it ends up arguing about averages.
By the end of the audit, the dashboards refreshed on cadences someone could defend. The CFO could ask which queries fired which tile, and the answer took ninety seconds to produce. Workloads with these seven patterns typically reach 25–50% of original spend after addressing them; this one landed near the bottom of that range.
The patterns are boring. The combination was expensive. Most BigQuery audits I’ve seen end with the team buying a third-party FinOps tool — and most of those tools would have flagged maybe three of these seven. The other four sit in the join between dashboards, dbt, and connector defaults, which is exactly the join no vendor tool owns.
I’m not sure what to do with that observation, honestly. It either means the tooling will catch up, or it means cost work in warehouses is going to stay a craft for another few years. I lean toward the second.