Snowflake Basics for Data Engineers

Snowflake is a cloud-native data warehouse platform used to store, process, and analyze large volumes of structured and semi-structured data. It is widely adopted by organizations for analytics, business intelligence, and data engineering workloads.

One of Snowflake's biggest advantages is its ability to separate storage and compute, allowing each to scale independently.

Why Snowflake?

Traditional databases often face challenges with scalability and concurrent workloads. Snowflake addresses these limitations by providing:

• Separate storage and compute layers
• Automatic scaling
• Multi-cloud support (AWS, Azure, GCP)
• Native support for structured and semi-structured data
• High concurrency without performance degradation
• Minimal infrastructure management

---

Snowflake Architecture

Snowflake consists of three major layers:

1. Database Storage Layer

• Stores all persisted data.
• Automatically compresses and organizes data.
• Manages micro-partitions behind the scenes.
• Scales independently from compute resources.

2. Compute Layer (Virtual Warehouses)

• Executes SQL queries.
• Can scale up or out independently.
• Multiple warehouses can access the same data simultaneously.

3. Cloud Services Layer

Responsible for:

• Authentication and security
• Query optimization
• Metadata management
• Access control
• Transaction management

                Cloud Services
        (Security, Metadata, Optimization)
                         │
        ┌────────────────┴────────────────┐
        │                                 │
 Virtual Warehouse A           Virtual Warehouse B
     (Compute)                     (Compute)
        │                                 │
        └────────────────┬────────────────┘
                         │
                    Storage Layer

---

Snowflake Object Hierarchy

Organization
 └── Account
      └── Database
           └── Schema
                ├── Tables
                ├── Views
                ├── Stages
                └── File Formats

---

Creating a Database

A database is the top-level container for schemas and objects.

CREATE DATABASE sales_db;

Use the database:

USE DATABASE sales_db;

---

Creating a Schema

Schemas help organize related objects within a database.

CREATE SCHEMA sales_schema;

Use the schema:

USE SCHEMA sales_schema;

---

Creating a Table

CREATE TABLE customers (
    id INT,
    name STRING,
    city STRING
);

Insert sample data:

INSERT INTO customers VALUES
(1, 'Mani', 'Vijayawada'),
(2, 'Rahul', 'Hyderabad');

Query data:

SELECT * FROM customers;

---

Virtual Warehouses

A Virtual Warehouse is Snowflake's compute engine.

Create a warehouse:

CREATE WAREHOUSE dev_wh
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;

Use the warehouse:

USE WAREHOUSE dev_wh;

Suspend warehouse:

ALTER WAREHOUSE dev_wh SUSPEND;

Resume warehouse:

ALTER WAREHOUSE dev_wh RESUME;

Benefits

• Auto Suspend
• Auto Resume
• Independent Scaling
• Concurrent Workloads

---

Working with Stages

Stages are used to store files before loading them into Snowflake.

Create an internal stage:

CREATE STAGE my_stage;

List files:

LIST @my_stage;

---

File Formats

Define how incoming files should be interpreted.

CREATE FILE FORMAT csv_format
TYPE = CSV
FIELD_DELIMITER = ','
SKIP_HEADER = 1;

---

Loading Data into Snowflake

The typical process is:

Source File
     │
     ▼
   Stage
     │
     ▼
 COPY INTO
     │
     ▼
  Snowflake Table

Load data:

COPY INTO customers
FROM @my_stage/customers.csv
FILE_FORMAT = (TYPE = CSV);

---

Snowflake Data Types

Numeric

NUMBER
INT
FLOAT

String

VARCHAR
STRING
TEXT

Date and Time

DATE
TIME
TIMESTAMP

Semi-Structured

VARIANT
OBJECT
ARRAY

Example:

SELECT PARSE_JSON('{
  "name":"Mani",
  "city":"Vijayawada"
}');

---

Time Travel

Time Travel allows access to historical versions of data.

Query data from five minutes ago:

SELECT *
FROM customers
AT (OFFSET => -300);

Common Use Cases

• Recover deleted records
• Restore dropped tables
• Audit historical changes

---

Zero-Copy Cloning

Create instant copies without duplicating storage.

CREATE TABLE customers_clone
CLONE customers;

Benefits:

• Fast environment creation
• Development and testing
• Minimal storage consumption

---

Role-Based Access Control (RBAC)

Create a role:

CREATE ROLE data_engineer;

Grant permissions:

GRANT USAGE ON DATABASE sales_db
TO ROLE data_engineer;

Common administrative roles:

Role	Purpose
ACCOUNTADMIN	Highest privilege
SYSADMIN	Object creation and management
SECURITYADMIN	User and role management
USERADMIN	User administration

---

Basic SQL Operations

Insert

INSERT INTO customers
VALUES (3, 'Suresh', 'Chennai');

Select

SELECT * FROM customers;

Update

UPDATE customers
SET city = 'Bangalore'
WHERE id = 3;

Delete

DELETE FROM customers
WHERE id = 3;

---

Typical Data Engineering Workflow

A common Snowflake architecture looks like this:

Amazon S3 / ADLS / GCS
           │
           ▼
        Stage
           │
           ▼
      COPY INTO
           │
           ▼
      Raw Tables
           │
           ▼
   Transformations
           │
           ▼
   Curated Tables
           │
           ▼
 Power BI / Tableau

---