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

dbt 中的测试简介

内置测试

在模型上定义测试

查找异常数据

创建单独测试

开发单一测试的步骤

验证行程时长

验证测试查询

创建可复用的自定义测试

Testing，testing，testing

实现可复用测试

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

测试与文档

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

dbt sources

可选项

仅 dbt models

models 与 sources 共有

仅 dbt sources

Models、sources，还是二者皆是？

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')}}

select * from ____
        


version: 2

sources:
  - name: ____
    tables:
      - name: taxi_rides


#!/usr/bin/env python3
import duckdb
con = duckdb.connect('dbt.duckdb', read_only=True)
print(con.sql('select * from taxi_rides_raw'))
print(con.sql('select count(*) from taxi_rides_raw'))
if (con.execute('select count(*) from taxi_rides_raw').fetchall()[0][0] == 300000):
  with open('/home/repl/workspace/successful_data_check', 'w') as f:
    f.write('300000')


Run the following commands in the terminal:
  
dbt run
datacheck

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

select * from {{ source('raw', 'taxi_rides') }}
        


version: 2

sources:
  - name: raw
    tables:
      - name: taxi_rides


Run the following commands in the terminal:
  
dbt run
./datacheck

添加一个 source

dbt seeds

真相的内核

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


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

select * from source_data


version: 2

seeds:
  - name: ____
    config:
      column_types:
        ____: ____(5)


#!/usr/bin/env python3
import duckdb
import os.path

try:
  con = duckdb.connect('dbt.duckdb', read_only=True)
  print(con.sql('select * from nynj_zipcodes'))
  if (con.execute('select count(*) as total_zipcodes from nynj_zipcodes').fetchall()[0][0] == 2877):
    print("Your data counts are correct!")
    with open('/home/repl/workspace/successful_data_check', 'w') as f:
      f.write('2877')
  else:
    print("There appears to be an issue with your data counts.")
except duckdb.CatalogException:
  if not os.path.isfile('/home/repl/workspace/nyc_yellow_taxi/seeds/nynj_zipcodes.csv'):
    print("It looks like the nynj_zipcodes.csv file is\n not present in the seeds/ directory.\n\nPlease move the file and try again.\n")
  else:
    print("It looks like your data warehouse doesn't exist yet\n\n Please run the dbt seed command and try again.\n")

version: 2

seeds:
  - name: nynj_zipcodes
    config:
      column_types:
        zipcode: varchar(5)


#!/usr/bin/env python3
import duckdb
con = duckdb.connect('dbt.duckdb', read_only=True)
print(con.sql('select * from nynj_zipcodes'))
if (con.execute('select * as total_zipcodes from nynj_zipcodes').fetchall()[0][1] == 2877):
  with open('/home/repl/workspace/successful_data_check', 'w') as f:
    f.write('2877')


- Run the following command in the terminal window (or drag and drop the nynj_zipcodes.csv file into the seeds folder using the file explorer window.)

mv nynj_zipcodes.csv seeds/

- Modify the properties.yml file as described in the solution.

- Run dbt seed in the terminal window.

- Run the command ./datacheck in the terminal window and verify you see zipcode information in the printed table.

ZIP is the code

使用 dbt snapshots 实现 SCD2（旧）

快照创建流程

快照流程

快照问题

version: 2

sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


____

{{
    config(
      target_schema='main',
      unique_key='____',
      strategy='timestamp',
      updated_at='____'
    )
}}

select * from ____
{% endsnapshot %}


{% snapshot vehicle_list_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='license_id',
      strategy='timestamp',
      updated_at='last_updated'
    )
}}

select * from {{ source('raw', 'vehicle_list') }}
{% endsnapshot %}


- Edit the snapshot_vehicle_list.sql file as shown
- Run the command dbt snapshot

添加一个快照

使用 dbt build 实现自动化

选项

dbt build 会执行该步骤

dbt build 不会运行该项

dbt build 不能做什么？


# 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: 'nyc_yellow_taxi'
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')}}

select * from 'taxi_rides'
        


{% snapshot vehicle_list_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='license_id',
      strategy='timestamp',
      updated_at='last_updated'
    )
}}

select * from {{ source('new', 'vehicle_list') }}
{% endsnapshot %}


with avg_daily_fare as (
  select date_part('day', tpep_pickup_datetime) as day,
         avg(total_amount) as avg_amount
  from {{ ref('taxi_rides_raw') }}
  group by day
  order by day
)
select * from avg_daily_fare


version: 2

models:
  - name: taxi_rides_raw
    description: Initial import of the NYC Yellow Taxi trip data from Parquet source
    access: public
  - name: avg_ride_cost_per_day
    description: Avg amount spent per ride, per day
    access: public

sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


version: 2

models:
  - name: taxi_rides_raw
    description: Initial import of the NYC Yellow Taxi trip data from Parquet source
    access: public
  - name: avg_ride_cost_per_day
    description: Avg amount spent per ride, per day
    access: public
sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


- Run dbt build and look for errors. You should see an issue in the snapshots/vehicle_list_snapshot.sql.

- Edit this file as shown.

- Run dbt build again. You should see errors relating to the models/taxi_rides_raw.sql file.

- Edit this file as shown.

- Run dbt build once more and you should not see any errors.

帮实习生一把！

Run the following commands in the terminal:

dbt build
dbt docs generate
dbt docs serve

把一切串起来

在生产环境中实施 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 基础知识

可在 `models/model_properties.yml` 中定义。

通过 `{{ source(source_name, table_name) }}` 调用。

Models、sources，还是二者皆是？

dbt 中级

动手互动练习