Databases and schemas in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Snowflake Database and Schema Hierarchy

In Snowflake, which statement correctly describes the relationship between databases and schemas?

A Databases and schemas are the same and can be used interchangeably.

B A schema contains multiple databases, and each database contains multiple tables.

C Tables contain multiple schemas, and schemas contain multiple databases.

D A database contains multiple schemas, and each schema contains multiple tables.

💡 Hint

Think about how folders and subfolders work on your computer.

In Snowflake, a database is like a main folder that holds multiple schemas, which are like subfolders. Each schema then contains tables.

❓ Configuration

intermediate

Creating a Schema in a Specific Database

Which Snowflake SQL command correctly creates a schema named sales_data inside the database company_db?

A CREATE SCHEMA company_db.sales_data;

B CREATE DATABASE company_db.sales_data;

C CREATE SCHEMA sales_data IN DATABASE company_db;

D CREATE sales_data SCHEMA IN company_db;

💡 Hint

Remember the syntax for creating schemas inside a database.

The correct syntax to create a schema inside a specific database is CREATE SCHEMA schema_name IN DATABASE database_name;

❓ Architecture

advanced

Best Practice for Organizing Data in Snowflake

You have multiple teams working on different projects. Which Snowflake structure best isolates data for security and management?

A Create multiple databases, one per team, each with its own schemas.

B Create one schema with multiple databases inside it, each for a team.

C Store all data in a single schema and use table names to identify teams.

D Create one database with multiple schemas, each schema for a team.

💡 Hint

Think about isolating data for security and easier management.

Using multiple databases per team provides better isolation and control over access and resources compared to using schemas alone.

❓ security

advanced

Granting Access to a Schema

Which SQL command grants the role analyst_role usage rights on the schema marketing in the database company_db?

A GRANT USAGE ON SCHEMA company_db.marketing TO ROLE analyst_role;

B GRANT ALL ON SCHEMA marketing TO ROLE analyst_role;

C GRANT USAGE ON DATABASE company_db TO ROLE analyst_role;

D GRANT SELECT ON DATABASE company_db TO ROLE analyst_role;

💡 Hint

Usage on schema allows access to objects inside it without giving full database rights.

Granting USAGE on a schema allows the role to access objects inside the schema without granting full database privileges.

❓ service_behavior

expert

Effect of Dropping a Schema with Dependent Objects

What happens when you run DROP SCHEMA company_db.sales_data; if the schema contains tables and views?

A The schema and all its objects are dropped immediately without warning.

B The command fails with an error because the schema is not empty.

C The schema is dropped, but tables and views remain in the database.

D The schema is marked for deletion and removed after 7 days automatically.

💡 Hint

Think about safety measures to prevent accidental data loss.

Snowflake requires schemas to be empty before dropping. If objects exist, the drop command fails.

Practice

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

easy

A. To store user login credentials

B. To organize tables and other data objects within a database

C. To manage network security settings

D. To create virtual machines

Solution

1. Step 1: Understand the role of a schema

   A schema is a container inside a database that holds tables and other data objects.

2. Step 2: Differentiate schema from other components

   User credentials, security settings, and virtual machines are unrelated to schemas in Snowflake.

3. Final Answer:

   To organize tables and other data objects within a database -> Option B

4. Quick Check:

   Schema = container for tables [OK]

Hint: Schemas hold tables inside databases [OK]

Common Mistakes:

• Confusing schemas with user accounts
• Thinking schemas manage security
• Mixing schemas with infrastructure components

2. Which of the following is the correct syntax to create a new schema named sales_data in Snowflake?

easy

A. CREATE SCHEMA sales_data;

B. MAKE SCHEMA sales_data;

C. NEW SCHEMA sales_data;

D. CREATE DATABASE sales_data;

Solution

1. Step 1: Recall Snowflake schema creation syntax

   The correct command to create a schema is CREATE SCHEMA schema_name;.

