编写高质量dbt模型实践指南

数据建模是越来越受关注的话题，尤其是在分析工程领域。数据建模和流行的数据转换工具dbt是相辅相成的。虽然数据建模概念已经存在很长时间，但dbt让它更具体、更易实现。

可以将数据模型理解为一系列转换，这些转换将数据从原始形式转化为最终可用的数据。编写数据模型已经成为分析工程师的主要职责，因为他们有能力理解技术概念和业务流程。DBT支持编写模块化的数据模型，支持测试和重用，从而使分析工程师的工作变得更加轻松。

前面博客主要介绍dbt中具体特定功能，缺少综合示例。在本文结合简单示例提供综合实践，介绍利用dbt编写分层数据模型的最佳实践，以及如何使用编写模型测试保障数据质量。如果阅读中遇到不清楚的内容，可以参考阅读数据分析工程专栏中其他文章，如dbt source, 模型测试等。

什么是dbt数据建模?

dbt数据建模是基于SQL工具库将原始数据转换为可用数据的过程。目标就是把原始数据转换为开发易于理解、清晰的、结构化和文档完备的数据集。

DBT简化了转换步骤，从而允许创建准确、可靠的数据模型，这些模型可以很容易地进行测试和版本控制。通过这种方式，数据团队可以使用dbt创建高效且可扩展的数据模型，从而能够及时交付高质量数据，提升数据价值、支持业务决策。

组织source数据

构建数据模型，首先需要确定数据source，可以从source或直接从外部源摄取数据开始。dbt不是直接在模型中使用这些数据，而是帮助在sources和模型之间创建过度层。通过这种方式原始数据的副本始终存在于数据仓库中，在模型中正式使用source之前，需要先正确地定义它。

定义 dbt source

通常在项目根目录下的sources.yaml文件中定义。最佳实践是在“models”目录下的“staging”目录中为每个源创建一个目录，这样source定义和staging模型位置较近。

在YAML文件中，使用数据库名称和模式名称定义源，然后为其定义名称以供下游使用。示例sources.yml文件如下：

version: 2sources:- name: rawdatabase: marketing schema: raw tables:- name: customer_orders- name: marketing_campaigns

dbt模型中使用的每个数据源都需要在YAML文件中定义。如果没有定义，在下游模型引用原始数据时，dbt将不知道去哪里查找。

引用源代码

在用dbt编写staging模型时，每个模型都将引用类似于我们上面定义的source。请注意，源应该只在staging模型中被引用。

引用语法如下:

{{ source(‘raw’, ‘customer_orders’) }}

source引用需要两个信息：源名称和表名称。如果查看我们定义的YAML文件，可以看到“raw”指的是源名称，“customer_orders”指的是表名称。

source文档化

最后对dbt模型中使用的所有source进行文档化描述。这也是在定义source的YAML文件中完成，就像我们上面写的那样。不仅可以向源名称添加描述，还可以向每个表名和列名添加描述。建议从开始就遵循最佳实践，这样你就不用担心以后再看时完全忘记source的描述和业务涵义。

完整的文档将帮助团队中的其他人理解你正在编写的代码，同时将降低问题和知识转移难度。一般来说，编写文档总是值得花费时间的，因为它会在后期项目开发和维护中提供清晰的信息。

source文档描述示例:

version: 2sources:- name: rawdatabase: marketing schema: raw description: This is a replica of the Postgres database used by our apptables:- name: customer_ordersdescription: >Inventory of orders placed by customers. columns:- name: iddescription: Primary key of the customer orders table- name: customer_iddescription: The unique identifier of a customer- name: order_iddescription: The unique identifier of an order- name: product_quantitydescription: The number of products in the order

这里不仅描述数据源、表，还包括字段的描述。当然我们可以用中文进行描述，这里仅仅为示例。

创建staging模型

dbt使用所谓的staging模型来提升原始数据源的完整性。Staging模型从我们定义的这些原始数据源中读取数据，然后在下游数据模型中引用。分层模型不仅是为了保护原始数据，而且有助于对其进行标准化。在staging层中，我们可以跨各种数据源对数据进行标准化，并将公司标准落实到位。

例如，可以将所有日期列强制转换为“date”数据类型，并以“_date”结尾，以便查询数据的人知道该列的预期内容。数据类型强制转换和列重命名等不同的函数在这里是最常见的。这层主要实现公司标准，不涉及到业务处理，尽可能保持简单。

示例如下：

SELECT Id AS customer_order_id, Customer_id::varchar AS customer_id, Order_id::varchar AS order_id, Product_quantity
FROM {{ source(‘raw’, ‘customer_orders’) }}

