Zenn
😎

サーバーサイドエンジニアにdbtを宣伝したお話

2022/12/24に公開
モデリング
データ基盤
bq
dbt
tech

こんばんは！
uniposアドベントカレンダー24日目の記事です

弊社では週1で各チームの技術共有などを発表する場があります。自分はデータエンジニアという立場ですが、業務でサーバーサイドと関わることが多いので発表する機会を何度かいただきました。共有回でdbtのことを紹介したので、今回はその内容をまとめました！

背景

  • 課題感
    • システム側で実装されている集計クエリ等は、継続で開発するというより一度実装しておしまいなケースが多いため、実装等を見返すケースが少ない(これは単なる個人的な感想です)
    • サーバーサイドエンジニアに集計の仕様を確認する度に、うん百行のSQLを頑張って読んで仕様を紐解いている。辛そう
    • 集計クエリって仕様が複雑だが実装も一度きりなので改善等の手直しが発生せず、将来的に周りで負債になりそう
    • データ基盤側で集計クエリの仕様を把握しなければならないケースがあり、ここの負債が将来の自分たち(データ基盤チーム)の首を締めそうだ・・
  • 自分が思ったこと
    • 普段の業務でSQLを書く機会が多く、データ基盤の保守運用でかなり苦しい経験をしたので、このあたりの知見は結構ありそう
    • dbtを使って保守運用コストがかなり下がったので、技術共有の発表ネタとしては面白そう？!
  • 発表を通じて意識したこと
    • あくまでも「データ基盤がdbtを使ってどんな課題が解決出来たのか？」を話す
    • 細かい話は本当にしない!あくまでも「○○の苦しみが△△で解決できてるじゃん〜」というのが伝わればいい
    • dbtはあくまでも手段。こういう手段あるんだ〜興味ある〜という機運を作りたい(だけ)
    • 実際にシステム側の集計クエリの課題はどうするの？は別問題なのでそこは一切考えない

共有回で話したこと

  • 何回かにわけて発表したものを以下にまとめます。
  • dbt導入前に頑張ってたSQLと、dbt導入でどうシンプルになったのか？を説明してます
    • 勿論dbtじゃなくても解決できるじゃんってものはありますが、その意見は今回スルーで笑
  • 各ケース、弊社で使ってるデータを例に発表したのですが、これをこの記事にそのまま載せても意味わからないと思うので、読んでくださってる皆様にイメージしやすいように勝手にtwitterっぽいシステムに置き換えます
    • なのでクエリのカラム名もテーブル名も架空です。あくまでもイメージね！！！

ケース① システムの仕様変更を楽に対応したい

お題 ユーザのフォロー情報と、投稿に関する色んな情報まとめたい

  • お題が適当ですが、以下の情報をまとめたい感じです
    • ユーザが何人フォローしているのか
    • ユーザの投稿頻度やリツイート数
    • 投稿が自分のフォロワーにリツイートされてるか
  • モデリングが適当ですが「ユーザのフォロー情報テーブル」と「投稿がフォロワーにリツイートされてるかテーブル」を作ってみます

dbt導入前

(あんまりクエリの中身自体はどうでいいです)

  • ユーザのフォロー情報テーブルはこんな感じ

WITH user AS (
    SELECT 欲しいユーザ情報
),
user_follow AS (
    SELECT ユーザのフォロー情報をいい感じにARRAY_AGG()するヤツ
)
SELECT
    user_id,
    user_follow_list
FROM 上記のテーブル結合したやつ
  • 投稿がフォロワーにリツイートされてるかテーブルはこんな感じ
WITH tweet AS (
  　　　SELECT ツイートと誰にリツイートされたのかいい感じにまとめるヤツ
),
user_follow AS (
   　SELECT ユーザのフォロー情報をいい感じにARRAY_AGG()するヤツ
)

SELECT
    tweet_id,
    tweeted_by AS user_id,
    retweet_user_list,
    　フォロワーにリツイートされたか判定FLAG
　FROM 上記のテーブル結合したやつ
  • 今回ユーザ情報に関して大幅にシステム改修が入りまして、DBが変わります
  • ユーザにまつわる、全てのスキーマが変わります
  • さて、上記2つのSQLも変更をしなければ！
  • 「投稿がフォロワーにリツイートされてるかテーブル」は、フォロー情報を参照しているので、こいつもめっこり修正対象になります
  • 修正範囲が広い、辛い

