Skip to main content

❄️ 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 SHAREView 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​

ComponentPurpose
25 Sample RowsHealthcare domain demo
Intra-Org ShareSharing within same organization
Read-Only for ConsumersNo DML allowed
Account CreationOrg-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