In a traditional data warehousing architecture—often guided by the Kimball methodology—data is organized around two primary concepts:
- Fact Tables
- Dimension Tables
This dimensional modeling approach helps streamline analytical queries and reporting, giving business users a straightforward way to perform slice-and-dice analysis on enterprise data.
Fact Tables
- Primary Role:
Fact tables hold the measurements or metrics of a business process (such as sales revenue, number of clicks, volume of calls, etc.). - Characteristics:
- Granularity: They typically capture events or transactions at a detailed level (one row per event).
- Foreign Keys: Fact tables typically include foreign keys referencing dimension tables (e.g.,
customer_id,product_id). - Append-Only: New rows are appended regularly (e.g., daily transaction inserts), and updates or deletes are less common, especially when tracking time-series data.
- Minimal Aggregation: Fact tables usually contain raw or near-raw transactional data. Aggregations are performed by analytical queries or views later in the pipeline.
Use Cases for Fact Tables:
- Tracking Business Activity: Perfect for monitoring trends, such as daily sales or website traffic.
- Detailed Analysis: Because they include granular event-level data, users can “drill down” to investigate the root causes of business outcomes.
- Historical Reporting: Fact tables can maintain a complete historical record, enabling time-based comparisons (year-over-year, month-over-month, etc.).
Dimension Tables
- Primary Role:
Dimension tables contain descriptive attributes used to analyze the metrics stored in fact tables. Examples include product details, customer information, or geographical data. - Characteristics:
- Primary Key: Each dimension table has a unique key (e.g.,
product_id) referenced by corresponding fact tables. - Updates Allowed: Unlike fact tables, dimension attributes (e.g., product category, customer address) can change over time.
- Slowly Changing Dimensions (SCD): Various strategies (Type 1, Type 2, etc.) allow tracking attribute changes over time if historical context is needed.
- Primary Key: Each dimension table has a unique key (e.g.,
Use Cases for Dimension Tables:
- Adding Context to Facts: Dimensions provide categories and descriptors (e.g., product category, customer region) that enable meaningful grouping and filtering of fact data.
- Conformed Dimensions: Common dimension tables are shared across multiple fact tables to ensure consistent reporting.
- Smarter Analysis: Having up-to-date dimensions (with the option to track historical changes) ensures accurate and rich insights.
Bringing It All Together
When querying a dimensional model, analysts join fact tables (transactional data) to dimension tables (descriptive data) to answer questions like:
- “What is the total sales volume by product category over the last quarter?”
- “Which customers in each region have made the most purchases this year?”
- “How does revenue trend when considering product brand or marketing campaigns?”
Good Practices in Spark/Databricks:
- Slight Dénormalization: Spark handles joins efficiently, but sometimes denormalizing select attributes from dimension tables into facts can speed up queries.
- Use Conformed Dimensions: Centralize shared attributes—like dates, products, or customers—across all fact tables to maintain consistency.
- Manage Slowly Changing Dimensions with Delta Lake: Leverage Delta Lake’s merge capabilities to handle Type 1 or Type 2 SCD for evolving dimension data.
- Append-Only Fact Tables: For time-series or transactional data, ingest new records without overwriting existing ones, preserving a robust historical trail for analysis.
A well-structured dimensional model—involving both fact and dimension tables—creates a reliable foundation for business intelligence, enabling quick, flexible, and comprehensive insights into your organization’s performance.