はじめに

こんにちは。okodooonです！！
データ基盤を参照したアウトプットが社内に溢れかえっていませんか？
弊社は追いきれていないLookerStudioやConnectedSheetがめちゃくちゃ溢れかえっていました。
そんな折、yoshidaさんの以下の記事を拝読いたしまして、今回の実装に至った次第でございます。

LookerStudioの記事	ConnectedSheetの記事
www.yasuhisay.info	www.yasuhisay.info

面識ないですがこの場を以て感謝の意を表させていただきます！ありがとうございます！

課題感・背景

使用しているBIツールについて

弊社ではBIツールを数種類利用して、BigQueryデータ基盤上のデータを活用しています。
以下がその一覧とざっくりとした役割です。

Looker: 社内の主要な分析プロセスをカバーするセマンティックレイヤーを提供
LookerStudio: アドホックなレポーティング、Lookerでカバーしきれていない指標を用いたダッシュボード構築
GoogleスプレッドシートのConnectedSheet: スプレッドシート上でビジネスメンバーが作業する際のデータソースとしての使われ方
Redash: 元々LookerStudio同様の使われ方をしていた。ガバナンス向上とSSoT実現のために廃止作業中

Looker経由のアウトプットであれば、ソースデータに変更が発生したり社内の指標出力ロジックに修正が発生した場合に、ディメンショナルモデリング層で吸収したりLookerのContentValidator機能などでガバナンスを効かせることができます。
しかしBIツール側に直接クエリを書く形となるLookerStudioとConnectedSheetを用いたアウトプットの保守とガバナンスが問題となっていました。

BIツールの使用ボリューム感について

こんな感じのクエリで調査しています。