这里仅从source表读取数据，并进行数据类型转换、和重命名，不涉及到具体业务处理。

DBT数据建模最佳实践

定义好source并为每个source编写了staging模型，就可以进一步构建数据模型了。构建dbt模型的下一步是关注core模型，即生成数据分析师将直接查询的数据集模型。

这些模型包含更高级的SQL转换，如连接、聚合和窗口函数。与staging模型不同，你几乎可以做任何想做的事情来生成所需的结果。但为了充分利用dbt的强大功能，应该遵循一些最佳实践。

模块化编写模型

dbt最大优势是支持编写模块化代码。前面我们讨论了source和staging模型，数据仓库通常定义的多维模型我们定义在中间层（或者简称为dw层），上层应用直接使用为core或mart层。我们可以重构已有的代码，以便在其他数据模型中重复使用，这样可以节省时间和计算资源! 中间模型包括可以复用的模型，分析用户一般从不直接查询中间模型数据，而是基于中间模型生成模块化core数据模型。

模型层次关系

在前节中我们提到，不要直接从source读取数据，因此，core模型应该只从staging模型读取数据。这将有助于维护原始数据的完整性，并能通过DAG跟踪dbt中不同模型之间的关系。

提示：如果你的模型没有使用{{source()}}或{{ref()}}宏函数，那么你应该做错了!

上层应用模型

数据分析人员使用的模型应该存储在Models目录中的core或mart目录中，dbt中的所有模型都是按来源或业务目的组织的。所有不是staging模型的模型都称为core模型。我们为这些模型创建相应目录，并在这里为每个业务功能创建子目录，从而高效组织多个复杂的业务模型。

请记住，core模型是数据分析师和业务用户直接使用的最终模型，报告和仪表板从中读取数据，而staging模型和中间模型更适合分析工程师使用。

SQL最佳实践

dbt利用SQL编写不同层的数据模型。在编写dbt模型时必须遵循SQL最佳实践。这会使数据模型代码保持干净和简洁，易理解、易重用。下面通过实例优化SQL模型：

SELECT customer_orders.Customer_order_id, customer_orders.Customer_id, customer_orders.Order_id 
FROM {{ source(‘raw’, ‘customer_orders’) }} customer_orders
WHERE referrer_campaign_id IN (SELECT marketing_campaign_id FROM {{ source(‘raw’, ‘marketing_campaigns’) }} WHERE platform=’Facebook’)

优化后代码如下：

SELECT customer_orders.Customer_order_id, customer_orders.Customer_id, customer_orders.Order_id 
FROM {{ source(‘raw’, ‘customer_orders’) }} customer_orders
INNER JOIN {{ source(‘raw’, ‘marketing_campaigns’) }}  marketing_campaigns
ON customer_orders.referrer_campaign_id = marketing_campaigns.marketing_campaign_id 
WHERE marketing_campaigns.platform=’Facebook’

使用连接比子查询更易阅，我们应该优先考虑可读性的SQL函数，这是编写高质量dbt数据模型的关键。

CTE命名约定

继续说CTE的概念，给它们命名有意义的名称尤为重要，有意义的CTE名称会让你更容易理解。建议使用动词来进行命名，例如，如果在一个CTE中连接了两个表，CTE命名以_joined_为前缀，这让读者知道为什么要写这个CTE。

少用缩写命名

在连接两个表时，重要的是要弄清楚列来自哪个表。为此，最好的方法是拼出完整的表名，或者至少在连接中使用与原始表名相似的名称。有人习惯采用随机字母的表别名，这会增加代码阅读难度，尤其是连接的表超过两个时，下面通过示例说明：

SELECT cust_orders.Customer_order_id, cust_orders.Customer_id, cust_orders.Order_id 
FROM {{ source(‘raw’, ‘customer_orders’) }} cust_orders
INNER JOIN {{ source(‘raw’, ‘marketing_campaigns’) }}  makt_campaigns
ON cust_orders.referrer_campaign_id = makt_campaigns.marketing_campaign_id

下面是随机别名方式：

SELECT a.Customer_order_id, a.Customer_id, a.Order_id 
FROM {{ source(‘raw’, ‘customer_orders’) }} a
INNER JOIN {{ source(‘raw’, ‘marketing_campaigns’) }}  b
ON a.referrer_campaign_id = b.marketing_campaign_id

第一个选项更容易阅读，并消除了在解释代码时可能出现的任何错误!

模型测试

dbt编写模型的另一个优势内置测试能力。DBT可以很容易地在yaml文档中定义测试。它包括内置的通用测试，这些测试最基础的，可以测试空值、唯一值等，还可以编写自定义测试。

测试source

