🕌

dbtを可視化できるのはelementaryだけじゃない！

2023/09/18に公開

PipeRiderのご紹介記事です。以前elementaryというツールの記事を書きましたが、Piperiderでも同じことができそう？？
もしくはどこまでできる？どんな違いがある？といった疑問点もあるので、そのあたり確認してみたいと思います。

PipeRiderについて

https://docs.piperider.io/

PipeRiderはdbtデータプロジェクトのデータ影響評価ツールである
コード変更による下流への影響を確認できるため、本番環境を中断することなく変更をマージできる
データの比較と差分を提供し、データプロファイリング統計と影響の概要を並べてコード変更の影響範囲を視覚化し、dbtプロジェクトの更新を確認するのに役立ちます

※23年の9月時点では、PIPERIDERクラウド版がありますが、ベータ版のようです。

特徴

PipeRiderデータプロファイルレポート機能（データを全体的に確認する）
PipeRiderデータ影響レポート（コード変更の前後の影響をリッチHTMLレポートで確認できる）
プルリクエストの影響の概要（コード変更の影響箇所）

サポートしているコネクタ

Athena
BigQuery
Databricks
DuckDB
PostgreSQL
Redshift
Snowflake

PipeRiderのインストール

pip3 install -U 'piperider[duckdb]'

チュートリアルの実施

PipeRider構成を確認する

root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop# piperider diagnose
Diagnosing...
PipeRider Version: 0.33.1
Check config files:
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/config.yml: [OK]
✅ PASS

Check format of data sources:
  dev: [OK]
✅ PASS

Check connections:
  DBT: [OK]
    Version: 1.6.1
    Adapter: duckdb
    Profile: jaffle_shop
    Target:  dev
  Name: dev
  Type: duckdb
  Connector: [OK]
  Connection: [OK]
✅ PASS

Check assertion files:
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_customers.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_raw_orders.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_orders.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_stg_payments.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_stg_orders.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_stg_customers.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_raw_customers.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_date_spine.yml: [OK]
  /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/assertions/recommended_raw_payments.yml: [OK]
✅ PASS

🎉 You are all set!

root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop#

🎉 You are all set!が表示されればOK

モデルにタグを付けてプロファイリングを有効にする

dbt_project.yml

models:
  jaffle_shop:
      +tags: piperider
      materialized: table
      staging:
        materialized: view

プロファイリングを有効にしたことで、統計情報を参照できるようになります。

データ影響レポートの作成

チュートリアルに従って、コードの差分レポートがどのように出力されるのか見てみます。

新しくブランチを切り、適当にカラムを追加する

ブランチを切る

root@3f7c4a32a705:/home/dbt# git checkout -b feature/add-average-value-per-order
Switched to a new branch 'feature/add-average-value-per-order'

下図のようにcustomersモデルにカラムを追加します。

piperider compare実行（ログ出力が長いので省略）

root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop# piperider compare
────────────────────────────────────────────────────────────────────────── Recipe executor: generate recipe ───────────────────────────────────────────────────────────────────────────
1 base:
2 branch: main
3 dbt:
4 commands:
5 - dbt deps
6 - dbt build
7 piperider:
8 command: piperider run
9 target:
10 dbt:
11 commands:
12 - dbt deps
13 - dbt build --select state:modified+ --state <DBT_STATE_PATH>
14 piperider:
15 command: piperider run
16
──────────────────────────────────────────────────────────────────────────────────── End of Recipe ────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────── Recipe executor: verify execution environments ────────────────────────────────────────────────────────────────────
Check: git
Check: dbt
─────────────────────────────────────────────────────────────────── Recipe executor: prepare execution environments ───────────────────────────────────────────────────────────────────
execute_command_in_silent git archive --format=tar --output=/tmp/tmpwwqb_441/8a9568d1f2efc60dd9f27f29426f9a8f03386ef1.tar 8a9568d1f2efc60dd9f27f29426f9a8f03386ef1
git archive --format=tar --output=/tmp/tmpwwqb_441/8a9568d1f2efc60dd9f27f29426f9a8f03386ef1.tar 8a9568d1f2efc60dd9f27f29426f9a8f03386ef1
Run: [dbt deps]
07:29:54 Running with dbt=1.6.1
07:29:56 Installing calogica/dbt_expectations
07:29:58 Installed from version 0.9.0
07:29:58 Up to date!
07:29:58 Installing dbt-labs/dbt_utils
07:29:59 Installed from version 1.1.1
07:29:59 Up to date!
07:29:59 Installing calogica/dbt_date
07:30:00 Installed from version 0.8.1
07:30:00 Updated version available: 0.9.1
07:30:00
07:30:00 Updates available for packages: ['calogica/dbt_date']
Update your versions in packages.yml, then run dbt deps

