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.