Querying the Sample Data You Already Have

Snowflake ships a million-order business you can query on day one — TPCH — so you can learn joins, window functions, the Query Profile, and warehouse behavior before loading a single row of your own.

Most database tutorials open with a chore: install this, seed that, wait for the load to finish before you can write your first real query. Snowflake skips it. The moment your account exists, there’s a database sitting in your object list called SNOWFLAKE_SAMPLE_DATA — read-only, shared by Snowflake, and already populated with a business large enough to be interesting. No download, no COPY INTO, no waiting. You can write a query that scans a million orders in the time it takes to read this sentence.

That database is where you should live for your first hour. It’s the difference between learning Snowflake’s SQL and warehouse behavior on realistic data versus poking at a SELECT 1 and pretending you’ve learned something.

What’s actually in there

SNOWFLAKE_SAMPLE_DATA holds the TPCH dataset — a standard benchmark schema modeled on a wholesale supplier. It’s not toy data. It’s a normalized orders business with the joins you’d actually write against a real warehouse. The same tables are provided at several scales, each in its own schema: TPCH_SF1 is roughly one gigabyte and about 1.5 million line items, TPCH_SF100 is a hundred times that, and TPCH_SF1000 is a thousand times — that last one is billions of line items, and it’s genuinely useful for feeling what a big warehouse is for.

The tables you’ll care about:

  • CUSTOMER — who’s buying, with a C_NATIONKEY pointing at their nation and a C_MKTSEGMENT you can slice by.
  • ORDERS — one row per order, with a total price (O_TOTALPRICE), an order date (O_ORDERDATE), a status, and a priority.
  • LINEITEM — the individual lines on each order (quantity, price, discount, ship date, return flag). This is the big one and where the real money lives.
  • PART, SUPPLIER, PARTSUPP — the catalog side: what’s being sold, who supplies it, and at what cost. PARTSUPP is the many-to-many bridge between parts and suppliers.
  • NATION and REGION — the geography lookup tables that turn a key into “Germany” and “Europe”.

Orders belong to customers, line items belong to orders and reference a part and a supplier, customers belong to nations, and nations roll up into regions. That’s a real star-ish shape — enough to practice every join and aggregation you’ll write for the rest of your Snowflake life.

Before any query runs, set your context so you don’t have to fully-qualify every table:

USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

A query that earns its keep

Here’s the top ten customers by total order value — a group-by across a join, which is the shape of most analytics work you’ll ever do:

SELECT
  c.c_name,
  n.n_name AS nation,
  SUM(o.o_totalprice) AS lifetime_value
FROM customer c
JOIN orders o ON o.o_custkey = c.c_custkey
JOIN nation n ON n.n_nationkey = c.c_nationkey
GROUP BY c.c_name, n.n_name
ORDER BY lifetime_value DESC
LIMIT 10;

Nothing here is Snowflake-specific — it’s ANSI SQL, and that’s the point. Snowflake didn’t invent a new dialect for you to relearn. What it did was make this query return against a million-order table without you thinking about indexes, partitions, or vacuum.

Push one level deeper and aggregate revenue by region, joining all the way up the geography chain:

SELECT
  r.r_name AS region,
  SUM(l.l_extendedprice * (1 - l.l_discount)) AS net_revenue
FROM lineitem l
JOIN orders o   ON o.o_orderkey = l.l_orderkey
JOIN customer c ON c.c_custkey  = o.o_custkey
JOIN nation n   ON n.n_nationkey = c.c_nationkey
JOIN region r   ON r.r_regionkey = n.n_regionkey
GROUP BY r.r_name
ORDER BY net_revenue DESC;

That l_extendedprice * (1 - l_discount) is how TPCH models net revenue after the line discount — worth internalizing, because you’ll reach for it every time you compute revenue off this dataset. Five joins, an arithmetic expression inside an aggregate, grouped and sorted. If you can write and read this, you can query Snowflake.

SQL beyond ANSI: the parts worth learning early

Snowflake speaks standard SQL, but it also ships a handful of extensions that turn three-query problems into one-query problems. You don’t strictly need them, but the day you learn them you stop writing subquery gymnastics. Here are the ones worth pocketing, each against TPCH so you can run it right now.

QUALIFY with window functions. The classic “top N per group” problem — the single most-valued customer in each nation — is awkward in plain SQL because you can’t filter on a window function in a WHERE clause. Snowflake’s QUALIFY fixes exactly that: it filters on the result of a window function the way HAVING filters on an aggregate.

