In Part 3, we manually uploaded a CSV and queried some JSON. That’s fine for a one-off project, but in a real business, data doesn’t arrive once—it flows in constantly. You don’t want to be the person clicking “Upload” at 8:00 AM every morning.
Today, we’re going to look at how Snowflake automates the “Transform” part of your data pipeline using two powerful features: Streams (the “eyes”) and Tasks (the “muscles”).
1. The Stream: Knowing What’s New
If you have a table with 10 million rows and 100 new rows arrive, you don’t want to re-process the whole 10 million. A Stream is an object that sits on top of a table and tracks exactly what has changed since the last time you looked.
Think of it like a bookmark. When you “read” the new data, the bookmark moves to the end.
— Create a stream to watch our raw USERS table
CREATE OR REPLACE STREAM USERS_STREAM ON TABLE MY_FIRST_DB.STAGING.USERS;

— To see what’s “new,” you just query the stream like a table
SELECT * FROM USERS_STREAM;

Note: If the stream is empty, the query returns nothing. As soon as you INSERT or UPDATE the USERS table, the stream “fills up” with those changes.
2. The Task: Putting it on Autopilot
A Task is simply a SQL statement that runs on a schedule. It uses your virtual warehouse to do the heavy lifting.
Let’s say we want to take new users from our STAGING table and move them into a clean ANALYTICS table every 5 minutes.
— First, create our “Clean” destination table
CREATE OR REPLACE TABLE MY_FIRST_DB.ANALYTICS.DIM_USERS (
    USER_ID NUMBER,
    FULL_NAME STRING,
    EMAIL_DOMAIN STRING,
    LOAD_TIME TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

— Now, create the muscle (The Task)
CREATE OR REPLACE TASK REFRESH_USERS_TASK
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = ‘5 MINUTE’
    WHEN SYSTEM$STREAM_HAS_DATA(‘MY_FIRST_DB.STAGING.USERS_STREAM’)
AS
    INSERT INTO MY_FIRST_DB.ANALYTICS.DIM_USERS (USER_ID, FULL_NAME, EMAIL_DOMAIN)
    SELECT
        USER_ID,
        FIRST_NAME || ‘ ‘ || LAST_NAME, — Combining names
        SPLIT_PART(EMAIL, ‘@’, 2)       — Extracting just the domain
    FROM MY_FIRST_DB.STAGING.USERS_STREAM;

3. The “Secret Sauce”: Conditional Execution
Notice the line WHEN SYSTEM$STREAM_HAS_DATA(…). This is one of Snowflake’s best cost-saving features.
If no new data has arrived in the last 5 minutes, the task won’t run. This means you don’t waste credits spinning up a warehouse just to realize there’s nothing to do. Snowflake checks the stream metadata for free.
4. Turning it On
All tasks are created in a “Started” state… just kidding! They are actually created Suspended by default to prevent accidental credit spend. You have to manually flip the switch:
— Start the automation
ALTER TASK REFRESH_USERS_TASK RESUME;

— If you need to stop it:
— ALTER TASK REFRESH_USERS_TASK SUSPEND;

What You Now Know
* Streams track “delta” changes (Inserts, Updates, Deletes) so you only process what’s new.
* Tasks allow you to schedule SQL logic directly inside Snowflake.
* Efficiency: By combining them, you create a “Change Data Capture” (CDC) pipeline that only costs money when there is actually work to be done.
What’s Next?
In Part 5, we’ll wrap up the series by looking at Time Travel. Yes, really. Snowflake allows you to query data as it existed up to 90 days in the past—perfect for when a “muscle-bound” task accidentally deletes something it shouldn’t have!

In Part 2, we explored Snowflake’s built-in sample data and learned how virtual warehouses power our queries. But in the real world, you aren’t just querying someone else’s data—you’re bringing in your own.
Today, we move from “read-only” to “read-write.” We will create our own storage hierarchy, upload a CSV file directly through the browser, and then tackle Snowflake’s “superpower”: the ability to query JSON data as easily as a standard table.
1. Building the Foundation
Before we can load anything, we need a container. Run these in a new worksheet to create your first custom database and schema. Note that we are using the SYSADMIN role here, which is the standard role for creating objects.
USE ROLE SYSADMIN;

CREATE DATABASE MY_FIRST_DB;
CREATE SCHEMA MY_FIRST_DB.STAGING;

— Verify it worked
SHOW SCHEMAS IN DATABASE MY_FIRST_DB;

2. Loading a CSV (The Easy Way)
For files under 250MB, you don’t need a complex pipeline; you can use the Snowsight UI.
First, let’s create a destination table for some imaginary “App Users”:
CREATE OR REPLACE TABLE MY_FIRST_DB.STAGING.USERS (
    USER_ID NUMBER,
    FIRST_NAME STRING,
    LAST_NAME STRING,
    EMAIL STRING,
    SIGNUP_DATE DATE
);

To load your data:
* Navigate to Data → Databases in the left sidebar.
* Drill down: MY_FIRST_DB → STAGING → Tables → USERS.
* In the top right, click Load Data.
* Drag and drop your CSV file.
* When prompted for a File Format, you can create a “Default CSV” format in the wizard—just ensure “Header lines to skip” is set to 1 if your CSV has column names.
3. The VARIANT Superpower: Querying JSON
Traditional databases often require you to “flatten” JSON into rows and columns before you can even see it. Snowflake handles this differently using the VARIANT data type, which stores JSON exactly as it is but allows you to query it with SQL speed.
Let’s create a table for raw JSON data:
CREATE OR REPLACE TABLE MY_FIRST_DB.STAGING.RAW_LOGS (
    EVENT_DATA VARIANT
);

If you have a JSON record like {“device”: “mobile”, “event”: “login”, “user_info”: {“id”: 101, “location”: “NY”}}, you can query it directly using dot notation:
SELECT
    EVENT_DATA:device::STRING AS device_type,
    EVENT_DATA:user_info.location::STRING AS city
FROM RAW_LOGS;

The : grabs the key, and the :: casts it to a specific data type.
4. Zero-Copy Cloning: The “Save Game” Feature
One of the most useful features for beginners is Cloning. Suppose you are about to run a massive update on your USERS table and want a safety net. You can create an instant “Clone” of the entire table:
CREATE TABLE USERS_BACKUP CLONE USERS;

This doesn’t copy the data (so it doesn’t cost extra storage initially); it just creates a new pointer to the existing data. It’s a perfect “Save Game” point for your data.
What You Now Know
* Object Creation: You know how to build your own Databases and Schemas.
* Web Loading: You can move data from your local machine into the cloud without external ETL tools.
* Semi-Structured Power: You’ve seen how the VARIANT type allows Snowflake to act like a NoSQL database when needed.
* Cloning: You understand how to protect your work with instant, metadata-only clones.
What’s Next?
Now that you have data in Snowflake, how do you keep it clean automatically? In Part 4, we will dive into Tasks and Streams—Snowflake’s built-in automation tools that allow you to transform data the second it arrives.

In Part 1, we signed up for a Snowflake account and took a tour of the Snowsight interface. In this post, we’ll write our first real SQL queries against Snowflake’s built-in sample data, and we’ll pull back the curtain on how virtual warehouses work — including why they matter for your credit balance.
The Three-Tier Data Model
Before we write a single line of SQL, it’s worth spending a moment on how Snowflake organizes data. Everything lives inside a three-level hierarchy:
Database → Schema → Table
A database is the top-level container. Think of it as a folder for an entire project or domain — you might have a database called MARKETING, another called FINANCE, and so on.
A schema is a namespace inside a database. It groups related objects — tables, views, functions — that belong together. A common pattern is to have a RAW schema for unprocessed incoming data and a ANALYTICS schema for the cleaned, transformed version. Schemas also serve an access control purpose: you can grant users permission to one schema without exposing another.
A table is what you’d expect: rows and columns. Nothing exotic here.
When you reference an object in SQL, you can use its fully-qualified name in the form DATABASE.SCHEMA.TABLE, like this:
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
Or, if you’ve already told Snowflake which database and schema you’re working in, you can refer to the table by name alone. You tell Snowflake your context using USE statements:
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

— Now this works without the full path:
SELECT * FROM ORDERS;
Both approaches work. Fully-qualified names are more explicit and less likely to surprise you; USE statements are more convenient for interactive exploration. For now, we’ll use fully-qualified names so it’s always obvious what we’re querying.
The Sample Data: What’s Already There
Every new Snowflake trial account comes pre-loaded with a shared database called SNOWFLAKE_SAMPLE_DATA. This database doesn’t consume any of your storage quota — it’s a live read-only connection to data hosted by Snowflake itself. You can run queries on it, but you can’t insert, update, or delete rows (which is fine for learning).
To explore what’s inside, navigate to Data → Databases in the left navigation bar. Click on SNOWFLAKE_SAMPLE_DATA to expand it, and you’ll see several schemas. The one we’ll use today is TPCH_SF1.
TPC-H is an industry-standard benchmark dataset that models a wholesale supplier’s business — orders, customers, products, suppliers, and shipping. It’s been used to benchmark databases for decades, which means it’s realistic enough to practice real analytical thinking on. The SF1 suffix stands for “scale factor 1,” meaning it contains a base-sized dataset with several million rows — large enough to be interesting, small enough to query without burning through credits.
The TPCH_SF1 schema contains eight tables:
Table
What it represents
ORDERS
Order headers — one row per order
LINEITEM
Individual line items within orders — the largest table at ~6 million rows
CUSTOMER
Customer records
SUPPLIER
Supplier records
PART
Product/part catalog
PARTSUPP
Which suppliers carry which parts, and at what price
NATION
Countries (25 rows)
REGION
Geographic regions (5 rows)
Opening a Worksheet
Navigate to Projects → Worksheets in the left nav and click the + button in the top-right corner to create a new worksheet. A blank SQL editor opens.
You’ll notice two dropdowns near the top of the editor: one for the active database and one for the active schema. These are the worksheet-level equivalents of USE DATABASE and USE SCHEMA. Setting them here means every query in this worksheet defaults to that context without needing fully-qualified names.
For now, leave them as-is and we’ll use fully-qualified names in our queries. This keeps things unambiguous.
There’s also a warehouse selector — a dropdown that shows which virtual warehouse will execute your queries. If your account came with a default warehouse (often named COMPUTE_WH), it will be pre-selected. If the dropdown is empty, we’ll address that in the warehouse section below.
Running Your First Query
Type the following into the worksheet editor:
SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM;
To run it, look for the Run button — it appears as a blue play button (▶) in the top-right area of the editor. Click it.
Important note about running queries: The Run button (or its equivalent button in the toolbar) executes only the SQL statement your cursor is currently positioned within — not everything in the worksheet. If you have five statements in a worksheet and want to run all of them in sequence, look for a small dropdown arrow next to the Run button and choose Run All. This distinction trips up nearly everyone at least once, so it’s worth knowing before you spend time wondering why only one thing happened.
After a moment, the results panel will appear below the editor showing a single number: around 6,001,215. That’s how many line items exist in this table. Not bad for a first query.
A Few More Queries to Try
Let’s do something a bit more interesting. Paste each of these in and run them one at a time — position your cursor inside the statement you want to run, then click Run.
How many orders were placed by customers in each region?
SELECT
    r.R_NAME        AS region,
    COUNT(o.O_ORDERKEY) AS total_orders
FROM
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS      o
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER  c ON o.O_CUSTKEY = c.C_CUSTKEY
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION    n ON c.C_NATIONKEY = n.N_NATIONKEY
    JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION    r ON n.N_REGIONKEY = r.R_REGIONKEY
GROUP BY
    r.R_NAME
ORDER BY
    total_orders DESC;
This query joins five tables together, walking from individual orders all the way up through the geographic hierarchy to regions. You’ll get five rows back — one per region.
What’s the total revenue per order status?
SELECT
    O_ORDERSTATUS                           AS order_status,
    SUM(O_TOTALPRICE)                       AS total_revenue,
    COUNT(*)                                AS order_count,
    ROUND(AVG(O_TOTALPRICE), 2)             AS avg_order_value
FROM
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
GROUP BY
    O_ORDERSTATUS
ORDER BY
    total_revenue DESC;
Notice how fast these queries return. That’s not magic — it’s the combination of Snowflake’s columnar storage format and the micro-partitioning architecture that automatically indexes data behind the scenes. You didn’t create a single index. Snowflake handled that.
Previewing a Table Without Writing SQL
One of Snowsight’s small conveniences: you don’t always need to write SQL to see what’s in a table. In the left navigation, go to Data → Databases, drill down to SNOWFLAKE_SAMPLE_DATA → TPCH_SF1, and click on any table name — say, CUSTOMER. A panel opens showing the table’s column definitions, their data types, and a Data Preview tab that shows you the first rows without needing a query at all.
This is useful when you’re exploring an unfamiliar schema and want to understand what a table contains before writing against it.
Understanding Virtual Warehouses
Now that you’ve run a few queries, let’s talk about what was actually doing the work — the virtual warehouse.
In Snowflake, a virtual warehouse is a cluster of compute resources (CPU, memory, and local SSD cache) that executes your SQL. It’s completely separate from storage. Storage holds your data permanently; the warehouse is a temporary, rentable engine that reads from that storage and processes your queries.
This separation is why Snowflake can do something traditional databases can’t: you can have multiple independent warehouses all querying the same data at the same time, with no interference between them. One warehouse running an ETL job doesn’t slow down another warehouse running your BI dashboards.
Sizes and Credits
Warehouses come in T-shirt sizes. Each step up doubles the compute resources — and doubles the credit consumption rate:
Size
Credits per hour
X-Small
1
Small
2
Medium
4
Large
8
X-Large
16
2X-Large
32
… and up
…
For the queries in this post — counting rows, joining the TPC-H tables — an X-Small warehouse is perfectly sufficient. You’d only need to scale up when you’re regularly querying billions of rows or running complex transformations on large datasets. Starting small and scaling up when queries feel slow is the right approach.
Credits themselves are Snowflake’s billing currency. On a trial account, you start with $400 worth. On a paid account, credits cost roughly $2–$4 each depending on your cloud provider and whether you’re on on-demand or pre-purchased pricing. An X-Small warehouse burning 1 credit per hour therefore costs about $2–$4 per hour of active use.
The 60-Second Minimum — and Why It Matters
Snowflake bills per second, but with a minimum charge of 60 seconds each time a warehouse resumes from a suspended state. This means if you run a query that takes 3 seconds and then the warehouse sits idle long enough to auto-suspend, you’ll be billed for a full 60 seconds of that size warehouse.
For an X-Small warehouse at 1 credit/hour, 60 seconds of use costs 1/60th of a credit — roughly 3 cents at on-demand pricing. Barely noticeable for learning. But this minimum applies at every size, so a 2X-Large warehouse that resumes, runs a 5-second query, and then suspends has still billed you for a full minute at 32 credits/hour. That’s about $1 per query. Worth keeping in mind as you scale up.
Auto-Suspend and Auto-Resume
Two warehouse settings deserve your attention early:
Auto-Suspend tells Snowflake to automatically shut down the warehouse after a period of inactivity. The default is 10 minutes — meaning if you run a query and then walk away, the warehouse will run idle for 10 minutes before Snowflake turns it off. For a learning account, that’s fine. For production, it’s often set to 1–5 minutes to avoid paying for idle compute.
Auto-Resume is the opposite: when a query hits a suspended warehouse, Snowflake automatically starts it back up without any manual intervention. There’s typically a 2–5 second delay while the warehouse provisions — you’ll see a spinner in the worksheet during this time. Auto-resume is enabled by default and you should leave it on.
You can inspect and change these settings by going to Compute → Warehouses in the nav bar, finding your warehouse, and clicking into it. The settings panel will show you the current auto-suspend timeout, let you change the size, and show a credit usage chart for the warehouse.
Checking Your Credit Usage
To see how many credits you’ve consumed so far, navigate to Admin → Cost Management (the exact label may vary depending on your account configuration, but it’s in the Admin section). You’ll find a breakdown of credit consumption by warehouse, date, and service type. On a fresh trial account after running the queries in this post, you should have used a fraction of a credit — less than a nickel.
A Note on the USE Commands
We used fully-qualified names throughout this post, but you’ll see USE DATABASE and USE SCHEMA constantly in Snowflake documentation and tutorials. For reference, here’s how they work:
— Set the active database for this session
USE DATABASE SNOWFLAKE_SAMPLE_DATA;

— Set the active schema within that database
USE SCHEMA TPCH_SF1;

— Set the active warehouse
USE WAREHOUSE COMPUTE_WH;

— Now this works without any path at all:
SELECT * FROM ORDERS LIMIT 10;
These context-setting commands are session-scoped — they apply only to your current connection. If you open a new worksheet tab, you’d need to run them again (or use the dropdowns in the worksheet header, which do the same thing). They don’t change anything permanent in your account.
What You Now Know
You’ve queried a multi-million-row dataset, joined five tables together, and watched Snowflake return results in seconds. You also understand the virtual warehouse — that it’s the engine doing the work, that it’s separately billed from storage, and that the auto-suspend and auto-resume settings govern both cost and availability.
A quick recap of the key mental models from this post:
Data lives in a Database → Schema → Table hierarchy. Reference objects with their full path or set context with USE statements.
The virtual warehouse is compute, separate from storage. You pay for it only while it’s running.
Warehouses bill per second with a 60-second minimum each time they resume.
Auto-suspend stops an idle warehouse. Auto-resume starts it back up when a query arrives. Leave both enabled.
When in doubt, start with an X-Small warehouse and resize only if queries are genuinely slow.
What’s Next
In Part 3, we’ll create our own database and tables, load some real data into Snowflake using the web interface’s file upload feature, and write queries against data we own. We’ll also look at Snowflake’s VARIANT column type — the gateway to querying JSON and other semi-structured data without needing to flatten it first.
This series is designed to be hands-on. If you ran into anything unexpected — a warehouse that wouldn’t start, an error message you didn’t recognize — drop it in the comments. Real errors make the best teaching moments.

Cloud Maturity Series — Runbook for Posts 4–6

This runbook operationalizes Posts 4–6:
Preventive guardrails, multi-account scaling, and velocity-safe delivery.

Post 4 — Enforce Preventive Guardrails (SCPs)

1. Enable AWS Organizations.
2. Create Organizational Units (OUs): Management, Shared, Dev, Prod.
3. Create baseline SCP denying:
   – CloudTrail disable/delete
   – AWS Config disable
   – GuardDuty disable
4. Attach baseline SCP to all OUs.
5. Add stricter SCPs to Prod OU only.

Post 5 — Multi-Account Design

1. Create separate AWS accounts for Shared Services, Dev, and Prod.
2. Move accounts into appropriate OUs.
3. Centralize logging and security tooling.
4. Configure cross-account CI/CD roles.
5. Restrict Prod access to pipelines only.

Post 6 — Velocity-Safe Delivery

1. Standardize permission sets via IAM Identity Center.
2. Deploy infrastructure only through IaC.
3. Automate new-account baselines.
4. Monitor drift with AWS Config.
5. Review guardrails quarterly, not per-deploy.