Snowflake database structures and DML

Introduction to Snowflake SQL

Palak Raina

Senior Data Engineer

Overview

Snowflake

SHOW
DESCRIBE
INSERT
UPDATE
MERGE
COPY

Similarities to Postgres

INSERT
UPDATE
MERGE

SHOW DATABASES

Snowflake

SHOW
```
SHOW DATABASES
```

Show databases results

SHOW TABLES

SHOW TABLES IN { DATABASE [ <db_name> ] }

SHOW TABLES IN DATABASE PIZZA_SALES

show tables result

SHOW TABLES LIKE

SHOW TABLES [ LIKE '<pattern>' ]
            [ IN { DATABASE [ <db_name> ] } ]

SHOW TABLES LIKE '%PIZZA%' IN DATABASE PIZZA_SALES

SHOW TABLES LIKE result

SHOW SCHEMAS, COLUMNS

SHOW SCHEMAS IN DATABASE PIZZA_SALES

Show schemas results

SHOW COLUMNS IN PIZZA_TYPE

Show columns results

SHOW VIEWS

SHOW VIEWS IN DATABASE PIZZA_SALES

Show views full result

DESCRIBE DATABASE, SCHEMA

DESCRIBE or DESC

DESCRIBE DATABASE PIZZA_SALES

Describe database pizza_sales result

DESCRIBE SCHEMA PUBLIC

Describe schema public result

DESCRIBE TABLE, VIEW

DESCRIBE TABLE PIZZA_TYPE

Describe table results

DESCRIBE VIEW ORDERS_VIEW

Describe view results

DESCRIBE STAGE

DESCRIBE STAGE my_local_stage

Describe stage results

DML (Data Manipulation Language) Commands

INSERT

Insert Using Explicitly Specified Values

  INSERT INTO orders (order_id, order_date, order_time)
  VALUES (1, '2015-01-01', '11:38:36')

Inserted records results

INSERT Using Query

Insert using Query

  INSERT INTO orders_filtered
    SELECT * FROM orders
        WHERE order_date > '2015-01-02'

Inserted records using query results

UPDATE

UPDATE orders
SET order_time = '17:00:00'
WHERE order_id = '5'

Before:

Before update orders table result

After:

After update orders table result

MERGE

Combines data from two tables

MERGE INTO orders_filtered AS target -- Target table
USING orders AS source -- Source table

ON target.order_id = source.order_id -- Common column

WHEN MATCHED THEN -- When there is a match
 UPDATE SET 
 -- Update order_date and time of target table
  target.order_date = source.order_date, 
  target.time = source.order_time

MERGE RESULTS

Source table: orders

Source table order results

Before Merge: orders_filtered

Before merge orders_filtered table results

After Merge: orders_filtered updated order_id = 5 based on orders table

After merge orders_filtered table results

COPY

Snowflake:

COPY INTO orders FROM  @my_local_stage/orders.csv 
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER=1 )

@my_local_stage: stage we have created.
orders.csv: file within that stage we're copying data from.
FILE_FORMAT: format of the source data, in this case, a CSV.

Let's practice!

Introduction to Snowflake SQL