Run: [dbt compile]
07:30:04 Running with dbt=1.6.1
07:30:04 Registered adapter: duckdb=1.6.0
07:30:04 Unable to do partial parsing because saved manifest not found. Starting full parse.
07:30:07 Found 6 models, 26 tests, 3 seeds, 0 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
07:30:07
07:30:07 Concurrency: 1 threads (target='dev')
07:30:07

Run: [dbt deps]
07:30:11 Running with dbt=1.6.1
07:30:12 Installing calogica/dbt_expectations
07:30:15 Installed from version 0.9.0
07:30:15 Up to date!
07:30:15 Installing dbt-labs/dbt_utils
07:30:15 Installed from version 1.1.1
07:30:15 Up to date!
07:30:15 Installing calogica/dbt_date
07:30:16 Installed from version 0.8.1
07:30:16 Updated version available: 0.9.1
07:30:16
07:30:16 Updates available for packages: ['calogica/dbt_date']
Update your versions in packages.yml, then run dbt deps

Run: [dbt compile]
07:30:20 Running with dbt=1.6.1
07:30:20 Registered adapter: duckdb=1.6.0
07:30:20 Found 6 models, 26 tests, 3 seeds, 0 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
07:30:20
07:30:20 Concurrency: 1 threads (target='dev')
07:30:20

Run: [dbt list] select option 'state:modified+' with state
07:30:22 Registered adapter: duckdb=1.6.0

Config: piperider env "PIPERIDER_DBT_RESOURCES"
───────────────────────────────────────────────────────────────────────────── Recipe executor: base phase ─────────────────────────────────────────────────────────────────────────────
Run: [git archive] main...HEAD = 8a9568d1f2efc60dd9f27f29426f9a8f03386ef1

Run: [dbt deps]
07:30:27 Running with dbt=1.6.1
07:30:29 Installing calogica/dbt_expectations
07:30:31 Installed from version 0.9.0
07:30:31 Up to date!
07:30:31 Installing dbt-labs/dbt_utils
07:30:32 Installed from version 1.1.1
07:30:32 Up to date!
07:30:32 Installing calogica/dbt_date
07:30:32 Installed from version 0.8.1
07:30:32 Updated version available: 0.9.1
07:30:32
07:30:32 Updates available for packages: ['calogica/dbt_date']
Update your versions in packages.yml, then run dbt deps

