Choosing the Grain of a Fact Table

If you remember one word from this whole track, make it grain. The grain is the precise answer to "what does one row of this fact table represent?" Get it right and every later decision (which dimensions, which measures, how big the table gets) follows on its own. Get it wrong, or leave it vague, and the warehouse will double-count, under-count and confuse everyone who touches it. Almost every "the warehouse gives wrong numbers" call we've taken has ended at a grain problem.

What grain means

Take a retail sale. One receipt has a header (receipt number, time, cashier, payment type) and several lines (each product bought). Which of these is one row of fact_sales?

• One row per receipt line item. The finest grain. It can answer anything about products, baskets and customers. Around 2 million rows a month for a 42-store chain.
• One row per receipt. Answers basket size, time of day, payment method. Can't tell you which products were bought.
• One row per store per day. Daily store totals only. Nothing about products, customers or hours.

The finest grain answers every question the coarser ones can (by summing) plus many more. The coarser grains are smaller, but they throw information away for good. So the rule is to build the base fact table at the lowest grain the source provides and build aggregates on top for speed if you need them.

The four-step design process

Kimball's process, and the order matters:

1. Select the business process. One measurable activity. Retail sales, returns, stock counts, loan applications. Not "the finance department", which is a group of people, not a process.
2. Declare the grain. Write it as one plain sentence. "One row per line item on a customer receipt." Everyone on the project should be able to repeat it.
3. Identify the dimensions. Anything that's true of one row at that grain. Date, time, store, product, customer, cashier, promotion, payment type.
4. Identify the facts. Numbers that are true at that grain. Quantity, unit price, extended amount, discount, cost. Anything not true at that grain is rejected or allocated.

Steps 3 and 4 depend entirely on step 2. Skip the grain and you'll soon be arguing about whether "delivery charge" belongs on the line item (it doesn't, it belongs to the receipt) or whether "monthly target" is a fact (it's a different process altogether). We make students write the grain sentence at the top of every DDL file. It feels childish for a week and then it saves them.

The grain test

For every proposed column, ask one question. Is this value true for exactly one row at the declared grain?

Candidate column	True for one line item?	Decision
quantity	Yes	Fact
product	Yes	Dimension key
delivery charge (per receipt)	No, shared by all lines	Allocate across lines in proportion to amount, or put it in a receipt-grain fact
cashier	Yes (same for all lines, but still one value per line)	Dimension key
store's monthly sales target	No, different process	Separate fact_target at store-month grain
customer's total spend to date	No, changes over time	Not a fact; compute in a query or a snapshot

Mixed grain: the classic mistake

A team adds a receipt_total column to the line-item fact "so the dashboard is easier". Every line of a 3-line receipt now carries the same total. Someone sums the column and reports three times the real revenue. This is a mixed-grain fact and it's the single most common cause of a warehouse that "gives wrong numbers". The fix is discipline. One grain per table, and if you need receipt-level facts, build fact_receipt at receipt grain.

Declaring the grain in DDL

-- Grain: one row per line item on a customer receipt.
-- Enforced by the primary key: a receipt line can appear only once.
CREATE TABLE dw.fact_sales (
  receipt_no    TEXT     NOT NULL,
  line_no       SMALLINT NOT NULL,
  date_key      INT NOT NULL,
  time_key      INT NOT NULL,
  store_key     INT NOT NULL,
  product_key   INT NOT NULL,
  customer_key  INT NOT NULL,
  cashier_key   INT NOT NULL,
  promo_key     INT NOT NULL,
  quantity      INT NOT NULL,
  gross_amount  NUMERIC(12,2) NOT NULL,
  discount      NUMERIC(12,2) NOT NULL DEFAULT 0,
  net_amount    NUMERIC(12,2) NOT NULL,
  cost          NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (receipt_no, line_no)
);

-- Receipt-level facts get their own table at their own grain
CREATE TABLE dw.fact_receipt (
  receipt_no      TEXT PRIMARY KEY,
  date_key        INT NOT NULL,
  store_key       INT NOT NULL,
  customer_key    INT NOT NULL,
  payment_key     INT NOT NULL,
  line_count      SMALLINT NOT NULL,
  delivery_charge NUMERIC(10,2) NOT NULL DEFAULT 0,
  receipt_total   NUMERIC(12,2) NOT NULL
);

-- Basket size is safe here: no double counting
SELECT s.store_name, AVG(r.receipt_total) AS avg_basket_pkr
FROM dw.fact_receipt r
JOIN dw.dim_store s ON s.store_key = r.store_key
GROUP BY s.store_name;

The primary key is the grain written in SQL. If the load ever tries to insert the same receipt line twice, the database refuses, which is exactly what you want at 3 am.

The fuel bill that was 200 times too big: a Karachi courier company built fact_shipment with one row per parcel, then added route_fuel_cost (a per-vehicle-per-day figure) to every parcel row because operations wanted cost per parcel on the dashboard. Fuel cost was summed across 200 parcels per van and reported 200 times too high. Management nearly cancelled a fuel contract on the strength of it. The fix was a separate fact_route_day at vehicle-day grain and an allocated fuel_cost_share on the parcel row, computed by dividing route cost by parcels on that route.

Recap in four lines

• Grain is the exact meaning of one fact row, stated in one sentence before any column is added.
• Choose the lowest grain the source supports and aggregate upward later, never downward.
• Process, grain, dimensions, facts, in that order. Grain decides the other two.
• Every column must pass the grain test. Enforce the grain with the primary key, one grain per table.

Exercises

1. Write a one-sentence grain for each: hospital outpatient visits, JazzCash transfers, school exam results, K-Electric monthly bills.
2. A fact_order table has one row per order line and an order_shipping_fee column. Show with a three-line order how a SUM goes wrong, then describe two correct designs.
3. For the fact_sales DDL above, list two more columns that would fail the grain test and say where they belong instead.