Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Share Data Across Accounts in Snowflake Easily

In Snowflake, you share data across accounts by creating a share object in the provider account and granting access to specific database objects. Then, you grant the target account access to this share, allowing it to create a database from the shared data without copying it.
📐

Syntax

To share data across accounts, you use the CREATE SHARE command to define a share, then add database objects to it with ALTER SHARE ... ADD. Finally, you grant access to another account with GRANT USAGE ON SHARE.

  • CREATE SHARE <share_name>: Creates a share object.
  • GRANT USAGE ON SHARE <share_name> TO ACCOUNT <account_identifier>: Allows another account to use the share.
  • ALTER SHARE <share_name> ADD DATABASE <database_name>: Adds a database to the share.
  • In the consumer account, CREATE DATABASE <db_name> FROM SHARE <provider_account>.<share_name> creates a database from the shared data.
sql
CREATE SHARE my_share;
GRANT USAGE ON SHARE my_share TO ACCOUNT consumer_account;
ALTER SHARE my_share ADD DATABASE my_database;

-- On consumer account:
CREATE DATABASE shared_db FROM SHARE provider_account.my_share;
💻

Example

This example shows how the provider account shares a database named sales_db with a consumer account named abc12345. The consumer then creates a database from the share to query the data.

sql
-- Provider account
CREATE SHARE sales_share;
ALTER SHARE sales_share ADD DATABASE sales_db;
GRANT USAGE ON SHARE sales_share TO ACCOUNT abc12345;

-- Consumer account
CREATE DATABASE sales_shared FROM SHARE provider_account.sales_share;

-- Query shared data
SELECT * FROM sales_shared.public.orders LIMIT 5;
Output
5 rows returned from sales_shared.public.orders
⚠️

Common Pitfalls

  • Not granting USAGE on the share to the consumer account will block access.
  • Forgetting to add the database or specific schemas/tables to the share means nothing is shared.
  • Trying to modify shared data from the consumer account is not allowed; shares are read-only.
  • Using incorrect account identifiers or missing the provider account prefix in the consumer's CREATE DATABASE FROM SHARE causes errors.
sql
/* Wrong: Missing GRANT USAGE */
CREATE SHARE my_share;
ALTER SHARE my_share ADD DATABASE my_db;
-- No GRANT USAGE here

/* Right: Include GRANT USAGE */
GRANT USAGE ON SHARE my_share TO ACCOUNT consumer_account;
📊

Quick Reference

CommandPurpose
CREATE SHARE Create a new share object
ALTER SHARE ADD DATABASE Add a database to the share
GRANT USAGE ON SHARE TO ACCOUNT Allow another account to access the share
CREATE DATABASE FROM SHARE .Consumer creates a database from the share
✅

Key Takeaways

Create a share and add database objects in the provider account to share data.
Grant USAGE on the share to the consumer account to allow access.
Consumer accounts create databases from shares to query shared data without copying.
Shares are read-only for consumers; they cannot modify shared data.
Always use correct account identifiers and include the provider account prefix.