dbt 宏：全面指南

在分析工程中，dbt（data build tool）宏通过可复用的代码块，为编写动态、可复用的 SQL 逻辑提供了强大的方式。

在本教程中，我们将介绍 dbt 宏的基础知识、如何使用Jinja 构建它们、便于维护的最佳实践，以及实际应用案例，全部配以代码示例。

什么是 dbt 宏？

dbt 宏是在 dbt 中使用 Jinja 模板语言编写的可复用代码块，它允许您动态生成SQL。它们有助于减少重复并集中管理转换逻辑。

这些宏的工作方式与传统编程语言中的函数相同。

例如，如果您需要在多个模型中计算某个特定指标，宏可以封装该计算，从而更易于维护和更新。

宏也可以存储在包中，例如dbt-utils。

为什么要使用 dbt 宏？

宏带来多项优势，主要包括：

• 减少冗余： 一次编写逻辑块，到处复用。
• 保持一致性： 在多个模型中应用相同的转换逻辑，而无需手动复制。
• 灵活性： 宏支持参数，使其可适配多种输入。
• 跨项目共享： 设计良好的宏可以作为包进行共享。

这些优势可打造更简洁、可维护且可扩展的代码库。

dbt 宏的基本概念

为帮助您理解宏在 dbt 中如何工作，我们来看一些关键概念。

1. 可复用性

宏允许您将一段 SQL 逻辑定义一次，并在多个模型、测试甚至其他宏中复用。

2. Jinja 模板引擎

Jinja 与 dbt 的集成使您可以在 SQL 进入数据库之前插入变量、应用条件并循环列表。dbt 会将该模板编译为原生 SQL，从而使宏与具体数据库解耦。

3. 引入编程技巧

宏可以将 SQL 转变为可编程的框架。它们支持逻辑、循环和条件——这些是标准 SQL 无法灵活处理的常见需求。

与函数类似，dbt 中的宏可以接受参数，并可通过不同输入调用以生成不同输出。它们通过将复杂转换拆分为更小、可复用的单元来简化复杂逻辑。

使用 Jinja 构建宏

接下来，让我们看看如何自己构建一些宏。

Jinja 是驱动 dbt 宏的模板引擎，它让您可以将 SQL 视为动态、可编程的文本。

模板结构与分隔符

要使用 Jinja，您需要使用特定的模板结构和分隔符。

以下是一些常见的结构和分隔符：

