ちゅらデータ株式会社Publicationへの投稿
✨

【社内アドバイザリー】監査ログとして各種データを保存したいのですが考慮すべき点は？

Gaku Tashiro＠ちゅらデータエンジニア
に公開
Snowflake
tech

がく＠ちゅらデータエンジニアです。
こんばんはっ！！

現在、ちゅらデータのデータエンジニアギルドでは、社内アドバイザリーって取り組みをしています。

社内アドバイザリーとは

弊社では様々なデータエンジニアリングな案件をさせていただいております。
データエンジニアリングはとても範囲が広範で、やったことがなかったり、どうすればいいのか？あたり非常に迷うことが多いと思います。
そこに対して、有識者として主にシニアデータエンジニアがアドバイザリーを行い、デリバリーの品質を担保していこう！
って取り組みです。

黄色い「ni-gaku」がまぁるくおさめまっせ！！！

黄色い「ni-gaku」がまぁるくおさめまっせ！！！
※分かる人おるやろうか・・・じぇねれーしょんぎゃっぷぅぅぅ

それでは今回の相談はこちらです
「
SnowflakeのAccout_Usageデータなどを監査ログとしてS3にcopy intoを行うのだが、
考慮漏れがないか心配なので、その辺り考慮した方が良い点を聞きたい
」
どのような点について考慮・検討したかを聞いてみました

  • AWS S3 bucketに書き出す
  • 監査ログなので削除ができないように設定している
  • Intelligent Tieringを使って、古いものを自動的に安くするように設定している
  • SnowflakeでTASKでしているが、失敗したときにNotificationで通知が飛ぶようにしている
  • QUERY_HISTORYの反映にタイムラグがあるので、前日分を処理するようにしている

Intelligent Tieringの仕様について

「Intelligent Tieringを使っているのはなぜ？Intelligent Tieringはアクセスパターンによって使うストレージを最適化するものですけど、監査ログならそもそも　S3　Glacier Deep Archive に入れるのでもいいのではないか？」

Intelligent Tiering
https://qiita.com/pohd_ccoe/items/9463d71e602e6eda40c5

https://aws.amazon.com/jp/s3/storage-classes/intelligent-tiering/

Amazon S3 Intelligent-Tiering は、データのアクセスパターンが変化したときに、パフォーマンスへの影響や運用上のオーバーヘッドを発生させることなく、ストレージコストを自動的に削減できる唯一のクラウドストレージクラス

S3のタイプによるPrice
https://aws.amazon.com/jp/s3/pricing/

S3標準

S3 Glacier Deep Archive *** - 1 年に 1〜2 回アクセスされ、12 時間以内に復元できる長期のデータアーカイブの場合
USD 0.002/GB

あれ・・・・東京リージョン、全然安くないぞ・・・・　USD 0.002/GB
米国東部（バージニア北部）は、USD 0.00099/GB　なのに

※このあたりは、AWSなのでは当たり前のことなんだろうか・・・・

監査ログを取る方式について

SnowflakeのACCOUNT_USAGEの各種データを監査ログとして取得するにはいくつか方法があると思います。

  1. 実テーブルに書き出す方式
  2. S3へCOPY INTOで書き出す方式
  3. Snowflakeの「SNAPSHOT機能」を用いる方式
  4. 組織アカウントのプレミアムビュー機能を使う

順に説明をします

実テーブルに書き出す方式

こちらは、一番お手軽な方式です。
SnowflakeのTASK機能を用いて日次で実施し、
単純なINSERT　INTOを使います。

デメリットとしては、ACCOUNTADMINなど強いROLEでは「消せてしまう」
一応、こちらを配置するスキーマなどの権限を絞って、特定のロールでしか見えないようにして、セキュリティを強化することはできると思います。

以下は、例です。

    INSERT INTO BACKUP_DB.LOG_SCHEMA.LOGIN_HISTORY (
      SELECT
          EVENT_ID,
          EVENT_TIMESTAMP,
          EVENT_TYPE,
          USER_NAME,
          CLIENT_IP,
          REPORTED_CLIENT_TYPE,
          REPORTED_CLIENT_VERSION,
          FIRST_AUTHENTICATION_FACTOR,
          SECOND_AUTHENTICATION_FACTOR,
          IS_SUCCESS,
          ERROR_CODE,
          ERROR_MESSAGE,
          RELATED_EVENT_ID,
          CONNECTION,
          CLIENT_PRIVATE_LINK_ID
      FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
      WHERE EVENT_TIMESTAMP > COALESCE((SELECT MAX(EVENT_TIMESTAMP) FROM BACKUP_DB.LOG_SCHEMA.LOGIN_HISTORY), '1900-01-01'::TIMESTAMP)
    )

たたくSQLはこんな感じ（DB名とかスキーマ名は適宜直してくださいまし）
これを取得するVIEWごとにTASKを作って実行する感じです。

S3へCOPY INTOで書き出す方式

