Lab 10 · Domain 3 · You are Priya

Move files through a loading dock

Read every Teach first section before you copy SQL. Domain 3 starts here: stages hold files, file formats describe shape, COPY INTO moves data in or out. We avoid local PUT so Windows Snowsight still works.

Business problem Riley’s vendor drops CSVs. Morgan wants a repeatable dock: named stage, format object, load into a table, and the reverse (unload) for a partner. “No mystery desktop folders.”

Solution you will build PIPELINE.STG_ALPINE_LOAD, CSV/JSON file formats, seed with INSERT, unload with COPY INTO @stage FROM (SELECT …), validate, reload with transform + ON_ERROR. Directory tables for file metadata awareness.

Before

Mystery folders

Files on laptops only
No shared format definition
“It worked on my machine”

After

Named dock + COPY

Internal stage in PIPELINE
FF_CSV_CUSTOMERS / FF_JSON
Load · unload · validate

Episode 10 · Start here

Riley draws a loading dock. Dev names every object.

RileyPeer engineer

I keep pasting CSV into worksheets. When the file gets big, that falls over. Where do files live before they’re a table?

DevCoach

On a stage — the loading dock. File format = the packing label (CSV vs JSON). COPY INTO = the forklift in or out.

Stage stores files · table stores rows · warehouse runs the COPY.

See it — stage types

Personal

User stage @~

Your private dock. Fine for scratch; awkward to share with Riley’s role.

LIST @~;

Tied to table

Table stage @%T

Dock attached to one table. Handy for quick loads into that table only.

LIST @%CUSTOMERS_LOAD;

Shared (this lab)

Named internal stage

First-class object you GRANT like a table. Best for team pipelines.

@STG_ALPINE_LOAD

Cloud bucket

External stage

Points at S3 / Azure / GCS via storage integration. Same COPY syntax, different dock location.

s3://alpine-raw/…
+ storage integration

See it — COPY directions

Load

Stage → table

COPY INTO my_table FROM @stage. Optional SELECT transform, ON_ERROR, VALIDATION_MODE.

files on dock →
rows in CUSTOMERS_LOAD_2

Unload

Table → stage

COPY INTO @stage FROM (SELECT …). Writes files out for partners or reload drills.

CUSTOMERS_LOAD →
@STG…/customers_unload/

Objects in this lab

1 · Seed INSERT rows CUSTOMERS_LOAD

2 · Unload COPY INTO @stage STG_ALPINE_LOAD

3 · Validate VALIDATION_MODE no load yet

4 · Reload COPY + transform CUSTOMERS_LOAD_2

Before Step 1

Kai: “Is a stage a table?”

KaiAnalyst

If I LIST a stage and see files, can I SELECT * FROM the stage like a table?

DevCoach

Not as normal rows. You LIST files, query with $1, $2 in a COPY SELECT, or use a directory table for file metadata. Tables hold typed columns after COPY.

1 File formats + named stage

What this does: Creates reusable packing labels (CSV + JSON) and the shared internal dock in PIPELINE, with directory metadata enabled.

SQL · formats + stage

USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
USE SCHEMA PIPELINE;

CREATE OR REPLACE FILE FORMAT FF_CSV_CUSTOMERS
  TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE STAGE STG_ALPINE_LOAD
  FILE_FORMAT = FF_CSV_CUSTOMERS
  DIRECTORY = (ENABLE = TRUE);

✓ SHOW STAGES LIKE 'STG_ALPINE%' returns one row

2 Seed the table (INSERT — no PUT)

What this does: Puts three demo customers in CUSTOMERS_LOAD. Snowsight on Windows often can’t PUT local files — INSERT keeps the lab moving.

SQL · create + insert

CREATE OR REPLACE TABLE CUSTOMERS_LOAD (
  CUSTOMER_ID NUMBER, FULL_NAME STRING,
  EMAIL STRING, COUNTRY STRING
);

INSERT INTO CUSTOMERS_LOAD VALUES
  (10, 'Fran Kim',   'fran.kim@example.com',   'US'),
  (11, 'Gita Singh', 'gita.singh@example.com', 'CA'),
  (12, 'Hugo Diaz',  'hugo.diaz@example.com',  'US');

SELECT * FROM CUSTOMERS_LOAD ORDER BY CUSTOMER_ID;

Want real PUT? SnowSQL: PUT file://customers.csv @STG_ALPINE_LOAD/raw/ then COPY — commented in sql/10_copy_stages.sql.

✓ Three rows in CUSTOMERS_LOAD

Before Step 3

Morgan: “Can we send a file out?”

MorganManager

Partner asks for a CSV extract. Do we email a spreadsheet from someone’s laptop again?

RileyPeer engineer

COPY INTO @stage FROM (SELECT …) writes files on the dock. Same forklift, opposite direction.

3 Unload to stage + directory list

