Databases and schemas in Snowflake - Deep Dive

Overview - Databases and schemas

What is it?

Databases and schemas are ways to organize and store data in Snowflake, a cloud data platform. A database is like a big container that holds related data. Inside a database, schemas act like folders that group tables and other data objects. This structure helps keep data tidy and easy to find.

Why it matters

Without databases and schemas, data would be messy and hard to manage, especially when many people or applications use it. They help separate data by purpose, project, or team, preventing confusion and mistakes. This organization also improves security by controlling who can see or change specific data.

Where it fits

Before learning about databases and schemas, you should understand basic data concepts like tables and records. After this, you can learn about data sharing, access control, and advanced data modeling in Snowflake.

Mental Model

Core Idea

Databases are big containers for data, and schemas are folders inside them that organize related data objects.

Think of it like...

Think of a database as a filing cabinet and schemas as the drawers inside it. Each drawer holds folders (schemas) that group related documents (tables). This keeps everything organized and easy to find.

┌─────────────┐
│  Database   │
│  ┌───────┐  │
│  │Schema │  │
│  │  A    │  │
│  │ ┌───┐ │  │
│  │ │Tbl│ │  │
│  │ └───┘ │  │
│  └───────┘  │
│  ┌───────┐  │
│  │Schema │  │
│  │  B    │  │
│  │ ┌───┐ │  │
│  │ │Tbl│ │  │
│  │ └───┘ │  │
│  └───────┘  │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding what a database is

Concept: A database is a container that holds data and organizes it for easy access.

In Snowflake, a database groups related data objects like tables and views. It acts as the top-level container. You can create multiple databases to separate data by project or team. For example, a company might have one database for sales data and another for marketing data.

Result

You can create and use databases to keep data separated and organized.

Knowing that a database is a container helps you understand how Snowflake keeps data organized and separated for different uses.

2

Foundation

What schemas do inside databases

Concept: Schemas are subdivisions inside databases that group related tables and other objects.

Inside each database, schemas act like folders that hold tables, views, and other data objects. This lets you organize data further. For example, within a sales database, you might have schemas for customers, orders, and products.

Result

Schemas help you organize data inside databases, making it easier to manage and find.

Understanding schemas as folders inside databases clarifies how Snowflake structures data hierarchically.

3

Intermediate

Creating and using databases and schemas

🤔Before reading on: do you think you can create a schema without first having a database? Commit to your answer.

Concept: You must create a database before creating schemas inside it, and you use commands to manage them.

In Snowflake, you create a database with the command: CREATE DATABASE my_db; Then, inside it, create a schema: CREATE SCHEMA my_schema; You can then create tables inside the schema. This order is important because schemas belong to databases.

Result

You can organize data by creating databases and schemas in the right order.

Knowing the creation order prevents errors and helps you plan your data structure correctly.

4

Intermediate

How access control works with databases and schemas

🤔Before reading on: do you think granting access to a database automatically grants access to all its schemas? Commit to your answer.

Concept: Access permissions can be set separately on databases and schemas to control who can see or change data.

Snowflake lets you grant privileges on databases and schemas independently. For example, you can allow a user to use a database but restrict access to certain schemas inside it. This fine control helps secure data and limit access to only what is needed.

Result

You can protect sensitive data by controlling access at both database and schema levels.

Understanding separate access control layers helps you design secure data environments.

5

Intermediate

Default schemas and search paths

Concept: Snowflake uses default schemas and search paths to simplify how you refer to tables.

When you run queries, Snowflake looks for tables in your current schema by default. You can set a default schema for your session, so you don't have to write the full path every time. For example, if your default schema is my_db.my_schema, you can just write SELECT * FROM my_table; instead of SELECT * FROM my_db.my_schema.my_table;

Result

Queries become shorter and easier to write when default schemas are set.

Knowing about default schemas helps you write cleaner queries and avoid mistakes.

6

Advanced

Cross-database and cross-schema queries

🤔Before reading on: do you think you can query tables across different databases without special syntax? Commit to your answer.

Concept: Snowflake allows querying data across databases and schemas using fully qualified names.

You can write queries that join tables from different databases and schemas by specifying their full names, like SELECT * FROM db1.schema1.table1 JOIN db2.schema2.table2 ON ... This lets you combine data without moving it physically.

Result

You can analyze data across multiple databases and schemas seamlessly.

Understanding cross-database queries unlocks powerful data analysis capabilities.

7

Expert

Schema evolution and metadata management

🤔Before reading on: do you think schemas in Snowflake can change structure without downtime? Commit to your answer.

Concept: Schemas can evolve by adding or modifying tables without disrupting existing data or queries.

Snowflake supports schema changes like adding columns or creating new tables inside schemas without downtime. Metadata about schemas is stored separately and updated automatically. This flexibility allows continuous development and scaling of data models.

Result

You can update your data structure smoothly as requirements change.

Knowing how schema evolution works helps you design adaptable data systems that grow with your needs.

Under the Hood

