Integer range partitioning in BigQuery is a table partitioning method that divides a table into segments based on ranges of integer values in a specified column. You define a start, an end, and an interval, and BigQuery automatically creates one partition for each range, making queries that filter on that column faster and cheaper.
Why Partition by Integer Range?
BigQuery offers several partitioning strategies: ingestion time (by date), date/timestamp columns, and integer range. Integer range partitioning is ideal when your data has a natural numeric key that isn’t a date—for example, a customer ID, a sensor reading, a product SKU, or a score. The main benefits are the same as for any partitioning: cost reduction (you only scan the partitions that match your filter) and performance improvement (less data to read). Unlike date partitioning, integer range lets you control the exact bucket boundaries, which is useful when your numeric values don’t map neatly to calendar days.
How Integer Range Partitioning Works
You define three parameters when creating or altering a table:
- Partition column: an INTEGER column (or INT64) that will hold the values used for partitioning.
- Start: the inclusive lower bound of the first range.
- End: the exclusive upper bound of the last range.
- Interval: the width of each range.
BigQuery creates partitions for ranges: [start, start+interval), [start+interval, start+2*interval), …, up to but not including end. For example, with start=0, end=100, interval=10, you get partitions for 0–9, 10–19, 20–29, …, 90–99. Any row with a partition column value outside the defined ranges goes into a special __UNPARTITIONED__ partition.
Creating an Integer-Range Partitioned Table
You can create such a table using DDL, the console, or the API. The DDL syntax is straightforward:
CREATE TABLE mydataset.sales (
customer_id INT64,
amount FLOAT64,
order_date DATE
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100000, 1000));
Here, RANGE_BUCKET takes the column and an array of bucket boundaries. The array is generated by GENERATE_ARRAY(0, 100000, 1000), which creates [0, 1000, 2000, …, 99000]. The buckets are: [0,1000), [1000,2000), …, [99000,100000). Rows with customer_id outside this range go to __UNPARTITIONED__.
Alternatively, you can use the older PARTITION BY RANGE syntax with explicit start, end, and interval:
CREATE TABLE mydataset.sales (
customer_id INT64,
amount FLOAT64
)
PARTITION BY RANGE(customer_id, 0, 100000, 1000);
Both are valid, but RANGE_BUCKET with GENERATE_ARRAY gives you more flexibility for non-uniform ranges (though the interval method is simpler for uniform ranges).
Choosing the Right Interval and Bounds
Selecting the interval is a balance between the number of partitions and query performance. Too many partitions (e.g., interval of 1) can create overhead in metadata and may slow down queries that scan many partitions. Too few (e.g., interval of 1,000,000) means each partition is huge, reducing the benefit of pruning. A good rule of thumb is to aim for a few hundred to a few thousand partitions. For a table with values from 1 to 10 million, an interval of 10,000 gives 1,000 partitions—reasonable.
Also consider your query patterns. If you frequently filter by ranges like WHERE customer_id BETWEEN 5000 AND 15000, you want the interval small enough that you only scan a few partitions. But if you often query the entire table, partitioning won’t help much anyway. Start with the column’s distribution and typical filters.
Querying and Pruning
When you run a query with a filter on the partition column, BigQuery automatically prunes partitions—it only reads the partitions that could contain matching rows. For example, with the table above and a query:
SELECT * FROM mydataset.sales WHERE customer_id = 12345;
BigQuery will only scan the partition covering [12000,13000). This reduces bytes billed. However, pruning only works when the filter is on the partition column itself. If you filter on another column, all partitions are scanned.
One common misconception is that partitioning replaces clustering. They serve different purposes: partitioning prunes at the partition level, while clustering sorts data within each partition to allow block-level pruning. You can combine both—partition by integer range and cluster by another column—for even better performance.
Common Problems and Limitations
- Rows in __UNPARTITIONED__: Values outside the defined range go to a special partition. This can cause unexpected costs if you forget to update bounds as data grows.
- NULL values: A NULL in the partition column also goes to
__UNPARTITIONED__. - Cannot change partitioning after creation: You cannot alter the partition column, start, end, or interval on an existing table. You must recreate the table and copy data.
- Partition limits: BigQuery allows up to 4000 partitions per table (for integer range, that’s the number of ranges). If your interval creates more, you’ll get an error.
- DML operations: When you insert or update rows, BigQuery routes them to the correct partition automatically. But if you use
MERGEorDELETEwith filters not on the partition column, it may scan more partitions.
Integer Range vs. Date Partitioning
Date partitioning is the most common type because many datasets are time-series. Integer range is for when your natural key is a number that isn’t a date. For example, a table of IoT sensor readings might have a sensor_id that ranges from 1 to 100,000. Partitioning by sensor_id range lets you quickly query a specific sensor. If you also have a timestamp, you could partition by date and cluster by sensor_id, or partition by sensor range and cluster by timestamp—the choice depends on which filter is more common in your queries.
Another difference: date partitioning supports automatic partition expiration (e.g., delete partitions older than 30 days), but integer range partitioning does not. You’d have to manually delete partitions or use a scheduled job.
Cost and Performance Considerations
The main cost benefit is reduced bytes scanned. BigQuery pricing is based on the amount of data processed by queries. With partitioning, a query that filters on the partition column scans only the relevant partitions, so you pay less. However, you still pay for storage of all partitions. There’s no extra charge for partitioning itself.
Performance-wise, partition pruning reduces the I/O and the amount of data to shuffle, which often speeds up queries. But be aware: if your query filters on a range that spans many partitions, you’ll scan many partitions. For example, WHERE customer_id BETWEEN 0 AND 1000000 with interval 1000 scans 1000 partitions—still better than the whole table, but not as good as a narrow filter.
Alternatives and Best Practices
If integer range doesn’t fit, consider:
- Date/timestamp partitioning: for time-based data.
- Clustering: if you have a high-cardinality numeric column and don’t need strict partition boundaries, clustering on that column can also reduce costs, but it doesn’t guarantee partition-level pruning—it sorts data and allows block-level skipping, which is less aggressive.
- Sharding tables: old practice of creating multiple tables (e.g., table_1, table_2) is discouraged; use partitioning instead.
Best practices: choose a partition column that is frequently used in WHERE clauses, keep the interval such that you stay under 4000 partitions, and monitor the __UNPARTITIONED__ partition to ensure you don’t have unexpected data. Also, when loading data, use the WRITE_TRUNCATE or WRITE_APPEND appropriately—BigQuery will route rows to the right partitions automatically.
Frequently Asked Questions
Can I change the interval on an existing integer-range partitioned table?
No. You cannot alter the partition specification of an existing table. You must create a new table with the desired interval and copy the data over, for example using INSERT INTO ... SELECT or the copy job. Plan your bounds carefully from the start.
What happens to rows with NULL in the partition column?
They are placed in the __UNPARTITIONED__ partition, just like rows with values outside the defined range. This partition is scanned if your query doesn’t filter on the partition column, or if you specifically query it.
How many partitions can I have with integer range partitioning?
BigQuery allows a maximum of 4000 partitions per table. For integer range, that’s the number of ranges you define. If your interval produces more ranges, the table creation fails. For example, start=0, end=10,000,000, interval=1000 gives 10,000 ranges—too many.
Does integer range partitioning work with clustering?
Yes, you can cluster a partitioned table by additional columns. For example, partition by customer_id range and cluster by order_date. This can improve performance for queries that filter on both columns.
Is integer range partitioning supported in all BigQuery environments?
Yes, it’s a core feature available in the BigQuery UI, CLI, and API. There’s no special requirement. However, the RANGE_BUCKET syntax with GENERATE_ARRAY is the recommended modern approach; the older PARTITION BY RANGE is still supported but less flexible.
How do I see which partitions exist in my table?
You can query the __PARTITIONS_SUMMARY__ pseudo-table, e.g., SELECT * FROM mydataset.sales__PARTITIONS_SUMMARY__, to see the partition IDs and row counts. For integer range, the partition ID is the lower bound of the range.
Related
- Excel Template to Pay Off Debt? Yes, Here’s How
- Excel Inventory Template: How to Track Stock
- Excel Template Job Schedule: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Questionnaire Template in Word Step by StepTo create a questionnaire template in Microsoft Word, lay out your questions and response areas in a document,…
- How to Search in Excel: Keyboard Shortcut and TipsTo search an Excel worksheet, press Ctrl+F in Excel for Microsoft 365 on Windows, type the text or…
- How to Record a PowerPoint Presentation With SubtitlesYes. In PowerPoint for Microsoft 365, record your slides and narration, export the presentation as a video, then…
- Free Inventory Spreadsheet Template for ExcelYes. You can make a free inventory spreadsheet in Excel with columns for each item’s SKU, description, location,…
- Excel Utility for E-Invoice: What It DoesExcel can serve as a practical utility for e-invoicing when you use it to generate, validate, and manage…
- Excel Row Autofit: How to Fit Row HeightTo autofit a row in Excel for Microsoft 365 on Windows, select the row or cells in it,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.