dbt incremental model on BQ Partitioned table

Question

The problem I'm having

I have a BigQuery partitioned table from which I need to create a table on incremental mode. I'm unable to find how to run the full fresh

The context of why I'm trying to do this

My BigQuery table is partitioned by DAY on field _PARTITIONTIME and it requires partition filter. I need to create an incremental model with this table as source and create one record per key to show the latest record.

What I've already tried

I tried without the full refresh and it is working as expected. But I want to run the full refresh every Sunday for which I get an error

Cannot query over table 'my_dataset.my_table' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination

Some example code or error messages

{{
    config(
        materialized='incremental',
         unique_key='Id'
    )
}}

select *
from `my_dataset.my_table`

{% if is_incremental() %}
where _PARTITIONTIME > timestamp_sub(current_timestamp, INTERVAL 1 DAY)
{% endif %}

qualify row_number() over(partition by Id order by SystemModstamp desc) = 1

If i run dbt run it works without error but when i run with dbt run --full-refresh I get an error saying I need to include the partition column.

I tried the below code to select all partition less then current_timestamp which should select all the partitions when the model in not run on incremental mode. I think this works. But is there any other solution to this?

{{
    config(
        materialized='incremental',
         unique_key='Id'
    )
}}

select *
from `my_dataset.my_table`

{% if is_incremental() %}
where _PARTITIONTIME > timestamp_sub(current_timestamp, INTERVAL 1 DAY)
{% else %}
where _PARTITIONTIME < current_timestamp
{% endif %}

qualify row_number() over(partition by Id order by SystemModstamp desc) = 1

Answer 1

You can modify the require partition filter requirement and make it false so that it is not mandatory to add partition filter in the query. You can use below query to do this.

ALTER TABLE mydataset.mypartitionedtable SET OPTIONS (
require_partition_filter = false);