Star Schema and Data Warehouse Design
Fact and dimension tables, denormalization trade-offs, and OLAP modeling.
Star Schema and Data Warehouse Design is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
OLTP vs OLAP
Data-warehouse questions begin with one distinction interviewers expect you to nail: OLTP vs OLAP.
- OLTP (transactional): many small reads/writes, highly normalized for integrity. Powers the app.
- OLAP (analytical): few large aggregating reads over history, deliberately denormalized for speed. Powers reporting and dashboards.
Star schemas are an OLAP design. The whole point is fast analytical queries, accepting redundancy in exchange.
Facts and Dimensions
A star schema splits data into two kinds of tables:
- Fact table: the measurable events or transactions (a sale, a click). Holds numeric measures and foreign keys to dimensions.
- Dimension tables: the descriptive context you slice by (date, product, customer, store).
The fact sits in the center; dimensions surround it like points of a star, hence the name.
Anatomy of a Fact Table
A fact table is mostly foreign keys plus numeric measures. It is long and narrow and grows continuously.
Measures are additive numbers you aggregate: quantity, revenue, cost. The grain (one row = one ?) must be stated clearly; here one row is one product line on one sale.
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL, -- FK to dim_date
product_key INT NOT NULL, -- FK to dim_product
customer_key INT NOT NULL, -- FK to dim_customer
store_key INT NOT NULL, -- FK to dim_store
quantity INT, -- measure
revenue DECIMAL(12,2), -- measure
cost DECIMAL(12,2) -- measure
);Anatomy of a Dimension Table
Dimensions are short and wide: many descriptive columns you filter and group by. They are intentionally denormalized so a query needs only one join per dimension.
Notice dim_product keeps category and brand in the same row instead of in separate tables. That redundancy is the point: it avoids extra joins at query time.
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id INT, -- natural/business key
product_name VARCHAR(100),
category VARCHAR(50), -- denormalized
brand VARCHAR(50), -- denormalized
unit_price DECIMAL(10,2)
);A Star Schema Query
This is what the design buys you. A typical analytics query joins the fact to a few dimensions, filters, and aggregates. One join per dimension, no deep chains.
Interviewers ask you to write exactly this kind of query against a star schema.
SELECT d.category,
t.year,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date t ON t.date_key = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;Surrogate Keys
Dimensions use a surrogate key: a meaningless integer primary key (like product_key) generated by the warehouse, separate from the source system's natural key.
Why interviewers care:
- It decouples the warehouse from changing business keys.
- It keeps fact tables narrow (integer joins are fast).
- It is required to track history with slowly changing dimensions (next scene).
Slowly Changing Dimensions
A favorite warehouse interview topic: when a dimension attribute changes (a customer moves city), how do you handle it? These are slowly changing dimensions (SCD):
- Type 1: overwrite the old value. No history.
- Type 2: add a new row with effective dates and a current flag. Full history; this needs surrogate keys.
- Type 3: keep a "previous value" column. Limited history.
Type 2 is the most commonly expected answer for tracking change over time.
-- SCD Type 2 dimension
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- surrogate
customer_id INT, -- natural key
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);Star vs Snowflake
Expect the comparison question. A snowflake schema normalizes dimensions into sub-tables (product -> category -> department), where a star keeps them flat.
- Star: fewer joins, faster reads, some redundancy. Preferred for query performance.
- Snowflake: less storage and easier dimension maintenance, but more joins per query.
Say: "Default to star for query speed; snowflake only when dimensions are large and reused."
The Date Dimension
Nearly every star schema has a dedicated date dimension instead of a raw date column. It precomputes year, quarter, month, day-of-week, holiday flags, and fiscal periods.
This lets analysts group by "fiscal quarter" or "is_weekend" with a simple join rather than scattered date functions. Mentioning a date dimension unprompted is a strong signal you have built warehouses.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20250131
full_date DATE,
year INT,
quarter INT,
month INT,
day_of_week VARCHAR(10),
is_weekend BOOLEAN,
fiscal_qtr VARCHAR(6)
);Choosing the Grain
The single most important fact-table decision is the grain: what one row represents. Declare it before anything else.
- Too coarse (one row per day per store) and you lose detail.
- Too fine (one row per scanned item) and the table explodes.
A clear grain statement, like "one row per product per order line," drives which dimensions and measures belong. Interviewers listen for this discipline.
When to Denormalize
Tie it back to normalization. OLTP systems normalize to 3NF for integrity; warehouses deliberately denormalize dimensions for read speed.
The trade-off you must articulate:
- Redundant dimension data is acceptable because the warehouse is loaded by controlled ETL, not ad-hoc app writes.
- Fewer joins means faster aggregations over billions of fact rows.
The judgment, not the rule, is what separates senior answers here.
Quick Check
You are designing a sales data warehouse and need to keep full history of a customer's city when they move.
Recap: Star Schema and Warehouse Design
You can now field warehouse modeling questions:
- OLTP normalizes for integrity; OLAP denormalizes for read speed.
- A star has a central fact table (FKs + numeric measures) surrounded by flat dimensions.
- Use surrogate keys and a dedicated date dimension.
- Track change with SCD Type 2; declare the fact grain first.
- Prefer star over snowflake for query performance.
Frequently asked questions
Is the “Star Schema and Data Warehouse Design” lesson free?
Yes — the full text of “Star Schema and Data Warehouse Design” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Star Schema and Data Warehouse Design”?
Fact and dimension tables, denormalization trade-offs, and OLAP modeling. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Star Schema and Data Warehouse Design” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Normalization Through 3NF
- ER Modeling and Relationship Cardinality
- Star Schema and Data Warehouse Design
- Full Mock Interview Problem Set