SELECT
  n.n_name AS nation,
  c.c_name,
  SUM(o.o_totalprice) AS customer_value,
  ROW_NUMBER() OVER (
    PARTITION BY n.n_name
    ORDER BY SUM(o.o_totalprice) DESC
  ) AS rnk
FROM customer c
JOIN orders o ON o.o_custkey = c.c_custkey
JOIN nation n ON n.n_nationkey = c.c_nationkey
GROUP BY n.n_name, c.c_name
QUALIFY rnk = 1;

That’s a group-by and a window function and a top-1-per-partition filter in one statement. Swap ROW_NUMBER() for RANK() if you want ties to share a rank (and possibly return more than one row per nation), or DENSE_RANK() if you don’t want ranks to skip. ROW_NUMBER() always breaks ties arbitrarily and gives you exactly one winner per partition — usually what you want for “the top one.”

Window functions also do running totals without a self-join. Monthly revenue with a cumulative-to-date column:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', o.o_orderdate) AS order_month,
    SUM(l.l_extendedprice * (1 - l.l_discount)) AS revenue
  FROM lineitem l
  JOIN orders o ON o.o_orderkey = l.l_orderkey
  GROUP BY order_month
)
SELECT
  order_month,
  revenue,
  SUM(revenue) OVER (ORDER BY order_month
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_total
FROM monthly
ORDER BY order_month;

Two things to notice. First, the CTE (WITH monthly AS (...)) — Snowflake handles these cleanly, and chaining several of them is the idiomatic way to write a readable multi-step query instead of nesting subqueries five deep. Second, SUM() OVER (...) with an explicit window frame is how you get a running total; the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame says “everything from the start up to this row.”

Date functions. TPCH order dates run from 1992 through 1998, which makes it a good playground for Snowflake’s date toolkit. DATE_TRUNC snaps a date down to the start of a month, quarter, or year. YEAR(), MONTH(), and friends pull out components. DATEADD and DATEDIFF do date arithmetic, and LAST_DAY gives you the end of a period:

SELECT
  YEAR(o.o_orderdate)                       AS order_year,
  COUNT(*)                                  AS orders,
  DATEADD('day', 90, MAX(o.o_orderdate))    AS ninety_days_out,
  LAST_DAY(MAX(o.o_orderdate))              AS month_end
FROM orders o
GROUP BY order_year
ORDER BY order_year;

You’ll use DATE_TRUNC constantly for time-series rollups and DATEADD/DATEDIFF for anything involving windows of days. The gap between l_commitdate and l_receiptdate on LINEITEM, for instance, is a natural “how late was this shipment” calculation — and DATEDIFF makes it one line:

SELECT
  l_shipmode,
  AVG(DATEDIFF('day', l_commitdate, l_receiptdate)) AS avg_days_late,
  COUNT_IF(l_receiptdate > l_commitdate)            AS late_shipments
FROM lineitem
GROUP BY l_shipmode
ORDER BY avg_days_late DESC;

COUNT_IF there is another Snowflake convenience — a conditional count that saves you a SUM(CASE WHEN … THEN 1 END). Small, but it’s the kind of thing that makes queries read like what they mean.

PIVOT and UNPIVOT. To turn rows into columns — a cross-tab of order counts by year and priority — Snowflake gives you PIVOT instead of a pile of CASE WHEN … THEN 1 END expressions:

SELECT *
FROM (
  SELECT YEAR(o_orderdate) AS yr, o_orderpriority AS priority
  FROM orders
) src
PIVOT (
  COUNT(*) FOR priority IN (
    '1-URGENT', '2-HIGH', '3-MEDIUM', '4-NOT SPECIFIED', '5-LOW'
  )
)
ORDER BY yr;

UNPIVOT does the reverse, folding a set of columns back into rows — handy when someone hands you a wide report and you need it long for charting. Say you’ve built a per-region summary with one column per year; UNPIVOT turns those year-columns back into (region, year, revenue) rows:

WITH wide AS (
  SELECT * FROM (
    SELECT r.r_name AS region, YEAR(o.o_orderdate) AS yr,
           l.l_extendedprice * (1 - l.l_discount) AS rev
    FROM lineitem l
    JOIN orders o    ON o.o_orderkey  = l.l_orderkey
    JOIN customer c  ON c.c_custkey   = o.o_custkey
    JOIN nation n    ON n.n_nationkey = c.c_nationkey
    JOIN region r    ON r.r_regionkey = n.n_regionkey
  )
  PIVOT (SUM(rev) FOR yr IN (1994, 1995, 1996))
    AS p (region, y1994, y1995, y1996)
)
SELECT * FROM wide
UNPIVOT (revenue FOR order_year IN (y1994, y1995, y1996))
ORDER BY region, order_year;

The catch with PIVOT is that you have to list the target values explicitly (Snowflake also supports PIVOT (... FOR yr IN (ANY ORDER BY yr)) to discover them dynamically, which is newer and worth knowing). Notice too how the CTE lets the pivot and unpivot read as two clean steps rather than one unreadable nest.

QUALIFY again, for deduplication. The same QUALIFY ROW_NUMBER() = 1 shape that found the top customer per nation is also the idiomatic Snowflake way to deduplicate. Suppose you want the single most recent order per customer:

SELECT o_orderkey, o_custkey, o_orderdate, o_totalprice
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY o_custkey ORDER BY o_orderdate DESC
) = 1;

