Data Modeling Theoretical

The reasoning

The conceptual level is the business vocabulary. It lists entities such as Customer, Order and Product and the relationships between them, and nothing else. A product owner can read it and say "yes, that is our business" or "no, a subscription is not an order".

The logical level adds the detail a designer needs: every attribute, the primary key of each entity, the foreign keys, and the cardinality of each relationship. It is still engine-neutral. The same logical model can become PostgreSQL tables or a Snowflake star schema.

The physical level is the DDL. It chooses column types, surrogate keys, indexes or clustering, partitioning, and whether a relationship becomes a foreign key constraint or a test. It is the only level the database sees.

The separation matters because the levels change at different speeds. The conceptual model changes when the business changes. The physical model changes when you move from Redshift to BigQuery. Mixing them means a storage decision rewrites the business vocabulary.

The reasoning

An entity becomes a table; an attribute becomes a column; a relationship becomes a foreign key or a bridge table. The modeler's first job is to name the entities so that each one has a single, clear identity.

One-to-many is the common case: one customer, many orders. The foreign key goes on the many side, `orders.customer_id`. One-to-one is rare and usually a sign that two tables could be one, or that one side is optional and kept apart on purpose.

Many-to-many cannot be stored with a single foreign key. One student takes many courses, one course has many students. It needs a third table with both keys, an enrolment or bridge table, which is where the relationship's own attributes live, such as the enrolment date.

Cardinality matters because it predicts join behaviour. Joining a fact to a dimension on a one-to-many key keeps the fact's row count. Joining through a many-to-many without a bridge duplicates rows and inflates every sum. Optionality, whether a side can have zero, decides inner against left join.

The reasoning

Uniqueness is the property; the key terms describe how tight the set is. (customer_id, email) is a superkey if customer_id alone is unique, but it is not a candidate key, because email is unnecessary.

A table can have several candidate keys. An employee table may have employee_id and national_id, both unique. You pick one as the primary key and the others become alternate keys, often enforced with a unique constraint.

A composite key is a key with more than one column. Fact tables often have a composite natural key: (date_key, store_key, product_key) for a daily snapshot. Order lines are (order_id, line_number). Composite keys are correct and common; the choice is whether to also add a single surrogate column for convenience.

In a pipeline these terms become tests. The primary key is asserted unique and not null after every load. A duplicate on the primary key means the load ran twice or the grain was misunderstood. A candidate key that stops being unique, such as a reused email, is a business finding worth raising.

The reasoning

Third normal form allows one odd case: a dependency where a non-key attribute determines a key attribute. The classic example is (student, course, instructor) where each instructor teaches one course. Instructor determines course, but instructor is not a key. Updates to an instructor's course can go inconsistent.

BCNF forbids that case. Every determinant must be a superkey. The fix is to split into (instructor, course) and (student, instructor). Most tables in 3NF are already in BCNF; the difference appears only with overlapping candidate keys.

4NF addresses a different problem. Suppose an employee has several skills and several languages, and they are independent. A table (employee, skill, language) must hold every skill paired with every language, so adding one language adds a row per skill. That is a multi-valued dependency. 4NF says split into (employee, skill) and (employee, language).

In warehouse work the practical echo is the many-to-many bridge. Two independent multi-valued attributes on one dimension row, say a patient's diagnoses and medications, must each get their own bridge. Putting both in one bridge multiplies rows exactly as the 4NF example does.

The reasoning

The terms overlap, and the useful distinction is who assigns them. A natural key comes from the world: an ISBN, a VAT number. A business key comes from the operating system of record: customer number 10045. A surrogate key comes from the warehouse load and means nothing outside it.

Kimball's rule is that every dimension gets a surrogate key and facts reference it, not the business key. The reason is history. Under SCD Type 2, customer 10045 may have three rows, one per address version. The business key is no longer unique in the dimension, so the fact needs another column to point at one version.

Surrogates also insulate the warehouse from source changes. Business keys get reused, reformatted, or collide when two source systems merge. A surrogate stays stable. It also lets you add special rows, such as key 0 for "unknown", that no source produced.

