Data modelling, one row at a time

Build one normalized (3NF) enterprise warehouse first, integrating every source into a single version of truth. Dimensional data marts are then derived from it. A warehouse is subject-oriented, integrated, time-variant, non-volatile.

Build dimensional models per business process and let them share conformed dimensions. The warehouse is the union of its marts; the bus matrix (processes × dimensions) is the plan. Fastest to first value. Integration is a discipline, not a layer.

Split every entity into a hub (the business key), links (relationships between hubs) and satellites (descriptive attributes, historized). Insert-only and hash-keyed, so it absorbs source change without redesign. Auditable and agile, but nobody queries it directly — build a star on top.

Flatten the fact and every dimension attribute into a single wide table. No joins, no key management, no fanout risk for analysts. Cheap on columnar engines like Snowflake. Costs you reuse, history and reload time.

One fact table surrounded by denormalized dimensions, each exactly one join away. The default choice for analytics. Fewest joins, most redundancy, easiest for BI tools to navigate.

A star whose dimensions have been normalized into sub-dimensions — product splits into category, then department. Saves storage nobody is short of. Adds joins everyone pays for.

Several fact tables sharing the same conformed dimensions. Any warehouse covering more than one business process is a galaxy. Shared dimensions are what make drill across possible.

What exactly one row of a fact table means — "one row per order line per shipment". Declare it in words before you write any DDL; every measure and every dimension must be true at that grain. Most modelling bugs are a grain violation that surfaces later as double counting.

Every cell holds a single atomic value. No repeating groups, no comma-separated lists, no phone1, phone2, phone3. Test: could you split this cell and lose nothing?

1NF plus no partial dependency: with a composite key, no attribute may depend on only part of it. Product name in an order-line table depends on product, not on the order. Only ever an issue when the primary key is composite.

2NF plus no transitive dependency: no non-key column may depend on another non-key column. Storing city next to zip in the order table breaks it. This is the form Inmon's enterprise warehouse is built in.

Stricter 3NF: every determinant must be a candidate key. Only bites when a table has overlapping candidate keys. 4NF and 5NF exist; you will almost never model to them deliberately.

Deliberately reintroducing redundancy to remove joins. A star schema is denormalization with rules. You trade write complexity and storage for read speed and comprehension.

One row per event at the finest grain available, written once and never updated. The workhorse of every warehouse. Insert-only, so it is trivially incremental.

One row per entity per fixed interval, whether or not anything happened. Daily balances, month-end inventory, weekly headcount. Measures here are usually semi-additive — never sum a balance across time.

One row per process lifecycle, revisited and updated as each milestone completes. Columns hold a date per step, so lag between steps is a subtraction. The only fact type you routinely update in place.

A fact table with keys but no measures. Records that something happened (a student attended a class) or that something was possible (a promotion covered a product). You count rows instead of summing columns.

A pre-summarised fact at a coarser grain, built from the atomic fact for speed. Kept alongside the detail, never instead of it. Must be rebuilt, not appended, whenever history restates.

Whether a measure can be summed across a dimension. Additive: across all of them. Semi-additive: all except time. Non-additive: none. Never store a ratio alone — store numerator and denominator and divide at query time.

One dimension with identical keys and meaning wherever it is used, so two facts can be compared side by side. Without it, "customer" means something different in each report.

An identifier with no attributes of its own — an order number, an invoice number. It lives in the fact table because a lookup table would hold nothing but the key. Still useful: it groups fact rows that belong to one transaction.

A single small dimension holding every combination of the low-cardinality flags and indicators that would otherwise clutter the fact table. Four yes/no flags become one key pointing at sixteen rows.

One physical dimension joined several times in different roles — order date, ship date, due date all point at dim_date. Expose each role as its own view so column names stay readable.

Volatile attributes (age band, income band, credit score) split into their own dimension so the parent dimension does not spawn a Type 2 row every week. The fact then carries two keys: the stable dimension and the mini-dimension.

Resolves a many-to-many between a fact and a dimension, or a ragged hierarchy. Carries an optional weighting factor so measures can be allocated rather than double counted. Patients with several diagnoses, accounts with several holders, org charts.

A prebuilt table with one row per calendar day and every attribute anyone will ever ask for: fiscal period, holiday flag, weekday name, week-of-year. Build it once. Never derive calendar logic from a timestamp at query time.

A dimension that joins to another dimension instead of to the fact table — dim_customer reaching out to dim_geography for country-level attributes it would rather not duplicate. Use sparingly: every hop is one more join between the fact and the answer.

