dbt incremental model on BQ Partitioned table

Help
, ,
1

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
2

Hi!

It looks like your source table is configured to require partition filtering when querying it. That means that you won’t be able to query the table unless you prune partitions by adding a WHERE condition in your DQL statement which affects the table partitioning column.

The easiest fix for this is pruning partitions using a condition with which you are sure you will be getting the whole table as a response. The _PARTITIONTIME < CURRENT_TIMESTAMP() logic you used is a perfect option if your table is partitioned by ingestion time. A different one would be:

{% if is_incremental() %}
WHERE _PARTITIONTIME > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
{% else %}
WHERE _PARTITIONTIME > TIMESTAMP("1900-01-01 00:00:00")
{% endif %}

In both cases, you will be querying your whole table when it’s not an incremental load: the first time you build the model or if you use the --full-refresh flag.

Just remember that this need is not coming from dbt, but from the source BigQuery table configuration Partition filter: required