❄️ Snowflake Data Sharing Demo: Intra-Organization (Internal Sharing)
This demo shows how to:
- ✅ Create sample healthcare data
- ✅ Share it securely across accounts within the same organization
- ✅ Demonstrate read-only isolation on the consumer side
🔹 Use Case
When the consumer already has a Snowflake account (e.g., a business partner, subsidiary, or internal department in another cloud/region).
🏗 Step 0: Org Admin – Create Consumer Account
Run as ORGADMIN in Snowflake to create a new child account under the same Org:
CREATE ACCOUNT sf_data_consumer_1
ADMIN_NAME = 'consumer_admin'
ADMIN_PASSWORD = 'GoodPassword@123'
EMAIL = 'consumer@example.com'
FIRST_NAME = 'Consumer'
LAST_NAME = 'User'
EDITION = ENTERPRISE
GRACE_PERIOD_IN_DAYS = 7;
✔ GRACE_PERIOD_IN_DAYS is mandatory.
🏥 Step 1: Producer (Base Account) Setup
✅ 1.1 Create Database, Schema & Table
-- Create database and schema
CREATE DATABASE healthcare_demo_db;
CREATE SCHEMA healthcare_demo_db.healthcare_demo_schema;
-- Create table
CREATE OR REPLACE TABLE healthcare_demo_db.healthcare_demo_schema.patient_records (
patient_id STRING,
patient_name STRING,
record_type STRING,
description STRING,
last_updated_date DATE
);
✅ 1.2 Insert Sample Data
INSERT INTO healthcare_demo_db.healthcare_demo_schema.patient_records
(patient_id, patient_name, record_type, description, last_updated_date) VALUES
('P001', 'Ramesh Kumar', 'Diagnosis', 'Diagnosed with hypertension', '2025-06-01'),
('P002', 'Olivia Smith', 'Treatment', 'Prescribed beta blockers', '2025-06-02'),
('P003', 'Meena Verma', 'Insurance', 'Policy renewed with ₹5L coverage', '2025-06-03'),
('P004', 'Wang Lei', 'OPD Visit', 'General check-up, all vitals normal', '2025-06-04'),
('P005', 'Arun Sharma', 'Critical Illness', 'Heart bypass scheduled', '2025-06-05'),
-- … continue till P025
('P025', 'Grace Watson', 'Diagnosis', 'Diagnosed with high cholesterol', '2025-06-25');
✔ Verify data:
SELECT * FROM healthcare_demo_db.healthcare_demo_schema.patient_records;
✅ 1.3 Create & Configure Share
-- Create share
CREATE OR REPLACE SHARE healthcare_demo_share;
-- Grant database & schema usage
GRANT USAGE ON DATABASE healthcare_demo_db TO SHARE healthcare_demo_share;
GRANT USAGE ON SCHEMA healthcare_demo_db.healthcare_demo_schema TO SHARE healthcare_demo_share;
-- Grant SELECT privilege on table
GRANT SELECT ON TABLE healthcare_demo_db.healthcare_demo_schema.patient_records TO SHARE healthcare_demo_share;
-- Add consumer account (replace with actual locator)
ALTER SHARE healthcare_demo_share ADD ACCOUNTS = MV17235;
✔ Check share details:
SHOW SHARES LIKE 'HEALTHCARE_DEMO_SHARE';
SELECT CURRENT_ACCOUNT(), CURRENT_REGION(), CURRENT_ORGANIZATION_NAME();
🛠 Step 2: Consumer Side (sf_data_consumer_1)
✅ 2.1 Create Database from Share
CREATE DATABASE shared_demo FROM SHARE ZZ88953.a2a_sf_healthcare_demo_share;
✅ 2.2 Query Shared Data (Read-Only)
SELECT * FROM shared_demo.a2a_sf_base_sch.a2a_sf_healthcare_data;
❌ 2.3 Try DML (Fails – Read-Only)
INSERT INTO shared_demo.a2a_sf_base_sch.a2a_sf_healthcare_data VALUES (...); -- Will fail
🧠 Additional Notes
| ✅ Command | 📌 Purpose |
|---|---|
SHOW SHARES and SHOW GRANTS TO SHARE | View metadata related to shares |
SELECT CURRENT_ACCOUNT(), CURRENT_ORGANIZATION_NAME(); | Identify Producer/Consumer accounts |
SHOW ORGANIZATION ACCOUNTS; (as ORGADMIN) | Find account locators in the org |
✅ Summary Table
| Component | Purpose |
|---|---|
| 25 Sample Rows | Healthcare domain demo |
| Intra-Org Share | Sharing within same organization |
| Read-Only for Consumers | No DML allowed |
| Account Creation | Org-based multi-account demo |
🎯 Key Takeaways
- ✅ Internal sharing in Snowflake is secure & managed at Org level
- ✅ Consumers cannot modify shared data
- ✅ Perfect for multi-department analytics or cross-region access