Data warehouse design: More than dragging tables together

We open a BI tool, connect two datasets, select "many to one" and build a chart. Everything runs. Somewhere in those clicks, we have also made several data architecture decisions. Often without realising it.

Knowing the business and knowing how to model its data are different skills. A finance analyst can understand card spend perfectly without knowing why a join duplicates it. The interface makes the operation easy. It cannot make the underlying assumptions true.

This is the gap I want to close: enough architecture to understand what the buttons mean, which modelling approach fits, and where dbt and a semantic layer belong.

We will cover:

• The questions to ask before trusting a relationship.
• One simple example, from raw payments to a shared metric.
• When stars, snowflakes and Data Vaults help, and when they add work.

What are we actually counting?

Before drawing anything, agree what the report should answer. "Daily settled card spend in GBP" is useful. "Join transactions to rates" describes an operation, not a requirement.

Five principles do most of the work:

• Grain: what does one row represent? A transaction, a settlement instalment and a daily total are different things.
• Keys: a primary key identifies one row uniquely and cannot be null. A foreign key references a key in another dataset. Matching column names prove neither.
• Cardinality and optionality: how many related rows can match, and is no match allowed?
• Aggregation: which values can be combined? Payments converted under the same currency policy can be summed; exchange rates should not be.
• History: does the report need today's corrected answer, the answer published last month, or both?

We also need an owner, refresh deadline and correction policy. Otherwise we can implement the wrong requirments perfectly.

Conceptual modelling names business things. Logical modelling defines grain, identifiers and relationships. Physical modelling implements tables, views and storage. Semantic modelling defines how those datasets become useful metrics and analysis paths.

View diagram in SlimNode

These are responsibilities, not four competing products. I cover the foundations in Data Modelling for Data Engineers.

Let's keep it simple: two payments and an exchange rate

Here is a very simple example. No giant banking platform needed. Two card payments and an exchange rate are enough to answer the main design questions.

Suppose finance wants settled spend in GBP. Our invented payments are USD 10 and USD 20, settled on the same day. At GBP 0.80 per USD, the total should be GBP 24.

The transaction grain is one settled payment, identified by transaction_id. For this example, transaction_date means settlement date. Split settlements would need a different grain.

The FX grain is one daily currency-pair rate within an immutable rate-set version. Its composite primary key is:

fx_rate_pk(rate_set_id, rate_date, base_currency, quote_currency)

Those four fields identify one rate together. None is independently unique.

An Explore, using Looker's terminology, is a starting point for queries with defined fields and joins. Our report supplies two parameters alongside transaction fields:

View diagram in SlimNode

The parameters are not transaction columns. Together with date and currency, they form an Explore-scoped semantic FK in this design, not a physical database FK. The transaction keeps its own PK. If FX already has another PK, reuse an appropriate key or declare a composite unique key, not a second PK.

For a fixed report context, we require each payment to match one rate. A rate may serve no payments or many:

View diagram in SlimNode. Four FX PK flags represent one named composite key. The crow's foot means 0..*; the other end means 1.

If missing rates are legitimate, that 1 becomes 0..1. An INNER JOIN removes unmatched payments; it does not establish a business rule that rates must exist. A LEFT JOIN preserves them, but does not make missing rates acceptable. Weekend rate selection also needs an explicit policy.

Why did 24 become 48?

Duplicate our matching FX row. Each payment now matches twice, producing four rows and GBP 48. This is fanout: a join multiplies rows and can inflate aggregates. Remove the rate entirely and an inner join returns nothing; a left join retains payments with missing conversion values. Flag missing conversions: SUM can otherwise ignore nulls and return an incomplete total.

The relationship dropdown can still say "many to one". That is a declaration, not a measurement. Looker documents relationship direction; the data still has to satisfy it.

Suggested keys need review. Approval should cover operands, types, component order, source aliases and cardinality; changing these requires another review. Separate tests check uniqueness, missing references and totals. Some warehouses, including BigQuery, do not enforce declared keys.

Approved, tested and enforced are three different states.

Real one-to-many relationships are not mistakes. They need compatible-grain aggregation or bridge/allocation rules, not a misleading dropdown setting.

So, ERD first or report first?

Start with the business question and source evidence. Sketch the grain, prototype a report, then review its relationships in an entity relationship diagram (ERD). Iterate between them as assumptions change. Existing models deserve inspection before replacement.

View diagram in SlimNode

Kimball's design process puts business needs and source realities behind the choice of process, grain, dimensions and facts. Neither the diagram nor the query is a substitute for that conversation.

Would a star schema do the job?

Usually, this is where I would start for our reporting example.

In a star schema, a fact table stores measurements at a declared grain. Dimensions describe them. Our transaction fact contains amounts and keys; merchant, date and currency dimensions provide context.

View diagram in SlimNode

This makes common reporting paths understandable. The price is managing repeated descriptive attributes, shared definitions and history.

Kimball is more than this shape. We can deliver spend first, then refunds, using conformed dimensions: agreed merchant and calendar definitions shared across processes. Its bus architecture supports incremental enterprise delivery, not disconnected departmental tables.

And when does a snowflake help?

A snowflake schema splits a dimension into related tables. Instead of repeating region attributes in every merchant record, we reference a region dimension:

View diagram in SlimNode. Other star branches are omitted. Both diagrams assume required dimension matches.

Use this when a shared hierarchy merits separate management. It reduces duplication but adds joins and history coordination. For ordinary BI, a flatter star is often easier to navigate. Microsoft explains these tradeoffs. Performance needs measurement, not a schema slogan.

Snowflake the schema is not Snowflake the platform. Either shape needs historical rules: a Type 2 dimension keeps versions, allowing a payment to reference the merchant region valid at settlement.

