Difference between Dimension and Fact tables in SQL

In SQL and data warehousing, dimension and fact tables are two types of tables used in a star schema or snowflake schema to model and organize data. They serve different purposes and play key roles in data analysis and reporting. Here are definitions and real-life examples of dimension and fact tables:

Dimension Tables:

• Dimension tables contain descriptive attributes that provide context and additional information about the facts in a data warehouse.
• Dimension tables are typically small in size and have a one-to-many relationship with fact tables.
• They are used to categorize, filter, and group data for analysis.
• Examples of dimension tables:
  • Customers: Contains information about customers, such as customer ID, name, address, demographics, etc.
  • Products: Contains details about products, including product ID, name, category, price, etc.
  • Time: Contains time-related information, such as date, month, quarter, year, etc.
  • Locations: Contains information about geographic locations, such as country, city, region, etc.

Fact Tables:

• Fact tables contain quantitative and numeric data, also known as facts, and are the central focus of analysis in a data warehouse.
• Fact tables typically have a large number of rows and capture the measurements or metrics associated with business processes.
• They have foreign key relationships with dimension tables, enabling data aggregation and slicing based on various dimensions.
• Examples of fact tables:
  • Sales: Contains information about sales transactions, including the product sold, customer involved, date of sale, quantity, revenue, etc.
  • Inventory: Contains data about available inventory quantities for various products, locations, and time periods.
  • Website Visits: Contains data about website visits, including visitor ID, page visited, duration, timestamp, etc.
  • Call Center Logs: Contains data about call center interactions, such as call duration, agent involved, customer ID, call outcome, etc.

In summary, dimension tables provide descriptive attributes and serve as the "who, what, where, and when" context for analysis, while fact tables store the numerical measurements or facts associated with business processes. Together, they form the foundation for multidimensional analysis and reporting in data warehousing scenarios.