Warehouse Performance & Cost

A query can be correct and still cost too much. A cloud warehouse is a database built for large reports. Vendors bill for it in two main ways:

Prices and rules change. Check the current docs of your product before you quote any number.

The size we assume

In this chapter, imagine the shop's orders table is 2 TiB. This is an assumption. It gives the cost numbers a scale.

A TiB (tebibyte) is 1,024 GiB. It is about 1.1 TB. BigQuery bills per TiB, so we use TiB and GiB everywhere.

Our example rate is $5 per TiB. It is not a real price.

With on-demand pricing, one run costs the TiB scanned times the price per TiB. Here is a plain query on the 2 TiB table:

SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

The filter on status keeps 4 rows. It does not shrink the scan. The engine must read every row to check its status.

Here is the arithmetic for one run:

1. Data read: all 5 columns of the whole table = 2 TiB.
2. Example rate: $5 per TiB.
3. One run: 2 x $5 = $10.00.
4. Once a day for 30 days: 30 x $10.00 = $300.00.

In column storage, each column is stored apart. Name two columns and the engine reads two. To count the cost, we assume these column sizes:

These sizes are made up to keep the arithmetic easy. Real sizes depend on the data type.

Now ask for revenue by status. This query names two columns:

SELECT status,
       SUM(amount) AS revenue
FROM orders
GROUP BY status
ORDER BY status;

The query reads status and amount only. That is 0.1 + 0.5 = 0.6 TiB. Compare it with the 2 TiB read by SELECT *:

You save $7.00 on every run, and that is 70 percent of $10.00. The numbers in the result do not change.

You know partitions from Storage Layers & File Formats. A partition is one slice of a table, such as one day of orders. You choose the partition column when you create the table:

CREATE TABLE orders (
  order_id    INT64,
  customer_id INT64,
  status      STRING,
  amount      NUMERIC,
  order_date  DATE
)
PARTITION BY order_date;

Each day becomes its own partition. The six shop orders fall on six different days, so they sit in six partitions:

At our scale, assume 2,048 days of data in equal partitions. 2 TiB is 2,048 GiB. Each partition holds 2,048 GiB / 2,048 = 1 GiB.

Pruning, from the same chapter, means the engine skips partitions that cannot match. You get it when you compare the partition column with fixed values. Look at this query:

SELECT order_id, amount, order_date
FROM orders
WHERE order_date >= '2026-01-10'
  AND order_date <  '2026-01-18'
ORDER BY order_id;

The range covers 8 days, so the engine reads 8 of the 2,048 partitions.

The query names three columns. Each is 0.5 TiB, so the three are 1.5 TiB. The steps are:

1. All columns of 8 partitions: 8 x 1 GiB = 8 GiB.
2. The three columns are 1.5 of the 2.0 TiB, so 75 percent. We assume each column is spread evenly over the days. 8 GiB x 0.75 = 6 GiB.
3. Cost: 6 / 1,024 TiB x $5 = $0.0293, about 3 cents.

A function on the column can block pruning

Both of the next two queries count the January orders. The first wraps order_date in a function:

SELECT COUNT(*) AS january_orders
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
  AND EXTRACT(MONTH FROM order_date) = 1;

The second asks the same question with a range of fixed dates:

SELECT COUNT(*) AS january_orders
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

Many engines cannot prune the first form, because they must run the function on every date. Some engines rewrite simple cases, so check the plan. The range form is safe.

A block is one chunk of stored rows, like a Parquet row group. Each block keeps the min and max of every column, so the engine can skip it.

Clustering sorts the data by columns you choose. Then each block covers a narrow range, and more blocks can be skipped.

In BigQuery, you add CLUSTER BY to a table. In Snowflake, a micro-partition is a block of about 50 to 500 MB before compression. You can add a clustering key:

-- BigQuery
CREATE TABLE orders (
  order_id INT64, customer_id INT64, status STRING,
  amount NUMERIC, order_date DATE
)
PARTITION BY order_date
CLUSTER BY customer_id;

-- Snowflake
ALTER TABLE orders CLUSTER BY (customer_id);

Imagine the six shop orders stored in two blocks, sorted by customer_id:

This query asks for one customer:

SELECT order_id, status
FROM orders
WHERE customer_id = 3
ORDER BY order_id;

Many people add LIMIT to make a query cheaper. In BigQuery on-demand, LIMIT does not cut the bytes read from a table that is not clustered. On a clustered table, LIMIT can read fewer bytes.

Look at this query:

SELECT *
FROM orders
LIMIT 2;

Two rows come back. Imagine orders without clustering. Then all 5 columns of every row are scanned.

The bill is the same 2 TiB, which is $10.00.

To look at the data for less, try one of these three ways:

• Add a filter on the partition column, as in the pruning section.
• Use the table preview in the web page of your warehouse. In BigQuery it is free, but check the docs.
• Read a sample with TABLESAMPLE. You are billed for the sampled part. Check the docs.

A result cache keeps the answer of a recent query. If you run the same query and the data has not changed, the warehouse returns the saved answer.