• 表达式 {{ }}：插入表达式。
• 语句 {% %}：执行语句。
• 注释 {# #}：添加希望 dbt 忽略的注释。

在宏中，{{ ... }} 用于应输出值的表达式（例如列名或计算），而 {% ... %} 用于控制流（循环、if/else 语句、宏定义）。Jinja 注释使用 {# ... #}，在编译期间会被完全移除。

下面是宏中用于语句的语法示例：

{% macro your_macro_name(your_variable) %}
    select 'Hello, {{ your_variable}}' as greeting
{% endmacro %}技术支持

下面是使用语句创建宏的示例：

{% macro greet(name) %}
    select 'Hello, {{ name }}' as greeting
{% endmacro %}技术支持

要运行该宏，请这样使用：

dbt run-operation greet(name) --args 'Austin'技术支持

变量管理

您可以将变量传入宏，或在宏内定义它们。诸如 upper 或 lower 等过滤器可以转换文本值。

下面是使用 upper 函数将所有文本值转换为大写的实现。

{% macro to_upper_case(column_name) %}
    upper({{ column_name }})
{% endmacro %}技术支持

要实现该功能，您需要在 dbt 模型中调用/引用新创建的宏。

以下是代码示例：

SELECT
    id,
    {{ to_upper_case('product_name') }} AS product_name_upper
FROM {{ source('public', 'orders') }}技术支持

控制结构

接下来，与编程语言类似，dbt 宏可以提供以下结构：

• 条件语句（{% if %} / {% endif %}）：
• 循环（{% for %} / {% endfor %}）

这些结构让您可以根据输入动态构建 SQL 子句或生成多个表达式。

这使宏更易于处理复杂的 SQL 生成任务。

参数处理与错误管理

宏可以在编译时验证输入并抛出错误，以防止运行时问题。

参数在宏声明中使用 () 括号进行定义，与编程语言中的函数定义类似。

下面是包含 2 个参数的示例：

{% macro my_macro(param1, param2) %}
    SELECT
        {{ param1 }},
        {{ param2 }}
    FROM my_table
{% endmacro %}技术支持

如您所见，在第一行中，我们包含了 param1 和 param2。

要在 dbt 模型中调用该宏，应使用双花括号 {{ }}。参数通过括号内的参数传递。

SELECT
    id,
    {{ my_macro('column_a', 'column_b') }}
FROM my_source_table技术支持

在此示例中，我们将 column_a 和 column_b 指定为参数。

使用 dbt 宏的设置指南

在开始构建和使用宏之前，让我们先搭建一个可用环境，以便您可以跟随本教程中的示例。

这样可确保您已安装 dbt、加载了示例数据集，并具有用于创建宏的正确文件夹结构。

1. 安装 dbt Core

您需要在本地安装 dbt Core。如果您在本教程中使用 Postgres，请使用以下命令安装：

pip install dbt技术支持

如果您的项目使用数据仓库，请改为运行相应的命令：

pip install dbt-core dbt-postgres

pip install dbt-core dbt-snowflake

pip install dbt-core dbt-bigquery技术支持

2. 创建 dbt 项目

在运行任何 dbt 命令之前，我们需要设置项目环境。

导航到您希望存放项目的目录。

这里将存放您的 dbt 项目文件。本例中，我们会将所有相关文件存放在这个 dbt 项目文件夹中。

要初始化一个 dbt 项目，您需要运行：

dbt init技术支持

cd ~/dbt-macros-tutorial技术支持

按以下格式创建文件夹结构：

macros_tutorial/
  ├─ models/
  ├─ macros/
  ├─ tests/
  ├─ seeds/
  ├─ dbt_project.yml技术支持

3. 准备示例数据集

我们将使用 Python 生成一个小型订单数据集，并将其加载到 Postgres（或您选择的仓库）。

import pandas as pd
import random
from datetime import datetime, timedelta
from sqlalchemy import create_engine

# Create sample orders dataframe
random.seed(42)
num_rows = 20
start_date = datetime(2025, 1, 1)

data = []
for i in range(1, num_rows + 1):
    order_date = start_date + timedelta(days=random.randint(0, 30))
    ship_date = order_date + timedelta(days=random.randint(1, 10))
    data.append({
        'order_id': i,
        'customer_id': random.randint(100, 105),
        'order_date': order_date.date(),
        'ship_date': ship_date.date(),
        'status': random.choice(['pending', 'shipped', 'delivered']),
        'revenue': round(random.uniform(50, 500), 2),
        'cost': round(random.uniform(20, 400), 2)
    })

df = pd.DataFrame(data)

# Load into Postgres
engine = create_engine('postgresql://user:password@localhost:5432/dbt_demo')
df.to_sql('orders', engine, if_exists='replace', index=False)技术支持

注意：请更新为您数据库的连接字符串。

4. 在 dbt 中创建数据源

将以下内容添加到 models/src_orders.yml：

version: 2
sources:
  - name: public
schema: public

    tables:
      - name: orders技术支持

5. 测试连接和数据源

dbt debug

dbt run-operation list_relations --args '{"schema_name": "public"}'技术支持

现在，您已准备好开始编写针对该数据集运行的宏。

如何编写和使用 dbt 宏

现在，让我们尝试编写一些自己的宏。

定义宏

将宏定义放在 macros/ 目录下的 .sql 文件中。

为宏命名时，请确保名称能清晰表达其用途，并使用参数使其更灵活。在顶部添加简短的文档字符串可帮助团队成员理解其意图。

例如：

    -- macros/my_macro.sql
    {% macro cents_to_dollars(column_name, precision=2) %}
        ({{ column_name }} / 100.0)::numeric(18, {{ precision }})
    {% endmacro %}技术支持

在模型中调用宏

在模型内调用宏时使用 {{ macro_name(arg1, arg2) }}。它们可用于 SELECT 子句、WHERE 过滤器或整个 CTE 块，让您能在任何位置注入可复用的 SQL 模式。

示例如下：

    -- models/my_model.sql
    SELECT
        order_id,
        {{ cents_to_dollars('amount_in_cents') }} AS amount_in_dollars,
        {{ cents_to_dollars('tax_in_cents', precision=4) }} AS tax_in_dollars
    FROM {{ ref('raw_orders') }}技术支持

从 CLI 调用宏

对于模型构建之外的运维任务，使用以下格式/语法：

dbt run-operation macro_name --args '{"param": "value"}'技术支持

这使您无需构建模型即可执行诸如创建架构或清理数据等实用 SQL。

命令示例如下：

dbt run-operation greet --args '{"name": "Austin"}'技术支持

使用包中的宏

宏也可以来自 dbt 的包，例如dbt-utils。

当您通过 packages.yml 安装包后，其宏会立即可用。为此，您需要运行 dbt deps。

然后即可使用 package_name.macro_name 的格式调用新增的宏。

宏文件的组织

为更好地组织，请按功能对宏进行分类和拆分，以提升可维护性。

尽量将相关宏分组到独立文件中（如 date_utils.sql、string_utils.sql），以便维护。这会让您更容易在后续定位和更新逻辑。

dbt 宏的实践示例

以下是一些更贴近实务场景的宏示例：

日期比较宏

假设您想创建一个经常用于比较日期的函数。

在这种情况下，您可以使用宏在处理不同 SQL 方言差异的同时，计算两个日期字段之间的天数差。

首先在此处创建新文件：macros/date_utils.sql。

然后使用以下代码：

{% macro days_between(start_col, end_col) %}
    {#-- Handle different warehouses --#}
    {% if target.type in ['postgres', 'redshift'] %}
        (DATE_PART('day', {{ end_col }}::timestamp - {{ start_col }}::timestamp))
    {% elif target.type in ['snowflake'] %}
        DATEDIFF('day', {{ start_col }}, {{ end_col }})
    {% elif target.type in ['bigquery'] %}
        DATE_DIFF(CAST({{ end_col }} AS DATE), CAST({{ start_col }} AS DATE), DAY)
    {% else %}
        -- Fallback to ANSI-ish expression; may need adjustment per engine
        CAST({{ end_col }} AS DATE) - CAST({{ start_col }} AS DATE)
    {% endif %}
{% endmacro %}技术支持

现在，我们将在一个模型中使用这个新的 days_between 宏。

with src as (
  select * from {{ source('public', 'orders') }}
)
select
  order_id,
  customer_id,
  order_date,
  ship_date,
  {{ days_between('order_date', 'ship_date') }} as days_to_ship
from src技术支持

自定义 generate_schema_name 宏

默认情况下，dbt 使用一个名为 generate_schema_name 的默认宏。该宏用于指定应在哪个 schema 中构建模型。

您也可以重写 dbt 的默认行为，以按环境控制所用的 schema。

您可以在此位置包含该文件：macros/generate_schema_name.sql。

`sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {# Policy:
       - prod:    ANALYTICS
       - nonprod: dev_analytics, ci_analytics, etc.
    #}
    {%- if target.name == 'prod' -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ (target.name ~ '_' ~ custom_schema_name) | lower | replace(' ', '_') }}
    {%- endif -%}
{%- endmacro %}技术支持

它的作用：在 dev 环境中，模型会落在 dev_analytics（隔离）环境中；在 prod 环境中，则落在 analytics。这可实现可预测、无冲突的部署。

在模型之外执行 SQL

您还可以在模型之外运行 SQL 命令，以进行表清理或元数据更新，而无需创建模型。

您可以编写一个执行原生 SQL 的宏，然后通过 dbt run-operation 运行它。

这对于不适合放入模型的 DDL（删除/创建表）或元数据更新很有用。

宏如下：

{% macro cleanup_temp_tables() %}
    {% set sql %}
        drop table if exists {{ target.schema }}.temp_table;
        delete from {{ target.schema }}.audit_log where event_type = 'test';
    {% endset %}
    {{ run_query(sql) }}
{% endmacro %}技术支持

接着在终端中运行该操作：

dbt run-operation cleanup_temp_tables技术支持

这将允许您在不将宏包含到模型内的情况下运行它们。

参数化宏示例

我们也可以像函数一样创建高度参数化的宏。

在本例中，我们将基于给定的状态过滤记录（当传入值时）。这使用 if 和 do 语句来构建逻辑。

{% macro where_orders(status=None, min_date=None, max_date=None) %}
  {%- set clauses = [] -%}
  {%- if status is not none -%}
    {%- do clauses.append("status = '" ~ status ~ "'") -%}
  {%- endif -%}
  {%- if min_date is not none -%}
    {%- do clauses.append("order_date >= '" ~ min_date ~ "'") -%}
  {%- endif -%}
  {%- if max_date is not none -%}
    {%- do clauses.append("order_date < '" ~ max_date ~ "'") -%}
  {%- endif -%}
  {%- if clauses | length > 0 -%}
    where {{ clauses | join(' and ') }}
  {%- endif -%}
{% endmacro %}技术支持

现在我们在一个模型中应用该宏：

with src as (
  select * from {{ ref('orders') }}
)
select *
from src
{{ where_orders(status='shipped', min_date='2025-01-05') }}技术支持

该宏为省略参数的情况内置了逻辑；被省略的条件将被忽略。这能让可选逻辑保持简洁。

dbt 宏的最佳实践

设计优秀的宏不仅仅是让 SQL 能编译通过。最好的宏应当易于发现、文档完善、行为可预期、可测试，且易于废弃或扩展。

请遵循以下最佳实践：

1. 命名规范与参数设计

与 SQL 和编程语言类似，需要遵循命名规范以确保可读性。

以下是一些命名建议：

• 明确且以动作为导向。优先使用动词或清晰意图（如 generate_select_list、where_orders），避免 helper 这类含糊名称。
• 保持名称稳定且小写。使用 snake_case，并按领域使用稳定前缀，例如 date_*、string_*、test_*、admin_*。

2. 文档编写策略

应养成记录所创建内容的习惯。文档应写在开发者会查看的地方。

例如，将内联文档字符串与包含描述和示例的 macros.yml（或 schema.yml）结合使用。

文档内容可包括：

• 用途与预期输出（1–2 句话）
• 参数表（含默认值与允许值）
• 在模型中以及使用 run-operation 调用的简短示例
• 跨数据库行为说明

配置文件示例片段：

version: 2
macros:
  - name: days_between
    description: |
      Return day difference between two date expressions, normalized across warehouses.
    arguments:
      - name: start_col
        type: string
        description: Column/expression for the start date
      - name: end_col
        type: string
        description: Column/expression for the end date
    docs:
      show: true技术支持

3. 代码组织

当需要在目录中管理大量宏时，您的代码也需要良好的组织。

尽量按领域进行结构化以便使用。示例如下：

• macros/date_utils.sql
• macros/string_utils.sql
• macros/testing/（自定义测试）
• macros/admin/（DDL、维护）
• macros/compat/（仓库适配）

4. 保持宏小而专一

一个宏应当把一件事做好。如果发现一个宏变得过大或处理不相关的逻辑，请将其拆分为更小的宏。这会提升复用性并使测试更直接。

5. 使用具代表性的数据测试宏

使用专用测试模型或预备环境来验证宏的行为。

例如：

• 创建涵盖边界情况的小型种子数据集。
• 编写一个测试模型，用不同参数调用该宏。
• 使用 dbt run 并检查编译后的 SQL 是否正确。

专业提示： 将宏与 dbt test 的自定义测试配合使用，以验证输出符合预期约束。

常见用例与应用

dbt 宏用途广泛，其应用延伸到日常分析工程工作流中的许多方面。

以下是一些更详细的示例：

• 标准化数据转换： 宏可确保在多个模型中应用相同的命名规范、修剪、多空值处理和类型转换逻辑。例如，您可以创建一个 standardize_customer_name 宏，在所有数据集市中以一致方式清洗与格式化姓名。
• 支持跨数据库查询： 不同仓库的 SQL 方言存在微妙差异。宏可以抽象这些差异——例如在 Snowflake 使用 DATEDIFF，在 BigQuery 使用 DATE_DIFF。这让您的模型具备可移植性。
• 自动化质量检查： 编写可复用的宏来生成数据测试，例如检查重复 ID、关键列为空或超出范围的值。这些宏可在多个测试模型中调用。
• 简化重复的 SQL 模式： 常见的 CTE、计算字段或过滤子句可以在宏中参数化。例如，一个根据可选筛选条件构建动态 WHERE 子句的宏，可避免复制粘贴复杂逻辑。

高级技巧与性能优化

dbt 宏也可用于更高级的应用。下面来看两种常见方式。

递归与元编程模式

宏可以调用其他宏、遍历元数据，或基于架构检查动态生成 SQL 片段。

这使您能够：

• 为每个源表自动创建分层模型。
• 无需手动输入每个列即可生成透视/反透视查询。
• 通过遍历配置变量构建与环境相关的查询。

性能优化策略

您还可以进一步优化以提升性能：

• 通过仅选择必要的列或关联来尽量减少查询复杂度。
• 根据仓库类型调整物化方式或 SQL 策略。

与 dbt 生态系统的集成

宏被设计成 dbt 生态的一部分，与包、云端功能和外部工具配合使用时更加强大。

包管理

许多社区 dbt 包（如 dbt-utils、dbt-date 和 dbt-expectations）本质上是高度可复用宏的集合。

这些包提供了预构建的转换、测试和辅助工具，免去重复造轮子。

与 dbt Cloud 和开发者工具的兼容性

宏可在 dbt Cloud 环境中无缝工作，这意味着无论在本地还是在计划的云作业中运行，您的动态 SQL 逻辑的行为都一致。

它们也能很好地与版本控制、CI/CD 流水线和代码审查流程集成，确保宏的更新得到一致的测试与部署。

真实世界的落地策略

从传统 SQL 脚本转向以宏为驱动的 dbt 工作流，既需要技术变更，也需要文化上的采纳。

迁移与变更管理

迁移时，先识别各模型间重复的 SQL 模式。随后将其逐步重构为宏，并验证编译后的 SQL 与旧逻辑一致。

使用功能开关（var()）在发布过程中开启/关闭新的宏逻辑，以尽量减少干扰。更重要的是，请告知相关方变更情况，避免破坏下游查询。

治理与部署策略

在治理方面，先为所有宏建立清晰的命名规范、参数标准和文档要求。

接着像审查模型更新一样，在拉取请求中审查宏的变更，确保其达到性能与兼容性标准。

在生产环境中，监控作业日志中的宏生成查询，确认其按预期运行。对于宏使用频繁的项目，建立可观测性看板，按宏类型跟踪编译时间与查询性能。

结论

dbt 宏是现代分析工程团队提升生产力与可维护性的关键组成部分。将 dbt 模型与宏配合使用，有助于实现更优的数据清洗与转换效果。

想进一步了解 dbt？我们的Introduction to dbt 课程和dbt 教程可能对您有所帮助。