2.1 Object Hierarchy & Management
Now that you understand Snowflake's cloud architecture, it is time to look at how data is logically organized and managed within the platform.
Snowflake Object Hierarchy
The fully qualified name for any object in Snowflake follows a strict logical path:
DATABASE.SCHEMA.OBJECT_NAME
-
Organization
- The absolute top level for managing billing, provisioning new accounts, and tracking usage across all regions and clouds.
-
Account
- A single deployment of Snowflake. An organization can have many accounts (e.g.,
DEV,PROD).
- A single deployment of Snowflake. An organization can have many accounts (e.g.,
-
Database
- A logical grouping of schemas. Each account can have many databases.
-
Schema
- A logical grouping of database objects (tables, views, etc.). Every database has a default schema named
PUBLIC, but you can create as many as needed (e.g.,RAW,STAGING,PROD).
- A logical grouping of database objects (tables, views, etc.). Every database has a default schema named
-
Objects
- Tables: Where data is physically stored.
- Views: Virtual tables representing the result of a SQL query.
- Stages: Locations where files are stored for loading/unloading.
- Pipes: Objects used for continuous data ingestion (Snowpipe).
- Dynamic Tables: Declarative pipelines for data transformation.
Naming Conventions & Best Practices
Establishing a strong naming convention early is critical because renaming heavily dependent objects later can be painful.
- Databases: Often mapped to environments or major business domains (e.g.,
PROD_HR_DB,DEV_FINANCE_DB). - Schemas: Often mapped to layers of your data architecture (e.g.,
RAW,STAGING,MART,ANALYTICS). - Objects: Use clear, descriptive names.
TBL_CUSTOMERSor justCUSTOMERSfor base tables.VW_ACTIVE_CUSTOMERSfor views.STG_AWS_S3_RAWfor external stages.
[!IMPORTANT] Snowflake object names are case-insensitive by default, but if you enclose them in double quotes (e.g.,
"My_Table"), they become strictly case-sensitive. It is highly recommended to avoid using double quotes for object creation to save future headaches!
Object Ownership Model
In Snowflake, every object is owned by a Role, never by a User.
- If
User Acreates a table using theSYSADMINrole, theSYSADMINrole owns that table. - If
User Aleaves the company and their user account is dropped, the table remains perfectly safe and accessible because it is owned by the role, not the individual. - The owning role has full
USAGE,ALTER, andDROPprivileges on the object.