学习如何通过 dbt 的高级测试技术来保障数据质量。探索内置测试、单体（singular）测试和可复用测试，以验证模型、sources 和 seeds。了解如何使用 Jinja 定义自定义测试、排查失败原因，并优化验证工作流，在问题影响下游分析之前及时发现不一致。

dbt 中的测试简介

选项

内置

非内置

内置测试

nyc_yellow_taxi:
  outputs:
    dev:
      type: duckdb
      path: dbt.duckdb
  target: dev



# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'taxi_project'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'nyc_yellow_taxi'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  nyc_yellow_taxi:
  # Config indicated by + and applies to all files under models/example/
    taxi_rides:
    +materialized: view


{{ config(materialized='view')}}


with source_data as (
    select * from read_parquet('yellow_tripdata_2023-01-partial.parquet')
)

select * from source_data


version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: fare_amount
      tests:
        - not_null
    # Enter the name of the column
    - name: ____
    # Define the two tests
      tests:
        - ____
        - ____
            ____: [1, 2, 3, ____]


Run the following commands in the terminal:
  
dbt run
dbt test

version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: fare_amount
      tests:
        - not_null
    # Enter the name of the column
    - name: payment_type
    # Define the two tests
      tests:
        - not_null
        - accepted_values:
            values: [1, 2, 3, 4, 5, 6] 


在模型上定义测试

{{ config(materialized='view')}}


with source_data as (
    select * from read_parquet('yellow_tripdata_2023-01-partial.parquet')
)

-- Add a filter / WHERE clause to the line below
select * from source_data ____


version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: fare_amount
      tests:
        - not_null
    - name: payment_type
      tests:
        - not_null
        - accepted_values:
            values: [1, 2, 3, 4, 5, 6]


Run the following commands in the terminal:
  
dbt run

{{ config(materialized='view')}}


with source_data as (
    select * from read_parquet('yellow_tripdata_2023-01-partial.parquet')
)

-- Add a filter / WHERE clause to the line below
select * from source_data where payment_type != 0


Run the following command in the terminal:
  
dbt run

查找异常数据

创建单独测试

单一测试的开发流程

开发单一测试的步骤

select * 
from taxi_rides_raw
-- Complete the test on the following line


Run the following command in the terminal:
  
dbt test --select assert_trip_duration_gt_0.sql

select * 
from taxi_rides_raw
-- Complete the test on the following line
where tpep_pickup_datetime = tpep_dropoff_datetime


The appropriate filter has been added to assert_trip_duration_gt_0.sql

Run the following command in the terminal:
  
dbt test --select assert_trip_duration_gt_0.sql

验证行程时长

有效的测试查询

无效的测试查询

验证测试查询

创建可复用的自定义测试

Options

Reusable

Singular

Testing，testing，testing

version: 2

# Replace any entry of ____ with the appropriate content
models:
- name: taxi_rides_raw
  columns:
    - name: fare_amount
      tests:
        - ____
    - name: ____
      tests:
        - check_gt_0
        


{% test ____(model, column_name) %}
select * 
from {{ model }}
where {{ column_name }} <= 0
{% endtest %}



version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: fare_amount
      tests:
        - check_gt_0
    - name: total_amount
      tests:
        - check_gt_0


{% test check_gt_0(model, column_name) %}
select * 
from {{ model }}
where {{ column_name }} <= 0
{% endtest %}


- Update the check_gt_0.sql file as shown in the solution. (Note, the last character is a zero)
- Update the model_properties.yml file as shown.
- Run dbt run in the terminal window
- Run dbt test --select taxi_rides_raw or dbt test --select taxi_rides_raw.sql in the terminal window

实现可复用测试

version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: tpep_pickup_datetime
      tests:
        - ____:
            ____: ____


select * 
from taxi_rides_raw
where tpep_pickup_datetime = tpep_dropoff_datetime


{% test ____(model, column_name, column_name2) %}
select *
from {{ model }}
____
{% endtest %}


version: 2

models:
- name: taxi_rides_raw
  columns:
    - name: tpep_pickup_datetime
      tests:
        - columns_equal:
            column_name2: tpep_dropoff_datetime