こちらの方式を使っているのが多いのではないかなと推測します。

  • COPY INTO ＜場所＞　で書き出す
  • 書き出し場所は、保護していないバケット（S3 tempbucket)（なぜなら、バージョニングされたバケットには直接おけない・・・・Snowflakeさーーん、直してぇぇぇ）
  • S3 Object Lock＋バージョニングで保護　を作る
  • 一時書出しBucket→保護BucketへS3へS3レプリケーションを張る
  • メリットは、Snowflakeの外に置くことで、保護ができる
    • あえてSnowflake外に置くことで、より「消しにくくする」的な
    • 仮にSnowflakeのアカウントを停止・削除しても、監査ログは残る
  • （Stageに与える権限で、消せるようにしたらダメだよ）
  • TASKで実行（ここは別のWorkflowエンジンを使うのもあり）
  • ACCOUNTADMINで消せません！Snowflake外で管理するため※重要

ちなみに、保護S3BucketにCOPY INTOしようとすると

Content-MD5 OR x-amz-checksum- HTTP header is required for Put Object requests with Object Lock parameters

デフォルト保持のかかったバケットへPutするならHTTPヘッダーにMD5 or x-amz-checksumをつけないといけないです。

というエラーになります。

SnowflakeのSNAPSHOT機能を使う

https://docs.snowflake.com/en/release-notes/2025/other/2025-08-18-worm-snapshots
2025年8月にPublicPreviewになった機能
この機能が出たときは、やっとキターーーーーーーーーーーーーーーーーーーーって感じでした

https://docs.snowflake.com/en/user-guide/snapshots

スナップショットは、Snowflakeオブジェクトの個別のバックアップを表します。バックアップするオブジェクト、バックアップの頻度、バックアップの保存期間、そしてバックアップが早期に削除されないように保持ロックを追加するかどうかを選択できます。

今後はこちらを使ってやるのがいいのではないか？と思っています。

https://dev.classmethod.jp/articles/snowflake-try-snapshot/
DeveloperIOさんでさがらさんがブログを書かれていますね。

対象としては、オブジェクトは、単一のテーブル、スキーマ、またはデータベース全体
これを使えば、なんとでもなりそうですね

※まだやったことがないので、後日検証します

  • ACCOUNTADMINでも消せない
  • CLONEでゼロコピーなのでストレージコストは増えない（ハズ）

ORGACCOUNTのPremiumViewを使う方法

ログの保存としては、組織アカウントのPremiumViewで取り込む方法もある
が、PremiumViewはとてもお金がかかるので注意

https://docs.snowflake.com/ja/user-guide/organization-accounts-premium-views

GAが今年6月なので、出来立てのほやほやですね
※ちなみに、これが出たときに、PremiumViewがデフォルトでONになっていて、急にコストが跳ね上がったという事象が発生しておりました（その後サポートチケットを上げて、OFFにしてもらいました）
https://docs.snowflake.com/ja/ja/release-notes/2025/other/2025-06-24-premium-views

ログを見たい　ACCOUNT_USAGE　　vs INFORMATION_SCHEMA

ログは基本的には、ACCOUNT_USAGEを見ることを推奨しています。
INFORMATION_SCHEMAの下のViewは、関数での実装になっています。
関数を動かして生データを集計するなどをしています。
なので、リアルタイム性は高いが、クラウドサービスの料金が増えるのでそこが注意

S3バケットの構造とは、階層構造にしておく方がよい

query_history/2025/01/20250101.parquet
query_history/2025/01/20250102.parquet
query_history/2025/02/20250201.parquet
login_history/2025/01/20250101.parquet
login_history/2025/01/20250102.parquet

こんな雰囲気
S3 Bucketならフラットに置いてもいいかもなのですが、
後々、仮にファイルを取り込み直す場合（＝異常時）に、取り込み範囲を絞れる形の方が良きかなとおもっています。

https://qiita.com/hoto17296/items/92e8d4bdb9d363420b62
この辺を考慮に入れておくと良き


こちらを参考までに
Snowflakeでは、「走査量」がホントパフォーマンスに影響するんですよねぇ

ステージ分けるなどは不要で、ステージのサブディレクトリでも実現できる

例えば、去年の◯月とかそういう単位で。（ここは用途次第になるけど）

まとめ

エンプラ用途では必須のSnowflakeのログ（ACCOUNT_USAGEにあるQUERY_HISTORYやLOGIN_HISTORYなど）の保存について技術的に検討してみました。
具体的な部分は出し切れておらず、ここは今後の宿題にしたいですね

宿題）

  • SNAPSHOTを使った検証
  • S3に書き出す仕組みの詳細書きたい
  • 監査ログ用の仕組みをマーケットプレイスに出せたら世の中の人達助かるよなぁ〜（野望）

宣伝

なお、この文章の校正には、ちゅらデータ謹製の

https://chuwrite.com/

を用いています！

ちゅらデータ株式会社
ちゅらデータ株式会社Publication

沖縄に「最高に面白い仕事」を作る IT ベンチャーです！ エンジニア積極採用中！！

Discussion