dbtとLookerを使ってSnowflakeの利用費が確認できるダッシュボードをつくってみた

ルッスノティー
特集

たまちゃん

2021.02.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

dbtの学習のために色々なPackageを探していたら、Snowflakeの利用費を計算してくれるPackageがあったので使ってみました。ついでにダッシュボードも作ってみました。

Snowflakeの費用を計算してくれるdbt Packageがある

Snowflakeの費用を計算してくれる（費用を計算したテーブルやビューを作ってくれる）dbt Packageがあります。開発したのはなんとあのGitLab社。使わない手はありません。

ちなみに「Snowflakeの管理画面から計算できるやん」っていう声が聞こえてきそうですが、その声は一旦置いておきます（これについては記事の後半で改めて触れます）。

やってみた

実施環境

  • macOS Catalina 10.15.7
  • dbt CLI 0.19.0
  • Snowflake
    • Enterprise
    • AWS
    • Asia Pacific
  • Looker 21.0.25

Snowflake側の作業

dbt用の権限の準備

dbtが使用するロールに対して、Snowflakeのメタ情報（クレジットの消費量など）にアクセスできる権限を付与しておく必要があります。具体的には、SNOWFLAKEという名前の共有データベースが（アカウント毎に必ず）あるので、そこに（dbtが）アクセスできるようにします。SNOWFLAKEという名前の共有データベースについての詳細は、下記のドキュメントに載っています。

権限の付与の方法は色々ありますが、上記のPackageのドキュメントでは、下記のGRANT文が紹介されています。

GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE <ロール名>;

dbt側の作業

新規Projectを作成する

dbt initで新しいProjectを作成します。Snowflakeの接続情報をまだ設定していない場合は、下記を参考にして、profiles.ymlに設定しておきます。

例のPackageをインストールする

作成したProjectに、gitlabhq/snowflake_spend at v1.2.0をインストールします。packages.ymlに必要な記述は下記の通り（更新されるかもしれないので、公式もチェックしましょう）。

packages.yml

packages:
  - package: gitlabhq/snowflake_spend
    version: 1.2.0

dbt Package自体のインストールの仕方は下記をどうぞ。

csvファイルを用意して、dbt seedでSnowflakeにロードする

このPackageは、実行する前に、少しだけ下準備が必要になっています。

下記のようなcsvファイルを用意し、dataディレクトリに格納します。

snowflake_contract_rates.csv

effective_date,rate
yyyy-mm-dd,2.55
yyyy-mm-dd,2.48

effective_dateはSnowflakeの契約開始日です。rateはSnowflakeのクレジットが実際いくらになるのかを算出するためのレートです。レートについては、エディションやリージョン等で変わってくるので、自環境のレートを確認しておきましょう。

ちなみに、Snowflakeの利用途中で、エディションの変更等を行ったことによって、レートに変動があった場合は、csvの2行目以降に、変更日と変更後のレートを付け加えることが出来ます。これにより、レートの変更も加味した上で計算を行うことができます。

作成したcsvは、dataディレクトリ下に配置します。その状態で、dbt seedというコマンドを実行すると、配置したcsvファイルがSnowflakeにロードされます。

$ dbt seed
Running with dbt=0.19.0
Found 7 models, 15 tests, 0 snapshots, 6 analyses, 314 macros, 0 operations, 1 seed file, 2 sources, 0 exposures

18:03:26 | Concurrency: 1 threads (target='cm_tokyo')
18:03:26 |
18:03:26 | 1 of 1 START seed file DBT_RTAMAI.snowflake_contract_rates........... [RUN]
* Deprecation Warning: The quote_columns parameter was not set for seeds, so the
default value of False was chosen. The default will change to True in a future
release.

For more information, see:
https://docs.getdbt.com/v0.15/docs/seeds#section-specify-column-quoting
18:03:29 | 1 of 1 OK loaded seed file DBT_RTAMAI.snowflake_contract_rates....... [INSERT 1 in 2.56s]
18:03:30 |
18:03:30 | Finished running 1 seed in 6.45s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

