Setup and SQL Basics

The Snowflake object hierarchy, warehouses vs databases, creating warehouses/databases/schemas/tables, core DML, session context, and first-day gotchas.

Est. 85 min Updated Suggest a fix

Quick answer

Snowflake objects nest as account → database → schema → table, while warehouses sit beside that tree as account-level compute. Each session has a current role, warehouse, database and schema, and unqualified names resolve against them.

  • Set AUTO_SUSPEND and AUTO_RESUME on every warehouse.
  • USE ROLE, USE WAREHOUSE, USE DATABASE, USE SCHEMA set the context.
  • A query fails without a running or resumable warehouse.
Create the basics and set the session context
CREATE WAREHOUSE dev_wh WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE freshcart;
CREATE SCHEMA freshcart.raw;

USE WAREHOUSE dev_wh;
USE SCHEMA freshcart.raw;
CREATE TABLE orders (order_id NUMBER, order_date DATE, total_amount NUMBER(10, 2));
// Part 01 — The object hierarchy

Account, Database, Schema, Table — and Warehouses Off to the Side

Every object you create in Snowflake lives inside a strict containment hierarchy: an account holds databases, a database holds schemas, and a schema holds tables, views, stages, file formats, streams, tasks, and most other objects. This is close to what you'd expect from PostgreSQL or SQL Server, where a database contains schemas and schemas contain tables.

What trips up beginners is that warehouses are not part of this hierarchy at all. A warehouse is a separate, account-level compute resource. It does not belong to a database or a schema, and it does not "contain" anything. It is simply the compute engine that executes your SQL. You pick a warehouse to run a query, independent of which database or schema you are querying. This separation of storage (the account/database/schema/table hierarchy) from compute (warehouses) is the single most important architectural idea to internalize before writing your first query.

The hierarchy, drawn out
ACCOUNT
  │
  ├── DATABASE  (e.g. LEARN_SNOWFLAKE)
  │     │
  │     ├── SCHEMA  (e.g. RAW)
  │     │     ├── TABLE
  │     │     ├── VIEW
  │     │     ├── STAGE
  │     │     ├── FILE FORMAT
  │     │     ├── STREAM
  │     │     └── TASK
  │     │
  │     └── SCHEMA  (e.g. SILVER, GOLD, ...)
  │
  └── DATABASE  (more databases...)

WAREHOUSE  (e.g. WH_LEARN_XS)  <- lives beside the hierarchy above,
                                   not inside it. Pure compute.

Plain-English definition: the database/schema/table tree is where your data lives. A warehouse is what you rent to go get it, transform it, or write more of it. You can point the same warehouse at ten different databases, and you can query the same database with ten different warehouses. They are independent choices.

ObjectWhat it holdsAnalogy
AccountAll databases and all warehouses for your organization.The whole building.
DatabaseA set of related schemas.A floor in the building.
SchemaA set of related tables/views/stages/etc.A room on that floor.
TableRows of data.A filing cabinet in the room.
WarehouseCompute that runs your SQL against any of the above.A crew of movers you hire by the hour.
// Part 02 — Creating a warehouse

CREATE WAREHOUSE and the Options That Actually Matter

A warehouse is created once and then reused across sessions and queries. The options you set at creation time control how much it costs to run and how forgiving it is when idle.

Create a warehouse with real options
CREATE WAREHOUSE IF NOT EXISTS WH_LEARN_XS
  WAREHOUSE_SIZE      = XSMALL
  AUTO_SUSPEND        = 60
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT             = 'Small warehouse for learning and light exploration';
OptionWhat it controlsBeginner default
WAREHOUSE_SIZENumber of servers in the compute cluster (XSMALL, SMALL, MEDIUM, LARGE, ...). Bigger sizes cost more credits per second but run individual queries faster.XSMALL for learning; size up only when a query is genuinely slow.
AUTO_SUSPENDSeconds of inactivity before Snowflake automatically suspends (stops billing) the warehouse.60 seconds. Short suspend windows keep idle costs near zero.
AUTO_RESUMEWhether the warehouse automatically starts again the next time a query needs it.TRUE. Otherwise every query fails until someone manually resumes it.
INITIALLY_SUSPENDEDWhether the warehouse starts in a suspended (not running) state right after creation.TRUE. No reason to pay for compute before you run your first query.
Cost safety
Snowflake bills warehouse compute by the second while a warehouse is running, based on its size. A warehouse left running with a long or disabled auto-suspend is the single most common way beginners rack up unexpected Snowflake bills. Always set AUTO_SUSPEND explicitly rather than trusting defaults.

