Data warehouse design: More than dragging tables together

Just imagine your business people can build the reports themselves including semantic model design - I like this idea so much. However, the reality is cruel and often we need a developer to do it. Adding one more breakdown dimensions is frustrating, especially when the person asking already knows the business inside out. The challenge starts when our BI tools ask to make a data architecture decision and do not explain it.

In this article we will start with a simple business queston and see how it translates into data architecture. We will cover the most common data platform design problems and will act as data architects tasked to design a new DWH. Which data platform architecture to choose, how to build an ERD, how to transofrm data and control the data transofrmation models with DRY approach and tests in CI. I want to connect those small decisions with the bigger warehouse design questions. Star schemas, Data Vault, dbt, semantic models. What would we actually need for a great data warehouse?

Everything in data worlkd starts with a conceptula model. A concept is what defines an entity in our DWH and provides a conceptual defition with metadata to support further development of logical and actual physical data models.

A typical business question translated into BI report

Imagine finance wants daily card spend in pounds. Before opening the semantic explore editor with all the joins, dimensions and measures, I'd ask what counts as spend. Authorised payments? Settled payments? The answer changes which records belong in the report, even before we touch SQL.

This is where semantic modelling starts - just add a little bit of metadata and we are good? Not that simple. Payments and merchants give us the conceptual model. Working out what a row represents, its keys and its relationships takes us into the logical model. Tables, views and underlying storage are the physical implementation. The definition of spend, including the date and breakdowns people can use, belongs in the semantic model - that metadata we were taling about.

Lets take a look into our report and underlying semantic model:

View diagram

I previously wrote about it in Data Modelling for Data Engineers. for the purpose of simplicity, let's keep the dataset small enough to check without a dashboard.

A simple report with just two records

We have USD 10 and USD 20, both settled today. With an exchange rate of GBP 0.80 per USD, the report should show £24. Nothing very interesting or too difficult - example. The design chart below is the mist important.

One transaction row represents one settled payment, identified by transaction_id. This is our grain. Here, transaction_date means settlement date. Split settlements are outside this example; including them would require deciding whether a row represents a payment or an individual settlement.

transaction_id is the transaction's primary key (PK): no duplicates or nulls. A foreign key (FK) refers to a key in another entity. Matching similarly named columns is not enough to establish either role.

In the FX table, the usd/gbp rate we find inside a named set. Imagine finance publishing a correction tomorrow. We still need the earlier set for today's signed-off report, so the correction gets a new version. Within each version we allow one rate per date and currency pair. All four fields belong to one key:

fx_rate_pk(rate_set_id, rate_date, base_currency, quote_currency)

Is it four keys or just one - that would be a composite primary key, not four independent PKs. If an existing FX table already has a different primary key, this combination could instead be declared a unique alternate key.

An Explore, for example - in Looker's terminology, provides a starting point for queries with fields and joins defined and acts as a guardrail, i.e. allowing business users to drug and drop things not breaking those semantic rules. Our payment supplies only half of the FX lookup. The report chooses the rate set and the currency we want back:

View diagram

PLus parameters - I would show them beside the transaction. Together with the two fields they form an Explore-scoped semantic FK. It describes this report's lookup and how to use it; it is not a physical constraint in our database and the payment keeps transaction_id as its own PK.

For this report we require one matching rate per payment. A rate can have no payments at all - that would be fine:

View diagram. The crow's foot notation here means 0..* (one to amny) transactions; the other end means 1 rate. Four PK flags belong to the single named FX key.

What about a payment whose rate hasn't arrived? I would take that question back to finance. Accepting such payments would make the rate end 0..1. Choosing a left join in the editor doesn't settle that business rule.

What is fanout?

Imgine we ingested today's rate into DWH twice and both payments find two matches. Our USD 10 appears twice, and so does USD 20. The report runs. Unfortunately, it says 48. The sum is now GBP 48 indeed. This is called a fanout issue, and a chart can display it quite happily and business just delivered the inflated KPI.

As Looker's documentation explains, "many to one" declares the relationship. It hasn't checked our FX rows for duplicates.

For example, a missing rates cause a different problem. An inner join can easily lose the affected payments. A left join keeps them, but their converted amounts are null. SUM skips those nulls; if every amount is null, the result is null, not zero. Neither of those should become a complete spend figure.