2. Step 2: Identify incorrect options

   Options A and B use invalid keywords. CREATE DATABASE sales_data; creates a database, not a schema.

3. Final Answer:

   CREATE SCHEMA sales_data; -> Option A

4. Quick Check:

   CREATE SCHEMA = correct syntax [OK]

Hint: Use CREATE SCHEMA to make schemas [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Confusing CREATE SCHEMA with CREATE DATABASE
• Missing semicolon at the end

3. Given the following commands executed in order:

CREATE DATABASE company_db;
CREATE SCHEMA hr_schema;
USE DATABASE company_db;
USE SCHEMA hr_schema;

What is the current working database and schema?

medium

A. Database: company_db, Schema: public

B. Database: hr_schema, Schema: company_db

C. Database: default, Schema: hr_schema

D. Database: company_db, Schema: hr_schema

Solution

1. Step 1: Analyze the commands step-by-step

   CREATE DATABASE company_db; creates the database (current context unchanged).
   CREATE SCHEMA hr_schema; creates hr_schema in the current database (which is the default database).
   USE DATABASE company_db; sets current database to company_db (schema: public).
   USE SCHEMA hr_schema; attempts to switch to hr_schema schema in company_db. Since hr_schema was created in the default database, this will fail unless hr_schema exists in company_db. However, if hr_schema was created after switching to company_db, it would exist there.
   Given the commands, CREATE SCHEMA hr_schema; was run before switching to company_db, so hr_schema is in the default database, not company_db. Therefore, USE SCHEMA hr_schema; will fail and schema remains public.

2. Step 2: Determine current context

   After execution: Database: company_db, Schema: public.

3. Final Answer:

   Database: company_db, Schema: public -> Option A

4. Quick Check:

   CREATE SCHEMA uses current DB context [OK]

Hint: Schemas created in current database context [OK]

Common Mistakes:

• Mixing database and schema names
• Assuming schema changes database
• Forgetting USE commands set context
• Not USE DATABASE before CREATE SCHEMA

4. You run the command USE SCHEMA analytics; but get an error saying the schema does not exist. What is the most likely cause?

medium

A. Schemas cannot be switched using USE SCHEMA

B. The syntax of the command is incorrect

C. The current database does not contain a schema named analytics

D. You need to create a database before using a schema

Solution

1. Step 1: Understand USE SCHEMA behavior

   The USE SCHEMA command switches to a schema within the current database context.

2. Step 2: Identify cause of error

   If the schema does not exist in the current database, Snowflake returns an error. Syntax is correct, and schemas can be switched. Creating a database is unrelated if one is already in use.

3. Final Answer:

   The current database does not contain a schema named analytics -> Option C

4. Quick Check:

   Schema must exist in current database [OK]

Hint: Schema must exist in current database to use it [OK]

Common Mistakes:

• Assuming USE SCHEMA creates schema
• Thinking syntax is wrong
• Ignoring current database context

5. You want to organize your data by creating a database sales_db with two schemas: domestic and international. Which sequence of commands correctly achieves this?

hard

A. CREATE SCHEMA domestic; CREATE SCHEMA international; CREATE DATABASE sales_db;

B. USE DATABASE sales_db; CREATE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international;

C. CREATE DATABASE sales_db; CREATE SCHEMA domestic; USE SCHEMA international; CREATE SCHEMA international;

D. CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international;

Solution

1. Step 1: Create the database first

   You must create the database sales_db before creating schemas inside it.

2. Step 2: Set the database context and create schemas

   Use USE DATABASE sales_db; to set the context, then create schemas domestic and international inside it.

3. Step 3: Verify command order

   CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international; follows the correct order: create database, switch to it, then create schemas. Other options create schemas before database or misuse commands.

4. Final Answer:

   CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international; -> Option D

5. Quick Check:

   Create DB, USE DB, then CREATE SCHEMA [OK]

Hint: Create DB first, then schemas inside it [OK]

Common Mistakes:

• Creating schemas before database
• Not switching to database before schemas
• Using USE DATABASE before creating it