Data modeling
Data modeling is deciding how data is organized into tables: what each one holds, what one row means and how they connect, so questions about it get the right answer.
Select any words here to ask your Maestro about them.
Ana has 2 orders ($100 and $50) and 3 support tickets. You LEFT JOIN customers to orders and to tickets in one query, then SUM the order amounts. What total do you get for Ana?
What it's for: a good model makes the right query the easy one. It decides each table's grain (what one row is), its keys, and how tables relate: one to one, one to many or many to many.
Three levels: a conceptual model names the things the business cares about (customers, orders, products); a logical model lists their fields and relationships; a physical model is the actual tables in a database. For analysis, the usual shape is a star schema.
Who does it: data engineers and analytics engineers, often with tools like dbt, for a data warehouse.
How it's used
From idea to tables
A store, at three levels
- Conceptual: customers place orders, and each order has products
- Logical: customers(customer_id, name, city) · orders(order_id, customer_id, date) · order_lines(order_id, product_id, qty, price)
- marked: Physical: CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers, order_date DATE)The same idea, now in the database's own types and keys.
When to look closer
Everything in one sheet
An orders sheet
- orders: order_id, date, amount, customer_name, customer_city
- marked: Maya has 12 orders; she moves, and you change the city to Denver on her latest one
Your orders sheet repeats each customer's city on every order. Maya moves, and you update her latest order. What does a report of customers by city show?
Ask your Maestro about Data modeling
It answers from the free course page closest to this word, the way it answers while you study.
You: What does "Data modeling" mean, in plain words?
Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which tables do you put together by hand to build your reports?
Asking about: Answers from the page "Which chart should I use? Start with the question"
Only this browser keeps what you ask here. How it works
Learn more
Related terms
- Star schema
A star schema arranges data for analysis: a fact table of events, like sales, in the center, joined by keys to dimension tables (customer, product, date) that describe them.
- Grain (of a table)
What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.
- SQL join
A SQL join combines rows from two tables where a column matches, like orders with their customers. The type of join decides what happens to rows without a match.
- dbt (data build tool)
dbt (data build tool) turns SQL SELECT queries into tables or views in your data warehouse, runs them in order and tests the results: dbt run builds the models, dbt test checks them.