The Warehouse Platform: Storage, Compute & Loading

A cloud warehouse is a database service built for large reports. It looks like one thing. It has three parts:

Metadata means data about data. Snowflake calls its metadata part Cloud Services.

Here is one query on the shop’s orders table. Each part has a job in it:

SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';

1. Metadata says which files make up orders today.
2. Compute reads those files from storage and adds up the amounts.
3. The answer returns to you. Storage keeps the files unchanged.

Metadata explains two odd things you will meet later. A load can add no rows. A copy of a table can cost nothing at first.

In many older databases, the data and the query machines were tied together. To get more disk, you bought more machines.

In a cloud warehouse they are apart.

The data sits in one place. Several groups of machines can read the same copy at the same time.

So the shop can run three jobs on one orders table. Each job has its own compute:

A heavy night load cannot slow the dashboards. They do not share machines. Nobody copied the data.

A bucket is a named container in cloud object storage. Each product names its compute differently:

A dashboard is slow. Before you pick a bigger size, find out where the time went.

A query can wait in a queue before it runs. A bigger size is often not the best fix for that wait.

Snowflake’s query history shows the waiting times and the run time. The times are in milliseconds. This query reads two dashboard queries:

SELECT
  query_id,
  queued_overload_time     / 1000.0 AS overload_sec,
  queued_provisioning_time / 1000.0 AS provisioning_sec,
  execution_time           / 1000.0 AS run_sec
FROM snowflake.account_usage.query_history
WHERE query_id IN ('q1', 'q2');

• q1 waited 29 seconds because other queries used the compute. That is overload. Add clusters, extra copies of the warehouse, so more queries run at once (Snowflake Enterprise Edition or higher).
• q2 waited 25 seconds because the warehouse had to start. That is provisioning. Keep the warehouse running longer between queries.

A bigger size is the wrong first fix here. It may help q1 a little, but it does not help q2.

The run time of q1 and q2 was short already.

One case does need a bigger size. A query spills when a join or sort does not fit in memory.

Then it writes temporary data to disk. Heavy spill to remote storage means the compute is too small.

Ask two questions about any table. Who owns the data files? Who owns the metadata?

First, one word. A stage is a named link to a folder of files. COPY INTO reads files from a stage.

An external stage points to your own bucket. An internal stage lives in the vendor’s storage. A stage is not a table.

Now compare the three kinds of table:

An open table format, such as Iceberg or Delta, keeps the file list in a log that many engines read. Delta Lake & the Lakehouse in the Spark KB teaches it.

An external table lets you query files in your bucket without loading them. It has limits in Snowflake:

• It is read-only. You cannot run INSERT, UPDATE or DELETE on it.
• A new file does not appear until the file list is refreshed.
• It has no Time Travel. Time Travel is explained in a later section.

A bulk load reads files that already exist. It inserts their rows into a table. In Snowflake the command is COPY INTO.

Redshift has COPY from Amazon S3. BigQuery has load jobs.

In the story, the shop’s system writes order files to object storage. A stage points at that folder.

COPY INTO loads the files into a staging table. Staging holds raw rows before they reach orders.

The two files below are made up. They hold the six shop orders. The empty amount for order 105 is NULL:

CREATE TABLE stg_orders (
  order_id    NUMBER,
  customer_id NUMBER,
  status      VARCHAR,
  amount      NUMBER(10, 2),
  order_date  DATE
);

COPY INTO stg_orders
FROM @orders_stage
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1);

The FILE_FORMAT part tells Snowflake how to read the files. TYPE = CSV says the files are comma-separated text. SKIP_HEADER = 1 skips the first line of each file, which holds the column names.

Without a matching format, a load can fail. Or it can put values into the wrong columns. Match the format to the file.

Run the same COPY INTO again. Snowflake keeps a load history for each table. It remembers which files it loaded, and it keeps that for 64 days.

So the second run loads nothing. The files are in the history:

COPY INTO stg_orders
FROM @orders_stage
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1);

The option FORCE = TRUE loads every file again, even if it was loaded. COPY INTO has no update step. Every row is inserted again:

