Warehouse Architectures & Layering

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.

Because the vault must be able to prove what each source said at each point in time. Every change inserts a new row stamped with a load date, the previous row is never touched, and loads stay restartable and parallel.

Because the bottom-up unit of delivery is one business process, not the enterprise. You model only the sources that process needs and publish a usable star schema; the top-down approach must integrate the enterprise model before any report exists.

Because it is the only copy of what the source actually sent. Keeping it append-only lets you recompute every downstream table after a logic bug or schema change, prove whether an error came from the source or from your code, and survive sources you cannot re-read.

Duplicated attributes make every correction a rewrite of history instead of a one-row dimension update, spread the same business definition across marts where copies drift, and make rebuild cost scale with history rather than with the size of the change.

Put the two queries side by side and find where they differ: filters, join grain, which date column, currency handling, and which dimension version. Then agree one owner and one definition, migrate both dashboards to it, and add a reconciliation test.

Because reconciling sources once, in a model of the enterprise rather than of any report, means every mart built on top inherits the same definitions and history. New sources and new marts attach to that layer instead of re-deriving the truth.

A semantic layer sits between physical tables and consumers, defining metrics, dimensions and join paths once in one reviewed place. Tools request a metric by name instead of writing their own SQL, so every report computes it identically.

Nest children when the table's grain must stay one row per parent and the children are bounded and always read with the parent. Flatten to one row per child when children carry their own measures or filters, and accept that parent measures then repeat.

Because a hash is computed from the business key alone, every pipeline can derive the same key without looking anything up. Hubs, links and satellites then load in parallel, in any order, across systems, with no sequence assignment step.

The raw vault holds source data with only hard rules applied — type casting, key standardization, hashing — so it stays an auditable copy of what arrived. The business vault holds derived structures built by applying soft business rules on top.

Yes, and it is the common shape today: raw landing, a reconciled integration layer built once, and dimensional star marts published on top for consumption. It buys inherited definitions plus a query surface users can navigate, at the cost of maintaining two models.

Materialise additive components in the mart and define the metric over them in the semantic layer. Store a computed metric in a column only when it must be frozen, is too expensive to compute at read time, or is consumed by tools that cannot reach the layer.

By recomputing rather than repairing. Because raw landings are retained unmodified, you fix the transform, delete and rebuild the affected window from raw, then rebuild every downstream table in dependency order. It works only if the transform is deterministic and depends on nothing mutable.

Layers that add no guarantee: uncatalogued bronze nobody can interpret, silver tables with no declared grain or tests, chains of near-duplicate copies, business logic embedded in the landing layer, dashboards reading raw directly, and no owner for any of it.

Carry the frozen as-of value as its own named column, built by joining the fact to the dimension version effective at event time, and keep the durable business key on the row so consumers can join to a small current-attribute table for today's value.

Adopt it when many sources must be integrated on business keys, schemas churn, and history must be provable — those are the problems it solves. Skip it with few stable sources: it adds a whole layer and marts are still required on top.

Decide from how much the sources disagree, how many consumers exist, and how much delivery pressure you are under. Heavy reconciliation and many consumers favour integrating first; a single clear source and an urgent question favour publishing a mart first.

Give every metric a named business owner, keep its definition in version control with review, publish certification status in a catalog, and make the governed path faster than writing private SQL. Governance that is slower than the workaround gets routed around.

Put a layer where responsibility changes hands and a guarantee is added, not where a diagram says three. Count the real handoffs — ingestion, conformance, business definition — give each an accountable owner with a tested contract, and delete any layer whose promise nobody can name.

Treat wide tables as generated, disposable serving artifacts over one modelled core. Business definitions live upstream and are selected, never restated; each mart must be reproducible from the core, owned, lineage-tracked and retirable. Duplicated values are fine; duplicated definitions are not.

A dependent mart is built from a central integrated warehouse; an independent mart is built straight from source systems for one department. Inmon's top-down architecture produces dependent marts, which is how it guarantees marts cannot disagree.

Headless BI runs the metrics layer as a standalone service with no visualisation of its own, exposed over SQL or an API. Dashboards, notebooks, spreadsheets and applications all request the same named metrics, so definitions stop living inside one vendor's tool.

The guarantee attached to it. A bronze table promises only fidelity to what the source sent; a silver table promises a declared grain, enforced types, resolved business keys and conformed values, backed by tests and an owner. Without that promise the name is decoration.

They make vault queries affordable. A point-in-time table pre-resolves which satellite version was in effect per key per snapshot date, and a bridge table pre-resolves multi-hop key paths, turning correlated lookups into plain equality joins.

Use a key-value column when attributes are sparse, per-tenant or high-churn and a typed column each would mean constant schema changes and mostly-null columns. Promote an attribute to a typed column once many consumers filter, group or document it.