Databases and schemas in Snowflake - Mini Project: Build & Apply

Databases and Schemas in Snowflake

📖 Scenario: You are working as a cloud data engineer. Your team needs a clear structure to organize data in Snowflake. This means creating a database and inside it, a schema to hold tables later.

🎯 Goal: Build a Snowflake setup by creating a database named company_data and inside it, a schema named sales_schema. This will prepare the environment for storing sales data.

📋 What You'll Learn

Create a database called company_data

Create a schema called sales_schema inside the company_data database

Use correct Snowflake SQL syntax

Follow best practices for naming and structure

💡 Why This Matters

🌍 Real World

Organizing data in Snowflake with databases and schemas helps teams keep data tidy and easy to find, just like folders on your computer.

💼 Career

Knowing how to create and manage databases and schemas is a basic skill for cloud data engineers and analysts working with Snowflake.

1

Create the company_data database

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

Snowflake

-- Your code here to create the database company_data

Hint

Use the CREATE DATABASE command followed by the database name.

2

Create the sales_schema schema inside company_data

Write a Snowflake SQL command to create a schema named sales_schema inside the database company_data.

Snowflake

CREATE DATABASE company_data;
-- Your code here to create the schema sales_schema inside company_data

Hint

Use CREATE SCHEMA with the full path including the database name.

3

Set the context to use company_data database

Write a Snowflake SQL command to set the current database context to company_data.

Snowflake

CREATE DATABASE company_data;
CREATE SCHEMA company_data.sales_schema;
-- Your code here to set the current database to company_data

Hint

Use the USE DATABASE command to switch context.

4

Set the context to use sales_schema schema

Write a Snowflake SQL command to set the current schema context to sales_schema inside the company_data database.

Snowflake

CREATE DATABASE company_data;
CREATE SCHEMA company_data.sales_schema;
USE DATABASE company_data;
-- Your code here to set the current schema to sales_schema

Hint

Use the USE SCHEMA command to switch schema context.

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