Snowflake の集計ポリシーで個別レコードの閲覧を防ぎつつ集計結果だけ返す仕組みを試してみた

かわばたです。

データ共有やアナリストへのアクセス権付与の際に、個別レコードは見せたくないが集計結果は取得させたい、そんな要件に対して、Snowflake の**集計ポリシー（Aggregation Policy）**が有効です。テーブルに集計ポリシーを設定すると、クエリは GROUP BY またはスカラー集計関数によって一定サイズ以上のグループに集計する必要があり、個別レコードをそのまま返す SELECT が制限されます。この課題を集計ポリシーで解決できないか試してみました。

https://docs.snowflake.com/ja/user-guide/aggregation-policies

https://docs.snowflake.com/ja/user-guide/aggregation-policies-entity-privacy

基本的な集計ポリシーに加え、同一エンティティ（ユーザーや世帯など）の複数行から情報が推測されることを防ぐエンティティレベルのプライバシーも試してみたので、手順と確認結果をまとめます。

背景・課題

Snowflake でアナリストにテーブルへの SELECT 権限を付与すると、SELECT * FROM table で個別レコードを閲覧できてしまいます。たとえば、従業員の給与データを部署別に集計して分析したいが、個人の給与額が見えてしまうのは NGのようなケースです。

既存の手段で対処しようとすると、以下のような限界があります。

• マスキングポリシー: カラム値をマスクできるが、行の存在自体は見える。SELECT * で行数や他のカラムは確認できてしまう
• 集計済みビューの提供: あらかじめ集計したビューだけ公開する方法もあるが、アナリストが任意の切り口で GROUP BY できなくなる。分析の柔軟性が大幅に低下する
• 行アクセスポリシー（Row Access Policy）: 行レベルでアクセスを制御できるが、「集計結果だけ見せたい」という要件には粒度が合わない

求めるゴールは、アナリストに「任意の切り口で集計クエリは投げられるが、個別レコードは取得できない」という制御を実現することです。

技術的アプローチ

集計ポリシーの概要

集計ポリシー（Aggregation Policy）はスキーマレベルのオブジェクトです。テーブルに適用すると、そのテーブルに対するクエリは GROUP BY 句またはスカラー集計関数（COUNT(*) など）によって一定サイズ以上に集計する必要があり、かつグループのサイズが指定した最小値（MIN_GROUP_SIZE）以上であることが要求されます。

CREATE AGGREGATION POLICY <name>
  AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => <n>);

• MIN_GROUP_SIZE 未満の行を含むグループは**剰余グループ（Remainder Group）**として NULL キーにまとめられ、個別の値が露出しません
• ロールに応じて制約を切り替えることも可能です（CURRENT_ROLE() を条件に使う）
• 許可される集計関数は AVG、COUNT、COUNT(DISTINCT)、HLL、SUM のみです

エンティティレベルのプライバシー

基本の集計ポリシーでは MIN_GROUP_SIZE は「行数」で判定されます。しかし、1 人の顧客が複数行の購入履歴を持つような場合、行数は多くても実質 1 人分のデータということがあります。

エンティティレベルのプライバシーでは、ENTITY KEY を指定することで MIN_GROUP_SIZE が「一意のエンティティ数」で判定されるようになります。

ALTER TABLE <table_name>
  SET AGGREGATION POLICY <policy_name>
  ENTITY KEY (<column_name>);

さらに、サブクエリの GROUP BY がエンティティキーと一致する場合、ポリシーチェックが外側のクエリに繰り延べされる**遅延集計（Deferred Aggregation）**という仕組みもあります。

制限事項

• 外部テーブルには適用不可
• GROUP BY ROLLUP / CUBE / GROUPING SETS は使用不可
• ウィンドウ関数は使用不可
• セット演算子は UNION ALL のみ許可（UNION、INTERSECT、EXCEPT は不可）
• 再帰 CTE は使用不可
• 相関サブクエリ・ラテラル結合に制限あり
• テーブルまたはビューに関連付けられる集計ポリシーは 1 つのみ（エンティティレベルのプライバシーで複数エンティティキーを扱う場合の制約は公式ドキュメントを参照）
• 同一の集計ポリシーを行レベルのプライバシーとエンティティレベルのプライバシーの両方に使用することは不可。行レベルのプライバシーに使用できるポリシーは 1 つまで
• 信頼できるパートナー向けの機能であり、悪意あるユーザーが複数クエリを試行して推測する攻撃は想定外

前提条件

• Snowflake: 本検証はトライアルアカウントのEnterprise版で実施
• 必要な権限: ACCOUNTADMIN ロール（検証用）。本番運用では、ポリシー作成に対象スキーマの CREATE AGGREGATION POLICY 権限、ポリシーの適用にアカウントレベルの APPLY AGGREGATION POLICY 権限（または対象ポリシーへの APPLY 権限 + 対象テーブルの OWNERSHIP 権限）が必要

