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.