メタデータでdbtモデルのコストと性能を可視化

dbtはこの5年で急速に普及し、データウェアハウス内でデータモデルを構築・管理するフレームワークとして最も広く使われる存在となりました。一方で、ウェアハウス内で大規模データに対して多数の変換処理を行うコストは決して小さくありません。dbtに限らずSQLベースの変換ツール全般において、こうした変換処理はSnowflakeユーザーのコンピュートコストの大きな部分を占めるのが一般的です。

データクラウドの支出を正確に把握し、モニタリングして削減したいというニーズが高まるなか、dbtモデルごとのコストを可視化することの重要性は増しています。さらに、ビジネスクリティカルなアプリケーションや意思決定をdbtで支えるケースが広がるにつれ、SLAを確実に満たすためにモデルのパフォーマンス監視も不可欠になってきました。

Snowflakeとdbtを組み合わせて使うだけでは、こうした重要なモニタリング機能は標準では手に入りません。クエリタグやコメントでdbtモデルにメタデータを付与することで、これらの基本的なモニタリングが実現できます。

クエリタグの解説記事では、dbtでクエリタグを設定する3つの方法を紹介しました。

1. profiles.ymlでグローバルに設定する
2. dbt_project.ymlまたはモデル設定で、モデルごとにquery_tagを指定する
3. set_query_tagマクロを作成し、プロジェクト内の各モデルに動的にクエリタグを設定する

3つ目のアプローチは、手動でタグを設定する手間がなく、群を抜いておすすめです。モデルごとに動的にクエリタグを設定したい場合は、こちらのようなカスタムマクロを実装すれば、dbtが発行する各クエリに詳細なメタデータを付与できます。

dbt関連のメタデータについては、クエリタグではなくクエリコメントの利用をおすすめします。自動生成されるメタデータがクエリタグの2000文字制限を超えてしまうことがあるためです。

dbtにはこの機能が標準で備わっています。dbt_project.ymlに次の設定を追加してください。

1query-comment: append: true # Snowflake removes prefixed comments

これでクエリの末尾に次のようなコメントが付与されます。

1create view analytics.analytics.orders as ( select ... ); /* {"app": "dbt", "dbt_version": "0.15.0rc2", "profile_name": "debug", "target_name": "dev", "node_id": "model.dbt2.my_model"} */

より充実したメタデータをクエリコメントに含めたい場合は、当社のdbt-snowflake-monitoringパッケージを導入してください。このパッケージを使うと、すべてのdbtクエリで次のメタデータが利用できるようになります。

1{ "dbt_snowflake_query_tags_version": "2.0.0", "app": "dbt", "dbt_version": "1.4.0", "project_name": "my_project", "target_name": "dev", "target_database": "dev", "target_schema": "larry_goldings", "invocation_id": "c784c7d0-5c3f-4765-805c-0a377fefcaa0", "node_name": "orders", "node_alias": "orders", "node_package_name": "my_project", "node_original_file_path": "models/staging/orders.sql", "node_database": "dev", "node_schema": "mart", "node_id": "model.my_project.orders", "node_resource_type": "model", "materialized": "incremental", "is_incremental": true, "node_refs": [ "raw_orders", "product_mapping" ], "dbt_cloud_project_id": "146126", "dbt_cloud_job_id": "184124", "dbt_cloud_run_id": "107122910", "dbt_cloud_run_reason_category": "other", "dbt_cloud_run_reason": "Kicked off from UI by [email protected]", }

これらの情報を活用すれば、dbtプロジェクト、モデル名、環境(devまたはprod)、マテリアライゼーションの種類など、さまざまな切り口でコストとパフォーマンスをモニタリングできます。

クエリタグを使っている場合は、次のクエリをベースにdbtモデルのパフォーマンスをモニタリングできます。

