Lab 02 · Domain 1 objects · You are Priya
Read every Teach first section before you copy SQL. Snowflake has many object types. Today you touch the ones Core loves to name-drop — and learn which ones sit at account level vs inside a schema.
PRODUCTS table, a view, a sequence, a CSV file format, and an internal named stage
in ALPINE_RETAIL.LAB — then SHOW them so the catalog feels real.
Episode 02 · Start here
Priya, we need a table for products, a view for cheap SKUs, a sequence for order IDs, a file format, and a stage for inbound CSVs.
That’s… five different things? I thought Snowflake was just tables and SQL.
Dev (off-panel): Each object has a job. The exam asks “which object?” constantly.
Table stores rows. View stores a saved SELECT (no private copy of the result).
LAB.PRODUCTSLAB.V_PRODUCTS_CHEAPStores precomputed results — refresh/cost tradeoff. Not the same as a normal view.
Stage = folder pointer. File format = CSV/JSON recipe. Used together on COPY.
@LAB.STG_INBOUNDFF_CSV_STDHands out unique numbers (order IDs) without racing inserts.
SEQ_ORDER_ID.NEXTVALContinuous load, change feed, schedule, outbound share — know the names today.
Functions return values; procedures run multi-step logic (often scripting).
Before Step 1
Do I memorize every object type before creating anything?
No. SHOW warehouses/databases/schemas first — see the tree. Then create one of each common schema object.
What this does: Lists Alpine’s warehouse, database, and schemas — account vs DB vs schema awareness.
USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
SHOW WAREHOUSES LIKE 'WH_CORE%';
SHOW DATABASES LIKE 'ALPINE%';
SHOW SCHEMAS IN DATABASE ALPINE_RETAIL;Before Step 2
A view is a window on a table. If someone drops the table, the window shows nothing useful.
Exact. Materialized views are different — they store results. Exam distractor: treating MV like a free normal view.
What this does: Creates PRODUCTS and a cheap-SKU view.
USE SCHEMA LAB;
CREATE OR REPLACE TABLE LAB.PRODUCTS (
PRODUCT_ID NUMBER, SKU STRING,
NAME STRING, UNIT_PRICE NUMBER(10,2)
);
INSERT INTO LAB.PRODUCTS VALUES
(1, 'ALP-TEE', 'Alpine Tee', 29.00),
(2, 'ALP-HAT', 'Trail Hat', 19.50);CREATE OR REPLACE VIEW LAB.V_PRODUCTS_CHEAP AS
SELECT PRODUCT_ID, SKU, NAME, UNIT_PRICE
FROM LAB.PRODUCTS
WHERE UNIT_PRICE < 25;
SELECT * FROM LAB.V_PRODUCTS_CHEAP;Before Step 3
Can two inserts grab the same order ID if they run at once?
Sequences hand out unique numbers safely. Gaps can happen — uniqueness matters more than “no gaps.”
What this does: Creates SEQ_ORDER_ID starting at 1000 and pulls two values.
CREATE OR REPLACE SEQUENCE LAB.SEQ_ORDER_ID
START = 1000 INCREMENT = 1;
SELECT LAB.SEQ_ORDER_ID.NEXTVAL AS next_id;
SELECT LAB.SEQ_ORDER_ID.NEXTVAL AS next_id;Before Step 4
Can everyone dump files into Kai’s personal user stage for production loads?
No. User stage (@~) is personal. Named stage in a schema is shareable with roles — that’s what teams use.
What this does: Creates a CSV recipe and an internal stage that references it.
CREATE OR REPLACE FILE FORMAT LAB.FF_CSV_STD
TYPE = CSV FIELD_DELIMITER = ','
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"';
CREATE OR REPLACE STAGE LAB.STG_INBOUND
FILE_FORMAT = LAB.FF_CSV_STD;
SHOW STAGES IN SCHEMA LAB;
SHOW FILE FORMATS IN SCHEMA LAB;Cover each answer before you open it.
Say: A view is a saved query with no stored result set. A materialized view stores precomputed results and has refresh/cost implications. Don’t call every view an MV.
Say: User stage (@~) is private to the user. A named stage is a schema object you can grant to roles — preferred for shared pipelines.
Say: Warehouses, users, roles, and (among others) shares and account-level integrations. Tables/views/stages usually live in a schema inside a database.
Say: So COPY / LIST consumers inherit the CSV/JSON parsing recipe. You can still override per statement — defaults reduce mistakes.
Say: Pipe = continuous/auto load from stage. Stream = change-data feed on a table. Task = scheduled SQL (often after streams). Different jobs, different CREATE statements.