You can change any of these settings later with ALTER WAREHOUSE, and you can manually suspend or resume:

Alter and manually control a warehouse
ALTER WAREHOUSE WH_LEARN_XS SET WAREHOUSE_SIZE = SMALL;
ALTER WAREHOUSE WH_LEARN_XS SUSPEND;
ALTER WAREHOUSE WH_LEARN_XS RESUME;

SHOW WAREHOUSES LIKE 'WH_LEARN_XS';
// Part 03 — Creating databases and schemas

CREATE DATABASE and CREATE SCHEMA

Databases and schemas are cheap and fast to create — they are metadata, not compute, so they don't need a running warehouse. A common beginner pattern is to create one learning database with a few schemas that represent stages of data trustworthiness: raw, cleaned, and business-ready.

Database and schema setup
CREATE DATABASE IF NOT EXISTS LEARN_SNOWFLAKE
  COMMENT = 'Sandbox database for learning Snowflake SQL';

CREATE SCHEMA IF NOT EXISTS LEARN_SNOWFLAKE.RAW;
CREATE SCHEMA IF NOT EXISTS LEARN_SNOWFLAKE.SILVER;
CREATE SCHEMA IF NOT EXISTS LEARN_SNOWFLAKE.GOLD;

SHOW SCHEMAS IN DATABASE LEARN_SNOWFLAKE;

Note the naming pattern: DATABASE.SCHEMA.TABLE is the fully qualified name of any table. You'll see this three-part naming constantly — in queries, in COPY INTO statements, and in error messages when Snowflake can't find an object because your session context points somewhere else.

// Part 04 — Roles, just enough to get moving

USE ROLE: Enough to Run These Commands

Every action in Snowflake runs under a role, and the role determines what you are permitted to do.SYSADMIN is the conventional role for creating warehouses, databases, and schemas in a learning environment or as an account owner setting things up. ACCOUNTADMIN has the broadest privileges and is reserved for account-level administration, not day-to-day object creation.

Set your role before creating objects
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS WH_LEARN_XS
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;
This is not the whole roles story
This lesson only covers enough about roles to run CREATE WAREHOUSE,CREATE DATABASE, and basic DML. Grants, custom roles, the role hierarchy, and least-privilege design are covered in full in the dedicated roles and security basics module later in this track. Don't try to learn access control from this page.
// Part 05 — Session context: USE WAREHOUSE/DATABASE/SCHEMA/ROLE

Every Session Has Implicit Context — Set It Explicitly

A Snowflake session tracks four pieces of context at all times: current role, current warehouse, current database, and current schema. Any query you run without a fully qualified name resolves against that context. If the context is wrong — or unset — your query either fails or, worse, runs successfully against the wrong objects.

Set full session context
USE ROLE SYSADMIN;
USE WAREHOUSE WH_LEARN_XS;
USE DATABASE LEARN_SNOWFLAKE;
USE SCHEMA RAW;

-- Now this resolves to LEARN_SNOWFLAKE.RAW.ORDERS
SELECT * FROM ORDERS;

-- Check current context at any time:
SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();
ContextSet withWhat breaks if unset or wrong
RoleUSE ROLE ...'Insufficient privileges' errors, or objects that exist but you can't see them.
WarehouseUSE WAREHOUSE ...'No active warehouse' error — queries that need compute simply refuse to run.
DatabaseUSE DATABASE ...Unqualified table names resolve to the wrong database, or fail to resolve at all.
SchemaUSE SCHEMA ...Same table name in a different schema gets queried by mistake.