1select date_trunc('day', start_time) as date, try_parse_json(query_tag)['model_name']::string as model_name, count(distinct try_parse_json(query_tag)['invocation_id']) as num_executions, avg(total_elapsed_time/1000) as avg_total_elapsed_time_s, approx_percentile(total_elapsed_time/1000, 0.95) as p95_total_elapsed_time_s, avg(execution_time/1000) as avg_execution_time_s, approx_percentile(execution_time/1000, 0.95) as p95_execution_time_s -- optionally repeat for other query time metrics, like: -- compilation_time, queued_provisioning_time, queued_overload_time, etc. from snowflake.account_usage.query_history where try_parse_json(query_tag)['app']::string = 'dbt' and start_time > current_date - 30 group by 1,2

クエリコメントを使っている場合は、まずコメント文字列からメタデータを抽出する必要があります。

1with query_history as ( select *, regexp_substr(query_text, '/\\*\\s({"app":\\s"dbt".*})\\s\\*/', 1, 1, 'ie') as _dbt_json_meta, try_parse_json(_dbt_json_meta) as dbt_metadata from snowflake.account_usage.query_history ) select date_trunc('day', start_time) as date, dbt_metadata['model_name']::string as model_name, count(distinct dbt_metadata['invocation_id']) as num_executions, avg(total_elapsed_time/1000) as avg_total_elapsed_time_s, approx_percentile(total_elapsed_time/1000, 0.95) as p95_total_elapsed_time_s, avg(execution_time/1000) as avg_execution_time_s, approx_percentile(execution_time/1000, 0.95) as p95_execution_time_s -- optionally repeat for other query time metrics, like: -- compilation_time, queued_provisioning_time, queued_overload_time, etc. ... from query_history where dbt_metadata is not null and start_time > current_date - 30 group by 1,2

当社のdbt-snowflake-monitoringパッケージを使えば、このクエリコメントのパース処理は自動で行われます。

上記のクエリはいずれもクエリの実行時間のみを対象としています。あわせてチェックしたい指標としては、次のようなものがあります。

• partitions_scannedとpartitions_total: クエリが効率的にマイクロパーティションをプルーニングできているかを確認できます
• bytes_scanned: 処理されているデータ量の推移を把握でき、実行時間の増加要因を特定する手がかりになります
• bytes_spilled_to_local_storageとbytes_spilled_to_remote_storage: より大きなウェアハウスでの実行が有効かどうかを判断できます
• queued_overload_time: ウェアハウスのmax_cluster_countの引き上げが必要かどうかの目安になります

dbtモデルのコストを継続的にモニタリングするには、まずSnowflakeクエリごとのコストを算出します。そのうえで、クエリコストをモデル単位で集計します。

1select date_trunc('day', start_time) as date, try_parse_json(query_tag)['model_name']::string as model_name, count(distinct try_parse_json(query_tag)['invocation_id']) as num_executions, sum(query_cost) as total_cost, total_cost / num_executions as avg_cost_per_execution from query_history_w_costs where try_parse_json(query_tag)['app']::string = 'dbt' and start_time > current_date - 30 group by 1,2

各クエリのコストを付与したクエリ履歴を自動で取得したい場合は、当社のdbt-snowflake-monitoringパッケージをインストールしてください。

SELECTでは、クエリタグとコメントを活用し、お客様がdbtから発行されたクエリを環境、マテリアライゼーション、リソースタイプなど、さまざまな切り口でモニタリングできるようにしています。こうした可視性が得られると、データチームはどのdbtモデルに優先的に手を入れるべきかを容易に判断でき、非常に大きな価値があります。プロダクト内での表示例は以下のとおりです。

Snowflakeのコスト削減、コストドライバーの把握、あるいは日々の動向の確認にご関心のある方は、以下のリンクから今すぐご利用いただくか、デモをご予約ください。

Ian Whitestone・Co-founder & CEO of SELECT

Ianは、SaaS型のSnowflakeコスト管理・最適化プラットフォームであるSELECTの共同創業者兼CEOです。SELECT創業以前はShopifyとCapital Oneで6年間にわたり、フルスタックのデータサイエンス&エンジニアリングチームを率いてきました。Shopifyでは、データウェアハウスの最適化とコスト可視性の向上に向けた取り組みをリードしました。