dbt utilsを使ったテストをいくつかやってみるよ
これはデータの品質を担保するために最近dbt testを調査していたので、その備忘録です。
dbt test
Generic Test
unique test
データの値がユークであること
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- unique
not_null test
データの値にnull値が無いこと
tests:
- not_null
accepted_values test
データの値がvaluesで指定した値であること（values値以外の値はエラーとなる）
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
relationships test
relationshipsは参照生合成をチェックするテストです。
下記テストクエリの場合、ordersテーブルのcustomer_idは参照先のstg_customersテーブルのcustomer_idに存在する（同じ値）と言う意味のテストです。
つまり、ordersテーブルのcustomer_idはstg_customersテーブルのcustomer_idを基にしたデータであることをテストしています。
version: 2
models:
- name: orders
columns:
- name: customer_id
- relationships:
to: ref('stg_customers')
field: customer_id
Singular Test
Singular test実施方法
基本的にtestsディレクトリは以下にSQLファイルとしてテストクエリを記載する。
select amount from orders where amount > 100
ここでの注意は、テストクエリに何も出力してはいけない点です。
何故かと言うと、実際に実行されたテストクエリを見ればわかります。
以下が実際のdbt Singular testで実行されたテストクエリです。
服問い合わせで問い合わせたselect amount from orders where amount > 100の結果にもし、値が返っていればcountが0以上となり、エラーとなります。
select
count(*) as failures,
count(*) != 0 as should_warn,
count(*) != 0 as should_error
from (
select amount from orders where amount > 100
) dbt_internal_test
dbt utils
パッケージ入れ方
packages:
- package: dbt-labs/dbt_utils
version: 1.1.1
Generic Tests
equal_rowcount
2つのリレーションが同じ行数を持つことを保証する。
※カラムに対してtestsは書けない
version: 2
models:
- name: orders
tests:
- dbt_utils.equal_rowcount:
compare_model: ref('stg_orders')
equal_rowcountテストクエリ
with a as (
select
1 as id_dbtutils_test_equal_rowcount,
count(*) as count_a
from "jaffle_shop"."main"."orders"
group by id_dbtutils_test_equal_rowcount
),
b as (
select
1 as id_dbtutils_test_equal_rowcount,
count(*) as count_b
from "jaffle_shop"."main"."stg_orders"
group by id_dbtutils_test_equal_rowcount
),
final as (
select
a.id_dbtutils_test_equal_rowcount as id_dbtutils_test_equal_rowcount_a,
b.id_dbtutils_test_equal_rowcount as id_dbtutils_test_equal_rowcount_b,
count_a,
count_b,
abs(count_a - count_b) as diff_count
from a
full join b
on
a.id_dbtutils_test_equal_rowcount = b.id_dbtutils_test_equal_rowcount
)
select * from final
fewer_rows_than
それぞれのモデルの行数が比較対象のモデルよりも少ないことを表明する。
ordersモデルの行数がcustomersより少ないことをテストする。
version: 2
models:
- name: orders
tests:
- dbt_utils.fewer_rows_than:
compare_model: ref('customers')
fewer_rows_thanテストクエリ
with a as (
select
1 as id_dbtutils_test_fewer_rows_than,
count(*) as count_our_model
from "jaffle_shop"."main"."orders"
group by id_dbtutils_test_fewer_rows_than
),
b as (
select
1 as id_dbtutils_test_fewer_rows_than,
count(*) as count_comparison_model
from "jaffle_shop"."main"."customers"
group by id_dbtutils_test_fewer_rows_than
),
counts as (
select
a.id_dbtutils_test_fewer_rows_than as id_dbtutils_test_fewer_rows_than_a,
b.id_dbtutils_test_fewer_rows_than as id_dbtutils_test_fewer_rows_than_b,
count_our_model,
count_comparison_model
from a
full join b on
a.id_dbtutils_test_fewer_rows_than = b.id_dbtutils_test_fewer_rows_than
),
final as (
select *,
case
-- fail the test if we have more rows than the reference model and return the row count delta
when count_our_model > count_comparison_model then (count_our_model - count_comparison_model)
-- fail the test if they are the same number
when count_our_model = count_comparison_model then 1
-- pass the test if the delta is positive (i.e. return the number 0)
else 0
end as row_count_delta
from counts
)
select * from final
equality
2つのリレーションシップが等しいことを保証する。比較する列のサブセットを指定することもできる。
ordersテーブルのorder_idとstg_ordersのrder_idのデータと取得し、差分が無いかテストする。
version: 2
models:
- name: orders
tests:
- dbt_utils.equality:
compare_model: ref('stg_orders')
compare_columns:
- order_id
- {カラムを複数指定する場合はここに対象のカラムを記載}
equalityテストクエリ
with a as (
select * from "jaffle_shop"."main"."orders"
),
b as (
select * from "jaffle_shop"."main"."stg_orders"
),
a_minus_b as (
select order_id from a
except
select order_id from b
),
b_minus_a as (
select order_id from b
except
select order_id from a
),
unioned as (
select 'a_minus_b' as which_diff, a_minus_b.* from a_minus_b
union all
select 'b_minus_a' as which_diff, b_minus_a.* from b_minus_a
)
select * from unioned
expression_is_true
有効なSQL式がすべてのレコードで真であることを保証します。
これは、カラム間の整合性をチェックする際に便利です。
- 列間の基本的な代数演算の適用に基づく結果を検証する。
- 列の長さを検証する。
- 列の真理値を検証する。
カラム間での計算式チェック
jaffle_shopのorderテーブルを参考にします。
ordersテーブルには4種類の支払い方法で支払った金額カラムが存在します。
どれか一つで支払えば、他の支払い方法カラム金額には数値は存在しません。
そのため、4つの値の合計値が合計金額（amount）と一致しているはずです。
それを検証する計算式を入れて確認します。
version: 2
models:
- name: orders
tests:
- dbt_utils.expression_is_true:
expression: "(credit_card_amount + coupon_amount + bank_transfer_amount + gift_card_amount) = amount"
計算式チェックテストクエリ
select
1
from "jaffle_shop"."main"."orders"
where not((credit_card_amount + coupon_amount + bank_transfer_amount + gift_card_amount) = amount)
カラムの長さチェック
金額カラム（amount）の値が3桁未満であることをチェックする。
version: 2
models:
- name: orders
columns:
- name: amount
description: Total amount (AUD) of the order
tests:
- not_null
- dbt_utils.expression_is_true:
expression: '< 100'
カラム長さチェックテストクエリ
select
1
from "jaffle_shop"."main"."orders"
where not(amount < 100)
ちなみにINT32型でないとエラーとなる
version: 2
models:
- name: orders
columns:
- name: status
tests:
- dbt_utils.expression_is_true:
expression: '<100'
> Executing task: dbt test --select dbt_utils_expression_is_true_orders_status___100
00:23:45 Running with dbt=1.6.1
00:23:46 Registered adapter: duckdb=1.6.0
00:23:47 Found 5 models, 29 tests, 3 seeds, 0 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
00:23:47
00:23:47 Concurrency: 1 threads (target='dev')
00:23:47
00:23:47 1 of 1 START test dbt_utils_expression_is_true_orders_status___100 ............. [RUN]
00:23:47 1 of 1 ERROR dbt_utils_expression_is_true_orders_status___100 .................. [ERROR in 0.09s]
00:23:47
00:23:47 Finished running 1 test in 0 hours 0 minutes and 0.20 seconds (0.20s).
00:23:47
00:23:47 Completed with 1 error and 0 warnings:
00:23:47
00:23:47 Runtime Error in test dbt_utils_expression_is_true_orders_status___100 (models/orders.yml)
00:23:47 Conversion Error: Could not convert string 'returned' to INT32
00:23:47
00:23:47 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
条件付きチェック
whereオプションを設定することで、とあるカラムの条件下でチェックすることができます。
例えば、以下だと、gift_card_amount値が２以上でmaount値が100未満となるチェックを行う。
version: 2
models:
- name: orders
columns:
- name: amount
- dbt_utils.expression_is_true:
expression: '<100'
config:
where: gift_card_amount > 2
条件付きチェックテストクエリ
select
1
from (select * from "jaffle_shop"."main"."orders" where gift_card_amount > 2) dbt_subquery
where not(amount <100)
recency
参照モデルのタイムスタンプ列が、少なくとも定義された日付間隔と同じ最近のデータを含んでいることを保証する。
オプション説明
- datepart : 日付単位を設定（day,minute,など）
- dield : タイムスタンプ列を設定する
- interval : 更新間隔を設定する
更新間隔が1日なら以下のクエリとなる
version: 2
models:
- name: orders
tests:
- dbt_utils.recency:
datepart: day
field: order_date
interval: 1
recencyテストクエリ
with recency as (
select
max(order_date) as most_recent
from "jaffle_shop"."main"."orders"
)
selecz
most_recent,
cast(
now() + ((interval '1 day') * (-1))
as TIMESTAMP) as threshold
from recency
where most_recent < cast(
now() + ((interval '1 day') * (-1))
as TIMESTAMP)
at_least_one
カラムが少なくとも1つの値を持つことを保証する。
全部空だとエラーになります。
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- dbt_utils.at_least_one
at_least_oneテストクエリ
select *
from (
with pruned_rows as (
select
order_id
from "jaffle_shop"."main"."orders"
where order_id is not null
limit 1
)
select
count(order_id) as filler_column
from pruned_rows
having count(order_id) = 0
) validation_errors
not_constant
カラムがすべての行で同じ値を持たないことを保証する。
って書いているけど、同じ値持ってるカラムあるけどエラーにならないの、なぁぜなぁぜ？
version: 2
models:
- name: orders
- name: status
tests:
- dbt_utils.not_constant
not_constantテストクエリ
select
count(distinct status) as filler_column
from "jaffle_shop"."main"."orders"
having count(distinct status) = 1
not_empty_string
カラムに '' と等しい値がないことを保証します。
version: 2
models:
columns:
- name: order_id
tests:
- dbt_utils.not_empty_string
not_empty_stringテストクエリ
with
all_values as (
select
trim(order_id) as order_id
from "jaffle_shop"."main"."orders"
),
errors as (
select * from all_values
where order_id = ''
)
select * from errors
cardinality_equality
与えられたカラムの値が、異なるモデルの異なるカラムの値とまったく同じカーディナリティを持つことを保証する。
※カーディナリティとは、特定のデータベーステーブル内の行の数を表す用語
なので、テストでは指定のカラムのカーディナリティ（行の数）を取得し、異なるテーブルと比較している。
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- dbt_utils.cardinality_equality:
field: customer_id
to: ref('stg_orders')
cardinality_equalityテストクエリ
with table_a as (
select
order_id,
count(*) as num_rows
from "jaffle_shop"."main"."orders"
group by order_id
),
table_b as (
select
order_id,
count(*) as num_rows
from "jaffle_shop"."main"."stg_orders"
group by order_id
),
except_a as (
select *
from table_a
except
select *
from table_b
),
except_b as (
select *
from table_b
except
select *
from table_a
),
unioned as (
select *
from except_a
union all
select *
from except_b
)
select *
from unioned
not_null_proportion
カラムに存在する非NULL値の割合が指定された範囲[at_least, at_most]の間にあることを表明します。
以下クエリはNULL値が95%%以下を許容する。
version: 2
models:
columns:
- name: order_id
tests:
- dbt_utils.not_null_proportion:
at_least: 0.95
not_null_proportionテストクエリ
with validation as (
select
sum(case when order_id is null then 0 else 1 end) / cast(count(*) as numeric) as not_null_proportion
from "jaffle_shop"."main"."orders"
),
validation_errors as (
select
not_null_proportion
from validation
where not_null_proportion < 0.95 or not_null_proportion > 1
)
select
*
from validation_errors
not_accepted_values
与えられた値に一致する行がないことを表明する。
以下クエリでは、order_idに存在しないID番号を指定しておりテストは成功する。
逆にすでに存在しているID値「10」などを指定するとエラーとなる。
version: 2
models:
columns:
- name: order_id
tests:
- dbt_utils.not_accepted_values:
values: ['120']
not_accepted_valuesテストクエリ
with all_values as (
select distinct
order_id as value_field
from "jaffle_shop"."main"."orders"
),
validation_errors as (
select
value_field
from all_values
where value_field in (
'120'
)
)
select *
from validation_errors
relationships_where
2つのリレーション間の参照整合性をアサーションします (コアリレーションシップのアサーションと同じです)。
これは、テスト用のエンティティや、ETL の制限による一時的なずれを考慮して過去 X 分/時間に作成された行などのレコードを除外するのに便利です。
ちょっとよく分からない・・・
version: 2
models:
- name: model_name
columns:
- name: id
tests:
- dbt_utils.relationships_where:
to: ref('other_model_name')
field: client_id
from_condition: id <> '4ca448b8-24bf-4b88-96c6-b1609499c38b'
to_condition: created_date >= '2020-01-01'
mutually_exclusive_ranges
与えられたlower_bound_columnとupper_bound_columnについて、下限と上限の範囲が他の行の範囲と重複しないことを保証する。
あとで調査・・・
version: 2
models:
# test that age ranges do not overlap
- name: age_brackets
tests:
- dbt_utils.mutually_exclusive_ranges:
lower_bound_column: min_age
upper_bound_column: max_age
gaps: not_allowed
# test that each customer can only have one subscription at a time
- name: subscriptions
tests:
- dbt_utils.mutually_exclusive_ranges:
lower_bound_column: started_at
upper_bound_column: ended_at
partition_by: customer_id
gaps: required
# test that each customer can have subscriptions that start and end on the same date
- name: subscriptions
tests:
- dbt_utils.mutually_exclusive_ranges:
lower_bound_column: started_at
upper_bound_column: ended_at
partition_by: customer_id
zero_length_range_allowed: true
sequential_values
このテストは、カラムが連続した値を含むことを確認します。このテストは、以下のように、数値と日時値の両方に使用できます。
例えばorder_idのシーケンシャルのインターバルが1づつ増えることをチェックするクエリは以下となる。
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- dbt_utils.sequential_values:
interval: 1
sequential_valuesテストクエリ
with windowed as (
select
order_id,
lag(order_id) over (
order by order_id
) as previous_order_id
from "jaffle_shop"."main"."orders"
),
validation_errors as (
select
*
from windowed
where not(order_id = previous_order_id + 1)
)
select *
from validation_errors
それ以外にも日時で取得しているデータの日付カラムに対して、以下のようにチェックする事もできる。
- name: util_hours
columns:
- name: date_hour
tests:
- dbt_utils.sequential_values:
interval: 1
datepart: 'day'
unique_combination_of_columns
列の組み合わせが一意であることを主張する。例えば、月と商品の組み合わせは一意ですが、どちらの列も単独では一意ではありません。
一般的には、この一意性の条件をテストする方法として、次のいずれかを推奨します：
- サロゲートキーを生成し、そのキーの一意性をテストする。
- 一意性テストにカラムの連結を渡す（ここで説明したように）
しかし、これらの方法は大規模なデータセットでは実行不可能になる可能性があり、その場合は代わりにこのテストを使用することを推奨します。
エラーを起こしたかったが、できなくて一旦後回し
- name: revenue_by_product_by_month
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- month
- product
accepted_range
カラムの値が期待される範囲内にあることを保証します。
min_valueとmax_valueの組み合わせは何でもよく、範囲は包括的でも排他的でもよい。
特定のレコードのみに絞り込むにはwhere引数を指定します。
スカラー値との比較に加えて、別のカラムの値との比較も可能です。
演算子 > や < をサポートするデータ型であれば、どのようなものでも比較することができるので、注文日がすべて過去であるかどうかをチェックするようなテストを実行することもできます。
以下は金額カラムの最小値最大値を設定し、チェックするクエリ
version: 2
models:
- name: orders
- name: amount
tests:
- dbt_utils.accepted_range:
min_value: 0
max_value: 10
accepted_rangeテストクエリ
with meet_condition as(
select *
from "jaffle_shop"."main"."orders"
),
validation_errors as (
select *
from meet_condition
where
-- never true, defaults to an empty result set. Exists to ensure any combo of the `or` clauses below succeeds
1 = 2
-- records with a value >= min_value are permitted. The `not` flips this to find records that don't meet the rule.
or not amount >= 0
-- records with a value <= max_value are permitted. The `not` flips this to find records that don't meet the rule.
or not amount <= 100
)
select *
from validation_errors
version: 2
models:
- name: model_name
columns:
- name: user_id
tests:
- dbt_utils.accepted_range:
min_value: 0
inclusive: false
- name: account_created_at
tests:
- dbt_utils.accepted_range:
max_value: "getdate()"
#inclusive is true by default
- name: num_returned_orders
tests:
- dbt_utils.accepted_range:
min_value: 0
max_value: "num_orders"
- name: num_web_sessions
tests:
- dbt_utils.accepted_range:
min_value: 0
inclusive: false
config:
where: "num_orders > 0"
Discussion