The Snowflake object model is a hierarchical framework that organizes and manages data within the Snowflake cloud data platform . An “object” itself refers to a logical container or structure that is used to either
- Store data,
- Organize data, or
- Manage data.
From the top-level organization and account objects down to the granular elements like tables and views, the Snowflake object model provides a structured framework for data storage, access, and security. The following is a detailed overview of the key objects in the Snowflake object model and their respective functions.
Organisation
In Snowflake, an organisation is a top-level entity that groups together related accounts, providing a way to manage billing, usage, and access at a higher level.
Example: A multinational corporation might have a separate Snowflake organisation for each region it operates in, with individual accounts for each country.
Account
An account in Snowflake represents an independent environment with its own set of users, databases, and resources. It is the primary unit of access and billing.
Example: A retail company might have a Snowflake account dedicated to its e-commerce analytics, separate from other accounts used for different business functions.
Role
A role in Snowflake is a collection of permissions that define what actions a user or group of users can perform. Roles are used to enforce security and access control.
Example: A “Data Analyst” role might have permissions to query and view data in specific databases and schemas but not to modify or delete data.
User
A user in Snowflake is an individual or service that interacts with the platform, identified by a unique username. Users are assigned roles that determine their access and capabilities.
Example: A user named “john.doe” might be a data scientist with access to analytical tools and datasets within the Snowflake environment.
Share
A share in Snowflake is a mechanism for securely sharing data between different accounts or organisations. It allows for controlled access to specific objects without copying or moving the data.
Example: A company might create a share to provide its partner with read-only access to a specific dataset for collaboration purposes.
Network Policy
A network policy in Snowflake is a set of rules that define allowed IP addresses or ranges for accessing the Snowflake account, enhancing security by restricting access to authorized networks.
Example: A financial institution might configure a network policy to allow access to its Snowflake account only from its corporate network.
Warehouse
In Snowflake, a warehouse is a cluster of compute resources used for executing data processing tasks such as querying and loading data. Warehouses can be scaled up or down to manage performance and cost.
Example: A marketing team might use a small warehouse for routine reporting tasks and a larger warehouse for more intensive data analysis during campaign launches.
Resource Monitor
A resource monitor in Snowflake is a tool for tracking and controlling the consumption of compute resources. It can be used to set limits and alerts to prevent overspending.
Example: A company might set up a resource monitor to ensure that its monthly compute costs do not exceed a predetermined budget.
Database
A database in Snowflake is a collection of schemas and serves as the primary container for storing and organizing data. It is similar to a database in traditional relational database systems.
Example: A healthcare organization might have a database called “PatientRecords” that contains schemas for different types of medical data.
Schema
A schema in Snowflake is a logical grouping of database objects such as tables, views, and functions. It provides a way to organize and manage related objects within a database.
Example: In a “Sales” database, there might be a schema called “Transactions” that contains tables for sales orders, invoices, and payments.
UDF (User-Defined Function)
A UDF in Snowflake is a custom function created by users to perform specific operations or calculations that are not available as built-in functions.
Example: A retail company might create a UDF to calculate the total sales tax for an order based on different tax rates for each product category.
Task
A task in Snowflake is a scheduled object that automates the execution of SQL statements, including data loading, transformation, and other maintenance operations.
Example: A data engineering team might set up a task to automatically refresh a materialized view every night at midnight.
Pipe
A pipe in Snowflake is an object used for continuous data ingestion from external sources into Snowflake tables. It processes and loads streaming data in near real-time.
Example: A streaming service might use a pipe to ingest real-time user activity data into a Snowflake table for analysis.
Procedure
A procedure in Snowflake is a stored sequence of SQL statements that can be executed as a single unit. It is used to encapsulate complex business logic and automate repetitive tasks.
Example: A finance team might create a procedure to generate monthly financial reports by aggregating data from various sources and applying specific calculations.
Stages
In Snowflake, stages are objects used to stage data files before loading them into tables. They can be internal (managed by Snowflake) or external (located in cloud storage).
Example: A data integration process might use a stage to temporarily store CSV files before loading them into a Snowflake table for analysis.
External Stage
An external stage in Snowflake is a reference to a location in cloud storage (such as Amazon S3, Google Cloud Storage, or Azure Blob Storage) where data files are staged before loading.
Example: A company might use an external stage pointing to an Amazon S3 bucket to stage log files before loading them into Snowflake for analysis.
Internal Stage
An internal stage in Snowflake is a built-in storage location managed by Snowflake for staging data files before loading them into tables.
Example: An analytics team might use an internal stage to temporarily store JSON files before transforming and loading them into a Snowflake table for analysis.
Table
A table in Snowflake is a structured data object that stores data in rows and columns. Tables can be of different types, such as permanent, temporary, or external.
Example: A logistics company might have a permanent table called “Shipments” that stores detailed information about each shipment, including origin, destination, and status.
External Tables
External tables in Snowflake are tables that reference data stored in external stages, allowing for querying data directly from cloud storage without loading it into Snowflake.
Example: A data science team might use external tables to query large datasets stored in Amazon S3 without importing the data into Snowflake, saving storage costs.
Transient Tables
Transient tables in Snowflake are similar to permanent tables but with a shorter lifespan and lower storage costs. They are suitable for temporary or intermediate data.
Example: During a data transformation pipeline, a transient table might be used to store intermediate results that are needed for a short period before being discarded.
Temporary Tables
Temporary tables in Snowflake are session-specific tables that are automatically dropped at the end of the session. They are useful for temporary calculations or intermediate steps.
Example: In an ad-hoc analysis session, a data analyst might create a temporary table to store query results for further exploration without affecting the permanent dataset.
Permanent Tables
Permanent tables in Snowflake are tables that persist data indefinitely and are the default table type for long-term data storage.
Example: A financial institution might use permanent tables to store historical transaction data for compliance and reporting purposes.
View
A view in Snowflake is a virtual table that is defined by a SQL query. Views can be standard, secured, or materialized, each serving different purposes.
Example: A sales dashboard might use a view to present aggregated sales data by region and product category, based on a query that joins multiple underlying tables.
Secured Views
Secured views in Snowflake are views that enforce column-level security, ensuring that sensitive data is only visible to authorized users.
Example: In a multi-tenant application, a secured view might be used to ensure that each tenant can only see their own data, even though the underlying table contains data for all tenants.
Standard Views
Standard views in Snowflake are the default view type, providing a simple way to create a virtual table based on a SQL query without any additional security features.
Example: A marketing team might use a standard view to create a simplified representation of a complex query that combines customer.
Materialized Views
Materialized views in Snowflake are views that store the result set of the query physically, providing faster access to precomputed data.
Example: To speed up reporting on large datasets, a data warehouse might use materialized views to pre-aggregate daily sales data by store and product category.