Mental model: think of session context as four sticky notes on your monitor — role, warehouse, database, schema. Every unqualified SQL statement reads those sticky notes first. Scripts that don't set them explicitly are relying on whatever context happened to be left over from your last session, which is a common source of "it worked yesterday" bugs.

// Part 06 — Creating tables with real Snowflake types

CREATE OR REPLACE TABLE and Snowflake's Data Types

Snowflake's type system covers the same ground as most SQL databases, with a few names worth learning precisely because they differ from Postgres or MySQL conventions.

Create a table with common types
CREATE OR REPLACE TABLE RAW.ORDERS (
  order_id      STRING,
  customer_id   STRING,
  order_ts      TIMESTAMP_NTZ,
  placed_at_utc TIMESTAMP_TZ,
  status        STRING,
  order_amount  NUMBER(10,2),
  is_test_order BOOLEAN DEFAULT FALSE,
  updated_at    TIMESTAMP_NTZ
);
TypeMeaningNotes
NUMBER(precision, scale)Exact fixed-point numeric.NUMBER(10,2) is a common shape for money — 10 total digits, 2 after the decimal.
STRING / VARCHARVariable-length text.Snowflake treats STRING and VARCHAR as the same type; there is no meaningful length penalty for over-sizing.
TIMESTAMP_NTZTimestamp with no timezone attached ("naive" / wall-clock time).Good default when your whole pipeline agrees on one timezone, usually UTC, by convention.
TIMESTAMP_TZTimestamp with an explicit timezone offset stored per value.Use when values arrive from multiple timezones and you need the offset preserved.
BOOLEANTrue/false/null.Straightforward — no surprises here.
VARIANTSemi-structured data (JSON-like) stored natively.Only a brief mention here — VARIANT gets a full module later covering JSON parsing, dot notation, and FLATTEN.
Don't over-invest in VARIANT yet
You will see VARIANT columns in later lessons when loading JSON. For this lesson, just recognize the type exists and holds semi-structured data — the deep dive on querying it comes with the semi-structured data module.
// Part 07 — Basic DML: INSERT

INSERT Rows the Standard SQL Way

Snowflake's DML is close to standard ANSI SQL, so if you know PostgreSQL or MySQL, most of this will feel immediately familiar.

Insert literal rows
INSERT INTO RAW.ORDERS (order_id, customer_id, order_ts, placed_at_utc, status, order_amount, updated_at) VALUES
  ('O-1001', 'C-10', '2026-09-01 10:15:00', '2026-09-01 10:15:00 +00:00', 'PLACED',    129.99, CURRENT_TIMESTAMP()),
  ('O-1002', 'C-11', '2026-09-01 11:20:00', '2026-09-01 11:20:00 +00:00', 'PLACED',     59.50, CURRENT_TIMESTAMP()),
  ('O-1003', 'C-10', '2026-09-02 09:05:00', '2026-09-02 09:05:00 +00:00', 'CANCELLED',  35.00, CURRENT_TIMESTAMP()),
  ('O-1004', 'C-12', '2026-09-02 12:30:00', '2026-09-02 12:30:00 +00:00', 'PLACED',    210.00, CURRENT_TIMESTAMP());

You can also insert from a SELECT, which is the standard way to populate one table from another — you'll see this constantly when building Raw → Silver → Gold layers:

Insert from a SELECT
CREATE OR REPLACE TABLE RAW.ORDERS_BACKUP LIKE RAW.ORDERS;

INSERT INTO RAW.ORDERS_BACKUP
SELECT * FROM RAW.ORDERS
WHERE status = 'PLACED';
// Part 08 — Basic DML: SELECT

SELECT, WHERE, GROUP BY, and QUALIFY

Standard SELECT clauses behave as expected. One feature is especially worth learning early because it is distinctly Snowflake-friendly: QUALIFY, which filters on the result of a window function without needing a wrapping CTE or subquery.

Aggregate revenue
SELECT
  DATE_TRUNC('day', order_ts)::DATE AS order_date,
  COUNT(*) AS order_count,
  SUM(order_amount) AS gross_revenue
