Bigquery Partitioning with Looker front end

Google Cloud Data Analytics
,
1

We have a table that has streaming data of a few hundreds of MBs of data coming in everyday. This table is partitioned on a timestamp column (let’s say it’s called timestamp_column) on DAY. A couple of questions on this -

  • If I query the table on date(timestamp_column) is BQ smart enough to look through the partitions and query on the selected partition or does it scan the whole table?
  • Our business layer is basically all done in Looker through views and models. In a model, there are some joins to create explores without using any filters. But we filter on date(timestamp_column). Does BQ apply the filter intelligently here?

Thank you so so much in advance!

2

If the partition granularity is daily, the table already contains a pseudocolumn named _PARTITIONDATE (but it is not returned by a SELECT * statement and you may not know it’s there). Hence, you shouldn’t have to use date(timestamp_column) to avoid using a “calculated” timestamp_column.

There are also other factors to consider when querying partitioned tables to ensure limited partitions are scanned in a query. Please check the examples and best practices here: https://cloud.google.com/bigquery/docs/querying-partitioned-tables

3

The _PARTITIONDATE column is only found if the table is partitioned on ingestion time, I’m partitioning on an existing timestamp column, so that doesn’t actually exist in my tables.

1 Like