Reader accounts for non-Snowflake users - Mini Project: Build & Apply

Reader accounts for non-Snowflake users

📖 Scenario: You are a data engineer at a company that uses Snowflake for data warehousing. Your company wants to securely share data with a partner organization that does not have Snowflake accounts. To enable this, you will create a Reader account in Snowflake. This special account allows the partner to access shared data without needing their own Snowflake login.

🎯 Goal: Build a Snowflake Reader account setup that shares a database with a non-Snowflake user partner. You will create the Reader account, share a database, and grant the necessary privileges so the partner can query the shared data securely.

📋 What You'll Learn

Create a Reader account named partner_reader_account in Snowflake

Create a database named shared_data_db

Create a share named partner_share in the main account

Add the shared_data_db to the partner_share

Grant usage privileges on the database and schema to the share

Grant select privileges on all tables in the schema to the share

Associate the Reader account with the share

💡 Why This Matters

🌍 Real World

Companies often need to share data securely with partners who do not have Snowflake accounts. Reader accounts provide a simple, secure way to do this without requiring new user licenses.

💼 Career

Data engineers and cloud architects use Reader accounts to enable external data sharing, a common task in data collaboration and governance.

1

Create the Reader account

Write a Snowflake SQL command to create a Reader account named partner_reader_account with the comment 'Reader account for partner organization'.

Snowflake

-- Your code here to create the Reader account

Hint

Use the CREATE READER ACCOUNT statement with the exact account name and comment.

2

Create the shared database

Write a Snowflake SQL command to create a database named shared_data_db.

Snowflake

CREATE READER ACCOUNT partner_reader_account COMMENT = 'Reader account for partner organization';
-- Your code here to create the database

Hint

Use the CREATE DATABASE command.

3

Create a share and add the database

Write Snowflake SQL commands to create a share named partner_share in your main account and add the database shared_data_db to this share.

Snowflake

CREATE READER ACCOUNT partner_reader_account COMMENT = 'Reader account for partner organization';
CREATE DATABASE shared_data_db;
-- Your code here to create the share and add the database

Hint

Use CREATE SHARE to make the share, then ALTER SHARE ... ADD DATABASE to add the database.

4

Grant privileges and associate Reader account with the share

Write Snowflake SQL commands to grant usage on the database shared_data_db and its schema public to the share partner_share. Also grant select on all tables in the public schema to the share. Finally, associate the Reader account partner_reader_account with the share partner_share.

Snowflake

CREATE READER ACCOUNT partner_reader_account COMMENT = 'Reader account for partner organization';
CREATE DATABASE shared_data_db;
CREATE SHARE partner_share;
ALTER SHARE partner_share ADD DATABASE shared_data_db;
-- Your code here to grant privileges and associate the Reader account

Hint

Grant usage on database and schema, select on all tables, then link the Reader account to the share.

Practice

1. What is the main purpose of a Reader Account in Snowflake?

easy

A. To monitor Snowflake account usage and billing

B. To allow non-Snowflake users to access shared data securely

C. To manage user permissions within a Snowflake account

D. To create a backup of Snowflake data

Solution

1. Step 1: Understand the role of Reader Accounts

   Reader accounts are designed to let users without Snowflake accounts access shared data safely.

2. Step 2: Compare options with this purpose

   The options for monitoring usage, creating backups, and managing user permissions describe other Snowflake features unrelated to Reader Accounts.

3. Final Answer:

   To allow non-Snowflake users to access shared data securely -> Option B

4. Quick Check:

   Reader Account purpose = secure data access for non-users [OK]

Hint: Reader accounts = access for users without Snowflake [OK]

Common Mistakes:

• Confusing Reader accounts with user management
• Thinking Reader accounts are for backups
• Assuming Reader accounts monitor billing

2. Which SQL command is used to create a Reader Account from an existing share in Snowflake?

easy

A. CREATE READER ACCOUNT

