dbt中adapter.get_relation()函数未按预期工作问题求助

问题背景 #

我有一个基于源数据构建的dbt模型，该源数据（BigQuery中的events_intraday_*通配符表）有时可用有时不可用。尝试用adapter.get_relation()检查源表是否存在，仅在表存在时查询，但源表不存在时仍抛出错误：400 GET <bigquery_source_table_link> does not match any table。

使用的代码如下：

{%- set source_relation = adapter.get_relation(
            database=source('analytics', 'events_intraday_*').database,
            schema=source('analytics', 'events_intraday_*').schema,
            identifier=source('analytics', 'events_intraday_*').name,
        )  -%}

{% set table_exists = source_relation is not none %}

        {% if table_exists %}

            select
                user_pseudo_id,
                event_timestamp,
                event_name,
                event_params
            from {{ source('analytics', 'events_intraday_*') }}

        {% else %}

            select
                cast(null as string) as user_pseudo_id,
                cast(null as int) as event_timestamp,
                cast(null as string) as event_name,
                null as event_params

        {% endif %}

问题原因 #

核心问题有两点：

1. 调用source('analytics', 'events_intraday_*')时，dbt会在编译阶段尝试解析这个带通配符的源，即使是在adapter.get_relation()的参数中调用，dbt也会提前去BigQuery验证该通配符对应的表是否存在，导致还没进入后续条件判断就抛出错误。
2. adapter.get_relation()本身是用来检查单个具体表的存在性，并不支持通配符匹配。

解决方案 #

针对BigQuery的通配符表场景，改用查询INFORMATION_SCHEMA.TABLES的方式判断是否存在匹配的表，绕开dbt的提前源验证逻辑：

{% set check_query %}
SELECT EXISTS(
    SELECT 1
    FROM `{{ source('analytics', 'events_intraday_*').database }}.{{ source('analytics', 'events_intraday_*').schema }}.INFORMATION_SCHEMA.TABLES`
    WHERE table_name LIKE 'events_intraday_%'
)
{% endset %}

{% set table_exists = run_query(check_query).columns[0][0] %}

{% if table_exists %}
    select
        user_pseudo_id,
        event_timestamp,
        event_name,
        event_params
    from {{ source('analytics', 'events_intraday_*') }}
{% else %}
    select
        cast(null as string) as user_pseudo_id,
        cast(null as int64) as event_timestamp, -- 适配BigQuery整数类型
        cast(null as string) as event_name,
        cast(null as struct<key string, value struct<string_value string, int_value int64>>) as event_params -- 匹配原字段结构体类型
{% endif %}

关键说明 #

• 用run_query查询系统表：通过BigQuery的INFORMATION_SCHEMA.TABLES判断是否有符合通配符规则的表存在，避免dbt提前触发源验证。
• 适配BigQuery类型：BigQuery的整数类型为int64，event_params通常是结构体类型，else分支中匹配原字段类型可避免后续模型出现类型不兼容问题。

如果想彻底绕开dbt的source()函数，也可以直接用原生SQL引用通配符表：

{% set database = 'your_database_name' %}
{% set schema = 'analytics' %}
{% set identifier_pattern = 'events_intraday_%' %}

{% set check_query %}
SELECT EXISTS(
    SELECT 1
    FROM `{{ database }}.{{ schema }}.INFORMATION_SCHEMA.TABLES`
    WHERE table_name LIKE '{{ identifier_pattern }}'
)
{% endset %}

{% set table_exists = run_query(check_query).columns[0][0] %}

{% if table_exists %}
    select
        user_pseudo_id,
        event_timestamp,
        event_name,
        event_params
    from `{{ database }}.{{ schema }}.{{ identifier_pattern }}`
{% else %}
    select
        cast(null as string) as user_pseudo_id,
        cast(null as int64) as event_timestamp,
        cast(null as string) as event_name,
        cast(null as struct<key string, value struct<string_value string, int_value int64>>) as event_params
{% endif %}

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