dbt 是一个专注于 ELT 流程中 transformation 部分的工具。只要具备 SQL 经验，我们就可以用这个工具开发所有 analytical code。与此同时，我们还可以将这些代码封装在一套通常见于 software engineering 的 best practices 和 standards 之下，例如 test development、automatic deployment，甚至是在开发过程中同步构建 documentation。
在本章中，我们关于 dbt 的旅程将进入更高级、也更细微的阶段。我们将深入研究 dbt 中多样化的 model materializations。除了传统的 views 和 tables 之外，我们还会探索 ephemeral models 的潜力，利用 materialized views，在精确时刻捕获 data snapshots，甚至使用 incremental models，从而避免反复执行资源密集型的 full data loads。
但这还不是全部。我们还将通过 Jinja、macros 和 packages，把 analytics code 提升到下一层级。我们的目标是改造你的 codebase，让它更高效、更 DRY。到本章结束时，你将掌握必要知识和工具，升级你的 analytics workflow，使你能够更快、更精准地交付 insights。
Model Materializations
Materializations 是将 dbt models 持久化到 data platform 中的策略。在 dbt 中，materializations 可以通过减少即时计算 queries 和 views 的需要，来提升 data model 的 performance 和 scalability。
在 dbt 中，可以根据 project 的 needs 和 requirements 使用多种 materialization 类型。例如，你可以使用 incremental materializations 来存储只需要 incrementally 更新的 query results。此外，也可以使用 snapshots。Snapshots 与 dbt 中的 materializations 类似，但具有不同特征。Snapshots 用于在特定时间点存储 query 或 view 的结果，不过 snapshots 在 dbt 中并不是 models。它们被有意设计为 non-idempotent，这一点使它们区别于 dbt 中的大多数其他方面。
我们在第 4 章中已经使用过 materialization strategies，例如 views 和 tables。不过，熟悉所有可用 materializations 类型非常重要，包括 ephemeral models、incremental data loads，甚至 materialized views。这样你就可以在优化 analytics code 时充分利用它们，并为公司的 data consumers 提供准确且及时的响应。
Tables, Views, and Ephemeral Models
我们一直在使用 view 或 table materializations 来实现 models。本章旨在深入讨论这两类 materializations，并介绍 ephemeral models。但首先来看图 5-1，它展示了之前已经构建好的 dim_customers 当前 lineage。在这个 use case 中，我们将测试各种 materialization strategies，尤其是通过修改 stg_jaffle_shop_customers.sql model 的 materialization type 来实现。
图 5-1：dim_customers data lineage
先从 table materialization type 开始。在 dbt 中，tables 是用于存储和组织 data 的 structures，由 rows 和 columns 组成；每一行表示一条 record 或一段 data，每一列表示 data 的一个 specific attribute 或 field。当你选择这种 materialization 时，本质上是在参数化 dbt，让它将被引用的 model 渲染为在 data platform 中物理创建的对象，并将 data 存储到 disk 中。因此，它的构建速度会较慢。通常，这类 materializations 用于 marts layer 的 downstream，尤其在处理大量 data，并且对 models 有多次 queries 且要求 fast response times 时推荐使用。
为了测试 table materialization，请修改 YAML file _jaffle_shop_models.yml，将 stg_jaffle_shop_customers model 的 materialization 设置为 table。如果运行代码，在 BigQuery 中应该类似图 5-2。
图 5-2：stg_jaffle_shop_customers materialized as a table
Views 是通过选择和组合一个或多个 upstream models 中的数据创建的 virtual tables。View 本身不存储任何 data，而是在被访问时从底层 tables 中检索 data。通常，我们会在 dbt 中使用 views 来简化 complex queries、促进整体 transformation process，或者通过隐藏特定 columns 或 rows 来提供安全性。当我们将 model 设置为 view 时，它会在 data platform 中被构建为 view。存储在 disk 中的是 query 本身，因此只有在 runtime 时才会捕获 data 并执行 transformations，这可能导致 query response times 较慢。
为了测试 views 的使用，请修改 YAML file _jaffle_shop_models.yml，将 stg_jaffle_shop_customers model 的 materialization 设置为 view。再次运行代码。在 BigQuery 中应该类似图 5-3。
图 5-3：stg_jaffle_shop_customers materialized as a view
最后是 ephemeral models。dbt 会即时构建这些 temporary data models，但不会将它们持久化到 database 中。Ephemeral models 最适合用于 lightweight data manipulation 或 analysis tasks，这类任务不要求 data 被永久存储。选择这种策略时，必须记住 dbt 会在 downstream models 中将其解释为 CTEs，这也可能增加这些 downstream models 的整体构建时间。此外，如果过度使用 ephemeral models，debug 代码时也可能带来复杂性，因为你不能直接在 data platform 中 query 它们，它们在那里并不存在。
为了测试 ephemeral model，并延续前面的示例，请修改 YAML file _jaffle_shop_models.yml，将 stg_jaffle_shop_customers model 的 materialization 设置为 ephemeral。由于在这种情况下 data platform 中不会有实际 materialization，请查看 dim_product 的 compiled code。图 5-4 展示了在 stg_jaffle_shop_customers model 分别使用 view 和 ephemeral model 时，compiled code 的差异。
图 5-4：dim_customer code compiled with the stg_jaffle_shop_customers model using a view（上）and an ephemeral model（下）
Incremental Models
在 dbt project 中，incremental model 被设计为只处理 new 或 changed data，而不是处理 source 中的全部 data。这类 models 可以提升 data pipeline 的效率和速度，尤其是在处理频繁更新的大型 datasets 时。
为了测试 incremental models，首先需要配置 model 的 YAML file，将目标 model 设置为 incremental。我们将使用一个已经创建的 model，即 stg_jaffle_shop_orders，作为测试案例。查看它的 YAML file _jaffle_shop_models，可以看到它此前配置为 materialized as view。由于我们想让它变成 incremental，修改很直接，不过我们还会给它嵌入一些额外能力，例如 incremental_type。因此，让我们用 Example 5-1 中的代码更新 model 的 YAML file。
Example 5-1：Incremental model，YAML file configuration
version: 2
models:
- name: stg_jaffle_shop_orders
config:
materialized: incremental
incremental_strategy: merge
unique_key: order_id
首先，我们将 model materialization type 改为 incremental。这是 incremental models 的核心，也是让 incremental model 正常工作的 mandatory configuration。同时，我们加入了两个 additional configurations：incremental_strategy: merge 和 unique_key: order_id。这些配置有助于优化并增强 incremental loads。Incremental strategy 被定义为 merge，不过在 dbt 中还有更多选项，例如 append 或 insert_overwrite。每次 incremental run 会基于指定 unique key，将 new rows 与 existing rows 合并。在这个案例中，如果 order_id 匹配，existing rows 会用 new information 更新。否则，如果没有匹配，则创建 new rows。在标准 incremental load 中，这两种情况会并行发生。
最后一步是调整 model code，使其兼容 incremental loads。Example 5-2 展示了如何在 stg_jaffle_shop_orders model 中实现这一点。
Example 5-2：Incremental model，sample code
select
id as order_id,
user_id as customer_id,
order_date,
status,
_etl_loaded_at
from {{ source('jaffle_shop', 'orders') }}
{% if is_incremental() %}
where _etl_loaded_at >= (select max(_etl_loaded_at) from {{ this }} )
{% endif %}
分析这个 query 可以看到，我们利用 Jinja 来构建 incremental models。直接看 if statement，可以看到使用了 is_incremental() macro。这个 macro 会在以下条件同时满足时返回 true：当前运行的 model 配置为 materialized='incremental'，dbt model 已经存在，并且 dbt 不是以 full-refresh mode 运行。当 is_incremental() 返回 true 时，在 if code block 内，我们用 where condition 基于 timestamp column _etl_loaded_at 过滤 rows。它将该 timestamp 与当前 table（{{ this }}）中的最大 _etl_loaded_at timestamp 进行比较，这实际上是在检查该 row 的 load timestamp 是否大于或等于当前 table 中的最大 load timestamp。
Incremental models 在优化 dbt project 中的 data pipelines 方面发挥重要作用。它们最突出的优势之一是 cost efficiency。采用 incremental models 后，可以显著减少处理 data 所需的 computational resources。这种效率不仅会加速 data transformation processes，也会带来 cost savings，因为你不需要重复执行不必要的工作。
此外，incremental models 能确保 dbt project 始终使用最新 data。这类与 data sources 的 synchronization 提升了 analytics 的 reliability 和 accuracy。无论面对 streaming data 还是 periodic updates，incremental models 都能让 analytical insights 与不断演进的 data landscape 保持同步。
Materialized Views
Materialized views 的核心，是一种专门的 database objects，用于将 query results 存储为 physically materialized table。它们的 dynamic nature 使其不同于 regular tables；materialized view 内的数据会定期刷新，以反映 underlying dataset 的最新变化。这种 refreshing process 确保 materialized views 保持 up-to-date，而无需 reprocessing。当 low-latency data access 非常关键时，它们尤其理想。
有趣的是，materialized views 与 dbt 的 incremental models 有一些共同点，这种相似并非巧合。在许多方面，materialized views 可以被视为 incremental models 的后继者，提供了一种 data optimization 的替代方法。根据 project requirements 和所选 data platform，你甚至可以考虑用 materialized view models 替换所有 incremental dbt models。这种转变可以简化 workflow，消除手动 incremental strategies 的需求，也就是不再需要详细说明 dbt 应如何更新 base table，因为这些任务会由 data platform 无缝处理。
不过，也必须承认这种转变带来的 trade-offs。虽然 materialized views 提供效率，但它们可能让你对 incremental logic 和 orchestration 的细粒度控制减少。把 updates 的 logic 和 execution 交给 data platform 定义，你获得了便利性，但可能失去 incremental models 能提供的一部分 specific control。
测试 dbt materialized view 的方式可能因 data platform 而异。以下方法适用于 Postgres、Redshift、Databricks 或 BigQuery（dbt 1.7），并假设你希望继续测试 stg_jaffle_shop_customers model。在 _jaffle_shop_models.yml file 中，将 materialization 修改为 materialized_view，如 Example 5-3 所示。
Example 5-3：Materialized view，YAML file configuration
models:
- name: stg_jaffle_shop_customers
config:
materialized: materialized_view
不过，如果你使用 Snowflake，概念会略有不同。Snowflake 没有 materialized views 的同一概念，而是有 distinct concept：dynamic tables。使用 dynamic table 的基本配置如 Example 5-4 所示。
Example 5-4：Dynamic table，YAML file configuration
models:
- name: stg_jaffle_shop_customers
config:
materialized: dynamic_table
总结来说，materialized views 是 data optimization 的重要组成部分，提供 performance improvement 和 data currency 的好处。它们的角色与 dbt 中的 incremental models 相交，为希望简化 workflows 的 data engineers 提供了一种选择，同时也需要考虑 control 和 customization 方面的 trade-offs。
Snapshots
Snapshot 是在特定时间点保存的 dataset 副本。通常，当 analysis 需要查看持续更新 tables 的 previous data states 时，我们会使用 snapshots。例如，你可以使用 snapshot 来跟踪 dataset 的历史，使你能够看到它随时间如何演进。此外，snapshots 对 testing 和 debugging 也很有帮助，因为它们允许你将 dataset 当前状态与先前状态进行比较，以识别任何 changes 或 discrepancies。
dbt snapshots 是通过在 mutable source tables 上应用 type 2 slowly changing dimensions（SCDs）来实现的。这些 SCDs 用于识别 table 中某一行如何随时间变化。来看一个例子。使用 jaffle_shop database，假设你希望记录 orders 的 status transition，以便监控和检查 lead times，并识别某个 status 中潜在 bottlenecks。查看 stg_jaffle_shop_orders model，可以在图 5-5 中看到我们已经有 order status，但还需要看到 order 到达当前 status 之前经历过的所有 statuses。
图 5-5：stg_jaffle_shop_orders transactional dataset
为了跟踪 status transition，首先需要保留 snapshots。在 dbt 中，snapshots 是在 snapshot folder 中的 .sql file 里，在 snapshot block 内定义的 select statements。因此，首先在 snapshots directory 中创建一个名为 snap_order_status_transition.sql 的 file，并复制 Example 5-5 中的代码。
Example 5-5：snap_order_status_transition.sql snapshot creation
{% snapshot orders_status_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='id',
strategy='timestamp',
updated_at='_etl_loaded_at',
)
}}
select * from {{ source('jaffle_shop', 'orders') }}
{% endsnapshot %}
在执行代码前，先说明这些 configurations 的含义：
target_schema
这是 dbt 应将 snapshot table 渲染到的 schema。换句话说，dbt 允许你把 snapshots 存储到 data platform 中不同于实际 production environment 的 schema 中。这让你可以灵活地将它们取出并备份到其他地方。你也可以将该 field 与 target_database 结合使用，不仅把 snapshots 存储到不同 schema，也可以存储到不同 database。
unique_key
通常，这是 record 的 primary-key column 或 expression。它必须指向一个 unique key。
strategy
这表示使用哪种 snapshot strategy，可以是 timestamp 或 check。在前面的示例中，我们使用了 timestamp strategy。这是推荐策略，因为它对新增 columns 具有 scalability。有时 timestamp 不可靠，在这种情况下可以使用 check strategy 来比较一组 columns 的当前值和历史值。
updated_at
使用 timestamp strategy 时，我们需要声明 dataset 中需要查看的 column。
check_cols
仅在 check strategy 中使用，表示 dbt 需要检查哪些 columns 来生成 snapshot。
现在我们理解了这些 configurations 表示什么，执行 snapshot 并查看其 output。为此，在 CLI 中运行 dbt snapshot。成功完成后，查看 BigQuery。一个名为 snapshots 的新 schema 被创建，其中 actual snapshot 已经 materialized，如图 5-6 所示。
图 5-6：BigQuery 中的 snap_order_status_transition snapshot table
可以看到，dbt 在 data platform 中创建了 snapshot orders_status_snapshot，并生成了四个 additional columns：
dbt_scd_id
dbt 内部使用。每条 snapshotted record 都会生成一个 unique key。
dbt_updated_at
同样由 dbt 内部使用。该 field 是插入该 snapshot row 时 source record 的 updated_at timestamp。
dbt_valid_from
该 snapshot row 第一次插入时的 timestamp。可以用它对某条 record 的不同 “versions” 进行排序。
dbt_valid_to
该 row 失效时的 timestamp。如果该 record 仍然是最新 / 有效 record，则显示 null。
NOTE
如果你想继续探索 snapshot 概念，dbt 提供了全面 documentation，涵盖我们这里提到的内容，以及 additional content，例如 best practices 和如何处理来自 source systems 的 hard deletes。只需在 dbt Developer Hub 中搜索 Snapshots。
Dynamic SQL with Jinja
Jinja 是一种用于 Python 的 templating language，广泛应用于 web development。它允许你使用 variables 和 expressions 创建 dynamic HTML pages，并轻松定制 website 的 appearance 和 behavior。你也可以利用它通过 dbt 提升 SQL code 的能力。
Jinja 的关键特性之一，是能够将 variables 和 expressions 插入 templates，使你能够为不同 users 或 contexts 创建 customized templates，而无需在 template 本身中 hardcode values。例如，你可能希望根据 working environment 定义一些 behaviors，比如在 development environment 中限制 data 量。对于这种情况，我们使用 dbt 中的 target name property，然后在 SQL code 中借助 Jinja 定义处理规则，如 Example 5-6 所示。
Example 5-6：Jinja sample with the target name property
select *
from {{ ref('fct_orders' )}}
-- limit the amount of data queried in dev
{% if target.name != 'prod' %}
where order_date > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
{% endif %}
注意，这里使用的是 BigQuery 和 BigQuery syntax。如果使用不同 data platform，一些 functions 和 syntax 可能不同。现在看前面的代码，可以看到已经使用了一些 Jinja notation：
{% … %}
用于 statements，执行任何 function programming，例如设置 variable 或启动 for loop。在这个特定示例中，我们使用了一个 if statement，用于检查 property name 是否不同于 prod field。
Jinja 还提供一系列 control structures，例如 loops 和 conditional statements，使你可以创建更复杂的 templates，并让它们适应不同 data 和 contexts。你可能会使用 loop 遍历 items list，并动态生成 SQL code，而不是逐 field 手动编写。
一个理想示例可以用你之前在 Example 4-16 中创建的 int_payment_type_amount_per_order.sql model 来展示。你不必手动为每种 type 写 amount metrics，而可以自动生成它们，并使其具备 scalability，以处理当前和未来的 payment types。查看 Example 5-7，看看我们如何利用 Jinja 实现这一点。
Example 5-7：带 dynamic Jinja 的 int_payment_type_amount_per_order model
{# declaration of payment_type variable. Add here if a new one appears #}
{%- set payment_types= ['cash','credit'] -%}
with
payments as (
select * from {{ ref('stg_stripe_order_payments') }}
),
pivot_and_aggregate_payments_to_order_grain as (
select
order_id,
{% for payment_type in payment_types -%}
sum(
case
when payment_type = '{{ payment_type }}' and
status = 'success'
then amount
else 0
end
) as {{ payment_type }}_amount,
{%- endfor %}
sum(case when status = 'success' then amount end) as total_amount
from payments
group by 1
)
select * from pivot_and_aggregate_payments_to_order_grain
前面的代码是 Jinja 的更复杂用法，包含 loops 和 variables 声明。不过一旦 compiled，它看起来会与 Example 5-6 中代码的结果非常相似。现在，如果我们要考虑一种新的 payment type，不再需要手动创建新 metric，只需要把它添加到代码顶部声明的 payment_types list 中。
让我们讨论 Example 5-7 中可以看到的 Jinja 细节：
{% … %}
回顾一下，这用于 statements。在这里，我们在两个不同位置使用它，不同于 Example 5-6。
set payment_types= ['cash','credit']
这声明了 payment_types variable，后续代码会使用它。在这个案例中，它是一个包含两个元素的 list：cash 和 credit。
for payment_type in payment_types
这里，我们遍历顶部声明的不同 payment_types。逐行地，我们开始动态构建代码。
{{ … }}
用于 expressions，将内容打印到 template output。在我们的例子中，使用了 {{ payment_type }}，具体来说，是与 amount string 拼接，生成每种 payment type 的最终 metric name。同时，也在实际 metric computation 中使用了 expression：when payment_type = '{{ payment_type }}' and status = 'success'。
{# … #}
用于 comments，允许你在代码中 inline document。
Whitespaces
这是代码中的另一个小而重要的细节。你可以通过在 Jinja delimiter 任一侧使用 hyphen 来控制 whitespaces，例如 {%- … -%} 会 trim expression 那一侧的 Jinja delimiter 与内容之间的 whitespace。
TIP
建议探索专门课程，或参考官方 Jinja template design documentation，以全面理解 Jinja。这些资源可以提供有价值的 insights，并帮助你加深对 Jinja capabilities 的理解。
Using SQL Macros
Macros 是可复用代码片段，用于在 dbt project 中自动化 tasks 和 processes。它们通过允许你自动化重复或复杂任务来提升 productivity，例如 queries、data manipulation 和 data visualization。开发 macros 之后，你可以通过多种方式 call 和 trigger 它们，包括 manually、automatically，或响应 user input。
在 dbt project 中，macro 通常定义在独立 file 中，位于 macros directory 内，并使用 Jinja syntax 编写。将 macros 与 models 分离，可以让 macros 在 project 中多个 models 和其他 files 中使用。它还允许使用 variables 和 expressions 来 customize macros，使 macro 根据特定 model 传入的 arguments 做出适配。
要在 dbt project 中使用 macro，通常会用 Jinja syntax call 该 macro，并传入任何必要 arguments 或 options。Macros 也可以与 dbt 的其他 features 交互，例如 views 和其他 macros，从而创建更复杂、更 robust 的 solutions。例如，你可能使用 macro 自动刷新 data model，以特定方式 filter 或 transform data，或基于 data 生成 reports 或 charts。
让我们尝试创建第一个 macro。初始 use case 很简单：创建一个 macro，用于求两个数字之和。记住，你的 macro 需要使用 Jinja syntax。首先，在 dbt project 的 macros directory 中创建一个 macro file，命名为 macro_sum_two_values.sql。Example 5-8 展示了代码应该是什么样子。
Example 5-8：macro_sum_two_values.sql
{% macro sum(x, y) -%}
select {{ x + y }}
{%- endmacro %}
现在测试它。然后，在 dbt project 内的一个新 file 中，可以通过传入期望的 x 和 y values 来 call macro，如 Example 5-9 所示。
Example 5-9：Trigger the macro inside macro_sum_two_values.sql
{{ sum(13, 89) }}
Example 5-9 会在 macro 被触发的位置，将 macro 的结果（102）呈现在 output window 中。你也可以向 macro 传入 variables 或 expressions，而不是 hardcoded values。看 Example 5-10，它必须产生与前一个示例相同的 output。
Example 5-10：Trigger the macro inside macro_sum_two_values.sql，先在顶部定义 variables
{% set x = 13 %}
{% set y = 89 %}
{{ sum(x, y) }}
在 dbt project 中结合 Jinja 使用 macros，可以让你复用 code，并以灵活且强大的方式 customize models。现在，让我们在示例中使用 macros。使用 jaffle_shop database，我们第一个要处理的 use case 是创建一个 macro，用于集中配置 payment types，避免像之前新版 int_payment_type_amount_per_order.sql 中那样在每个 model 中定义它。为此，在 macros directory 中创建一个名为 get_payment_types.sql 的新 file，并复制 Example 5-11 中的代码。
Example 5-11：get_payment_types.sql macro
{% macro get_payment_types() %}
{{ return(["cash", "credit"]) }}
{% endmacro %}
然后，在 int_payment_type_amount_per_order.sql model 中，用 Example 5-12 中的代码替换顶部声明的 payment_types variable。
Example 5-12：int_payment_type_amount_per_order.sql payment_types variable declaration calling the get_payment_types() macro
{%- set payment_types= get_payment_types() -%}
现在，你可以在其他 use cases 中使用这个 macro，但需要考虑以下几点：
通常，macros 会接收 arguments。因此，虽然 Example 5-11 是一个 macro，但它并不代表你通常会构建的 macro。Arguments 指的是在 macro 被 called 或 executed 时传入的 values。这些 arguments 可用于修改 macro 的 behavior，例如指定 input data sources、定义 custom configuration settings，或设置某些 parameters 或 flags。
在 Example 5-11 中，我们使用了 return function 来返回一个 list；如果没有这个 function，macro 会返回一个 string。
回顾刚才的操作，Example 5-11 中的 macro 看起来并不强大，也有些 static。如何优化它，避免依赖 manual inputs？我们可以这样做：
- 理解 source data，从中可以动态拉取 payment types。
- 用 modular mindset 重新思考 macro。
我们可以使用 Example 5-13 中的 query 来解决第一点。
Example 5-13：Get distinct payment types query
select
distinct payment_type
from {{ ref('stg_stripe_order_payments') }}
order by 1
可以看到，如果运行该 query，它会得到 distinct payment types。为了把它做成 macro，将 Example 5-14 中的代码复制到 get_payment_types.sql file 中。
Example 5-14：让 get_payment_types 更 dynamic 和 scalable 的新版本
{% macro get_payment_types() %}
{% set payment_type_query %}
select
distinct payment_type
from {{ ref('stg_stripe_order_payments') }}
order by 1
{% endset %}
{% set results = run_query(payment_type_query) %}
{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}
{{ return(results_list) }}
{% endmacro %}
看看我们做了什么：
- 顶部声明了 query
payment_type_query。 - 随后使用
run_queryfunction 执行它，并将 output 存储到resultsvariable 中。 - 然后，检查 Jinja 是否处于 execute mode，也就是 SQL 正在被执行。如果是，则将 dataset 第一列的结果存储到
results_list中。这个第一列就是包含 distinct values 的列。 - 最后，返回
results_listvariable，使其可以在 models 中使用。
现在，如果再次 compile int_payment_type_amount_per_order.sql model，结果不应发生变化。不过，你已经实现了更 scalable 的代码。此时，如果出现新的 payment type，就不再需要 manual input。但我们还能在 modularity 上做得更多。想象你想在 dbt project 的其他地方使用类似 pattern，例如 payment methods。在这种情况下，可以像 Example 5-15 这样做。
Example 5-15：针对不同 scenarios 多次复用代码
{# Generic macro to give a column name and table, outputs
the distinct fields of the given column name #}
{% macro get_column_values(column_name, table_name) %}
{% set relation_query %}
select distinct
{{ column_name }}
from {{ table_name }}
order by 1
{% endset %}
{% set results = run_query(relation_query) %}
{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}
{{ return(results_list) }}
{% endmacro %}
{# Macro to get the distinct payment_types #}
{% macro get_payment_types() %}
{{ return(get_column_values('payment_type', ref('stg_stripe_order_payments'))) }}
{% endmacro %}
{# Macro to get the distinct payment_methods #}
{% macro get_payment_methods() %}
{{ return(get_column_values('payment_method', ref('stg_stripe_order_payments'))) }}
{% endmacro %}
分析这段代码，可以看到三个 macros。第一个 get_column_values() 接收 column_name 和 table_name 作为 arguments，并动态生成一个 query 来执行，返回给定 column_name 的 distinct values。接下来，我们实现了对该 macro 的两个独立调用：通过 get_payment_types() macro 获取 distinct payment_types，通过 get_payment_methods() macro 获取 distinct payment_methods。还要注意，macro filename 改为了 get_distinct_by_column.sql，以便更清楚地表达它的目的。
前面的例子展示了如何使用 macros，但 macros 在更多场景中也非常有用。另一个好例子是创建一个 macro，用于动态验证我们当前处于 development 还是 deployment environment，然后自动过滤 dataset。为此，在 macros directory 中创建一个名为 limit_dataset_if_not_deploy_env.sql 的新 macro，并复制 Example 5-16 中的代码。
Example 5-16：limit_dataset_if_not_deploy_env macro
{# Macro that considering the target name,
limits the amount of data queried for the nbr_months_of_data defined #}
{% macro limit_dataset_if_not_deploy_env(column_name, nbr_months_of_data) %}
-- limit the amount of data queried if not in the deploy environment.
{% if target.name != 'deploy' %}
where {{ column_name }} > DATE_SUB(CURRENT_DATE(), INTERVAL {{ nbr_months_of_data }}
MONTH)
{% endif %}
{% endmacro %}
然后，在 fct_orders model 中，在 left join 后面的底部加入 Example 5-17 中的代码。
Example 5-17：从 fct_orders 调用 limit_dataset_if_not_deploy_env macro
with orders as (
select * from {{ ref('stg_jaffle_shop_orders' )}}
),
payment_type_orders as (
select * from {{ ref('int_payment_type_amount_per_order' )}}
)
select
ord.order_id,
ord.customer_id,
ord.order_date,
pto.cash_amount,
pto.credit_amount,
pto.total_amount,
case
when status = 'completed'
then 1
else 0
end as is_order_completed
from orders as ord
left join payment_type_orders as pto ON ord.order_id = pto.order_id
-- Add macro here
{{- limit_dataset_if_not_deploy_env('order_date', 3) }}
现在 compile 代码。如果你处于 development environment，fct_orders.sql model 应该显示一个新的 filter，即 where order_date > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)。换句话说，这个 filter 允许代码区分 environments：如果不是 deployment environment，就只处理最近三个月的数据；否则，收集整个 dataset。在 development environment 中只查看 N 个月数据，可以显著减少 data platform 的 overhead，同时你仍然拥有一个足够好的 data subset，用于开发和测试代码。如果这还不够，可以增加到 12、24，甚至 36 个月。
最后，必须提到 dbt 的 adaptability，也就是它可以 customize core macros。这些 macros 服务于 dbt 的一些 core functionalities，为常见任务提供 predefined templates。一个突出示例是 generate_schema_name macro。这个 macro 负责为 dbt models 创建 schema names。真正出色的是，你可以调整它，使其与你 project 独特的 naming conventions 无缝对齐。想象一下，它可以轻松生成映射组织 data structure 的 schema names。
Customizing 这些 core macros 不只是技术能力。它是你如何发挥 dbt capabilities 的 game-changer，可以释放能力，构建一个与你 project needs 精确对齐的 data transformation process。
总之，在 dbt project 中使用 macros，是一种强大且高效的方式，用于自动化和定制 data models 与 processes。Macros 允许你轻松复用 code，并让 models 适应不同 contexts 和 requirements。使用 Jinja syntax，可以创建 flexible 且 easy-to-maintain 的 macros，并以多种方式调用和触发它们。总体而言，macros 可以帮助你提升 productivity，并创建更 robust 和 scalable 的 data models。
dbt Packages
Packages 是一种在 dbt project 中组织和共享已经编写好的 code 与 resources 的方式，例如 models 和 macros。它们允许你将 project 结构化为 logical units，并在多个 models 和 files 中复用 code 和 resources。
在 dbt project 中，packages 定义在 packages.yml file 中，安装到 dbt_packages directory 中，并使用 directories 和 files 的层级结构组织。每个 package 可以包含 models、tests、macros 和其他 resources，这些内容与特定 topic 或 functionality 相关。
Packages 可以在 dbt project 中以多种方式使用。例如，你可能使用 packages 来完成以下事情：
Organize your project into logical units
Packages 可以帮助你以直观且易理解的方式组织 project，将相关 models、tests 和其他 resources 分组在一起。
Reuse code and resources
Packages 允许你在多个 models 和 files 中复用 code 和 resources，节省时间并减少 maintenance overhead。
Encapsulate functionality
Packages 可以帮助你封装 specific functionality，并向 project 其他部分隐藏 implementation details，使 project 更 modular、更容易理解。
Share code and resources with others
Packages 可以与其他 users 或 projects 共享，使你能够利用他人的工作，并通过贡献自己的 code 成为 community 的一部分。
总体而言，packages 是 dbt 的一项有价值功能，可以帮助你在 project 中组织、复用和共享 code 与 resources。你可以从三个不同位置安装 dbt packages：public packages hub、Git 或 local directory。在本节中，我们将介绍如何安装 packages，并展示一些使用示例。我们将使用最常见的 packages 之一 dbt_utils，但请注意，外面还有许多优秀 packages。你可以在 dbt Hub 找到大量 packages，也可以直接从 GitHub 导入它们。
Installing Packages
在 dbt project 中安装 package 是一个直接的过程，它可以帮助你利用他人的工作，并为 project 添加新 functionalities。前面已经概览过这一点，现在进一步讨论 package installation。
安装 package 的 step-by-step guide 如下：
- 如果还没有
packages.ymlfile，请创建它。这个 file 是配置 dbt project 中需要安装的 dbt packages 的地方。 - 将 package 添加到
packages.ymlfile：在 dbt project 中打开该 file，并为你想安装的 package 添加 entry。请记住，在安装 package 前，确保 dbt project 满足该 package 可能具有的 requirements 或 dependencies 很重要。这些可能包括特定 dbt 版本或其他 packages，以及 system 或 software requirements。 - 在 terminal 中运行
dbt depscommand 安装 package。它会把 package 及其 dependencies 安装到 dbt project 中。 - 测试 package，确保它正常工作。你可以运行
dbt testcommand，并验证 package 的 models 和 tests 是否通过。
让我们尝试安装最常用的 packages 之一：dbt_utils，你可以在 dbt Hub 找到它。通常，使用 public packages hub 会提供某个特定 dbt package 需要放入 packages.yml file 的全部 configurations，使安装更顺畅。因此，为了安装 dbt_utils，将 Example 5-18 中的配置复制到 packages.yml file 中。
Example 5-18：dbt_utils package configuration
packages:
- package: dbt-labs/dbt_utils
version: 1.1.1
保存 YAML file，并在 CLI 中运行 dbt deps。如果看到 success message，如图 5-7 所示，就说明一切正常。稍后本章会使用 dbt_utils 来看看一切是否按预期运行。
图 5-7：安装 dbt_utils 后 logs 中的 success message
NOTE
如果你收到 package 与 dbt version 不兼容的问题，请确保当前运行的 dbt 版本与想使用的 package 兼容。查看 package documentation 或 repository，了解支持的 dbt versions。你也可以更新 package version，使其与 dbt version 兼容。最后，选择提供类似 functionality 且与你 dbt version 兼容的 alternative packages 也可以是一种方案。
Exploring the dbt_utils Package
首先，让我们认识用于示例的 package：dbt_utils。这个 package 由 dbt 的创建者 dbt Labs 开发并维护。它包含一组 utility functions、macros 和其他 resources，用于扩展和增强 dbt project 的功能。
以下是 dbt_utils package 中包含的资源类型示例：
- Helper functions，用于执行 common tasks，例如生成 columns list、格式化 dates 和 timestamps，以及处理 null values。
- Custom data types，用于以更 expressively 和 flexibly 的方式表示 data，例如 arrays、ranges 和 intervals。
- Debugging 和 testing tools，用于 dbt projects，例如 logging functions 和 test frameworks。
- Macros 和 models，用于执行广泛 tasks，例如 data manipulation、visualization 和 testing。
总之，dbt_utils 是一个对 dbt users 很有帮助的 package，适用于希望以多种方式扩展和定制 projects 的场景。它持续更新和扩展，不断加入 new features 和 resources。
Using Packages Inside Macros and Models
在 dbt project 中，可以在 macros 内使用 packages，以访问 package 中定义的其他 macros、models、tests 和 resources。这使你可以在多个 models 和 files 中复用 code 和 resources，让 project 更 modular，从而让 code 更 DRY。
Package 安装完成后，正如 “Installing Packages” 中所述，可以使用 package name 作为 prefix 来访问其 macros，并遵循特定 syntax，如 Example 5-19 所示。
Example 5-19：Sample macro call
{{ package.my_macro() }}
使用 dbt_utils，我们可以在 database 中生成一系列 numbers 或 dates，这对多种 use cases 很有用。让我们看一个实际示例。尝试使用 date_spine() macro。复制 Example 5-20 中的代码并执行。
Example 5-20：dbt_utils 中的 date_spine macro
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('2023-01-01' as date)",
end_date="cast('2023-02-01' as date)"
)
}}
预期 output 是从 2023 年 1 月 1 日到 2023 年 2 月 1 日之前的日期列表，不包含 2 月 1 日。date_spine macro 是一个有效且灵活的 function，可以帮助你处理 dates、生成 dates sequence，或执行其他涉及 dates 的任务，例如在 analytics model 中创建 dim_date dimension。
另一个 use case 是直接在你开发的 models 中使用已安装 packages。例如，假设你想计算某个 specific order 中 cash_amount 占比，但需要确保在 total_amount 为 0 的 orders 中，代码不会因为 division-by-zero error 而中断。你当然可以自己实现这个逻辑，但 dbt_utils 已经有一个内置 function 可以处理它。让我们看 Example 5-21 中的代码。
Example 5-21：dbt_utils 中的 safe_divide macro
select
order_id,
customer_id,
cash_amount,
total_amount,
{{ dbt_utils.safe_divide('cash_amount', 'total_amount') }}
from {{ ref('fct_orders') }}
这段代码使用 safe_divide macro，用 numerator cash_amount 除以 denominator total_amount，并将结果存储到一个名为 result 的 variable 中。如果 denominator 为 0 或 null，safe_divide macro 会返回 null，而不是 raise error。
safe_divide macro 非常适合在 dbt project 中执行 division operations，尤其是在处理可能包含 null 或 0 values 的 data 时。它可以节省时间并减少 maintenance overhead，因为不再需要手动检查 null 或 0 values。
dbt packages 是一个多用途工具，帮助你构建更好、更高效的 data transformation pipelines。本章中，我们介绍了 dbt_utils，它提供了一组有用 macros 和 functions，可以简化 common data modeling tasks，是工具箱中的重要补充。另一个有趣 package 是 dbt_expectations，它使你能够定义、文档化并测试 data expectations，从而确保 data quality 和 reliability。此外，dbt_date 简化 data models 中与 dates 相关的 calculations 和 manipulations。通过利用这些 packages 以及其他 packages，你可以简化 code sharing 和 collaboration，减少重复劳动，并创建更 scalable 和 maintainable 的 data models。
dbt Semantic Layer
在 data analytics 中，semantic layer 扮演关键角色，它是 raw data 和 meaningful insights 之间的桥梁。这种 logical abstraction 是一个关键 translator，它简化 complex data structures，并促进整个组织对 data 的共同理解。通过这样做，它把复杂 database setups 转换为 user-friendly language，使包括 data analysts 和 business leaders 在内的多元 audience 能轻松访问和理解 data。除了简化之外，semantic layer 还提供 data integrity 和 reliability，确保 data 既 understandable 又 trustworthy。
dbt semantic layer 的本质，使其从根本上区别于 conventional semantic layers。在许多 semantic layers 中，users 通过明确指定 left 和 right join keys 来划定 data 中的 connections。然而，dbt semantic layer specification 采用一种独特方法，引入了 entities。这些 entities 使我们能够在 layer 内自动推断 data connections，也就是 graph’s edges。例如，考虑一个 customer table，其 primary key 为 customer_id，以及一个 orders table，其 foreign key entity 为 customer_id。这可以形成一个 relationship，或者更准确地说，是 data graph 中的一条 edge。这一创新显著减少了 manual logic maintenance 的需要，因为 graph 中通常 nodes 少于 edges。
这种 approach 的美妙之处在于它的 simplicity 和 efficiency。它以极其 DRY 的方式封装 semantic logic，支持更广泛的 metric 和 dimension combinations，并生成更干净的 SQL。这些优势使 data teams 更容易监督、演进和利用 data models。
dbt semantic layer 的核心有两个 essential components：semantic models 和 metrics。Semantic models 是 foundational building blocks，由三个关键元素组成：entities、dimensions 和 measures，用于创建 metric。这些 components 赋能 MetricFlow，也就是驱动 semantic layer 的 framework，让它可以构建用于定义 metrics 的 queries。
另一方面，metrics 是我们用于 measure 和 analyze data 的工具。它们运行在 semantic models 之上，使我们可以基于 reusable components 创建 sophisticated 和 elaborate definitions。
如前所述，semantic layer 依赖三个基本概念来创建 metrics：entities、dimensions 和 measures。
Entity 指特定 context 中独立且可识别的 object。在 databases 的语言中，entities 通常对应 tables，是我们 data collection efforts 的核心主题。Entities 表示 business 中的 real-world concepts，例如 customers 或 orders。在 semantic models 中，entities 使用 ID columns 表示，这些 ID columns 作为 join keys，与 semantic graph 中的其他 semantic models 连接。
Entities 对帮助 Semantic Engine 理解 tables 或 datasets 之间的 relationships 至关重要。这使 engine 能理解 data 如何互联，并确保当 query 涉及某个 specific entity 时，engine 知道应该从哪里 retrieve relevant information。
另一方面，dimensions 通过作为 categorical attributes 为 measures 提供 context，使 data 在 analysis 过程中可以用不同方式 breakdown。Dimensions 通常描述 model 内其他 elements 相关的 characteristics。
Dimensions 被配置为赋能 users 从不同 perspectives 探索和分析 data。Semantic Engine 利用这些 dimensions，根据 user preferences 定制 queries。
最后，measures 是 quantifiable data points，是 analysis 的主要关注对象，表示我们打算检查的 metrics。Measures 通常会被 aggregation，而且在很多情况下，BI tool 的基本作用就是跨各种 dimensions 聚合这些 measures。Measures 的定义确保 calculations 在所有 queries 和 reports 中保持一致，消除任何 semantic ambiguity。
让我们展示如何构建 dbt semantic layer。继续使用 customers 和 orders entities 的示例。我们想 measure total amount paid（total_amount），并拆分为 cash paid（cash_amount）和 credit paid（credit_amount）。最后，我们还想获得 total number of orders made（order_count），以及 customers with orders 的数量（customers_with_orders）。我们还希望具备按 day（order_date）切片的能力，并知道 orders 是否 completed（is_order_completed）。
考虑这些 requirements，完整 semantic model 如 Example 5-22 所示。你可以将它添加到之前创建的相应 YAML file _core_models.yml，或为 semantic model 创建一个新的 file。
Example 5-22：semantic model 的 YAML file configuration
semantic_models:
- name: orders
description: |
Order fact table. This table is at the order grain with one row per order.
model: ref('fct_orders')
entities:
- name: order_id
type: primary
- name: customer
type: foreign
expr: customer_id
dimensions:
- name: order_date
type: time
type_params:
time_granularity: day
- name: is_order_completed
type: categorical
measures:
- name: total_amount
description: Total amount paid by the customer with successful payment
status.
agg: sum
- name: order_count
expr: 1
agg: sum
- name: cash_amount
description: Total amount paid in cash by the customer with successful
payment status.
agg: sum
- name: credit_amount
description: Total amount paid in credit by the customer with successful
payment status.
agg: sum
- name: customers_with_orders
description: Distinct count of customers placing orders
agg: count_distinct
expr: customer_id
进入最后阶段后，前面覆盖的所有 components 都需要 Semantic Engine 参与，才能 operationalize 它们。这个 engine 在解释所提供 data，并根据这些 definitions 构建 analytical queries 方面发挥基础作用。例如，即使我们已经细致指定了 customer orders 的所有方面，仍然依赖一个 engine 来 parse semantic model，并生成计算 desired metrics 的 query。在 dbt 的领域内，这个 function 由 MetricFlow 实现。
Semantic Engine 概念类似于 dbt Documentation Engine。当你为 model 创建 YAML file 时，它本身是 inert 的，缺乏显著 functionality。不过，dbt Documentation Engine 会将这些 data 转换为实用工具，包括 documentation website、dbt tests、alert systems、data contracts 等。类似地，MetricFlow 作为 dbt Semantic Engine 运行，利用其能力解释 semantic data，并生成有价值 outcomes，尤其是 standardized 和 reusable analytical queries。
要使用 MetricFlow 生成 analytical queries，初始步骤是基于你精心构建的 semantic model 建立 metrics。你可以在与 semantic models 相同的 YAML files 中定义 metrics，也可以创建一个新 file。
为了说明 metrics creation 的过程，先明确我们打算开发的 specific metrics。为了保持简单但仍有趣，可以包含一个计算 orders total amount 的 metric（order_total）。此外，也可以创建另一个 metric，用于跟踪 placed orders 的数量（order_count）。最后，我们会探索一个 metric，它基于 placed orders 的 count，对 metric 本身进行 filter，揭示 completed orders 占 placed orders 的哪一部分。Example 5-23 提供了一个 YAML file，展示这些 metrics 的正确配置。
Example 5-23：Metrics YAML file configuration
metrics:
- name: order_total
description: Sum of total order amount.
type: simple
label: Order Total
type_params:
measure: total_amount
- name: order_count
description: Count of orders.
label: Orders
type: simple
type_params:
measure: order_count
- name: completed_orders
description: Count of orders that were delivered
label: Delivered Orders
type: simple
type_params:
measure: order_count
filter: |
{{ Dimension('order_id__is_order_completed') }} = true
举例来说，要让 MetricFlow 在 order_total 上工作，可以使用 CLI command：mf query --metric order_total。MetricFlow 会将该 definition 与 measure 的 definition，也就是 semantic model 中定义的内容一起解释，并生成 Example 5-24 中的 query。
Example 5-24：Order total query
SELECT SUM(total_amount) as order_total
FROM fct_orders
NOTE
虽然本章旨在展示 semantic layer 在 dbt 中的工作方式，但请注意，对于 organization-wide deployment 来说，mf query 可能不是最佳选择。为了在组织范围内更广泛、更 robust 地使用，可以考虑使用 dbt 提供的 APIs。此外，建议参考 “Set Up the dbt Semantic Layer” 页面，以获得 semantic layer 和 MetricFlow 最新、最准确的安装说明，因为该页面会定期更新最新信息和进展。
建立 dbt semantic layer 后，你实际上已经在 data 之上创建了一个 abstraction layer。不管 orders dataset 发生任何修改，任何想获得 total order amount 的人，都可以轻松访问 order_total metric。这使 users 能够根据自身 specific requirements 分析 orders data。
Summary
本章中，我们深入研究了 dbt 世界中的 advanced topics，扩展了对这个 transformation tool 的理解。我们探索了 dbt models 和 materializations 的力量，了解它们如何帮助我们管理 complex data transformations，同时确保高效的 performance optimization。使用 Jinja 的 dynamic SQL 使我们能够创建 dynamic 和 reusable queries，使它们适应 changing requirements，从而增强 data processes 的 agility。
在 fundamentals 之外，我们介绍了 SQL macros，解锁了 codebase 中新的 automation 和 reusability 层级。通过有启发性的示例，我们看到 SQL macros 如何大幅简化代码，并为 data transformations 带来 consistency。
此外，dbt packages 的概念成为 data ecosystem 中 collaboration 和 knowledge sharing 的基石。我们讨论了 dbt packages 如何让我们封装 logic、best practices 和 reusable code，从而培养 collaboration culture，并加快 development cycles。
最后，我们展示了 dbt semantic layer 如何通过在 data 上提供 abstraction layer 来增强 analytics solution。由于 business logic 被集中并在 semantic layer 中验证，这一层可以确保所有 reports 和 analyses 之间的 consistency 和 exactness，从而最小化 disparities 或 mistakes 的风险。此外，随着 database 扩展或发生修改，拥有 semantic layer 可以让你在单一位置完成调整，避免逐个更新大量 reports 或 queries。
本章结束时，我们已经走过多个 advanced dbt topics，掌握了优化 data processes 所需的知识和工具。这些高级概念提升了我们的 data transformations，并赋能我们将 data analytics 提升到前所未有的高度。借助这些 insights，我们能够更胜任地应对 data challenges 的复杂性，并推动 data-driven endeavors 中的 innovation。
不过，需要注意的是，虽然本章已经是 dbt 的 comprehensive guide，但 dbt universe 非常广阔，并且持续演进。还有多个额外主题值得探索，例如 blue/green、canary 或 shadow deployments 等 advanced deployment techniques。此外，深入了解 Write-Audit-Process（WAP）patterns 的使用，可以让 teams 更好地控制 data quality 和 traceability。同样，探索 dbt 如何与 data ecosystem 中其他 tools 交互也很有价值，还包括理解如何在 multiproject organizations 中工作。确实，dbt 是一个动态且广阔的世界；在这段令人兴奋的数据旅程中，总有更多内容值得学习和发现。
