What is a fact table, and how does it differ from a dimension table?
Explanation:
A fact table is a central table in a star schema of a data warehouse. It stores quantitative data for analysis and is typically denormalized. Fact tables record measurements or metrics for a specific event, such as sales transactions or log entries, and are often highly normalized.
In contrast, a dimension table contains descriptive attributes related to the fact data. These attributes help to filter, group, or summarize data stored in the fact table. Dimension tables typically contain textual or categorical data, such as product names, customer demographics, or dates.
Key Talking Points:
-
Fact Table:
- Contains quantitative data.
- Stores measurable metrics or facts.
- Often has a large number of rows.
- Contains foreign keys referencing dimension tables.
-
Dimension Table:
- Contains descriptive attributes.
- Stores data to describe dimensions of facts.
- Typically smaller in size compared to fact tables.
- Contains primary keys referenced by fact tables.
NOTES:
Reference Table:
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Data Type | Quantitative (numeric) | Qualitative (descriptive) |
| Purpose | Store measurable data | Store descriptive information |
| Row Count | High | Relatively low |
| Keys | Foreign keys to dimension tables | Primary keys referenced by fact table |
| Examples | Sales amount, transaction count | Product name, customer location |
Follow-Up Questions and Answers:
-
Question: How do you handle slowly changing dimensions (SCD) in a dimension table?
- Answer: Slowly Changing Dimensions can be handled using different strategies such as Type 1 (overwrite old data), Type 2 (create a new additional record), or Type 3 (add a new column to store old values).
-
Question: What is a star schema, and how does it relate to fact and dimension tables?
- Answer: A star schema is a type of database schema that is used in data warehouses. It consists of a central fact table surrounded by dimension tables, forming a star-like structure. This schema simplifies queries and enhances performance by denormalizing data.
-
Question: Can you explain the concept of a snowflake schema?
- Answer: A snowflake schema is a variant of the star schema where dimension tables are normalized into multiple related tables, leading to a more complex structure resembling a snowflake. This can improve storage efficiency but may require more complex queries.