Run: [dbt build]
07:30:36 Running with dbt=1.6.1
07:30:36 Registered adapter: duckdb=1.6.0
07:30:36 Unable to do partial parsing because saved manifest not found. Starting full parse.
07:30:39 Found 6 models, 26 tests, 3 seeds, 0 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
07:30:39
07:30:39 Concurrency: 1 threads (target='dev')
07:30:39
07:30:39 1 of 35 START sql table model main.date_spine .................................. [RUN]
07:30:39 1 of 35 OK created sql table model main.date_spine ............................. [OK in 0.25s]
07:30:39 2 of 35 START seed file main.raw_customers ..................................... [RUN]
07:30:39 2 of 35 OK loaded seed file main.raw_customers ................................. [INSERT 100 in 0.11s]
07:30:39 3 of 35 START seed file main.raw_orders ........................................ [RUN]
07:30:39 3 of 35 OK loaded seed file main.raw_orders .................................... [INSERT 100 in 0.06s]
07:30:39 4 of 35 START seed file main.raw_payments ...................................... [RUN]
07:30:39 4 of 35 OK loaded seed file main.raw_payments .................................. [INSERT 113 in 0.06s]
07:30:39 5 of 35 START test singular_test ............................................... [RUN]
07:30:39 5 of 35 PASS singular_test ..................................................... [PASS in 0.06s]
07:30:39 6 of 35 START sql view model main.stg_customers ................................ [RUN]
07:30:40 6 of 35 OK created sql view model main.stg_customers ........................... [OK in 0.11s]
07:30:40 7 of 35 START sql view model main.stg_orders ................................... [RUN]
07:30:40 7 of 35 OK created sql view model main.stg_orders .............................. [OK in 0.07s]
07:30:40 8 of 35 START sql view model main.stg_payments ................................. [RUN]
07:30:40 8 of 35 OK created sql view model main.stg_payments ............................ [OK in 0.07s]
07:30:40 9 of 35 START test not_null_stg_customers_customer_id .......................... [RUN]
07:30:40 9 of 35 PASS not_null_stg_customers_customer_id ................................ [PASS in 0.05s]
07:30:40 10 of 35 START test unique_stg_customers_customer_id ........................... [RUN]
07:30:40 10 of 35 PASS unique_stg_customers_customer_id ................................. [PASS in 0.04s]
07:30:40 11 of 35 START test accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned [RUN]
07:30:40 11 of 35 PASS accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned [PASS in 0.04s]
07:30:40 12 of 35 START test not_null_stg_orders_order_id ............................... [RUN]
07:30:40 12 of 35 PASS not_null_stg_orders_order_id ..................................... [PASS in 0.04s]
07:30:40 13 of 35 START test unique_stg_orders_order_id ................................. [RUN]
07:30:40 13 of 35 PASS unique_stg_orders_order_id ....................................... [PASS in 0.04s]
07:30:40 14 of 35 START test accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card [RUN]
07:30:40 14 of 35 PASS accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card [PASS in 0.04s]
07:30:40 15 of 35 START test not_null_stg_payments_payment_id ........................... [RUN]
07:30:40 15 of 35 PASS not_null_stg_payments_payment_id ................................. [PASS in 0.04s]
07:30:40 16 of 35 START test unique_stg_payments_payment_id ............................. [RUN]
07:30:40 16 of 35 PASS unique_stg_payments_payment_id ................................... [PASS in 0.04s]
07:30:40 17 of 35 START sql table model main.customers .................................. [RUN]
07:30:40 17 of 35 OK created sql table model main.customers ............................. [OK in 0.08s]
07:30:40 18 of 35 START sql table model main.orders ..................................... [RUN]
07:30:40 18 of 35 OK created sql table model main.orders ................................ [OK in 0.08s]
07:30:40 19 of 35 START test not_null_customers_customer_id ............................. [RUN]
07:30:40 19 of 35 PASS not_null_customers_customer_id ................................... [PASS in 0.04s]
07:30:40 20 of 35 START test unique_customers_customer_id ............................... [RUN]
07:30:40 20 of 35 PASS unique_customers_customer_id ..................................... [PASS in 0.04s]
07:30:40 21 of 35 START test dbt_expectations_expect_column_to_exist_orders_order_id .... [RUN]
07:30:40 21 of 35 PASS dbt_expectations_expect_column_to_exist_orders_order_id .......... [PASS in 0.08s]
07:30:40 22 of 35 START test dbt_utils_at_least_one_orders_order_id ..................... [RUN]
07:30:40 22 of 35 PASS dbt_utils_at_least_one_orders_order_id ........................... [PASS in 0.04s]
07:30:40 23 of 35 START test dbt_utils_equal_rowcount_orders_ref_stg_customers_ ......... [RUN]
07:30:40 23 of 35 PASS dbt_utils_equal_rowcount_orders_ref_stg_customers_ ............... [PASS in 0.04s]
07:30:40 24 of 35 START test dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_ [RUN]
07:30:40 24 of 35 PASS dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_ .. [PASS in 0.05s]
07:30:41 25 of 35 START test dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount [RUN]
07:30:41 25 of 35 PASS dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount [PASS in 0.04s]
07:30:41 26 of 35 START test dbt_utils_not_accepted_values_orders_order_id__120 ......... [RUN]
07:30:41 26 of 35 PASS dbt_utils_not_accepted_values_orders_order_id__120 ............... [PASS in 0.04s]
07:30:41 27 of 35 START test dbt_utils_not_constant_orders_order_id ..................... [RUN]
07:30:41 27 of 35 PASS dbt_utils_not_constant_orders_order_id ........................... [PASS in 0.09s]
07:30:41 28 of 35 START test dbt_utils_not_empty_string_orders_order_id ................. [RUN]
07:30:41 28 of 35 PASS dbt_utils_not_empty_string_orders_order_id ....................... [PASS in 0.04s]
07:30:41 29 of 35 START test dbt_utils_not_null_proportion_orders_0_95__order_id ........ [RUN]
07:30:41 29 of 35 PASS dbt_utils_not_null_proportion_orders_0_95__order_id .............. [PASS in 0.05s]
07:30:41 30 of 35 START test dbt_utils_unique_combination_of_columns_orders_order_id__amount [RUN]
07:30:41 30 of 35 PASS dbt_utils_unique_combination_of_columns_orders_order_id__amount .. [PASS in 0.04s]
07:30:41 31 of 35 START test not_null_orders_customer_id ................................ [RUN]
07:30:41 31 of 35 PASS not_null_orders_customer_id ...................................... [PASS in 0.04s]
07:30:41 32 of 35 START test not_null_orders_order_id ................................... [RUN]
07:30:41 32 of 35 PASS not_null_orders_order_id ......................................... [PASS in 0.04s]
07:30:41 33 of 35 START test relationships_orders_customer_id__customer_id__ref_stg_customers_ [RUN]
07:30:41 33 of 35 PASS relationships_orders_customer_id__customer_id__ref_stg_customers_ [PASS in 0.04s]
07:30:41 34 of 35 START test relationships_orders_order_id__order_id__ref_stg_orders_ ... [RUN]
07:30:41 34 of 35 PASS relationships_orders_order_id__order_id__ref_stg_orders_ ......... [PASS in 0.04s]
07:30:41 35 of 35 START test unique_orders_order_id ..................................... [RUN]
07:30:41 35 of 35 PASS unique_orders_order_id ........................................... [PASS in 0.04s]
07:30:41
07:30:41 Finished running 3 table models, 3 seeds, 26 tests, 3 view models in 0 hours 0 minutes and 2.35 seconds (2.35s).
07:30:41
07:30:41 Completed successfully
07:30:41
07:30:41 Done. PASS=35 WARN=0 ERROR=0 SKIP=0 TOTAL=35