FROM RAW.ORDERS
WHERE status = 'PLACED'
GROUP BY 1
ORDER BY order_date;
Deduplicate with QUALIFY
-- Keep only the latest row per order_id.
SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  order_amount,
  updated_at
FROM RAW.ORDERS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC
) = 1;

Why QUALIFY matters: in PostgreSQL or MySQL you typically wrap aROW_NUMBER() query in a CTE or subquery just so you can filter on it in an outerWHERE. Snowflake's QUALIFY clause filters directly on window function results in the same query — shorter, and the intent ("keep exactly one row per group") reads more clearly.

// Part 09 — Basic DML: UPDATE and DELETE

UPDATE and DELETE Work as You'd Expect

Update and delete
UPDATE RAW.ORDERS
SET status = 'REFUNDED', updated_at = CURRENT_TIMESTAMP()
WHERE order_id = 'O-1003';

DELETE FROM RAW.ORDERS
WHERE status = 'CANCELLED'
  AND order_ts < DATEADD(day, -90, CURRENT_DATE());
Snowflake does not enforce PRIMARY KEY uniqueness
You can declare PRIMARY KEY and UNIQUE constraints in Snowflake, and they are useful as documentation and for some query optimizations, but Snowflake does not enforce them by default the way PostgreSQL does. An INSERT that violates a declared primary key will succeed and create duplicate rows. If you need real uniqueness guarantees, enforce them in your load logic (dedupe with QUALIFY, or use MERGE) — don't rely on the constraint alone.
// Part 10 — Schemas as trust layers

Use Schemas to Organize Raw, Silver, and Gold

A schema is not only a technical namespace. In analytics engineering, schemas often represent trust levels. Raw tables stay close to source data. Silver tables are cleaned and deduplicated. Gold tables are business-facing marts and aggregates that dashboards and stakeholders query directly.

LayerWhat lives thereWho queries it directly
RawSource-shaped data, minimal transformation, may contain duplicates or bad rows.Data engineers debugging ingestion issues.
SilverCleaned, deduplicated, typed, joined to reference data.Analytics engineers building models; rarely end users.
GoldBusiness-defined aggregates and marts with agreed-upon definitions.Dashboards, BI tools, business stakeholders.

This is a convention, not a Snowflake-enforced feature — nothing stops you from naming schemas differently or skipping the pattern for a small project. But it scales well: it gives every table an implicit answer to "how much do I trust this data," and it keeps in-progress cleanup work from leaking into dashboards people rely on.

Layered tables across schemas
CREATE OR REPLACE TABLE SILVER.ORDERS AS
SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  order_amount,
  updated_at
FROM RAW.ORDERS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC
) = 1;

CREATE OR REPLACE TABLE GOLD.DAILY_REVENUE AS
SELECT
  DATE_TRUNC('day', order_ts)::DATE AS order_date,
  COUNT(*) AS placed_orders,
  SUM(order_amount) AS gross_revenue
FROM SILVER.ORDERS
WHERE status = 'PLACED'
GROUP BY 1;
// Part 11 — ALTER, DROP, and cloning tables

Changing Tables Safely: ALTER, DROP, and Zero-Copy Cloning

Beyond CREATE OR REPLACE, you'll frequently need to change a table's shape without losing its data, or make a throwaway copy to test something risky. Snowflake's ALTER TABLE covers the first case, and its zero-copy CLONE feature covers the second — cheaply, because a clone shares the underlying storage with its source until either side changes data.

Adding and modifying columns without losing data
ALTER TABLE RAW.ORDERS ADD COLUMN channel STRING DEFAULT 'web';

ALTER TABLE RAW.ORDERS RENAME COLUMN channel TO sales_channel;

ALTER TABLE RAW.ORDERS ALTER COLUMN order_amount SET DATA TYPE NUMBER(12,2);

