So far this week you wrote one-off queries: a JOIN here, an aggregation there. Each query repeated the same cleaning and joining logic. That works for a single answer, but the analytics team needs a stable, reusable layer they can query without reinventing the same WHERE fare_amount > 0 filter every time. SQL views give you exactly that: a saved query that looks and behaves like a table, but stores no data of its own.
This chapter is the hands-on payoff of the week. You will turn the messy nyc_taxi.raw_trips and nyc_taxi.raw_zones tables into a clean star schema (one fact view, one dimension view) that you query together to answer real business questions. This is the same structure your assignment asks you to build, so by the end you arrive at the assignment ready to go.
By the end of this chapter, you should be able to:
CREATE VIEW and explain how a view differs from a table.COMMENT ON VIEW and query the fact and dimension views together.<aside>
⚠️ Read the raw tables from the shared, read-only nyc_taxi schema (nyc_taxi.raw_trips, nyc_taxi.raw_zones). Create the views in this chapter in your own schema (your personal login already defaults to it, so an unqualified CREATE VIEW lands there), never in nyc_taxi or public, so they never collide with a classmate's work.
</aside>
A view is a named, saved SELECT query. When you query a view, PostgreSQL runs the underlying query for you and hands back the rows as if they came from a table. The key difference from a table: a view stores no data. It is a stored definition, not stored rows.
| Table | View | |
|---|---|---|
| Stores rows on disk | Yes | No |
| Always reflects latest source data | Only after a reload | Yes, automatically |
| Costs storage | Yes | Almost none |
| Can hide complexity from users | No | Yes |
Use a table when you want to physically store results (raw ingested data, or a snapshot). Use a view when you want a clean, always-fresh window onto data that already lives somewhere else. Because nyc_taxi.raw_trips is the source of truth and you only want to reshape it for analysts, a view is the right tool.
<aside> 💡 In the wild: The "saved SELECT as a clean layer" pattern is everywhere in production. Datasette publishes SQLite databases as browsable websites and leans on saved views to expose tidy, named queries, and every dbt project (like the public jaffle_shop) is essentially a versioned stack of view-like models on top of raw tables. Open either to see the reusable-query layer at scale.
</aside>
The syntax is simple:
CREATE VIEW vw_example AS
SELECT vendor_id, fare_amount
FROM nyc_taxi.raw_trips
WHERE fare_amount > 0;
When you edit a view's definition, use CREATE OR REPLACE VIEW. It updates the saved query in place without you having to DROP it first, as long as the new query returns the same columns in the same order with compatible types.
<aside> 🤓 Curious Geek: A view adds zero storage
A view of a billion-row table takes up only the few hundred bytes needed to store its SELECT text. The rows are never copied. You can stack ten views on one table and your database size barely moves, because every view just points back at the original rows.
</aside>
Before you model a star schema, it helps to have one clean, well-named layer sitting on top of the raw data. This is a staging view: it keeps the grain of nyc_taxi.raw_trips (one row per trip) but casts types and filters obvious garbage. Analytics engineers build this layer so that every downstream query starts from clean, predictable columns.
Create vw_stg_trips in your schema:
CREATE OR REPLACE VIEW vw_stg_trips AS
SELECT
vendor_id,
pickup_datetime::timestamp AS pickup_datetime,
dropoff_datetime::timestamp AS dropoff_datetime,
passenger_count,
trip_distance,
pickup_location_id,
dropoff_location_id,
fare_amount,
tip_amount,
payment_type
FROM nyc_taxi.raw_trips
WHERE fare_amount >= 0;
What this view does:
TIMESTAMP so date math works.<aside>
💡 A staging view is your single place to fix a column once. If nyc_taxi.raw_trips later gains a dirty column, you patch vw_stg_trips and every view built on top of it inherits the fix.
</aside>
A fact view holds the quantitative events you measure. Its grain is one row per taxi trip, and its measures are the numbers you aggregate later: fare_amount, trip_distance, and tip_amount. It keeps the location IDs so it can join to the dimension.
This is the exact view your assignment (Task 2) asks for, so match it precisely: filter out fare_amount < 0 and cast pickup_datetime to TIMESTAMP.
CREATE OR REPLACE VIEW vw_fact_trips AS
SELECT
vendor_id,
pickup_datetime::timestamp AS pickup_datetime,
dropoff_datetime,
passenger_count,
pickup_location_id,
dropoff_location_id,
trip_distance,
fare_amount,
tip_amount,
payment_type
FROM nyc_taxi.raw_trips
WHERE fare_amount >= 0;
<aside> ❗ The grain is the most important thing to be able to state about a fact view. If you cannot say "one row equals one _" in a single sentence, you do not yet understand the view. Here: one row equals one taxi trip.
</aside>
A dimension view holds the descriptive labels you join onto facts. vw_dim_zones turns the bare location_id numbers (like 43) into human-readable zone and borough names (like Central Park, Manhattan). Its location_id is the primary key: one row per location, no duplicates.
CREATE OR REPLACE VIEW vw_dim_zones AS
SELECT
location_id,
zone,
borough
FROM nyc_taxi.raw_zones;
<aside> 🤓 Curious Geek: Plain views vs materialized views
A plain view runs its query every time you read it. A materialized view runs the query once and stores the result on disk like a table, which is fast to read but goes stale until you REFRESH it. Postgres supports both. At 57K rows a plain view is instant, so you do not need materialization yet. You will meet the trade-off again when you reach dbt.
</aside>
A view that compiles is not a view that is correct. Run three quick checks after creating each one.
1. Row counts. The dimension should match nyc_taxi.raw_zones exactly. The fact view should be slightly smaller than nyc_taxi.raw_trips, because you filtered out negative fares.
SELECT COUNT(*) AS zone_rows FROM vw_dim_zones;
SELECT COUNT(*) AS fact_rows FROM vw_fact_trips;
zone_rows
-----------
265
fact_rows
-----------
56xxx -- slightly under 57,000 once negative fares are removed
2. Null checks. Make sure the join keys you rely on are populated.
SELECT COUNT(*) AS null_pickup_ids
FROM vw_fact_trips
WHERE pickup_location_id IS NULL;
You should see roughly 0 here. If the count is non-zero, those trips will silently drop out of an INNER JOIN, which you need to know about before you report numbers.
3. Join validation. This is the test the assignment requires: confirm the fact view joins cleanly to the dimension on location_id.
SELECT COUNT(*) AS matched_rows
FROM vw_fact_trips f
JOIN vw_dim_zones d
ON f.pickup_location_id = d.location_id;
matched_rows
--------------
56xxx -- close to the full fact count; a small gap means some pickup IDs have no zone
If matched_rows is far below your fact-view count, some pickup_location_id values are missing from nyc_taxi.raw_zones. That is a referential-integrity problem worth flagging, not a reason to give up.
<aside>
⌨️ Hands on: In your own schema, run the three CREATE OR REPLACE VIEW statements (vw_stg_trips, vw_fact_trips, vw_dim_zones) in order, then run the join-validation query above. Confirm matched_rows is close to your fact_rows count. If it is not, run a quick SELECT DISTINCT pickup_location_id FROM vw_fact_trips f WHERE NOT EXISTS (SELECT 1 FROM vw_dim_zones d WHERE d.location_id = f.pickup_location_id) to see which IDs have no zone.
</aside>
<aside> 🚀 Try it in the widget: Build the views in the SQL playground (real Postgres, no setup). The Build views, then query them example creates a fact and dimension view, then joins them, so you can see the layered pattern run end to end before doing it on the shared database.
</aside>
One thing to keep in mind once your views work: a view is not free to read.
<aside> ⚠️ A plain view runs its full query every single time you read it. There is no cached result. At 57K rows you will not notice, but on a billion-row table a heavy view re-computes joins and filters on every query. The fix (materialized views and dbt models that persist their output) is the performance story you will meet with dbt, where you can actually benchmark it. For now, just know that "view" means "re-run the query", not "stored answer".
</aside>
A view's name and columns rarely tell the whole story. Attach a one-line description with COMMENT ON VIEW so the next person (including future you) knows the grain and intent.
COMMENT ON VIEW vw_fact_trips IS 'One row per taxi trip. Measures: fare_amount, trip_distance, tip_amount. Negative fares excluded.';
COMMENT ON VIEW vw_dim_zones IS 'One row per location_id (primary key). Maps location_id to zone and borough names.';
For a fuller record, keep a small data dictionary alongside your code: a short table listing each view, its grain, its keys, and its measures. Your assignment asks for exactly this in data_dictionary.md.
| View | Grain | Keys | Measures |
|---|---|---|---|
vw_fact_trips |
One taxi trip | FK: pickup_location_id, dropoff_location_id |
fare_amount, trip_distance, tip_amount |
vw_dim_zones |
One location | PK: location_id |
none (descriptive only) |
Now the payoff. With the two views in place, answering a business question is a clean join. The borough and zone names live only in vw_dim_zones, so you always join on location_id to get human-readable labels.
Question 1: total revenue by borough.
SELECT
d.borough,
SUM(f.fare_amount) AS total_revenue,
COUNT(*) AS trip_count
FROM vw_fact_trips f
JOIN vw_dim_zones d
ON f.pickup_location_id = d.location_id
GROUP BY d.borough
ORDER BY total_revenue DESC;
You should see roughly:
borough | total_revenue | trip_count
--------------+---------------+------------
Manhattan | 612000.00 | 41000
Queens | 98000.00 | 7200
Brooklyn | 54000.00 | 5100
Bronx | 9000.00 | 900
...
Manhattan dominates both revenue and trip count, which matches what you would expect for NYC pickups.
Question 2: top 5 pickup zones by trip count.
SELECT
d.zone,
d.borough,
COUNT(*) AS trip_count
FROM vw_fact_trips f
JOIN vw_dim_zones d
ON f.pickup_location_id = d.location_id
GROUP BY d.zone, d.borough
ORDER BY trip_count DESC
LIMIT 5;
zone | borough | trip_count
-----------------------+-----------+------------
Upper East Side South | Manhattan | 2400
Midtown Center | Manhattan | 2100
Penn Station | Manhattan | 1950
Upper East Side North | Manhattan | 1850
Times Sq/Theatre Dist | Manhattan | 1700
Same query shape, different GROUP BY and ORDER BY. Once the views exist, every analyst answers questions like these without touching the raw data or repeating your cleaning logic.
<aside> 💡 Using AI to help: Paste a sample query and its result (⚠️ the NYC Taxi data is public, but never include any PII or sensitive company data) into an LLM and ask it to suggest a clearer grain statement for your fact view, then verify the suggestion against your actual view columns before trusting it.
</aside>
vw_fact_trips when 200 new rows are inserted into nyc_taxi.raw_trips?CREATE OR REPLACE VIEW instead of CREATE VIEW?vw_fact_trips keep pickup_location_id instead of storing the zone name directly?vw_fact_trips. What does that tell you?vw_dim_zones in one sentence, and name its primary key.