Creating and managing shares in Snowflake - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new share named 'my_share'.

Snowflake

CREATE SHARE [1];

Drag options to blanks, or click blank then click option'

Amy_share

Bshare1

Cdata_share

Dnew_share

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different share name than 'my_share'.

Omitting the share name after CREATE SHARE.

The command CREATE SHARE my_share; creates a new share named 'my_share'.

2fill in blank

medium

Complete the code to add the database 'sales_db' to the share 'my_share'.

Snowflake

ALTER SHARE my_share ADD DATABASE [1];

Drag options to blanks, or click blank then click option'

Afinance_db

Bmarketing_db

Chr_db

Dsales_db

Attempts:

3 left

💡 Hint

Common Mistakes

Adding the wrong database name.

Forgetting to specify the database after ADD DATABASE.

The command ALTER SHARE my_share ADD DATABASE sales_db; adds the 'sales_db' database to the share.

3fill in blank

hard

Fix the error in the code to remove the database 'marketing_db' from the share 'my_share'.

Snowflake

ALTER SHARE my_share [1] DATABASE marketing_db;

Drag options to blanks, or click blank then click option'

ADROP

BDELETE

CREMOVE

DCLEAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using DELETE or REMOVE instead of DROP.

Omitting the DROP keyword.

The correct syntax to remove a database from a share is ALTER SHARE my_share DROP DATABASE marketing_db;.

4fill in blank

hard

Fill both blanks to grant usage on the share 'my_share' to the account 'partner_account'.

Snowflake

GRANT [1] ON SHARE my_share TO ACCOUNT [2];

Drag options to blanks, or click blank then click option'

AUSAGE

BSELECT

Cpartner_account

DREAD

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT or READ instead of USAGE.

Using the wrong account name.

The command GRANT USAGE ON SHARE my_share TO ACCOUNT partner_account; allows the partner account to use the share.

5fill in blank

hard

Fill all three blanks to create a share named 'analytics_share', add the database 'analytics_db', and grant usage to account 'client_account'.

Snowflake

CREATE SHARE [1]; ALTER SHARE [2] ADD DATABASE [3]; GRANT USAGE ON SHARE [2] TO ACCOUNT client_account;

Drag options to blanks, or click blank then click option'

Aanalytics_share

Banalytics_db

Dsales_db

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up share and database names.

Using the wrong share name in ALTER and GRANT commands.

This sequence creates the share 'analytics_share', adds the 'analytics_db' database, and grants usage to 'client_account'.

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