CODEWITHSUNDEEP
accountreadersnowflake-cloud-data-platform
DaveTurpin
DaveTurpin

Reputation: 33

Snowflake: READER ACCOUNT, 1 DB, 2 Schemas

I am running into strange behavior when attempting to share 2 schemas from a single database to a SNOWFLAKE READER ACCOUNT.

Assuming both schemas on the producer database are properly shared the commands to create the database and shares on the READER ACCOUNT would look something like:

CREATE DATABASE myDB_A from share producerAccount.Producer_SHARE_Schema_A;
CREATE DATABASE myDB_B from share producerAccount.Producer_SHARE_Schema_B;

Where

What seems to happen is myDB_A will have Schema_A and when the second command is run, myDB_A also picks up Schema_B.

Ideally, we would need a command that allows us to add a second schema to the database, using an ALTER approach:

CREATE DATABASE myDB_A from share producerAccount.Producer_SHARE_Schema_A;
**ALTER DATABASE myDB_B ADD share producerAccount.Producer_SHARE_Schema_B**;

The final question... Is there a standardized method to add additional schemas to a shared database in a target READER ACCOUNT?

Thanks!

Upvotes: 0

Views: 95

Answers (1)

DaveTurpin
DaveTurpin

Reputation: 33

I think I am able to answer my question... Instead of trying to add the second schema to a share that is created on the consumer (READER ACCOUNT) side, it's best to create the share on the PRODUCER side to include both schemas:

grant usage on database myDB to share myShare;
grant usage on schema myDB.Schema_A to share myShare;
grant usage on schema myDB.Schema_B to share myShare;

Then on the consumer side we only need to worry about a single share to create the database:

CREATE DATABASE myDB from share producerAccount.myshare;

That solved the problem. In hindsight, it is obvious. Put all of the pieces together on the producer side, and keep things real simple on the READER ACCOUNT.

Duh...

Upvotes: 1

Related Questions