BigQuery does not bill bytes for a cached result. Snowflake also reuses results.

In BigQuery, the cache lasts about 24 hours. In Snowflake, a result is kept 24 hours, and each reuse extends it, up to 31 days. In Snowflake, reuse starts no compute machines.

The query text and the data must match. Check the docs for the exact rules.

A materialized view stores the result of a query and keeps it fresh. Reports then read the small stored result. Here is one for paid revenue per day:

CREATE MATERIALIZED VIEW daily_paid AS
SELECT order_date,
       SUM(amount) AS paid_amount
FROM orders
WHERE status = 'paid'
GROUP BY order_date;

Now a report that needs paid revenue per day reads 4 small rows, not the 2 TiB table. At our scale, it reads one row per day instead of every order. BigQuery still bills at least 10 MB for each table a query reads.

You know this from Joins & Relationship Reasoning. One order can have many items, so a join repeats the order row once for each item. That is fan-out.

This query adds up paid revenue after the join:

SELECT SUM(o.amount) AS paid_revenue,
       COUNT(*)      AS joined_rows
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid';

The true paid revenue is 160.50, so the answer is wrong. Four paid orders became 6 rows. At scale, the engine also moves and sums more rows, which costs compute time.

The fix is to shrink the many side first. This query turns order_items into one row per order, then joins:

WITH units AS (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items
  GROUP BY order_id
)
SELECT SUM(o.amount) AS paid_revenue,
       SUM(u.units)  AS units
FROM orders o
JOIN units u ON u.order_id = o.order_id
WHERE o.status = 'paid';

Keep three habits for cheaper joins:

1. Filter each table first, with a partition filter if it has one.
2. Aggregate the many side to one row per key before the join.
3. Select only the columns you need from each table.

In Snowflake, the word warehouse has a second meaning. A virtual warehouse is a group of machines that runs your queries. It is not the data.

Snowflake bills it in credits. A credit is a unit of compute that you buy with money.

It bills for each second it runs. You can suspend it (stop it) and resume it (start it again).

Each time it resumes, it bills at least 60 seconds. Check the current docs.

Sizes go XS (extra small), S (small), M (medium), and up.

Each size up uses about twice the credits per hour. Imagine one query that runs 8 minutes on size S and 4 minutes on size M. The arithmetic is:

The cost is the same, and the answer comes in half the time. This holds only if the query runs twice as fast on the bigger size. Many queries do not, so test your own.

Stop the warehouse when idle

A warehouse is idle when it runs no query. Auto-suspend stops it after it has been idle for a time you set.

Until it stops, an idle warehouse still bills. A warehouse of size XS uses 1 credit per hour, so 24 idle hours use 24 credits.

The 60-second minimum matters too. Imagine 10 queries of 10 seconds each, on XS.

Assume the warehouse stops right after each run. Real auto-suspend adds idle seconds. This table compares two ways to run them:

You can see the cost before you pay it. A dry run checks a query without running it. BigQuery shows the bytes it would process.

Snowflake EXPLAIN shows the partitions it plans to read. After a run, the Query Profile shows what it really read.

Indexes, Sargability & Reading a Plan teaches how to read a plan in general. In a warehouse, look at these four things:

Take the date-range query from the pruning section. You expect about 6 GiB. If the estimate says 1.5 TiB, then pruning failed.

Every warehouse keeps a history of past queries. You can query it like any table.

Here is where to look. Check the docs. Names differ, and new rows can take minutes to appear:

This BigQuery query adds up the TiB billed to each user over the last 7 days:

SELECT user_email,
       SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY tib_billed DESC;

At the example rate, Mia cost 4.0 x $5 = $20.00, and Raj cost 1.2 x $5 = $6.00. For Snowflake, group QUERY_HISTORY by user_name or warehouse_name instead. The WAREHOUSE_METERING_HISTORY view has credits per warehouse.

The practice lab uses a small query log in the same shape.

A query that looks big may not be the costly one. Multiply the data per run by the runs per day. Compare these two:

The dashboard reads 8.5 times more each day, because 2,040 / 240 = 8.5. Fix the dashboard first.

A wasteful query that runs once every three months can stay as it is. The fix saves very little, and a clever rewrite is more code to maintain. Also, make the query correct before you make it cheap.

Here is the date-range report fixed in two steps. The table shows the data read after each step:

When someone says "this query is expensive", check six things in this order:

1. Find it in the query history. How many bytes per run, and how many runs per day?
2. Look for SELECT * and wide columns. Name the columns you need.
3. Check the plan: partitions scanned and total. Remove any function on the partition column.
4. Check clustering: does the filter use the clustered columns?
5. Check the joins: are there more rows after the join than before? Aggregate first.
6. Can you reuse the answer with a cache or a materialized view? On Snowflake, check the size and auto-suspend.

After the fix, run it again and compare the bytes. A number proves the fix worked.

Each exercise opens in the SQL workspace with two small log tables.

table_partitions has rows and bytes for each partition. query_runs is a query history.

Write the query, run it, and check the numbers. These labs use only this chapter, so you can start now.