Run: [piperider run]
DataSource: dev
───────────────────────────────────────────────────────────────────────────────────── Validating ──────────────────────────────────────────────────────────────────────────────────────
everything is OK.
────────────────────────────────────────────────────────────────────────────────── Collect metadata ───────────────────────────────────────────────────────────────────────────────────
[0/0] METADATA ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 9/9 0:00:00
───────────────────────────────────────────────────────────────────────────────── Profile statistics ──────────────────────────────────────────────────────────────────────────────────
[1/2] customers ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 8/8 0:00:00
[2/2] orders ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 10/10 0:00:00
──────────────────────────────────────────────────────────────────────────────────── Query metrics ────────────────────────────────────────────────────────────────────────────────────
No metrics to query
─────────────────────────────────────────────────────────────────────────────────────── Testing ───────────────────────────────────────────────────────────────────────────────────────
Test completed ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 77/77 0:00:00
────────────────────────────────────────────────────────────────────────────────── Assertion Results ──────────────────────────────────────────────────────────────────────────────────
───────────────────────────────────────────────────────────────────────────────────────── dbt ─────────────────────────────────────────────────────────────────────────────────────────

Status Test Subject ───────── [ OK ] stg_customers.customer_id [ OK ] stg_customers.customer_id [ OK ] stg_orders.status [ OK ] stg_orders.order_id [ OK ] stg_orders.order_id [ OK ] stg_payments.payment_method [ OK ] stg_payments.payment_id [ OK ] stg_payments.payment_id [ OK ] customers.customer_id [ OK ] customers.customer_id [ OK ] orders.order_id [ OK ] orders.order_id [ OK ] stg_customers [ OK ] stg_orders [ OK ] orders [ OK ] orders.order_id [ OK ] orders.order_id [ OK ] orders.order_id [ OK ] orders.order_id [ OK ] orders [ OK ] orders.customer_id [ OK ] orders.order_id [ OK ] stg_customers.customer_id [ OK ] stg_orders.order_id [ OK ] orders.order_id Assertion Message
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
not_null_stg_customers_customer_id
unique_stg_customers_customer_id
accepted_values_stg_orders_status__placed__shipped__completed__return_pending__returned
not_null_stg_orders_order_id
unique_stg_orders_order_id
accepted_values_stg_payments_payment_method__credit_card__coupon__bank_transfer__gift_card
not_null_stg_payments_payment_id
unique_stg_payments_payment_id
not_null_customers_customer_id
unique_customers_customer_id
dbt_expectations_expect_column_to_exist_orders_order_id
dbt_utils_at_least_one_orders_order_id
dbt_utils_equal_rowcount_orders_ref_stg_customers_
dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_
dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount
dbt_utils_not_accepted_values_orders_order_id__120
dbt_utils_not_constant_orders_order_id
dbt_utils_not_empty_string_orders_order_id
dbt_utils_not_null_proportion_orders_0_95__order_id
dbt_utils_unique_combination_of_columns_orders_order_id__amount
not_null_orders_customer_id
not_null_orders_order_id
relationships_orders_customer_id__customer_id__ref_stg_customers_
relationships_orders_order_id__order_id__ref_stg_orders_
unique_orders_order_id

