In data warehousing, what are fact tables and dimension tables, and how do they differ in purpose and structure?

Introduction / Context:
Fact tables and dimension tables are the core building blocks of a data warehouse or business intelligence solution. Interviewers frequently ask this question because understanding these two table types is essential for designing star schemas, writing analytical queries, and building dashboards that support management decisions.

Given Data / Assumptions:

• The question is about data warehousing, not OLTP transaction databases.
• We are dealing with star or snowflake schemas designed for reporting and analytics.
• Business users need to analyze numeric metrics such as sales, revenue, quantity, or counts.
• They also need descriptive context like product names, dates, regions, and customer segments.

Concept / Approach:
In dimensional modeling, tables are divided into facts and dimensions. Fact tables contain the measurable events of the business, usually numeric metrics that can be aggregated, as well as foreign keys to related dimension tables. Dimension tables provide rich descriptive attributes that explain the who, what, when, where, and how of each fact. Separating measures from descriptive context makes queries simpler and improves performance for analytical workloads.

Step-by-Step Solution:
Step 1: Define a fact table as a central table that stores business events, for example individual sales transactions or daily inventory levels. Step 2: Note that fact tables typically contain foreign keys referencing dimension tables plus measures such as amount, quantity, or cost. Step 3: Define a dimension table as a supporting table that stores descriptive attributes like product name, category, brand, customer demographics, or region. Step 4: Explain that analysts join fact tables to dimensions to slice and dice measures by different perspectives, such as sales by month, by region, or by product category. Step 5: Emphasize that fact tables are usually wide and very large, while dimensions are relatively smaller but rich in textual attributes.

Verification / Alternative check:
A typical star schema diagram clearly shows a central fact table such as Fact_Sales connected via foreign keys to dimensions like Dim_Date, Dim_Product, and Dim_Customer. SQL queries for reports almost always select measures from the fact table and join out to dimension tables to filter or group by their attributes, confirming the conceptual roles of each table type in practice.

Why Other Options Are Wrong:
Option B is incorrect because fact tables are primarily numeric and key based, and dimension tables contain descriptive text and codes, not only numbers. Option C incorrectly places fact and dimension tables in OLTP and file systems, which is not how dimensional models are used. Option D claims there is no difference, but in dimensional modeling the distinction between facts and dimensions is fundamental to schema design.

Common Pitfalls:
A common mistake is to put too many descriptive attributes into the fact table, which increases its size and complexity and slows queries. Another pitfall is under designing dimension tables with too few attributes, limiting the types of analysis users can perform. Good dimensional models strike a balance by keeping facts lean and numeric while giving dimensions rich, well organized descriptive attributes.

Final Answer:
A fact table stores numeric business measures plus foreign keys to descriptive dimensions, and a dimension table stores contextual attributes like time, product, or customer that are used to analyze those measures.