In Joins, CTEs, and Aggregations you joined nyc_taxi.raw_trips to nyc_taxi.raw_zones and squashed 57,000 rows into a handful of insights. That worked, but every query started from scratch: the same cleaning, the same join, the same risk of a subtle mistake. Real analytics teams do not rewrite that logic in every query. They agree on a shared data model first, then build every report on top of it.
A data model is a deliberate arrangement of tables that makes the data easy to query correctly and hard to query wrong. This chapter teaches the vocabulary and the rules behind that arrangement. You will not write much SQL here: Building Views in Azure PostgreSQL turns these ideas into real views, and the assignment asks you to build a star schema by hand. This chapter is the blueprint.
By the end of this chapter, you should be able to state the grain of a table in one sentence, tell a fact apart from a dimension, sketch a star schema for the taxi data, and explain how the wrong grain double-counts a metric in a join.
Raw data is rarely safe to hand to an analyst. nyc_taxi.raw_trips has negative fares, cryptic column names, and location IDs instead of zone names. Instead of cleaning it in every query, professional teams move data through three layers, each with one job.
flowchart LR
raw[("**raw**<br/>nyc_taxi.raw_trips, nyc_taxi.raw_zones<br/>as loaded, untouched")]:::raw
stg["**staging**<br/>stg_trips, stg_zones<br/>cleaned + renamed"]:::stg
mart["**marts**<br/>fct_trips, dim_zones<br/>business-ready"]:::mart
raw --> stg --> mart
classDef raw fill:#f5f5f5,stroke:#666,stroke-width:2px,color:#000
classDef stg fill:#dae8fc,stroke:#6c8ebf,stroke-width:2px,color:#000
classDef mart fill:#d5e8d4,stroke:#82b366,stroke-width:2px,color:#000
nyc_taxi.raw_trips and nyc_taxi.raw_zones from Week 6. You never edit raw tables: they are the audit trail. If something looks wrong downstream, you can always trace back to what actually arrived.fct_trips and dim_zones live here.This is the same raw to staging to mart flow you will meet again when you reach dbt. Learning it now in plain SQL means the dbt version will feel familiar, not new.
<aside> 💡 Each layer reads only from the layer above it. Marts read staging, staging reads raw. A mart never reaches straight back to a raw table. That rule keeps cleaning logic in exactly one place.
</aside>
Before you model anything, answer one question: what does a single row represent? That answer is the grain of the table. It is the single most important modeling decision you will make, because every other choice (which keys, which measures, which joins are safe) depends on it.
The grain of nyc_taxi.raw_trips is one taxi trip. One row, one ride from pickup to dropoff. The grain of nyc_taxi.raw_zones is one location: one row per location_id.
State the grain as a plain sentence before you write a line of SQL. "One row per taxi trip." "One row per zone." "One row per zone per day." If you cannot say it in one sentence, the table is trying to be two tables at once, and your metrics will not add up.
State the grain of nyc_taxi.raw_trips in one sentence, then prove it. If the grain really is "one row per trip," then a column combination that uniquely identifies a trip should have as many distinct values as there are rows. Run both counts and compare.
<aside> ⌨️ Hands on: Run the query below and compare the two numbers.
</aside>
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT (vendor_id, pickup_datetime, dropoff_datetime)) AS distinct_trips
FROM nyc_taxi.raw_trips;
If the two numbers match, every row is a distinct trip and your grain statement holds. If distinct_trips is smaller, you have duplicate rows at the stated grain, which is exactly what Task 1 of the assignment asks you to check for.
total_rows | distinct_trips
------------+----------------
56551 | 56362
In this example the counts differ slightly, so a few trips share the same vendor and timestamps. That is a real data-quality finding, not a mistake in your query. Note the exact numbers may shift if the shared data is reloaded; what matters is the comparison.
A key is a column (or set of columns) that identifies a row. Three kinds matter for modeling.
nyc_taxi.raw_zones, location_id is the natural key: it comes from the NYC taxi commission, and it is what nyc_taxi.raw_trips references. One location_id, one zone.nyc_taxi.raw_zones, the natural key location_id is also the primary key. A primary key must be unique and never null.In a dimension table, the primary key is what facts point to. nyc_taxi.raw_zones.location_id is the primary key of the zones dimension, and nyc_taxi.raw_trips.pickup_location_id is a foreign key: a column in one table that refers to the primary key of another.
<aside> 🤓 Curious Geek: surrogate keys and the slowly changing dimension
Why generate a fake key when a natural one exists? Because natural keys change. If a zone is renamed or a customer moves city, a surrogate key lets the warehouse keep the old version and the new version as two separate rows while the fact still points at the right one. This trick is called a slowly changing dimension, and it is one of the most-cited ideas in all of data modeling.
</aside>
Dimensional modeling sorts every table into one of two buckets.
nyc_taxi.raw_trips is a fact: each row is a trip, and fare_amount, trip_distance, and tip_amount are measures you can SUM and AVG. Facts are tall and narrow: many rows, mostly numbers and foreign keys.nyc_taxi.raw_zones is a dimension: borough and zone describe where a trip happened. Dimensions are short and wide: few rows, mostly text attributes you filter and group by.The test: if you would SUM it, it is a measure in a fact. If you would GROUP BY it or filter on it, it is an attribute in a dimension. fare_amount is a measure. borough is an attribute. That single distinction drives the whole model.
Put one fact table in the center and surround it with the dimensions it references. Draw the foreign keys as lines and the result looks like a star: hence star schema.
flowchart TB
dim_pickup["**dim_zones**<br/>(pickup)<br/>location_id PK<br/>borough, zone"]:::dim
dim_dropoff["**dim_zones**<br/>(dropoff)<br/>location_id PK<br/>borough, zone"]:::dim
dim_date["**dim_date**<br/>date_key PK<br/>year, month, weekday"]:::dim
fct["**fct_trips**<br/>one row per trip<br/>pickup_location_id FK<br/>dropoff_location_id FK<br/>fare_amount, tip_amount,<br/>trip_distance"]:::fact
dim_pickup --> fct
dim_dropoff --> fct
dim_date --> fct
classDef fact fill:#d5e8d4,stroke:#82b366,stroke-width:3px,color:#000
classDef dim fill:#dae8fc,stroke:#6c8ebf,stroke-width:2px,color:#000
fct_trips sits in the middle. The same dim_zones table answers both "where was the pickup?" and "where was the dropoff?": you join to it twice, once on each foreign key. A date dimension is the standard first dimension in almost every warehouse, because nearly every report slices by time. You will not build dim_date this week, but expect it when you reach dbt: it lets you group by month, weekday, or quarter without re-deriving those from a timestamp in every query.
The star schema is popular because it is fast to query and easy for a human to read. An analyst can look at the diagram and know exactly which table holds the borough name and which holds the fare.
The names "fact," "dimension," "grain," and "star schema" all come from Ralph Kimball's dimensional-modeling school, which won out over Bill Inmon's rival approach for the analytics layer. The History of SQL & Warehouses page covers that Kimball-vs-Inmon debate.
<aside>
💡 In the wild: The fact-and-dimension split you just drew is how production analytics repos are actually laid out. dbt Labs' public reference project jaffle_shop models a small business as fct_orders plus dim_customers, exactly the star-schema shape on this page. Open it to see fact and dimension tables modeled in a real codebase.
</aside>
Here is why grain is not just vocabulary. When you join two tables at mismatched grains, the finer-grained side gets duplicated, and any SUM over it is silently wrong.
fct_trips is at trip grain: one row per trip. dim_zones is at zone grain: one row per location. Joining a trip to its single pickup zone is safe, because each trip matches exactly one zone. The trip row is not multiplied.
Now imagine a dimension that is not unique on the join key. Suppose a zones table accidentally had two rows for location_id = 43 (a duplicate). Every trip from location 43 would now match both rows and appear twice in the result. A SUM(fare_amount) would count those fares twice. The metric fans out: the total balloons for no real reason.
-- one trip, fare 18.00, joined to a zone table with a duplicate row for id 43
trip_id | fare_amount | zone
---------+-------------+------------
1 | 18.00 | Central Park <- counted once
1 | 18.00 | Central Park <- counted AGAIN (duplicate dim row)
SUM(fare_amount) = 36.00 -- should be 18.00
This is the most common real bug in analytics work, and it always traces back to grain. The defense is simple: make sure the column you join on is unique in the dimension. That is why location_id must be the primary key of dim_zones, and why the assignment makes you verify it. If your dimension's join key is not unique, your fact will fan out.
<aside> ⚠️ When a total looks suspiciously high after adding a join, suspect fan-out first. Count rows before and after the join: if the row count jumped, a dimension on the join key is not unique.
</aside>
Watching a total balloon is more convincing than reading about it.
<aside>
🚀 Try it in the widget: See fan-out happen in the SQL playground (real Postgres, no setup). The Fan-out: a join that double-counts example joins on both pickup and dropoff zones so each trip is counted twice. Compare its SUM to the same query with a single, correct join.
</aside>
Models in a warehouse follow prefix conventions so anyone can tell a model's layer and role from its name alone.
| Prefix | Meaning | Example |
|---|---|---|
stg_ |
staging model (cleaned, one per source) | stg_trips |
fct_ |
fact table (measurable events) | fct_trips |
dim_ |
dimension table (descriptive context) | dim_zones |
vw_ |
a plain SQL view (used in this week's assignment) | vw_fact_trips |
The assignment uses the vw_ prefix because you build everything as PostgreSQL views; the dbt project uses stg_, fct_, and dim_. They describe the same idea: a named, reusable query.
Naming is only half of it. Every model needs a short data dictionary: its grain in one sentence, its primary and foreign keys, and which columns are measures. Task 3 of the assignment asks for exactly this, because a model nobody can read is a model nobody will trust.
<aside> 💡 Using AI to help: Paste your one-sentence grain statement and the model's column list into an LLM and ask it to flag any column that does not fit the stated grain. Then check its answer against your real columns: the model can be confidently wrong. (⚠️ The NYC taxi data is public, so it is safe to paste. Never paste real customer data or PII.)
</aside>
nyc_taxi.raw_trips has fare_amount and trip_distance; nyc_taxi.raw_zones has borough and zone. Which table is the fact and which is the dimension, and how can you tell?fct_trips to a zones table and SUM(fare_amount) suddenly doubles. What most likely happened, and how do you confirm it?Want to check your understanding before moving on? Try the interactive quiz for this chapter.
<aside> 🚀 Try it in the widget: Interactive Quiz: Data Modeling Concepts
</aside>
https://lasse.be/simple-hyf-teach-widget/mcq.html?bank=week_9_ch5_data_modeling_concepts_quiz&embed=1
If grain, facts, and dimensions still feel abstract, this video builds a star schema step by step.
<aside> 🎬 Struggling with this concept? Watch this beginner-friendly video:
</aside>
https://www.youtube.com/watch?v=mPnnygpy2lY