Fact Table vs Dimension Table
People mix up fact tables and dimension tables, and they hold different things. A fact table holds the numbers from events, one row per sale or click, and it grows fast. A dimension table holds the words you slice by, such as product or store. It is short and wide. Reports sum the facts and group by the dimensions.
Fact Table
The table of events you count, such as sales. It holds numbers and keys.
A fact table holds the events a business measures. Each row is one event, such as one sale or one click. It stores numbers, such as amount and quantity. It also stores keys that point to dimension tables, such as the date and the product. Fact tables are long and thin, and often the biggest tables in a warehouse.
Dimension Table
A table that names the things in the facts, such as shops and dates.
A dimension table describes the things in your facts. Each row is one thing, such as one product, one shop or one day. It holds words people filter and group by, such as product name, city or month. A fact row points to it with a key. Dimension tables are short and wide, and change much less often than facts.
In one line
- Fact Table
- The table of events you count, such as sales. It holds numbers and keys.
- Dimension Table
- A table that names the things in the facts, such as shops and dates.