ALTER TABLE RAW.ORDERS DROP COLUMN is_test_order;
Zero-copy clone for safe experimentation
-- Instant, storage-cheap copy - safe to experiment on:
CREATE OR REPLACE TABLE RAW.ORDERS_EXPERIMENT
  CLONE RAW.ORDERS;

-- Try something destructive against the clone, not the real table:
DELETE FROM RAW.ORDERS_EXPERIMENT WHERE status = 'CANCELLED';

-- Drop the clone when you're done - no impact on RAW.ORDERS:
DROP TABLE IF EXISTS RAW.ORDERS_EXPERIMENT;
CREATE OR REPLACE vs ALTER TABLE
CREATE OR REPLACE TABLE fully drops and recreates the object — every row, every grant, and every dependent view or stream reference is gone and rebuilt from scratch. Use ALTER TABLE when you want to change shape while keeping existing data and grants intact. Reach for CREATE OR REPLACE only when you genuinely want a clean slate.
// Part 12 — Multi-statement transactions

Snowflake Auto-Commits by Default — Use Explicit Transactions When Steps Must Stay Together

By default, each DML statement in Snowflake commits immediately once it completes. That's fine for a single INSERT or UPDATE, but if you need several statements to succeed or fail together — for example, moving money between two rows, or updating a table and an audit log in lockstep — wrap them in an explicit transaction.

Explicit transaction
BEGIN;

UPDATE RAW.ORDERS
SET status = 'REFUNDED'
WHERE order_id = 'O-1004';

INSERT INTO RAW.ORDER_AUDIT (order_id, action, actioned_at)
VALUES ('O-1004', 'REFUND', CURRENT_TIMESTAMP());

COMMIT;

-- If anything looked wrong before COMMIT, you could instead run:
-- ROLLBACK;

Transactions matter less for simple analytical SELECTs and more once you start writing pipelines that touch multiple tables per logical unit of work — you'll see this pattern again when stored procedures and multi-step MERGE pipelines come up later in the track.

// Part 12b — SHOW, DESCRIBE, and self-discovery

Finding Your Way Around With SHOW and DESCRIBE

Before you memorize an environment's layout, you need commands that let Snowflake tell you what exists. SHOW lists objects of a given kind, and DESCRIBE (or its shorthand DESC) reveals the structure of one specific object. These are the commands you'll run constantly while debugging "why can't I find this table" issues.

Discovering what exists
SHOW DATABASES;
SHOW SCHEMAS IN DATABASE LEARN_SNOWFLAKE;
SHOW TABLES IN SCHEMA LEARN_SNOWFLAKE.RAW;
SHOW WAREHOUSES;

DESCRIBE TABLE LEARN_SNOWFLAKE.RAW.ORDERS;

-- Also useful: query the account-wide metadata views directly
SELECT table_catalog, table_schema, table_name, row_count, bytes
FROM LEARN_SNOWFLAKE.INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'RAW';
CommandAnswers
SHOW DATABASES / SCHEMAS / TABLES / WAREHOUSES"What objects of this kind exist, and where?"
DESCRIBE TABLE <name>"What columns does this table have, and what are their types?"
INFORMATION_SCHEMA.TABLES"Give me this as a queryable result set I can filter, join, or script against."
Cheap habit, real payoff
Running a quick SHOW or DESCRIBE before writing a query against an unfamiliar table takes seconds and avoids guessing column names — especially useful combined with Part 14's case-sensitivity rules, since DESCRIBE shows you exactly how each column name is actually stored.
// Part 13 — Ways to run this SQL

Snowsight, SnowSQL, and Connectors

None of the SQL above changes depending on how you connect — Snowflake exposes the same SQL surface regardless of client. What changes is convenience, scriptability, and where the work fits in your workflow.

ToolWhat it isBest for
SnowsightSnowflake's built-in browser-based web UI.Exploration, ad-hoc queries, dashboards, worksheet history, reviewing query profiles visually.
SnowSQLA command-line client (CLI) you install locally.Scripting, automation, uploading local files to stages with PUT, CI/CD pipelines.
Connectors / driversPython connector, JDBC, ODBC, Node driver, etc.Application code, orchestration tools (Airflow, dbt), BI tools, custom pipelines.

