Blog

BigQuery time travel and fail-safe storage: Pitfalls and how to handle them

Switching from BigQuery’s logical storage to physical storage can dramatically reduce your storage costs, and has for many customers we’ve worked with.

But if you factor in BigQuery time travel and fail-safe costs, it may end up costing you a lot more than logical storage – or generate higher storage costs than you were expecting.

In this post, we’ll cover:

  1. The pitfalls with BigQuery’s time travel and fail-safe storage that inflate your costs
  2. Your options for getting around these pitfalls

The pitfalls with BigQuery time travel and fail-safe

BigQuery’s time travel feature lets you access data that's been changed or deleted from any point in time within a specific window. By default, your time travel window is seven days, but you can modify it down to two days.

BigQuery's fail-safe feature retains deleted data for an additional seven days after the time travel window for emergency data recovery (it was, until recently, 14 days). And unlike with time travel, you can’t modify the data retainment period. However, to get fail-safe data restored, you need to open a ticket with Google Support.

You pay for both time-travel and fail-safe storage costs when on physical storage at active physical storage rates, while on logical storage you don’t pay for either.

The chart below simulates the situation where:

  • Total long-term physical storage is 200 GiB and then 50 GiB is deleted, 
  • The time-travel window is seven days, which is then followed by 
  • A fail-safe period of seven days.

Time travel and fail-safe storage chart

 

Consider the story described below from a live BigQuery Q&A we recently held: 

We had a customer who deleted a large table that was in long-term physical storage and time-travel stored the deleted table in case it would need to be restored. Once deleted, the table data was converted to active storage inside of time-travel and the fail-safe storage, for a total of 21 days (seven on time-travel, 14 on fail-safe back when it was 14 days) the customer unknowingly paid the active storage rate, leading to an unexpectedly-large storage bill.

 

Option #1: Tweak your BigQuery time travel settings

By default, BigQuery time travel is set to seven days and can be reduced down to as low as two days. If you feel that you can afford to reduce data resilience, you can reduce this setting to reduce the costs of the data stored in time-travel.

For instance, if you have a robust backup process from BQ to other sources like GCS, decreasing your time travel period to two days should be fine. Some of our customers set up auto-archive policies/pipelines for their data to back that data up periodically, so in scenarios like this it also makes sense to have shorter time travel periods because that data is already being backed up outside BigQuery.

 

From our experience, most customers genuinely don't need the full seven-day time travel period, as even if they reduced it to two days, they’d still get the seven-day fail-safe option on top of that. So if cutting costs is more important than having the full seven days of data history, then you shouldn’t be too impacted by cutting down your time travel period to two days.

Alternatively, if you are about to delete a large amount of data, it might be worth reducing the time-travel period to two days prior to deleting the data just to reduce your storage footprint inside of it temporarily. Just make sure you set this back to the previous value if you need it!

Option #2: Convert your table to BigQuery logical storage before deleting it

The other solution is to change your dataset’s storage billing model to logical storage before deleting the data because you’re not billed for time-travel or fail-safe storage under this model.

Note that you can only do this change once every 14 days, so you would need to wait 14 days before changing back. Additionally, it can take up to 24 hours for the switch in storage model to take effect.

However, before you make the switch make sure to run this query to compare your costs with 14 days of logical storage vs. nine days of active physical storage (minimum 2 time travel days + 7 fail-safe storage days).

Below is an example output of this query, with “additional_costs_for_physical_storage” containing both time travel and fail-safe storage costs:

dataset logical_active_price base_physical_price logical_long_term_price base_long_term_price additional_costs_for_physical_storage logical_storage_price physical_storage_price difference_in_price_if_physical_is_chosen recommendation
warehouse $ 0.57 $ 0.07 $ 0.00 $ 0.00 $ 62.27 $ 0.57 $ 62.34 $ -61.77 Keep dataset on logical storage

Option #3: Don’t switch to BigQuery physical storage to begin with

If you are constantly adding and deleting data, chances are your time travel and fail-safe data volumes will be pretty high, so it might not even make sense to switch to physical storage in the first place.  

This is because you are not charged for time travel and fail-safe data in the logical storage model but are in the physical storage model.  

So if you have large amounts of data in those areas, this may bloat the costs past what logical storage would charge.  It will be important to examine those particular data volumes when considering the switch to physical storage.

The query shared above cycles through a project (and single region), and looks at each of the datasets to give you the cost breakdown and recommendation.

It makes sense to switch to BigQuery physical storage in the following scenarios:

  • The majority of your data is text/strings (i.e. json, addresses, logs, etc.) as they have the highest compression ratios.
  • When there is a solid data lifecycle plan in place (for instance, a table partitioned by day and each partition expires after X number of days).
  • Data is not being modified constantly in table/partitions (as mentioned above, this causes increased time-travel and fail-safe data storage).

Final thoughts

While in many cases, switching from BigQuery logical storage to physical storage can indeed reduce your storage costs, it's good to be aware of the potential pitfalls that could erase the savings benefit.

Subscribe to updates, news and more.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related blogs

Schedule a call with our team

You will receive a calendar invite to the email address provided below for a 15-minute call with one of our team members to discuss your needs.

You will be presented with date and time options on the next step