Snowflake Data Objects

Introduction to Data Modeling in Snowflake

Nuno Rocha

Director of Engineering

Data warehouse

Data warehouse representation

Traditional vs. virtual warehouse

Snowflake virtual data warehouse

Traditional vs. virtual warehouse (1)

Snowflake virtual data warehouse vs. traditional data warehouse

Traditional vs. virtual warehouse (2)

CREATE WAREHOUSE: Snowflake clause that establishes a virtual computing resource for data processing tasks.
USE WAREHOUSE: Snowflake clause that designates the active data warehouse for the current session.

Snowflake virtual data warehouse vs. traditional data warehouse

Schemas and tables

Database, Schemas, Tables

Schemas and tables (1)

Snowflake UI

Schemas and tables (2)

CREATE DATABASE: Snowflake clause to create a new database for organizing data objects.
CREATE SCHEMA: Snowflake clause to define a container for grouping tables and views.
CREATE TABLE: Snowflake command to create a new table structure and data columns within a specified schema.

Snowflake UI, Creating elements

Views

View visualization

VIEWS:
- Acts like a virtual entity.
- Avoid data duplicates.
- Saves storage space and ensures data consistency.
- Dynamically present data based on query logic.

Views (1)

Example VIEW summarizing customers' urgent order

CREATE OR REPLACE VIEW customer_urgent_orders AS
SELECT c.c_name AS customer_name, 
    COUNT(DISTINCT o_orderkey) AS order_count,
    SUM(o_totalprice) AS total_price
FROM customer AS c
    JOIN orders AS o 
    ON c.c_custkey = o.o_custkey
WHERE o.o_orderpriority = '1-URGENT'
GROUP BY c.c_name;
ORDER BY total_price DESC;

Materialized views

Pre-calculated view

MATERIALIZED VIEWS:
- Store pre-calculated results.
- Improve query performance by storing computed operations.
- Reduces query processing time.
- Maintain a refreshed data snapshot for retrieval at any time.
- Encapsulate complex operations for streamlined data modeling.

Materialized views (1)

CREATE OR REPLACE MATERIALIZED VIEW top_customers AS
SELECT o.o_custkey AS customer_id,
    c.c_name AS customer_name,
    CASE 
        WHEN SUM(o.o_totalprice) > 5000000 THEN 'Over Price'
        WHEN SUM(o.o_totalprice) > 3000000 THEN 'Top Price'
        WHEN SUM(o.o_totalprice) > 2000000 THEN 'Average Price'
        ELSE 'Review Price'
    END AS total_price
FROM customer AS c
    JOIN orders AS o ON c.c_custkey = o.o_custkey
GROUP BY o.o_custkey, c.c_name
HAVING total_price > 2000000;

Terminology overview

Data warehouse (traditional): A centralized system for storing and analyzing large volumes of data using a single server setup.
Virtual data warehouse (in Snowflake): A flexible, scalable set of cloud-based computing resources used explicitly for processing and analyzing data.
Database: In Snowflake, a database is the primary container for data, holding schemas.
Schema: Collection of logical structures,that contain tables, views, etc.
Table: Representation of an entity in the database; fundamental data storage structure, organized in rows and columns within schemas.
View: Saved query that presents data as a virtual entity without storing the data separately.
Materialized View: A stored version of a view that physically saves the query result for faster access.

Functions overview

CREATE OR REPLACE WAREHOUSE data_warehouse_name;

USE WAREHOUSE data_warehouse_name;

CREATE OR REPLACE DATABASE database_name;

CREATE OR REPLACE SCHEMA schema_name;

CREATE OR REPLACE TABLE table_name(
    column_name datatype,
    other_columns datatype);

Exemplary (materialized) view template

CREATE OR REPLACE VIEW view_name AS
SELECT column_name, 
    SUM(column_name2) AS sum_alias,
    CASE WHEN column_name5   condition   value THEN assigned_value
        ELSE another_assigned_value
    END AS case_alias
FROM table_name AS table_alias
    JOIN other_table AS other_alias ON table_alias.FK = other_alias.PK
    LEFT JOIN another_table AS another_alias ON table_alias.FK = other_alias.PK
GROUP BY column_name;

Let's practice!

Introduction to Data Modeling in Snowflake