Snowflake Object Hierarchy

Overview

Understanding how objects are organized and nested within Snowflake is fundamental for management, security, and querying. The COF-C03 exam will test your knowledge of this hierarchy and how objects relate to one another.

Key Concepts

Snowflake employs a strict, logical hierarchy. The top level is the Organization, followed by Accounts, Databases, Schemas, and finally, specific data objects.

1. Organization

An Organization is a first-class Snowflake object that links all the Snowflake accounts owned by your company.

• It is used for central billing, cross-account monitoring, and managing features like replication and failover across different regions or cloud providers.

2. Account

A Snowflake Account is a distinct environment tied to a specific cloud provider and region (e.g., AWS US East).

• Each account has its own users, roles, compute resources (Virtual Warehouses), and storage.
• An organization can have multiple accounts.

3. Database

A database is the highest-level logical container for data within an account.

• Every account can contain multiple databases.
• Databases are primarily organizational constructs to group related schemas.

4. Schema

A schema is a logical grouping of database objects (tables, views, etc.).

• Every database automatically includes a default schema named PUBLIC.
• A database can contain multiple schemas.

5. Objects (Tables, Views, etc.)

These are the actual data structures that reside within a schema.

• Examples: Tables, Views, Sequences, Stored Procedures, User-Defined Functions (UDFs), Stages, File Formats.

The Hierarchy Visualized

Organization
└── Account 1
    ├── User / Roles
    ├── Virtual Warehouses
    ├── Database A
    │   ├── Schema 1 (e.g., PUBLIC)
    │   │   ├── Table X
    │   │   ├── View Y
    │   └── Schema 2 (e.g., STAGING)
    │       └── Table Z
    └── Database B
└── Account 2

Fully Qualified Names

To refer to an object in SQL, especially if it resides in a different database or schema than your current active context, you use a Fully Qualified Name.

The format is: <database>.<schema>.<object>

SQL Examples

-- Creating the hierarchy
CREATE DATABASE sales_db;

-- Explicitly use the database
USE DATABASE sales_db;

CREATE SCHEMA apac_region;

-- Using a fully qualified name to query a table
SELECT * FROM sales_db.apac_region.daily_sales;

-- View current context
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

Exam Tips

⚠️ Crucial for COF-C03:

• Understand the difference between Account-level objects (Users, Roles, Warehouses, Databases) and Schema-level objects (Tables, Views, Functions, Stages).
• Virtual Warehouses are NOT tied to a database. They exist at the account level and can query any database the user has permissions for.
• Know how to construct and read a Fully Qualified Name (database.schema.table).

Key Takeaways

• The hierarchy is Organization -> Account -> Database -> Schema -> Object.
• Warehouses, Users, and Roles are Account-level objects.
• Tables and Views are Schema-level objects.
• Fully Qualified Names allow cross-database and cross-schema querying.