In Snowflake, database objects form a hierarchical structure that organizes and manages data efficiently within the AI Data Cloud platform. Understanding this hierarchy is essential for the SnowPro Core Certification.
**Databases** serve as the top-level container in Snowflake's organizational hie…In Snowflake, database objects form a hierarchical structure that organizes and manages data efficiently within the AI Data Cloud platform. Understanding this hierarchy is essential for the SnowPro Core Certification.
**Databases** serve as the top-level container in Snowflake's organizational hierarchy. A database holds schemas, which in turn contain other objects. Each Snowflake account can have multiple databases, allowing logical separation of data for different projects, environments, or business units. Databases can be created, cloned, and shared across accounts using Snowflake's data sharing capabilities.
**Schemas** exist within databases and act as logical groupings for database objects. They provide a namespace that helps organize tables, views, stages, file formats, sequences, and other objects. Every database contains a default schema called PUBLIC. Schemas enable better access control and help prevent naming conflicts between objects. Organizations typically use schemas to separate objects by function, such as RAW, STAGING, and ANALYTICS schemas.
**Tables** are the fundamental objects that store actual data in Snowflake. Snowflake supports several table types:
- **Permanent Tables**: Standard tables with full Time Travel and Fail-safe protection
- **Transient Tables**: Tables with Time Travel but no Fail-safe, reducing storage costs
- **Temporary Tables**: Session-specific tables that exist only for the duration of the session
- **External Tables**: Tables that reference data stored in external cloud storage
Tables in Snowflake use a columnar storage format optimized for analytical queries. They automatically compress data and organize it into micro-partitions, which are immutable storage units ranging from 50-500MB.
The complete object naming convention follows the pattern: DATABASE.SCHEMA.OBJECT. For example, SALES_DB.PUBLIC.CUSTOMERS refers to the CUSTOMERS table in the PUBLIC schema within the SALES_DB database. This three-part naming ensures unique identification across the entire Snowflake account.
Database Objects: Databases, Schemas, and Tables in Snowflake
Why Database Objects Are Important
Database objects form the foundational building blocks of data storage and organization in Snowflake. Understanding how databases, schemas, and tables work together is essential for the SnowPro Core certification because these concepts underpin virtually every operation you'll perform in Snowflake. Exam questions frequently test your knowledge of object hierarchies, naming conventions, and the relationships between these fundamental structures.
What Are Database Objects?
Databases are the highest-level container objects in Snowflake's logical hierarchy. They contain schemas and provide a namespace for organizing related data and objects. Each database is independent and can have its own access controls.
Schemas are containers within databases that group related tables, views, stages, file formats, sequences, and other objects. Every database has a default schema called PUBLIC. Schemas help organize objects logically and manage permissions at a granular level.
Tables are the primary objects for storing data in Snowflake. They exist within schemas and come in several types: - Permanent Tables: Default table type with full Time Travel and Fail-safe protection - Temporary Tables: Exist only for the duration of the session, no Fail-safe - Transient Tables: Persist beyond sessions but have no Fail-safe period, reducing storage costs - External Tables: Reference data stored in external cloud storage
How the Hierarchy Works
The object hierarchy follows this structure: Account → Database → Schema → Table/View/Other Objects
When referencing objects, you use fully qualified names: database_name.schema_name.object_name
You can set context using USE commands: - USE DATABASE database_name; - USE SCHEMA schema_name;
Key Characteristics to Remember
1. Object Naming: Names can be up to 255 characters, are case-insensitive unless quoted, and must be unique within their container
2. Time Travel: Permanent tables support up to 90 days (Enterprise Edition), transient and temporary tables support up to 1 day
3. Fail-safe: Only permanent tables have a 7-day Fail-safe period after Time Travel expires
4. Cloning: Databases, schemas, and tables can all be cloned using zero-copy cloning
5. Data Sharing: Databases can be shared with other Snowflake accounts; individual schemas and tables within shared databases can be granted selectively
Exam Tips: Answering Questions on Database Objects
1. Know the hierarchy order: Questions often test whether you understand that tables exist within schemas, and schemas exist within databases
2. Understand table types: Be clear on the differences between permanent, transient, temporary, and external tables, especially regarding Time Travel and Fail-safe
3. Remember storage implications: Transient and temporary tables cost less because they lack Fail-safe, making them suitable for staging or intermediate data
4. Focus on cloning behavior: When you clone a database, all schemas and objects within are cloned; when you clone a schema, all objects within that schema are cloned
5. Watch for fully qualified names: Questions may test whether you can identify the correct syntax for referencing objects across databases
6. Default schema awareness: Remember that PUBLIC is the default schema created in every database
7. Permissions cascade: Granting privileges on a database does not automatically grant access to schemas or tables within; privileges must be granted at each level or use FUTURE grants
8. Read questions carefully: Distinguish between questions asking about logical organization versus physical storage, as Snowflake separates these concerns