And god help you if they figure out you can easily connect data studio to a BQ backend.
The core issue is data studio's date time time is incompatible with the BQ type so even if you've partitioned and sorted your dataset to the absolute maximum, the data studio query engine will ruin all that work and force a bunch of column conversions that require your entire dataset to be scanned again.
So, the conversation went:
"You need to edit all your data studio queries."
"Yea.. but there's like thousands of them across hundreds of our client facing 'dashboards.'"
"Okay, here's the bill, how many full time hours does one month of this insanity purchase? If you fix your queries this bill drops by 90%. FIX YOUR QUERIES!"
They decided it was easier to leave GCP instead.
A bizarre conversation I witnessed between the BigQuery team and my company at the time (a major customer):
Company: "We need to be able to see our consumption and a breakdown of our bill"
BQ team: "Oh, yeah. We can see how that would be useful. We should probably build that..."
Like, this was a GA product without any thought given to self-serve billing visibility.
I realize billing is usually the last thing bolted on, but I'd expect some basics to be in place before the product ships.
1. for some it will make sense to move to pricing based on CPU time per query vs billing on scanned TB of data. This can be done through the commits in UI.
2. there is option to have storage billed on logical or physical bytes. If you have data with a lot of duplication (enums, customer ids etc) then physical billing can be a lot better option. Last I looked this was only available through CLI setting in dataset and you may need to ask Google to include you in their beta. We lowered our billing with 30% for storage.
I try to keep an eye on GCP release notes to find things like the physical vs logical billing.
Used this to power a +$30M revenue affiliate platform tracking.
Rule #2. BQ is amazing for being able to churn through and analyze massive amounts of data, and can very well be the best option in some use cases.
Rule #3. Letting "just anyone" run queries is as dangerous as casually handing a credit card to your drug-addicted cousin. Just wait until you get the bill!
Rule #4: Partition and cluster your data wisely. You don't have indexes.
Rule #5: Duplicate data. Throw all of the normal forms out the window. Storage is cheap, computation is expensive.
Rule #6: BQ is not meant to be used like MySQL. It's "spin up" time is too slow, but you would be hard-pressed to beat its performance on truly large data sets.
My perspective: One of our customers has a database growing by 17 gigs a day. One of them. There's several on the same scale. Yes, it's necessary. Another instance: One of our customers spent $8k in one month because limits were not properly placed on the account and we didn't catch it until the bill came. We monitor better now. A different instance: We had a dev trying to optimize a query, and they spent $250 in queries to get the cost down from $50/query to $15/query. Most of the time, though, our queries are only pennies.
Now that I've written all of this out, I feel like I need to record a video about it. There's not a lot of BQ info aside from the marketing fluff put out by "teh Google".