BigQuery大表DBT增量物化出现全扫描，求优化方案

问题原因 #

你当前的写法中，WHERE load_date >= (select max(load_date) from {{ this }}) 使用了子查询获取目标表的最大分区日期，BigQuery查询优化器无法在执行计划阶段确定这个子查询的返回值，因此无法触发分区修剪（Partition Pruning），只能扫描源表的所有分区，导致资源浪费。

优化方案 #

方案1：提前获取最大分区日期作为常量变量 #

通过DBT的run_query宏预查询目标表的最大load_date，将其作为常量传入过滤条件，让BigQuery能明确识别分区范围，触发修剪。

示例代码：

{% set max_load_date_query %}
select max(load_date) from {{ this }}
{% endset %}

{% set max_load_date = run_query(max_load_date_query).columns[0][0] %}

{{ config(
    materialized='incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'load_date', 'data_type': 'date'}
) }}

select * from {{ source('huge_table') }}
{% if is_incremental() %}
    {% if max_load_date is not none %}
        where load_date >= '{{ max_load_date }}'
    {% else %}
        -- 目标表为空时的处理逻辑，可根据业务调整初始范围
        where load_date >= '2020-01-01'
    {% endif %}
{% endif %}

方案2：通过元数据表快速获取最新分区 #

直接查询BigQuery的INFORMATION_SCHEMA.PARTITIONS元数据表获取目标表的最新分区，比全表查询max(load_date)更高效，尤其适合超大目标表。

示例代码：

{% set get_max_partition_query %}
select max(partition_date)
from `{{ this.project }}.{{ this.dataset }}.INFORMATION_SCHEMA.PARTITIONS`
where table_name = '{{ this.table }}'
{% endset %}

{% set max_load_date = run_query(get_max_partition_query).columns[0][0] %}

{{ config(
    materialized='incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'load_date', 'data_type': 'date'}
) }}

select * from {{ source('huge_table') }}
{% if is_incremental() %}
    {% if max_load_date is not none %}
        where load_date >= '{{ max_load_date }}'
    {% else %}
        where load_date >= '2020-01-01'
    {% endif %}
{% endif %}

额外注意事项 #

• 确保源表huge_table确实按load_date（DATE类型）分区，否则分区修剪无法生效。
• 若业务允许，可考虑将incremental_strategy改为merge，但核心优化点仍在于让分区过滤条件成为可识别的常量。

内容的提问来源于stack exchange，提问作者GaetanZ