The costs are a lookup on every fact load to translate business key to the current surrogate, and a key that users cannot read. Data Vault 2.0 instead hashes the business key, which removes the lookup but keeps the business key as the identity.

The reasoning

A normal fact row says "this happened, and here is how much". A factless row says only "this happened". A row in attendance holds (date_key, student_key, class_key) and nothing else. The question "how many students attended class 7 on Monday" is `COUNT(*)` with two filters.

The second use is coverage. A promotion coverage table records every (week, store, product) that was on promotion. Alone it answers "what was promoted". Joined against sales, it answers "what was promoted but did not sell", the question the sales fact cannot answer because non-sales have no row.

Many designers add a column `count` that is always 1. It costs nothing and lets BI tools sum a measure instead of counting rows, which some tools handle better.

The grain statement is still required. "One row per student per class per date" is a grain. Without it, duplicates from a double load are invisible, because there is no measure to look wrong.

The reasoning

The star is the unit: one fact table in the middle, one join away from each dimension. A real business has many processes, so it has many facts: orders, shipments, returns, inventory. Drawn together they look like constellations, which is where the name comes from.

The value is in the shared dimensions. If sales and inventory both use the same product dimension with the same surrogate keys, a report can show sales and stock side by side per product. That is drill-across, and it is the point of a warehouse rather than a pile of reports.

The condition is conformance. Two product dimensions with different keys or different category names cannot be compared. Kimball's bus matrix is the planning tool: rows are business processes, columns are dimensions, and a tick means the process uses that conformed dimension.

Snowflaking is orthogonal. Any fact in a galaxy may have snowflaked dimensions; the galaxy term describes how facts relate to each other, not how a dimension is normalised.

The reasoning

The matrix is built before any table. You list the processes the business runs and the dimensions it describes them with. A process that touches customer, product and date gets three marks. Reading down a column shows every process that needs that dimension.

A column with many marks is a conformed dimension by necessity. Customer appears in orders, support tickets and payments, so one customer dimension must serve all three. Building it once and well is the highest-leverage work in the warehouse.

Reading across a row shows the scope of one fact table. The first fact to build is the one the business values most that touches dimensions you can conform quickly. That is how a warehouse gets delivered in increments without becoming three incompatible marts.

The matrix is also the document you keep. When a new process arrives, you add a row and see which dimensions already exist. When someone proposes a second customer table, the column shows the damage.

The reasoning

The star schema rule is that dimensions are flat and denormalised. If a customer's country has a region and a region has a manager, all three live as columns on the customer row. An outrigger breaks that rule by moving some attributes to a second table reached through the dimension.

The acceptable case is a date on a dimension. First purchase date, hire date, contract start. Pointing it at the date dimension gives every calendar attribute for free, fiscal period, holiday flag, without copying them onto the dimension. Kimball's own guidance names this as the common legitimate outrigger.

The other acceptable case is a large block of low-cardinality attributes that few queries use, where keeping them on the main row bloats it. Even then, many teams prefer a mini-dimension joined from the fact instead, which is SCD Type 4.

The cost is a two-hop join and BI tools that expect dimensions one join from the fact. The usual failure is a modeler building outriggers everywhere because the source system was normalised. The fix is to flatten at load time.

The reasoning

An account with two owners has two bridge rows. Joining a balance fact through the bridge returns the balance twice. Summing gives double the money. That is the many-to-many fan-out, and it is the reason bridges are feared.

The allocation factor fixes the sum. With factors 0.5 and 0.5, each owner is credited half the balance and the total is correct. With 0.7 and 0.3 the business decides who gets more. The constraint is that factors within one group sum to exactly 1.

Both reports are valid. "How much money is attributable to each owner" is the weighted report and uses the factor. "How much money is each owner connected to" is the impact report and ignores the factor on purpose, accepting that it does not add to the total.

Floating-point factors that sum to 0.9999 leak money. Store them as decimals, test the sum per group, and decide the rounding rule for a 3-owner account before the first load.

