---
title: "Grain"
description: "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."
canonical: "https://motherduck.com/glossary/grain/"
related:
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "Guides: Warehouse-Native Context for AI Agents | MotherDuck"
    url: "https://motherduck.com/videos/guides-context-layer-ai-agents/"
gated_asset:
  title: "Postgres Is Full: A Field Guide to Analytics at Scale"
  url: "https://motherduck.com/lp/postgres-analytics-guide-full/"
---

# 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

```sql
-- 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.