Shared Databases Flashcards

1
Q

What are five general limitations of shared databases

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Using SQL, how can you view all the shares on your snowflake account

A

show shares;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

what is the sql to show more information on a share

A

desc share ‘share-name’ (no quotes)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

To run the tasks for shared databases, you need what privilege

A

ACCOUNTADMIN or IMPORT SHARES global privilege

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

T/F You can create a database from a share

A

True, the command is CREATE DATABASE database-name FROM SHARE provider-account.share-name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How many times can a share be consumed per account

A

once

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does a creating a Stream on a shared object do

A

Enables you to track data manipulation language changes made in those objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How do you create a stream on a shared object

A

Using the role IMPORT PRIVILEGES
CREATE STREAM stream-name ON VIEW shared-db.schema.view-name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Before streams can be created on objects, what must the data provider do

A

must enable change tracking

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How can you avoid a stream becoming stale

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What commands can tell you if a stream has gone stale

A

DESCRIBE STREAM or SHOW STREAMS
STALE will be TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

When can a role grant IMPORT PRIVILEGES to a user

A
  • when it owns the db (has OWNERSHIP priv)
  • was granted MANAGE GRANTS global priv
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

T/F Virtual Private Snowflake supports Secure Data Sharing

A

False, Snowflake does not support because of current limitations on sharing data across regions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What privileges do you need to create shares

A

Either the ACCOUNTADMIN or granted the CREATE SHARES privileges

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

If you drop a share, what happens to any databases created by the consumer

A

Immediately invalidates them

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What does SHOW GRANTS TO SHARE do

A

lists all objects privs that have been granted to a share

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What does SHOW GRANTS OF SHARE do

A

lists all accounts and indicates the accounts that are using the share

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What kind of views can be shared

A

Secured views only!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

T/F A new object created in a db in a share is automatically available to consumers

A

False, To make the object available you must use GRANT privileges-name TO SHARE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

T/F New and modified rows in the table of a share are available immediately

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How do you enable change tracking

A

ALTER TABLE … CHANGE_TRACKING = TRUE

22
Q

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

A

IMPORT PRIVILEGES

23
Q

Refreshing a secondary database is not allowed in what two circumstances

A
  • Databases created from shares
  • Primary database has external table
24
Q

You have created a reader account for sharing data. who will pay for the compute usage of the account

A

The Provider

25
Q

T/F Replicating a primary database is blocked if one or more external tables exist in the database.

A

True

26
Q

What are the sql steps to create a share

A

CREATE SHARE ….
GRANT USAGE ON DATABASE …. TO SHARE ….
GRANT USAGE ON SCHEMA…..
GRANT USAGE ON TABLE….
ALTER SHARE….ADD ACCOUNTS=

27
Q

T/F Secure views can reference tables and views in multiple databases

A

True

28
Q

What privilege must be granted on the databases the secure view is referencing

A

REFERENCE_USAGE

29
Q

T/F You need to grant REFERENCE_USAGE on the database where the secure view is created.

A

False

30
Q

Before adding a secured view to a share you must

A

grant the privilege separately on each database referenced in a view

30
Q

Before adding a secured view to a share you must

A

grant REFERENCE_USAGE separately on each database referenced in a view

31
Q

What is a replication group

A

A group of objects that are replicated as a unit to one or more target accounts
Read only access
point in time consistency

32
Q

to refresh objects in a replication group, what is the command

A

ALTER REPLICATION GROUP … REFRESH
The user has to exist in the source account and have the correct privilege

33
Q

What is the parameter for scheduling automatic refreshes

A

REPLICATION_SCHEDULE

34
Q

T/F only one refresh is executed at any given time

A

True

35
Q

What two constraints apply to database and share objects in replication groups?

A

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.

36
Q

T/F You must disable database replication before adding the database to a replication group.

A

True

37
Q

T/F Creating secure views on streams in your database and then sharing those views with consumers is not recommended.

A

True

38
Q

To create streams on shared tables or secure views, you must enable what?

A

Change tracking

39
Q

How do you revoke access to a shared view

A

REVOKE SELECT ON VIEW …. FROM SHARE….

40
Q

T/F By default, Automatic Clustering is suspended for the new table when it is cloned

A

True

41
Q

How do you enable automatic clustering on a cloned table

A

ALTER TABLE <name> RESUME RECLUSTER</name>

42
Q

When tables are cloned, how are internal stages handled

A

the internal stage associated with each table is cloned and empty

43
Q

T/F pipes that reference an internal stage can be cloned

A

False

44
Q

T/F pipes that reference an external stage can be cloned

A

True

45
Q

If a table is cloned with an external pipe and the INTEGRATION parameter is set, what happens the next time data is loaded

A

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.

46
Q

When a database or schema is cloned, what happens to any unconsumed records in the stream

A

They are inaccessible

47
Q

When does time travel start on a cloned table

A

When it was cloned

48
Q

When a database or schema is cloned, what happens to the tasks by default

A

The tasks are suspended by default

49
Q

T/F DDL statements are atomic and not part of multi-statement transactions.

A

True

50
Q

If an object is being cloned, and ddl statements are running against it at the same time, what happens to the ddl statements

A

The clone might not be effected by the ddl statements