In this lesson, you will learn how to handle shifting business requirements that demand different levels of detail in your fact tables using bridge tables and multi-level designs.
The Challenge of Grain Evolution
In data engineering, the "grain" of a fact table defines what a single row represents (e.g., one row per line item in an order vs. one row per order total). Sometimes, business stakeholders start by needing high-level summary data, but eventually demand atomic, transactional-level detail. If you try to force high-level metrics and atomic events into a single table, you risk "fan-out" issues where calculations become inaccurate.
Strategy 1: The Multi-Level Fact Approach
If your reporting needs vary, you can maintain two separate fact tables at different grains. For example, a Fact_Order_Summary (grain: Order ID) stores total order value, while Fact_Order_Line_Items (grain: Order ID + Product ID) stores specific item sales. This allows your dashboard to query the summary table for quick top-level performance trends and the atomic table for deep-dive analysis.
Strategy 2: Using Bridge Tables
When a single business event relates to multiple granularities simultaneously—such as a sales promotion that applies to a bundle of products—you can use a Bridge Table. The bridge table resolves the many-to-many relationship between the grain of the fact (the event) and the grain of the dimension (the components).
For example, if a Fact_Sales table is at the transaction level, but you want to analyze by "Campaign Groups," you would use a bridge:
-- Structure of a simple Bridge Table
CREATE TABLE Bridge_Campaign_Product (
Campaign_Key INT,
Product_Key INT,
Allocation_Weight FLOAT -- Handles splitting metrics across items
);
By connecting the Fact_Sales to the Bridge_Campaign_Product, you can distribute the sale amount correctly across the associated products even when the grain of the campaign and the grain of the sale don't perfectly align. This approach keeps your primary fact table clean while providing the flexibility to report at various levels of aggregation.
Key Takeaway: Rather than forcing a single grain on a fact table, use multi-level fact designs for performance or bridge tables to resolve complex many-to-many relationships when reporting at different levels of detail.