Shared Databases Flashcards
What are five general limitations of shared databases
- Are Read Only
- Cannot create a clone of the db, schema, or tables
- time travel is not supported
- Editing the comments of a shared database is not supported
- Shared dbs and db’s objects cannot be shared/forwarded
Using SQL, how can you view all the shares on your snowflake account
show shares;
what is the sql to show more information on a share
desc share ‘share-name’ (no quotes)
To run the tasks for shared databases, you need what privilege
ACCOUNTADMIN or IMPORT SHARES global privilege
T/F You can create a database from a share
True, the command is CREATE DATABASE database-name FROM SHARE provider-account.share-name
How many times can a share be consumed per account
once
What does a creating a Stream on a shared object do
Enables you to track data manipulation language changes made in those objects
How do you create a stream on a shared object
Using the role IMPORT PRIVILEGES
CREATE STREAM stream-name ON VIEW shared-db.schema.view-name
Before streams can be created on objects, what must the data provider do
must enable change tracking
How can you avoid a stream becoming stale
Contact the data provider to determine the data retention period for the object, and make sure the stream records within a transaction during the retention period for the table
What commands can tell you if a stream has gone stale
DESCRIBE STREAM or SHOW STREAMS
STALE will be TRUE
When can a role grant IMPORT PRIVILEGES to a user
- when it owns the db (has OWNERSHIP priv)
- was granted MANAGE GRANTS global priv
T/F Virtual Private Snowflake supports Secure Data Sharing
False, Snowflake does not support because of current limitations on sharing data across regions
What privileges do you need to create shares
Either the ACCOUNTADMIN or granted the CREATE SHARES privileges
If you drop a share, what happens to any databases created by the consumer
Immediately invalidates them
What does SHOW GRANTS TO SHARE do
lists all objects privs that have been granted to a share
What does SHOW GRANTS OF SHARE do
lists all accounts and indicates the accounts that are using the share
What kind of views can be shared
Secured views only!
T/F A new object created in a db in a share is automatically available to consumers
False, To make the object available you must use GRANT privileges-name TO SHARE
T/F New and modified rows in the table of a share are available immediately
True
How do you enable change tracking
ALTER TABLE … CHANGE_TRACKING = TRUE
Which privilege only applies to shared databases and is used to grant the ability to enable roles other than the owning role to access a shared db
IMPORT PRIVILEGES
Refreshing a secondary database is not allowed in what two circumstances
- Databases created from shares
- Primary database has external table
You have created a reader account for sharing data. who will pay for the compute usage of the account
The Provider