The reasoning

Some date attributes are computable: year, month, weekday. Many are not: the fiscal quarter when the fiscal year starts in April, whether a day is a trading day, which marketing week it falls in. A table holds them once, and every query reads them with a join rather than reimplementing them.

The key is usually an integer like 20261101, readable and sortable, or the date itself in engines that partition by date. Kimball recommends a surrogate but notes the date case is the one where a meaningful key is acceptable.

The table is small, a few thousand rows for a decade, and is generated by a script, not loaded from a source. Columns typically include the day, week, month, quarter, year, their fiscal equivalents, day-of-week name, a weekend flag, a holiday flag per country, and relative markers such as is_current_month.

It also plays roles. The same table joins as order_date, ship_date and delivery_date through three foreign keys, a role-playing dimension. Time of day is usually a separate time dimension or a plain timestamp, because a row per second is too many.

The reasoning

The fixed-depth case is easy. Product has category and department, so the dimension row carries all three columns, and drill-down is a `GROUP BY` on a different column. This is the denormalised star pattern and it works because the depth never varies.

Ragged means the depth varies. One employee is four levels below the CEO, another is two. You cannot make level_3 a column, because for some rows it is empty and for others it is a leaf. Geography is often ragged too: a city-state has no region.

Option one is a parent pointer: each row carries its parent's key. Querying "everyone under manager X" needs a recursive CTE, which BI tools handle poorly. Option two, Kimball's hierarchy bridge, stores one row per (ancestor, descendant, depth) pair, including each node with itself. Then "everyone under X" is a simple join to ancestor = X.

The bridge is larger, roughly nodes times average depth, and must be rebuilt when the tree changes. Some teams fix the depth instead by padding: repeating the leaf value down to the deepest level so every row has all columns. That trades truth for speed and must be documented.

The reasoning

In Inmon's design the enterprise data warehouse is a normalised, integrated store, and marts are downstream, dimensional, and built per department from it. The warehouse is the single source; a mart is a view into it.

In Kimball's design there is no separate normalised layer. The marts are the dimensional models, and the conformed dimensions on the bus are what make them one warehouse. A mart is a group of fact tables for one process area.

The dangerous kind is the independent mart. Marketing loads customers from the CRM with its own rules, finance loads them from billing. The two marts disagree on customer count and neither is wrong by its own definition. That disagreement is what conformance exists to prevent.

In modern warehouses a mart is often a schema of final models in dbt, built from a shared core layer. The vocabulary is Inmon-shaped even where the core is dimensional: shared layer first, audience-specific marts after.

The reasoning

Type 2 keeps history by adding a row per change. For a dimension with many rows and attributes that change often, that explodes. Kimball's Type 4 answer is the mini-dimension. Move the fast-changing attributes, such as age band and income band, into a small table of every combination, and give the fact a second foreign key to it. The main dimension stays slow.

The mini-dimension is small because it stores combinations, not customers. Ten age bands times ten income bands is a hundred rows. Each fact row points at the combination that was true at the time, so history is preserved without touching the customer dimension.

Type 6 answers a reporting problem. Type 2 alone reports sales by the region the customer was in at the time. The business also wants sales by the customer's current region, restated. Type 6 keeps the Type 2 rows and adds a `current_region` column on every row, overwritten for all versions whenever the region changes.

Type 6 doubles the write work, because a change updates every historical row for that member. Type 7 is Kimball's cheaper variant: keep a durable key on the fact and join to the current row through a view. Both exist because "as it was" and "as it is" are both legitimate questions.

The reasoning

When a customer moves city, the old row gets valid_to set to the change date and is_current false. A new row is inserted with valid_from at the change date and is_current true. The business key is the same; the surrogate key is new.

The point-in-time join is `ON f.customer_bk = d.customer_bk AND f.order_date >= d.valid_from AND f.order_date < d.valid_to`. With a half-open interval, from inclusive and to exclusive, consecutive versions never overlap and never leave a gap. That is why most teams choose it.

