Reading a Schema

Database Design Principles builds the picture of how data is stored. This page is the practical craft: you have been handed a database nobody has documented, and a question that matters. How do you get an answer you would stake a decision on?

The order below is the actual order. Most bad answers come from skipping straight to step four.

1. Write the question down first#

Before touching a query, write the question in one sentence, and add what you would do differently depending on the answer.

If no decision changes either way, stop; you have found a number that is decoration, not evidence (Reading Data covers why). If a decision does change, the sentence you just wrote is the thing you check your result against at the end.

Be specific about the boundaries, because the query will force you to be:

Over what period? “Last month” means calendar month or trailing 30 days, and they give different answers.
Which population? All users, or active ones, or paying ones? Does it include staff and test accounts?
Counting what exactly? Consultations started, completed, or paid for? These are three different numbers and people say “consultations” for all three.

2. Find the spine#

A real database has hundreds of tables. You need about six of them.

Ignore the long alphabetical list. Instead, name the two or three nouns in your question, find the tables that hold them, and follow the ids outward from there. Almost every database has a spine: the handful of tables the business actually runs on, usually the ones that are largest and most linked-to. Everything else is configuration, logs, lookups, and abandoned experiments.

Signals that a table is on the spine:

Its name is a core business noun, plural: users, orders, consultations.
It is one of the biggest by row count.
Other tables carry its id.

Signals it is not: a name ending in _log, _temp, _audit, or _backup; a name with a date or a version in it (diagnoses-new-2); near-duplicate names where one is clearly the survivor. Real databases are full of these. A table existing does not mean anything still writes to it. When two tables look like they do the same job, find out which one the application writes to today before you trust either.

3. Read one record, not the field list#

This is the fastest way to understand a table, and it is the step people skip.

Pull a single row or document and read every field. One real record teaches you more than any column list, because you see actual values: what the ids look like, which fields are empty, whether the status is "completed" or "COMPLETED" or 2.

Then ask these of the table:

Question	Why it matters
What is one row, in plain words?	”One row is one appointment” vs “one row is one status change” are different worlds
What makes it unique?	Tells you whether counting rows counts things, or double counts them
Which timestamp means what?	`created_at`, `scheduled_at`, `completed_at` answer different questions
What are the possible status values?	Get the real list from the data, not from what someone remembers
Which fields are often empty?	An empty field is either “not applicable” or “we started collecting this late”
Is there a deleted or cancelled flag?	If yes, almost every query needs to exclude them

For status values, do not guess: group by the field and count. A five-second query gives you the real vocabulary of the table, including the two legacy values nobody mentioned.

4. Build the query like a production line#

Every query in either language is a production line. Rows enter at one end, each station does one transformation, and what falls out the far end is your answer. If you have ever drawn a process flow, you already know how to reason about this: the stations are ordered, each hands its output to the next, and where you put a filter changes how much work everything downstream has to do.

MongoDB makes this literal, since an aggregation pipeline is written as a list of stations in order. SQL hides it behind one statement, but the line is the same underneath, which is why the two languages are far more alike than they look.

We use both MongoDB and SQL, and the good news is that this is one mental model with two vocabularies. The same seven stations, in the same order, in both languages.

The station	What it does	MongoDB	SQL
Pick the source	Choose what you are reading from	the collection	`FROM`
Filter rows	Keep only what meets a condition	`$match`	`WHERE`
Attach related	Bring in data from another table or collection	`$lookup`	`JOIN`
Collapse	Many rows into one per key	`$group`	`GROUP BY`
Do the maths	Count, total, average per bucket	`$sum`, `$avg`	`COUNT()`, `SUM()`, `AVG()`
Filter buckets	Drop whole groups after collapsing	`$match` after `$group`	`HAVING`
Choose columns	Pick and reshape what you show	`$project`	`SELECT`
Order and cut	Sort, then take the top slice	`$sort`, `$limit`	`ORDER BY`, `LIMIT`

Learn the stations once and you can work in either. What differs is only how they are spelled and arranged.

MongoDB: the stages are already in order#

A Mongo pipeline is a literal list, written in the order it runs, which makes it the easier of the two to reason about. Habits that matter more than syntax:

$match first, always. Correctness and speed in one move.
$unwind multiplies your rows. One order with five items becomes five documents. Any count after an unwind counts items, not orders.
$lookup is expensive. Filter down before you join, not after.
Add one stage at a time, looking at the output each time. Writing six stages then debugging is how an afternoon disappears.