dbt導入後

  • dbtには stg層 mart層という層があるよ、ざっくりこんな感じ
    • stg層: SELECT 欲しい情報を軽く加工する FROM DB
    • mart層: stg層からクエリして実体化する
  • 今回はDBに対応するstgテーブルを1対1で作ってみたよ
  • DB移行前だとこんな感じ（テーブルのモデリング周りのツッコミは一切無しで!笑）

  • DB移行後だとこんな感じ（赤色線とオレンジ枠が今回の修正箇所となる）
  • DBの向き先やそこの仕様はstg層のテーブルで変更を吸収する
  • 実体化担当のmart層は、変更影響受けないように対応が可能
  • レイヤードアーキテクチャっぽい話かもね

  • それWITH句でいい感じにまとめれば、dbt無くても変更対応出来るくね？
    • 出来る思う。実際自分は一度、大規模なシステム変更の修正対応やりきった
    • 大事なのはモデリングして、意味のある適切な粒度で分割すること
    • 上記の図だと stg_member のクエリがそのまま使いまわし出来たりするし！
  • 超雑なイメージ
    • dbt導入前: 1個のHTMLやjsを超頑張って書く
    • dbt導入後: Reactでいい感じにコンポーネントを分割してそれをwebpackでビルドして1ファイルとして出力するよ！開発しやすいよね！

ケース② データの変更履歴を持ちたい

お題 ユーザの自己紹介文の履歴を持ちたい

  • データ分析する上で、自己紹介ってどんな頻度どんな分量で変わってるのか調査したい
  • しかし本番DBって最新情報しか保持されていないので

dbt導入前

  • 以下のクエリを毎日ストックして保持してました
  • 特定の日付で各ユーザの名前や自己紹介文を復元出来る
SELECT
    CURRENT_DATE() as snapshot_date,
    user_id,
    user_name,
    profile_text
FROM DB.User
  • しかし、例えば1年間プロフィール情報一切変えてない場合、snapshot_date以外365件同じレコードが存在する
  • 価値としては1レコードしか情報持っていないのに、ストックしている日付分だけレコード持つ
  • データ量が多いことにボトルネックがあるわけではないが、なんか微妙だな〜

dbt導入後

  • dbtのsnapshotという機能を使う
    • 指定されたDBの変更を検知して、よしなにスナップショットとして保持出来る
  • 変更検知の仕方は2種類
    • 上書きされ得るカラム名を指定(以下例はuser_name,profile_text,profile_image_urlの3つ)
    • modifiedAtやupdatedAtなどのカラムを使って、レコードが上書きされた時刻から検知する
  • これ、賢いなーって知った当時思いました
{% snapshot user %}
{{
    config(
        target_schema="snapshots",
        unique_key="user_id",
        strategy="check",
        check_cols=[
            "user_name",
            "profile_text",
            "profile_image_url"
	],
        invalidate_hard_deletes=True
    )
}}

SELECT
    user_id,
    user_name,
    profile_text
FROM {{ source('DB', 'user') }}

{% endsnapshot %}
  • snapshotデータは以下のように保持される
  • WHERE dbt_valid_to IS NULL で検索すると各user_idに対する最新のレコードが取得できる
  • dbt_valid_to と dbt_valid_from の日付をいい感じに挟めばその時点での情報が取得できる
  • 毎日同じテーブルを保持していた時と比べて、かなりスッキリ！

ケース③　複雑な仕様もシンプルに表現したい

データ基盤で一番仕様が複雑だったケースを持ってきました。

お題 ユーザのスマホ、ブラウザ通知の設定情報を取りたい

  • フォロー、お気に入り、サジェスト、リプライ、リツイート、DMなど通知の種類がたくさんある
  • スマホ、ブラウザ、メールで設定可能な通知が違う
  • 通知設定の情報DBを取得するだけでは意味がない
    • アカウント削除したユーザの通知情報が残っていたり
    • スマホ通知に関して通知ONのレコードがあっても「スマホ本体の通知許可しますか？」が許可されてない場合は意味ないので、他情報と組み合わせが必要
  • アカウント作成時に通知の初期設定が、設定項目ごとに違うし、DBに入る値も違う
    • 初期値TREEとしてレコードが有るケースと、初期値レコード無し = TRUEと解釈、というケースがある
  • 要するに分析するのにめちゃめちゃ不都合なシステム仕様になってる。ツライよ！
  • 発表時はこんな感じで説明してました
    • こんな情報が取りたいけど、プロダクト仕様が○○でDB仕様が△△だから、要件がこんな感じ
    • こんなクエリ書いちゃうとこういうケースに落とし穴があるからかなり複雑
    • AとBとCとDとEとFとGとHを考慮しながら取らなきゃいけないんだよね、ヤバッ！

dbt導入前

  • 700行のSQLを書いてました。通知のON/OFFが欲しいだけなのに・・・ｗ
  • 実装した自分でさえ、3ヶ月後に見直すのにかなり体力が居る
  • 長いクエリって健康に悪い

dbt導入後

  • 通知.sql自体は60行になりました
  • 各媒体都合やシステム都合の加工作業は、stg層にて
  • 意味のある単位で分離するの大事

まとめ

  • サーバーサイドエンジニアに、dbtの魅力どうしたら伝わるのかな〜〜〜と考えるの結構楽しかった
  • データ基盤の知見をプロダクト方面にも還元出来ると思っているので、2023年はそのあたりを目論んでます

以上です。ありがとうございました〜！

Discussion