❄️

Snowflakeで、本番、ステージング、開発環境で使うデータを用意する

Gaku Tashiro＠ちゅらデータエンジニア

2024/12/13に公開

Snowflake

tech

がく＠ちゅらデータエンジニア(50)です。

はっぴばーすでーとぅみーー、はっぴばーすでーとぅみーー

天命を知る・・・そんな年齢になりました!

50代、パワフルに生きていきたいと思っています！
こちらは

https://qiita.com/advent-calendar/2024/snowflake
のシーズン3の12日目の記事になります。

※寝落ちして公開が一日遅れてしまったorz

 開発環境、ステージング環境、本番環境についてさて、皆さんは、あるプロジェクトで開発をするときは、環境をどのように用意されますか？
本番環境
ステージング環境
開発環境
の三環境を用意して開発を進めるのが理想ですよね。

※ソフトウェア開発上で使用される重要な3つの環境
CI/CDを用意するので環境ごとに構築するのはそこまで大変ではありません。

※データエンジニア的に、Terraformはホント必須スキルかなーーーって思っています。
この3環境について、ChatGPTで聞いてみました。

----- ここからChatGTP -----

開発環境
目的
開発者がコードを記述、テスト、デバッグするための環境
新機能の実装やバグ修正を行う場所

特徴
開発者ごとに個別の環境を持つことが多い
自由度が高く、設定やデータが頻繁に変更される
本番データを模倣したダミーデータを使用する場合が多い



ステージング環境
目的
本番環境にデプロイする前に、リリースの動作を確認するための環境
テストチームや開発者が統合テストや受け入れテストを実施する場所

特徴
本番環境とほぼ同じ設定・構成がされている
本番環境に近いリアルなデータを使うことがあるが、個人情報やセンシティブなデータは避ける
負荷テストやエンドツーエンドテスト（E2Eテスト）を行う

重要性
本番環境での問題を事前に検出する
チーム間の調整やQA（品質保証）プロセスをスムーズに進める



本番環境
目的
実際にユーザーが使用する環境
完全に安定したバージョンのみがデプロイされる

特徴

本番データを使用し、ユーザーに公開される
高い可用性、セキュリティ、パフォーマンスが求められる
ダウンタイムを極力避けるため、メンテナンスも慎重に計画される

モニタリング
ログやメトリクスを常時監視し、問題が発生した場合に迅速に対応
A/Bテストやユーザーの行動データを分析するための基盤が整備されている


実装の流れ例
開発環境で新機能を作成し、基本的な動作確認を行う。
完成したコードをステージング環境にデプロイし、統合テストや負荷テストを実施。
テストを通過したコードを本番環境にデプロイして公開。
----- ここまでChatGTP -----
ソフトウェア開発では、関数や機能のテストが主な目的になると思います。

ですが、（データパイプライン等の）データに関する開発では、追加で考慮しなければならないことがあります。
JOINとかちゃんと関連つけれないとテストできないやん？本番データで開発させてくれぇ
実際データを見てみないと、ろくに開発できない！

とか往々にしてあります。
環境における「データ」についてまとめ直してみると・・・
本番環境は「本番データ」
重要なデータにはマスキングで保護

ステージング環境は「本番相当データ」
重要なデータはマスキングで保護

開発環境は、「ダミーデータ」
テーブル構成は本番と一緒
ただどこまでこだわって「本番に近い」ダミーを用意するかが難しい
データの形式は分かるが、統計的にもダミーで再現できる？
値の分布はどこ再現？
複数のテーブル間で本番データと同様にJOINできる？

といった考慮点もあり、どこまで「こだわって」ダミーデータを作るのかが、とても難しいです。

こだわればどこまでもこだわれてしまい、際限がありません・・・・

 Snowflakeでの3環境方式としては
アカウントを分ける方法
アカウントは一緒で、DATABASEで分ける方法
があるかと思います。（スキーマで分けるのはちょっと単位が小さいかな・・・）
今回に関しては

