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

> "Revenue by province, by product category, by month" sounds like a simple question. A client's sales database is rarely built to answer it, and the FDE is the one who has to build the bridge.

Bản gốc: https://fdetimes.net/en/guides/star-schema-grain-beside-oltp-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:

```sql
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.

```sql
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.

**Điểm mấu chốt:** Ask what each row of data represents before you draw any table.

## 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.

**Thử ngay tuần này:**

- Take a sample database with orders and order_items tables, write a one-sentence grain for the sales fact, then draw a star schema with 1 fact and 3-4 dimensions.
- Write the same reporting question in SQL on the original schema and on the star schema, and count the JOINs on each side.
- Add a second fact (inventory, for example) that shares dim_date and dim_product, then check whether the two facts can be compared with each other.

## Nguồn

- [7 data modeling techniques and concepts for business](https://www.techtarget.com/searchdatamanagement/tip/7-data-modeling-techniques-and-concepts-for-business)

- [What is Normalization in DBMS (SQL)? 1NF, 2NF, 3NF, BCNF Database with Example](https://www.guru99.com/database-normalization.html)

- [What is OLAP? - Online Analytical Processing Explained](https://aws.amazon.com/what-is/olap/)

- [What is a Data Mart?](https://www.ibm.com/think/topics/data-mart)

- [Data Mart vs Data Warehouse: a Detailed Comparison](https://www.datacamp.com/blog/data-mart-vs-data-warehouse)

- [What are columnar databases? Here are 35 examples.](https://www.tinybird.co/blog-posts/what-is-a-columnar-database)

- [What is a columnar database?](https://www.techtarget.com/searchdatamanagement/definition/columnar-database)

- [Data Modeling for Mere Mortals – Part 2: Dimensional Modeling Fundamentals](https://data-mozart.com/data-modeling-for-mere-mortals-part-2-dimensional-modeling-fundamentals/)