dbtとLookerを使ってSnowflakeの利用費が確認できるダッシュボードをつくってみた
大阪オフィスの玉井です。
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:
- package: gitlabhq/snowflake_spend
version: 1.2.0
dbt Package自体のインストールの仕方は下記をどうぞ。
csvファイルを用意して、dbt seedでSnowflakeにロードする
このPackageは、実行する前に、少しだけ下準備が必要になっています。
下記のようなcsvファイルを用意し、dataディレクトリに格納します。
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ファイルを作成します。
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は超シンプルです。
explore: snowflake_warehouse_metering_xf {}
PeriscopeのクエリをExploreで再現する
Exploreができたので、後はこれを使ってシコシコとタイルを再現していきます。
再現の方法ですが、コツみたいなものがあります。例えば、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ツールで再現するっていうの、意外と勉強になります。
![[小ネタ]dbt-databricksでINCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_WEEK_BASED_PATTERNエラーが発生したときの対処方法](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
![[Tips] dbt のドキュメントをStreamlitで表示する方法](https://images.ctfassets.net/ct0aopd36mqt/wp-refcat-img-a0f70de29a00d596aa50512303131e72/2fcbfee571e6db80cd904c3c64f7cecc/dbt-1200x630-1.jpg)