{% test columns_equal(model, column_name, column_name2) %}
select *
from {{ model }}
where {{ column_name }} = {{ column_name2 }}
{% endtest %}


- Edit the columns_equal.sql file as shown.
- Edit the model_properties.yml file as shown.
- Run dbt run in the terminal.
- Run dbt test --select taxi_rides_raw.sql or dbt test --select taxi_rides_raw in the terminal.

将单一测试更新为可复用测试

测试与文档

通过构建可扩展、可投入生产的工作流，将您的 dbt 技能更进一步。学习使用 dbt sources 和 seeds 改进数据血缘，利用 snapshots 跟踪历史变化，并用 dbt build 实现转换自动化。完成后，您将具备自信来管理大规模数据管道。

dbt sources

Models、sources，还是二者皆是？

添加一个 source

dbt seeds

真相的内核

ZIP is the code

使用 dbt snapshots 实现 SCD2（旧）

快照流程

快照问题

添加一个快照

使用 dbt build 实现自动化

dbt build 不能做什么？

帮实习生一把！

把一切串起来

在生产环境中实施 dbt

通过这门面向数据工程师和分析专业人士的实践课程，进一步提升您的 dbt 技能。您将探索 dbt 的高级功能，包括自定义测试策略、可复用测试、sources、seeds，以及用于跟踪历史数据变化的 snapshots。学习如何使用 dbt build 自动化 dbt 流水线，确保在生产环境中实现可靠且高效的转换。借助交互式练习和真实场景，您将有信心在大规模数据工作流中实施 dbt。

<h2>掌握 dbt 工作流</h2>
<p>将你的 dbt 技能提升到新水平，学习如何在生产环境中实施稳健、可扩展的数据转换工作流。 本课程专为希望超越基础并获得高级 dbt 功能实战经验的数据工程师、分析师和分析工程师而设计。</p>

<h2>通过高级测试确保数据质量</h2>
<p>构建可靠的数据管道始于验证。 你将探索高级测试技术，以捕捉数据不一致，创建可重复使用的自定义测试，以标准化跨模型的验证，并将测试应用于源和种子，以实现更好的治理和数据血缘跟踪。</p>

<h2>利用 dbt Sources、Seeds 和 Snapshots</h2>
<p>了解 dbt sources 如何在确保原始数据可追溯性的同时，提升文档和数据血缘。 学习使用 dbt seeds 高效管理小型静态数据集。 然后，掌握使用 dbt snapshots 的缓慢变化维度（SCD2），让你能够以最少的工作量跟踪数据仓库中的历史变化。</p>

<h2>使用 dbt Build 实现自动化与优化</h2>
<p>在生产环境中，效率至关重要。 您将学习如何使用 dbt build 简化工作流程，自动化模型执行、测试和快照，以确保转换的可靠性。 通过优化您的数据管道，您将提升 dbt 项目的性能、可维护性和可扩展性。</p>

<h2>在真实场景中应用你的技能</h2>
<p>通过互动练习和动手实践，你将巩固所学知识，并获得在真实场景中应用 dbt 的信心。 到课程结束时，你将能够设计、测试并自动化可投入生产的 dbt 工作流，确保在大规模场景下实现高质量且文档完善的转换。</p>


Introduction to dbt

通过此实践课程提升您的dbt技能，专为数据工程师和分析专业人士设计。

dbt 中级

通过这门面向数据工程师和分析专业人士的实操课程，将你的 dbt 技能提升到新水平。

dbt 基础知识

`SELECT * FROM taxi_rides_raw WHERE tpep_pickup_datetime = tpep_dropoff_datetime`

`SELECT * FROM taxi_rides_raw WHERE tpep_dropoff_datetime - tpep_pickup_datetime = 0`

`SELECT * FROM taxi_rides_raw WHERE tpep_dropoff_datetime - tpep_pickup_datetime >= 0`

`SELECT * FROM taxi_rides_raw WHERE tpep_dropoff_datetime > tpep_pickup_datetime`

验证测试查询

dbt 中级

动手互动练习