Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. BigQuery quotas and limits that bite

Snowflake, BigQuery & Databricks · BigQuery

BigQuery quotas and limits that bite

Mediumwarehouses-35
bigqueryquotaslimitsproduction

Question

Which BigQuery limits do data engineers run into in production?

Solution

BigQuery quotas and limits are documented and change over time, so quote numbers carefully and check the current documentation. The ones that most often cause trouble in production are about partitions, DML, loads, concurrency and query complexity.

The ones that bite

  • Partitions per table: 10,000. Hourly partitioning over several years, or partitioning on a column with many distinct values, can reach it.
  • Partition modifications per day: limits on how many partitions your load jobs and DML can change each day. A job that touches many partitions with many small writes can fail with a quota error.
  • Load jobs per table per day: loading with a job for every tiny file will hit it. Batch the files together.
  • Table update rate: there are limits on how often a table's metadata can be changed, so rapidly looping CREATE OR REPLACE TABLE or many small DML statements can be throttled.
  • DML concurrency: mutating DML on the same table runs with limits, and extra statements queue.
  • Concurrent interactive queries: a project has a limit on how many can run at once. More are queued.
  • Query complexity: very large or complicated queries can fail with "Resources exceeded during query execution", usually a sort, window or aggregation that needs more memory per slot than it has. Reducing data or simplifying helps.
  • Result size: a query result has a maximum size unless you write it to a destination table, and API responses have their own limits.
  • Maximum query length and number of referenced tables and views.

How to handle them

Design around them early. Batch writes. Pick partition granularity with the cap in mind. Break up giant queries into stages written to intermediate tables. Retry quota errors with backoff, but treat repeated errors as a design smell. Monitor the quota usage dashboards and alerts.

In an interview

Do not recite a list of numbers. Say which limits you have hit or would expect to, say what you did about them, and say you check the documentation for current values. That is the answer of someone who has operated BigQuery, not someone who memorised a page.

PreviousNext