（余談ですが）つまり、dbt seedとは「dataディレクトリ下に配置したcsvファイルをDWHにロードできるコマンド」なのです。詳細は下記をどうぞ。

dbtを実行する

csvファイルをロードし終えたら、後はdbtを実行するだけです。このPackageは、先程ロードしたcsv（snowflake_contract_rates）を元に、Snowflakeの使用費を計算してくれます（だから、事前にレートがわかるデータをロードする必要があった）。

$ dbt run
Running with dbt=0.19.0
Found 4 models, 11 tests, 0 snapshots, 6 analyses, 314 macros, 0 operations, 1 seed file, 2 sources, 0 exposures

18:04:47 | Concurrency: 1 threads (target='cm_tokyo')
18:04:47 |
18:04:47 | 1 of 4 START view model DBT_RTAMAI.snowflake_amortized_rates......... [RUN]
18:04:49 | 1 of 4 OK created view model DBT_RTAMAI.snowflake_amortized_rates.... [SUCCESS 1 in 2.09s]
18:04:49 | 2 of 4 START view model DBT_RTAMAI.snowflake_warehouse_metering...... [RUN]
18:04:51 | 2 of 4 OK created view model DBT_RTAMAI.snowflake_warehouse_metering. [SUCCESS 1 in 1.42s]
18:04:51 | 3 of 4 START incremental model DBT_RTAMAI.snowflake_query_history.... [RUN]
18:05:03 | 3 of 4 OK created incremental model DBT_RTAMAI.snowflake_query_history [SUCCESS 1 in 11.89s]
18:05:03 | 4 of 4 START view model DBT_RTAMAI.snowflake_warehouse_metering_xf... [RUN]
18:05:05 | 4 of 4 OK created view model DBT_RTAMAI.snowflake_warehouse_metering_xf [SUCCESS 1 in 2.07s]
18:05:06 |
18:05:06 | Finished running 3 view models, 1 incremental model in 21.50s.

Completed successfully

Done. PASS=4 WARN=0 ERROR=0 SKIP=0 TOTAL=4

Snowflakeの利用費が計算されたテーブルやビューが作成されました。

ちなみに、ご丁寧にテストも定義されています。

$ dbt test
Running with dbt=0.19.0
Found 4 models, 11 tests, 0 snapshots, 6 analyses, 314 macros, 0 operations, 1 seed file, 2 sources, 0 exposures

17:28:09 | Concurrency: 1 threads (target='cm_tokyo')
17:28:09 |
17:28:09 | 1 of 11 START test not_null_snowflake_amortized_rates_date_day....... [RUN]
17:28:11 | 1 of 11 PASS not_null_snowflake_amortized_rates_date_day............. [PASS in 2.33s]
17:28:11 | 2 of 11 START test not_null_snowflake_amortized_rates_effective_start_date [RUN]
17:28:12 | 2 of 11 PASS not_null_snowflake_amortized_rates_effective_start_date. [PASS in 1.62s]
17:28:12 | 3 of 11 START test not_null_snowflake_amortized_rates_rate........... [RUN]
17:28:14 | 3 of 11 PASS not_null_snowflake_amortized_rates_rate................. [PASS in 1.65s]
17:28:14 | 4 of 11 START test not_null_snowflake_query_history_query_end_time... [RUN]
17:28:16 | 4 of 11 PASS not_null_snowflake_query_history_query_end_time......... [PASS in 1.51s]
17:28:16 | 5 of 11 START test not_null_snowflake_query_history_query_start_time. [RUN]
17:28:17 | 5 of 11 PASS not_null_snowflake_query_history_query_start_time....... [PASS in 1.27s]
17:28:17 | 6 of 11 START test not_null_snowflake_query_history_query_text....... [RUN]
17:28:18 | 6 of 11 PASS not_null_snowflake_query_history_query_text............. [PASS in 1.18s]
17:28:18 | 7 of 11 START test not_null_snowflake_query_history_snowflake_query_id [RUN]
17:28:19 | 7 of 11 PASS not_null_snowflake_query_history_snowflake_query_id..... [PASS in 1.15s]
17:28:19 | 8 of 11 START test not_null_snowflake_query_history_snowflake_role_name [RUN]
17:28:20 | 8 of 11 PASS not_null_snowflake_query_history_snowflake_role_name.... [PASS in 1.15s]
17:28:20 | 9 of 11 START test not_null_snowflake_query_history_snowflake_user_name [RUN]
17:28:22 | 9 of 11 PASS not_null_snowflake_query_history_snowflake_user_name.... [PASS in 1.20s]
17:28:22 | 10 of 11 START test unique_snowflake_amortized_rates_date_day........ [RUN]
17:28:24 | 10 of 11 PASS unique_snowflake_amortized_rates_date_day.............. [PASS in 2.44s]
17:28:24 | 11 of 11 START test unique_snowflake_query_history_snowflake_query_id [RUN]
17:28:26 | 11 of 11 PASS unique_snowflake_query_history_snowflake_query_id...... [PASS in 1.65s]
17:28:26 |
17:28:26 | Finished running 11 tests in 18.27s.

