Snowflake Quick Reference

Analogy categoryAnalogies

This is a condensed reference for the Snowflake objects, commands, and concepts you reach for daily: virtual warehouses for compute, databases/schemas/tables for storage organization, roles for RBAC, and a handful of core SQL extensions (VARIANT, Streams, Tasks, Time Travel) unique to Snowflake versus a generic ANSI SQL database. Keep this page bookmarked for syntax you use occasionally but don't have memorized.

🏏

Cricket analogy: Like a fielding chart taped to the dugout wall listing each player's position for quick reference during a match, this page is a fast lookup, not a full coaching manual.

Core Objects and Editions

The object hierarchy is Account > Database > Schema > Table/View/Stage/etc., with Warehouses existing as a separate compute-only object not nested under a database. Editions scale up in features and per-credit price: Standard (core features), Enterprise (adds multi-cluster warehouses, longer Time Travel up to 90 days, materialized views), Business Critical (adds HIPAA/PCI compliance, Tri-Secret Secure), and Virtual Private Snowflake (fully isolated deployment for the most regulated customers).

🏏

Cricket analogy: Like a league structure of Board > Zone > State Association > Club, with the stadium (compute) existing separately as a bookable venue, Snowflake's object hierarchy nests storage under Account/Database/Schema while warehouses sit apart.

Frequently Used Commands

Beyond standard SQL, the commands you'll reach for constantly include COPY INTO for bulk loading from a stage, CREATE ... CLONE for zero-copy copies, SELECT ... AT/BEFORE for Time Travel, SHOW/DESCRIBE for metadata inspection, and GRANT/REVOKE for RBAC. The SnowSQL CLI and Snowsight web UI both support these; worksheets in Snowsight additionally give you a visual Query Profile for every executed query, which is the fastest way to spot spilling or poor pruning.

🏏

Cricket analogy: Like a batsman's core shot repertoire — drive, pull, cut, defend — covering most deliveries faced in a match, these commands cover the majority of daily Snowflake work.

sql

-- Bulk load from a stage
COPY INTO raw_orders
FROM @my_stage/orders/
FILE_FORMAT = (TYPE = 'JSON');

-- Zero-copy clone
CREATE TABLE orders_backup CLONE orders;

-- Time Travel query
SELECT * FROM orders BEFORE(STATEMENT => '<query_id>');

-- Metadata inspection
SHOW TABLES IN SCHEMA sales;
DESCRIBE TABLE orders;

-- RBAC grant
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO ROLE analyst;

-- Semi-structured access
SELECT data:customer.name::STRING FROM raw_events;

The context functions CURRENT_WAREHOUSE(), CURRENT_ROLE(), CURRENT_DATABASE(), and CURRENT_SCHEMA() are worth memorizing — they're the fastest way to sanity-check your session context before running a destructive command like DROP or TRUNCATE.

USE WAREHOUSE, USE DATABASE, and USE SCHEMA only change your session's default context — they do not grant access. A missing GRANT will still block a query even if USE succeeds, which is a common source of confusing 'object does not exist' errors for users who actually lack privilege rather than the object being genuinely missing.

Object hierarchy: Account > Database > Schema > Table/View/Stage; Warehouses are a separate compute-only object.
Editions scale up: Standard, Enterprise (multi-cluster, longer Time Travel, materialized views), Business Critical (compliance), VPS (full isolation).
COPY INTO loads from a stage; CREATE ... CLONE makes a zero-copy copy; SELECT ... AT/BEFORE queries historical data.
SHOW and DESCRIBE are the fastest way to inspect metadata without querying INFORMATION_SCHEMA directly.
GRANT/REVOKE manage RBAC; USE WAREHOUSE/DATABASE/SCHEMA only change session context, they don't grant access.
VARIANT columns use colon/dot notation (data:field.subfield::TYPE) to query semi-structured JSON directly.
Snowsight's Query Profile is the fastest way to diagnose spilling, pruning, and warehouse sizing issues for any query.

Practice what you learned

1. Where does a virtual warehouse sit in Snowflake's object hierarchy?

2. What is the effect of running USE WAREHOUSE my_wh without an accompanying GRANT?

3. Which Snowflake edition first introduces multi-cluster warehouses and materialized views?

4. What syntax is used to query a VARIANT column's nested JSON field as a string?

Was this page helpful?

Snowflake Interview Questions

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Programming

PowerShell Study Notes

Programming · 30 topics

Topics covered

#Programming#SnowflakeStudyNotes#SnowflakeQuickReference#Snowflake#Quick#Reference#Core#StudyNotes#SkillVeris#ExamPrep

Snowflake Quick Reference