────────────────────────────────────────────────────────────────────────────────────── PipeRider ──────────────────────────────────────────────────────────────────────────────────────

Status Test Subject Assertion Expected Actual
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
[ OK ] customers.customer_id assert_column_schema_type INTEGER INTEGER
[ OK ] customers.customer_id assert_column_not_null - -
[ OK ] customers.first_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] customers.first_name assert_column_not_null - -
[ OK ] customers.last_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] customers.last_name assert_column_not_null - -
[ OK ] customers.last_name assert_column_value [ -
'R.',
'H.',
'W.',
'M.',
'P.',
'C.',
'A.',
'F.',
'B.',
'K.',
'G.',
'O.',
'S.',
'D.',
'J.',
'T.',
'N.',
'E.',
'L.'
]
[ OK ] customers.first_order assert_column_schema_type DATE DATE
[ OK ] customers.most_recent_order assert_column_schema_type DATE DATE
[ OK ] customers.number_of_orders assert_column_schema_type BIGINT BIGINT
[ OK ] customers.customer_lifetime_value assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] raw_orders.id assert_column_schema_type INTEGER INTEGER
[ OK ] raw_orders.id assert_column_not_null - -
[ OK ] raw_orders.user_id assert_column_schema_type INTEGER INTEGER
[ OK ] raw_orders.user_id assert_column_not_null - -
[ OK ] raw_orders.order_date assert_column_schema_type DATE DATE
[ OK ] raw_orders.order_date assert_column_not_null - -
[ OK ] raw_orders.status assert_column_schema_type VARCHAR VARCHAR
[ OK ] raw_orders.status assert_column_not_null - -
[ OK ] raw_orders.status assert_column_value ['completed', 'placed', 'shipped', 'returned', 'return_pending'] -
[ OK ] orders.order_id assert_column_schema_type INTEGER INTEGER
[ OK ] orders.order_id assert_column_not_null - -
[ OK ] orders.customer_id assert_column_schema_type INTEGER INTEGER
[ OK ] orders.customer_id assert_column_not_null - -
[ OK ] orders.order_date assert_column_schema_type DATE DATE
[ OK ] orders.order_date assert_column_not_null - -
[ OK ] orders.status assert_column_schema_type VARCHAR VARCHAR
[ OK ] orders.status assert_column_not_null - -
[ OK ] orders.status assert_column_value ['completed', 'placed', 'shipped', 'returned', 'return_pending'] -
[ OK ] orders.credit_card_amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] orders.coupon_amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] orders.bank_transfer_amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] orders.gift_card_amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] orders.amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] stg_payments.payment_id assert_column_schema_type INTEGER INTEGER
[ OK ] stg_payments.payment_id assert_column_not_null - -
[ OK ] stg_payments.order_id assert_column_schema_type INTEGER INTEGER
[ OK ] stg_payments.order_id assert_column_not_null - -
[ OK ] stg_payments.payment_method assert_column_schema_type VARCHAR VARCHAR
[ OK ] stg_payments.payment_method assert_column_not_null - -
[ OK ] stg_payments.payment_method assert_column_value ['credit_card', 'bank_transfer', 'coupon', 'gift_card'] -
[ OK ] stg_payments.amount assert_column_schema_type DOUBLE PRECISION DOUBLE PRECISION
[ OK ] stg_payments.amount assert_column_not_null - -
[ OK ] stg_orders.order_id assert_column_schema_type INTEGER INTEGER
[ OK ] stg_orders.order_id assert_column_not_null - -
[ OK ] stg_orders.customer_id assert_column_schema_type INTEGER INTEGER
[ OK ] stg_orders.customer_id assert_column_not_null - -
[ OK ] stg_orders.order_date assert_column_schema_type DATE DATE
[ OK ] stg_orders.order_date assert_column_not_null - -
[ OK ] stg_orders.status assert_column_schema_type VARCHAR VARCHAR
[ OK ] stg_orders.status assert_column_not_null - -
[ OK ] stg_orders.status assert_column_value ['completed', 'placed', 'shipped', 'returned', 'return_pending'] -
[ OK ] stg_customers.customer_id assert_column_schema_type INTEGER INTEGER
[ OK ] stg_customers.customer_id assert_column_not_null - -
[ OK ] stg_customers.first_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] stg_customers.first_name assert_column_not_null - -
[ OK ] stg_customers.last_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] stg_customers.last_name assert_column_not_null - -
[ OK ] stg_customers.last_name assert_column_value [ -
'R.',
'W.',
'H.',
'M.',
'P.',
'C.',
'A.',
'F.',
'B.',
'K.',
'G.',
'O.',
'S.',
'D.',
'J.',
'T.',
'N.',
'L.',
'E.'
]
[ OK ] raw_customers.id assert_column_schema_type INTEGER INTEGER
[ OK ] raw_customers.id assert_column_not_null - -
[ OK ] raw_customers.first_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] raw_customers.first_name assert_column_not_null - -
[ OK ] raw_customers.last_name assert_column_schema_type VARCHAR VARCHAR
[ OK ] raw_customers.last_name assert_column_not_null - -
[ OK ] raw_customers.last_name assert_column_value [ -
'R.',
'W.',
'H.',
'M.',
'P.',
'C.',
'A.',
'F.',
'B.',
'K.',
'G.',
'O.',
'S.',
'D.',
'J.',
'T.',
'N.',
'L.',
'E.'
]
[ OK ] date_spine.date_day assert_column_schema_type DATE DATE
[ OK ] date_spine.date_day assert_column_not_null - -
[ OK ] raw_payments.id assert_column_schema_type INTEGER INTEGER
[ OK ] raw_payments.id assert_column_not_null - -
[ OK ] raw_payments.order_id assert_column_schema_type INTEGER INTEGER
[ OK ] raw_payments.order_id assert_column_not_null - -
[ OK ] raw_payments.payment_method assert_column_schema_type VARCHAR VARCHAR
[ OK ] raw_payments.payment_method assert_column_not_null - -
[ OK ] raw_payments.payment_method assert_column_value ['credit_card', 'bank_transfer', 'coupon', 'gift_card'] -
[ OK ] raw_payments.amount assert_column_schema_type INTEGER INTEGER
[ OK ] raw_payments.amount assert_column_not_null - -

