Account

A Snowflake account is a core component of the Snowflake Data Cloud platform, providing organizations with access to Snowflake's cloud data warehousing and analytics services. It serves as the primary environment where users can store, manage, and analyze their data. A Snowflake account is associated with a unique name and URL, enabling users to access their data and resources securely over the internet. Within a Snowflake account, users can create and manage various resources, including databases, warehouses, tables, views, and stored procedures. Additionally, a Snowflake account allows organizations to configure security settings, manage user access and permissions, monitor system performance, and track usage metrics. Snowflake accounts are available in different editions and pricing tiers, allowing organizations to choose the level of features and resources that best suit their needs and budget. Overall, a Snowflake account serves as the central hub for organizations to leverage Snowflake's powerful data management and analytics capabilities in the cloud.

Database

A Snowflake database is a logical container within a Snowflake account where data is stored and organized. It serves as a centralized repository for organizing and managing data tables, views, and other database objects. Snowflake databases provide a structured environment for storing data, allowing users to define schemas, tables, and relationships between data entities.

• All data in Snowflake are stored in database tables.

• Tables are logical entities and are collection of rows. The rows are collection of columns.

• Tables reside in another logical entity called schema.

• Schema are logical containers of database objects.

Schema

A Snowflake schema is a type of data warehouse schema that consists of a central fact table surrounded by multiple dimension tables. It is called a Snowflake schema because its diagram resembles a snowflake, with the fact table at the center and dimension tables branching out like spokes.

• Schema reside in database.

• There can be multiple schema in a database and multiple tables in a schema.

• A schema also contain other logical entities – views, stages, file format, sequences, stored procedures, etc.

• Schema is used for managing access to database objects it contains. 

Tables

• Tables and materialized views point to physical data.

• Data stored in tables are processed to get meaningful outputs by using SQL (Structured Query  Language) queries. 

• Databases and database objects are created by executing SQL Data Definition Language (DDL).

• Data stored in tables are processed by using SQL Data Manipulation Language (DML) queries.

• Views are logical entities comprising of SQL DML statements meant for ease of use.

• The logical entities, tables and materialized views point to physical entities that contain actual data.

• Data in the physical structure of a table are processed by using INSERT, UPDATE, DELETE data manipulation language (DML) queries of SQL.

  • Insert into ….

  • Select … set … where

  • Delete from …

• Tables can be dropped or truncated.

• Snowflake retains deleted and before image of updated underlying data of a table for Time Travel recovery for specified period depending on data retention settings.

• A dropped table can be recovered by executing …

• A point-in-time (Time Travel) recovery of a table can be done by executing….

• Process of data storage for materialized views is different from process of table data storage.

• Data for materialized views cannot be manipulated by executing DML queries.

• The underlying data for materialized views are periodically refreshed by Snowflake.

The following figure summarize Snowflake Object Hierarchy: