使用dbt run_query循环执行查询及自动生成依赖的技术问询

要解决大量UNION ALL导致的性能问题，可通过循环执行单条查询并累加结果的方式实现：

1. 从配置表读取所有查询规则
2. 创建临时表存储中间结果
3. 遍历每条规则生成独立SQL，执行后将结果插入临时表
4. 最终将临时表数据写入目标表

完整示例代码：

{% set config_rows = run_query("
    SELECT 
        OriginSourceTableName,
        ColumnNames,
        ReferenceSourceTableName,
        JoinClause,
        WhereClause
    FROM " ~ ref('mdm_Screening_01') ~ "
") %}

-- 初始化临时表（根据实际字段类型调整结构）
{% do run_query("
    CREATE OR REPLACE TEMP TABLE temp_results (
        ColumnNames STRING
    )
") %}

-- 循环执行每个查询并追加结果
{% for row in config_rows.rows %}
    {% set single_sql %}
        SELECT 
            {{ row.ColumnNames }} AS ColumnNames
        FROM {{ ref(row.OriginSourceTableName) }} A  
        LEFT JOIN {{ ref(row.ReferenceSourceTableName) }} B 
        ON {{ row.JoinClause }} 
        {% if row.WhereClause %}
        WHERE {{ row.WhereClause }}
        {% endif %}
    {% endset %}
    
    {% do run_query("INSERT INTO temp_results " ~ single_sql) %}
{% endfor %}

-- 输出最终结果到目标表
SELECT * FROM temp_results

注：不同数据仓库的临时表/插入语法可能有差异，请根据使用的仓库（如BigQuery、Snowflake等）调整。

无需手动添加-- depends_on，可从配置表中动态提取所有关联表并生成依赖：

示例代码：

-- 自动生成依赖声明
{% set dep_query %}
    SELECT DISTINCT table_name
    FROM (
        SELECT OriginSourceTableName AS table_name FROM {{ ref('mdm_Screening_01') }}
        UNION ALL
        SELECT ReferenceSourceTableName AS table_name FROM {{ ref('mdm_Screening_01') }}
    )
{% endset %}

{% for table in run_query(dep_query).columns[0].values() %}
-- depends_on: {{ ref(table) }}
{% endfor %}

-- 以下是原查询生成逻辑
{% set query %}
    SELECT 
        OriginSourceTableName,
        ColumnNames,
        ReferenceSourceTableName,
        ReferenceColumns,
        WhereClause,
        JoinClause
    FROM {{ ref('mdm_Screening_01') }}
{% endset %}

{% for i in query_to_list(query) %}
{{ log(dbt_utils.pretty_log_format(i), info=True) }}

SELECT 
    {{ i[1] }} AS ColumnNames
FROM {{ ref(i[0]) }} A  

LEFT JOIN {{ ref(i[2]) }} B 
ON {{ i[5] }} 

{% if i[4] %}
WHERE {{ i[4] }}
{% endif %}

{% if not loop.last %}
UNION ALL 
{% endif %}
{% endfor %}

这样每次dbt运行时，会自动扫描配置表中所有用到的源表，生成对应的依赖声明，适配查询的动态变更。

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