Generating reports from: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163046/run.json
Report generated in: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163046/index.html

──────────────────────────────────────────────────────────────────────────── Recipe executor: target phase ────────────────────────────────────────────────────────────────────────────
Run: [dbt deps]
07:30:52 Running with dbt=1.6.1
07:30:54 Installing calogica/dbt_expectations
07:30:55 Installed from version 0.9.0
07:30:55 Up to date!
07:30:55 Installing dbt-labs/dbt_utils
07:30:56 Installed from version 1.1.1
07:30:56 Up to date!
07:30:56 Installing calogica/dbt_date
07:30:56 Installed from version 0.8.1
07:30:56 Updated version available: 0.9.1
07:30:56
07:30:56 Updates available for packages: ['calogica/dbt_date']
Update your versions in packages.yml, then run dbt deps

Run: [dbt build --select state:modified+ --state /tmp/tmpwwqb_441/8a9568d1f2efc60dd9f27f29426f9a8f03386ef1/state]
07:31:00 Running with dbt=1.6.1
07:31:00 Registered adapter: duckdb=1.6.0
07:31:00 Found 6 models, 26 tests, 3 seeds, 0 sources, 0 exposures, 0 metrics, 698 macros, 0 groups, 0 semantic models
07:31:00
07:31:00 Concurrency: 1 threads (target='dev')
07:31:00
07:31:00 1 of 19 START sql table model main.customers ................................... [RUN]
07:31:01 1 of 19 OK created sql table model main.customers .............................. [OK in 0.20s]
07:31:01 2 of 19 START sql table model main.orders ...................................... [RUN]
07:31:01 2 of 19 OK created sql table model main.orders ................................. [OK in 0.09s]
07:31:01 3 of 19 START test not_null_customers_customer_id .............................. [RUN]
07:31:01 3 of 19 PASS not_null_customers_customer_id .................................... [PASS in 0.06s]
07:31:01 4 of 19 START test unique_customers_customer_id ................................ [RUN]
07:31:01 4 of 19 PASS unique_customers_customer_id ...................................... [PASS in 0.04s]
07:31:01 5 of 19 START test dbt_expectations_expect_column_to_exist_orders_order_id ..... [RUN]
07:31:01 5 of 19 PASS dbt_expectations_expect_column_to_exist_orders_order_id ........... [PASS in 0.09s]
07:31:01 6 of 19 START test dbt_utils_at_least_one_orders_order_id ...................... [RUN]
07:31:01 6 of 19 PASS dbt_utils_at_least_one_orders_order_id ............................ [PASS in 0.09s]
07:31:01 7 of 19 START test dbt_utils_equal_rowcount_orders_ref_stg_customers_ .......... [RUN]
07:31:01 7 of 19 PASS dbt_utils_equal_rowcount_orders_ref_stg_customers_ ................ [PASS in 0.05s]
07:31:01 8 of 19 START test dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_ [RUN]
07:31:01 8 of 19 PASS dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_ ... [PASS in 0.07s]
07:31:01 9 of 19 START test dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount [RUN]
07:31:01 9 of 19 PASS dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount [PASS in 0.04s]
07:31:01 10 of 19 START test dbt_utils_not_accepted_values_orders_order_id__120 ......... [RUN]
07:31:01 10 of 19 PASS dbt_utils_not_accepted_values_orders_order_id__120 ............... [PASS in 0.05s]
07:31:01 11 of 19 START test dbt_utils_not_constant_orders_order_id ..................... [RUN]
07:31:01 11 of 19 PASS dbt_utils_not_constant_orders_order_id ........................... [PASS in 0.05s]
07:31:01 12 of 19 START test dbt_utils_not_empty_string_orders_order_id ................. [RUN]
07:31:01 12 of 19 PASS dbt_utils_not_empty_string_orders_order_id ....................... [PASS in 0.04s]
07:31:01 13 of 19 START test dbt_utils_not_null_proportion_orders_0_95__order_id ........ [RUN]
07:31:01 13 of 19 PASS dbt_utils_not_null_proportion_orders_0_95__order_id .............. [PASS in 0.05s]
07:31:01 14 of 19 START test dbt_utils_unique_combination_of_columns_orders_order_id__amount [RUN]
07:31:01 14 of 19 PASS dbt_utils_unique_combination_of_columns_orders_order_id__amount .. [PASS in 0.05s]
07:31:01 15 of 19 START test not_null_orders_customer_id ................................ [RUN]
07:31:02 15 of 19 PASS not_null_orders_customer_id ...................................... [PASS in 0.04s]
07:31:02 16 of 19 START test not_null_orders_order_id ................................... [RUN]
07:31:02 16 of 19 PASS not_null_orders_order_id ......................................... [PASS in 0.04s]
07:31:02 17 of 19 START test relationships_orders_customer_id__customer_id__ref_stg_customers_ [RUN]
07:31:02 17 of 19 PASS relationships_orders_customer_id__customer_id__ref_stg_customers_ [PASS in 0.05s]
07:31:02 18 of 19 START test relationships_orders_order_id__order_id__ref_stg_orders_ ... [RUN]
07:31:02 18 of 19 PASS relationships_orders_order_id__order_id__ref_stg_orders_ ......... [PASS in 0.04s]
07:31:02 19 of 19 START test unique_orders_order_id ..................................... [RUN]
07:31:02 19 of 19 PASS unique_orders_order_id ........................................... [PASS in 0.04s]
07:31:02
07:31:02 Finished running 2 table models, 17 tests in 0 hours 0 minutes and 1.40 seconds (1.40s).
07:31:02
07:31:02 Completed successfully
07:31:02
07:31:02 Done. PASS=19 WARN=0 ERROR=0 SKIP=0 TOTAL=19