The open row needs a valid_to that the comparison can use. Null forces `OR valid_to IS NULL` into every query and defeats some optimisers. A sentinel such as 9999-12-31 keeps the predicate simple. Pick one and test for it.

is_current is redundant with valid_to but convenient. Current-state queries read `WHERE is_current` and skip the date logic. The failure mode is two current rows for one business key after a bad load, so a uniqueness test on (business key) where is_current is standard.

The reasoning

Transaction facts are meant to be append-only, because an event happened once. Reality adds corrections. The question is whether yesterday's report should still be reproducible after the correction.

Update in place is simplest: find the row by its natural key and overwrite the measure. The old value is gone, so a report run last week cannot be reproduced. It is acceptable when the business only ever wants the current truth.

Offsetting rows are the accountant's answer. A correction from 100 to 80 inserts a row with minus 20 and a correction flag, dated when it was known. Sums are right by any date, and the audit trail is complete. Row counts no longer equal event counts, so count queries must filter the flag.

Versioned rows keep both: each correction inserts a full new row with a higher load timestamp, and a view picks the latest per key. It supports "what did we report on the first" by filtering on load time. Accumulating snapshot facts, which update as a process moves through milestones, are the designed exception where updates are expected.

The reasoning

The hub is the list of things the business knows about: every customer number ever seen, every product code. It never changes once a key is inserted. Its job is identity. In Data Vault 2.0 the primary key is a hash of the business key, which lets loads run in parallel without lookups.

The link is a relationship: customer placed order, order contains product. It stores the hash keys of the hubs it connects and its own hash key. Like the hub, it is insert-only. A new relationship adds a row; nothing is updated.

The satellite carries the context: the customer's name, address and status, each row stamped with a load date and the source system. A change in the source inserts a new satellite row. History is kept automatically, like SCD Type 2 without end dates, and the current row is the latest load date.

The reason for the split is auditability and agility. Adding a source adds a satellite without touching the hub. Adding a relationship adds a link. Nothing already loaded is rewritten, which is why Data Vault is sold as the raw, integrated layer. Its cost is query complexity, so a dimensional layer is built on top for consumers.

The reasoning

Relational databases cannot store many-to-many with one foreign key, because a column holds one value. Students take many courses and courses have many students, so an enrolments table holds (student_id, course_id). That pair is unique, so it is the primary key, and attributes that belong to the pair, enrolment date and grade, live there.

In a star schema the problem appears when a fact relates to several members of one dimension at once: a sale with several sales reps, a diagnosis set per visit. Kimball's answer is a bridge table. The fact points at a group key; the bridge maps group key to each member key; the dimension holds the members.

The bridge is where fan-out happens, so it carries an allocation factor when sums must be preserved. It may also carry validity dates when group membership changes over time, which turns it into a Type 2 bridge.

The alternative when the set is small and fixed is to flatten: columns rep_1, rep_2, rep_3 on the fact. It avoids the bridge and breaks as soon as a fourth rep appears. Choose it only when the maximum is a business rule.

The reasoning

Reference data answers "what are the valid values". ISO 3166 country codes, ISO 4217 currencies, the eight order statuses your system allows. It is often external or standards-based, changes rarely, and is shared across every system in the company.

A lookup table is the storage: code, name, sometimes a sort order and an active flag. Facts and other tables store the code, and the lookup supplies the readable name. Validating a load against the lookup is the simplest data quality test there is: every code in the fact must exist in the lookup.

Master data is different in kind. A customer has identity, hundreds of attributes, duplicates across systems and a lifecycle. Managing it is MDM: matching, merging, and a golden record. Reference data has none of that complexity; its challenge is agreeing on one list.

Dimensionally, a small reference set such as order status becomes either its own tiny dimension or a column in a junk dimension that bundles several low-cardinality flags. The large master entities become the conformed dimensions on the bus.

The reasoning