Here merchant_key identifies a historical version. A business merchant ID can repeat across versions, so joining on that ID alone can multiply payments.

What if three systems disagree about the merchant?

That is an integration problem before it is a dashboard problem.

Inmon's approach puts an integrated, historical warehouse before consumption-specific marts. Its core commonly uses third normal form, separating entities and dependencies to reduce redundancy. That core can feed star schemas; Inmon is not another name for snowflaking.

This earns its cost when finance, risk and operations need shared identities across conflicting systems. The benefit is reusable integration; the cost is coordination and more transformations before reporting. The Wiley design chapter explains the separation. It need not mean a giant upfront project: Inmon advocated iterative delivery.

Do we really need a Data Vault?

Not for our small payments example. Consider it when sources keep changing and reconstructing received history matters.

Data Vault puts business identifiers in hubs, relationships in links, and descriptive history in satellites. For payments and merchants:

View diagram in SlimNode. Arrows indicate references, not cardinality.

A Raw Vault retains source-oriented history; a Business Vault adds reusable derivations. Reporting marts still do the analyst-friendly work. AutomateDV documents the structures; Data Vault 2.0 also includes delivery practices.

An insurer integrating acquisitions may benefit from that separation. It pays with more tables, loading conventions and specialist skills. Hashes do not resolve conflicting business identities. Load time is not necessarily business-effective time. A vault is not automatic compliance, and a star can retain history too.

Where does dbt fit into this?

dbt data modelling implements transformations. It is not an alternative to Kimball, Inmon or Data Vault: dbt can help build their datasets.

A dbt SQL model is a SELECT statement in a file. References such as ref('stg_transactions') connect dependencies; materialisations determine how results become views, tables or other supported forms.

For a fixed GBP reporting policy, I would use this small pipeline, following dbt's staging, intermediate and marts convention:

I go further into project structure and dbt workflows in Building a Data Warehouse.

Test the FX composite key before joining, then transaction uniqueness, missing rates and the GBP 24 total. dbt data tests check assertions; they do not automatically enforce database constraints.

Use dbt when several reports need repeatable cleaning, conversion and history logic. Views can suit early development; tables trade refresh work and storage for cheaper repeated reads. Incremental processing needs explicit handling of late payments and corrected rates. For a small one-off exploration, a reviewed view may be enough.

Then what belongs in the semantic model?

The shared meaning of the analysis. dbt builds the converted payment rows; the semantic model tells BI tools how to use them.

For our fixed-GBP mart, the definition would be:

• Metric: settled_spend_gbp = sum(amount_gbp).
• Time: settlement date, not ingestion date.
• Breakdowns: merchant and merchant region, through a many-to-one dimension relationship.
• Scope: settled payments under the approved rate version; access restricted to authorised business data.

Now the finance dashboard and merchant report can request the same metric, grouped differently. The merchant key is unique in its dimension and repeats in the fact. dbt semantic models describe entities, dimensions and metrics; MetricFlow's joins use declared entity types to avoid unsupported fanout paths. Incorrect declarations still need data tests.

Use a semantic layer when multiple reports or tools must share metric definitions and navigation rules. A BI-native model can suffice for one governed application; a shared semantic service becomes useful across consumers. Avoid independently maintaining the same metric in both.

Dynamic reporting currencies can instead use approved semantic parameters and rate-selection logic, where supported. Our fixed-GBP dbt model deliberately removes that runtime choice. Precomputing several currencies or versions changes the grain to payment plus currency/version; unfiltered sums would duplicate spend again.

A dbt model builds the dataset. A semantic model explains how to ask it questions.

Which combination would I actually build?

These are layers we can combine, not rival products to pick from a menu:

View diagram in SlimNode. Illustrative paths, not mandatory stages.

The principles travel with us: grain defines facts; keys connect dimensions or hubs; history shapes Type 2 rows or satellites. dbt implements and tests those choices. Semantic definitions carry their meaning into reporting.

A retailer aligning sales and refunds may need conformed dimensions, not a vault. An acquisition-heavy insurer may justify deeper integration. Business requirements and operating capacity decide, not the industry label alone.

For our payments: retain source history, build tested dbt transformations and a small star, then publish approved semantic definitions. Add layers when a concrete requirement earns them.

Before we trust the dashboard

1. Agree grain, keys, metric meaning, owner and refresh target.
2. Review suggested relationships, including parameters outside entity columns.
3. Test duplicates, missing rates, late arrivals and historical corrections.
4. Reconcile the report to known totals and measure query cost.
5. Version definitions and preserve the rate versions and parameters needed to reproduce results.

Pinning model code does not freeze changing data. Keep "what we reported then" distinct from "what corrected data says now". We can optimise the rest seperately.

Doesn't AI do the modelling now?

It can propose joins, ERDs and tests. Someone still has to decide what a payment means and which exchange-rate policy finance accepts.

In my Medium article on IT jobs and AI, I placed platform architects among roles less likely to be replaced soon. I think the same reasoning applies to data architecture. Not untouched by AI, but still responsible for definitions, tradeoffs and accountability.

Conclusion

We do not need an architect's job title to build a useful BI model. We do need its basic questions. Agree the meaning, choose suitable structures, build and test the data, then expose governed metrics. The chart is the last step, not proof that the earlier ones were correct.

Recommended read

1. Kimball: design process, conformed dimensions and bus architecture, Type 2 history.
2. Microsoft: star and snowflake schemas.
3. Inmon: integrated warehouses and marts, iterative delivery.
4. Data Vault: Data Vault 2.0; AutomateDV hubs, links, satellites.
5. dbt: SQL models, project structure, data tests, semantic models, join behaviour.
6. Google Cloud: Looker relationships, BigQuery key constraints.