A dimension built from a subset of another dimension's columns at a coarser grain, so a fact at that coarser grain — a monthly snapshot, say — still conforms to the same hierarchy. Still conformed: same keys, same meaning, just fewer rows and columns.

The attribute is fixed at first load and never updated, whatever the source does afterwards. Original credit score, date of birth, first-order channel.

Update in place and lose the old value. Correct for genuine data-quality fixes; wrong whenever history matters. Yesterday's reports will no longer reproduce.

Close the current row and insert a new version with a new surrogate key. Facts keep pointing at whichever version was current when they happened. Needs valid_from, valid_to, is_current. In dbt this is a snapshot.

Keep one prior value in a second column. Useful for a single planned realignment — last year's sales region alongside this year's. Only ever remembers one step back.

Keep the current row in the main dimension and push every past version into a separate history table, so the everyday join stays small.

Type 2 rows that also carry a current-value column, so one join answers both "what was it then" and "what is it now". Every historical row must be updated when the current value changes.

Type 1 overwrite on the base dimension, plus a Type 2 mini-dimension for the volatile attributes — and the base dimension also carries the mini-dimension's current key, so "profile this customer today" needs no history lookup. The fact still carries the mini-dimension key that was current when it happened.

The fact carries both the Type 2 surrogate key (what was true then) and the durable key (what is true now), so one fact table answers both questions without a Type 6 overwrite.

A late-arriving fact shows up after the event date and must join to the version that was current then. A late-arriving dimension forces a retroactive version insert. An early-arriving fact references a member that does not exist yet, so you insert an inferred row and backfill it.

The identifier the business and the source system use: SKU, email, order number. Meaningful, and therefore liable to be reused, reformatted or merged.

Any column, or combination of columns, that could uniquely identify a row. One candidate key is chosen as the primary key; the rest become alternate (unique) keys. BCNF is exactly the rule that every determinant must be one of these.

A meaningless key generated by the warehouse and used for every fact-to-dimension join. It insulates you from source key changes and is what makes Type 2 versioning possible at all. One per version of a dimension row, not one per entity.

A stable identifier for the entity itself that survives every Type 2 version. Carry it alongside the surrogate key so you can pivot between the historical view and the current one.

A deterministic hash of the business key used as the surrogate. Data Vault's standard, and the norm in dbt because it needs no sequence and computes independently in parallel loads. Deterministic means two warehouses derive the same key from the same input.

A reserved row in every dimension (key -1) that orphan facts point at, so nothing silently disappears from an inner join. Missing, not applicable and not yet known are three different rows, not one null.

A large, curated, structured repository of integrated data from many sources, modelled for querying and historical analysis. Schema on write.

A focused, department-specific subset of a warehouse — finance, sales, engineering — scoped so one team gets fast answers without touching everything else.

A vast pool of raw data kept in its native format — files, logs, JSON, images — on cheap object storage until someone needs it. Schema on read. Without a catalog and governance it degrades into a data swamp.

An open table format laid over lake storage that adds ACID transactions, schema enforcement, time travel and unified batch and streaming — warehouse behaviour on lake economics.

Splitting a table's physical storage by a column — almost always date — so a query filtering on that column only scans the relevant slice instead of the whole table. Pair it with clustering keys on whatever you filter on next most often.

A near-real-time, lightly integrated copy of current operational data. Holds present state rather than deep history, and serves operational reporting rather than analytics.

The process that moves and transforms data from one system to another, on a schedule or a trigger, and populates warehouses and lakes. Aim for idempotent runs: re-running the same window gives the same result.

ETL transforms before loading, in an external engine. ELT loads raw first and transforms inside the warehouse, which is what Snowflake and dbt do — the raw layer stays available to reprocess.

Reading a source database's transaction log to stream inserts, updates and deletes as they happen, instead of re-querying and reloading the whole table on a schedule. The backbone of low-latency ELT — no polling, no missed deletes.

Layered refinement inside the warehouse: raw landing, then cleaned and conformed entities, then the models people query. In dbt these are staging, intermediate and marts.

An organizational answer, not a technology: domain teams own and publish their own data as a product, on a shared self-serve platform, under federated governance.

A layer between the warehouse and every tool that queries it, where a metric — revenue, active users — is defined exactly once and reused everywhere: dbt Semantic Layer, LookML, Cube. Without it, "revenue" quietly means something different in each dashboard.

OLTP systems are normalized and row-oriented for many small concurrent writes. OLAP systems are denormalized and column-oriented for few enormous reads. Everything on this page is OLAP.