BigQuery: Handling Datetime field and partitioning in Holistics

Question

I have set the my DATETIME field created_date on BigQuery as partition field. Why does filtering this field on Holistics reporting not make use of the partition that has been set on created_date?. This results in a full table scan and higher costs per query

Answer

Because our platform relies on the TIMESTAMP type for precise Time Zone Handling. Storing data in TIMESTAMP ensures that the timestamp works across all time zones and correctly handles things like Daylight Saving Time, and historical changes in time zone.

As a result, when executing a query in BigQuery, we perform a CAST operation to convert the DATETIME field to a TIMESTAMP field. This is the reason why the executed query cannot make use of the partition on BigQuery. E.g.

(CAST ( `fact_transactions`.`created_date` AS TIMESTAMP ) < TIMESTAMP('2024-02-19T00:00:00.000+00:00'))

To address this issue, we recommend changing the data type of the created_date field in BigQuery to TIMESTAMP for partitioning instead.