dimensional_data_modeling v1.0.0

name: dimensional_data_modeling description: "Design dimensional models for analytics: declare the grain before anything else, design fact and dimension tables, choose slowly changing dimension handling per attribute, keep dimensions conformed across processes, and run the checks that catch grain mistakes early. Use when building a star schema or data mart, modeling warehouse or lakehouse tables for reporting, reviewing a fact or dimension design, deciding how to track dimension history, or diagnosing double counting in a report."

Dimensional Data Modeling

Purpose

Build an analytics model that gives the same right answer every time it is asked. The model is a set of fact tables, each recording one business process at a declared grain, joined to dimension tables that carry the descriptive context. Most wrong reports from a warehouse trace back to three causes: a grain nobody stated, a measure summed in a way its nature forbids, or history overwritten by accident. This skill designs against all three, in that order.

The method is the one formalized by Ralph Kimball and the Kimball Group: choose the business process, declare the grain, identify the dimensions, identify the facts. Microsoft star schema guidance for semantic models says the same thing in shorter form: dimensions enable filtering and grouping, facts enable summarization, and the one side of a relationship is the dimension while the many side is the fact.

Workflow

1. Pick one business process that produces measurements. Taking an order, shipping a parcel, closing a support ticket. One process gets one fact table. Do not start from the source tables or from a report layout. A report is a question, a process is what the business does, and the model has to outlive any single report.

1. Declare the grain in one sentence, and write it down before designing anything else. One row per order line. One row per account per day. One row per order, updated as it moves. If the sentence cannot be written, the process is not understood yet. Prefer the most atomic grain the source offers: lines sum into orders, orders never split back into lines. Every later choice, dimensions, measures, keys, is tested against this sentence.

1. Choose the fact type that matches the grain.

• Transaction: one row per event, inserted once and not updated. The default, and the most flexible.
• Periodic snapshot: one row per entity per period, including periods with no activity, because a balance or a headcount still exists on a quiet day.
• Accumulating snapshot: one row per process instance, updated in place as milestones complete, with one date key per milestone. Built for pipeline and cycle time questions.
• Factless: a fact with no numeric measure, recording that an event happened or that coverage existed. Attendance and page eligibility are the usual cases.

1. Put only measures that belong at the grain into the fact, and label each one by additivity.

• Additive: sums across every dimension. Quantities and amounts at the fact grain.
• Semi-additive: sums across some dimensions and not others. A balance sums across accounts, never across time. Name the blocked dimensions in the spec.
• Non-additive: ratios, percents, and unit prices. Store the additive parts, revenue and quantity, and compute the ratio in the query. A measure from a coarser grain, such as a discount set on the whole order inside a line grain fact, must be allocated to the grain by a stated rule, moved to a fact at its own grain, or left out. Copied onto every line, it counts once per line.

1. Design the dimensions. One denormalized table per entity, with a warehouse assigned surrogate key as its primary key, the source identifier kept as a natural key attribute, and the descriptive attributes analysts filter and group by. Keep text out of the fact: a name repeated on a million fact rows belongs in the dimension, stored once. Identifiers with no attributes of their own, such as an order number on a line grain fact, stay in the fact as degenerate dimensions. Build a date dimension as a table, with calendar attributes a date column alone cannot give, instead of extracting parts of a timestamp in every query.

1. Decide history handling per attribute using references/scd-decision-rules.md. Type 1 overwrites, Type 2 versions with a new surrogate key plus effective dates and a current flag, Type 3 keeps one prior value in a second column, Type 0 never changes. The choice is per attribute: one customer dimension can overwrite a name correction, version a city move, and keep both territory values at once. If one attribute changes so fast that Type 2 would swamp the dimension, split it into a mini-dimension joined straight to the fact.

1. Reserve placeholder dimension rows for unknown, not applicable, and late arriving members, each with its own surrogate key. Fact foreign keys are never null. A late arriving member gets a placeholder row when the fact loads, and its attributes are filled in later under the same key, so no fact row ever needs rewriting.