事前準備

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS agg_policy_test;
USE DATABASE agg_policy_test;
CREATE SCHEMA IF NOT EXISTS hr;
USE SCHEMA hr;

CREATE OR REPLACE TABLE employees (
    employee_id INT COMMENT '従業員ID',
    employee_name VARCHAR COMMENT '氏名',
    department VARCHAR COMMENT '部署',
    position VARCHAR COMMENT '役職',
    salary NUMBER(10, 2) COMMENT '月給',
    hire_date DATE COMMENT '入社日'
);

-- 検証用データの投入（25レコード）
-- Engineering: 8人、Sales: 7人、Marketing: 5人、HR: 3人、Finance: 2人
-- HR(3人)とFinance(2人)は MIN_GROUP_SIZE=5 で剰余グループに入ることを確認するため意図的に少人数
INSERT INTO employees VALUES
    (1, '田中太郎', 'Engineering', 'Senior Engineer', 850000, '2020-04-01'),
    (2, '佐藤花子', 'Engineering', 'Engineer', 650000, '2021-06-15'),
    (3, '鈴木一郎', 'Engineering', 'Lead Engineer', 950000, '2019-01-10'),
    (4, '高橋美咲', 'Engineering', 'Engineer', 600000, '2022-04-01'),
    (5, '伊藤健太', 'Engineering', 'Junior Engineer', 450000, '2023-10-01'),
    (6, '渡辺直美', 'Engineering', 'Engineer', 700000, '2020-09-01'),
    (7, '山本学', 'Engineering', 'Senior Engineer', 880000, '2018-07-01'),
    (8, '中村陽子', 'Engineering', 'Engineer', 620000, '2022-01-15'),
    (9, '小林誠', 'Sales', 'Sales Manager', 780000, '2019-04-01'),
    (10, '加藤裕二', 'Sales', 'Sales Rep', 520000, '2021-07-01'),
    (11, '吉田真理', 'Sales', 'Sales Rep', 480000, '2022-10-01'),
    (12, '松本大輔', 'Sales', 'Senior Sales', 650000, '2020-01-15'),
    (13, '井上さくら', 'Sales', 'Sales Rep', 500000, '2023-04-01'),
    (14, '木村拓也', 'Sales', 'Sales Rep', 490000, '2023-07-01'),
    (15, '林美穂', 'Sales', 'Sales Rep', 470000, '2024-01-15'),
    (16, '清水翔太', 'Marketing', 'Marketing Manager', 720000, '2020-04-01'),
    (17, '斎藤恵', 'Marketing', 'Content Creator', 480000, '2022-06-01'),
    (18, '前田健一', 'Marketing', 'Designer', 550000, '2021-10-01'),
    (19, '藤井優子', 'Marketing', 'Analyst', 530000, '2023-01-15'),
    (20, '岡田龍一', 'Marketing', 'Content Creator', 460000, '2023-09-01'),
    (21, '石川由美', 'HR', 'HR Manager', 680000, '2019-04-01'),
    (22, '三浦健二', 'HR', 'Recruiter', 450000, '2022-04-01'),
    (23, '西田あかり', 'HR', 'HR Specialist', 500000, '2021-07-01'),
    (24, '上田浩司', 'Finance', 'CFO', 1100000, '2018-01-10'),
    (25, '河野真由', 'Finance', 'Accountant', 520000, '2021-04-01');

テーブルの内容を SELECT して、25件のデータが正しく入っていることを確認しておきます。

SELECT * FROM employees ORDER BY employee_id;

購入履歴テーブルの作成（エンティティレベルのプライバシー検証用）

1 人の顧客が複数回購入するデータ構造で、行数とエンティティ数の違いを確認します。

CREATE OR REPLACE TABLE purchase_history (
    purchase_id INT COMMENT '購入ID',
    customer_id VARCHAR COMMENT '顧客ID',
    customer_name VARCHAR COMMENT '顧客名',
    product_category VARCHAR COMMENT '商品カテゴリ',
    amount NUMBER(10, 2) COMMENT '購入金額',
    purchase_date DATE COMMENT '購入日'
);

