Skip to main content

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

  1. Organization

    • The absolute top level for managing billing, provisioning new accounts, and tracking usage across all regions and clouds.
  2. Account

    • A single deployment of Snowflake. An organization can have many accounts (e.g., DEV, PROD).
  3. Database

    • A logical grouping of schemas. Each account can have many databases.
  4. 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).
  5. 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_CUSTOMERS or just CUSTOMERS for base tables.
    • VW_ACTIVE_CUSTOMERS for views.
    • STG_AWS_S3_RAW for 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 A creates a table using the SYSADMIN role, the SYSADMIN role owns that table.
  • If User A leaves 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, and DROP privileges on the object.