Snowflake stores databases and schemas as metadata objects in its cloud service. When you create a database, Snowflake registers it as a namespace. Schemas are sub-namespaces inside databases. Tables and other objects are linked to schemas. This hierarchy is managed by Snowflake's metadata service, which tracks permissions, object definitions, and locations. Queries use this metadata to locate and access data efficiently.

Why designed this way?

This design separates data organization from physical storage, allowing Snowflake to scale and manage data flexibly in the cloud. Using databases and schemas as logical containers helps users organize data intuitively while Snowflake handles the complex storage and compute behind the scenes. Alternatives like flat namespaces would be harder to manage and secure at scale.

┌───────────────┐
│ Snowflake     │
│ Metadata      │
│ Service       │
│  ┌─────────┐  │
│  │Database │  │
│  │ Namespace│ │
│  │ ┌─────┐ │ │
│  │ │Schema│ │ │
│  │ │Namespace││
│  │ │ ┌───┐│ │
│  │ │ │Tbl││ │
│  │ │ └───┘│ │
│  │ └─────┘ │ │
│  └─────────┘ │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does granting USAGE on a database give access to all its schemas? Commit yes or no.

Common Belief: Granting USAGE on a database automatically grants access to all schemas inside it.

Reality: USAGE on a database allows you to see the database exists, but you still need USAGE on individual schemas to access their objects.

Why it matters: Assuming database access equals schema access can cause security gaps or confusion when users cannot access expected data.

Quick: Can you create a schema without a database first? Commit yes or no.

Common Belief: Schemas can exist independently without a database.

Reality: Schemas must be created inside an existing database; they cannot exist alone.

Why it matters: Trying to create schemas without databases leads to errors and misunderstanding of Snowflake's structure.

Quick: Does changing a schema's structure require downtime? Commit yes or no.

Common Belief: Modifying schemas always requires downtime or locking the data.

Reality: Snowflake allows schema changes like adding columns without downtime or locking, enabling continuous operations.

Why it matters: Believing schema changes require downtime can cause unnecessary delays and cautiousness in development.

Quick: Can you query tables across databases without specifying full names? Commit yes or no.

Common Belief: You can query tables across databases using just table names if they share the same schema name.

Reality: You must use fully qualified names including database and schema to query across databases.

Why it matters: Incorrect assumptions about naming cause query errors and confusion when working with multiple databases.

Expert Zone

1

Schemas can be used to implement multi-tenant data separation within the same database, improving resource sharing while isolating data.

2

Snowflake's metadata service caches database and schema information to speed up query planning, so frequent schema changes can impact performance temporarily.

3

Using separate databases for different environments (dev, test, prod) is a common pattern to avoid accidental data mixing and simplify access control.

When NOT to use

Avoid using a single database with many schemas for unrelated projects that require strict isolation; instead, create separate databases. For very large datasets with different lifecycle policies, consider separate databases to optimize storage and compute costs.

Production Patterns

In production, teams often create databases per business domain and schemas per application or data source. Access control is layered: database-level for broad permissions, schema-level for finer control. Cross-database queries are used for reporting and analytics combining multiple data sources.

Connections

Namespace management in operating systems

Databases and schemas act like namespaces that organize resources hierarchically.

Understanding OS namespaces helps grasp how Snowflake isolates and organizes data objects logically.

Folder and file systems

Schemas are like folders and tables like files inside them, mirroring how data is organized on computers.

Knowing file system organization clarifies how schemas group tables for easy navigation and management.

Library classification systems

Databases and schemas classify data like libraries classify books by categories and shelves.

Seeing data organization as classification helps appreciate the importance of logical grouping for efficient retrieval.

Common Pitfalls

#1 Trying to create a schema without an existing database.

Wrong approach:

CREATE SCHEMA my_schema;

Correct approach:

CREATE DATABASE my_db;
CREATE SCHEMA my_schema;

Root cause:

Misunderstanding that schemas must belong to a database leads to creation errors.

#2 Assuming access to a database grants access to all schemas inside it.

Wrong approach:

GRANT USAGE ON DATABASE my_db TO USER alice; -- expecting full access

Correct approach:

GRANT USAGE ON DATABASE my_db TO USER alice;
GRANT USAGE ON SCHEMA my_db.my_schema TO USER alice;

Root cause:

Confusing database-level and schema-level permissions causes unexpected access issues.

#3 Using unqualified table names when querying across databases.

Wrong approach:

SELECT * FROM table1; -- table1 is in another database

Correct approach:

SELECT * FROM other_db.other_schema.table1;

Root cause:

Not specifying full path leads to query errors or wrong data retrieval.

Key Takeaways

Databases and schemas in Snowflake organize data hierarchically to keep it manageable and secure.

A database is a top-level container, and schemas are folders inside it grouping related tables and objects.

You must create a database before creating schemas inside it, and permissions are managed separately at each level.

Using fully qualified names is essential for querying data across different databases and schemas.

Snowflake supports flexible schema changes without downtime, enabling smooth data evolution.

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