Dimensional Modeling skill for Claude Code

A star schema is right when each fact table has one declared grain written in a sentence, every measure is valid at that grain, dimensions are shared (conformed) across facts that report together, history is tracked deliberately per attribute, and a business user can answer the top questions with one fact table and simple joins.

Workflow

- [ ] 1. List business processes and the questions asked of each
- [ ] 2. Build the bus matrix (processes x dimensions)
- [ ] 3. For each process: declare the grain in one sentence
- [ ] 4. Identify dimensions true at that grain
- [ ] 5. Identify facts (measures) and their additivity
- [ ] 6. Decide history handling (SCD type) per dimension attribute
- [ ] 7. Specify keys, late-arriving handling, and tests
- [ ] 8. Validate against source totals and the original questions

1-2. Bus matrix

Business process	Date	Customer	Product	Store	Promotion	Employee
Orders	x	x	x	x	x
Shipments	x	x	x	x		x
Inventory snapshot	x		x	x
Support tickets	x	x	x			x

Columns are conformed dimensions: one dim_customer used by every fact, with the same keys and attribute definitions. This is what makes “revenue per customer segment” and “tickets per customer segment” comparable. Build one process at a time, but agree the conformed dimensions up front.

3. Grain

Write it as: “One row per <event or snapshot> per <dimensions>.”

• “One row per order line” (transaction grain) - finest, most flexible.
• “One row per product per store per day” (periodic snapshot).
• “One row per order, updated as it moves through placed, paid, shipped, delivered” (accumulating snapshot).

Choose the finest grain the source supports; aggregates can be built later, missing detail cannot be recovered. Never mix grains in one table (order headers and lines in one fact, daily and monthly rows together) - it causes double counting.

Fact table types

Type	Grain	Typical measures	Update pattern
Transaction	one row per event	amount, quantity	insert-only
Periodic snapshot	one row per entity per period	balance, stock on hand, MRR	insert per period
Accumulating snapshot	one row per process instance	durations between milestones, milestone dates	update in place as milestones occur
Factless	event or coverage with no measure	count of rows	insert-only (attendance, eligibility)

5. Measures and additivity

Measure	Additivity	Rule
Sales amount, quantity	additive	sum across all dimensions
Account balance, inventory level	semi-additive	sum across accounts/products, never across time - use last value or average over time
Unit price, ratios, percentages, averages	non-additive	store the components (revenue and units; numerator and denominator), compute the ratio after aggregation
Distinct counts	non-additive	compute from the grain, or store sketches (HLL) for rollups

Store money in the transaction currency plus a converted reporting currency with the rate date, or a currency dimension and conversion fact.

Header-level amounts (order shipping fee) on a line-grain fact: allocate to lines with a documented rule, or keep a separate order-grain fact. Repeating the header amount on each line double counts.

4 and 6. Dimensions and history

Dimension rules:

• Surrogate key (customer_sk, integer or hash) as primary key; keep the natural/business key (customer_id) as an attribute. Facts reference surrogate keys.
• Wide and descriptive: flatten hierarchies (product -> subcategory -> category) into the dimension instead of snowflaking, unless a hierarchy is huge and shared.
• Replace codes with labels users understand; no NULL attributes - use “Unknown” or “Not applicable” members.
• Include an “unknown” member row (key -1) for facts whose dimension value is missing or late.

SCD types per attribute:

Type	Behavior	Use for
0	never changes	original signup date, birth date
1	overwrite	corrections, attributes where history is irrelevant (typo in name)
2	new row with valid_from, valid_to, is_current	attributes reports must slice historically (customer segment, region, account manager)
3	previous value column	one prior value is enough (“previous region”)
4 / mini-dimension	rapidly changing attributes split out	frequently changing bands (age band, credit score band)

Type 2 implementation:

-- dim_customer (Type 2)
customer_sk     bigint PRIMARY KEY,
customer_id     text NOT NULL,          -- natural key
segment         text NOT NULL,
region          text NOT NULL,
valid_from      timestamp NOT NULL,
valid_to        timestamp NOT NULL DEFAULT '9999-12-31',
is_current      boolean NOT NULL
-- unique (customer_id, valid_from); exactly one is_current row per customer_id

-- Fact lookup: pick the version valid at the event time
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.event_at >= d.valid_from AND s.event_at < d.valid_to

Use half-open intervals (>= valid_from AND < valid_to) so versions never overlap. In dbt, snapshots implement Type 2 from source tables (see dbt-models).

Special dimensions:

Kind	What	Example
Degenerate	dimension key with no attributes, stored on the fact	order number, invoice number
Junk	combine low-cardinality flags into one small dimension	payment_type x is_gift x channel
Role-playing	one dimension joined several times under different roles	dim_date as order_date, ship_date, delivery_date (views or aliases)
Bridge	many-to-many between fact and dimension, with weighting factor	account with multiple holders, ticket with multiple tags
Outrigger	dimension referencing another dimension	avoid unless shared and stable

Date dimension: one row per day with date_key (20261020 int or date), day of week, week start, ISO week, month, quarter, fiscal periods, holiday flags, and is_business_day. Generate it once for a wide range (e.g. 2000-2050). A time-of-day dimension is separate.

7. Keys and late data

• Late-arriving facts (event arrives after the dimension changed): look up the version valid at event time, not the current one.
• Late-arriving dimensions (fact before its customer exists): insert an inferred member row with the natural key and placeholder attributes, then update it when the real record arrives (Type 1 on inferred members).
• Hash surrogate keys (md5(natural_key || valid_from)) make loads idempotent and parallel; integer sequences are smaller and faster to join. Pick one per warehouse.
• Modern cloud warehouses join cheaply; wide denormalized “one big table” marts on top of the star are fine for BI tools, but keep the star as the source of truth.

Gotchas

• Fan-out: joining two facts directly (orders to shipments on order_id) multiplies rows. Aggregate each fact to a common grain first, then join on conformed dimensions (“drill across”).
• Filtering a Type 2 dimension on is_current while joining by natural key restates history to current values; decide which view each report needs.
• Snapshot facts must have a row for every entity every period, including zero values, or averages and counts are wrong.
• Changing a dimension from Type 1 to Type 2 later cannot recreate lost history; ask about history requirements before the first load.
• NULL foreign keys in facts silently drop rows in inner joins; use the unknown member.

Verification

• Grain test: SELECT grain_cols, count(*) ... GROUP BY grain_cols HAVING count(*) > 1 returns nothing.
• Reconciliation: fact totals per day match the source system within an agreed tolerance.
• Type 2 integrity: no overlapping intervals per natural key; exactly one current row.
• Every fact foreign key resolves to a dimension row (including unknown member).
• The original business questions are answered by sample queries reviewed with the user.

Output: model spec

### fact_order_line
**Grain:** one row per order line at placement time.
**Dimensions:** dim_date (order_date, ship_date), dim_customer (SCD2 at order time), dim_product, dim_store, dim_promotion; degenerate: order_number.
**Measures:** quantity (additive), gross_amount, discount_amount, net_amount (additive, USD and local), unit_price (non-additive, not summed).
**Load:** incremental by order updated_at, 3-day lookback; late customers -> inferred members.
**Tests:** unique(order_line_id), FK coverage 100%, daily net_amount within 0.5% of ERP.