No subquery, no self-join — partition by the key you want to be unique, order by whatever decides the winner, keep row 1. You’ll write this pattern constantly against your own data to collapse duplicate loads down to one row per key.

LISTAGG. To collapse many rows into one delimited string — every ship mode used by each order priority, on a single line:

SELECT
  o_orderpriority,
  LISTAGG(DISTINCT l_shipmode, ', ')
    WITHIN GROUP (ORDER BY l_shipmode) AS ship_modes
FROM orders o
JOIN lineitem l ON l.l_orderkey = o.o_orderkey
GROUP BY o_orderpriority
ORDER BY o_orderpriority;

LISTAGG is the string-building aggregate: it’s how you get a comma-separated summary column without exporting to a script. The WITHIN GROUP (ORDER BY …) clause controls the order inside each concatenated string.

TOP n and SAMPLE. LIMIT works, but Snowflake also accepts SELECT TOP 10 … if you prefer that spelling. More interesting is SAMPLE, which pulls a pseudo-random subset — invaluable for eyeballing a huge table cheaply:

-- 1% of line items, sampled by row (Bernoulli)
SELECT * FROM lineitem SAMPLE (1);

-- exactly 100 rows, sampled
SELECT * FROM lineitem SAMPLE (100 ROWS);

On TPCH_SF1000, SELECT * is a bad idea and SAMPLE (0.01) is how you get a feel for the data’s shape without scanning billions of rows. Add a SEED if you need the same sample twice — SAMPLE (1) SEED (42) — which matters when you’re comparing two versions of a transform and want them looking at identical rows. It’s a real workhorse once your own tables get big.

Reading the Query Profile

Everything so far treats Snowflake as a black box that “just works.” The Query Profile is where you open the box. After any query runs in Snowsight, click into the query’s history and open the Query Profile tab — it’s a visual tree of the operators Snowflake used, with the expensive ones flagged. Learning to read it is what separates “the query is slow” from “here’s why it’s slow.” Run the region-revenue query above, then open its profile and look for four things.

Pruning. Find the TableScan on LINEITEM and read its statistics: partitions scanned versus partitions total. Snowflake stores data in immutable micro-partitions and keeps min/max metadata for each one, so a filtered query can skip partitions that can’t possibly match — that’s pruning. Our region-revenue query has no filter on LINEITEM, so it scans all of them, and the ratio will read something like “partitions scanned: 250 / 250.” Add a WHERE o.o_orderdate >= '1998-01-01' and rerun, and — because the data correlates with date — you’ll see far fewer partitions scanned. That drop is the performance win, made visible.

The most expensive operator. Each node shows a percentage of total execution time. In a five-join aggregation the biggest cost is usually a TableScan (reading LINEITEM) or one of the Join nodes. The profile sorts this for you — the “Most Expensive Nodes” panel names the single operator eating your query so you know where to aim.

Join order. The tree shows the order Snowflake chose to join the tables and, on each Join node, how many rows came in versus went out. A join that takes ten million rows in and emits fifty million is exploding — usually a sign of a missing or wrong join key producing a partial cross product. On correct TPCH joins the row counts stay sane, but this is exactly the panel you’d check when a query mysteriously balloons.

Spilling. If a join or sort needs more memory than the warehouse has, Snowflake spills to local disk, and if that fills, to remote storage. The profile shows “Bytes spilled to local storage” and “…to remote storage.” Any remote spilling is a red flag — it means the warehouse is too small for this query’s working set, and the fix is a bigger warehouse (or a cheaper query). On TPCH_SF1 you won’t spill; run the same query on SF100 on an X-Small and you might, which is the point of the next section.

Two more numbers reward a glance. Bytes scanned, shown on the scan nodes, is the honest measure of how much data the query actually touched — it’s what maps most directly to work done, and watching it fall as you add filters is the tightest feedback loop you have. And if you want to see the plan before paying to run it, EXPLAIN USING TEXT SELECT … prints the operator tree with estimated row counts without executing — a cheap way to sanity-check a monster query on SF1000 before you commit compute to it.

