Data modelling: when it's worth doing, and what it fixes

Data modelling earns its place once you have a warehouse and people writing SQL against it. It cuts wasted compute and time spent rediscovering joins. You write the cleaning steps once, in dbt, in three layers: staging, intermediate and marts.

Data modelling only makes sense once two things are true. You have a data warehouseData warehouseA separate database built for analysis rather than for running the product. It is where every source lands together, which is what makes it the number you trust when two tools disagree.Glossary, one central database where all your data lands, covered in the earlier chapters. And people are actually writing SQL against it, to do the analysis that product analyticsProduct analyticsThe analysis of what users do inside your product. The test is whether you can point at a row of data and name the user who did it.Glossary tools like Mixpanel cannot do.

That usually means you are already scaling. You have a lot of data, a lot of different tables, and a lot of different tools that each hold different information. When you do get there, data modelling is one of the most important things you will do.

The data sitting in your backend database, and the data coming in from third party tools, is messy.

Every time someone queries it to get something meaningful, two things go wrong.

• You waste compute. Every question runs against the raw data from scratch, so you pay for more computing power in your warehouse.
• You waste time. Someone has to figure out the joins, figure out which columns make sense, and deal with the same user ID being named differently in every table.

That is what data modelling fixes, and that is what dbt is for. dbt lets you write your cleaning steps once, in SQL, and rerun them automatically every time new data arrives.

You use dbt to clean your data in three layers, one on top of the other: staging, then intermediate, then marts. Each layer is built from the one below it, which is the structure dbt's own guide to structuring a project recommends. At the end, anyone on the team has clean data they can use.

Start with your messy tables. Naming is all over the place. The same user ID has a different name in every table, and half the columns nobody uses.

Staging: You take all of those tables and clean them up. Rename some things, remove the columns nobody needs. Every table is now a clean view, which is a saved query that looks and behaves like a table. That is your staging layer.

Intermediate: Here you join the meaningful tables together. Say there are three or four tables that all talk about the same feature. You want that in one table instead of joining four every time, so you do it here once.

If you had 100 tables in staging, the intermediate layer might come down to 40 tables that people actually use. Those 40 still carry all the context of the original 100. Nobody is joining anything by hand any more.

These 40 are the only tables anyone queries when they need data. They are clean, and everything is there, instead of the 100 you had before.

Marts: There is one more layer on top. Marts are summary tables. Each one has one row per thing you care about, like a user or a company, with the key numbers already added up so you get quick answers.

Take a user mart. You can see everything about a user in one place. When did they sign up? When did they purchase? How many times have they purchased? How much have they spent? What features have they used?

Every user gets one row.

If you are a B2B SaaS product you will have a company mart and probably a user mart. A health score can be a mart. If you have a feature and you want its data rolled up per user, in a format you use often for analytics, that becomes a mart too.

Marts are usually built on top of intermediate tables.

The layers need someone who knows which questions the business keeps asking. That means an analytics engineer, whose job is turning raw data into clean, ready-to-use tables, or whoever already owns your warehouse.

Anyone can query any of your tables easily, because the data is clean and ready.

You can set up tests in dbt. These are automatic checks that run every time the data refreshes. If data goes stale and stops updating, or anything else goes off, dbt flags it for you, instead of you realising months later that something has gone wrong.

And the marts get you answers quickly. That turns out to be one of the most important parts of using your data with AI, which we will get to in the chapter Analytics in the age of AI.