Databases and schemas in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a database in Snowflake?

A database in Snowflake is a container that holds schemas, which in turn contain tables and other database objects. It organizes data logically for easy management.

beginner

What is a schema in Snowflake?

A schema is a way to group database objects like tables and views inside a database. It helps organize and separate data within the database.

beginner

How do databases and schemas relate in Snowflake?

A database contains one or more schemas. Schemas organize the data objects inside the database, like folders inside a filing cabinet.

intermediate

Why use multiple schemas in a Snowflake database?

Using multiple schemas helps separate data by project, team, or purpose. It improves organization, security, and access control.

beginner

How do you create a new schema in Snowflake?

You use the SQL command: CREATE SCHEMA schema_name; inside the desired database to create a new schema.

In Snowflake, what does a schema contain?

A Users

B Databases

C Warehouses

D Tables and views

Schemas contain tables, views, and other database objects. Databases contain schemas.

Which command creates a new database in Snowflake?

A CREATE DATABASE db_name;

B CREATE SCHEMA db_name;

C CREATE WAREHOUSE db_name;

D CREATE TABLE db_name;

The command CREATE DATABASE creates a new database.

What is the main purpose of using schemas in a database?

A To organize and separate data objects

B To store user passwords

C To run queries faster

D To create backups

Schemas help organize and separate tables and other objects inside a database.

Which of these is true about Snowflake databases?

A They contain warehouses

B They contain users

C They contain schemas

D They contain roles

Databases contain schemas. Warehouses, users, and roles are separate Snowflake objects.

How do you refer to a table inside a schema and database in Snowflake?

A schema.database.table

B database.schema.table

C table.schema.database

D schema.table.database

The correct order is database.schema.table to fully qualify a table.

Explain the relationship between databases and schemas in Snowflake and why this structure is useful.

Think of databases as big folders and schemas as smaller folders inside.

Describe how you would create and use multiple schemas in a Snowflake database for a team project.

Consider how dividing work helps keep things clear and secure.

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