2
0
Aggregation Policy: Let’s consider a scenario where Sachin Mittal, “Cloudyard,” has a central data repository stored in Snowflake. This repository contains detailed transaction records of customer orders across various stores. Sachin needs to share summarized transaction data with Vishal Kaushal (partner) regularly. However, Vishal is known for his inquisitive nature, and Sachin wants to enable him to perform analyses on the data while ensuring the privacy of individual customer transactions.
Hence, Sachin must strike a balance between:
- Transparency and Collaboration: Providing Vishal with sufficient summarized data to conduct valuable analyses and support the partnership.
- Data Privacy: Protecting the confidentiality of individual customer details and adhering to any relevant data privacy regulations.
To address this concern, Cloudyard implements an aggregation policy on the shared transaction dataset. The aggregation policy requires consumers of the data to aggregate transactions into groups of a specified minimum size before retrieving any information.
Here’s how the scenario unfolds:
- Data Sharing Setup: Cloudyard sets up a data-sharing arrangement with its business partners using Snowflake’s data-sharing features.
- Aggregation Policy Implementation:
- The Cloudyard team creates an aggregation policy for the shared table.
- Specifies a minimum group size for aggregation, such as 10 transactions per group. This means that consumers of the shared data must aggregate at least 10 transactions together before retrieving any information.
- Data Consumption by Vishal:
- Access the shared view containing summarized transaction data. When querying the data, partners must ensure that their queries aggregate transaction records into groups of at least 10 transactions each.
- By enforcing the aggregation policy, Cloudyard ensures that individual transaction details are not exposed to business partners.
- Business partners can still perform meaningful analyses on the summarized data, such as calculating Average Account balance for customer or identifying trends in orders.
Technical Implementation:
Technical Implementation:
Here’s a step-by-step walkthrough of the technical implementation using Snowflake:
-
Tables: We’ll assume two tables:
CustomerandOrders. -
Create Aggregation Policy:
CREATE OR REPLACE AGGREGATION POLICY AGG_POL_GRP AS () RETURNS AGGREGATION_CONSTRAINT -> AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 10);
This code creates an Aggregation Policy named AGG_POL_GRP with a minimum group size of 10. This means any query on the tables must aggregate data into groups of at least 10 transactions before retrieving results.
- Assign Policy to Tables:
ALTER TABLE CUSTOMER SET AGGREGATION POLICY AGG_POL_GRP;
ALTER TABLE ORDERS SET AGGREGATION POLICY AGG_POL_GRP;
- Create Share Object (Assuming a Share exists):
We’ll assume a Share object has been created in Snowflake that encapsulates the Customer and Orders tables.
Allowed Queries for Vishal (Consumer):
Vishal can access the shared tables containing summarized transaction data. However, his queries must ensure they aggregate data following the minimum 10 group size defined in the policy. Here are some examples:
Customer Table:
SELECT c_nationkey, c_mktsegment, COUNT(*) AS num_customers, AVG(c_acctbal) AS avg_acctbal FROM CUSTOMER GROUP BY c_nationkey, c_mktsegment;
Orders Table:
Query 1:
SELECT o_orderstatus, DATE_TRUNC('day', o_orderdate), COUNT(*) AS orders FROM orders GROUP BY o_orderstatus, DATE_TRUNC('day', o_orderdate);
Query 2:
SELECT o_orderstatus, o_orderpriority, SUM(o_totalprice), COUNT(*) FROM orders GROUP BY o_orderstatus, o_orderpriority;
Restricted Queries for Vishal:
Due to the aggregation policy, Vishal cannot access individual transaction details. Here are some examples of queries that would be blocked:
By enforcing minimum group sizes, businesses can empower partners with valuable insights while safeguarding sensitive customer information.
