XenorsSnowflake Data Warehouse Tutorial for Data Science Teams
Snowflake separates storage from compute, so analysts, pipelines and model training can each use their own compute without slowing each other down.
Snowflake architecture
Storage holds compressed data in cloud object storage. Virtual warehouses are independent compute clusters that you start, resize and suspend. A cloud services layer handles security, metadata and query optimisation.
| Layer | Role |
|---|---|
| Storage | Central database storage |
| Virtual warehouse | Query compute, billed while running |
| Cloud services | Authentication, metadata, optimiser |
Create objects and load data
Auto-suspend stops billing when nobody is querying.
CREATE WAREHOUSE IF NOT EXISTS analytics_wh
WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS shop;
CREATE OR REPLACE TABLE shop.public.orders (
order_id INT, customer_id INT, order_date DATE, amount NUMBER(10,2));
CREATE OR REPLACE STAGE shop.public.raw_stage;
-- upload with SnowSQL: PUT file://orders.csv @shop.public.raw_stage;
COPY INTO shop.public.orders
FROM @shop.public.raw_stage/orders.csv
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);Analyse with SQL
Window functions and QUALIFY keep analysis queries short.
SELECT customer_id,
SUM(amount) AS lifetime_value,
COUNT(*) AS orders,
DATEDIFF('day', MIN(order_date), MAX(order_date)) AS active_days
FROM shop.public.orders
GROUP BY customer_id
QUALIFY RANK() OVER (ORDER BY SUM(amount) DESC) <= 10;Query from Python
Keep credentials in environment variables or a secrets manager.
# pip install "snowflake-connector-python[pandas]"
import os, snowflake.connector
conn = snowflake.connector.connect(
account=os.environ["SF_ACCOUNT"], user=os.environ["SF_USER"],
password=os.environ["SF_PASSWORD"], warehouse="analytics_wh",
database="shop", schema="public")
df = conn.cursor().execute(
"SELECT order_date, SUM(amount) AS revenue FROM orders GROUP BY 1 ORDER BY 1"
).fetch_pandas_all()
print(df.head())
conn.close()Snowpark and cost control
Snowpark lets you write DataFrame code in Python that runs inside Snowflake, so large data does not have to leave the warehouse. To control cost: use the smallest warehouse that meets your latency, set AUTO_SUSPEND to a low value, add resource monitors with credit limits, and avoid SELECT * on wide tables.
Hands-on practice
Run the examples in the downloadable Jupyter notebook. The extra checks below focus on mistakes that appear in real projects, not just syntax.
Create a small warehouse and table
CREATE WAREHOUSE IF NOT EXISTS ds_wh WAREHOUSE_SIZE='XSMALL' AUTO_SUSPEND=60;
CREATE TABLE IF NOT EXISTS sales(order_id INT, amount NUMBER(10,2));
INSERT INTO sales VALUES (1,120.50),(2,75.00);
SELECT COUNT(*) AS rows, SUM(amount) AS revenue FROM sales;ROWS | REVENUE 2 | 195.50
Auto-suspend is an easy first control for warehouse cost.
Check pruning-friendly filters
SELECT *
FROM events
WHERE event_date BETWEEN '2026-10-01' AND '2026-10-07';Query profile should show reduced partitions scanned.
Use the query profile to verify pruning instead of assuming a filter is cheap.
Self-check
- Can you explain why the first practice result is trustworthy rather than just plausible?
- What would you monitor or test before using this Snowflake Warehouse workflow in production?
- Which assumption in the example would break first on a larger or messier dataset?
Frequently asked questions
Is Snowflake a database or a data warehouse?
It is a cloud data platform used mainly as a data warehouse, with support for semi-structured data and data science workloads.
What is a virtual warehouse?
A named compute cluster that runs queries and loads. You can resize it or suspend it independently of stored data.
Can I use Snowflake for machine learning?
Yes. You can pull features into Python, or use Snowpark and Snowflake's ML features to work inside the platform.
Primary references
Use these official sources when exact syntax, defaults or definitions matter beyond the examples here.
