BigQuery pricing model cost us $10k in 22 seconds | Hacker News Reader

BigQuery pricing model cost us $10k in 22 seconds

linkedin.com

22 pointshliyan15 comments

When I started using bigquery almost 10 years ago, it was obvious from the documentation and running queries in the console that LIMIT does not change the price of the query since it doesn't change the data that needs to be scanned.

The user even highlights numerous places in the documentation and hints in the UI which communicate BQ pricing model... so it's interesting they decide to post that it's hidden or dark pattern.

Bigquery has a feature in pre-release stage which will be useful for a limit-like experience while incurring much lower cost.

https://cloud.google.com/bigquery/docs/table-sampling

I have a lot of sympathy for GCP users. There are so many stories of surprise and unpredictable billing.

All the cloud providers have foot-guns for the unwary or not-yet-bitten.

Although I use GCP all the time, were I to set something up for a friend I would not turn to GCP because of the fear of expensive oopsies.

E.g. I wish there were project and per-query options to limit max slot hours and max bytes scanned per query etc.

I regularly run really big queries in BQ that can take 10x the slots on some runs just because of 'BQ weather' and slot contention.

As others have pointed out, it's pretty easy to learn that typically using LIMIT doesn't affect query cost. That said, one surprising side effect of adding clustering is that LIMIT actually works as you'd expect it to. See this post by Felipe Hoffa, formerly of Google: https://hoffa.medium.com/bigquery-optimized-cluster-your-tab....

I am grateful for GCP's quotas that help us prevent similar own-goals.

While this specific error is something we know to avoid, I'm sure quotas have helped us avoid the pain of other errors. So I'm somewhat sympathetic.

I think it's important to read the language of and judgements in the post in the context of someone who just got a large unexpected bill (expensive lesson).

I don't have a lot of sympathy for people using their tools wrong. Using partitioning surely would have prevented this.

The replies on LinkedIn are are quick to point out everything they did wrong.

Querying a partitioned table only works if you also filter on the partition key, so you can still footgun with a LIMIT if not aware of it.

Wouldn't people who knew their tools perfectly well not even use a cloud service like BigQuery? At the level you expect them to use the tool, they could have created a big query engine themselves. Isn't the whole point of these tools is to make things easier?

Sorry but that's nonsense. Partitioning is THE central cost controlling mechanism in BigQuery and the docs clearly state this. And it's an easy to use feature, so I'm not sure what makes you think using that would be as challenging as building your own query engine.

I have some sympathy for people who are blindsided by surprising difference between a new tool and their old one.

This post is not eliciting sympathy. They're data consultants, who don't understand a very basic and fundamental aspect of the tool that they're using and recommending. If you're a consultant you have a responsibility to RTFM, and the docs are clear that LIMIT doesn't prune queries in BigQuery. And, also, the interface tells you explicitly how much data you're gonna query before you run it.

This post is also blaming Google rather than accepting their own part in this folly, and event admits the docs are clear on this matter. Cost-control in BigQuery is not difficult. One of the tradeoffs of BQ's design is that it must be configred explicitly, there's no "smart" or "automatic" query pruning, but that also makes it easier to guarantee and predict cost over time as new data arrives.

Yes the whole consultancy situation really is the icing on the cake - as the customer you pay for (alleged) experts in the field and get this as the result...

Big query has an interesting pricing and I do kinda like it. You pay for data in, while processing is basically free.

I don’t know the query they used, but limit can limit data scanned.

It’s been a long time since I used BQ, but I remember their query optimizer not being particularly advanced, so you had to be really careful where you put the limit.

You have that mixed up, storage and ingestion is cheap in BigQuery but processing is exactly were they grab $$$

But you don’t pay per computer second, but per byte scanned. That’s why TFA paid for “terabytes” in their query.

That is a sad story. And I think the referenced doc fragment is even worse than they described; they wrote:

> BigQuery charges based on referenced data, not processed data!

(emphasis theirs) and linked to a doc that says:

> When you run a query, you're charged according to the data processed in the columns you select, even if you set an explicit `LIMIT` on the results.

I would have interpreted the latter to mean something else, like that you get charged for scanning all rows when you do something like the following:

    select a, sum(b) as b_sum from table group by a order by b_sum limit 10;

...because my post-group LIMIT clause doesn't actually prevent it from needing all rows. But their query should genuinely not need all rows. It does need all partitions. I suppose if they have way too many partitions (such that each is <= the minimum fetch size, note: see edit below) then GCP genuinely needs to fetch all the data. Otherwise I am surprised they were charged so much.

edit: a caveat on "such that each is <= the minimum fetch size", I suppose their "select *" together with the columnar format might mean that I should word this as something like each (partition, column) is <= the minimum fetch size.

Reply on news.ycombinator.com