Creating and Managing Database Objects

Snowflake Architecture

Emily Melhuish

Technical Curriculum Developer, Snowflake

Creating a Database and Schema

Creating a Database Query

CREATE DATABASE snowy_peak_db;

Creating a Schema Query

CREATE SCHEMA snowy_peak_db.raw;

Screenshot 2026-05-13 at 3.12.30 pm.png

Setting Context

Querying Your Data

Query

SELECT * EXCLUDE(email)
FROM user_registrations
LIMIT 10;

Output SQL output from user registration table

Limit does not guarantee a cheaper query

Understanding Your Tables

Query

SELECT table_name, row_count, bytes
FROM information_schema.tables
WHERE table_schema = 'PRODUCT';

Output Output of running query on product schema

Understanding Your Columns

Query

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'IN_APP_PURCHASES';

Output Output of running query on IN_APP_PURCHASES table

RBAC: Role-Based Access Control

Common Privileges

SELECT to read data
INSERT, UPDATE and DELETE to write data
USAGE to access a database, schema, or warehouse
OWNERSHIP for full structural control

RBAC: Granting Privileges in Practice

Granting access to the analyst role

GRANT USAGE ON DATABASE snowy_peak_db TO ROLE analyst;
GRANT USAGE ON SCHEMA product TO ROLE analyst;
GRANT SELECT ON TABLE product.in_app_purchases TO ROLE analyst;

Users do not get privileges directly
- They are assigned roles
Principle of least privilege

Let's practice!

Snowflake Architecture