Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create a Share in Snowflake: Syntax and Example

To create a share in Snowflake, use the CREATE SHARE command followed by the share name. Then add database objects like schemas or tables to the share using ALTER SHARE ADD commands, and finally grant access to consumers.
📐

Syntax

The CREATE SHARE command creates a new share object. You then use ALTER SHARE ADD to include database objects like schemas or tables. Finally, you grant usage privileges to consumers to access the share.

  • CREATE SHARE <share_name>: Defines the share.
  • ALTER SHARE <share_name> ADD DATABASE <database_name>.[.<schema_name>].<object_name>: Adds objects to the share.
  • GRANT USAGE ON SHARE <share_name> TO ACCOUNT <consumer_account>: Allows another Snowflake account to consume the share.
sql
CREATE SHARE my_share;

-- Add a table to the share
ALTER SHARE my_share ADD TABLE my_database.my_schema.my_table;

-- Grant access to another account
GRANT USAGE ON SHARE my_share TO ACCOUNT consumer_account;
💻

Example

This example creates a share named sales_share, adds a table from the sales_db database, and grants access to a consumer account named partner_account.

sql
CREATE SHARE sales_share;

ALTER SHARE sales_share ADD TABLE sales_db.public.customers;

GRANT USAGE ON SHARE sales_share TO ACCOUNT partner_account;
Output
Statement executed successfully. Statement executed successfully. Statement executed successfully.
⚠️

Common Pitfalls

Common mistakes when creating shares include:

  • Forgetting to add objects to the share after creating it.
  • Not granting usage privileges to the consumer account, so they cannot access the share.
  • Trying to share unsupported object types (only databases, schemas, tables, and secure views can be shared).
  • Using incorrect object names or missing schema qualifiers.
sql
/* Wrong: Created share but forgot to add objects and grant access */
CREATE SHARE my_share;

/* Right: Add table and grant usage */
ALTER SHARE my_share ADD TABLE my_db.public.my_table;
GRANT USAGE ON SHARE my_share TO ACCOUNT consumer_account;
📊

Quick Reference

CommandPurpose
CREATE SHARE Create a new share object
ALTER SHARE ADD TABLE ..
Add a table to the share
ALTER SHARE ADD DATABASE Add a whole database to the share
GRANT USAGE ON SHARE TO ACCOUNT Allow another account to access the share
SHOW SHARESList all shares in your account
✅

Key Takeaways

Use CREATE SHARE to start a new share in Snowflake.
Add database objects like tables to the share with ALTER SHARE ADD commands.
Grant USAGE on the share to consumer accounts to enable access.
Only supported objects like tables and secure views can be shared.
Always verify object names and privileges to avoid access issues.