Databases and schemas in Snowflake - Step-by-Step Execution

Process Flow - Databases and schemas

Start

↓

Create Database

↓

Create Schema inside Database

↓

Use Schema

↓

Create Tables/Objects in Schema

↓

Query or Manage Objects

↓

End

This flow shows how you create a database, then a schema inside it, use the schema, and create or manage tables within that schema.

Execution Sample

Snowflake

CREATE DATABASE mydb;
CREATE SCHEMA mydb.myschema;
USE SCHEMA mydb.myschema;
CREATE TABLE mytable (id INT, name STRING);

This code creates a database, a schema inside it, switches to that schema, and creates a table.

Process Table

Step	Command	Action	Result	Notes
1	CREATE DATABASE mydb;	Create database named 'mydb'	Database 'mydb' created	Database is now available
2	CREATE SCHEMA mydb.myschema;	Create schema 'myschema' inside 'mydb'	Schema 'myschema' created in 'mydb'	Schema organizes objects inside database
3	USE SCHEMA mydb.myschema;	Set current schema context	Current schema set to 'mydb.myschema'	Commands now affect this schema
4	CREATE TABLE mytable (id INT, name STRING);	Create table 'mytable' in current schema	Table 'mytable' created	Table ready to store data
5	-- End of commands --	No more commands	Execution complete	All objects created and ready

💡 All commands executed successfully; database and schema setup complete.

Status Tracker

Object	Initial	After Step 1	After Step 2	After Step 3	After Step 4	Final
Database	None	mydb	mydb	mydb	mydb	mydb
Schema	None	None	myschema	myschema	myschema	myschema
Current Schema Context	None	None	None	mydb.myschema	mydb.myschema	mydb.myschema
Table	None	None	None	None	mytable	mytable

Key Moments - 2 Insights

Why do we need to use 'USE SCHEMA' after creating it?

Because creating a schema only defines it; 'USE SCHEMA' sets the current working schema so that subsequent commands like creating tables happen inside it, as shown in step 3 of the execution_table.

Can we create a table without creating a schema first?

No, because tables must belong to a schema. The schema organizes tables inside a database. The execution_table shows schema creation before table creation.

Visual Quiz - 3 Questions

Look at the execution_table, what is the current schema context after step 3?

A None

B mydb

C mydb.myschema

D myschema

💡 Hint

Check the 'Current Schema Context' row in variable_tracker after step 3.

At which step is the table 'mytable' created?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look at the 'Table' row in variable_tracker and the 'Command' column in execution_table.

If we skip 'USE SCHEMA' command, where will the table be created?

A In 'mydb.myschema'

B Nowhere, error occurs

C In the default schema

D In a new schema automatically created

💡 Hint

Consider what happens if schema context is not set, referencing step 3 in execution_table.

Concept Snapshot

Databases hold schemas; schemas organize tables.
Create database first, then schema inside it.
Use 'USE SCHEMA' to set context.
Create tables inside the current schema.
This structure helps organize data clearly.

Full Transcript

This visual execution shows how to create a database and schema in Snowflake, then set the schema context and create a table inside it. First, the database 'mydb' is created. Next, the schema 'myschema' is created inside 'mydb'. Then, the command 'USE SCHEMA mydb.myschema' sets the current working schema. After that, a table 'mytable' is created inside this schema. Variables track the state of database, schema, current schema context, and table creation step by step. Key moments clarify why setting schema context is necessary and that tables must belong to schemas. The quiz tests understanding of schema context, table creation step, and effects of skipping 'USE SCHEMA'. The snapshot summarizes the relationship and commands for databases and schemas.

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