在使用dbt进行测试时，一定要先为source编写测试。这样确保在将其用于任何下游数据模型之前满足预期。通过在source定义测试并提前暴露错误，可以减少后续调试过程。

想想看——如果只测试数据模型，遇到问题你可能会认为是模型逻辑导致的。但如果模型中使用了错误的数据，则问题可以追溯到更远的地方，而不是化大量时间调试模式代码逻辑。

对于source数据，通常使用dbt通用测试，包括:

Unique
Not_null
Accepted_values
Relationships

建议在所有source列上添加必要的通用测试，这样可以收到意外的空列或重复数据警报。示例如下:

version: 2sources:- name: rawdatabase: marketing schema: raw description: This is a replica of the Postgres database used by our apptables:- name: customer_ordersdescription: >Inventory of orders placed by customers. columns:- name: iddescription: Primary key of the customer orders tableTests:- unique- not_null- name: customer_iddescription: The unique identifier of a customerTests:- not_null- name: order_iddescription: The unique identifier of an orderTests:- unique- not_null- name: product_quantitydescription: The number of products in the  order

添加必要的通用测试可以确保在数据上运行正确的查询。当然不能为了测试而添加不必要的测试，否则会收到大量不准确的警报，导致警报疲劳。

测试模型

与source模型类似，也必须在dbt中测试数据模型。向模型中添加测试将有助于更好地掌握由于代码本身造成的问题，如有可能是不正确的逻辑或上游数据模型中的问题。我们可以为每个模型定义测试捕获问题，这样避免需要调试整个DAG数据链路。

当在模型中添加not_null和unique测试时，建议也使用 accepted_values 和 Relationships 测试。这些测试有助于确保没有任何意外从你的逻辑裂缝中溜走。accepted_values测试可以断言字段的期望值列表，如果dbt在该测试中遇到未指定的值，则测试将失败。

在单个字段中添加测试:

- name: order_type_idDescription: The unique id of the order typeTests:- Accepted_values : Values: [‘1’, ‘2’, ‘3’]

现在，如果order_type_id的值不是1、2或3，测试将失败，dbt将抛出一个错误。

关系测试可以设置不同模型之间以及模型和source之间的关系，从而确保连接按预期工作。实际上检查以确保正在测试字段中的所有值也存在于与之相关的模型或源中的字段中。

关系示例如下：

- name: order_iddescription: The unique identifier of an orderTests:- unique- Not_null- Relationships:To: ref(‘orders’)Field: order_id

上面示例中，dbt将检查模型中的order_id列中的每个值也可以在orders模型中的order_id列中找到，如果它在此列中发现一个不在orders表中的值，则测试将失败。

模型综合示例

假设有关客户订单的原始数据存储在名为raw.orders的表中。你希望获取这些原始数据并对其进行清理，以便删除任何无效的订单，并增强数据以供分析。这是dbt模型SQL文件:

-- models/orders.sqlwith raw_data as (select  * from   {{ source('raw', 'orders') }}
),renamed_data as (selectid as order_id,userid as customer_id,orderdate as order_date,statusfrom  raw_data
),final_orders as (selectorder_id,customer_id,order_date,casewhen status = 1 then 'Pending'when status = 2 then 'Processing'when status = 3 then 'Shipped'else 'Unknown'end as order_statusfrom   renamed_data
)select  * 
from  final_orders

抽取源数据

raw_data的CTE(Common Table Expression)从原始数据中选择所有列。orders表使用{{source('raw ‘，’ orders ')}}引用，该函数指向dbt项目中定义的原始数据源。

重命名列

renamed_data的CTE将列重命名为更有意义的名称。例如，id更改为order_id, userid更改为customer_id。这一步使数据更具可读性，更易于使用。

数据转换

final_orders的CTE包含CASE语句，它将数字状态码转换为文本描述。例如，status = 1与 'Pending’相关联，status = 2与 ‘Processing’ 相关联，status = 3与 'Shipped’相关联。这种转换有助于使订单状态更易于理解。

最后select

最后的SELECT * FROM final_orders语句，引用前面步骤中所有转换和选择，并生成了一个干净且丰富的数据集，适合于业务分析。这个示例展示了如何使用dbt模型来清理原始数据，并以更易分析的方式对其进行结构化。

结论

dbt的强大之处在于能够编写模块化代码，并在模型yaml文件中定义文档和测试。DBT支持定义source，使用source编写staging模型、中间模型和core模型，同时描述文档、定义测试。在构建dbt数据模型时，不仅要遵循最佳实践，还要遵循编写SQL代码的最佳实践。期待您的真诚反馈，更多内容请阅读数据分析工程专栏。