Fact Tables: Where the Numbers Live
A fact table stores the measurable events in your business: sales transactions, page views, shipments, support tickets, or any other activity you want to count, sum, or average. Each row represents one event, and each column holds either a measurement — revenue, quantity, duration — or a foreign key that links to a dimension table providing context about that event.
Fact tables tend to be wide and tall. They accumulate rows over time and rarely get updated after insertion, making them well-suited to an append-only pattern. This characteristic makes them efficient to partition by date, which in turn makes time-range queries fast because the engine scans only the relevant partition rather than the entire table history.
The key design decision is grain: how specific is each row? A fact table where each row is one line item on one order gives you more analytical flexibility than a table where each row is one order total. The finer grain generates more rows and requires more storage, but it preserves detail that cannot be reconstructed later if the data is aggregated too early in the pipeline. Choose grain based on the questions your analysts actually ask, not on a theoretical ideal.
Dimension Tables: Context for Every Fact
Dimension tables describe the who, what, where, and when of each fact. A product dimension contains product names, categories, and attributes. A customer dimension holds names, regions, and segments. A date dimension provides calendar attributes like day of week, fiscal quarter, and holiday flags that let analysts group and filter facts across time in ways that raw timestamps cannot support natively.
Dimensions are typically small relative to fact tables and change slowly. When a product name changes or a customer moves to a new region, the dimension row updates rather than generating a new fact. This separation of stable descriptive data from high-velocity event data is what makes the star schema efficient and maintainable over long periods.
Queries join the fact table to one or more dimensions, and because dimensions are compact, those joins are fast even at large scale. A well-designed dimension table also makes queries more readable — filtering by product category name is easier to understand and audit than filtering by a numeric category code that only the database administrator can interpret.
Why Star Schemas Speed Up Queries
Query engines optimize for the star join pattern. When a query filters on a dimension — for example, selecting sales in a specific region during a specific quarter — the engine resolves the dimension filter first, retrieves a small set of matching keys, and then scans only the fact rows that match those keys. This two-step process avoids scanning the entire fact table and dramatically reduces compute time for filtered queries.
The predictability of the star layout also helps with indexing and partitioning strategies. Because fact tables have a regular structure with foreign keys pointing to known dimensions, the database can create efficient indexes and partition plans without custom tuning for each individual query pattern. This regularity lowers the ongoing maintenance burden on the data engineering team.
Run the cost worksheet on the home page to see how reduced scan volume translates to lower compute costs for your workload. Fewer bytes scanned means less processing time per query, which directly lowers the compute line item on your warehouse bill. For organizations running hundreds of dashboard queries per day, the aggregate savings from efficient schema design are substantial.
When to Use a Star Schema and When to Consider Alternatives
Star schemas fit well when your analytical workload revolves around structured business questions: revenue by region, conversions by channel, inventory by warehouse, support tickets by priority level. These queries map naturally to fact-dimension joins, and the performance benefits are immediate and measurable.
The pattern fits less well when your data is highly unstructured, when relationships between entities are complex and many-to-many, or when your primary workload is machine learning training rather than BI reporting. Graph databases, document stores, or denormalized wide tables may serve those use cases more efficiently because they avoid the rigid table structure that gives star schemas their performance advantage.
Even in a mixed environment, a star schema for the structured reporting layer alongside a data lake for exploratory and ML workloads is a common and effective combination. The warehouse handles the questions that executives and analysts ask repeatedly, while the lake handles the open-ended exploration that data scientists need. The warehouse-vs-lake guide on this site covers how to set boundaries between the two layers and manage the data flow between them.
Schema design choices depend on your specific query patterns and data volume — the star pattern is a strong default, not a universal mandate.