SELECT
    DISTINCT
    JSON_VALUE(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") AS sheet_id,
FROM
    `example-project.bq_usage_logs.cloudaudit_googleapis_com_data_access_*`
WHERE
    protopayload_auditlog.serviceName = "bigquery.googleapis.com"
    AND JSON_VALUE(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") IS NOT NULL

SELECT
  DISTINCT
  label.value AS report_id,
FROM `example-project`.`region-xx`.`INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION`,
UNNEST(labels) AS label,
WHERE label.key = "looker_studio_report_id"

過去半年にクエリが走ったConnectedSheetの数: 600個強
過去半年にクエリが走ったLookerStudioの数: 400個強

前述したロジックやソースシステム側の破壊的な変更への対応工数の観点以外にも、もう使われていないConnectedSheetに配信し続けている可能性などが考えられるため、まずはアウトプットを管理できる体制が必要であると考えました。

やったこと：概要

以下のような処理の流れを構築しました。

exposure登録情報を出力するviewをdbtで構成
以下の処理をweeklyで実行
- viewの結果を取得してexposureのyaml形式に変換
- アウトプット単位でexposureのyamlファイルを作成
- 未登録と変更があったexposureを登録するpull requestを作成

参考にしたブログから、弊社の運用に合わせて変更した点は以下です。

exposure単位でyamlファイルを作成する方針に変更しました
referenced tableにテーブル名ではなくdbtモデル名が入るようにしました
各種アウトプットの公開設定をmeta情報として付与する方針としました
tagを追加してexposureの検索性を向上させました
exposureのnameにシートとダッシュボードのタイトルを反映する方針にしました

今回の実装によって以下のような形式のexposure用のyamlファイルが自動生成されます。

version: 2
exposures:
- name: {{ConnectedSheetタイトル}}_{{ConnectedSheetId}}
  label: {{ConnectedSheetId}}
  type: dashboard
  tags:
  - shared_externally
  - spreadsheet
  url: https://docs.google.com/spreadsheets/d/{{ConnectedSheetId}}
  owner:
    name: test@example.com
    email: test@example.com
  depends_on:
  - ref('hogehoge_model')
  - ref('foo_bar_model')
  - ref('chomechome_model')
  meta:
    visibility: shared_externally

やったこと：詳細

説明があるとわかりやすそうな部分の詳細を記載していきたいと思います。

referenced tableにテーブル名ではなくdbtモデル名が入るようにしたことについて

弊社はdbtモデル名とBigQuery上のテーブル名が一致しないため、INFORMATION_SCHEMAやaudit_logから取得したテーブル名をref関数化してもリネージュを作成できません。

そのため、dbtモデル名とBigQuery上の対応関係を取得するためにdbt-elementary実行時に生成されるdbt_modelsテーブルを活用しました。
このテーブルは

カラム名	内容
dbt_models.name	dbtモデル名
dbt_models.alias	BQテーブル名
dbt_models.schema_name	BQデータセット名
dbt_models.database_name	BQプロジェクト名

このような情報を持つカラム群を保持しています。
このテーブルを活用することでINFORMATION_SCHEMAが保持するBQテーブル名をdbtモデル名に変換してref指定することができています。

各種アウトプットの公開設定をmeta情報として付与する方針としたことについて

https://support.google.com/a/answer/9079364?hl=ja

google workspaceのactivityログを使うことで、LookerStudio,SpreadSheetの公開設定とタイトルを取得することができるので、それらを取得しています。

SELECT
    data_studio.asset_id AS report_id,
    data_studio.asset_name AS report_name,
    data_studio.visibility AS visibility
FROM example-project.google_workspace.activity
WHERE activity.data_studio.asset_type = 'REPORT' AND activity.event_name = 'VIEW'

SELECT 
    drive.doc_title AS sheet_title,
    drive.doc_id AS sheet_id,
    drive.visibility
FROM `example-project.google_workspace.activity` 
WHERE drive.doc_type = 'spreadsheet'

tagを追加してexposureの検索性を向上させたこと

dbt exposureはmeta, owner, typeなどの情報を使って、dbt lsコマンドなどでアウトプットを一覧することができません
dbt ls --resource-type exposure --type dashboard --owner example@example.com
みたいなことがしたいのですができないです。

そのため、exposureに対して適切なtagを付与することで絞り込みができるようにしました！

今回は公開設定,アウトプット種別の二つをtagとして持たせました。
これによって
「xxx_modelを参照しているshared_externally設定にしているスプレッドシート一覧を出したい」という要望に対して
dbt ls --select xxx_model+,tag:shared_externally,tag:spreadsheet --resource-type exposure
このようなコマンドで出力ができるようになります

exposureのnameにシートとダッシュボードのタイトルを反映する方針にしたこと

Add Exposures to your DAG | dbt Developer Hub

こちら公式Docにexposureのnameに指定するのはスネークケースにしてくださいという記載がありますが、なんとスネークケースにしなくても日本語名でも通ります!（名前をユニークにする必要はあります）
そしてexposure.nameがリネージュ上で表示される名前なので、データリネージュの可視性を高めるためにLookerStudioとコネクテッドシートのタイトルをnameに含む形で設定している状態です。

LookerStudioID, SpreadSheetIDだけをnameにすると、このようにデータリネージュ上でどのアウトプットのexposureかぱっと見判断がつかないのですが {{タイトル}}_{{ID}}の形式にすることでデータリネージュ上の可視性を確保した形です。

今後の発展

保守運用の設計

アウトプットが全件自動で管理されるようにはなったことで、「ソースシステムに影響が起きた場合」や「算出ロジックに変更が生じた場合」の影響範囲は迅速に把握できるようになりました。

しかし使われていないことがわかったアウトプットに対してどのようにアクションしていくのか、フローが組めていない状態です。
管理ができるようになった上でどのように運用改善に繋げるのか。どのように削除やdeprecatedにしていくのか。あたりのフローはしっかりと組んで、ユーザーの目に触れるアウトプットの品質の最大化に努めていきたいです。

カラムレベルリネージュ ✖️ exposure

dbt cloud enterpriseではカラムレベルのリネージュがexplore上で確認できるようになりました。
docs.getdbt.com 重要なアウトプットはBIツール側にクエリを書くのでなく、dbt側にそのアウトプット専用のマートを作成することで、カラムレベルでの影響範囲調査が可能となるのではと考えています。

before	after

この図でいうbeforeからafterの構成に変えることでマートまではdbtで管理されるようになるため、dbt exploreのcolumn level lineageで可視化することで、カラムレベルでのアウトプットへの影響範囲を確認可能です。
こうすることで、「このテーブルに何かしら変更を加えたら最大でどのくらいの数のアウトプットに影響があるんだろう」から「このテーブルのこのカラムを消したらどのアウトプットに影響があるんだろう」まで影響調査の解像度を上げることができます。

こういった理由から、アウトプット専用マート作成の取り組みを始められたらなと思っております。
(column level lineageは現状dbt exploreで見ることができるだけですが、もうちょっと使いやすくなって欲しいです)