Star Schema: A Data Warehouse Pattern You Should Understand

Imagine that the business asks this question:

What was the total sales amount for electronic products in the Bali region last month?

If the data is still spread across a highly normalized transactional database, one question may need many JOINs: order, item, product, category, store, region, customer, and address. The query becomes harder to write, maintain, and understand for BI users.

Star schema is a modeling pattern for an analytics database or data warehouse. It organizes analytics data so questions like this can be answered with simpler queries by separating measurable events from the context that explains them.

Basic Concept of Star Schema

A star schema has one fact table in the center and several dimension tables around it. The relationships form a shape like a star.

Loading diagram...

Every dimension table connects directly to fact_sales, so the diagram shows the star schema pattern without relying on mindmap syntax. Mermaid uses automatic layout, so the final node positions may vary between versions.

Fact Tables and Dimension Tables

Fact table: measurable events

A fact table stores the events or transactions that you want to analyze. Examples include sales, payments, deliveries, and page views.

Common characteristics:

• One row represents one event at a specific level of detail.
• The table usually has a large number of rows.
• Many columns are measures, such as quantity, revenue, and discount.
• Foreign key columns connect the table to dimension tables.

Simple example:

date_key	product_key	store_key	customer_key	order_id	quantity	revenue
20240115	402	12	8817	ORD-9912	2	150000
20240115	118	12	8817	ORD-9912	1	75000

order_id in this example is called a degenerate dimension. The transaction attribute stays in the fact table, but it does not become a separate dimension table.

Dimension table: context for reading facts

A dimension table stores the context that explains a fact: which product, which customer, when it happened, and where it happened.

Example of dim_product:

product_key	product_name	category	brand	unit_price
402	Keyboard Mech	Electronics	LogiTech	75000

The fact table answers how much. Dimension tables help answer what, who, when, and where.

Grain: The First Design Decision

Grain defines what one row in a fact table represents.

For sales data, possible grains include:

• one row for each product item in a transaction;
• one row for each complete transaction;
• one row for the total sales of each store per day.

You must define the grain before choosing columns and writing the pipeline. If the fact table is summarized by day, you can no longer answer questions about the items in one transaction.

For flexible analysis, start with the most detailed grain that is still practical. You can aggregate detailed data later, but you cannot easily restore data after it has been summarized.

Common term at work

During a data model review, a question such as “What is the grain of this fact table?” helps the team confirm that everyone understands what each row means.

Slowly Changing Dimensions

Dimension tables can also change. A customer may move to another city, a product may change category, and a store name may be updated.

The question is whether old reports should use the latest value or keep the context from the time when the transaction happened.

SCD Type 1: overwrite

The old value is replaced directly. The history is not kept.

Use this approach when the old value was incorrect, or when the change does not need historical analysis. One example is fixing a typo in a customer name.

SCD Type 2: keep history

Every change creates a new row. Each row has a validity period and a flag that shows whether it is still active.

Loading diagram...

With SCD Type 2, old facts still point to customer key 8817, while new facts point to 9042. Historical reports can show the customer's location when each transaction actually happened.

Common columns include:

customer_key	name	city	valid_from	valid_to	is_current
8817	Budi	Bandung	2022-01-01	2024-01-10	false
9042	Budi	Jakarta	2024-01-11	NULL	true

In data warehouse work, SCD Type 1 and Type 2 are the two most important patterns to understand.

Aggregate Tables for Performance

A large fact table does not always need to be read in full for every dashboard. Data teams often create an aggregate table, which is a table summarized for a specific query pattern.

Loading diagram...

Examples:

• fact_sales: 10 billion rows with item and transaction details.
• agg_sales_daily: sales summarized by store and day.
• agg_sales_monthly: sales summarized by store and month.

Aggregate tables make frequently used dashboards faster. However, metric definitions must stay consistent with the fact table so reports do not show different numbers.

Star Schema and Snowflake Schema

In a snowflake schema, dimension tables are normalized further. For example, dim_product can be split into dim_product, dim_category, and dim_brand.

Aspect	Star schema	Snowflake schema
Number of JOINs	Fewer	More
Redundancy in dimensions	Slightly more	Less
Ease of use for BI users	Easier	More complex

For everyday analytics, star schema is usually the default because its queries and model are easier to understand. Snowflake schema is still useful when the extra normalization provides a clear benefit.

When Does Star Schema Fit?

Star schema fits when:

• data will be used for dashboards, reporting, or repeated analysis;
• business users need to filter and aggregate data by clear context;
• several fact tables need to use the same dimensions;
• the team needs consistent metric definitions.

Star schema is not the only pattern. Highly exploratory data or events with frequently changing structures may be better stored in a more flexible form first. Once the analysis needs become stable, the data can be modeled as a star schema.

Summary

• Fact tables store events and measurable values.
• Dimension tables store context such as product, customer, time, and location.
• Grain defines what one fact row means and must be decided early.
• SCD Type 1 replaces old values, while SCD Type 2 keeps history in new rows.
• Aggregate tables speed up queries by storing summarized results.
• Star schema is often the default for the analytics layer because it is easy for BI tools and users to understand.

When you design a model, start with three questions:

1. What event do you want to measure?
2. What is the grain of one row?
3. What context is needed to filter and explain the event?