What this does: Writes CSV files under @STG_ALPINE_LOAD/customers_unload/, lists them, refreshes directory metadata.

SQL · unload

COPY INTO @STG_ALPINE_LOAD/customers_unload/
  FROM (
    SELECT CUSTOMER_ID, FULL_NAME, EMAIL, COUNTRY
      FROM CUSTOMERS_LOAD
  )
  FILE_FORMAT = (FORMAT_NAME = 'FF_CSV_CUSTOMERS')
  OVERWRITE = TRUE HEADER = TRUE;

LIST @STG_ALPINE_LOAD/customers_unload/;
ALTER STAGE STG_ALPINE_LOAD REFRESH;

✓ LIST shows file(s) under customers_unload/

4 VALIDATION_MODE — check without loading

What this does: Parses stage files and reports errors without inserting rows. Safe dry-run before a big load.

SQL · validate

COPY INTO CUSTOMERS_LOAD
FROM @STG_ALPINE_LOAD/customers_unload/
FILE_FORMAT = (FORMAT_NAME = 'FF_CSV_CUSTOMERS')
VALIDATION_MODE = 'RETURN_ERRORS';

✓ Validation output · CUSTOMERS_LOAD row count unchanged

5 Reload with TRANSFORM + ON_ERROR

What this does: Loads into a second table, uppercasing names and stamping LOADED_AT during COPY. ON_ERROR = CONTINUE keeps good rows if a bad one appears.

SQL · transform load

CREATE OR REPLACE TABLE CUSTOMERS_LOAD_2 (
  CUSTOMER_ID NUMBER, FULL_NAME STRING,
  EMAIL STRING, COUNTRY STRING,
  LOADED_AT TIMESTAMP_NTZ
);

COPY INTO CUSTOMERS_LOAD_2
  (CUSTOMER_ID, FULL_NAME, EMAIL, COUNTRY, LOADED_AT)
FROM (
  SELECT $1::NUMBER, UPPER($2::STRING), $3::STRING,
         $4::STRING, CURRENT_TIMESTAMP()
  FROM @STG_ALPINE_LOAD/customers_unload/
)
FILE_FORMAT = (FORMAT_NAME = 'FF_CSV_CUSTOMERS')
ON_ERROR = 'CONTINUE';

✓ Names UPPER · LOADED_AT filled

6 JSON awareness (tiny)

What this does: Reminds you JSON uses a different file format and often lands in VARIANT. Tiny INSERT stands in for a JSON COPY.

SQL · JSON / VARIANT

CREATE OR REPLACE FILE FORMAT FF_JSON_EVENTS
  TYPE = JSON STRIP_OUTER_ARRAY = TRUE;

CREATE OR REPLACE TABLE EVENTS_STAGE_LOAD (PAYLOAD VARIANT);
INSERT INTO EVENTS_STAGE_LOAD
SELECT PARSE_JSON(COLUMN1) FROM VALUES
  ('{"event":"click","user":1}'),
  ('{"event":"purchase","user":2}');
SELECT * FROM EVENTS_STAGE_LOAD;

✓ Two VARIANT payloads

Exam presses further — say these out loud

Basics moved files through the dock. Cover each answer before you open it.

Q1. User vs named vs external stage — when each?

Say: User (@~) = personal scratch. Named internal = shared team dock with grants. External = files already in cloud storage via storage integration. Table stage (@%) = dock tied to one table.

Q2. How do you TRANSFORM during COPY?

Say: COPY INTO table FROM (SELECT … FROM @stage) — cast columns, UPPER, add defaults like CURRENT_TIMESTAMP(). Transform in the SELECT, not after a blind load, when possible.

Q3. What does PURGE = TRUE do?

Say: After a successful load, delete the loaded files from the stage. Saves clutter; don’t purge if you still need the files for audit/replay.

Q4. File format options you must name on CSV questions?

Say: TYPE, FIELD_DELIMITER, SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, NULL_IF / ERROR_ON_COLUMN_COUNT_MISMATCH as needed. JSON: STRIP_OUTER_ARRAY often matters.

Q5. VALIDATION_MODE vs ON_ERROR CONTINUE?

Say: VALIDATION_MODE dry-runs and does not load. ON_ERROR CONTINUE loads good rows and skips bad ones in a real COPY. Different tools — validate first on scary files.

Priya’s wrap-up (say this out loud)
“Domain 3 loading: I created FF_CSV_CUSTOMERS and named stage STG_ALPINE_LOAD in PIPELINE, seeded CUSTOMERS_LOAD with INSERT, unloaded with COPY INTO @stage FROM SELECT, validated with VALIDATION_MODE, then reloaded with a SELECT transform and ON_ERROR CONTINUE. Outcome: a repeatable dock without depending on Windows PUT — ready for Snowpipe / streams in later labs.”

Next Lab 11 — continuous loading (Snowpipe) and related automation. Review Domain 2–3 tips.