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
T/F Replicating a primary database is blocked if one or more external tables exist in the database.
True
What are the sql steps to create a share
CREATE SHARE ….
GRANT USAGE ON DATABASE …. TO SHARE ….
GRANT USAGE ON SCHEMA…..
GRANT USAGE ON TABLE….
ALTER SHARE….ADD ACCOUNTS=
T/F Secure views can reference tables and views in multiple databases
True
What privilege must be granted on the databases the secure view is referencing
REFERENCE_USAGE
T/F You need to grant REFERENCE_USAGE on the database where the secure view is created.
False
Before adding a secured view to a share you must
grant the privilege separately on each database referenced in a view
Before adding a secured view to a share you must
grant REFERENCE_USAGE separately on each database referenced in a view
What is a replication group
A group of objects that are replicated as a unit to one or more target accounts
Read only access
point in time consistency
to refresh objects in a replication group, what is the command
ALTER REPLICATION GROUP … REFRESH
The user has to exist in the source account and have the correct privilege
What is the parameter for scheduling automatic refreshes
REPLICATION_SCHEDULE
T/F only one refresh is executed at any given time
True
What two constraints apply to database and share objects in replication groups?
An object can be in multiple replication groups as long as each group is replicated to a different target account.
Secondary (replica) objects cannot be added to a primary replication group.
T/F You must disable database replication before adding the database to a replication group.
True
T/F Creating secure views on streams in your database and then sharing those views with consumers is not recommended.
True
To create streams on shared tables or secure views, you must enable what?
Change tracking
How do you revoke access to a shared view
REVOKE SELECT ON VIEW …. FROM SHARE….
T/F By default, Automatic Clustering is suspended for the new table when it is cloned
True
How do you enable automatic clustering on a cloned table
ALTER TABLE <name> RESUME RECLUSTER</name>
When tables are cloned, how are internal stages handled
the internal stage associated with each table is cloned and empty
T/F pipes that reference an internal stage can be cloned
False
T/F pipes that reference an external stage can be cloned
True
If a table is cloned with an external pipe and the INTEGRATION parameter is set, what happens the next time data is loaded
If the table is fully qualified, the original table is loaded to twice. If the table is not fully qualified, then each table, clone and original, is loaded to.
When a database or schema is cloned, what happens to any unconsumed records in the stream
They are inaccessible
When does time travel start on a cloned table
When it was cloned
When a database or schema is cloned, what happens to the tasks by default
The tasks are suspended by default
T/F DDL statements are atomic and not part of multi-statement transactions.
True
If an object is being cloned, and ddl statements are running against it at the same time, what happens to the ddl statements
The clone might not be effected by the ddl statements