-- 7顧客の購入履歴（25レコード）
-- C001: 6件、C002: 5件、C003: 4件、C004: 4件、C005: 3件、C006: 2件、C007: 1件
-- エンティティ数（顧客数）と行数が異なることを示すため、顧客ごとの件数に偏りを持たせている
INSERT INTO purchase_history VALUES
    (1, 'C001', '田中太郎', 'Electronics', 45000, '2026-06-01'),
    (2, 'C001', '田中太郎', 'Books', 3200, '2026-06-03'),
    (3, 'C001', '田中太郎', 'Electronics', 28000, '2026-06-10'),
    (4, 'C001', '田中太郎', 'Food', 5600, '2026-06-15'),
    (5, 'C001', '田中太郎', 'Clothing', 12000, '2026-06-20'),
    (6, 'C001', '田中太郎', 'Electronics', 8500, '2026-06-25'),
    (7, 'C002', '佐藤花子', 'Clothing', 15000, '2026-06-02'),
    (8, 'C002', '佐藤花子', 'Food', 3800, '2026-06-08'),
    (9, 'C002', '佐藤花子', 'Books', 2400, '2026-06-12'),
    (10, 'C002', '佐藤花子', 'Clothing', 9200, '2026-06-18'),
    (11, 'C002', '佐藤花子', 'Electronics', 32000, '2026-06-22'),
    (12, 'C003', '鈴木一郎', 'Food', 7200, '2026-06-04'),
    (13, 'C003', '鈴木一郎', 'Books', 4500, '2026-06-11'),
    (14, 'C003', '鈴木一郎', 'Food', 2800, '2026-06-19'),
    (15, 'C003', '鈴木一郎', 'Electronics', 18000, '2026-06-26'),
    (16, 'C004', '高橋美咲', 'Clothing', 22000, '2026-06-05'),
    (17, 'C004', '高橋美咲', 'Electronics', 55000, '2026-06-13'),
    (18, 'C004', '高橋美咲', 'Books', 1800, '2026-06-21'),
    (19, 'C004', '高橋美咲', 'Food', 4200, '2026-06-27'),
    (20, 'C005', '伊藤健太', 'Electronics', 12000, '2026-06-06'),
    (21, 'C005', '伊藤健太', 'Food', 6500, '2026-06-14'),
    (22, 'C005', '伊藤健太', 'Clothing', 8800, '2026-06-23'),
    (23, 'C006', '渡辺直美', 'Books', 5200, '2026-06-07'),
    (24, 'C006', '渡辺直美', 'Electronics', 15000, '2026-06-16'),
    (25, 'C007', '山本学', 'Food', 3400, '2026-06-09');

テーブルの内容を SELECT して、25件のデータが正しく入っていることを確認しておきます。

SELECT * FROM purchase_history ORDER BY purchase_id;

試してみた

基本的な集計ポリシーを作成してテーブルに適用する

集計ポリシーを作成します。MIN_GROUP_SIZE => 5 とすることで、5行以上のグループでないと集計結果を返さない制約を設けます。

CREATE OR REPLACE AGGREGATION POLICY agg_policy_test.hr.min_group_5
  AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

作成した集計ポリシーを employees テーブルに適用します。

ALTER TABLE agg_policy_test.hr.employees
  SET AGGREGATION POLICY agg_policy_test.hr.min_group_5;

適用後、個別レコードの SELECT を試みます。

SELECT * FROM employees;

集計ポリシーが適用されたテーブルに対して SELECT *（集計なし）を実行すると、エラーが返ります。SQL compilation error: Aggregation policy violation: aggregation required. のようなエラーメッセージが表示されれば問題ありません。

集計クエリを実行して結果を確認する

部署別の平均給与・合計給与を集計します。

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

以下の結果になれば問題ありません。

• Engineering（8人）、Sales（7人）、Marketing（5人）: MIN_GROUP_SIZE=5 以上なので、部署名と集計値がそのまま表示される
• HR（3人）と Finance（2人）: MIN_GROUP_SIZE=5 未満なので、剰余グループ（department が NULL）にまとめられる。剰余グループの employee_count は 5（3+2）、avg_salary と total_salary は HR + Finance の合算値になる

剰余グループの仕組みにより、HR が 3 人・Finance が 2 人という内訳は分からなくなります。少人数の部署の個別情報が保護されているのがポイントです。

次に、WHERE 句で少人数の部署に絞った場合の挙動を確認します。

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE department = 'HR'
GROUP BY department;

HR 部署のみに絞ると 3 人しかいないため、MIN_GROUP_SIZE=5 を下回ります。この場合、department は NULL となり、少人数グループの詳細が表示されないことを確認できます。なお、剰余グループを構成するのに十分な行数がない場合、公式ドキュメント上は結果の各フィールドが NULL になると説明されています。

ロール別の制御

ポリシーの CASE 式で CURRENT_ROLE() を条件に使うことで、管理者ロールには制限をかけず、それ以外のロールにだけ集約を強制する設定も可能です。

-- 既存のポリシーを解除
ALTER TABLE agg_policy_test.hr.employees
  UNSET AGGREGATION POLICY;

-- ロール別の集計ポリシーを作成
CREATE OR REPLACE AGGREGATION POLICY agg_policy_test.hr.role_based_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
  CASE
    WHEN CURRENT_ROLE() = 'ACCOUNTADMIN'
    THEN NO_AGGREGATION_CONSTRAINT()
    ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
  END;

