如何在DBT中为BigQuery实现动态列透视？

一、用Jinja模板动态生成透视列 #

BigQuery的PIVOT语法要求IN子句必须是固定枚举值，无法直接嵌套SELECT查询，但可以通过DBT的Jinja模板在SQL编译阶段动态生成所需列列表：

{% set get_names_query %}
SELECT DISTINCT name FROM {{ ref('company_entity') }}
{% endset %}

{% set results = run_query(get_names_query) %}

{% if execute %}
{% set name_list = results.columns[0].values() %}
{% set name_str = name_list | map('quote') | join(', ') %}
{% else %}
{% set name_str = "'placeholder'" %}
{% endif %}

SELECT *
FROM (
    SELECT ceiu.value, ceiu.user_id, ce.name as name
    FROM {{ ref('company_entity_item_user') }} ceiu
    LEFT JOIN {{ ref('company_entity') }} ce ON ce.id = ceiu.company_entity_id
)
PIVOT(STRING_AGG(value) FOR name IN ({{ name_str }}))

关键逻辑说明： #

• run_query宏会在模型实际运行阶段（execute为True时）执行查询，获取所有唯一的name值
• 通过Jinja过滤器map('quote')给每个name值添加单引号，再用join(', ')拼接成符合PIVOT语法的字符串
• 当仅编译SQL不执行时（比如dbt compile --no-execute），用占位符避免语法报错

二、关于BigQuery DECLARE/SET在DBT中的使用 #

DBT完全支持BigQuery的脚本语法（包括DECLARE/SET），但这种方式更适合临时脚本或存储过程，而非DBT模型开发：因为即使通过DECLARE/SET获取列列表，最终仍需用EXECUTE IMMEDIATE动态执行透视SQL，无法直接生成DBT管理的持久化表/视图。

如果要用脚本方式实现，示例如下：

DECLARE name_list ARRAY<STRING>;
DECLARE pivot_sql STRING;

SET name_list = ARRAY(SELECT DISTINCT name FROM company_entity);

SET pivot_sql = """
SELECT *
FROM (
    SELECT ceiu.value, ceiu.user_id, ce.name as name
    FROM company_entity_item_user ceiu
    LEFT JOIN company_entity ce ON ce.id = ceiu.company_entity_id
)
PIVOT(STRING_AGG(value) FOR name IN (""" || ARRAY_TO_STRING(name_list, '", "') || """))
""";

EXECUTE IMMEDIATE pivot_sql;

综上，Jinja预编译SQL的方案更适配DBT的模型开发流程，推荐优先使用。

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