學習如何用 dbt 的進階測試技巧來確保資料品質。探索內建、singular，以及可重複使用的測試，來驗證 models、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 是關鍵

使用 dbt snapshots 的 SCD2（舊版）

建立 Snapshot 的流程

Snapshot 流程

Snapshot 問題

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

新增一個 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 精簡工作流程，自動化模型執行、測試與快照，確保轉換過程的可靠性。 透過優化您的 pipeline，您將提升 dbt 專案的效能、可維護性與擴展性。</p>

<h2>在真實情境中應用您的技能</h2>
<p>透過互動式練習與實作，你將鞏固所學知識，並建立信心，能夠在實際情境中運用 dbt。 在本課程結束時，您將具備設計、測試及自動化可投入生產環境的 dbt 工作流程的能力，確保大規模轉換具備高品質且有完善文件紀錄。</p>


Introduction to dbt

透過此實作課程提升您的dbt技能，專為資料工程師和分析專業人士設計。

dbt 中級

提升你的dbt技能，專為資料工程師與分析專業人士打造的實作課程。

column_name	description
license_id	分配給計程車公司的數值型 ID
vehicle_make	車輛的製造商
vehicle_model	車輛的型號
vehicle_year	車輛的出廠年份
last_updated	此筆記錄最後修改的日期

新增一個 snapshot

dbt 中級

動手互動練習