Despite its cloud-based nature, Snowflake is in essence a relational database system. Which means it supports all of the objects RDBMS users are used to, plus a few more.
If you've used SQL Server, Oracle, MySQL or any other relational system, you will recognise a lot of these objects, with a few additions.
- Database
Essentially a folder, housing all of the objects mentioned below. A Customers database would house objects related to, well, customers! An awesome feature is the ability to clone a database - with or without data. - Table
Used to store data. 'nuff said! - View
Allows you to use a query as though it were a table. - Stored Procedure
A block of code which performs a certain action. These can be written in SQL, Python, Java, JavaScript and Scala. You can loop, have IF statements and the SP can be called as many times as needed. Can return a data set. - User-Defined Function (UDF)
A block of code which performs a certain task, e.g. changing a string to upper case. Returns a value, and supports the same five languages as stored procedures. - User-Defined Table Function (UDTF)
As above, but returns a table instead of a value.
So far, so relationally typical. But here are a few database objects that are not always found in other systems.
- Stage
Defines a location where data can be loaded into Snowflake tables, or data can be extracted from Snowflake tables into files. There are a few different types of Stage, which we will look at in future posts. - Notebook
A popular concept these days. Allows you to write code in a sequence, interspersed with notes and visualisations. - Pipe
Used by Snowpipe to copy data into a table using the COPY INTO command. - Sequence
Defines a unique sequence of numbers across sessions and statements. - Task
These are automated processes, and can be scheduled. - Stream (CDC)
Performs Change Data Capture (CDC). Records changes made to a table as a result of DML (Data Manipulation Language) statements, such as INSERT, UPDATE and DELETE. Can be used for auditing, for instance.
There are also some non-database objects:
- Share
Allows you to share databases and objects with other users. - Streamlit App
A Python library which allows you to create front-end applications for Snowflake.
We'll be exploring all of these objects in future posts. Why? Because these form the Snowflake object catalog, and you need to know all about them if you're going to pass your SnowPro Core exam!