Data Warehouse Tutorial: Learn DW from Scratch (2026)
A data warehouse centralizes data from multiple sources for reporting and analytics, transforming raw operational data into structured, query-optimized information. In my years designing analytic systems, I have found that the difference between a query that runs in seconds and one that takes hours is almost always the warehouse schema design and ETL pipeline architecture. This tutorial covers dimensional modeling, ETL/ELT processes, OLAP vs OLTP systems, and modern cloud data warehouses.
We will design a star schema from scratch, build ETL pipelines, and explore how modern tools like Snowflake, BigQuery, and Redshift handle massive-scale analytics. The focus is on practical design decisions that directly impact query performance and maintainability.
OLTP vs OLAP Systems
Online Transaction Processing (OLTP) systems handle high-volume, low-latency transactions with row-oriented storage — think e-commerce order processing or banking transactions. They are optimized for INSERT/UPDATE/DELETE with ACID guarantees. Online Analytical Processing (OLAP) systems handle complex queries scanning millions of rows with column-oriented storage, optimized for aggregation and read throughput. Data warehouses are OLAP systems, whereas the source systems feeding them are typically OLTP. The Extract-Transform-Load (ETL) process bridges these two worlds.
-- OLTP: normalized schema optimized for writes
CREATE TABLE orders_oltp (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
order_ts TIMESTAMP
);
CREATE INDEX idx_orders_customer ON orders_oltp(customer_id);
-- OLAP: denormalized star schema for aggregation queries
CREATE TABLE fact_sales (
order_id INT,
customer_key INT,
product_key INT,
date_key INT,
quantity INT,
revenue DECIMAL(10,2)
) DISTKEY(product_key) SORTKEY(date_key);Dimensional Modeling: Star and Snowflake Schemas
Dimensional modeling organizes data into fact and dimension tables. Fact tables store quantitative measures (sales amount, quantity) and foreign keys to dimension tables. Dimension tables store descriptive attributes (customer name, product category, date). The star schema has facts in the center surrounded by denormalized dimension tables, enabling simple queries with few joins. The snowflake schema normalizes dimensions into sub-dimensions (e.g., product -> category -> department), saving storage but requiring more joins. In practice, star schemas dominate because query simplicity usually outweighs storage savings.
-- Star schema for retail analytics
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id INT,
name VARCHAR(100),
email VARCHAR(255),
city VARCHAR(50),
state VARCHAR(20),
zip VARCHAR(10),
segment VARCHAR(20)
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_id INT,
name VARCHAR(100),
category VARCHAR(50),
subcategory VARCHAR(50),
unit_price DECIMAL(10,2)
);
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
year INT, quarter INT, month INT, day INT,
day_of_week VARCHAR(10)
);
CREATE TABLE fact_sales (
order_id INT,
customer_key INT REFERENCES dim_customer,
product_key INT REFERENCES dim_product,
date_key INT REFERENCES dim_date,
quantity INT,
unit_price DECIMAL(10,2),
discount DECIMAL(5,2),
revenue DECIMAL(12,2)
);ETL Pipeline Design
ETL (Extract, Transform, Load) moves data from source systems to the warehouse. Extract reads from operational databases (CDC via binary logs, incremental timestamps, or full snapshots), APIs, or flat files. Transform cleans, deduplicates, joins, aggregates, and converts data types. Load inserts into dimension and fact tables, handling slowly changing dimensions (SCD Type 1 overwrites, Type 2 adds new rows with effective dates). Modern ELT (Extract, Load, Transform) loads raw data first and transforms within the warehouse using SQL, leveraging warehouse compute power.
# Simple ETL pipeline (pseudo-code)
def etl_pipeline(extract_date):
source_df = read_source_db(f"SELECT * FROM orders WHERE date >= '{extract_date}'")
customer_df = read_source_db("SELECT * FROM customers")
clean_orders = source_df.dropna(subset=['customer_id', 'product_id'])
clean_orders['revenue'] = clean_orders['quantity'] * clean_orders['price']
clean_orders['date_key'] = clean_orders['order_date'].apply(
lambda d: int(d.strftime('%Y%m%d')))
load_dimension(dim_customer, customer_df, scd_type=1)
load_fact(fact_sales, clean_orders)
print(f"Loaded {len(clean_orders)} sales records for {extract_date}")Slowly Changing Dimensions
Dimension attributes change over time — customers move, products get re-categorized. SCD Type 1 overwrites the old value, losing history (simple but no audit trail). Type 2 creates a new row with effective dates, preserving full history (most common). Type 3 adds a 'previous' column, tracking only the last change. The choice depends on business requirements: for a 'customer address at time of order' report, Type 2 is necessary. Type 2 dimensions grow unbounded, so partitioning by date is essential for performance.
-- SCD Type 2: customer address changes tracked with effective dates
CREATE TABLE dim_customer_scd2 (
customer_key INT PRIMARY KEY,
customer_id INT,
name VARCHAR(100),
address VARCHAR(200),
effective_date DATE NOT NULL,
end_date DATE,
is_current BOOLEAN DEFAULT TRUE
);
-- Update: expire old record, insert new
UPDATE dim_customer_scd2
SET end_date = '2026-07-07', is_current = FALSE
WHERE customer_id = 42 AND is_current = TRUE;
INSERT INTO dim_customer_scd2
(customer_key, customer_id, name, address, effective_date)
VALUES (nextval('cust_seq'), 42, 'Alice', '456 New St', '2026-07-08');Columnar Storage and Query Optimization
Modern cloud warehouses (Snowflake, Redshift, BigQuery) use columnar storage: each column is stored separately, enabling queries to read only the columns they need. Columnar storage also enables high compression ratios because adjacent values in a column are similar. Zone maps and min-max indexes skip entire blocks that do not match query predicates. Distribution keys (Redshift) or clustering keys (Snowflake) co-locate related data to minimize shuffling. Sort keys enable efficient range scans. Choosing the right distribution and sort keys can improve query performance by 10-100x.
-- Redshift: distribution and sort key design
CREATE TABLE fact_orders (
order_id BIGINT DISTKEY,
customer_id INT,
order_date TIMESTAMP SORTKEY,
status VARCHAR(20),
total DECIMAL(12,2),
shipping_cost DECIMAL(8,2)
) DISTSTYLE KEY;
SELECT DATE_TRUNC('month', order_date) as month,
COUNT(*), SUM(total)
FROM fact_orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY 1
ORDER BY 1;Modern Cloud Data Warehouses
Snowflake separates storage and compute: data is stored in compressed columnar format in cloud object storage (S3/Azure Blob), while virtual warehouses (clusters of EC2 instances) provide compute, scaling independently. BigQuery is serverless — no sizing required, storage and compute are fully separated. Redshift offers dense compute nodes with local SSD storage. Data lakehouses (Databricks, Apache Iceberg) combine data lake flexibility with warehouse features like ACID transactions and schema enforcement. The trend is toward open formats (Parquet, Iceberg) and separation of compute and storage.
-- Snowflake: virtual warehouse sizing
CREATE WAREHOUSE analytics_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 10
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
SELECT COUNT(DISTINCT customer_id), SUM(total)
FROM sales
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE());Frequently Asked Questions
What is the difference between a data warehouse and a data lake?
A data warehouse stores structured, processed data optimized for SQL analytics. A data lake stores raw data in native format (structured, semi-structured, unstructured) for flexibility. Data warehouses enforce schema-on-write; data lakes use schema-on-read.
When should I use a star schema over a snowflake schema?
Use star schema for most analytics — queries are simpler with fewer joins. Use snowflake when dimension tables are very wide and storage savings from normalizing sub-dimensions justifies the query complexity, or when you need to maintain a single source of truth for shared dimension hierarchies.
How do you handle incremental loads in a data warehouse?
Use CDC (change data capture) from database logs, timestamp-based incremental extraction (WHERE updated_at > last_extract), or watermark tables tracking the last processed record. Merge/Upsert statements insert new rows and update changed ones.
What is the role of an OLAP cube?
An OLAP cube pre-aggregates measures across multiple dimensions, enabling sub-second drill-down and roll-up queries. Modern columnar warehouses often eliminate the need for pre-computed cubes by scanning data efficiently in real-time, but cubes remain valuable for ultra-low-latency dashboards.
Originally published on Ayodhyyya. Last updated June 1, 2026.