Partitioning - For TimeSeries Data

High Cardinality (> 1000) --> Use Clustering

Low Cardinality (<1000) --> Use Partitioning

Methods to Partition BigQuery Tables

You can partition BigQuery tables by:

  • Time-unit column: Tables are partitioned based on a TIMESTAMP, DATE, or DATETIME column in the table.
  • Ingestion time: Tables are partitioned based on the timestamp when BigQuery ingests the data.
  • Integer range: Tables are partitioned based on an integer column.

How does the partition help query performance?

If a query uses a qualifying filter on the value of the partitioning column, BigQuery can scan the partitions that match the filter and skip the remaining partitions. This process is called pruning.

Summary

Use Table Partitioning for large tables with low cardinality - to improve bigquery query performance.