A sale posts at midnight for a customer created at 00:02, and the customer feed runs hourly. When the sales load runs, the customer key is missing. Dropping the row loses revenue; failing the load blocks everything for one row.

The placeholder approach inserts a dimension row with the business key and null or "unknown" attributes, assigns it a surrogate, and loads the fact. When the real attributes arrive, the placeholder is updated in place. The fact already points at the right surrogate, so nothing downstream moves.

The suspense approach parks the fact row until the dimension exists, then loads it. It keeps the dimension clean but delays the fact and needs a process to retry and a report for rows stuck too long.

Under Type 2 the late dimension creates a second problem. A change that arrives late may need a version inserted in the middle of history, with the surrounding valid_from and valid_to adjusted. Facts in that window must be re-pointed. That is why many teams store the business key on the fact as well as the surrogate.

The reasoning

A business process produces events, and each event has measurements and context. A sale has an amount and a quantity; it happened on a date, in a store, for a product, to a customer. Dimensional modeling stores the measurements in a fact table and each kind of context in a dimension table.

The result is a star: one fact in the centre, dimensions around it, each one join away. Queries read "sum of amount, by product category, for last quarter" as a filter on two dimensions and a sum on the fact.

The design steps are fixed: choose the business process, declare the grain, identify the dimensions, identify the facts. The grain comes before the facts, because a measure that does not fit the grain belongs in another table.

It trades write-time normalization for read-time simplicity. Dimensions are flat and redundant on purpose. Its contrast is Inmon's normalised warehouse and Data Vault's hubs and satellites, both of which usually feed a dimensional layer for consumers anyway.

The reasoning

The fact table is narrow and long: a surrogate or natural key, a foreign key per dimension, and the measures. The dimensions are wide and short: many descriptive columns, comparatively few rows. Dimension attributes are not normalised into sub-tables; category and department both sit on the product row.

Every query follows the same pattern: filter on dimension attributes, join to the fact on surrogate keys, aggregate measures. Optimisers recognise it, and BI tools model it directly.

The price is redundancy. A category name is repeated on every product row in that category. Columnar storage compresses it away, and a single loading process keeps it consistent, which is why warehouses accept it.

Its contrast is the snowflake schema, where dimensions are normalised into chains of tables, and the galaxy, where several facts share conformed dimensions. A real warehouse is usually a galaxy of stars.

The reasoning

Take the flat product dimension of a star and pull category into its own table with a key, then department out of category. The product row now holds category_key only. Drawn out, the dimension branches like a snowflake.

Storage drops because the category name appears once, not once per product. A rename touches one row. Those were the strong arguments when storage was expensive and dimensions were updated by hand.

Reads pay for it. A sales-by-department report joins fact to product to category to department. BI tools that expect one join per dimension struggle, and users have to understand the chain.

Kimball's position is to avoid it in the presentation layer and flatten at load time, and to allow outriggers only for a few cases. Snowflaking is reasonable in the integration layer, where tables are loaded rather than queried, and in very large dimensions where the sub-attributes are big and volatile.

The reasoning

The grain comes first: "one row per order line", "one row per account per day". Every measure on the row must be true at that grain. Order total does not belong on an order-line row, because summing it would multiply it by the number of lines.

Additive measures are what make a fact useful: sum revenue by any dimension and the answer is right. Semi-additive measures, balances and inventory levels, sum across accounts or stores but not across days; you take the last day's value or an average. Non-additive measures such as margin percent must be recomputed from their additive parts.

Kimball names three fact table types. A transaction fact has a row per event. A periodic snapshot has a row per period per entity, such as daily balance. An accumulating snapshot has one row per process instance, updated as it passes milestones, such as an order from placement to delivery.

Facts are usually the largest tables and are the ones that get partitioned, usually by the event date, and clustered by the keys most filtered.

The reasoning

Dimensions carry the words of a report. "Revenue by region by month" reads region from the customer or store dimension and month from the date dimension. They are wide and comparatively short, and their attributes should be readable text, not codes.

