Creating and managing shares in Snowflake - Practice Exercises

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens when you create a share without adding any objects?

You create a share in Snowflake but do not add any databases or schemas to it. What will be the state of this share?

Snowflake

CREATE SHARE my_share;

A The share automatically includes all existing databases in your account.

B The share is created but contains no data and cannot be used to share data until objects are added.

C The share creation fails with an error because no objects were specified.

D The share includes only the default public schema automatically.

💡 Hint

Think about what a share represents and what it needs to share data.

A share in Snowflake is a container for database objects to share. Creating it without adding objects results in an empty share that cannot share data until objects are added.

❓ Configuration

intermediate

Which command correctly adds a database to an existing share?

You have an existing share named my_share. You want to add the database sales_db to this share. Which command is correct?

A ALTER SHARE my_share INCLUDE DATABASE sales_db;

B ALTER SHARE my_share ADD sales_db;

C ALTER SHARE my_share ADD DATABASE sales_db;

D ALTER SHARE my_share ADD DATABASE = sales_db;

💡 Hint

Recall the syntax for adding objects to a share in Snowflake.

The correct syntax to add a database to a share is ALTER SHARE <share_name> ADD DATABASE <database_name>;

❓ security

advanced

What is the effect of revoking usage on a share from a consumer account?

You revoke usage privileges on a share from a consumer account. What happens to the consumer's access to the shared data?

A The consumer can still query the data but cannot create new shared databases from the share.

B The consumer retains access until their current session ends.

C The consumer immediately loses access to all shared data from that share.

D The consumer's access is unaffected; revoking usage only affects the provider.

💡 Hint

Consider what revoking usage means for shared data access.

Revoking usage on a share prevents the consumer from creating new databases from the share but existing shared databases remain accessible for querying.

❓ Architecture

advanced

How does Snowflake handle data consistency when sharing data across accounts?

When a provider shares data with a consumer account, how does Snowflake ensure the consumer sees consistent data?

A Snowflake copies the data physically to the consumer's account to ensure consistency.

B Snowflake requires manual refreshes by the consumer to update shared data.

C Snowflake uses a zero-copy cloning mechanism to provide a consistent snapshot of the data at the time of sharing.

D The consumer always sees the latest live data with no snapshot guarantees.

💡 Hint

Think about how Snowflake shares data efficiently without duplication.

Snowflake Secure Data Sharing provides live access to the provider's data using zero-copy technology, so consumers always see the latest data.

✅ Best Practice

expert

What is the recommended approach to share only specific tables from a database?

You want to share only certain tables from a database with an external account. What is the best practice to achieve this in Snowflake?

A Add only the specific schemas containing the tables to the share; tables outside those schemas are excluded.

B Add the entire database to the share and restrict access using roles on the consumer side.

C Create a new database containing only the desired tables and share that database.

D Add the entire database to the share and use masking policies to hide unwanted tables.

💡 Hint

Consider how shares work at the database and schema level.

Snowflake shares data at the database and schema level. To share specific tables, add only the schemas containing those tables to the share.

Practice

1. What is the primary purpose of creating a share in Snowflake?

easy

A. To move data from one database to another

B. To create a backup of your data

C. To share data with other accounts without copying it

D. To encrypt data for security

Solution

1. Step 1: Understand the concept of shares in Snowflake

   Shares allow data to be shared between accounts without duplicating the data itself.

2. Step 2: Identify the main use case of shares

   Sharing data without copying saves storage and ensures data consistency.

3. Final Answer:

   To share data with other accounts without copying it -> Option C

4. Quick Check:

   Shares = Data sharing without copying [OK]

Hint: Shares share data directly, no copying needed [OK]

Common Mistakes:

• Thinking shares create data backups
• Confusing shares with data movement
• Assuming shares encrypt data automatically

2. Which Snowflake command correctly creates a share named my_share?

easy

A. ADD SHARE my_share;

B. MAKE SHARE my_share;

C. NEW SHARE my_share;

D. CREATE SHARE my_share;

Solution

1. Step 1: Recall the syntax for creating shares in Snowflake

   The correct command to create a share is CREATE SHARE <share_name>;.

2. Step 2: Compare options with correct syntax

   Only CREATE SHARE my_share; uses the correct CREATE SHARE syntax.

3. Final Answer:

   CREATE SHARE my_share; -> Option D

4. Quick Check:

   CREATE SHARE = correct command [OK]

Hint: Use CREATE SHARE to start a new share [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Forgetting the semicolon at the end
• Confusing ADD SHARE with creating

3. Given the commands:

CREATE SHARE my_share;
GRANT USAGE ON DATABASE sales_db TO SHARE my_share;
ALTER SHARE my_share ADD ACCOUNTS = ('account_123');

What does account_123 gain access to?

medium

A. Full access to all objects in sales_db

B. Only metadata about sales_db

C. Usage access to the sales_db database objects shared

D. No access until a role is granted

Solution

1. Step 1: Analyze the GRANT USAGE ON DATABASE to share

   Granting USAGE on a database to a share allows consumers to view database metadata (e.g., SHOW DATABASES, USE DATABASE) but not schemas, tables, or other internal objects.

2. Step 2: Understand the effect of adding accounts

   Adding account_123 to the share provides access limited to the granted privileges: database metadata only, as no schema or table privileges are granted.

3. Final Answer:

   Only metadata about sales_db -> Option B

4. Quick Check:

   GRANT USAGE ON DATABASE = metadata access only [OK]

Hint: GRANT USAGE on DB shares DB metadata only; need schema/table grants for data [OK]

Common Mistakes:

• Assuming full access to all objects in sales_db
• Thinking DB USAGE grants access to internal objects like tables
• Believing roles must be granted separately

4. You run:

CREATE SHARE my_share;
GRANT SELECT ON TABLE sales TO SHARE my_share;

But get an error. What is the likely cause?

medium

A. The table name must be fully qualified with database and schema

B. Shares cannot have privileges granted directly on tables

C. You need to add accounts to the share before granting privileges

D. You must grant privileges on the database, not the table

Solution

1. Step 1: Check the syntax for granting privileges on objects to shares

   Privileges on tables must be granted using fully qualified names: database.schema.table.

2. Step 2: Identify the missing qualification in the command

   The command uses just sales without database and schema, causing an error.

3. Final Answer:

   The table name must be fully qualified with database and schema -> Option A

4. Quick Check:

   Fully qualify table names when granting [OK]

Hint: Always use database.schema.table for grants [OK]

Common Mistakes:

• Granting on database instead of table
• Trying to grant privileges before adding accounts
• Assuming shares can't have table privileges

5. You want to share specific tables from two different databases with multiple accounts. Which approach is best?

hard

A. Create separate shares per database and add accounts to each share accordingly

B. Create one share, grant usage on both databases, and add all tables to it

C. Copy tables into one database, then create a single share

D. Grant privileges directly to accounts without using shares

Solution

1. Step 1: Understand share scope and management

   Shares are scoped to a single database, so sharing tables from different databases requires separate shares.

2. Step 2: Evaluate options for managing multiple accounts and databases

   Creating separate shares per database allows clear control and easier management of access.

3. Step 3: Consider drawbacks of other options

   Copying tables wastes storage; granting privileges directly is less secure and scalable.

4. Final Answer:

   Create separate shares per database and add accounts to each share accordingly -> Option A

5. Quick Check:

   Shares are per database, use multiple shares [OK]

Hint: Use one share per database for multi-db sharing [OK]

Common Mistakes:

• Trying to share multiple databases in one share
• Copying data instead of sharing
• Granting privileges directly to accounts bypassing shares