如何用DBT管理按年份分片的BigQuery表，支持全量与增量更新

1. Dynamic Table Naming Without Hardcoding #

• Use DBT variables or custom macros to generate target table names based on years extracted from your source data.
• For single-year targets, define a target_year variable and use it in the table name config: my_table_{{ var('target_year') }}.
• For multi-year automation, create a macro to fetch all distinct years from your source shards (e.g., querying my_table_* to extract unique years) and iterate over them.

2. Resolve CREATE Statement Conflict #

• Eliminate manual CREATE statements entirely. Use DBT's built-in materializations and config blocks to define table properties—DBT will auto-generate the correct DDL with your specified settings, removing conflicts between custom and auto-generated CREATE logic.
• All table configurations (name, partitioning, clustering) live in the model's config block at the top of your SQL file.

3. Support Full Refresh & Incremental Updates #

• Use the incremental materialization to handle both modes:
  • Full Refresh: Run dbt run --full-refresh to drop and recreate the year-sharded table from scratch.
  • Incremental Update: DBT will only load new records (post the latest entry in the target table) when the table already exists.
• Add incremental filtering logic in your SQL using the is_incremental() Jinja function.

4. Configure Partitioning, Clustering & Forced Partition Filter #

• Define these BigQuery-specific settings directly in the model's config block:
  • partition_by: Specify the date column and granularity (e.g., year-level partitioning on event_date).
  • cluster_by: List columns to optimize query performance (e.g., user_id, event_type).
  • require_partition_filter: true: Enable mandatory partition filtering to prevent full-table scans.

Example Model Implementation #

{{ config(
    materialized='incremental',
    partition_by={
      "field": "event_date",
      "data_type": "date",
      "granularity": "year"
    },
    cluster_by=['user_id', 'event_type'],
    require_partition_filter=true,
    table_name= "my_table_" ~ var('target_year')
) }}

WITH source_data AS (
    SELECT
        user_id,
        event_type,
        event_date,
        event_details
    FROM
        `{{ var('project_id') }}.{{ var('source_schema') }}.my_table_{{ var('target_year') }}`
    {% if is_incremental() %}
        -- Incremental logic: load only new records
        WHERE event_date > (SELECT MAX(event_date) FROM {{ this }})
    {% endif %}
)

SELECT * FROM source_data

Multi-Year Automation Macro #

To automate runs across all existing source years, use this macro to fetch distinct years:

{% macro get_source_years() %}
    {% set query %}
        SELECT DISTINCT EXTRACT(YEAR FROM event_date) AS year
        FROM `{{ var('project_id') }}.{{ var('source_schema') }}.my_table_*`
    {% endset %}
    {% set results = run_query(query) %}
    {% if execute %}
        {% set year_list = results.columns[0].values() %}
        {{ return(year_list) }}
    {% else %}
        {{ return([]) }}
    {% endif %}
{% endmacro %}

You can then use this macro in a parent model or DBT hook to trigger runs for each year, or run individual years with:

dbt run --vars '{"target_year": 2023}'

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