Skip to main content

Consumer Data Sharing (External Sharing)


✅ What is External Sharing in Snowflake?​

External Sharing is the ability to share data securely between two different Snowflake accounts that belong to different organizations (or sometimes the same organization but different account types). It uses Secure Data Sharing to allow a data provider to share specific objects (tables, schemas, or databases) with a data consumer, without physically copying the data.

The consumer can query the shared data in real time, without replication. The provider retains full control over:

  • What data is shared.
  • Revoking or altering the share anytime.

Key Concepts​

  • Provider: The account that owns and shares the data.
  • Consumer: The account that uses the shared data.
  • Share Object: Logical container in the provider account that defines what data and which privileges are shared.

✅ Use Cases for External Sharing​

  • Data Monetization: Sell or distribute data to partners, vendors, or clients.
  • Cross-Org Collaboration: Share HR or inventory data with a partner company.
  • Data Marketplace: Publish data for multiple customers.
  • B2B Integrations: Seamless sharing with suppliers or downstream systems.

✅ Restrictions & Considerations​

  • Edition Restriction: By default, a higher edition account (Business Critical) cannot share data with a lower edition account (Enterprise or Standard).
    • ✅ This can be overridden using OVERRIDE SHARE RESTRICTIONS privilege and SHARE_RESTRICTIONS = FALSE.
    • Security & Compliance: The provider must verify that the consumer account meets necessary security requirements (e.g., PCI-DSS).
  • Read-Only for Consumer: Consumer cannot modify shared data; they can only query.
  • Supported Objects: Database → Schema → Tables, Secure Views.
  • No Data Copy: Data remains in the provider’s storage.

✅ How to Achieve External Sharing (Step-by-Step Demo)​

Below is a full SQL demo arranged logically with comments for clarity.


Step 1: Use AccountAdmin Role and Enable Override​

-- Use ACCOUNTADMIN role (required for sharing and overriding restrictions)
USE ROLE ACCOUNTADMIN;

-- Grant the override privilege to AccountAdmin role
GRANT OVERRIDE SHARE RESTRICTIONS ON ACCOUNT TO ROLE ACCOUNTADMIN;

Step 2: Create Database & Schema​

-- Create a database for sharing
CREATE OR REPLACE DATABASE emp_db_share;

-- Create a schema inside the database
CREATE OR REPLACE SCHEMA emp_db_share.emp_db_share_sch;

Step 3: Create a Table​

-- Create an employee table
CREATE OR REPLACE TABLE emp_db_share.emp_db_share_sch.employee_records (
employee_id STRING,
employee_name STRING,
role STRING,
department STRING,
last_updated DATE
);

Step 4: Insert Sample Data​

-- Insert some demo data
INSERT INTO emp_db_share.emp_db_share_sch.employee_records
(employee_id, employee_name, role, department, last_updated) VALUES
('E001', 'Ramesh Kumar', 'Manager', 'HR', '2025-06-01'),
('E002', 'Olivia Smith', 'Engineer', 'IT', '2025-06-02'),
('E003', 'Grace Watson', 'Analyst', 'Finance', '2025-06-25');

Step 5: Verify Data​

-- Verify inserted data
SELECT * FROM emp_db_share.emp_db_share_sch.employee_records;

Step 6: Create a Share​

-- Create a share object
CREATE OR REPLACE SHARE emp_data_share;

Step 7: Grant Privileges to Share​

-- Grant usage on database, schema, and select on table
GRANT USAGE ON DATABASE emp_db_share TO SHARE emp_data_share;
GRANT USAGE ON SCHEMA emp_db_share.emp_db_share_sch TO SHARE emp_data_share;
GRANT SELECT ON TABLE emp_db_share.emp_db_share_sch.employee_records TO SHARE emp_data_share;

Step 8: Add Consumer Account with Restriction Override​

-- Add the consumer account (replace GR42008 with actual consumer locator)
-- SHARE_RESTRICTIONS = FALSE removes the edition compatibility check
ALTER SHARE emp_data_share ADD ACCOUNT = GR42008 SHARE_RESTRICTIONS = FALSE;

Step 9: Verify Share​

-- Check share details
SHOW SHARES;
SHOW SHARES LIKE 'EMP_DATA_SHARE';

Step 10: In Consumer Account​

-- Consumer side: Create a database from the share
CREATE DATABASE emp_db_from_share FROM SHARE PROVIDER_ORG.PROVIDER_ACCOUNT.emp_data_share;

-- Query the shared data
SELECT * FROM emp_db_from_share.emp_db_share_sch.employee_records;

✅ Important Notes​

  • PROVIDER_ORG.PROVIDER_ACCOUNT → Replace with actual provider account locator.
  • No data duplication → Queries run on the provider’s data storage.
  • Real-time access → Consumer sees the latest provider data.

✅ Architecture Diagram (Logical Flow)​

Provider Account (Business Critical)
|
| Secure Share (emp_data_share)
|
Consumer Account (Enterprise)