Data Modeling for Analytics

An aggregate fact table stores the same measures as an atomic fact table, pre-summed to a coarser grain such as month-by-product. It is a derived performance copy; the atomic table stays as the source of truth for detail and rebuilds.

A conformed dimension is one shared dimension - the same table, or copies with identical keys, attribute names and attribute values - used by several fact tables, so grouping or filtering by it means exactly the same thing in every star.

A degenerate dimension is an operational identifier such as an order or invoice number stored directly in the fact table with no dimension table behind it, because every descriptive attribute it would carry already lives in other dimensions.

A role-playing dimension is one physical dimension joined to the same fact table several times under different meanings — order date, ship date and due date — usually exposed as one view or alias per role so column names stay unambiguous.

A fact table holds the numeric measurements of a business process, one row per event at a declared grain, plus foreign keys to dimensions. A dimension table holds the descriptive attributes you filter, group and label by.

Because facts must point at the version of the dimension that was true when the event happened. The business key identifies the entity for its whole life; the warehouse surrogate key identifies one dated version of that entity.

A Type 1 change overwrites the attribute in place, so only the current value exists and history is lost. A Type 2 change retires the old row and inserts a new version, so old facts still report under the value that was true then.

A hashdiff hashes the concatenated tracked attributes of an incoming source row and compares that value to the hash stored on the key's current dimension version. Equal means skip the row; different means close the old version and insert a new one.

Never drop the row. Either route the fact to the dimension's reserved unknown member, or — the usual choice — insert an inferred placeholder dimension row carrying the natural key, and enrich it when the real record arrives.

An SCD Type 3 column keeps one prior value beside the current one on the same dimension row, so the same measures can be reported under both the old and the new attribute at once. It captures a realignment, not a timeline.

Inmon builds top-down: one normalized enterprise warehouse integrates every source, and dimensional marts are derived from it. Kimball builds bottom-up: dimensional marts per business process are the warehouse, tied together by shared dimensions.

A data mart is a subject-area slice of the warehouse: the facts and dimensions one business area needs, named in their vocabulary and owned by someone. The warehouse holds everything; a mart is a curated, governed subset for a specific audience.

Bronze holds source data landed as received, duplicates and quirks included. Silver holds cleaned, typed, deduplicated and conformed entities at a declared grain. Gold holds business-ready output — dimensional models, aggregates and metric tables that reporting queries directly.

A one-big-table model materialises a fact and all its dimension attributes into a single wide table, so analysts query it without joins. It buys simplicity and consumer safety, and pays in duplication, rebuild cost and lost dimension reuse.

A hub holds one row per distinct business key. A link holds a relationship between two or more hubs. A satellite hangs off one hub or one link and holds descriptive attributes plus their load-dated history.

The two schemas are optimized for different jobs. A transactional schema normalizes so concurrent single-row writes stay consistent with one place per fact. An analytics model flattens attributes onto few wide tables so large read-only scans need fewer joins.

Because nothing updates those copies ad hoc. One scheduled load owns the table, re-derives the region name from the source on each run, and rewrites the affected rows, so the redundancy is recomputable derived data rather than an independent authority.

Three families exist: an identity or sequence counter, a deterministic hash over the business key, or a random UUID. Hashing is the usual warehouse choice because it is reproducible, computable in parallel, and survives a full rebuild without renumbering.

A NULL dimension key drops the fact row from every inner join to that dimension, silently changing totals. A reserved Unknown member row keeps the fact joinable, so measures still add up and the missing value shows as its own visible bucket.

Additive changes — a new dimension attribute, a new dimension table, a new fact measure — leave existing queries valid. Grain changes, drops, renames and type changes break consumers loudly; redefining what an existing measure means breaks them silently, which is worse.