-- テーブルに適用
ALTER TABLE agg_policy_test.hr.employees
  SET AGGREGATION POLICY agg_policy_test.hr.role_based_agg_policy;

ACCOUNTADMIN ロールでは SELECT * を含むすべてのクエリが実行でき、それ以外のロールでは集計が強制されます。なお、CURRENT_ROLE() の戻り値は文字列として比較されるため、大文字小文字が区別されます。引用符なしで作成したロール名は通常大文字で格納されます。

ACCOUNTADMIN ロールで SELECT * FROM employees が正常に返れば問題ありません。

エンティティレベルのプライバシーを試す

次に、購入履歴テーブルでエンティティレベルのプライバシーを検証します。MIN_GROUP_SIZE を「行数」ではなく「一意の顧客数」で判定させます。

集計ポリシーを作成し、ENTITY KEY を指定してテーブルに適用します。

CREATE OR REPLACE AGGREGATION POLICY agg_policy_test.hr.entity_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 3);

ALTER TABLE agg_policy_test.hr.purchase_history
  SET AGGREGATION POLICY agg_policy_test.hr.entity_agg_policy
  ENTITY KEY (customer_id);

まず、個別レコードの SELECT がエラーになることを確認します。

SELECT * FROM purchase_history;

エラーが返れば問題ありません。

次に、商品カテゴリ別の集計を実行します。

SELECT
    product_category,
    COUNT(*) AS purchase_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM purchase_history
GROUP BY product_category;

ここがエンティティレベルのポイントです。MIN_GROUP_SIZE => 3 は「3 行以上」ではなく「3 人以上の一意の顧客」で判定されます。各カテゴリの顧客数は以下のとおりです。

すべてのカテゴリで一意の顧客数が 3 以上なので、全カテゴリの結果が表示されるはずです。

続いて、顧客別の集計を試します。

SELECT
    customer_id,
    COUNT(*) AS purchase_count,
    SUM(amount) AS total_amount
FROM purchase_history
GROUP BY customer_id;

各グループのエンティティ数は 1（各グループに自分自身しかいない）なので、MIN_GROUP_SIZE=3 を下回り、すべてのグループが剰余グループにまとめられます。customer_id が NULL の 1 行のみ返ればOKです。これにより、個人別の購入金額が特定されることを防げます。

遅延集計（Deferred Aggregation）の動作を確認する

エンティティレベルのプライバシーには遅延集計という仕組みがあります。サブクエリの GROUP BY がエンティティキーと一致する場合、ポリシーチェックがサブクエリではなく外側のクエリに繰り延べされます。

以下のクエリでは、サブクエリで customer_id（= エンティティキー）ごとに購入合計を算出し、外側のクエリでカテゴリ別に集計しています。

SELECT
    product_category,
    AVG(customer_total) AS avg_customer_total
FROM (
    SELECT
        customer_id,
        product_category,
        SUM(amount) AS customer_total
    FROM purchase_history
    GROUP BY customer_id, product_category
)
GROUP BY product_category;

サブクエリの GROUP BY は customer_id, product_category であり、エンティティキーである customer_id を含むセットになっています。このため、エンティティレベルのポリシーチェックはサブクエリではなく外側のクエリに繰り延べされます。外側のクエリの GROUP BY product_category に対して MIN_GROUP_SIZE=3 が適用され、各カテゴリの一意顧客数が 3 以上であれば結果が返ります。カテゴリ別の avg_customer_total（顧客あたりの平均購入合計）が表示されれば問題ありません。

最後に

集計ポリシーを使って、個別レコードの閲覧を防ぎつつ集計クエリだけ許可する制御を確認できました。MIN_GROUP_SIZE の閾値を下回るグループは剰余グループ（NULL キー）にまとめられるため、少人数グループの詳細が露出しません。

エンティティレベルのプライバシーにより、同一エンティティの複数行からの情報推測も防止できることを確認しました。行数ではなく一意のエンティティ数で判定される点が、1 対多のデータ構造を持つテーブルでは特に有効だと感じました。

注意点として、GROUP BY ROLLUP / CUBE やウィンドウ関数が使えない制限があるため、分析要件と照らし合わせて事前に確認が必要です。また、 集計ポリシーは、悪意あるユーザーが多数のクエリを試行して個別値を推測する攻撃を完全に防ぐことを保証するものではありません。信頼関係のあるパートナーや顧客向けに利用することが推奨されます。

マスキングポリシーや行アクセスポリシーと組み合わせることで、より厳密なデータガバナンスを実現できます。ただし、集計制約付きテーブルに他のポリシーを適用した場合、クエリはすべてのポリシー要件を満たす必要があるため、組み合わせ時の制約は事前に確認しておく必要があります。

この記事が何かの参考になれば幸いです！