B. CREATE ACCOUNT READER

C. CREATE READER ACCOUNT FROM SHARE

D. CREATE SHARE READER ACCOUNT

Solution

1. Step 1: Recall the correct syntax for creating Reader Accounts

   The correct command is CREATE READER ACCOUNT ... FROM SHARE ... followed by parameters.

2. Step 2: Eliminate incorrect syntax options

   Only CREATE READER ACCOUNT FROM SHARE matches the correct syntax including the FROM SHARE clause.

3. Final Answer:

   CREATE READER ACCOUNT FROM SHARE -> Option C

4. Quick Check:

   Command to create Reader Account from share = CREATE READER ACCOUNT FROM SHARE [OK]

Hint: Remember exact phrase: CREATE READER ACCOUNT FROM SHARE [OK]

Common Mistakes:

• Omitting FROM SHARE clause
• Swapping word order
• Combining CREATE and SHARE incorrectly

3. Given the SQL snippet:

CREATE READER ACCOUNT partner_reader FROM SHARE partner_share;

What is the expected result of this command?

medium

A. Grants partner_share access to the partner_reader account

B. Creates a new share named partner_reader from the account partner_share

C. Deletes the share named partner_share

D. Creates a new Snowflake account named partner_reader with access to partner_share data

Solution

1. Step 1: Analyze the CREATE READER ACCOUNT command

   This command creates a new reader account named partner_reader that can access data shared via partner_share.

2. Step 2: Understand the relationship between reader account and share

   The reader account is a new Snowflake account for external users, linked to the existing share.

3. Final Answer:

   Creates a new Snowflake account named partner_reader with access to partner_share data -> Option D

4. Quick Check:

   CREATE READER ACCOUNT creates account with share access [OK]

Hint: Reader account = new account accessing existing share [OK]

Common Mistakes:

• Confusing share creation with reader account creation
• Thinking the share is deleted
• Mixing up grant and create commands

4. You run this command:

CREATE READER ACCOUNT my_reader FROM SHARE;

But get an error. What is the likely cause?

medium

A. Missing share name after FROM SHARE

B. Reader accounts cannot be created from shares

C. Syntax requires USING keyword before share name

D. Reader account name cannot contain underscores

Solution

1. Step 1: Check the command syntax

   The command must specify the share name after FROM SHARE. Here, it is missing.

2. Step 2: Verify other options

   Reader accounts can be created from shares, no USING keyword is needed, and underscores are allowed in names.

3. Final Answer:

   Missing share name after FROM SHARE -> Option A

4. Quick Check:

   FROM SHARE must be followed by share name [OK]

Hint: Always specify share name after FROM SHARE [OK]

Common Mistakes:

• Omitting share name
• Adding unnecessary keywords
• Assuming naming restrictions that don't exist

5. A company wants to share data with a partner who does not have a Snowflake account. Which steps correctly describe how to enable this using Reader Accounts?

hard

A. Create a share with the data, then create a Reader Account from that share for the partner

B. Create a Reader Account first, then create a share inside that account

C. Ask the partner to create a Snowflake account, then grant access

D. Export data to CSV and send it manually

Solution

1. Step 1: Understand Reader Account workflow

   First, create a share containing the data to share.

2. Step 2: Create a Reader Account from the share

   This creates a Snowflake account for the partner to access the shared data without needing their own account.

3. Step 3: Evaluate other options

   Create a Reader Account first, then create a share inside that account reverses the order incorrectly; C requires partner to have an account; D is manual and not using Snowflake features.

4. Final Answer:

   Create a share with the data, then create a Reader Account from that share for the partner -> Option A

5. Quick Check:

   Share data first, then create Reader Account [OK]

Hint: Share data first, then create Reader Account [OK]

Common Mistakes:

• Creating Reader Account before share
• Assuming partner must have Snowflake account
• Using manual data export instead of Reader Account