DWH Interview Question 44 - How do you make a data warehouse fast?

Data Warehouse Interview Q&A · Question 44

In this post we are going to cover data warehouse performance, and answer an open interview question: how do you make a data warehouse fast?

The short answer is one idea: make the engine read less data. Columnar storage, partitioning, clustering, indexes and pre-aggregation are all different ways to do that. A good interview answer names each technique and explains how it reduces the data a query has to read.

We will go through each lever, and use a small fact table to show the one mistake that quietly switches partition pruning off.

Question 44 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Read less: columns and compression
3. Partitioning and pruning
4. Let's create some sample data
5. Step 1: A filter that can prune
6. Step 2: The same question with a function on the column
7. Step 3: Clustering, sort keys and indexes
8. Pre-aggregate, and the checklist
9. Follow-up questions interviewers ask
10. The full script

The interview answer

Say this first

Make the engine read less data. Use columnar storage and select only needed columns; partition big facts by date so filters prune partitions; cluster or sort by common filters (or index in row stores); pre-aggregate with aggregate tables or materialized views; and keep a clean star schema, fresh statistics and filters on raw columns.

Read less: columns and compression

Start with how data is stored. Modern warehouses store data by column. A query that needs two columns reads two columns, not the whole row.

• SELECT * on a wide fact table reads every column, which makes it the most expensive habit you can have.
• Naming only the columns you need, like sale_date and amount, reads just those two.
• Columns also compress very well, because similar values are stored together. Less data on disk means less data to read.

Partitioning and pruning

A large fact table is split into partitions, usually by date: one partition per month, or per day.

When a query asks for June, the engine checks each partition's date range and skips the ones that cannot match. This is called partition pruning. With monthly partitions for May, June and July, a June query reads one partition out of three. On a table with five years of daily partitions, a one-day query reads one partition out of more than eighteen hundred.

Let's create some sample data

We need one small fact table with sales from May, June and July 2026. Think of it as a table partitioned by month.

Sample data — run this first

DROP TABLE IF EXISTS fact_sales;

CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, sale_date DATE, product_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
  (1, '2026-05-20', 1, 10.00),
  (2, '2026-05-28', 2, 25.00),
  (3, '2026-06-02', 1, 15.00),
  (4, '2026-06-15', 3, 40.00),
  (5, '2026-06-30', 2, 20.00),
  (6, '2026-07-01', 1, 30.00);

Query

SELECT * FROM fact_sales ORDER BY 1;

Result6 rows · 4 columns

sale_id	sale_date	product_key	amount
1	2026-05-20	1	10.00
2	2026-05-28	2	25.00
3	2026-06-02	1	15.00
4	2026-06-15	3	40.00
5	2026-06-30	2	20.00
6	2026-07-01	1	30.00

Six sales: two in May, three in June and one on July 1. The June sales are 15.00, 40.00 and 20.00.

Step 1: A filter that can prune

Pruning only works if we write the filter the right way. Let's get June sales with a plain range on the raw sale_date column: on or after June 1, and before July 1.

Query

SELECT SUM(amount) AS june_sales
FROM fact_sales
WHERE sale_date >= DATE '2026-06-01'
  AND sale_date <  DATE '2026-07-01';

Result1 row · 1 column

june_sales
75.00

June sales are 75.00. On a table partitioned by month, the engine can compare this range with each partition's minimum and maximum date and skip May and July entirely, because the filter is on the column itself.

Step 2: The same question with a function on the column

Now let's ask the same question the way many people write it: extract the year and the month from the date and compare those. In SQL Server this would be MONTH(sale_date).

Query

SELECT SUM(amount) AS june_sales
FROM fact_sales
WHERE EXTRACT(YEAR FROM sale_date) = 2026
  AND EXTRACT(MONTH FROM sale_date) = 6;

Result1 row · 1 column

june_sales
75.00

The answer is the same, 75.00. But now the engine has to compute the function on every row before it can filter, so it cannot use the partition ranges and reads every partition. Same result, many times the cost.

Rule

Always filter on the raw column, with a range, so the engine can prune.

Step 3: Clustering, sort keys and indexes

Inside a partition, the order of the data matters too. If rows are sorted or clustered by a column we often filter on, like customer or product, each storage block holds a narrow range of values, and the engine skips blocks the same way it skips partitions. Each platform has its own name for this:

Platform	Feature
Snowflake	clustering keys
BigQuery	partitioning + clustered columns
Redshift	sort keys
SQL Server	ordered columnstore

On row-store databases, we use an index on the column we filter and join on most. Let's add one on sale_date.

Statement

CREATE INDEX ix_fact_sales_date
ON fact_sales (sale_date);

The index lets a row store jump straight to the dates it needs instead of scanning the whole table. But don't over-index a fact table: every index costs storage and slows every load.

Pre-aggregate, and the checklist

The last big lever is to not compute the same thing twice. If every dashboard sums sales by month and category, we store that answer in an aggregate table or a materialized view and refresh it after each load. Queries then read a few hundred rows instead of millions.

Then the basics: keep statistics up to date so the optimizer picks good plans, join on integer surrogate keys, and keep the star schema simple. Here is the full checklist:

• Columns - select only what you need.
• Partitions - by date, with filters on raw columns.
• Clustering, sort keys or indexes - on the most common filters.
• Pre-aggregate - aggregate tables and materialized views.
• Basics - fresh statistics, integer surrogate keys, simple stars.

Put it all together and you have the answer: read less data, at every level.

Follow-up questions interviewers ask

Why does a function on a column hurt performance?

The engine must compute it on every row before filtering, so it cannot prune partitions or use an index.

What is partition pruning?

Skipping partitions whose value range cannot match the filter, so they are never read.

Should you index every column of a fact table?

No: indexes cost storage and slow every load; index only frequent filter and join columns (in row stores).

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #44 - Data warehouse performance
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q44;  SET search_path TO dwh_q44;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;

CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, sale_date DATE, product_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
  (1, '2026-05-20', 1, 10.00),
  (2, '2026-05-28', 2, 25.00),
  (3, '2026-06-02', 1, 15.00),
  (4, '2026-06-15', 3, 40.00),
  (5, '2026-06-30', 2, 20.00),
  (6, '2026-07-01', 1, 30.00);


-- Step 1: Filter that can prune: a plain range on the partition column
SELECT SUM(amount) AS june_sales
FROM fact_sales
WHERE sale_date >= DATE '2026-06-01'
  AND sale_date <  DATE '2026-07-01';

-- Step 2: Same answer, but a function on the column blocks pruning (SQL Server: MONTH(sale_date))
SELECT SUM(amount) AS june_sales
FROM fact_sales
WHERE EXTRACT(YEAR FROM sale_date) = 2026
  AND EXTRACT(MONTH FROM sale_date) = 6;

-- Step 3: An index (row stores) on the column you filter and join on most
CREATE INDEX ix_fact_sales_date
ON fact_sales (sale_date);

Key points

• Read less data, at every level
• Columnar storage, no SELECT *
• Partition by date; filter on raw columns
• Cluster / sort / index by common filters
• Pre-aggregate; keep statistics fresh

Next in this series

Question 45 — Design a star schema: full interview answer Design a star schema for an online store

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.