COPY INTO stg_orders
FROM @orders_stage
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1)
FORCE = TRUE;

SELECT order_id, COUNT(*) AS copies
FROM stg_orders
GROUP BY order_id
ORDER BY order_id;

Use FORCE only when you know the target can take duplicates. If a file was wrong, give the fixed file a new name.

Read the load result

A load can succeed and still reject rows. The option ON_ERROR = CONTINUE loads the good rows and skips the bad ones. Without it, Snowflake stops at the first error and loads nothing from that statement.

Here is a made-up file with two rows. Order 200 has "N/A" in the amount column:

COPY INTO stg_orders
FROM @orders_stage/orders_part3.csv
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1)
ON_ERROR = CONTINUE;

Each file is a unit of work for a load. Too many tiny files add overhead. Very few huge files give the load little to share between machines.

Snowflake advises about 100 to 250 MB per file, compressed. This is vendor guidance, so check the current docs. Other products give their own advice.

Imagine the shop gets 50 GB of compressed order data each day. This number is made up. Here is how many files each size makes:

The 200 MB size gives 256 files. Many machines can each take some.

The 1 MB size gives 51,200 files, so the load spends its time on file bookkeeping. The 25 GB size gives only 2 files to share.

Storage Layers & File Formats also warns about many small files. The reason is the same: each file has a fixed cost.

COPY INTO on a schedule is one way to load. There are two more.

Imagine a new order, 200, is paid at 10:00. This table shows when each way makes it visible:

Snowpipe is not streaming. It is COPY INTO that starts by itself when a file lands. The unit of work is still a file, so the wait is often about a minute, and longer for big files.

Row streaming skips the file step. It needs a client that sends rows all the time, and more care to run. Choose it only when users need data in seconds.

Start with bulk loads. Move to a faster way only when a real need appears.

You saw this pattern in Data Engineering SQL Usage. Load raw rows into staging. Remove duplicates.

Then MERGE the batch into the target table.

The pattern fits what you just saw. A bad load or a FORCE rerun can add duplicates to staging. Those duplicates never reach orders.

A made-up batch is in staging. It pays order 105 for 22.00 and adds a new order, 200. Snowflake’s MERGE has the same shape you wrote before:

MERGE INTO orders AS t
USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
  status = s.status,
  amount = s.amount
WHEN NOT MATCHED THEN INSERT
  (order_id, customer_id, status, amount, order_date)
  VALUES (s.order_id, s.customer_id, s.status, s.amount, s.order_date);

SELECT * FROM orders ORDER BY order_id;

Metadata lists the files of a table now. It also keeps older lists. That gives you two tools.

Time travel reads a table as it was in the past. Imagine someone runs a wrong UPDATE on orders:

-- The mistake
UPDATE orders SET amount = 0 WHERE status = 'paid';

-- Two minutes later: read the table as it was five minutes ago
SELECT order_id, amount
FROM orders AT(OFFSET => -60 * 5)
ORDER BY order_id;

A clone is a new table that shares the files of the old one. No bytes are copied, so it is fast and free at first:

CREATE TABLE orders_restored
  CLONE orders AT(OFFSET => -60 * 5);

Time travel lasts a limited time. The windows differ by product:

The bill follows the three parts.

This table shows what each product charges for. It has no prices, because prices change:

Always check the current docs of your product before you plan a budget.

Auto-suspend stops an idle warehouse after a set time.

Four surprises

Four things catch many teams. All four are easy to find once you know to look:

1. An idle warehouse that never stops. Auto-suspend may be off. Or a tool sends SELECT 1 every minute, which keeps it awake.
2. A warehouse that stops too fast. Each resume bills the 60-second minimum and clears the local cache.
3. Features with their own credits. In Snowflake, Snowpipe and automatic clustering, a service that keeps table files sorted, bill apart from warehouses. Suspending every warehouse does not stop them.
4. Moving data between regions or clouds. It can add a transfer charge. Check which bill shows it.

When a warehouse surprises you, name the part first. Then pick the fix. This table sums up the chapter:

Reading less data is one way to lower the compute bill.