This is why I would keep suggested keys visibly marked Suggested, with approval required before publishing the relationship. A reviewer can check the fields, parameter bindings, types, component order and cardinality. Changed definitions need another review. Data-quality checks are a seperate capability: approval in our ERD says we agreed the rule, not that every row follows it. Even database declarations may be unenforced, as in BigQuery.

Of course, several matches can be legitimate. Allocating a payment across cost centres needs allocation amounts or weights, perhaps in a bridge table, and an explicit allocation grain. Hiding the extra matches would lose information.

So if a business user designs a semantic model - should they have drawn the ERD first?

I think, neither the diagram nor the report deserves to go first automatically. I'd start with finance's question and inspect the source records. Drafting an entity relationship diagram (ERD) and trying an Explore can then happen together. Our two payments are useful because we know the answer before running anything. Thi si called unit testing - where we test the logic of our data transformation.

View diagram

That fits Kimball's design process: understand business requirements first and check available data, then proceed to choose the process, grain, dimensions and facts. Discovering a duplicate or a new refund requirement gives us a reason to revisit the design.

SQL to transform data

We still need somewhere to put the SQL. This is where dbt modelling fits. A SQL model starts with a SELECT in a file; ref() connects it to other models. dbt can build a star or a vault. It doesn't choose our architecture.

For the GBP report, dbt's staging, intermediate and marts convention would give the currency calculation a clear home. stg_transactions and stg_fx_rates standardise names and types without changing grain. int_transactions_gbp matches all four FX operands against the approved rate version and calculates amount_gbp = amount * rate.

The report then reads fct_transactions, with one payment per row, dimension keys and the rate-set reference. dim_merchant holds merchant descriptions and history. My Building a Data Warehouse article goes further into organising this work.

I think sharing that transformation becomes valuable as soon as another team needs it. A view avoids storing another copy; a table avoids recalculating every query. Nice things to have as a golden standard in SQL world: Do not repeat yourself (DRY) approach and data logic tests.

Either way, we need tests for duplicate keys and missing rates, plus our GBP 24 example. dbt data tests check data; they do not enforce database constraints. Late payments and corrected rates also need reprocessing. An append-only incremental job will miss them ocassionally, however tidy its SQL looks.

Semantic modelling

Why add a semantic model as well?

Because another report can read the right rows and still use the wrong date. For our example, settled_spend_gbp = sum(amount_gbp) belongs on settlement date, using the approved rate version and merchant relationship. This is the definition we want reports to share.

The merchant key is unique in the dimension and repeats in the fact. For example, a dbt semantic model could describe it like this (dbt 1.12+ excerpt; other configuration omitted):

models:
  - name: fct_transactions
    columns:
      - name: merchant_key
        entity:
          name: merchant
          type: foreign
  - name: dim_merchant
    columns:
      - name: merchant_key
        entity:
          name: merchant
          type: primary

MetricFlow's joins use those declarations to block unsupported fanout paths. A duplicate merchant key can still slip into the data, though. The declaration will not find it; a uniqueness test will.

For one governed BI application, its own semantic model may be enough. Several tools disagreeing about spend would give me a better reason to introduce a shared semantic service.

And storing both GBP and EUR conversions adds another decision. The USD 10 payment now has a row for each currency. Keep several rate versions and those multiply it again. Our grain has changed; sums need one reporting currency and rate version selected. Renaming a field will not convert the stored GBP amount.

Hybrid approach

Here, "dbt modelling" means SQL transformation models. The semantic layer could be dbt's own Semantic Layer or another tool.

Star schema or Snowflake?

For simplicity we can start with a star schema. For payment reporting I would put the amounts and dimension keys in a fact table, one row per payment. Merchant, date and currency descriptions sit in dimensions directly connected to it. That gives us a star schema:

View diagram

It is fairly easy to explain and the reporting joins stay clear. Kimball's approach also lets us deliver this process before tackling anything else, i.e. refunds. Have you hear about Kimball Enterprise Bus? Shared merchant and calendar definitions, or conformed dimensions (CD), connect those deliveries through the bus architecture. Independent departmental stars with incompatible merchants would miss that point.

What would a snowflake approach change?

