使用dbt_utils.pivot实现多列表转置的技术求助

核心思路 #

先把宽表逆透视成窄表（日期+指标名+指标值），再通过dbt动态宏生成日期列，配合Snowflake的PIVOT完成转置，既不用创建大量单指标模型，也能自动适配新增日期。

步骤1：动态逆透视宽表 #

先做一个中间模型，把源表的所有metric*列转成统一的metric_name和value列，用宏自动识别所有指标列，不用硬编码。

1.1 写动态获取指标列的宏（macros/get_metric_columns.sql）

{% macro get_metric_columns() %}
    {% set query %}
        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name)
        FROM information_schema.columns
        WHERE table_name = '{{ ref('source_table').identifier }}'
          AND table_schema = '{{ ref('source_table').schema }}'
          AND column_name LIKE 'metric%'
    {% endset %}
    {% set result = run_query(query) %}
    {% if execute %}
        {% set metric_cols = result.columns[0][0] %}
        {{ return(metric_cols) }}
    {% else %}
        {{ return('') }}
    {% endif %}
{% endmacro %}

1.2 逆透视中间模型（models/stg_unpivoted_metrics.sql）

SELECT
    date,
    metric_name,
    value
FROM {{ ref('source_table') }}
UNPIVOT (
    value FOR metric_name IN ({{ get_metric_columns() }})
)

步骤2：动态生成日期透视列 #

写两个宏，分别生成Snowflake PIVOT需要的日期常量列表，以及SELECT语句中的日期列别名。

2.1 生成PIVOT的IN子句宏（macros/get_pivot_date_clause.sql）

{% macro get_pivot_date_clause() %}
    {% set query %}
        SELECT LISTAGG(DISTINCT 'DATE ''' || TO_CHAR(date, 'YYYY-MM-DD') || '''', ', ') WITHIN GROUP (ORDER BY date)
        FROM {{ ref('stg_unpivoted_metrics') }}
    {% endset %}
    {% set result = run_query(query) %}
    {% if execute %}
        {% set date_clause = result.columns[0][0] %}
        {{ return(date_clause) }}
    {% else %}
        {{ return('') }}
    {% endif %}
{% endmacro %}

2.2 生成SELECT的日期列别名宏（macros/get_pivot_select_columns.sql）

{% macro get_pivot_select_columns() %}
    {% set query %}
        SELECT LISTAGG(DISTINCT '"' || TO_CHAR(date, 'YYYY-MM-DD') || '" AS "' || TO_CHAR(date, 'YYYY-MM-DD') || '"', ', ') WITHIN GROUP (ORDER BY date)
        FROM {{ ref('stg_unpivoted_metrics') }}
    {% endset %}
    {% set result = run_query(query) %}
    {% if execute %}
        {% set select_cols = result.columns[0][0] %}
        {{ return(select_cols) }}
    {% else %}
        {{ return('') }}
    {% endif %}
{% endmacro %}

步骤3：最终透视模型（models/final_pivoted_metrics.sql） #

调用上面的宏，完成动态透视：

SELECT
    metric_name,
    {{ get_pivot_select_columns() }}
FROM {{ ref('stg_unpivoted_metrics') }}
PIVOT (
    MAX(value) FOR date IN ({{ get_pivot_date_clause() }})
) AS pivoted_result

关键优势 #

• 不用为每个指标单独建模型：通过逆透视统一处理所有指标，新增指标时无需修改代码。
• 自动适配新增日期：每次运行dbt模型时，宏会自动查询最新的日期列表，生成对应的透视列。
• 性能友好：基于Snowflake原生UNPIVOT/PIVOT，比多次UNION单指标表更高效。

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