Snowflake Quick Reference
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.
-- 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?
You May Also Like
Snowflake Best Practices
Practical guidance for sizing warehouses, controlling cost, tuning performance, and governing access in a production Snowflake account.
Building a Data Warehouse Schema
How to design a layered, star-schema Snowflake warehouse with correctly-chosen grain, slowly changing dimensions, and incremental loading.
Snowflake Interview Questions
Common Snowflake interview questions on architecture, Time Travel, cloning, and cost troubleshooting, with the reasoning strong answers demonstrate.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics