BigQuery doesn’t support subqueries (or any dynamic data) in partition filters:
I’m using the default __config for incremental tables and it’s creating a subquery to get the most recent record that was created from the current table.
Is there another workaround for incremental tables with bigquery to avoid full table scans?
Hi @ca136,
Can you show the generated SQL in target/run, so I can understand better the problem?
merge is a default behavior of incremental model .in Bigquery. dbt loads the data incrementally from source but scans full table to check matching unique key to update corresponding rows.
If you want to avoid full table scans you should use insert+overwrite strategy (BigQuery configurations | dbt Developer Hub). This will not scan table but drops associated partitions and loads the data in-place. For this strategy you should have partitioned table.