Data Modeling for Data Engineers
Learn to design tables people can trust: OLTP versus OLAP, normalization, grain, star schemas, slowly changing dimensions, and double counting.
What You'll Learn
Understanding Why Bad Data Models Break Production
You have just joined acme-shop's data team. In the SQL module you learned to write queries. Now you will design the tables those queries run on.
Understanding OLTP versus OLAP
Before designing anything, ask which kind of work the table will serve.
Designing Relational Tables the Right Way
A relational table holds facts about one kind of thing, such as customers, or one kind of event, such as orders.
Applying Normalization to Keep OLTP Data Honest
Normalization organizes tables so each fact is stored once. Denormalization deliberately repeats data to avoid joins.
Going from a Business Process to a Fact Table
Use the same sequence every time you model a business process. The Eight Steps Identify the business process. Example: selling products.
Designing Star Schemas for Analytics
A fact table stores the measurements of a business process. A dimension table stores the context around them. A fact is what happened.
Skills You'll Master
Curriculum Index13 topics
Understanding Why Bad Data Models Break Production
You have just joined acme-shop's data team. In the SQL module you learned to write queries.
Understanding OLTP versus OLAP
Before designing anything, ask which kind of work the table will serve.
Designing Relational Tables the Right Way
A relational table holds facts about one kind of thing, such as customers, or one kind of event, such as orders.
Applying Normalization to Keep OLTP Data Honest
Normalization organizes tables so each fact is stored once. Denormalization deliberately repeats data to avoid joins.
Going from a Business Process to a Fact Table
Use the same sequence every time you model a business process. The Eight Steps Identify the business process.
Designing Star Schemas for Analytics
A fact table stores the measurements of a business process. A dimension table stores the context around them.
Avoiding Double Counting
This is the most valuable practical lesson in the module. It is how real revenue numbers get silently doubled.
Handling Slowly Changing Dimensions
Dimension attributes change. A customer moves from Pune to Hyderabad.
Recognising Snowflake Schemas, Data Vault, and NoSQL
This topic is awareness only. You will meet these, but you will not build them often early on.
Preparing Models for the Warehouse
A few physical ideas affect how you design your schema, without needing warehouse internals.
Building the acme-shop Star Schema
You will build acme-shop's star schema on the shared de-lab PostgreSQL, populate it from the raw tables, change a...
Quick Reference and Common Mistakes
Quick Reference Common Mistakes Building a fact table without writing the grain.
What You Built and What Comes Next
You designed acme-shop's star schema: a date dimension, a product dimension, a customer dimension that keeps history...
Career Impact
Roles that use the skills in this module.
Data Engineer
Platform Engineer
Next Modules
Related Guides
Practice on the Coding Sheet
Not a software engineer sheet. Every problem comes from real DevOps, SRE, Platform and Cloud interviews, from your first script to a system you build yourself.
Open the Coding SheetFrequently Asked Questions
The grain is what one row means, written as one sentence such as 'one row per order line item'. Every column in the table must make sense for that sentence. Getting the grain wrong is the most common cause of wrong totals.
A big table repeats the same facts on many rows, so updates miss copies and totals get counted twice. Splitting data into fact and dimension tables keeps each fact in one place and makes queries predictable.
No. Use Type 2 when the business may ask what a record looked like in the past, such as a customer's city at the time of an order. Use Type 1 only to correct mistakes, because it overwrites history.
Yes. Modern warehouses are fast, but a clear model still decides whether the numbers are right. A star schema gives analysts a shape they can query without guessing how tables join.