[SOLVED] Cannot use IN function for Partition Filter of a View Table in BigQuery

Cannot use IN function for Partition Filter of a View Table in BigQuery

so I have a view table named table_1 and it has a partition field called business_date. Thie table_1 is constructed by using a raw table named data_mart.customer_raw.

There are a lot business needs that come from this table, one of which is to check customer's transactions in each of end of month.

Here's how I usually do my query:

SELECT
  customer_id
  ,total_transaction
FROM
  table_1
WHERE
  business_date IN ('2024-01-31', '2024-02-29', '2024-03-31')

This works well, but the issue is I have to manually create the business_date filter which will become a hassle if I need more than 10 dates.

Here's the workaround I've tried for this issue:

WITH dim_date AS (
SELECT
  DISTINCT business_date
FROM
  table_date
WHERE
  eom_flag IS TRUE
)

SELECT
  customer_id
  ,total_transaction
FROM
  table_1
WHERE
  business_date IN (SELECT business_date FROM dim_date)

Note: table_date is a table containing list of dates

The workaround above is resulting in this error:

Cannot query over table 'data_mart.customer_raw' without a filter over column(s) 'business_date' that can be used for partition elimination

Is there any way to solve this error?

Solution

As per the answer posted on the stack link to limit the partitions that are scanned in a query, you need to use a constant expression to filter the partition column. your first query satisfies the following condition whereas the second query might not be satisfying the condition as it might not be a constant. Thus it’s throwing an error about the filter over column(s) 'business_date'

I suppose modifying the query to support that condition might resolve the issue.

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future. Feel free to edit this answer for additional information.