Settle the grain before you draw a table: building a star schema beside a client's transactional database

In your first week on site, the head of operations asks what sounds like an easy question: revenue for each product category, by province, by month. You open the database and find the data spread across six tables. The quickest way to get a number is a long SQL query run against the same database that is taking live orders.

The client has the data. It is organised for writing transactions, not for reading them back for analysis. Knowing how to read the existing schema and design an analytical model beside it decides whether you ship a dashboard in two weeks or spend two months stuck.

The sales database was never built to answer the boss’s question

Transactional (OLTP) systems are designed around normalisation. Normalisation exists to remove duplicate data and store it sensibly. In third normal form (3NF), every non-key attribute depends only on the primary key. So province names live in a provinces table, category names live in a categories table, and an order holds only IDs.

That design is very good for writes. AWS describes OLTP as handling large volumes of transactions well but not complex queries. Analysts need an OLAP system to analyse multidimensional data. That is why “by province, by category, by month”, a question with three dimensions, forces you to JOIN across a whole chain of normalised tables.

The difference reaches down to how data sits on disk. A columnar database reads only the columns a query needs, which cuts I/O and time for analytical queries. Picture a fact table with 20 columns and a report that needs four: a column-oriented engine touches only those four.

The price is that columnar databases write slowly and support ACID less well than row-oriented databases. They suit data that is written once and read many times. They cannot replace the OLTP system that takes orders.

Facts are numbers, dimensions are context

A dimensional model splits data into two kinds. Facts are measurements of business activity, usually numeric, and fact tables are normalised with little redundancy. Dimensions are descriptive context: which product, which store, which day.

The two kinds of table look very different. A fact table is narrow and long, with one event per row. A dimension table is denormalised and holds mostly text and descriptive attributes. One fact table in the middle with many dimensions around it is a star schema as AWS defines it. It is also why a dimensional model is often just called a star schema.

In practice there is rarely only one star. A complete model usually has several fact tables that share dimensions, known as conformed dimensions. For example, a sales fact and an inventory fact can share the same date table and the same product table.

Worked example: a pharmacy chain and Kimball’s four steps

Picture a client that runs a chain of pharmacies. The OLTP database has the tables orders, order_items, products, categories, stores and provinces. Written against the original schema, the head of operations’ question looks like this:

SELECT pv.name AS tinh, c.name AS nhom_hang,
       date_trunc('month', o.created_at) AS thang,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM order_items oi
JOIN orders o      ON o.id = oi.order_id
JOIN stores s      ON s.id = o.store_id
JOIN provinces pv  ON pv.id = s.province_id
JOIN products p    ON p.id = oi.product_id
JOIN categories c  ON c.id = p.category_id
GROUP BY 1, 2, 3;

(The aliases are Vietnamese: tinh is province, nhom_hang is product category, thang is month and doanh_thu is revenue.)

That is five JOINs for one question, run on the database that is taking orders. Kimball’s four-step process gets you out of this: choose the business process, declare the grain, choose the dimensions, then choose the facts.

The business process here is over-the-counter sales. The grain is the lowest level of detail the process records. Write it as one sentence: “each row is one line item on one receipt”.

Why not make the whole receipt the grain? Try a hypothetical order: two boxes of painkillers at 30,000 dong each, one bottle of vitamins at 120,000 dong and one box of face masks at 40,000 dong.

At receipt grain you have a single row of 220,000 dong and no way to separate out the 60,000 dong that belongs to the painkiller category. At line-item grain you have three rows and can answer any question by category.

The dimensions come from every “by” in the question: dim_date, dim_store (including the province name) and dim_product (including category, active ingredient and manufacturer), plus dim_customer if there is a loyalty card. The facts are the numbers at that grain: quantity, revenue, discount.

After the four steps, add one check: rewrite the client’s question on the new model.

SELECT st.province, pr.category, d.year_month,
       SUM(f.revenue) AS doanh_thu
FROM fact_sales_line f
JOIN dim_store   st ON st.store_key   = f.store_key
JOIN dim_product pr ON pr.product_key = f.product_key
JOIN dim_date    d  ON d.date_key     = f.date_key
GROUP BY 1, 2, 3;

Each dimension of analysis now needs just one JOIN, and the SQL reads almost like the boss’s question. That is the real value of a star schema: the client’s analysts can write their own queries without you sitting beside them.

Star or snowflake: when should you split a dimension further?

IBM describes the snowflake schema as a logical extension of the star schema in which dimensions are split into further sub-tables. For the pharmacy, dim_product would point to dim_category, and dim_store would point to dim_province.

	Star	Snowflake
Dimensions	Denormalised, one flat table	Further normalised into several tables
Storage	Repeats text such as category names	More economical
Queries	Fewer JOINs, easy for analysts to write	More JOINs, more complex
Suits	End users querying for themselves, BI tools	Very large dimensions, higher-level attributes that change independently

The practical advice: default to a star. A category name repeated a few thousand times in dim_product is rarely a meaningful storage problem, while every extra JOIN is one more place an analyst can get the query wrong.

Mistakes that force you to tear the model down and start again

The most expensive mistake is mixing grains. Put a receipt-level delivery fee into a line-item fact table and every SUM will add that fee once per line. If a measurement lives at a different level, it needs a different fact table.

The second mistake is denormalising everywhere. Denormalisation adds redundant data to make queries faster, so apply it only in derived models that serve BI, not in the core warehouse. Keep the core layer clean, and when the client changes requirements you can rebuild the layer above without touching the foundation.

The third mistake is locking the model in too early. AWS notes that OLAP cubes are rigid: once modelled, the dimensions and underlying data cannot be changed. If the client is still exploring its questions, build the star schema on ordinary tables before thinking about cubes.

The last mistake is failing to state the project’s scope clearly. A data mart is a subset of the warehouse for one business area or department. A warehouse spans the whole organisation.

A project that starts in the pharmacy chain’s operations department is a data mart. Saying so plainly to the client prevents the expectation that you are building a data warehouse for the entire company.

Showing this skill on your CV and in interviews

When reading FDE job descriptions, treat phrases such as “analytics”, “data warehouse”, “BI” and “dbt” as a signal to revise data modelling thoroughly before you apply.

On your CV, do not just write “designed a data warehouse”. Write it in this form: which grain you chose, how many facts and shared dimensions there were, and which business question used to take five JOINs on production and can now be answered by analysts themselves. Written that way, the reader can see that you work from the business question to the model rather than just listing tools.

To prepare for interviews, set yourself two problems and answer them out loud. Problem one: given the tables orders, order_items, products and stores, declare the grain of the sales fact in one sentence and explain why you did not choose receipt grain.

Problem two: the client wants to add a delivery fee charged per receipt. Where do you put it so that SUM does not multiply it?

Next time someone asks for “revenue by X, by Y”, don’t rush to open the SQL editor. Ask them what one row of their data actually is.