Imagine a merchant with rows that repeat the same region descriptions. We could move region into its own table adn leave a reference in the merchant dimension - that extra branch makes the design a snowflake schema. Normalised. The name has nothing to do with choosing Snowflake as our database :-)

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

There is less repetition, but reports need another join. A centrally maintained hierarchy would make that sufficient enough - should work. Microsoft's guidance covers the reporting tradeoffs. I would also supply as much metadata as possible.

History matters in both designs. After a merchant moves region, should old payments move too? Have you heard about SCD 1,2,3,4,5? With a Type 2 dimension, we retain the old row and give the new version its own merchant_key. Payments reference the version valid at settlement. The business merchant ID now occurs more than once; joining on it alone would bring the duplicate problem back. Two columns track the history - available_to; available_from.

What if different business units use different defintions?

What if the departments cannot agree on a merchant definition, schema and SCD approach? That would make Inmon's approach more relevant. It puts an integrated, historical enterprise warehouse before the reporting marts, commonly using third normal form (3NF) in the core. We do the cross-department identity work there, rather than repeating it in every mart.

The warehouse and mart distinction still leaves room for dimensional reporting above that core. I see the attraction when several departments need the same reconciled entities. I also see the extra coordination before our small report gets delivered. Inmon's "top-down" approach does not require one enormous release - as described in iterative delivery this can be a step by step process.

Data vault

And where would Data Vault help? Consider an insurer company acquiring businesses - new systems keep arriving, while older source deliveries still need to be traceable. That is a stronger reason to use Data Vault architecture where reports run on marts built above the vault. Data vault is great for tracability where histories evovle independently.

Data Vault separates warehouse data into:

• Hubs: business keys, such as customer IDs.
• Links: relationships between those keys.
• Satellites: descriptive attributes and their history.

For example, CRM and billing both describe a merchant:

• Conflicting values: CRM says North; billing says West. Separate satellites retain both accounts. Choosing the reporting value happens downstream, without discarding either source's evidence.
• Replacing CRM: a new CRM can feed a new satellite while the old satellite retains its history. We need new mappings, but need not rewrite the existing billing history.
• Tracing corrections: record-source and load timestamps show which system supplied a value and when we received it. Business-effective timestamps must be captured separately to distinguish changed Monday from arrived Friday.

Shared hubs still require compatible business keys. Data Vault does not automatically reconcile different merchant identities.

View diagram . Arrows indicate references, not cardinality.

Here, hubs hold business keys, links record their associations and satellites hold descriptive history. Source-oriented history belongs in the Raw Vault; reusable business derivations can live in the Business Vault, with marts making the result convenient to query. AutomateDV's examples explain the structures, while Data Vault 2.0 includes the wider delivery methodology.

All those tables need loading conventions and people who understand them. Hashing two conflicting merchant IDs will not reconcile them, and arrival time is not necessarily when a business change happened. I would want enough source complexity and audit value to justify that cost. A star can preserve history too.

SCD 2 plus retained source data and lineage can support these needs too. Data Vault just standardises that separation across an integration platform, at the cost of more tables, loading logic and reporting joins.

Choosing something we can live with

View diagram . Illustrative paths, not mandatory stages.

For this report I would choose the small star, shared dbt transformations and reviewed semantic definitions. A reusable region hierarchy might justify snowflaking. Enterprise identity integration could justify an Inmon-style core; frequent source changes and substantial audit needs could justify a vault. Both can feed dimensional marts. Team skills, delivery time and maintenance costs belong in that conversation as much as the diagram.

Reproducing last month's figure also takes more than keeping old rates. We need the source data, model version and report parameters that produced it. Otherwise finance can remember approving GBP 24 while the refreshed report tells a different story.

Conclusion

I don't think easier report building makes data architecture less important. It brings the decisions closer to people who previously never had to make them. That can be useful, provided the tool leaves room to ask what the numbers mean.

An AI assistant can help with SQL and the diagram. Choosing Friday's rate for a Sunday payment is another matter: perhaps that is exactly finance's policy, or perhaps nobody asked. This is part of why I put platform architects among the roles less likely to disappear in my article on IT jobs and AI. That is my judgement, not a guarantee. The work I would miss most is the discussion around the report, when someone knows enough about the business to say the answer looks wrong.

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.