「アカウントは一緒で、DATABASEで分ける方法」

を採用しています。
というのも、CLONE機能を使うので、同じアカウントのほうがやりやすいからです。

DATABASEへのアクセス制御は、ロールを使って3環境へのアクセスを分離する形にはします。
本番：PROD_**_ROLE
ステージング：TEST_**_ROLE
開発：DEV_ROLE

みたいな感じ（もっと細かく制御しますが、とりあえず概要ってことで）

 本番データはダイナミックデータマスキングSnowflakeには、ダイナミックデータマスキングという機能があります。
https://docs.snowflake.com/ja/user-guide/security-column-ddm-intro
!ダイナミックデータマスキングは、マスキングポリシーを使用してクエリ時にテーブルとビューの列のプレーンテキストデータを選択的にマスクする、列レベルのセキュリティ機能
閲覧するロールによって、
マスキングする/しない
マスキングするならどのようにマスキングするか？
といったことも決められます。
実装する際には、tagを付与した後、マスキングポリシーを適用。ダイナミックデータマスキングを行っています。
以下に簡単な設定例を記載します。

※実際に動かしたSQLではなくて、ブログ用の説明用に作ってるSQLです、そこはご了承くださいｍ（＿）ｍ
ALTER TABLE CLEANSING.CLN_ACCOUNT ALTER COLUMN mail_address SET TAG identifier(:tag_for_analyst) = 'true';

ALTER TABLE CLEANSING.CLN_ACCOUNT ALTER COLUMN mail_address SET TAG identifier(:tag_for_operator) = 'true';

ALTER TABLE CLEANSING."cln_account" ALTER COLUMN name SET TAG identifier(:tag_for_ex_user) = 'true';
この例では、analyst用、operator用, ex_user（外部ユーザ）のタグを付与しています。
CREATE OR REPLACE MASKING POLICY common.string_mask
AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() in ('ANALYST_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_analyst') = 'true' THEN SHA2(val)
    WHEN CURRENT_ROLE() in ('OPERATOR_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_operator') = 'true' THEN SHA2(val)
    WHEN CURRENT_ROLE() in ('EX_USER_ROLE','DEPLOYER_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_ex_user') = 'true' THEN SHA2(val)
    ELSE val
  END;

CREATE OR REPLACE MASKING POLICY common.float_mask
AS (val float) RETURNS float ->
  CASE
    WHEN CURRENT_ROLE() in ('ANALYST_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_analyst') = 'true' THEN null
    WHEN CURRENT_ROLE() in ('OPERATOR_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_operator') = 'true' THEN null
    WHEN CURRENT_ROLE() in ('EX_USER_ROLE','DEPLOYER_ROLE') AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('common.pii_mask_level_for_ex_user') = 'true' THEN SHA2(val)
    ELSE val
こんな感じで、マスキングポリシーを付与します。

対象カラムのデータ型で、マスキングポリシーを策定しています。

タグ設計については、色々方式があるので、上記以外の方法もあると思います。タグに設定する「値　※上記ではyes」で切り替える方法もあるでしょう。
ALTER TAG common.pii_mask_level_for_analyst SET MASKING POLICY common.string_mask, MASKING POLICY common.float_mask, MASKING POLICY common.number_mask ;
ALTER TAG common.pii_mask_level_for_operator SET MASKING POLICY common.string_mask, MASKING POLICY common.float_mask, MASKING POLICY common.number_mask;
ALTER TAG common.pii_mask_level_for_ex_user SET MASKING POLICY common.string_mask, MASKING POLICY common.float_mask, MASKING POLICY common.number_mask;
ダイナミックデータマスキングを実現しています。

 ステージング環境のデータは本番相当データステージング環境の責務としては、「本番相当データ」を用います。

