Fact Tables.

Fact tables are where you store the low-level measurement data of business processes. These tables are big and should not be replicated for different parts of an organization.

Keep only a single centralized repository of measurement data to ensure consistency of use across the business.

The rows of a fact table represent a business measurement events (a fact). The level of detail of these measurement events is referred to as the grain. All rows in a fact table must be at the same level of detail.

Facts can be additive, semi-additive, non-additive, and are generally numbers. Additive facts sum across dimensions (e.g., time, products, locations). If you can meaningfully do a GROUP BY operation on it, you’re dealing with an additive fact. A QuantitySold fact is additive. Non-additive facts give you meaningless results when you try to add them up. A product’s SKUPrice is a fact that is not supposed to be added. Semi-additive facts may be added occasionally. An AccountBalance may not be summed across days, but we might take a snapshot of it at the end of each month. We could average it over a period as well. These are somewhat academic definitions. In practice, use common sense to work out how to aggregate your measurements.

Fact tables are meant to be sparse, meaning, we do not backfill the absence of measurement events. The rationale for this principle is that we want to save up on storage. These tables are long, and we don’t need to make them longer.

Fact tables have two or more foreign keys that connect to the dimension table’s primary keys. A fact table generally has a composite key, a primary key composed of a subset of the foreign keys. This situation happens because there are usually a few dimensions that together identify a fact table row. When you see a composite key somewhere in the definition of a table, you are looking at a fact table.

CREATE TABLE IF NOT EXISTS dim_date (
  date_key INTEGER PRIMARY KEY,
  calendar_date TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dim_store (
  store_key INTEGER PRIMARY KEY,
  store_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dim_product (
  product_key INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dim_region (
  region_key INTEGER PRIMARY KEY,
  region_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS fact_sale (
  -- Composite key (the grain).
  date_key    INTEGER NOT NULL,
  store_key   INTEGER NOT NULL,
  product_key INTEGER NOT NULL,
  region_key  INTEGER NOT NULL,

  -- Additive facts.
  sales_amount   REAL    NOT NULL,
  quantity_sold  INTEGER NOT NULL,
  cogs_amount    REAL    NOT NULL,

  -- Non-additive facts.
  unit_price     REAL    NOT NULL,
  discount_rate  REAL    NOT NULL,
 
  -- Semi-additive facts.
  inventory_on_hand REAL NOT NULL,
  account_balance   REAL NOT NULL,

  -- Composite primary key.
  PRIMARY KEY (
    date_key, store_key, product_key, region_key
  ),

  FOREIGN KEY (date_key) 
    REFERENCES dim_date(date_key),

  FOREIGN KEY (store_key) 
    REFERENCES dim_store(store_key),

  FOREIGN KEY (product_key) 
    REFERENCES dim_product(product_key),

  FOREIGN KEY (region_key) 
    REFERENCES dim_region(region_key)
);

CREATE INDEX idx_fact_sale_product_date
ON fact_sale(product_key, date_key);

PRAGMA foreign_keys = ON;

References