Grain
Grain is the level of detail that a single row in a fact table represents — for example, one row per order line item versus one row per order per day.
Overview
Grain answers the question "what does one row in this table mean?" It's arguably the single most important decision in dimensional modeling: Ralph Kimball's four-step design process starts with (1) choose the business process, (2) declare the grain, (3) identify the dimensions, and (4) identify the facts — grain comes before you even decide what columns to add, because every dimension and measure has to be consistent with it.
Grain should be as atomic as practical — the most granular level the source data supports — rather than pre-aggregated. Atomic grain gives every future query maximum flexibility to slice and roll up; a fact table stored at "daily total revenue per store" can never answer "what did this one customer buy at 2pm," but a table at "one row per order line item" can always be aggregated up to daily-per-store totals later.
Example
Copy code
-- Grain: one row per order line item
CREATE TABLE fact_order_lines (
order_id INTEGER,
line_number INTEGER,
product_key INTEGER,
customer_key INTEGER,
date_key INTEGER,
quantity INTEGER,
revenue DECIMAL(10,2),
PRIMARY KEY (order_id, line_number)
);
-- Rolling up from atomic grain to daily-per-product totals is trivial
SELECT date_key, product_key, SUM(revenue) AS daily_revenue
FROM fact_order_lines
GROUP BY ALL;
Mixing grains in one fact table — some rows representing a line item, others representing an order total — is a common modeling mistake, because it silently breaks aggregation (summing revenue would double-count).
DuckDB angle
Because DuckDB's columnar engine aggregates cheaply even over very large, atomic-grain fact tables, there's less pressure to pre-aggregate for performance than there was with older row-based warehouses. It's usually better to load fact data at its natural atomic grain into DuckDB or MotherDuck and compute rollups on demand (or materialize them as a separate summary table) than to throw away detail up front.
Related terms
A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.
Dimensional modeling →Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.
Fact constellation schema →A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.
Dimension table →A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Degenerate dimension →A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.
Wide table →A wide table is a table with a large number of columns, typically produced by denormalizing and flattening related data together, common in analytics and columnar data warehouses.
FAQS
Grain determines what every dimension and measure in a fact table can mean. Declaring it explicitly, before adding columns, prevents mixed-grain tables that silently produce wrong totals when aggregated.
