Stop 11 · Business Acumen & Domain KnowledgeTopics · 7/8

Understanding Your Company's Data Model

In short

Every company stores its world in a few dozen tables linked by IDs. Learn that map in your first weeks and you'll write correct joins while others are still asking where things live.

Knowing where key entities (users, orders, sessions) live, how they connect, and which tables are trustworthy versus stale or deprecated — the unglamorous knowledge that makes you fast at a real job.

The jaadu happily pins coloured paper squares to a cork board and links them with red string.

Think of it like this

The joint family tree on the wall

In a big joint family, you learn who is whose son, whose cousin and whose in-law. Each person links to others through clear relationships. Company tables work the same way: an order links to a customer, a product and a payment through ID columns.

Watch it happen

How an e-commerce order connects

orders

customers

order_items

products

payments

cities

1/5Start from orders. One row per order, with order_id, customer_id, date and status.

The big ideas

What you need to know

Keys connect tables

A primary key uniquely identifies a row. A foreign key in another table points back to it. Joins follow these.

Know each table's grain

Grain is what one row means: one order, one item, one day per user. Mixing grains causes double counting.

One-to-many is the trap

Joining orders to items multiplies order rows. Aggregate at the right level before joining further.

Find the data dictionary

Most teams have docs or a schema diagram. If they don't, building one in your first month is a gift to everyone.

See it in code

Walk through the code

sql

1-- Revenue per city, without double counting2WITH order_revenue AS (3  SELECT order_id, SUM(quantity * unit_price) AS revenue4  FROM order_items5  GROUP BY order_id6)7SELECT ci.city_name, SUM(r.revenue) AS revenue8FROM orders o9JOIN order_revenue r ON r.order_id = o.order_id10JOIN customers cu    ON cu.customer_id = o.customer_id11JOIN cities ci       ON ci.city_id = cu.city_id12WHERE o.status = 'delivered'13GROUP BY ci.city_name;

1/4The CTE turns many item rows into one revenue row per order.

Roll order items up to one row per order first, then join outward. The grain stays clean all the way through.

Save yourself the pain

Common mistakes

Oops

Joining every table you need in one go and summing at the end.

Do this instead

Check the grain of each table and aggregate before joining one-to-many tables.

Oops

Guessing what a column like status = 3 means.

Do this instead

Look it up in the docs or ask the table owner, then write it down for the next person.

Your mission

Draw a schema

~30 min

Quick check

You join orders (1,000 rows) to order_items and get 2,700 rows. Then you SUM the order shipping fee. What's wrong?

Go deeper

1If you have access to a real or sample company schema, spend an hour just exploring it.
2Identify the 3-5 'core' tables you'd expect to use in most analyses.
3Learn to ask a data engineer about table freshness and reliability before trusting a new source.