As a beginner, Snowsight is the fastest way to run everything in this lesson. You'll reach for SnowSQL specifically once you need to PUT local files onto an internal stage — covered in the next module on loading data.

// Part 14 — Identifier case-sensitivity

Unquoted Identifiers Are Uppercased — The #1 Beginner Confusion

This single rule causes more first-week confusion than anything else in Snowflake SQL. When you write an identifier — a table name, column name, database name — without double quotes, Snowflake automatically uppercases it before storing or matching it. Quoted identifiers are stored exactly as written and become case-sensitive.

Unquoted vs quoted identifiers
-- These three statements all create/reference the SAME object,
-- because unquoted identifiers are folded to uppercase:
CREATE TABLE raw.orders (id STRING);
CREATE TABLE RAW.ORDERS (id STRING);   -- same object as above
CREATE TABLE Raw.Orders (id STRING);   -- same object as above

-- Quoting freezes the exact case and makes it case-sensitive:
CREATE TABLE "raw"."my_special_table" (id STRING);

-- This will FAIL to find the table above unless you quote it exactly:
SELECT * FROM raw.my_special_table;      -- looks for RAW.MY_SPECIAL_TABLE, not found

-- This works, because it matches the quoted case exactly:
SELECT * FROM "raw"."my_special_table";
StyleExampleBehavior
Unquotedorders, Orders, ORDERSAll fold to ORDERS. Case-insensitive to write, but stored/compared as uppercase.
Quoted"orders"Stored and matched exactly as "orders" (lowercase). Must be quoted identically every time it is referenced.
Practical rule
Avoid quoted lowercase identifiers unless you have a specific reason (e.g. matching an external system's exact casing). Stick to unquoted identifiers everywhere, write them in whatever case is readable to you, and let Snowflake fold them to uppercase consistently. Mixing quoted and unquoted styles for the same objects is one of the most common sources of "table not found" errors for newcomers.
// Part 15 — Other first-day gotchas

Common Setup and SQL Mistakes

  • ✓Running a query with no warehouse selected — Snowflake returns "No active warehouse selected" instead of silently picking one for you.
  • ✓Leaving learning warehouses running instead of relying on AUTO_SUSPEND.
  • ✓Using SELECT * in production models where explicit column lists protect you from upstream schema drift.
  • ✓Assuming PRIMARY KEY declarations enforce uniqueness the way PostgreSQL does — they do not, by default.
  • ✓Putting raw, cleaned, and business-facing tables in one messy schema instead of separating by trust layer.
  • ✓Using TIMESTAMP_NTZ without a team convention for which timezone "naive" values represent.
  • ✓Mixing quoted and unquoted identifiers for the same object and getting inconsistent case-sensitivity.
  • ✓Forgetting that CREATE OR REPLACE TABLE fully drops and recreates the table, losing all existing rows and grants.
Production habit
At the top of every script or session, be explicit about role, warehouse, database, and schema. Invisible, inherited session context is convenient right up until a script runs against the wrong environment.
// Part 16 — Hands-on lab

Hands-On Lab: Stand Up a Full Environment From Scratch

This lab walks through everything in this lesson in order: role, warehouse, database, schemas, a table, DML, and a check of session context.

Lab: full setup
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS WH_SETUP_LAB_XS
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS SETUP_LAB;
CREATE SCHEMA IF NOT EXISTS SETUP_LAB.RAW;
CREATE SCHEMA IF NOT EXISTS SETUP_LAB.SILVER;

USE WAREHOUSE WH_SETUP_LAB_XS;
USE DATABASE SETUP_LAB;
USE SCHEMA RAW;

CREATE OR REPLACE TABLE CUSTOMERS (
  customer_id STRING,
  full_name   STRING,
  signup_ts   TIMESTAMP_NTZ,
  is_active   BOOLEAN DEFAULT TRUE
);

INSERT INTO CUSTOMERS VALUES
  ('C-1', 'Sarah Bennett', '2026-01-05 09:00:00', TRUE),
  ('C-2', 'Marcus Reed', '2026-02-11 14:30:00', TRUE),
  ('C-3', 'Old Account', '2024-06-01 08:00:00', FALSE);
Lab: query, update, verify context
SELECT customer_id, full_name
FROM CUSTOMERS
WHERE is_active = TRUE
ORDER BY signup_ts;

UPDATE CUSTOMERS
SET is_active = FALSE
WHERE customer_id = 'C-2';

SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

Lab questions

  • ✓If you ran the CREATE TABLE statement before running USE SCHEMA RAW, where would the table land?
  • ✓What would SELECT * FROM Customers (unquoted, mixed case) return, and why?
  • ✓What happens if you run a SELECT against SETUP_LAB.RAW.CUSTOMERS before ever running USE WAREHOUSE?
  • ✓How would you rewrite the CUSTOMERS table creation so full_name is quoted as "Full_Name" — and what would querying it look like afterward?
  • ✓Why does CREATE OR REPLACE TABLE risk data loss compared to ALTER TABLE ADD COLUMN?
// Part 17 — Interview answer

How to Explain Snowflake Setup and SQL Basics in an Interview

A strong answer sounds like this: Snowflake separates storage from compute. Data lives in a strict account → database → schema → table hierarchy, while warehouses are independent, account-level compute resources you attach to a session — they don't belong to any database or schema. To get started I'd set session context explicitly with USE ROLE, USE WAREHOUSE, USE DATABASE, and USE SCHEMA rather than relying on inherited defaults, because unqualified queries resolve against that context and wrong context is a common source of bugs. Snowflake's DML — INSERT, SELECT, UPDATE, DELETE — is close to standard ANSI SQL, with QUALIFY as a notably Snowflake-friendly way to filter window function results without a wrapping CTE. One of the most common beginner mistakes is identifier case-sensitivity: unquoted identifiers are folded to uppercase automatically, while quoted identifiers are stored exactly as written and become case-sensitive, which causes confusing "object not found" errors until you internalize the rule. I'd also set AUTO_SUSPEND and AUTO_RESUME on every warehouse to avoid paying for idle compute.

Questions you should answer out loud

  • ✓Where do warehouses sit relative to the database/schema/table hierarchy?
  • ✓What does AUTO_SUSPEND control, and why does it matter for cost?
  • ✓What are the four pieces of session context, and how do you set them?
  • ✓What does QUALIFY do that a plain WHERE clause cannot?
  • ✓Does Snowflake enforce PRIMARY KEY uniqueness by default?
  • ✓What happens to an identifier's case if you don't quote it?
  • ✓What is the difference between TIMESTAMP_NTZ and TIMESTAMP_TZ?
  • ✓What are the three main ways to run SQL against Snowflake, and when would you use each?

🎯 Key Takeaways

  • ✓Snowflake's hierarchy is account → database → schema → table; warehouses are separate, account-level compute that sits outside that tree.
  • ✓CREATE WAREHOUSE options that matter most for cost are WAREHOUSE_SIZE, AUTO_SUSPEND, AUTO_RESUME, and INITIALLY_SUSPENDED.
  • ✓A session tracks four pieces of context — role, warehouse, database, schema — and unqualified SQL resolves against them.
  • ✓Snowflake DML (INSERT/SELECT/UPDATE/DELETE) is close to ANSI SQL; QUALIFY is the standout Snowflake-friendly extension for filtering window functions.
  • ✓Snowflake does not enforce PRIMARY KEY/UNIQUE constraints by default — dedupe logic is your responsibility.
  • ✓Unquoted identifiers are automatically uppercased; quoted identifiers are stored and matched exactly as written and become case-sensitive — this is the most common first-week gotcha.
  • ✓Snowsight, SnowSQL, and connectors/drivers all expose the same SQL surface — they differ in workflow fit, not in what SQL you can run.
  • ✓Raw, Silver, and Gold schemas are a common, useful convention for organizing data by trust level, not just by topic.

Quick check

Where do warehouses sit in Snowflake's object hierarchy?

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...