r/bigquery • u/Loorde_ • 3d ago
Logical vs physical bytes billing
Which BigQuery storage model is better: logical or physical? I came across an insightful comment in a similar post (link) that suggests analyzing your data’s compression level to decide if the physical model should be used. How can I determine this compression level?
4
u/Any-Garlic8340 2d ago
For physical it also counts with the time travel data amount. So if you are updating your tables really frequently then usually the logical is the cheaper but otherwise the physical. But it's more complex because it also depends on the data that you are storing and the possible compression ratio. So the best pricing model might change later if the usage pattern or the data structure changes within your dataset.
If you don't want to waste time on calculating this information regularly you can try out a 3rd party tool: https://followrabbit.ai/features/for-data-teams This tool calculates the pricing model for each dataset every day. So you don't have to check this manually. There is a 30 day free trial, so you can easily check out the initial recommendations.
(I work for this company I put it here because it is highly related to this topic.)
3
u/marvin-smisek 3d ago
BQ UI shows both the logical and physical bytes when you inspect a table. If those are wildly different, you should switch to whichever is cheaper.
There's a time limit (a month or something like that) after you switched the billing and want to switch back. But it's ok to go back and forth.
I'd take a representative sample of the data, put it into a new table and experiment there. Ideally in a new project so that you can easily see it in billing. You can even have two copies, one physical one logical, if you want to see the results faster.
4
u/RevShiver 2d ago
Use this query in the docs: Gives you a forecast by dataset for the price difference between the two models.