Creating and managing shares in Snowflake - Step-by-Step CLI Walkthrough

Introduction

Sharing data securely between Snowflake accounts can be tricky. Snowflake shares let you share data without copying it, saving time and storage.

When you want to share a table with a partner company without moving data.

When you need to give read-only access to a dataset to another team.

When you want to share data across different Snowflake accounts in your organization.

When you want to update shared data instantly without resending files.

When you want to control exactly which data objects are shared.

Commands

This command creates a new share named 'my_share' to start sharing data objects.

Terminal

CREATE SHARE my_share;

Expected Output

Statement executed successfully.

Allows the share to access the database 'my_database' so its objects can be shared.

Terminal

GRANT USAGE ON DATABASE my_database TO SHARE my_share;

Expected Output

Statement executed successfully.

Gives read-only access to the table 'my_table' inside the share.

Terminal

GRANT SELECT ON TABLE my_database.public.my_table TO SHARE my_share;

Expected Output

Statement executed successfully.

Adds the Snowflake account with ID '123456789' as a consumer of the share.

Terminal

ALTER SHARE my_share ADD ACCOUNTS = ('123456789');

Expected Output

Statement executed successfully.

Lists all shares in your account to verify 'my_share' was created and configured.

Terminal

SHOW SHARES;

Expected Output

name     | created_on          | owner

my_share | 2024-06-01 12:00:00 | ACCOUNTADMIN

Key Concept

If you remember nothing else from this pattern, remember: a share lets you give controlled, read-only access to your data without copying it.

Common Mistakes

Trying to share a table without granting usage on its database first.

The share cannot access the table if it does not have permission on the database.

Always grant USAGE on the database to the share before granting access to tables.

Not adding consumer accounts to the share after creating it.

No one can access the shared data if no accounts are added as consumers.

Use ALTER SHARE to add the consumer account IDs explicitly.

Granting write permissions on shared objects.

Shares only support read-only access; write permissions are not allowed.

Only grant SELECT or USAGE privileges on shared objects.

Summary

Create a share with CREATE SHARE to start sharing data.

Grant USAGE on the database and SELECT on tables to the share.

Add consumer accounts with ALTER SHARE to let others access the data.

Use SHOW SHARES to verify your shares and their settings.