Quick reference for the SQL and data-warehousing vocabulary used this week. Each term has its own anchor so chapters can link straight to it. Definitions are tied to the NYC taxi dataset (nyc_taxi.raw_trips, ~57K rows, and nyc_taxi.raw_zones, 265 rows) wherever a concrete example helps. Terms are grouped logically; skim the group that matches the chapter you are reading.
Structured Query Language, the standard language for querying and transforming data in a relational database. Invented in the early 1970s and still the universal language shared by data engineers, analysts, and BI tools. This week you write SQL against Azure PostgreSQL.
A database-specific variant of SQL. The core (SELECT, JOIN, GROUP BY) is shared, but functions and syntax details differ between PostgreSQL, T-SQL (Azure SQL / SQL Server), BigQuery SQL, and Snowflake SQL. This course uses PostgreSQL, one of the most standards-aligned dialects, so most queries transfer directly to other engines.
The absence of a value in a column. NULL is not zero and not an empty string: it means "unknown" or "missing." NULLs behave specially in SQL: COUNT(column) skips them, a NULL join key drops rows from an inner join, and GROUP BY collects all NULLs into one separate group. About 6% of nyc_taxi.raw_trips rows have a NULL payment_type.
Squashing many rows into a few summary numbers with functions like COUNT, SUM, AVG, MIN, and MAX. Turning 57,000 trips into "average fare per borough" is an aggregation. The opposite of a one-row-at-a-time operational query.
The clause that defines how detailed an aggregation is. Every column in the SELECT must either sit inside an aggregate function or appear in the GROUP BY. GROUP BY z.borough produces one output row per borough; adding dropoff_zone produces one row per pickup-dropoff combination.
A filter applied to grouped results, after aggregation. Because WHERE runs before grouping, it cannot reference COUNT(*) or AVG(...); HAVING can. HAVING COUNT(*) > 1 keeps only groups that appear more than once, the basis of the duplicate-detection check.
An operation that combines rows from two tables based on a matching identifier. In the taxi data you join nyc_taxi.raw_trips to nyc_taxi.raw_zones on location_id to turn a bare number like 43 into a human-readable name like "Central Park."
Keeps only the rows that have a match in both tables. An inner join of trips and zones returns only trips whose pickup or dropoff location_id exists in nyc_taxi.raw_zones. Trips with an unmatched zone code silently disappear, which is why you validate referential integrity first.
Keeps every row from the left table and fills the right table's columns with NULL when there is no match. nyc_taxi.raw_trips LEFT JOIN nyc_taxi.raw_zones keeps all trips; a trip with no matching zone gets a NULL borough instead of being dropped. The LEFT JOIN ... WHERE right.key IS NULL pattern is how you find orphaned foreign keys.
Keeps every row from the right table and NULLs the left side when there is no match. Mirror image of a LEFT JOIN. A right join of trips and zones keeps all zones, including ones that no trip references.
Keeps every row from both tables, matching where it can and filling NULLs on either side where it cannot. Also called a full outer join. Useful for spotting records that exist on one side but not the other.
The result of pairing every row of one table with every row of another. Usually an accident: a comma join (FROM nyc_taxi.raw_trips t, nyc_taxi.raw_zones z) with no WHERE linking the tables is a cross join. Joining nyc_taxi.raw_trips (57,000 rows) with nyc_taxi.raw_zones (265 rows) this way produces over 15 million rows and can crash your session. Writing joins as JOIN ... ON ... protects you: leave off the ON and Postgres raises a syntax error instead of silently exploding.
A named, temporary result set defined in a WITH ... AS (...) block that exists only for the duration of one query. CTEs break a long query into readable, named steps (clean, then join, then aggregate). Preferred over nested subqueries because they are easier to read, debug, and reuse within the same query.
Running SQL checks after ingestion but before modeling to confirm the raw data is trustworthy: no duplicates, no missing required fields, foreign keys that resolve, and physically possible values. The shared pattern is a query that returns only the bad rows; zero rows back means the data passed.
The guarantee that every foreign key value actually exists in the table it references. Every pickup_location_id in nyc_taxi.raw_trips should exist in nyc_taxi.raw_zones. Raw source data does not always guarantee this, so you check it before trusting any join.
A foreign key value that has no matching row in the referenced table. A trip pointing at a location_id that is not in nyc_taxi.raw_zones is orphaned: it vanishes in an inner join or shows a NULL borough in a left join. Find orphans with LEFT JOIN ... WHERE z.location_id IS NULL or NOT EXISTS (never NOT IN, which hides them when the subquery contains a NULL).
A separate analytical store, optimized for large scans, that you copy operational data into so heavy reporting queries never compete with live traffic. The idea predates the cloud (Bill Inmon wrote about warehouses in the early 1990s); cloud warehouses just made it cheap to rent by the hour.
Online Transaction Processing: a system built for many small, fast reads and writes, one row at a time. A webshop recording an order or a banking app moving money. OLTP databases use row-oriented storage and normalized schemas. Azure Database for PostgreSQL, the shared class database, is an OLTP system.
Online Analytical Processing: a system built to scan huge numbers of rows and squash them into summary numbers. "What was the average fare per pickup zone last month?" is an OLAP query. OLAP systems use columnar storage and denormalized or star schemas.
A layout that keeps all the columns of one record together on disk, so fetching or updating a whole row is cheap. Ideal for OLTP point lookups and writes, poor for analytical scans that need only one column out of many.
A layout that keeps all the values of one column together on disk. A query that needs only fare_amount reads just that column and ignores the rest, and columns compress well because they hold one repeated data type. This is the single biggest reason OLAP warehouses are fast at analytics. Bad for OLTP, because updating one whole row means touching every column block.
Extract, Load, Transform: load raw data into the analytical store first, then transform it there with SQL. This is the flow you built across Weeks 6 to 8. It contrasts with ETL, which transforms data before loading it. The copy-then-transform step is what keeps heavy queries off the production OLTP database.
The middle of the three modeling layers (raw, staging, mart). One staging model per raw table that does light cleanup only: cast types, rename columns to snake*case, filter obvious garbage like negative fares. No joins, no aggregation. Named with a stg* prefix (or vw*stg* for this week's views).
The business-ready layer that analysts and dashboards query. Marts join staging models into facts and dimensions shaped to answer business questions. fct_trips and dim_zones live here. A mart has a clear, stated grain and stable column names.
What a single row of a table represents, stated in one plain sentence: "one row per taxi trip," "one row per zone." The most important modeling decision, because every other choice (keys, measures, safe joins) depends on it. If you cannot state the grain in one sentence, the table is trying to be two tables at once and your metrics will not add up.
A table of measurable events, where each row is something that happened with numbers you can add up. nyc_taxi.raw_trips is a fact: each row is one trip, and fare_amount, trip_distance, and tip_amount are measures. Facts are tall and narrow (many rows, mostly numbers and foreign keys) and use the fct_ prefix.
A numeric column in a fact table that you aggregate (SUM, AVG, COUNT). In nyc_taxi.raw_trips, fare_amount, trip_distance, and tip_amount are measures. The test: if you would add a column up, it is a measure in a fact; if you would GROUP BY it, it is an attribute in a dimension.
A table of descriptive context: the who, what, where, and when that gives a fact meaning. 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) and use the dim_ prefix. The test: if you would SUM a column it is a measure in a fact; if you would GROUP BY it, it is an attribute in a dimension.
A modeling pattern with one fact table in the center surrounded by the dimension tables it references, joined by foreign keys. Drawn out, the foreign-key lines radiate like a star. Fast to query and easy for a human to read. In the taxi model, fct_trips sits in the middle and joins to dim_zones twice (once for pickup, once for dropoff).
The column (or set of columns) designated as the unique identifier of a table. Must be unique and never null. In dim_zones, location_id is the primary key, and it must be unique or joins fan out.
A column in one table that refers to the primary key of another. nyc_taxi.raw_trips.pickup_location_id is a foreign key pointing at nyc_taxi.raw_zones.location_id. Foreign keys are the lines that connect a fact to its dimensions.
An identifier that already exists in the source data and carries business meaning. location_id in nyc_taxi.raw_zones is a natural key: it comes from the NYC taxi commission. When a natural key is clean and stable, it can also serve as the primary key.
An artificial identifier you generate yourself, usually an incrementing integer or a hash, used when no clean natural key exists, the natural key is messy (a long string or several columns), or you need to track changing dimension values over time. The taxi data does not need one, but production warehouses use them constantly.
A named, saved SELECT query that behaves like a table but stores no rows of its own. Querying a view re-runs its underlying query against the current source data, so it always reflects the latest rows and costs almost no storage. This week you build vw_stg_trips, vw_fact_trips, and vw_dim_zones as plain views. The trade-off: a plain view re-computes its query on every read, with no cached result.
A view whose query is run once and whose result is stored on disk like a table, so reads are fast. The stored result goes stale until you REFRESH it. Postgres supports both plain and materialized views. At 57K rows a plain view is instant, so materialization is not needed this week; you meet the trade-off again when you reach dbt.
Still missing a term? The PostgreSQL documentation is the authoritative reference for any SQL keyword or function this glossary does not cover.