Loading Structured and Semi-Structured Data

Snowflake Architecture

Emily Melhuish

Technical Curriculum Developer, Snowflake

The Loading Workflow

Loading Workflow showing files going into stage and the nloaded into snowflake

Files land in the stage, execute COPY INTO to load them

What is a Stage?

Stage = temporary storage location

What is a Stage diagram from Snowflake

Create Internal Stage

CREATE STAGE snowy_peak_stage;

Create External Stage

CREATE STAGE my_s3_stage
  STORAGE_INTEGRATION = s3_int
  URL = 's3://snowy-peak-data/raw/';

Loading data into Snowflake

COPY INTO

Bulk loading from Stage
Structured and Semi-Structured Data
Scalable

INSERT

Great for a small number of rows without staging
One-off additions or minor changes

Snowsight UI

Non-technical users and quick one-off workloads

Inspecting a Stage with LIST

Loading Structured Data with COPY INTO

Query

CREATE OR REPLACE FILE FORMAT csv_format
  TYPE = CSV
  SKIP_HEADER = 1;

COPY INTO orders
FROM @snowy_peak_stage/orders.csv
FILE_FORMAT = (FORMAT_NAME = csv_format);

Loading and Querying Semi-Structured Data

Loading

JSON loads into a special Snowflake column type called VARIANT.

CREATE TABLE events (raw_event VARIANT);

COPY INTO events
FROM @snowy_peak_stage/events.json
FILE_FORMAT = (TYPE = JSON);

Querying

Use colon notation to navigate the JSON structure,

SELECT 
raw_event:user_id::STRING AS user_id,
raw_event:event_type::STRING 
AS event_type
FROM events;

Loading with the UI

Screenshot of Snowflake UI

Let's practice!

Snowflake Architecture