Star Schemas and Ontologies: Modeling for Different Purposes
By Arshad Ansari
A star schema and an ontology do different jobs. Use the star schema to measure. Use the ontology to define. Problems start when one is asked to do the other's job.
This is part of a series on ontologies for data engineers. Here I put the two side by side.
What a star schema is for
A star schema is built around grain. Grain is the one sentence that says what a row in the fact table is. "One row per order line." "One row per account per day."
Once the grain is fixed, three things follow:
- Facts hold the numbers you add up: quantity, amount, duration.
- Dimensions hold the labels you group and filter by: date, product, region.
- Aggregation is safe, because every fact has the same grain.
That is the whole design. It is narrow on purpose. It makes "revenue by region by month" fast to write and hard to get wrong.
A star schema says little about meaning. A dim_customer table has columns. It does not say what a customer is, when someone stops being one, or how a customer differs from an account or a user.
What an ontology is for
An ontology states the concepts of a domain and how they relate. It names the kinds of things (customer, account, subscription, invoice), says how they connect (a customer holds accounts; an account has subscriptions), and gives each a definition people agree on.
It is about meaning and relationships. It does not care how many rows exist or how you sum them.
An ontology says little about aggregation. It will not tell you the grain of a fact table or whether a measure can be added across time.
Four questions, two owners
| Question | Star schema | Ontology |
|---|---|---|
| What is one row? | Owns it (grain) | Silent |
| How do numbers add up? | Owns it (additivity) | Silent |
| How do things relate? | Only as foreign keys | Owns it |
| What does the word mean? | Silent, or a column comment | Owns it |
Read the table by column. Each model is strong where the other is quiet. That is why they complement each other. It is also why neither replaces the other.
Where they meet
The ontology says what a customer is. The dimension table is where that definition lands in the warehouse.
Take a simple case. The ontology says: a customer is a legal entity with at least one active contract. An account is a login that belongs to one customer. A user belongs to one account.
The star schema then makes choices that follow from that:
dim_customerhas one row per legal entity. Not per login.- The fact table for usage has the grain "one row per account per day", with a key to
dim_customer. - "Active customer" is a filter on the contract, not a guess from login activity.
The definition came from the ontology. The grain and the keys came from dimensional design. If you skip the first step, you still build a star schema. It just encodes someone's private guess about the word "customer", and the next team guesses differently.
How each one fails when stretched
A star schema asked to carry meaning. The dimension table grows columns like is_real_customer, customer_type_v2 and status_new. Each one is a patch for a definition nobody wrote down. The model works, but nobody can explain it.
An ontology asked to answer metric questions. Someone tries to query the concept graph for monthly revenue. It has no grain, so there is nothing safe to sum. The answer is wrong, or slow, or both.
In both cases the model is fine. The job is wrong.
A working rule
- If the question starts with "how much" or "how many", it belongs to the star schema.
- If the question starts with "what is" or "how is this related to", it belongs to the ontology.
- If a column comment is doing the work of a definition, write the definition down once, in one place, and point to it.
You do not need a graph database or a special tool for the ontology. A reviewed document with named concepts, definitions and relationships is enough to start. Add tooling only when the document stops being enough.
Who should skip this
If you have one source system, a small team and a handful of reports that nobody argues about, you do not need an ontology. A clean star schema is enough. Write the ontology when two people give two answers to the same word, not before.
If you have AI workflows on top of your data, the definitions matter more. A model that reads dim_customer will take the column names at face value. Clear concepts above the tables give it, and your reviewers, something firm to check against.
Next step
If you are not sure whether your warehouse has a modeling problem or a definitions problem, start with the free Data Platform Teardown. You can run it yourself and keep the result.
Common questions
- Does an ontology replace a star schema?
- No. A star schema is built to aggregate facts fast and clearly at a stated grain. An ontology is built to state what things are and how they relate. They answer different questions.
- Can I build a star schema without an ontology?
- Yes. Most teams do. The cost shows up when two teams use the same word, such as customer, for different things.
- Where does the ontology sit relative to the warehouse?
- Above it. The ontology defines the concepts. The dimension tables are one way to implement some of them.
Get new posts by email
Data engineering notes like this one — what breaks and what it costs, in production.
What breaks and what it costs — pipelines, warehouse bills, and the failures that only show up in production. A few a month, never padded to hit a schedule. No sequence, no pitch deck. Reply 'stop' once and you're off — it reaches me, not a queue.
Want the whole playbook?
If this was useful, the long version is my book. Local-First Analytics — 313 pages, runnable code for every chapter — is the full build: DuckDB, Parquet and Arrow, from install to production. On Amazon, and chapter 1 is free to read here.
Get the bookNot ready to buy? Read chapter 1 free — the whole chapter, no email required.
Rather talk it through? Book a free 30-minute call. No slot that suits your time zone? Email info@hikmahtech.in.