Fact Table: The Numbers in Your Data Warehouse
A fact table is the ledger of business events, recording what happened and how much. It's the core of a data warehouse, holding sales figures or page views. The footgun is storing descriptive text here; that belongs in linked dimension tables.
Why it exists
Data warehouses need to answer business questions by analyzing large volumes of historical data. To do this efficiently, data is separated into quantitative measurements (facts) and descriptive context (dimensions). Fact tables were created to store these measurements in a compact, query-optimized format.
The mental model
Think of a fact table as a detailed transaction log or event stream. Each row is an event: a sale, a click, a login. The columns are either numbers representing what happened (e.g., sale_amount, quantity) or foreign keys pointing to dimension tables that provide context (e.g., product_id, customer_id, date_id). It answers "how much?" and "how many?".
How it works
A fact table has two column types. The first contains the actual measurements or "facts," which are almost always numeric (e.g., dollars, units, weight). These are called measures. The second type consists of foreign keys that link to the primary keys of surrounding dimension tables. The primary key of the fact table itself is typically a composite key made up of all these foreign keys, uniquely identifying each recorded event.
When to use it
Use a fact table as the core component of any data warehouse or analytics database designed with a star or snowflake schema. It's ideal for storing historical, transactional, or event-based data that you intend to aggregate for business intelligence, such as sales records, web analytics events, or financial transactions.
When not to use it
Avoid using a fact table structure for OLTP (Online Transaction Processing) systems, like a production e-commerce database's users or products table. These systems prioritize fast writes and updates on individual records, whereas fact tables are optimized for bulk loads and large-scale analytical queries. Also, never store descriptive text data directly in a fact table; that's what dimension tables are for.
One canonical example
A retail sales fact table might have columns for measures like units_sold and sale_amount_usd. It would also have foreign key columns like date_key, store_key, and product_key. A query could then join this fact table with the store dimension table to sum up sale_amount_usd for all stores in a specific region, providing a fast, aggregated answer.
Interview question
Which characteristic primarily defines a fact table's role in a data warehouse?
- a.It is optimized for fast, real-time updates of individual records.
- b.Its primary key is always a single, auto-incrementing integer.
- c.It stores descriptive textual information about business entities.
- d.It records quantitative measurements and foreign keys to contextual dimensions.Correct
Why? this is the answer
Fact tables are designed to store numeric measurements (facts) of business events and foreign keys that link to dimension tables for context. Option A describes OLTP systems, not fact tables, which are optimized for analytical queries and bulk loads.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #data warehousing
- #databases
- #star schema
- #architecture
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles