Slow analytics queries are almost always a storage design problem, not a hardware problem. Learn how columnar storage, partitioning, and clustering work from first principles — and how to design tables that make your queries 10x faster and cheaper.
Imagine you work at a retail company, and your analytics team needs to answer this question: What was our total revenue across all transactions in Q3 last year? Your transaction table has 500 million rows, each containing a transaction ID, customer ID, store ID, product ID, timestamp, quantity, unit price, discount applied, payment method, and about fifteen other columns. A naive system would read every single column of every single row just to add up one number. That's not a performance problem — it's an architectural one.
This is the problem that modern data warehouses were built to solve. The way data is physically stored on disk has a massive effect on how fast queries run and how much they cost. When you understand the mechanics behind columnar storage, partitioning, and clustering, you stop thinking of slow queries as bad luck and start thinking of them as engineering problems with concrete solutions.
By the end of this lesson, you'll be able to explain why analytical workloads need different storage strategies than transactional databases, design partitioning and clustering schemes for real-world tables, and make informed decisions about how to structure your data warehouse for performance. We'll use BigQuery and Snowflake as reference platforms — the most widely used cloud data warehouses — but the concepts apply across the board.
What you'll learn:
This lesson assumes you're comfortable with basic SQL (SELECT, WHERE, GROUP BY) and understand what a database table is. You don't need prior experience with data warehouses, Snowflake, or BigQuery. If you've ever run a query against a table with millions of rows and wondered why it took so long, you're in exactly the right place.
To understand columnar storage, you first need to understand how traditional databases store data — and why that approach breaks down at scale.
A traditional database like PostgreSQL or MySQL stores data row by row. Think of it like a filing cabinet where each drawer holds one complete customer record: name, email, address, account balance, signup date, and so on — all together in a single physical location on disk. When you insert a new record, everything about that customer goes into one place.
This is great for transactional workloads, which is the technical term for operations like inserting a new order, updating a customer's address, or looking up a single account by ID. These operations touch a small number of complete records, so having all the data for one row together in one place is efficient.
But analytics is a fundamentally different kind of workload. When you ask "what was total revenue in Q3?", you only care about two columns: the timestamp and the revenue amount. In a row-based system, the database still has to read every column — the customer ID, the product ID, the payment method, everything — for every single row, just to get at those two values. You're paying the I/O cost of reading data you immediately throw away.
A columnar store (also called a column-oriented database) flips this on its head. Instead of storing all the data for one row together, it stores all the data for one column together.
Picture the same transaction table. In a columnar system, all 500 million revenue values are stored contiguously in one section of disk. All 500 million timestamp values are stored together in another section. When you ask for total Q3 revenue, the database reads exactly two physical data segments, ignoring everything else entirely.
The performance difference is dramatic. A query that reads 2 out of 20 columns is doing 10% of the I/O compared to a row-based equivalent. At 500 million rows, that's the difference between a query that finishes in seconds and one that runs for minutes.
Columnar storage has a second advantage that's easy to overlook: compression. When all the values in a column are the same data type, and often similar in magnitude, compression algorithms become extraordinarily effective.
Consider a payment_method column with values like "credit_card", "debit_card", "paypal", and "cash". In a columnar store, you have 500 million repetitions of four distinct strings stored consecutively. A simple dictionary encoding — replace each string with a one-byte integer key — shrinks that column by 80% or more. Row-based storage mixes payment method values with customer IDs, timestamps, and product names, making the same compression nearly impossible.
Less data on disk means less data to read, which means faster queries, which means lower costs in cloud systems where you pay by the byte scanned. Columnar storage isn't just an optimization — it's the architectural foundation that makes cloud data warehousing economically viable.
Key insight: Row storage is optimized for reading and writing complete records quickly. Columnar storage is optimized for reading a few columns across many records quickly. Analytics almost always falls into the second category.
Even with columnar storage, scanning hundreds of millions of rows takes time. Partitioning is the technique of physically dividing a large table into smaller, self-contained segments based on the value of one column — the partition key. Queries that filter on the partition key can skip entire partitions they don't need, a technique called partition pruning.
Let's say your transaction table has three years of data, and 90% of your queries ask about the last 30 days. Without partitioning, every query scans the entire table. With partitioning by month, each month's data lives in its own physical segment. A query with WHERE transaction_date >= '2024-06-01' AND transaction_date < '2024-07-01' only reads 1/36th of the total data.
In BigQuery, you'd create this as an ingestion-time partitioned table or a column-partitioned table. A date-partitioned table looks like this:
CREATE TABLE ecommerce.transactions (
transaction_id STRING,
customer_id STRING,
store_id STRING,
product_id STRING,
transaction_date DATE,
quantity INT64,
unit_price NUMERIC,
discount NUMERIC,
payment_method STRING,
revenue NUMERIC
)
PARTITION BY transaction_date;
Now when you run:
SELECT
SUM(revenue) AS total_revenue
FROM ecommerce.transactions
WHERE transaction_date BETWEEN '2023-07-01' AND '2023-09-30';
BigQuery's query planner knows to open only the partitions for July, August, and September 2023. The other 33 months of data are never touched. BigQuery will even tell you this before you run the query — when you paste the SQL into the BigQuery console, it shows you exactly how many bytes will be scanned.
In Snowflake, partitioning works slightly differently. Snowflake automatically divides tables into micro-partitions — immutable, compressed chunks of 50–500 MB of uncompressed data containing a contiguous set of rows. You don't explicitly declare a partition key; instead, Snowflake records the minimum and maximum values of every column in every micro-partition in a structure called the metadata store. This is called micro-partition pruning, and it behaves similarly to explicit partitioning when your data is naturally ordered by date.
The right partition key depends on how your data is queried, not how it's inserted. A few principles:
Cardinality matters. Cardinality refers to the number of distinct values a column has. A partition key should have enough distinct values to create meaningful segments, but not so many that each partition is tiny. Date (or month) is often a near-perfect partition key for time-series data: high enough cardinality to create useful segments, low enough to keep each segment large enough to compress efficiently.
Query patterns should align with partition boundaries. If 90% of your queries filter by date, partition by date. If your users almost always query by region and rarely by date, partition by region. The partition key must appear in WHERE clauses to benefit from pruning.
Avoid low-cardinality keys with skewed distribution. If you partition by status and 95% of your rows have status = 'completed', then one partition holds almost everything and pruning saves almost nothing.
Warning: In BigQuery, if you run a query without a filter on the partition column, BigQuery scans the entire table. This is called a full table scan, and in a pay-per-query model it can be expensive. Always encourage query authors to include partition filters, and consider requiring them through table-level configurations.
Partitioning divides your table into large, coarse segments. Clustering takes things further by sorting data within each partition (or within the whole table, if it's not partitioned) based on one or more columns. This reduces the amount of data scanned at a finer granularity.
Think of your table as a large library. Partitioning is like organizing books into separate rooms by genre — when you want a mystery novel, you only go into the Mystery room. Clustering is like organizing the books within that room alphabetically by author — when you want books by Agatha Christie, you can go directly to the "C" shelf instead of scanning every single mystery novel.
Suppose your transactions table is partitioned by month, but within each monthly partition, the rows are in random insertion order. A query like:
SELECT
SUM(revenue)
FROM ecommerce.transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31'
AND store_id = 'STORE_042';
...still has to scan all rows in the January partition to find the ones matching store_id = 'STORE_042'. If your company has 500 stores, only about 0.2% of January's rows belong to that store.
By clustering on store_id, BigQuery organizes rows within each partition so that all STORE_042 rows are physically adjacent on disk. The query engine can scan a much smaller section of the January partition to answer the question.
In BigQuery, you add clustering to your table definition like this:
CREATE TABLE ecommerce.transactions (
transaction_id STRING,
customer_id STRING,
store_id STRING,
product_id STRING,
transaction_date DATE,
quantity INT64,
unit_price NUMERIC,
discount NUMERIC,
payment_method STRING,
revenue NUMERIC
)
PARTITION BY transaction_date
CLUSTER BY store_id, product_id;
BigQuery supports up to four cluster columns, and the order matters — it's a hierarchical sort, similar to an ORDER BY store_id, product_id clause. Rows are first organized by store_id, and within each store, by product_id. This means filtering on store_id alone will benefit from clustering. Filtering on product_id alone, without a store_id filter, will benefit less.
In Snowflake, you can define a clustering key explicitly:
ALTER TABLE ecommerce.transactions
CLUSTER BY (store_id, product_id);
Snowflake then continuously runs an automated background process called Automatic Clustering to maintain this organization as new data arrives. There's an additional compute cost for this service, so it's worth applying only to large, frequently-queried tables where the savings outweigh the maintenance cost.
Good clustering keys share a few traits:
store_id and product_category, those are your cluster candidates.Tip: In BigQuery, you can check whether clustering is actually helping by using the
INFORMATION_SCHEMA.TABLE_STORAGEand examining query execution details in the BigQuery console. Under "Execution Details" after running a query, you'll see bytes processed — compare this before and after adding clustering to validate the improvement.
Let's walk through a realistic design scenario. You're building a data warehouse for an e-commerce platform. You have two major fact tables:
orders: ~2 billion rows, growing by ~1 million rows/dayevents: ~50 billion rows (user clickstream data), growing by ~100 million rows/dayAnd your analytics team runs two types of queries:
For the orders table:
Partition by order_date (daily partitions). The team always filters by date range, and daily granularity gives you ~730 partitions for two years of data — manageable and meaningful.
Cluster by region, then product_category. Revenue reports filter by region first; product-level breakdowns come second.
CREATE TABLE analytics.orders (
order_id STRING,
customer_id STRING,
region STRING,
product_category STRING,
order_date DATE,
quantity INT64,
unit_price NUMERIC,
revenue NUMERIC,
status STRING
)
PARTITION BY order_date
CLUSTER BY region, product_category;
For the events table:
At 50 billion rows, this is a table where bad design really hurts. Partition by event_date. Cluster by product_category, then event_type. Funnel analysis always scopes to a product category and often filters by specific event types like "product_view" and "add_to_cart".
CREATE TABLE analytics.events (
event_id STRING,
session_id STRING,
customer_id STRING,
product_category STRING,
event_type STRING,
event_date DATE,
event_timestamp TIMESTAMP,
page_url STRING,
referrer STRING
)
PARTITION BY event_date
CLUSTER BY product_category, event_type;
The combination of partitioning and clustering means a query analyzing funnel behavior for "Electronics" in the last 30 days touches only 30 partitions and within each partition, reads only the rows clustered around product_category = 'Electronics'. On a 50-billion-row table, that can reduce scan volume by 98% or more.
You don't need a paid account to practice these concepts. BigQuery has a free sandbox mode that lets you run queries against public datasets.
Step 1: Go to console.cloud.google.com/bigquery and sign in with a Google account. If prompted, create a new project — it's free and takes 30 seconds.
Step 2: In the query editor, run this query against the public NYC taxi dataset. Notice the bytes scanned estimate that appears before you run it:
SELECT
COUNT(*) AS trip_count,
SUM(fare_amount) AS total_fare
FROM `bigquery-public-data.new_york_tlc.green`
WHERE DATE(lpep_pickup_datetime) BETWEEN '2020-01-01' AND '2020-03-31';
Step 3: Now explore the table schema. In the left panel, navigate to bigquery-public-data → new_york_tlc → green. Click the table name, then click the "Details" tab. Look at the "Partitioned by" and "Clustered by" fields if they're present.
Step 4: Write your own query that filters by a non-partitioned column, then try a version with a date filter added. Compare the bytes processed in the query execution details for both.
Step 5: Design (on paper or in a comment block) a partitioning and clustering scheme for the following scenario: A SaaS company tracks user logins. Their analysts always filter by tenant_id (they have 200 enterprise tenants) and often also filter by login_date. The table has 10 billion rows. Write the CREATE TABLE statement with your chosen partition and cluster keys, and write a two-sentence explanation of why you made those choices.
Mistake 1: Partitioning on a high-cardinality column like user_id.
If you have 10 million users and partition by user_id, you get 10 million partitions. Each partition holds a handful of rows — too small to compress effectively, and the metadata overhead of managing millions of partitions starts to hurt performance. Reserve high-cardinality columns for clustering, not partitioning.
Mistake 2: Forgetting to filter on the partition column in queries.
A partition only helps if queries actually use it for filtering. If your team writes queries with WHERE transaction_date IS NOT NULL instead of WHERE transaction_date = '2024-01-15', the partition pruning doesn't kick in. Educate your query authors and use query templates or views that enforce partition filters.
Mistake 3: Clustering on columns that are never in WHERE clauses.
Clustering has a maintenance cost in Snowflake (Automatic Clustering) and a one-time cost in BigQuery (re-sorting data). If you cluster on payment_method but no query ever filters by payment method, you've paid the cost with no benefit. Audit your query patterns before choosing cluster keys.
Mistake 4: Assuming clustering eliminates the need for partitioning. Clustering and partitioning are complementary, not interchangeable. Clustering organizes data within a table or partition — it doesn't skip entire date ranges the way partitioning does. On a two-billion-row table, clustering without partitioning still scans two billion rows (though it reads far fewer bytes per row from the relevant columns). Always partition first on the dominant time dimension, then cluster on secondary filter columns.
Mistake 5: Never revisiting your storage design. Query patterns change. A table that started with low query volume and no performance requirements might now power a production dashboard used by 200 people. If queries are slow and costs are high, revisit your partition and cluster keys against current query patterns. Both BigQuery and Snowflake let you alter clustering keys on existing tables without rebuilding everything from scratch.
Let's bring it all together. A modern data warehouse uses columnar storage as its foundation — storing each column's values contiguously on disk — which allows queries to read only the columns they need and compress data aggressively. On top of that foundation, partitioning divides tables into physical segments based on a key column, allowing the query engine to skip entire partitions for queries that filter on that key. Clustering goes one level deeper, sorting data within partitions by one or more additional columns so that queries with secondary filters can skip the irrelevant portions of a partition.
The three techniques work together hierarchically: columnar storage reduces bytes-per-column, partitioning reduces partitions-scanned, and clustering reduces rows-scanned within partitions. A well-designed table uses all three, and the design choices are driven entirely by the actual query patterns of the people using the data.
Designing storage well is one of the highest-leverage skills in data engineering. A 10x reduction in query cost and runtime is achievable — not through magic, but through understanding these mechanics and applying them deliberately.
Where to go next: