Understanding analytical tables

What does one row represent?

Suppose someone asks how many people attended an appointment at each practice. You find a table of appointments. Counting its rows sounds reasonable, but a person can attend more than once. A count of appointments and a count of people answer different questions, even when they come from the same table.

The grain of a table is what one row represents. In this fictional example, the grain is one appointment. All example identifiers on this page are invented.

Four rows describe four appointments and three people. P1 appearing twice is expected. Removing that repetition would throw away an appointment. Whether a repeated value is a duplicate depends on what the table promises.

A key is a column, or combination of columns, that identifies a row. Here, appointment_id identifies each appointment. person_id identifies its person, but does not identify the appointment: P1 has two. A column can be a useful join key without being unique in both tables.

Population and time complete the question

Grain alone is not enough. The request concerns people who attended, so the cancelled appointment does not qualify. We also need a reporting period. Without one, the same query might count all recorded history when the requester meant March.

select
    practice_code,
    count(*) as attended_appointments,
    count(distinct person_id) as people_who_attended
from example_appointments
where status = 'attended'
    and appointment_date >= '2026-03-01'
    and appointment_date < '2026-04-01'
group by practice_code

The where clause chooses the qualifying rows. group by collects them by practice and produces one output row for each practice that has a qualifying appointment. Within each group, count(*) counts rows and count(distinct person_id) counts different person identifiers.

The output now has practice grain. It no longer contains individual appointments, so it cannot answer which date P1 attended. Aggregation is a useful change of grain, but it removes detail. Keep the appointment model available for questions that need that detail.

A practice with no qualifying appointments has no row in this result. Showing every practice, including zero counts, requires a starting list of practices and a join to the counts. That is a population decision, not a formatting choice to leave until the dashboard.

A join can change the grain

Suppose a person table contains one row each for P1, P2 and P3. Joining appointments to that table can add person attributes without changing appointment grain, provided each appointment matches at most one person row. Reversing the starting point changes the question: joining all appointments onto the person table gives P1 two rows.

This is a one-to-many relationship. One person can have many appointments. The same thing happens with admissions below. Predict the number of result rows before revealing the join.

SQL has done what it was asked to do. It returns a row for each matching pair. The error appears when we continue describing or counting the output as one row per person. This multiplication of rows is often called fan-out.

If the required output is one row per person with an appointment count, group the appointments by person first and then join the counts. If it needs the latest appointment, choose one appointment per person using an agreed date and tie-breaking rule. If it needs all appointments, keep appointment grain and describe it honestly. These are different answers, not interchangeable fixes.

Adding distinct to the final query does not decide which answer you need. Different appointment dates still produce different rows. If you remove the dates too, the rows may collapse while the lost information goes unnoticed. First decide the intended grain; then choose the transformation.

Missing matches also change the answer

An inner join retains matching pairs. A left join also retains rows from the left-hand table with no match, filling the right-hand columns with nulls. Neither join guarantees one row per left-hand record: several matches still produce several rows.

Imagine a fourth person, P4, with no appointment record. An inner join from people to appointments excludes P4. A left join keeps P4 with null appointment fields. Which is right depends on whether the question concerns attendees or all people, including those with no recorded appointment.

A filter on a right-hand column can undo that preservation. After a left join, where appointment.status = 'attended' removes the unmatched rows because null does not satisfy the condition. If the intention is to keep every person, filter the appointment input before joining, or put that condition in the join itself. Check the resulting population, not just the SQL syntax.

Null does not explain why a match is missing. A person may have no appointment, an identifier may be unavailable, or a feed may not cover their provider. Turning every missing count into zero is justified only when the data's coverage and definition support that interpretation. A missing clinical measurement is not evidence of a normal result.

Facts record the subject; dimensions provide context

Dimensional modelling organises data around the things an analysis measures and the context used to describe them. An appointment fact records appointments, perhaps with duration or waiting time. Person, practice and date dimensions provide attributes such as ethnicity, practice name and financial year.

These names describe analytical roles. A fact does not need to be a large table, and a dimension does not need to be small. A numeric column is not enough to make something a fact: age can describe a person, while an appointment can be counted without a stored count column.

In this project, facts also record clinical states. A disease-register model can contain one row for each person meeting an agreed definition. Counting those rows measures register membership. Ethnicity and practice dimensions describe the people included.

A register flag might later be used to group another analysis, and analysts can count dimension rows. The model's name follows its primary purpose, not every possible use. The later naming chapter explains the project's fct_ and dim_ prefixes.

A mart is a supported dataset prepared for analysis around a subject such as appointments or people. It may include descriptive columns already joined from dimensions. That saves each analyst repeating the joins. Those extra columns should preserve the stated grain, and the shared definitions should still have clear owners upstream.

The same person can have different context over time

P1 might attend at GP_A in March and register with GP_B in April. Joining March activity to today's registration would group that activity under GP_B. That can be useful for reviewing the current population's past service use, but it does not answer which practice was responsible in March.

Decide which relationship the question needs. Current models describe a current state; historical models retain changes or events. A point-in-time result evaluates the relevant inputs at a specified date. A date label added to today's rows does not recreate an earlier population.

Historical tables often need a combined key. One row per person per month means a person can repeat and a month can repeat, but the same person-month combination should not. Joining on the person identifier alone may match several months. The time relationship belongs in the join as well.

Write the promise before the query

A useful model description answers what one row represents, which records qualify and when the result is true. For our summary: one row per practice with an attended appointment in March 2026, with appointment and distinct-person counts for that month. Someone can now challenge the population or time window before SQL obscures the disagreement.

The handbook calls these promises a model's contract. Here that means its documented analytical meaning. It is broader than dbt's optional enforced model-contract feature, which checks declared columns and types. Tests can protect particular promises, such as a unique key; they cannot decide whether we chose the right question.

The next chapter explains how dbt connects these tables through model dependencies. Designing models later returns to harder choices about history, uncertainty and where a definition should live.