Secure data sharing basics in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Snowflake Secure Data Sharing

Which statement best describes how Snowflake secure data sharing works?

A Data is copied and moved to the consumer's account for access.

B Data is encrypted and stored in a shared external cloud bucket accessible by both parties.

C Data remains in the provider's account and is accessed directly by the consumer.

D Data is exported as files and sent to the consumer via email.

💡 Hint

Think about whether data physically moves or stays put during sharing.

Snowflake secure data sharing allows consumers to query data directly in the provider's account without copying or moving it.

❓ Architecture

intermediate

Components Required for Snowflake Data Sharing

Which combination of Snowflake objects must a provider create to share data securely with a consumer?

A A share and a database containing the objects to share.

B A share, a database, and a secure view.

C A database, a share, and a virtual warehouse.

D A secure view and a virtual warehouse.

💡 Hint

Consider what objects define the shared data and the sharing mechanism.

The provider creates a share and adds database objects to it. The consumer accesses the shared data via the share.

❓ security

advanced

Access Control in Snowflake Secure Data Sharing

What is the effect of granting SELECT privileges on a table to a share in Snowflake?

A Consumers can only read the table data but cannot modify it.

B Consumers can modify the table data in the provider's account.

C Consumers can create new tables in the provider's database.

D Consumers can delete the table from the provider's database.

💡 Hint

Think about what SELECT privilege allows.

Granting SELECT on a table to a share allows consumers to read data but not modify or delete it.

❓ service_behavior

advanced

Behavior of Consumer Access to Shared Data

When a consumer queries data shared via Snowflake secure sharing, where is the query executed?

A In the provider's virtual warehouse accessing the original data.

B In the consumer's virtual warehouse accessing the provider's data remotely.

C In the consumer's virtual warehouse accessing a local copy of the data.

D In a shared virtual warehouse managed by Snowflake.

💡 Hint

Consider where compute resources run and where data physically resides.

The consumer's virtual warehouse runs the query, accessing the provider's data remotely without copying it.

✅ Best Practice

expert

Best Practice for Managing Sensitive Data in Snowflake Sharing

Which approach best protects sensitive columns when sharing data securely in Snowflake?

A Share the entire table and rely on consumers to ignore sensitive columns.

B Grant full access to the table and audit consumer queries regularly.

C Export the table to CSV, remove sensitive columns, then share the file externally.

D Create a secure view that excludes or masks sensitive columns and share the view instead.

💡 Hint

Think about controlling data visibility at the object level within Snowflake.

Using secure views to exclude or mask sensitive columns ensures only allowed data is shared securely.

Practice

1. What is the main benefit of using secure data sharing in Snowflake?

easy

A. Encrypt data before sharing it manually

B. Share live data without making copies

C. Create multiple copies of data for each user

D. Send data files via email securely

Solution

1. Step 1: Understand secure data sharing purpose

   Secure data sharing allows sharing data directly without duplication.

2. Step 2: Compare options

   Options A, B, and D involve copying or manual steps, which are not the main benefit.

3. Final Answer:

   Share live data without making copies -> Option B

4. Quick Check:

   Secure sharing = live data, no copies [OK]

Hint: Secure sharing means no data copies needed [OK]

Common Mistakes:

• Thinking data is copied to recipients
• Confusing sharing with encryption only
• Assuming manual file transfer is secure sharing

2. Which Snowflake object do you create first to start sharing data securely?

easy

A. Share

B. Role

C. Warehouse

D. Database

Solution

1. Step 1: Identify the object for sharing

   In Snowflake, a Share object is created to define what data to share.

2. Step 2: Eliminate other options

   Databases hold data, warehouses process queries, roles manage permissions but do not start sharing.

3. Final Answer:

   Share -> Option A

4. Quick Check:

   Create a Share to share data [OK]

Hint: Start sharing by creating a Share object [OK]

Common Mistakes:

• Trying to share by creating a database
• Confusing warehouse with sharing object
• Assuming roles start sharing

3. Given this SQL snippet, what does it do?

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

medium

A. Creates a share, grants access to sales_db, and shares with account_123

B. Creates a database named my_share and grants access to account_123

C. Adds a new database to sales_db and shares it with account_123

D. Creates a warehouse named my_share and assigns it to account_123

Solution

1. Step 1: Analyze each command

   CREATE SHARE my_share; creates a share object named my_share.
   GRANT SELECT ON DATABASE sales_db TO SHARE my_share; grants read access on sales_db to the share.
   ALTER SHARE my_share ADD ACCOUNTS = ('account_123'); adds account_123 as a recipient.

2. Step 2: Understand the overall effect

   This sequence shares sales_db data with account_123 via my_share.

3. Final Answer:

   Creates a share, grants access to sales_db, and shares with account_123 -> Option A

4. Quick Check:

   Share + grant + add account = share data [OK]

Hint: Look for CREATE SHARE then GRANT and ADD ACCOUNTS [OK]

Common Mistakes:

• Confusing share with database creation
• Thinking ALTER SHARE adds data, not accounts
• Mixing warehouse and share concepts

4. You run this command but get an error:

ALTER SHARE my_share ADD ACCOUNTS = ('account_123', 'account_123');

What is the likely cause?

medium

A. Account names must be numeric

B. Missing SELECT privilege on the share

C. Share name is invalid

D. Duplicate account names in the list

Solution

1. Step 1: Check the command syntax and parameters

   The command tries to add two accounts with the same name 'account_123' to the share.

2. Step 2: Identify the error cause

   Duplicate account names in the ADD ACCOUNTS list cause an error because each account must be unique.

3. Final Answer:

   Duplicate account names in the list -> Option D

4. Quick Check:

   Duplicate accounts cause errors [OK]

Hint: Avoid duplicate accounts in ADD ACCOUNTS list [OK]

Common Mistakes:

• Assuming share name is invalid without checking
• Thinking privileges cause this error
• Believing account names must be numbers

5. You want to share only specific tables from your database securely. Which approach should you use?

hard

A. Grant SELECT on the entire database to the share

B. Grant SELECT on specific schemas to the share

C. Grant SELECT on specific tables to the share

D. Create a new database with only those tables and share it

Solution

1. Step 1: Understand granularity of sharing

   Snowflake allows granting privileges at database, schema, or table level to shares.

2. Step 2: Choose the most precise option

   Granting SELECT on specific tables limits sharing to only those tables, which fits the requirement.

3. Step 3: Evaluate other options

   Granting on entire database or schema shares more than needed; creating a new database is extra work and less flexible.

4. Final Answer:

   Grant SELECT on specific tables to the share -> Option C

5. Quick Check:

   Grant on tables = share specific data [OK]

Hint: Grant on tables to share only those tables [OK]

Common Mistakes:

• Granting on database shares too much data
• Assuming schema-level grant is always best
• Creating new databases unnecessarily