1. Conform the dimensions across processes. A dimension is conformed when every fact that uses it uses the same table, the same keys, and the same definitions. Record the plan as a bus matrix: processes as rows, dimensions as columns, a mark where a process uses a dimension. Drill across two processes by aggregating each fact to the shared level separately and joining the results, never by joining fact rows to fact rows.

1. Write the model spec and run the checker before writing load code.

python3 bin/check_model.py assets/model.json

It validates grain statements, measure grain and additivity, Type 2 plumbing, undeclared dimensions, and bus matrix agreement. The spec format and finding codes are in references/model-spec.md. Fix every error. A warning needs a written reason or a fix.

1. Build, then run the data checks in references/review-checks.md: duplicate grain groups, control totals against the source, orphan keys, Type 2 interval chains, and a restatement test proving that a dimension change does not move historical numbers.

Worked example

assets/raw_orders.csv holds five order lines from three orders. The trap in it is visible: order 1001 carries a 5.00 discount on the whole order, and the customer on order 1003 has moved city since order 1001.

assets/star_schema.sql builds the model in SQLite:

python3 -c "import sqlite3; print(sqlite3.connect(':memory:').executescript(open('assets/star_schema.sql').read()))"

Or load it with any SQLite runner. What it shows:

• Grain: one row per order line in fact_order_line, keyed by order and line.
• The order discount is allocated to lines in proportion to line amount, 3.6364 and 1.3636, so the allocated total reconciles to 5.00 instead of counting 10.00.
• The city move is Type 2. The customer dimension holds two versions of the same customer, Austin closed at the end of January and Round Rock current from February. Fact rows keep the key of the version in place when the order happened, so revenue by city as it was gives Austin 33.00, Denver 12.00, and Round Rock 67.00.
• Revenue by category gives Grocery 36.00, Home 40.00, and Kitchen 36.00, for a gross total of 112.00.

assets/model.json is the spec for this model plus a daily inventory snapshot. The checker passes it with no errors and no warnings. assets/model_bad.json is the same territory designed badly: no grain, a surrogate key equal to the natural key, Type 2 attributes with no date or flag columns, an additive unit price, and a fact joined to an undeclared dimension. The checker reports 8 errors and fails, which is the correct result for both files.

Output Contract

A finished design delivers:

• A grain sentence for every fact table, stated before its columns.
• A bus matrix covering every process in scope and every shared dimension.
• DDL or a table spec for each fact and dimension, with surrogate and natural keys named, degenerate dimensions listed, and placeholder rows specified.
• A measure list with additivity per measure, the blocked dimensions for each semi-additive measure, and the allocation rule for any measure brought down from a coarser grain.
• An SCD decision per dimension attribute, with the load rules for Type 2 attributes, including the out of order change policy.
• Checker output for the model spec, and results for the data checks in references/review-checks.md, with control totals compared against the source.

Report design decisions as decisions, with the rejected alternative named where the choice was close. Report check results as counts and totals from the run, not as assurances.

Operating Rules

1. No grain sentence, no build. A fact table whose grain is described as sales data or order information is undesigned, whatever its columns look like.
2. Never mix grains in one fact table. Two grains means two fact tables that share dimensions.
3. Never sum a measure past its additivity. If a tool or dashboard sums a balance across time by default, fix the measure definition or the semantic layer, not the reader.
4. Never rewrite fact rows to fix dimension history. History lives in the dimension versions and the keys the facts already hold. The one stated exception is remapping a placeholder key when a design uses that pattern, and that choice belongs in the load rules.
5. A dimension used by two processes with two different definitions is not conformed, no matter what it is named. Settle the definition with the owners of both processes, or keep the dimensions separate and say so.
6. Snowflake a dimension, splitting it into normalized tables, only when a shared hierarchy or a maintenance problem forces it. The default is one flat dimension table per entity: fewer joins, simpler filters, and attributes that stay in one place.
7. Keep the atomic fact as the source other facts derive from. Snapshots and aggregates are built from it and can be rebuilt. An aggregate with no atomic fact under it cannot be audited or corrected.
8. Date and time are dimensions, not functions. If analysts group by fiscal period, holiday, or business day, those attributes live in the date dimension where everyone shares one definition.