Run: [piperider run]
DataSource: dev
───────────────────────────────────────────────────────────────────────────────────── Validating ──────────────────────────────────────────────────────────────────────────────────────
everything is OK.
────────────────────────────────────────────────────────────────────────────────── Collect metadata ───────────────────────────────────────────────────────────────────────────────────
[0/0] METADATA ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 9/9 0:00:00
───────────────────────────────────────────────────────────────────────────────── Profile statistics ──────────────────────────────────────────────────────────────────────────────────
[1/2] customers ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 9/9 0:00:00
[2/2] orders ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 10/10 0:00:00
──────────────────────────────────────────────────────────────────────────────────── Query metrics ────────────────────────────────────────────────────────────────────────────────────
No metrics to query
─────────────────────────────────────────────────────────────────────────────────────── Testing ───────────────────────────────────────────────────────────────────────────────────────
Test completed ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 77/77 0:00:00
────────────────────────────────────────────────────────────────────────────────── Assertion Results ──────────────────────────────────────────────────────────────────────────────────
───────────────────────────────────────────────────────────────────────────────────────── dbt ─────────────────────────────────────────────────────────────────────────────────────────

Status Test Subject Assertion Message
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
[ OK ] customers.customer_id not_null_customers_customer_id
[ OK ] customers.customer_id unique_customers_customer_id
[ OK ] orders.order_id dbt_expectations_expect_column_to_exist_orders_order_id
[ OK ] orders.order_id dbt_utils_at_least_one_orders_order_id
[ OK ] stg_customers dbt_utils_equal_rowcount_orders_ref_stg_customers_
[ OK ] stg_orders dbt_utils_equality_orders_order_id__order_date__ref_stg_orders_
[ OK ] orders dbt_utils_expression_is_true_orders__credit_card_amount_coupon_amount_bank_transfer_amount_gift_card_amount_amount
[ OK ] orders.order_id dbt_utils_not_accepted_values_orders_order_id__120
[ OK ] orders.order_id dbt_utils_not_constant_orders_order_id
[ OK ] orders.order_id dbt_utils_not_empty_string_orders_order_id
[ OK ] orders.order_id dbt_utils_not_null_proportion_orders_0_95__order_id
[ OK ] orders dbt_utils_unique_combination_of_columns_orders_order_id__amount
[ OK ] orders.customer_id not_null_orders_customer_id
[ OK ] orders.order_id not_null_orders_order_id
[ OK ] stg_customers.customer_id relationships_orders_customer_id__customer_id__ref_stg_customers_
[ OK ] stg_orders.order_id relationships_orders_order_id__order_id__ref_stg_orders_
[ OK ] orders.order_id unique_orders_order_id

