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
USAGEon 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 SHAREcauses 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
| Command | Purpose |
|---|---|
| CREATE SHARE | Create a new share object |
| ALTER SHARE | Add a database to the share |
| GRANT USAGE ON SHARE | Allow another account to access the share |
| CREATE DATABASE | 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.