For looking at records rather than aggregating, find is simpler: a filter and some fields, no pipeline. Use find for “show me examples”, the pipeline for “how many” and “what is the trend”.

For the actual syntax, with a pipeline traced document by document at every stage, see Querying MongoDB.

SQL: written in one order, run in another#

This is the single thing that confuses people learning SQL, and knowing it early saves weeks. SELECT is written first and runs almost last. The database picks the source, filters, and groups long before it decides which columns to show you.

Two consequences that will otherwise cost you an afternoon each:

WHERE filters rows, HAVING filters groups. “Only July” is WHERE. “Only doctors with more than 50 consultations” is HAVING, because that count does not exist until after GROUP BY has run.
You cannot use a name you invented in SELECT inside WHERE. WHERE runs first, so the alias does not exist yet. Repeat the expression, or wrap the query in another one.

5. Joins are where both languages bite#

Combining tables is where most wrong numbers are born, in either language, and it fails the same way in both.

Fan-out is the big one. Join a table with one row per patient to a table with one row per appointment, and the patient’s row is duplicated once per appointment. Count rows now and you are counting appointments while believing you are counting patients. In Mongo, $unwind does exactly the same thing.

The tell is a total that is suspiciously larger than you expected, and the fix is to know what one row of your result represents after every join. Say it out loud: “one row is now one appointment, not one patient.” If you need patients, count distinct patient ids (COUNT(DISTINCT patient_id)) rather than rows.

Then, which rows survive the join:

Join	Keeps	Use it when
`INNER JOIN`	Only rows with a match on both sides	You genuinely require both, e.g. paid orders with a payment record
`LEFT JOIN`	Every row on the left, with blanks where there is no match	You want all of the left side, matched or not

The trap: an INNER JOIN silently deletes rows. Ask “how many patients had an appointment” with an inner join and patients with none vanish, which is correct. Ask “how many patients do we have” with the same join and you have quietly excluded everyone who never booked. Same query shape, one is right and one is wrong, and neither errors.

A LEFT JOIN then filtering on the right-hand table in WHERE turns it back into an inner join, because the blank rows fail the condition. If you need to keep them, the condition belongs in the join itself, not in WHERE.

6. Verify before you believe#

This is what separates someone who ran a query from an analyst, and it is the step that earns you the right to put a number in a document.

Sanity-check the magnitude. Before you look, say roughly what you expect. If you guessed thousands and got 11, something is wrong, and it is usually a filter excluding a field that did not exist yet.
Check the total without filters. Run the count with no filter, then add filters one at a time and watch the number drop. A filter that takes it from 40,000 to 11 is a filter you have misunderstood.
Spot-check individual records. Pull three rows the query says qualify. Read them. Do they really qualify?
Cross-check against a second source. A dashboard, a finance report, someone who knows the ground truth. If they disagree, do not average them; find out why. Trust the Report, Doubt the Data is exactly this situation.
Ask who is missing. Every filter excludes someone. Say out loud who your query left out and confirm you meant to.

Then write the number down with its definition attached: not “4,200 consultations”, but “4,200 consultations completed between 1 and 31 July, excluding cancelled and staff accounts”. A number without its definition will be misread later, usually by you.

Learning path#

We use both MongoDB and SQL, so you need both eventually. Learn them in this order anyway: tool first, then Mongo, then SQL. You want to be answering real questions in week one, not finishing a course before you are useful.

1. The tool, so you can get answers immediately

Using Metabase, our own guide, start here
Metabase Learn, short official articles and videos, the “Getting started” and “Questions” paths

2. The foundations, alongside the tool

This page and Database Design Principles
Reading Data and How Data Misleads, how to not be fooled by a number you correctly retrieved

3. MongoDB

MongoDB University, free official courses with videos; the introductory learning path first, then anything on the aggregation framework
MongoDB Manual: Aggregation Operations, the reference you will keep open
Practical MongoDB Aggregations, a free online book, the best thing written on building pipelines well

4. SQL

SQLBolt, interactive, an afternoon, no setup
Mode SQL Tutorial, written for analysts rather than engineers, the best free course of its kind
Select Star SQL, free and interactive, teaches reasoning about data rather than syntax

Do not read all of these. Work a real question, get stuck, then read the section that unsticks you. Two hours on a question you actually care about beats a finished course.

The posture#

You will not understand a database before you query it. You understand it by querying it: reading a record, guessing, being wrong about a status value, checking, and adjusting. Getting a surprising number is not failure, it is the loop working. Being surprised and shipping the number anyway is the only real mistake.