Completed successfully

Done. PASS=11 WARN=0 ERROR=0 SKIP=0 TOTAL=11

Looker側の作業

Snowflakeに必要なデータは揃ったので、今度はそれを可視化するためにLooker側の作業をやっていきます。

例のPackageにダッシュボードの例があるが…

実は、今回使用したPackageには、BIツールのPeriscope（現Sisense）用のクエリがサンプルとして付属しています。

※snowflake_spend/analysis/periscope_dashboards at v1.2.0 · gitlabhq/snowflake_spendより

ダッシュボードの画像と、それぞれのクエリがあるので、これを参考に、Lookerで同じようなダッシュボードを作ってみたいと思います。

ダッシュボードとクエリの中身を確認し、それをLookMLに落とし込む

ご丁寧にも、ダッシュボードの各タイル名とクエリ名が連動してくれているようです。例えばYear to Dateというタイルは、year_to_date.sqlというクエリがもとになっています。

全てのタイルとクエリを確認したところ、ほとんどのタイルはsnowflake_warehouse_metering_xfというテーブルをもとに作られているようです。ですので、Looker側で、まずはこちらのテーブルを定義するviewファイルを作成します。

snowflake_warehouse_metering_xf.view

view: snowflake_warehouse_metering_xf {
  sql_table_name: "DBT_RTAMAI"."SNOWFLAKE_WAREHOUSE_METERING_XF"
    ;;

  dimension: credit_rate {
    type: number
    sql: ${TABLE}."CREDIT_RATE" ;;
  }

  dimension: dollars_spent {
    type: number
    sql: ${TABLE}."DOLLARS_SPENT" ;;
  }
  
  ...

dollars_spentは、ほとんどのタイルで集計の対象になるため、measureも作成しておきます。value_format等は好みでどうぞ。

measure: total_dollars_spent {
  type: sum
  value_format: "$0.00"
  drill_fields: [warehouse_name, usage_day_date]
  sql: ${dollars_spent} ;;
}

全てのタイルがこのviewで作れるので、exploreは超シンプルです。

snowflake_spend.model

explore: snowflake_warehouse_metering_xf {}

PeriscopeのクエリをExploreで再現する

Exploreができたので、後はこれを使ってシコシコとタイルを再現していきます。

再現の方法ですが、コツみたいなものがあります。例えば、year_to_date.sqlの中身は下記のようになっています。

year_to_date.sql

SELECT sum(dollars_spent) AS dollars_spent
FROM {{ref('snowflake_warehouse_metering_xf')}}
WHERE date_trunc('year', usage_month) = date_trunc('year', CURRENT_TIMESTAMP)::date

FROM句はexploreで定義しているので考えなくて大丈夫です。SELECT句が、Exploreでいうフィールドにあたります。このクエリでは、sum(dollars_spent)がありますが、これは先程定義しておいたmeasureのtotal_dollars_spentで対応できますね。WHERE句はフィルタにあたります。usage_monthが今年（2021年）に該当するものだけにフィルタリングしているので、それをExploreで再現するだけです。

最終的にこうなりました。

このように、Periscope用のクエリを、どんどんLookerのExploreとして落とし込んでいきます。

cumulative_spend_to_dateの再現について

一つだけ、再現をどうしようか迷ったタイルがありました。cumulative_spend_to_dateというタイルなのですが、クエリ自体にWINDOW関数が使われているため、派生テーブルの使用を検討しました。

ただ、こちらのタイルをよく見たところ、日毎に積み上がっていくSnowflakeの利用費を累計で折れ線グラフとして描いているだけなので、WINDOW関数を使わずとも、Exploreのテーブル計算で何とかなると考え、下記のようにしました。

累計していく日については、usage_monthのday of monthを指定し、それに合わせてtotal_dollars_spentを配置しました。ただし、こちらの数値は累計にする必要があるため、テーブル計算でrunning_totalを行い、元々の数値はビジュアライゼーションから非表示するようにしました。この累計折れ線グラフは月別に複数引く必要があるため、usage_monthをMonth形式でピボットして配置しました。

完成

最終的に下記のダッシュボードを作りました。いい感じにパクれたんじゃないでしょうか。

検討事項

「利用費はSnowflakeの管理画面で確認できるやん？」

ぶっちゃけその通りです。しかし、下記のデメリットもあります。

  • 費用関係の情報はACCOUNTADMINロールしか閲覧できない。
  • クレジットでしか確認できない
  • 踏み込んだ分析はできない

単純な確認であれば、ACCOUNTADMINロールを持ったユーザーが管理画面を見るだけで済みますが、Snowflakeの利用状況をもっと分析したい時は、BIツールを使用した方が良いと思います。また、費用情報の閲覧には、ACCOUNTADMINロールが必要ですが、全権限をもつロールである以上、無闇矢鱈にいろいろなユーザーに付与するわけにはいきません。しかし、Snowflakeの利用費について知りたいユーザーがたくさんいる場合、BIツールでダッシュボード化することで、ACCOUNTADMINロールのユーザーを増やすことなく、利用費の情報を共有することができます。

dbt（Package）を使う必要性

実は下記のようなものが既にLookerにあります。

上記の方法をとれば、わざわざdbtを使う必要はありません。

今回使ったPackageのミソはレートの変動を加味した計算ができることだと思っています。上記の方法でもSnowflakeの利用状況を可視化することはできますが、クレジット止まりです。金額までの計算はしてくれません。ちなみに、Snowflakeの管理画面もクレジットでの確認になります。

Snowflakeの利用開始からレートが一切変わっていなければ、そのレートをクレジットに掛ければすみますが、レートの変動があった場合、途端に計算が面倒になります。このdbt Packageが、そこを踏まえて計算してくれるのが便利です。

本番運用するのであれば

今回作ったダッシュボードは、dbtで生成したデータモデルが基になっています。つまり、ダッシュボードの情報を更新するには、dbt側を定期的に実行して、常に最新の計算結果を生成しておく必要があります。もっというと、dbtの本番運用を検討する必要があります。

dbtの本番運用については、下記をどうぞ。

おわりに

他社のダッシュボードを別のBIツールで再現するっていうの、意外と勉強になります。

スキルアップ|初心者向け|書評|デベキャン|AWS認定|スキルアップ|デベキャンだより|勉強会|コミュニティ

関連記事

dbtの認定試験『dbt Analytics Engineering Certification Exam』が5月から新しくなるようです #dbt

しんや

2024.04.11

ストアドプロシージャからdbtへの移行手順 #dbt

しんや

2024.04.05

dbtでのSQLモデル記述時に利用推奨されている『共通テーブル式(CTE/Common Table Expression)』について

しんや

2024.03.31

dbtにおける『SQLの記法』に関するベストプラクティス #dbt

しんや

2024.03.31