そして、本番のデプロイ前に品質チェックなどをすることが目的になります。
しかし、データについてはどうしたらいいでしょうか？
本番環境に近いリアルなデータを使うことがあるが、個人情報やセンシティブなデータは避ける
ステージング環境に本番環境のデータをそのまま使うのはまずい・・・
重要データはもちろんマスキングされてる状態でないとまずい
本番データをそのまま持ってくるとストレージコストが倍になる
このあたりを考えて、以下のような戦略でステージング環境のデータを用意します。
タグベースのマスキングポリシーで保護された列があるテーブルについては、その列を SHA2() で変換してコピーする。
CTASを使う、SHA2でハッシュ化してからコピー
保護されていないテーブルは、クローンによりコピーする
これでステージングには、保護された列にはマスキングデータが入ります。（not ダイナミック）
上記を実現するには、下記を考慮して、Stored Procedure を作ってます。

簡単に処理の流れだけを記載します。
どんなポリシーを設定したか？は、SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCESを用いて取り出します。
select distinct
    REF_COLUMN_NAME as COL
from
    SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
where
    ref_database_name = '<DB名>'
    and ref_schema_name = '<schema名>'
    and ref_entity_name = '<table名>' -- ここでの一致判定はダブルクオート不用
;
保護されている列を変換しつつ、既存列を削除してINSERTします
                        insert overwrite into <DB名>_TEST.{schema}.{table} ({dst_cols})
                            with cte AS (
                                select * exclude ({src_cols}), {masked_cols}
                                from <DB名>.{schema}.{table}
                            )
                            select
                                {dst_cols}
                            from
                                cte;
保護されていないテーブルは、CLONEします。

所有者をテスト用のロールに変えるなどをしておく必要があります。
create or replace table <DB名>_TEST.{schema}.{table} clone <DB名>.{schema}.{table};
grant ownership on table HANZO_TEST.{schema}.{table}                            to role TEST_ROLE
   copy current grants

 開発環境のデータはダミーデータ基本的に、テーブル名やカラム構成などは、本番データに合わせますが、中にあるデータはダミーデータを用意することになるかと思います。
レコード数もそこまで本番に合わせないでそこそこのデータを作ったりはします。

ダミーデータの作成には、Pythonで作ったり、生成AIを作る方法もあるでしょう
Snowflakeでは最近パブリックプレビューになった合成データを使って作る方法もあるでしょう。
https://zenn.dev/taro_cccmkhd/articles/2aa501bd841128
開発環境のデータは例えば下記のようなデータベースに開発環境用のデータを作ります。

<DB名>_DEV
こちらで開発するのですが、チームメンバーの数が多いと、ここがコンフリクトを起こしてしまって、開発に支障が出る場合があります。
その際には
<DB名>DEV<ユーザ名> <--私はこちらの方式が好き
<DB名>DEV<Branch名>
<DB名>_DEVからクローンして環境を作って開発をしていました。

 <DB名>DEV<ユーザ名>開発者ごとに開発環境用データを用意する形式
その人が好きに使ってもらってOK
一つの機能を一人で作るみたいなときには良い
一つの開発が終わったら、<DB名>_DEV からCLONEし直して、作り直す

 <DB名>DEV<Branch名>開発Featureごとに、開発環境用データを用意する形式
GitでBranchをきる際に、自動的にCLONEする
複数の人で一つの機能を開発する場合には良さそう
ブランチをCloseすると、この<DB名>DEV<Branch名>を自動的に削除する

 どっちの形式？個人的には、 <DB名>DEV<ユーザ名> で事足りる場合が多かったです。

<DB名>DEV<Branch名> 自動生成形式はちょっとToo Muchかな
一人で一つ機能の開発に収まることが多かったですし、準備の手間と開発運用の手間としては、バランスが良かったかなーと思いました。

ちゅらデータ株式会社Publication

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

開発環境、ステージング環境、本番環境について

Snowflakeでの3環境

本番データはダイナミックデータマスキング

ステージング環境のデータは本番相当データ

開発環境のデータはダミーデータ

<DB名>DEV<ユーザ名>

<DB名>DEV<Branch名>

どっちの形式？

Discussion