{"id":320,"date":"2021-05-25T10:41:29","date_gmt":"2021-05-25T08:41:29","guid":{"rendered":"https:\/\/stage.usercentrics.com\/?post_type=knowledge&p=9166"},"modified":"2025-02-04T16:18:48","modified_gmt":"2025-02-04T15:18:48","slug":"optimizing-your-bigquery-tables-using-partitioning-time-unit-column-partitioned-tables","status":"publish","type":"knowledge","link":"https:\/\/usercentrics.com\/us\/knowledge-hub\/optimizing-your-bigquery-tables-using-partitioning-time-unit-column-partitioned-tables\/","title":{"rendered":"Optimizing your BigQuery tables using partitioning: Time-unit column-partitioned tables"},"content":{"rendered":"\n

Especially when working with Big Data, costs can quickly explode, and performance can degrade fast when data starts to pile up.<\/span><\/p>\n\n\n\n

BigQuery offers the possibility to create partitioned tables that helps to prevent exploding costs and decreasing performance.<\/span><\/p>\n\n\n\n

Of course, the use case has to fit the idea behind partitioning even though most Big Data use cases should fit there in one or another way.<\/span><\/p>\n\n\n\n

Google provides three different ways to partition BigQuery tables:<\/span><\/p>\n\n\n\n

Ingestion Time<\/b> \u2014 Tables are partitioned based on the time they ingestion time.<\/span><\/p>\n\n\n\n

Time-unit column<\/b> \u2014 Tables are partitioned based on a time-unit column. Valid values here are <\/span>TIMESTAMP<\/span><\/i>, <\/span>DATE<\/span><\/i>, and <\/span>DATETIME<\/span><\/i>.<\/span><\/p>\n\n\n\n

Integer ranged<\/b> \u2014 Tables are partitioned based on an integer column.<\/span><\/p>\n\n\n

\n
\n \n\n<\/svg>\n <\/div>\n
\n

Read about Big data marketing<\/a> now<\/p>\n <\/div>\n<\/div>\n\n\n\n\n\n

What is time-unit partitioning, and how does it work?<\/h2>\n\n\n\n

Easy put partitioning a table is like splitting up the table into several “sub”-tables. Each of these partitioned tables has its unique key for easy and fast access.<\/span><\/p>\n\n\n\n

When using partitions, you can run your queries only on a specific set of the partitioned tables and thus are saving many rows your query doesn’t have to iterate to check for your conditions.<\/span><\/p>\n\n\n\n

Using “time-unit column” partitioning in BigQuery works like the following:<\/span><\/p>\n\n\n\n

You specify a column of your table with the type <\/span>TIMESTAMP<\/span><\/i>, <\/span>DATE<\/span><\/i>, or <\/span>DATETIME<\/span><\/i> and how granular the partitions should be. Valid values here are hourly, daily, monthly, and yearly. If you choose daily, all partitioned tables will each contain all rows of a specific day, so you can quickly get all rows or filter the rows for a particular day without running through ALL the other partitioned tables. <\/span><\/p>\n\n\n\n

Assuming that you write at least one entry per day over one year (365 days) and are using daily partitioning, you will have 365 partitioned tables at the end of the year. That is because each partition reflects one day of data.<\/span><\/p>\n\n\n\n

You can see a “normal” non-partitioned table and right afterward the same table with a daily partition on the “Day” column in the following two images.<\/span><\/p>\n\n\n\n

\"PARTITIONED_TABLE\"
PARTITIONED_TABLE<\/figcaption><\/figure>\n\n\n\n
\"NON_PARTITIONED\"
NON_PARTITIONED_TABLE<\/figcaption><\/figure>\n\n\n\n

Keep in mind that making actual use of the partitioning, your queries will also have to reflect it. For example, it doesn’t benefit when you have your table partitioned by a date column but then not filtering for the date, which results in a full table scan again.<\/span><\/p>\n\n\n\n

How much can it improve costs\/performance?<\/b><\/p>\n\n\n\n

Now that we went over the basics, one of the most crucial questions is “How much can it improve costs\/performance?”. No answer fits all use cases but let’s provide an example you can use as a blueprint for your calculations.<\/span><\/p>\n\n\n\n

To get to some concrete values, let’s assume the following:<\/span><\/p>\n\n\n\n