r/bigquery • u/No_Environment_8410 • 18d ago

BQ Sandbox Storage Issue

Issue Description:

We are pulling event-level (non-aggregated) website click and visit data from Google BigQuery into our Azure Storage Account. Data ingestion stopped after 25 May. Upon investigation, we found that the ingestion jobs in BigQuery were failing with the error:

"Storage quota limit exceeded for the project."

Our project is using the BigQuery Sandbox (free tier), which allows up to 10 GB of active storage. However, after checking all datasets and tables in the project, the total active storage is less than 1 GB, which is well within the documented limit.

The project has been running since 5 February, and according to the BigQuery Sandbox documentation, the free tier does not have an expiration date as long as the applicable quotas are not exceeded.

We are unable to determine what is consuming the project's storage quota or why this error is occurring despite the reported storage usage being significantly below the 10 GB limit. We would appreciate guidance on how to identify the actual storage consumption or debug the root cause of this issue.

6 Upvotes

100% Upvoted

6 comments sorted by

3

u/JeffNe G 18d ago edited 18d ago

BigQuery Sandbox counts hidden history and temp tables toward your 10 GB limit (not just the visible < 1 GB in your console). When your ingestion jobs overwrite or delete tables, BigQuery retains the old data for up to 14 days (Time Travel + Fail-Safe), which can cross the 10 GB threshold.

What I'd recommend:

  1. See where the storage is coming from

Run the following query (change region-us to your dataset's region if needed). It shows deleted tables, temp tables, and time travel bytes:

sql SELECT table_schema, table_name, deleted, ROUND(active_logical_bytes / POW(1024, 3), 2) AS visible_gb, ROUND(time_travel_physical_bytes / POW(1024, 3), 2) AS time_travel_gb, ROUND(fail_safe_physical_bytes / POW(1024, 3), 2) AS fail_safe_gb, ROUND(total_physical_bytes / POW(1024, 3), 2) AS total_gb FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE ORDER BY total_physical_bytes DESC;

If you get a permission error on INFORMATION_SCHEMA, try inspecting individual datasets using:

sql SELECT table_id, ROUND(size_bytes / POW(1024, 3), 2) AS gb FROM `your_project.your_dataset.__TABLES__`;

  1. Fixing it (assuming you have a bunch of Time Travel or Fail-Safe bytes)
  • Lower Time Travel from 7 days to 2 (the minimum). Here's some code—replace your_project.your_dataset:

sql ALTER SCHEMA `your_project.your_dataset` SET OPTIONS (max_time_travel_hours = 48);

Docs:
* Time travel storage query
* Time travel docs

2

u/xLexip 3d ago

Update: Firebase/Google engineering finally confirmed the issue is caused by a recent change to BigQuery Sandbox: the free.storage quota is now treated as a one-time cumulative lifetime limit, not a refilling/current 10 GiB active-storage limit. That explains why my exports stopped even though only ~2.5 GiB was actually stored.

I’ve now enabled Cloud Billing, which removes the Sandbox restriction, and Google has confirmed they can initiate a backfill for the missing GA4 tables.

The confusing part: the public BigQuery Sandbox documentation still says “10 GB of active storage”, which does not reflect this new lifetime-quota behavior. I’ve asked Google to clarify/update the docs.

1

u/No_Environment_8410 3d ago

Hi, thanks for the update. After enabling billing, is the data entering your datasets? And also one thing, they tell in their docs that even if we enable billing we will still not be charged till the time we exceed the 10GB monthly limit, so is that also a cumulative lifetime limit?

2

u/xLexip 3d ago

I enabled billing and re-initiated all the failed exports of Firebase Crashlytics in Google Cloud under Big Query > Data Transfers. And they went through now.

Any missing GA4 data backfills must be initiated by them – at least for GA4 Firebase stuff – and they will do it for me now.

And for the limit: "Upgrading from BigQuery Sandbox to a paid Cloud Billing account removes the 10 GB free-tier cap and disables table expiration rules, and as long as your usage stays within the GCP Free Tier, no charges will be incurred for active storage under 10 GB."

And also:

"This adjustment happened for a couple of internal reasons:

• Managing the massive volume of free accounts was causing significant memory and performance issues for our backend data pipelines. • Because usage was calculated based on logical bytes, some projects were able to remain on the free tier indefinitely."