Snowflake Data Objects

1. Snowflake Data Objects

Welcome back! It is time to consider the architectural components of Snowflake. We'll uncover the roles and functions of these components to understand how to create a seamless environment for our data model storage and analysis.

2. Data warehouse

A data warehouse is a centralized repository where data from various sources is stored and integrated. It's designed for query and analysis, providing a comprehensive view of an organization's data, like a massive library that keeps an extensive collection of data books.

3. Traditional vs. virtual warehouse

A virtual warehouse in Snowflake is a collection of computing resources dynamically allocated for data processing tasks. In Snowflake, a warehouse isn't where the databases live; instead, it operates separately, only executing queries and analyses. Unlike a traditional warehouse, it is a fixed, centralized system for storing and analyzing data.

4. Traditional vs. virtual warehouse (1)

Think of a traditional data warehouse as a large storage room where all data tasks happen in one place, sometimes leading to congestion. In contrast, Snowflake's virtual warehouse is like having multiple dedicated rooms, each capable of handling different tasks simultaneously without affecting each other.

5. Traditional vs. virtual warehouse (2)

In Snowflake, we can use CREATE WAREHOUSE to create one of these virtual warehouses. Then, with USE WAREHOUSE, we specify which virtual data warehouse we use, optimizing resource allocation for different tasks.

6. Schemas and tables

Tables are the entities where your data lives in Snowflake. They are organized within schemas as in folders, which are again contained within databases. This hierarchy is crucial for organizing and managing access to data.

7. Schemas and tables (1)

Let's take a look at the Snowflake interface. Databases have multiple schemas, which group multiple tables. The warehouse is selected or specified when you run a query, which powers the processing of your data without actually storing any of it.

8. Schemas and tables (2)

In Snowflake, we can use the CREATE OR REPLACE command to create this structure. CREATE DATABASE will establish a new database to organize the data objects. CREATE SCHEMA will define the container for the database objects. CREATE TABLE will determine the entity's structure with the data columns to store the data.

9. Views

Views in Snowflake are powerful, acting as virtual entities that present queries. Views bring significant advantages since they seamlessly integrate query results into the data model, avoiding data duplication, saving storage, and maintaining consistency. Views dynamically display data according to query logic, supporting an efficient data modeling.

10. Views (1)

Creating a view in Snowflake is simple with the CREATE OR REPLACE VIEW command to define or update a view. For example, creating a view for summarizing customers' urgent orders lets you quickly access these results by querying the view, avoiding the need to rewrite the query.

11. Materialized views

Materialized Views in Snowflake are a valuable tool for optimizing query performance in data modeling. These views store pre-calculated query results, offering immediate access to optimized data and reducing computational overhead. The advantages include efficient querying, quick access to optimized data, and encapsulating complex operations. However, it's important to note that materialized views may not always reflect the most up-to-date data, as they maintain a refreshed data snapshot.

12. Materialized views (1)

The CREATE OR REPLACE MATERIALIZED VIEW command in Snowflake creates a stored query. For instance, we create a view flagging top customers who spent over two million and a classification for the total spent. Note the HAVING clause use, filtering results like WHERE but for groups of rows post-grouping. Here, it filters groups with a total spend of over two million post-summing orders.

13. Terminology overview

Let's recap the concepts that we've learned.

14. Functions overview

Take note as well of the implementation of the Snowflake clauses we discussed.

15. Exemplary (materialized) view template

Finally, let's take a look at this great example of a view template. We can use it in the same way for materialized views by changing it to CREATE OR REPLACE MATERIALIZED VIEW command.

16. Let's practice!

We went through many new concepts, now it is time to practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.