A First Analysis on TPCH
Everything the series taught, pointed at one target: a real analysis of a million-order business, from setting context to answering questions a stakeholder would actually ask.
You’ve met all the pieces now — the storage-and-compute split, the object hierarchy, roles and grants, warehouses and their credit meter, Time Travel and clones. Pieces are not a skill, though. The skill is pointing them at a question someone actually cares about and getting a defensible number back. So we’ll do exactly that: take the TPCH sample business Snowflake handed you on day one and run a small, honest analysis on it — the kind you’d present, not the kind you’d SELECT 1 and abandon. No new features. Just the ones you have, used together the way real work uses them, plus the handful of SQL constructs — window functions, layered CTEs, QUALIFY — that turn a group-by into an actual finding.
Setting the table
Real analysis doesn’t start with a SELECT. It starts by deciding who you are and what runs your query, because in Snowflake both are choices you make explicitly. Pick a role that isn’t your admin account — you analyze as an analyst, not as the owner of everything — and a warehouse sized for the work:
USE ROLE analyst;
USE WAREHOUSE analyst_wh;
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;Four lines, and every one earns its place. USE ROLE puts you in a least-privilege identity, so a fat-fingered statement can’t reach past what an analyst should touch. USE WAREHOUSE attaches compute you right-sized — an XS is plenty for SF1, and if you’d pointed at SF100 you’d resize up first. The database and schema lines set context so the queries below can name tables plainly. This is the whole series compressed into a preamble: identity, compute, and storage, chosen separately because they are separate.
Question one: where does the revenue come from?
The first thing any stakeholder asks. Revenue by region, broken out by year, so you can see not just who’s biggest but who’s growing. This walks the full geography chain — line items up through orders, customers, nations, to regions — and buckets the order date into years:
SELECT
r.r_name AS region,
DATE_TRUNC('year', o.o_orderdate) AS order_year,
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, DATE_TRUNC('year', o.o_orderdate)
ORDER BY region, order_year;Five joins and two expressions, and all of them are worth reading closely. The join path is the star schema doing its job — a fact table (lineitem) reaching out to its dimensions until a numeric key becomes “Europe”. The l_extendedprice * (1 - l_discount) is how TPCH models money actually collected after the line discount; reach for it every time you compute revenue here, because summing extendedprice alone overstates the take. DATE_TRUNC('year', …) collapses every order date to January 1st of its year, which is the honest way to bucket time in Snowflake — it returns a real DATE, so downstream window functions can still order it chronologically, where a bare YEAR() integer would lose that footing. The result is a tidy region-by-year grid — the shape of a slide, not a data dump.
Reading the profile on that join
Before you trust a number, look at how Snowflake got it. Run the query, then open the Query Profile — in Snowsight, from the query’s row in Query History, or the profile tab beside your results. It’s a diagram of operators, and on a five-way join it tells you three things at a glance.
First, pruning. Click the bottom TableScan on lineitem and read the Partitions scanned versus Partitions total line. This query has no WHERE clause — it aggregates the whole fact table — so you’ll see all partitions scanned. That’s not a bug, it’s the honest cost of a total: there’s nothing to skip. The moment you add WHERE o_orderdate >= '1997-01-01', watch that ratio drop, because Snowflake reads each micro-partition’s min/max metadata and skips the ones that can’t match. Pruning is the single biggest lever on scan cost, and the profile is where you confirm it’s actually happening.
Second, the heaviest operator. Each node shows a percentage of total execution time; sort your attention by it. On this query the lineitem TableScan and the largest hash join dominate — six million line items have to be read and streamed through the joins, while region (five rows) and nation (twenty-five) are rounding error. That percentage is your optimization compass: tuning the thing that’s 2% of runtime is wasted effort.
Third, join order. Snowflake decides which side of each join to build a hash table on, and it’s smart about it — it builds on the small dimensions (region, nation, customer) and probes them by streaming the giant lineitem table through once. You’ll see the small tables feed the build side of each Join node and lineitem feed the probe side. That’s the plan you want. If you ever see Snowflake building a hash table on the fact table instead, that’s a red flag worth chasing — usually a bad row-count estimate. Here, the plan is textbook, which is exactly why a five-way join over millions of rows returns in seconds on an XS.
Question two: who’s actually growing?
Aggregate totals tell you who’s biggest. They’re silent about momentum, and momentum is what a stakeholder is really buying when they ask for “the revenue numbers.” So compute a monthly running total within each year and a year-over-year comparison, in one query, using a layered CTE. The first step buckets revenue by region and month; the second lays window functions over that tidy base:
WITH monthly AS (
SELECT
r.r_name AS region,
DATE_TRUNC('month', o.o_orderdate) AS order_month,
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, DATE_TRUNC('month', o.o_orderdate)
),
windowed AS (
SELECT
region,
order_month,
net_revenue,
SUM(net_revenue) OVER (
PARTITION BY region, YEAR(order_month)
ORDER BY order_month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_ytd,
LAG(net_revenue, 12) OVER (
PARTITION BY region
ORDER BY order_month
) AS rev_year_ago
FROM monthly
)
SELECT
region,
order_month,
net_revenue,
running_total_ytd,
ROUND(100 * (net_revenue / NULLIF(rev_year_ago, 0) - 1), 1) AS yoy_pct
FROM windowed
ORDER BY region, order_month;This is the first query in the series that’s genuinely more than a group-by, so slow down over the window clauses — they’re the workhorses of analytics SQL. SUM(net_revenue) OVER (PARTITION BY region, YEAR(order_month) ORDER BY order_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a running total: for each month it sums every earlier month in the same region and year, resetting cleanly each January because the year is part of the partition. The ROWS BETWEEN frame is what makes it cumulative rather than a flat per-group sum — without it you’d get the year’s whole total repeated on every row. LAG(net_revenue, 12) reaches twelve rows back within a region to grab the same month a year prior, which is only correct because the months are dense and ordered; that’s the payoff for bucketing with DATE_TRUNC instead of a bare integer year. The final SELECT turns those two lagged values into a year-over-year percentage, with NULLIF(rev_year_ago, 0) guarding the division so the first twelve months — which have no prior year — return NULL instead of blowing up. Layering it as CTEs isn’t decoration: you can’t nest a window function inside an aggregate, so the GROUP BY has to finish in monthly before windowed can lag over its output. Two steps, each doing one job, which is also how you’ll structure every non-trivial transformation you ever write.
Question three: the winner in every region
Top-N overall is easy — ORDER BY … LIMIT 10. Top-N per group is the question people actually ask (“what’s our best-selling part in each region?”), and it’s where newcomers reach for a clumsy self-join or a correlated subquery. Snowflake gives you a cleaner tool: QUALIFY, which filters on the result of a window function the same way HAVING filters on an aggregate. Here’s the single top-revenue part in each of the five regions, from a six-way join that adds part to the geography chain:
SELECT
r.r_name AS region,
p.p_name AS part,
SUM(l.l_extendedprice * (1 - l.l_discount)) AS net_revenue,
SUM(l.l_quantity) AS units_sold
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
JOIN part p ON p.p_partkey = l.l_partkey
GROUP BY r.r_name, p.p_name
QUALIFY ROW_NUMBER() OVER (
PARTITION BY r.r_name
ORDER BY SUM(l.l_extendedprice * (1 - l.l_discount)) DESC
) = 1
ORDER BY net_revenue DESC;The QUALIFY ROW_NUMBER() OVER (PARTITION BY r.r_name ORDER BY … DESC) = 1 ranks every part within each region by revenue and keeps only the top one — five rows out, one champion per region. Two details make it correct. First, the window’s ORDER BY repeats the full SUM(...) expression rather than leaning on the net_revenue alias, because the ranking has to happen after the GROUP BY aggregates but the alias isn’t guaranteed visible inside the window there — spelling out the aggregate is the safe, unambiguous form. Second, QUALIFY runs after GROUP BY and the window function, which is exactly why it can filter on a ranking that doesn’t exist until aggregation is done; WHERE couldn’t do this, because WHERE runs first. Carrying units_sold alongside revenue is the small move that keeps the answer honest — it separates the parts that win on volume from the ones that win on price, two very different stories to tell. Want the top three per region? Change = 1 to <= 3. That’s the whole edit, and it’s why QUALIFY beats every workaround.
A scratch space of your own
Three questions in, you’ll want to try something riskier — reshape a table, test a theory, maybe rewrite some rows. Don’t do it against the shared sample data. Clone or copy into a space that’s yours, experiment there, and the original stays pristine. But here’s the catch the analyst role hits immediately: there’s nowhere to write yet. SNOWFLAKE_SAMPLE_DATA is read-only, and analyst — correctly, per its least privilege — can’t create a database. So set the scratch space up first, as the role that owns infrastructure. Switch to SYSADMIN, build it, and grant the analyst room to work:
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS my_scratch;
CREATE SCHEMA IF NOT EXISTS my_scratch.dev;
GRANT USAGE ON DATABASE my_scratch TO ROLE analyst;
GRANT USAGE ON SCHEMA my_scratch.dev TO ROLE analyst;
GRANT CREATE TABLE ON SCHEMA my_scratch.dev TO ROLE analyst;
GRANT CREATE VIEW ON SCHEMA my_scratch.dev TO ROLE analyst;Now the database and schema exist and are owned by SYSADMIN (where infrastructure belongs, per the roles post), and analyst holds exactly the three privileges it needs and nothing more: USAGE to see the database and schema, and CREATE TABLE/CREATE VIEW to build inside dev. Switch back and you can finally write.
Here’s where a real Snowflake fact bites, and it’s worth knowing. The zero-copy CLONE from the cloning post is instant and free within your own account, because a clone is just new metadata pointing at micro-partitions you already own. But SNOWFLAKE_SAMPLE_DATA isn’t yours — it’s a share from Snowflake’s account (a preview of the data-sharing post ahead), and you can’t zero-copy a table across an account boundary, because the underlying storage lives somewhere you don’t own. So the first hop off shared data is a genuine copy — CREATE TABLE AS SELECT — into your scratch. Take a slice, not the whole six million rows, so it stays cheap:
USE ROLE analyst;
CREATE TABLE my_scratch.dev.lineitem_dev AS
SELECT *
FROM snowflake_sample_data.tpch_sf1.lineitem
WHERE l_shipdate >= '1998-01-01';That materializes a real, private, writable table you own — and it does cost storage, precisely because it crossed the share boundary and had to copy bytes. But now the cloning magic is available to you, because lineitem_dev is yours. Spin up a throwaway experiment copy for free, mangle it however the theory demands, and drop it when you’re done:
CREATE TABLE my_scratch.dev.lineitem_experiment
CLONE my_scratch.dev.lineitem_dev;That clone is instant and near-free — same-account, copy-on-write, paying only for whatever rows you eventually change. And if you fumble the experiment, Time Travel is right there: the pre-mistake version is one BEFORE(STATEMENT => …) away. This is the whole series composed into one workflow — least-privilege setup, a share you copy once, then fearless zero-copy iteration on data you control.
Making the analysis last
A query in a worksheet is an answer you have to re-run. Real analysis gets persisted, so the next person — or tomorrow’s you — reads a table or a view instead of your SQL. Snowflake gives you three ways to freeze work, and choosing among them is a genuine decision.
The bluntest is CREATE TABLE AS SELECT — snapshot the answer into a real table:
CREATE TABLE my_scratch.dev.revenue_by_region_year AS
SELECT
r.r_name AS region,
DATE_TRUNC('year', o.o_orderdate) AS order_year,
SUM(l.l_extendedprice * (1 - l.l_discount)) AS net_revenue
FROM snowflake_sample_data.tpch_sf1.lineitem l
JOIN snowflake_sample_data.tpch_sf1.orders o ON o.o_orderkey = l.l_orderkey
JOIN snowflake_sample_data.tpch_sf1.customer c ON c.c_custkey = o.o_custkey
JOIN snowflake_sample_data.tpch_sf1.nation n ON n.n_nationkey = c.c_nationkey
JOIN snowflake_sample_data.tpch_sf1.region r ON r.r_regionkey = n.n_regionkey
GROUP BY r.r_name, DATE_TRUNC('year', o.o_orderdate);That’s fast to read forever after, because the join already ran — but it’s frozen. New orders won’t show up until you rebuild it. When you want the query to stay live, save the SQL instead as a view:
CREATE VIEW my_scratch.dev.revenue_by_region_year_v AS
SELECT
r.r_name AS region,
DATE_TRUNC('year', o.o_orderdate) AS order_year,
SUM(l.l_extendedprice * (1 - l.l_discount)) AS net_revenue
FROM snowflake_sample_data.tpch_sf1.lineitem l
JOIN snowflake_sample_data.tpch_sf1.orders o ON o.o_orderkey = l.l_orderkey
JOIN snowflake_sample_data.tpch_sf1.customer c ON c.c_custkey = o.o_custkey
JOIN snowflake_sample_data.tpch_sf1.nation n ON n.n_nationkey = c.c_nationkey
JOIN snowflake_sample_data.tpch_sf1.region r ON r.r_regionkey = n.n_regionkey
GROUP BY r.r_name, DATE_TRUNC('year', o.o_orderdate);A view stores no data — it’s the query text, re-run every time someone selects from it. Always current, but it pays the full join cost on every read. That’s the trade: the table is fast-and-stale, the view is fresh-and-recomputed.
The third option splits the difference — a materialized view, which stores the pre-computed result and keeps it current as the source changes:
CREATE MATERIALIZED VIEW my_scratch.dev.lineitem_rev_by_ship_year AS
SELECT
DATE_TRUNC('year', l_shipdate) AS ship_year,
SUM(l_extendedprice * (1 - l_discount)) AS net_revenue,
SUM(l_quantity) AS units_sold
FROM my_scratch.dev.lineitem_dev
GROUP BY DATE_TRUNC('year', l_shipdate);Notice what changed, because Snowflake forces it to. A materialized view is single-table — no joins allowed — and can’t use window functions, HAVING, ORDER BY, or LIMIT. That’s why this one aggregates the one lineitem_dev table you own rather than the five-way geography join: the join query simply can’t be a materialized view, and Snowflake will reject it if you try. It also requires Enterprise edition or above, so on a Standard trial the statement fails outright — use a scheduled table rebuild instead. When it fits, though, it’s the best of both: reads hit the stored result, and Snowflake transparently refreshes it in the background (a serverless cost you should know is metered). The rule of thumb: reach for a materialized view only for a hot, single-table rollup you read far more often than the base data changes. For anything with joins — which is most real analysis — you’re choosing between a view and a rebuilt table, and that choice is exactly what the dbt-and-Airflow stack automates.
Putting it on a dashboard
“The kind of analysis you’d present” isn’t finished as a result grid — it’s finished as a chart someone can read in three seconds. Snowsight builds one without leaving the worksheet. Run the region-by-year revenue query, and above the results toggle from Results to Chart. Snowsight guesses a chart type from your columns; steer it — pick a line chart, set order_year as the X-axis, net_revenue as the Y-axis, and region as the series (the color split), and you’ve got five revenue trend lines, one per region, that make “who’s growing” obvious at a glance in a way the raw grid never will.
Then make it durable. From the left nav open Dashboards, create a new one, and add a tile — each tile is backed by its own worksheet query, so you paste the query in, render its chart, and pin it. Add a second tile for the year-over-year query as a bar chart, a third for the top-part-per-region result, and you’ve assembled something you can share with a stakeholder or refresh on a schedule. This is the payoff of persisting your work: a dashboard tile pointed at a view is always current, while one pointed at a table shows a frozen snapshot — the same fresh-versus-fast trade from the last section, now visible to whoever opens the link.
What it actually cost
You should never present a number without knowing what it cost to compute — partly for the bill, partly because cost is a signal that your query is doing something dumb. Two views tell the story. First, how much data each query chewed through this session:
SELECT
query_text,
partitions_scanned,
partitions_total,
bytes_scanned,
total_elapsed_time
FROM TABLE(information_schema.query_history_by_session())
ORDER BY start_time DESC
LIMIT 10;bytes_scanned and the partitions_scanned / partitions_total ratio are your work meter — the same pruning story the Query Profile showed, now queryable. A full-table aggregate scans everything; a well-filtered query scans a sliver. Watch that number fall when you add a WHERE, and you’ve measured pruning rather than hoped for it.
Here’s the correction worth internalizing, because it trips up everyone arriving from a scan-billed warehouse like BigQuery: Snowflake does not bill you for bytes scanned. It bills for warehouse time — credits burned equal warehouse size times how long it ran. Bytes scanned is a proxy for how hard the query worked, not a line item. The actual credit spend comes from the metering view:
SELECT
warehouse_name,
SUM(credits_used) AS credits
FROM TABLE(information_schema.warehouse_metering_history(
DATEADD('day', -1, CURRENT_TIMESTAMP())
))
GROUP BY warehouse_name;An XS burns one credit per hour, billed per second with a sixty-second floor on each resume. So the whole analysis above — a handful of queries that each ran a few seconds on an XS — cost a small fraction of one credit, and most of that was the sixty-second minimum on the first query that woke the warehouse. The relationship between the two meters is the intuition to keep: fewer bytes scanned usually means less time, which usually means fewer credits — but the thing you’re charged for is always the clock, which is why AUTO_SUSPEND and right-sizing move the bill far more than shaving a scan. The fuller, account-wide version of this accounting lives in the ACCOUNT_USAGE views, which the cost-governance post ahead builds real guardrails on.
Final thoughts
Step back from the SQL and notice what actually made this possible, because it wasn’t any single clever query — it was that none of the hard parts showed up. You never thought about indexes, or a vacuum job, or whether a six-way join over millions of rows would fit in memory, or whether your analysis would slow down the person loading data in the next room. The storage-and-compute split quietly absorbed all of it: storage that’s just there, immutable and shared; compute you size to the question and switch off after; a scratch database you stood up in four grants and can drop without a trace. That’s the real reward of the foundations — not that you can query Snowflake, but that the platform gets out of the way and leaves you alone with the actual work, which was always the analysis and never the plumbing.
And this analysis quietly unlocked the rest of the book. The moment you copied lineitem out of a share, you were one concept away from the data-sharing and Marketplace post — sharing is that same boundary, run in reverse. The materialized view that refused your join is the doorway into table types and views, and its background refresh is the doorway into programmability — streams, tasks, and Snowpark. The bytes_scanned and metering queries are the first pull of the thread that becomes cost, credits, and resource monitors. And if your orders had arrived as JSON instead of neat columns, you’d already want semi-structured data, which is next. You didn’t just answer three questions — you found the edges of every advanced topic ahead.
Where it all eventually points, once the queries stop being hand-run, is orchestration: a real analytics practice schedules these transformations, versions them like software, and rebuilds them on a cadence — a Snowflake warehouse with dbt modeling the transformations and Airflow orchestrating the runs. That’s a capstone of its own. But you can already walk up to a million-order business you didn’t have to load, on compute you didn’t have to provision, and hand back real answers with the receipts. That’s further than most people get with a data warehouse in a month.
Comments