Generating reports from: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163106/run.json
Report generated in: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163106/index.html

────────────────────────────────────────────────────────────────────────────────── Comparison report ──────────────────────────────────────────────────────────────────────────────────
Selected reports:
Base: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163046/run.json
Target: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918163106/run.json

Impact Summary:
Code Changes: added=0, removed=0, modified=2
Resource Impact: potentially_impacted=2, assessed=2, skipped=0, impacted=1

Comparison report: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/comparisons/latest/index.html
Comparison summary: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/comparisons/latest/summary.md
root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop#

差分レポートの確認

ログに記載がある通り以下のPATHにレポートが出力されます。

Comparison report: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/comparisons/latest/index.html

HTMLファイルをブラウザで確認する
Base（現行前）とTarget（現行後）で追加されたカラムがすぐわかるようになっています。

レポート生成

piperider run コマンドを使ってレポート生成

root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop# piperider run --state ./target
[ DBT ] Use the existing dbt project file: /home/dbt/jaffle_shop_pj/jaffle_shop/dbt_project.yml
DataSource: dev
─────────────────────────────────────────────────────────────────────────────── Validating ────────────────────────────────────────────────────────────────────────────────
everything is OK.
──────────────────────────────────────────────────────────────────────────── Collect metadata ─────────────────────────────────────────────────────────────────────────────
[0/0]   METADATA      ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   9/9 0:00:01
─────────────────────────────────────────────────────────────────────────── Profile statistics ────────────────────────────────────────────────────────────────────────────
[1/9]   stg_orders    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   5/5 0:00:00
[2/9]   stg_payments  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   5/5 0:00:00
[3/9]   stg_customers ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   4/4 0:00:00
[4/9]   raw_payments  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   5/5 0:00:00
[5/9]   raw_customers ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   4/4 0:00:00
[6/9]   customers     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   8/8 0:00:00
[7/9]   raw_orders    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   5/5 0:00:00
[8/9]   orders        ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 10/10 0:00:00
[9/9]   date_spine    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   2/2 0:00:00
────────────────────────────────────────────────────────────────────────────── Query metrics ──────────────────────────────────────────────────────────────────────────────
No metrics to query

Generating reports from: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918152130/run.json
Report generated in: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918152130/index.html
root@3f7c4a32a705:/home/dbt/jaffle_shop_pj/jaffle_shop#

ログの一番下にレポートの出力先が表示されているindex.htmlをブラウザで表示してみます。

Report generated in: /home/dbt/jaffle_shop_pj/jaffle_shop/.piperider/outputs/dev-20230918152130/index.html

まとめ

ブランチ毎に差分を可視化してくれるのは嬉しい機能ですね。
修正漏れがあると困るので、修正箇所やその影響範囲を確認できるのはグッドでした。
しかもブランチ切っておくだけで差分見れるので、プルリク前に確認して、修正漏れとかあれば、随時修正できるので。
ただ、レポートとしての操作感や見た目はelementaryの方が好きですね。
これは好みの問題なので。。。
あとは、piperider generate-assertionsコマンドでPipeRiderが自動でテストを書いてくれる機能も見方によってはありがたい機能です。
どっちが良いとかは無く、お互いのいい所を良い感じにチョイスして使っていけば良いんじゃないでしょうか。
PipeRider Cloudにはリネージ差分機能もあるので、OSSの方にもその機能を追加して欲しいです。。。