A conformed dimension has the same keys and attributes wherever it is used, so facts can be compared across it. A degenerate dimension is something like order number: it identifies a transaction but has no attributes of its own, so it stays on the fact as a column.

A junk dimension collects small flags, such as payment type, gift flag and channel, into one table of combinations so the fact does not carry a dozen tiny foreign keys. A role-playing dimension is one physical table, usually date, joined several times under different aliases.

A bridge dimension resolves a many-to-many between a fact and a multi-valued dimension, often with an allocation factor. Slowly changing dimensions describe how any of these keeps history, which is a separate axis from type.

The reasoning

In `customers`, `customer_id` is the primary key: one value per row, never null, never repeated. In `orders`, `customer_id` is a foreign key: it may repeat, because a customer has many orders, and each value must match a customer row.

OLTP databases enforce both. An insert with a duplicate primary key fails; an insert with a foreign key that matches nothing fails. The guarantee is called referential integrity.

Cloud warehouses such as Snowflake, BigQuery and Redshift accept both declarations but, per their documentation, do not enforce them. The pipeline must test uniqueness of the primary key and existence of every foreign key after each load.

In a star schema the fact table's foreign keys point at dimension surrogate keys. The fact's own primary key is usually the composite of those foreign keys plus a degenerate dimension, or a surrogate of its own.

The reasoning

1NF: each cell holds one value, and there are no repeating columns like phone1, phone2, phone3. A list of phone numbers becomes rows in a phones table.

2NF: with a composite key (order_id, product_id), a column such as product_name depends only on product_id, half the key. It moves to a products table. 2NF is automatically satisfied when the key is a single column.

3NF: no transitive dependency. If an orders table holds customer_id and customer_city, city depends on customer, not on order. It moves to customers. The slogan is "the key, the whole key, and nothing but the key".

The purpose is write safety. Each fact in one place means an update cannot leave two versions. OLTP schemas target 3NF or BCNF. Warehouses deliberately denormalise dimensions because they have one writer and many readers.

The reasoning

The normalised form of customer, country and region is three tables. The denormalised form is one customer row with country and region as columns. The region name repeats on every customer in it.

Reads get one join instead of three. Columnar engines compress the repeats, so storage grows less than the row count suggests. BI tools understand a flat dimension directly.

Writes get harder. A region rename updates many rows, and if two jobs load two copies of the same attribute, they can drift. The control is a single loading process and tests that compare the copies.

Star schema dimensions, One Big Table designs and summary tables are all denormalisation done on purpose. The failure mode is denormalising without lineage, so no one knows which copy is authoritative.

The reasoning

Subject-oriented: tables are about customers and sales, not about the billing application's screens. Integrated: the same customer from three systems is one customer with one set of codes. Time-variant: history is kept, so last year's view is still available. Non-volatile: data is loaded in batches and not updated by users.

An operational database, OLTP, is the opposite on each axis. It is application-shaped, holds current state, overwrites in place, and is normalised so that thousands of concurrent writes stay consistent.

The warehouse is built for analytical queries: scanning many rows, aggregating, joining a few large tables. That is why it uses columnar storage, denormalised dimensions and partitioning, and why its workload is read-heavy with one writing pipeline.

Modern platforms blur the storage line, a lakehouse holds both raw files and tables, but the definition still holds for the analytical layer: integrated, historical, loaded, organised by subject.

The reasoning

Chen introduced the ER model in 1976 as a way to describe data independently of storage. Entities are rectangles, relationships are diamonds or lines, and cardinality is marked at each end. It maps directly to tables, columns and foreign keys.

It produces normalised schemas, because each entity is stored once and relationships are keys. That suits OLTP systems, where many applications write and consistency is the goal.

Inmon's warehouse uses ER modeling for the integrated layer, and Data Vault is a specialised ER style for the same purpose. Kimball skips the normalised layer and models the presentation layer dimensionally from the start.

Most teams use both. The source systems are ER-modelled, the warehouse core may be, and the marts users query are dimensional. Knowing which tradition a table belongs to tells you whether to expect a flat dimension or a chain of joins.