First Steps: The four parts of a Snowflake query context
First Steps is a series of blog posts by NativeML intended as bite-size reading for anyone new to Snowflake.
When logging into Snowflake and attempting to query data, you will need to select four pieces of context to do so.
A role is “An entity to which privileges can be granted” which means it’s a re-usable entity that is granted privileges to access certain things. As a user, you can assume or change to various roles that have been granted to your user account.
See below how I can assume either the SYSADMIN or the PUBLIC roles, both of which are built-in. Other common roles would be MARKETING_READ for read-only access to marketing data or ETL_WRITE for system accounts performing ETL operations.
Learn more about Snowflake Roles and Access Control.
The next piece of context you will need to select is a warehouse. A warehouse is a set of compute resources. Default warehouses have one cluster of resources behind a warehouse, but Enterprise Edition accounts can have multi-cluster warehouses. Multi-cluster warehouses are used to control user and query concurrency.
A warehouse is technically known as a virtual warehouse because no data is associated with the warehouse itself, only compute resources.
The size of the cluster behind the warehouse is an important tuning parameter for both performance and cost. Of course, since Snowflake is truly a Cloud/SaaS offering you can auto-suspend and auto-resume warehouses.
Database and Schema
A database belongs to exactly one Snowflake account and contains schemas. While a schema belongs to exactly one database and contains database objects such as tables, views, etc.
In Snowflake when you want to query, you have to choose both a Database and Schema. Together a database and schema are called a namespace. Unlike Oracle where a schema is confusingly equal to a user, a schema is simply another level of organizing tables.
This means the full coordinates to a table in snowflake require the database, schema, and table name. That’s slightly more complex than say Apache Hive which has a one-level hierarchy where the term database and schema are synonymous. However, this author prefers the two-level hierarchy of database and schema ends up being simpler and easy to use.
That’s it! Now you understand the basic structure of Snowflake. Go forth and query! If you need assistance or want to learn more about how NativeML can help – let’s chat!
Subscribe To Our Newsletter
Get tips every month, right in your inbox.