Every data warehouse and almost every Power BI model is built from two kinds of tables: facts and dimensions. If you are preparing for an ETL testing, data analyst or BI interview in India, you will almost certainly be asked the difference. Here it is, with examples.
What Is a Fact Table?
A fact table stores measurable events: sales, payments, clicks, claims. Each row is one event (or one summary) and contains measures (numbers you add up) plus foreign keys to dimensions. Fact tables are usually long (millions of rows) and narrow.
What Is a Dimension Table?
A dimension table stores the descriptive context of those events: who, what, where and when. Customer, product, store and date are classic dimensions. They are usually shorter and wider, with text attributes used for filtering and grouping.
Fact vs Dimension Table: The Difference
| Fact table | Dimension table | |
|---|---|---|
| Stores | Measures (amount, quantity, count) | Descriptive attributes (name, city, category) |
| Answers | "How much / how many?" | "Who, what, where, when?" |
| Size | Many rows, few columns | Fewer rows, many columns |
| Keys | Foreign keys to dimensions (+ degenerate keys) | Surrogate primary key + business key |
| Changes | Mostly inserts | Updates tracked with SCD types |
| Example | fact_sales | dim_customer, dim_product, dim_date |
Worked Example: An Online Order
Priya from Pune buys 2 phone cases for ₹600 on 1 October 2026. In the warehouse:
| Table | Row stored |
|---|---|
| dim_customer | customer_key 501, customer_id C1023, name Priya, city Pune |
| dim_product | product_key 77, sku PC-100, name Phone case, category Accessories |
| dim_date | date_key 20261001, date 2026-10-01, month October, calendar Q4 (Q3 of the Indian financial year) |
| fact_sales | date_key 20261001, customer_key 501, product_key 77, quantity 2, sales_amount 600 |
-- Sales by city: measure from the fact, grouping from the dimension SELECT c.city, SUM(f.sales_amount) AS sales, SUM(f.quantity) AS units FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key GROUP BY c.city;
Types of Fact and Dimension Tables
Types of facts
- Transaction fact: one row per event (each sale).
- Periodic snapshot fact: one row per period (daily account balance).
- Accumulating snapshot fact: one row per process, updated as it moves (order placed, shipped, delivered).
- Factless fact: records that something happened, with no measure (student attended class).
Measures can be additive (sales amount), semi-additive (account balance: add across accounts, not across days) or non-additive (ratios, percentages).
Types of dimensions
- Conformed: shared by several facts (date, customer).
- Slowly changing (SCD): keeps history when attributes change. See SCD Type 1, 2 and 3.
- Role-playing: one dimension used in several roles (order date, ship date).
- Junk: small flags and indicators grouped into one table.
- Degenerate: an ID such as order number stored in the fact with no dimension table.
Fact and Dimension Tables in Power BI
In Power BI, dimensions sit on the "one" side and facts on the "many" side of relationships. Slicers and filters should use dimension columns; measures aggregate fact columns. This is the star schema pattern Power BI is designed for.
How ETL Testers Test Facts and Dimensions
| Table | Key checks |
|---|---|
| Dimension | No duplicate business keys (current rows), SCD history correct, unknown member row exists, attributes match source |
| Fact | Counts and totals match source, no orphan foreign keys, correct grain (no double counting), late-arriving rows handled |
-- Fact grain check: one row per order line expected SELECT order_id, line_no, COUNT(*) FROM fact_sales GROUP BY order_id, line_no HAVING COUNT(*) > 1;
More: data warehouse testing and data reconciliation.
Data warehouse basics: data warehouse concepts · star vs snowflake schema · SCD types · data reconciliation · data warehouse testing · data warehouse interview questions
More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · ETL testing notes · work from home ETL jobs
Frequently Asked Questions
What is the difference between a fact table and a dimension table?
A fact table stores measurable events and numbers such as sales amount and quantity, with keys to dimensions. A dimension table stores descriptive attributes such as customer name, product category and date, used to filter and group facts.
Can a table be both a fact and a dimension?
Rarely in a clean design. Some tables act as a dimension in one model and a fact in another, but each table normally has one role in a given star schema.
What is the grain of a fact table?
The grain is what one row represents, for example one order line per day. Defining it clearly prevents double counting.
Which is bigger, fact or dimension table?
Fact tables usually have far more rows; dimension tables usually have more columns.
What is a factless fact table?
A fact table with no numeric measures that records that an event happened, such as attendance or a product being on promotion.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.