This is a first pass, not the full treatment. The deep dive on micro-partitions, clustering keys, and the cache layers is post 13 — for now, just knowing these numbers exist changes how you think about every query you write.

The scale experiment, done honestly

Now the part that makes Snowflake click. Take the region-revenue query, change exactly one word, and run it against a hundred times more data:

USE SCHEMA TPCH_SF100;

Same query, same joins, same result shape — but now it’s scanning hundreds of millions of line items instead of a million and a half. Here’s the thing, though: if you just run it twice and time it, you’ll get a lie. To measure honestly, you have to defeat the caches.

Turn off the result cache first. Snowflake keeps a result cache that stores the exact output of a query for 24 hours. Run a query, run it again unchanged, and the second run returns instantly — without spinning the warehouse or burning a credit — because Snowflake handed you the stored result, not a fresh computation. That’s wonderful in production and poison for a benchmark, because your second timing measures the cache, not the warehouse. Switch it off for the session:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Now every run actually computes. Time the SF1 query, then the SF100 query, on the same small warehouse. SF1 returns in a second or two; SF100 makes you wait. That wait is the physical sensation of a query outgrowing its compute — and it’s exactly what a bigger warehouse is for. Resize the warehouse up, rerun, watch the wait shrink.

But there’s a second cache, and it’s subtler. Even with the result cache off, the first time a warehouse reads a table it pulls the micro-partitions from cloud storage and keeps them in a warehouse-local data cache on the compute node’s SSD. The next query against the same data reads from that local cache instead of remote storage — so a second run can be faster than the first even though it genuinely recomputed the result. This isn’t the result cache; it’s warm local disk. To see a true cold number you’d suspend and resume the warehouse (which clears its local cache) between runs. For a rough feel you don’t need to be that fussy, but know that the two caches are different animals: the result cache skips the computation entirely, the data cache just skips the remote read.

And mind the credits. With the result cache off, every rerun on SF100 and SF1000 is real compute you’re paying for by the second. This is the cheapest performance lab you’ll ever have, but it isn’t free — a few minutes of an X-Small is nothing, a spree on a Large against SF1000 adds up. Turn USE_CACHED_RESULT back on (or just start a new session) when you’re done experimenting. The credit-and-warehouse-sizing math gets its own treatment in post 8; for now, the discipline is: cache off to measure, cache on to work.

One more thing: this database is a share

There’s a detail hiding in plain sight. You never loaded SNOWFLAKE_SAMPLE_DATA, you can’t write to it, and it doesn’t count against your storage bill — because it isn’t really your database. It’s a share: Snowflake maintains one copy of TPCH and exposes it, read-only, to every account through its Secure Data Sharing feature. When you query it, you’re reading Snowflake’s data live, with no copy made on your side. That’s not a special case built just for samples — it’s the same sharing primitive any Snowflake account can use to hand a live dataset to a partner or customer without exporting a single file. You’re already a data-sharing consumer on day one and didn’t notice. The full mechanics — providers, consumers, reader accounts, and the Marketplace — are post 15; file the idea away.

While you’re exploring, SNOWFLAKE_SAMPLE_DATA has more than TPCH. Poke around its other schemas: TPCH_SF1000 for a genuinely large dataset to stress a warehouse, TPCDS_SF10TCL (the TPC-DS benchmark — a richer retail schema with many more tables than TPCH, at a huge scale), and the smaller WEATHER schema if you want something other than orders to join against. They’re all shared the same way, all free to query, all there before you’ve loaded anything.

Two operational notes to pocket. A query needs a running warehouse — storage and compute are separate here, so the data sits in the share whether or not anything is running, but to scan it you need a virtual warehouse turned on. If a query errors about no warehouse selected, that’s the fix: USE WAREHOUSE COMPUTE_WH; (or whatever yours is named), and make sure it isn’t suspended. And when you’re not benchmarking, leave the result cache on — that suspiciously-fast third run of your demo is a cache hit doing you a favor.

Final thoughts

The instinct with a new warehouse is to load your own data first, because that’s the data you care about. Resist it for an hour. TPCH is better than your data for learning Snowflake precisely because it’s already there, already big, and already shaped like a real business — so the only variable you’re studying is Snowflake itself. Write the window functions, open the Query Profile, flip the schema from SF1 to SF100 with the cache off and feel the difference. Get fluent on data you didn’t have to load, and the day your own data arrives, the only new thing to learn is the loading.

Next: Loading Your Own Data: Stages and COPY INTO

Comments