“The database is slow.” It is the most common sentence in data work and one of the least useful, because “the database” is not one thing. It is at least five distinct pieces of software stacked on each other, and the slowness belongs to exactly one of them. An engineer who can name the pieces asks a sharper question and finds the answer in minutes rather than days.

A database is the data. A database management system — DBMS — is the software that stands between you and the data and refuses to let you corrupt it. Everyone says “database” for both, which is fine in conversation and confusing when debugging.

The five components

flowchart TD
  Q["Your SQL text"] --> P["1. Parser
is this valid SQL?"]
  P --> C["2. Catalogue
do these tables and columns exist?"]
  C --> O["3. Optimiser
which plan is cheapest?"]
  O --> E["4. Execution engine
run the chosen plan"]
  E --> S["5. Storage manager
blocks, buffer pool, indexes"]
  E --> T["Transaction manager
locks, log, recovery"]
  T --> S

1. The parser reads your text and checks it is grammatically valid SQL. It knows nothing about your tables. An error here is a typo — a missing comma, a stray bracket — and it is the cheapest error to get, because nothing has been touched yet.

2. The catalogue (or system catalogue, or data dictionary) is the database's own record of what exists: every table, every column, every type, every constraint, every index. Crucially, the catalogue is itself stored in tables, so you can query it with ordinary SQL. This is where your query is checked for meaning rather than grammar — does orders exist, does it have a column called status, are you comparing a date to a number?

-- The database describing itself. Nothing here was written by hand:
-- it was recorded when the table was created.
SELECT column_name,
       data_type,
       is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Look at what this gives you for free. Nobody documented these types in a wiki. The database knows them because it enforced them, and the enforcement and the documentation are the same object. That is the difference between a schema written down and a schema in force.

3. The optimiser is the interesting one. Your query says what you want; the optimiser decides how. For a join of three tables it might consider dozens of orderings, several join algorithms, and whether to use each available index. It estimates the cost of each option using statistics about the data — how many rows, how many distinct values, how they are distributed — and picks the cheapest estimate. Fact These are estimates, not measurements, which is why a plan can be badly wrong when the statistics are stale.

4. The execution engine carries out the chosen plan: scanning, filtering, joining, sorting, aggregating. This is where CPU time is actually spent.

5. The storage manager owns the bytes. It decides which block a row lives in, maintains indexes, and runs the buffer pool from Lesson 0.2. Beside it sits the transaction manager, holding locks and writing the log that makes crash recovery possible.

Declarative is the whole trick

Nearly every programming language is imperative: you write the steps. SQL is declarative: you write the result you want and the system chooses the steps. Inference This is what lets a query written in 2015 still be correct in 2026 on a thousand times more data, running a plan nobody had invented when it was written. You never had to describe the plan, so the plan was free to change.

It also has a cost, and beginners feel it as frustration. When SQL is slow, you often cannot fix it by writing better steps — you have to persuade the optimiser to choose a different plan, by adding an index, refreshing statistics, or restructuring the query so a cheaper plan becomes available. The indirection that gives you longevity also takes away direct control.

Product analytics A concrete example: the query below asks for units sold per category. You have specified no join order, no algorithm, no memory budget. Three different engines will run it three different ways and all return the same answer.

SELECT c.department,
       c.category_name,
       SUM(oi.quantity) AS units
FROM order_items AS oi
JOIN products   AS p ON p.product_id  = oi.product_id
JOIN categories AS c ON c.category_id = p.category_id
GROUP BY c.department, c.category_name
ORDER BY units DESC, c.category_name;

Not all DBMSs are relational

The word covers a wider family than this course will use:

Inference Relational systems dominate analytics not because tables are the only sensible shape but because the relational model is the one with a rigorous mathematical foundation (Lesson 0.8), which is what makes automatic query optimisation possible in the first place. If you cannot reason about equivalent forms of a query, you cannot safely rewrite it — and if you cannot rewrite it, you have no optimiser.

When you do not want a DBMS at all

• Embedded, single-process work. An analyst exploring a few Parquet files needs a query engine, not a server. In-process engines give you SQL with no daemon, no ports, no users to administer.
• Pure caching. If you need a fast key-to-value store with no durability requirement, a full DBMS is enormous overhead for what an in-memory cache does better.
• Archival. Data that will be read once in five years, if ever, belongs in cheap object storage as compressed files. Keeping it in a live database means paying for availability nobody will use.
• Truly unstructured blobs. Video, images and raw documents belong in object storage, with only their metadata in a database.

SELECT column_name,
       data_type,
       is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;

Start where every company starts: a spreadsheet. A shop sells running shoes and keeps orders.csv on a shared drive. One person edits it. It works perfectly, and for a long time there is no reason to change.

Then the business grows, and the file starts to fail in five specific ways. Each failure has a name, and each one is a feature a database was built to provide. Inference Learning databases as “a list of five solved problems” rather than “a list of keywords” is what lets you decide, later in your career, when not to reach for one.

The five failures of the shared file

1. Concurrency. Two people open orders.csv at 14:00. Amara adds order 1026 and saves at 14:03. Bruno, who opened the older copy, adds order 1027 and saves at 14:05. Bruno's save overwrites the whole file. Order 1026 is gone — silently, with no error, and nobody notices until the month-end reconciliation.

2. Integrity. Nothing stops a typo. A quantity of -3, a date of 2024-13-45, an order pointing at customer 9999 who does not exist. The file will accept all of it, because a file has no opinion about its contents.

3. Atomicity. Recording a sale means two changes: append to orders, decrement stock. If the laptop dies between them, you have sold a shoe you still believe is in the warehouse. There is no way to say “both of these or neither”.

4. Query cost. To answer “revenue by category last quarter” you must read every row, every time. At ten thousand rows that is instant. At fifty million it is a coffee break, and it gets slower every week.

5. Redundancy. The customer's name and country get typed into every order row. When Amara moves from the UK to Ireland you must find and fix every row — and if you miss one, the file now disagrees with itself. There is no longer a single answer to “where does Amara live?”

What you are actually buying

A database management system (DBMS) is software that sits between you and the bytes on disk and enforces guarantees the bytes cannot enforce themselves. The five failures map onto them one-for-one:

E-commerce Here is the redundancy problem made concrete. Storing the customer's country on every order row means the same fact lives in many places. Storing it once in customers and joining means it lives in exactly one:

-- The customer's country is stored ONCE, in customers.
-- Orders reference the customer by id and borrow the country when asked.
SELECT o.order_id,
       o.order_date,
       c.full_name,
       c.country
FROM orders  AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
ORDER BY o.order_date
LIMIT 5;

Change Amara's country in one row of customers and every one of her orders reports the new value immediately. That is not a convenience; it is the elimination of an entire category of bug.

Notice the last row: Elif Demir's country is NULL — she never supplied one. The join did not drop her row, it simply carried the absence through. Fact NULL means “no value recorded here”, which is different from an empty string and different from zero. It is the single most common source of subtle SQL bugs, and Lesson 1.1 is devoted to it.

flowchart LR
  A["Application
(asks a question)"] --> B["Query planner
(chooses HOW)"]
  B --> C["Execution engine
(does the work)"]
  C --> D["Storage + indexes
(bytes on disk)"]
  C --> E["Transaction manager
(all-or-nothing)"]
  E --> D

When a database is the wrong choice

The honest answer, and one that separates engineers from keyword-memorisers: databases are frequently overkill. Reach for a file when:

• The data is small and single-writer. A few thousand rows that one person edits — a spreadsheet is faster to build, easier to inspect, and needs no operations.
• The data is write-once, read-sequentially. Application logs are appended and later scanned in bulk. Flat files (or Parquet) beat a database on both cost and throughput.
• The shape changes constantly. Early-stage exploratory data whose fields change weekly fights a rigid schema. Document stores or plain JSON files may fit better until the shape settles.
• You need one file to hand to someone. CSV is the universal interchange format precisely because it has no guarantees to violate.

Open The boundary is genuinely contested, and it has moved. Tools like DuckDB now run warehouse-grade analytical SQL directly over Parquet files with no server at all — which collapses much of the old “files versus databases” distinction. The five guarantees still describe what you gain; what has changed is how much infrastructure you must run to get them.

-- Both managers can run this simultaneously and the result is correct,
-- because the database reads and writes the row within one atomic step.
BEGIN;
UPDATE products
   SET unit_cost = unit_cost      -- stand-in for a stock column
 WHERE product_id = 105;
COMMIT;

SQL is old, and it shows. Keywords are shouted in capitals. The clauses are written in an order the engine does not execute them in. NULL behaves unlike anything in any other language you will meet. None of this is bad design in a vacuum — each of it is a reasonable answer to a problem that no longer exists, preserved because too much depends on it to change.

History here is not decoration. It is the shortest route to predicting how a database will behave.

Storage that you could hold

Fact The earliest large-scale data processing used punched cards: stiff cards where the presence or absence of a hole at a position encoded a value. A card held one record. A dataset was a physical box of cards, and processing meant feeding them through a machine in order.

Three properties of that world shaped everything after it. Records were fixed-width, because a card had a fixed number of columns — which is why so many legacy systems pad values with spaces to a set length. Access was strictly sequential, because you fed the box in one end. And a dataset was a physical object, so “the data” and “the file” and “the program that reads it” were inseparable.

Magnetic tape replaced cards and kept the sequential constraint. Then disks arrived, and with them the ability to jump straight to an arbitrary position — random access. That is the hinge on which everything turns. Once you can go directly to a record without reading everything before it, indexes become possible, and once indexes are possible, a query can be answered without a full pass.

The navigational era, and its trap

The first true database systems, in the 1960s, were navigational. Records held physical pointers to other records, and a program traversed the structure by hand: fetch the customer, follow the pointer to their first order, follow the next-order pointer, and so on. Two families competed — hierarchical systems, where data formed a strict tree, and network systems, which allowed more general graphs of pointers.

They worked, and they were fast, and they had one crippling property. Fact The application code encoded the physical access path. If the storage layout changed — a new index, a reordering, a new relationship — every program that walked that path had to be rewritten.

Inference That is the cost that made the relational model worth adopting, and it is easy to underestimate today. It was not that navigational systems gave wrong answers; it was that asking a new question meant writing a new program, and changing the storage meant editing every old one. Analysis at the speed of thought was impossible.

Compare what the same question costs now. Below, three tables are combined without you stating a single access path — no pointers, no traversal order, no knowledge of how any of it is stored:

-- In 1968 this required a program that walked pointers between records,
-- and a storage change would have broken it. Here it is a statement of intent.
SELECT c.department,
       COUNT(DISTINCT oi.order_id) AS orders_touching,
       SUM(oi.quantity)            AS units
FROM order_items AS oi
JOIN products    AS p ON p.product_id  = oi.product_id
JOIN categories  AS c ON c.category_id = p.category_id
GROUP BY c.department
ORDER BY units DESC;

The tables are connected by values — a product id that appears in two places — not by stored addresses. Change how any of the three tables is physically laid out and this query is unaffected. That property has a name, physical data independence, and it is the single largest idea in Lesson 0.7.

Codd, System R, and a language called SEQUEL

Fact In 1970 Edgar F. Codd, working at IBM, published a paper proposing that data be organised as mathematical relations — tables — and accessed by a language based on logic rather than on pointer traversal. IBM built a research prototype called System R to test whether the idea could be made fast enough to be practical, and the query language developed for it was called SEQUEL, later shortened to SQL.

Two things about that origin still shape your daily experience. SQL was designed to be readable by non-programmers, which is why it reads like stilted English and why keywords are words rather than symbols. And it was standardised early and widely, which is why every vendor's dialect is recognisably the same language — and why none of them can fix its rough edges without breaking working systems.

Warehouses, the NoSQL detour, and separation

Through the 1980s and 1990s relational systems became the default for business record-keeping. Then a second need appeared: analysing the accumulated records rather than recording new ones. The transactional systems were tuned for small, frequent writes and buckled under large scans, so organisations built separate data warehouses — copies of the data restructured for reading. Lesson 0.12 treats that split properly.

In the 2000s, web-scale companies hit limits that relational systems of the day handled badly, and a wave of non-relational stores appeared under the loose label NoSQL, trading joins and strict consistency for horizontal scale. Inference The lasting outcome was not the replacement of SQL but its absorption of the lessons: most systems that launched without SQL have since added a SQL interface, because the query language turned out to be more valuable than the storage engine it was originally attached to.

The most recent structural change is the separation of storage and compute in cloud warehouses: data sits in cheap object storage while query engines are started and stopped independently. This changes the economics rather than the semantics — your SQL is unchanged, but the thing you are billed for is bytes scanned and seconds of compute, not a server you own.

When history is the wrong guide

• Precedent is not justification. “We have always denormalised this table” explains the current state, it does not defend it. Ask which constraint produced the decision and whether that constraint still holds.
• Old benchmarks expire. Advice tuned for spinning disks — obsessive index minimisation, elaborate manual clustering — can be actively harmful on modern storage. The reasoning may be sound and the premises dead.
• Some problems really are new. Streaming, semi-structured event data and vector search are not variations on 1970s problems, and forcing them into a purely relational frame produces awkward systems.

SELECT c.channel                          AS segment,
       date_trunc('month', o.order_date)  AS month,
       COUNT(*)                           AS orders
FROM orders    AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.channel, date_trunc('month', o.order_date)
ORDER BY segment, month;

An analyst writes a query, sees the rows come back in a pleasing order, and builds a report that depends on it. Weeks later the same query returns the same rows in a different order and the report is wrong. Nobody changed the query. Nothing is broken. The analyst was relying on a guarantee that was never made.

Understanding why that guarantee does not exist — and why refusing to make it was deliberate and correct — is understanding the relational model.

A relation is a set, and sets have no order

Fact In 1970, while at IBM, Edgar F. Codd published a paper proposing that data be represented as mathematical relations. A relation, in that sense, is a set of tuples, where each tuple has one value drawn from each of a fixed collection of named domains. In everyday language: a table with named, typed columns, whose rows form a set.

Three consequences follow immediately from “set”, and all three surprise beginners:

• Rows have no order. A set is unordered by definition. Any order you observe is an accident of how the engine happened to execute the query, and it may change when the data grows, when the plan changes, or when the query runs in parallel.
• Columns have no order. They are identified by name, not position. SELECT * presents them in a conventional order, but the model attaches no meaning to it.
• Rows have no address. There is no “row 7”. The only way to identify a row is by its values — which is precisely why keys exist (Lesson 0.9).

The practical rule that falls out of this is absolute and worth learning before anything else: if you need a specific order, you must write ORDER BY. There is no other way, and every alternative you may be tempted by — insertion order, primary key order, “it comes out sorted anyway” — is an observation about one execution, not a property of the data.

-- These two queries return the same SET of rows.
-- Only the second one guarantees the sequence you see.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status;

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

Notice the tie-break. Two statuses have the same count, so ORDER BY orders DESC alone would leave their relative order undetermined — a partial sort is not a total one. Adding status as a second key makes the result deterministic: the same input always produces the same sequence. Inference This is why non-deterministic ordering is such a persistent source of flaky tests and unreproducible reports: it usually looks stable until the data or the plan changes.

The three rules Codd actually cared about

Codd's argument was not primarily about tables being tidy. It was about independence, and it rests on three separations:

1. Logical structure separated from physical storage. You describe data as tables and columns. The system decides files, blocks, indexes, compression, ordering. Change the physical layer and every query keeps working. This is physical data independence, and it is the property that killed navigational databases.

2. Data separated from the access path. Tables are linked by matching values — a customer_id that appears in two tables — never by stored addresses. So a relationship can be traversed in either direction, and relationships that nobody anticipated can be discovered later without changing anything.

3. What separated from how. You state the result you want. The optimiser chooses the procedure. This is only safe because relations have a rigorous algebra (Lesson 0.8) that tells the optimiser which rewrites preserve meaning.

Insurance The second point is the one that pays off fastest in practice. Suppose you want customers who have never placed an order. Nobody designed a “never ordered” relationship into the schema — and it does not matter, because the relationship can be computed from values:

-- Nobody stored a "has never ordered" link. It is derived on demand.
SELECT c.customer_id, c.full_name, c.channel
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Where the model and SQL part company

SQL is not a faithful implementation of the relational model, and the differences are exactly the places where beginners get hurt.

SQL tables are bags, not sets. A true relation cannot contain duplicate tuples; a SQL table can contain identical rows unless a constraint forbids it. This is why DISTINCT exists at all, and why an accidental duplicate in a join can inflate a total without any error being raised.

SQL has NULL. Codd recognised the need to represent missing information, but SQL's particular treatment — a three-valued logic where comparisons with NULL yield “unknown” rather than true or false — is widely criticised and universally implemented. Open Whether NULL was the right design remains genuinely contested among database theorists; what is not contested is that you must learn its behaviour, because every engine you will use has it. Lesson 1.1 is devoted to it.

SQL column order is visible. SELECT * and positional constructs expose an ordering the model says does not exist — which is one good reason to name your columns explicitly in anything that will run more than once.

When the relational model is the wrong frame

• Order is intrinsic to the data. Time series, event streams, ranked sequences and document text are naturally ordered. You can model them relationally with an explicit sequence column, but you are re-adding what the model removed, and specialised stores may serve you better.
• Deeply nested, irregular records. Data whose shape varies per record — a product catalogue where every category has different attributes — either explodes into many sparse columns or many tables. Document storage can be a better fit.
• Recursive traversal of unknown depth. “Find every account connected to this one through any chain of shared addresses” is expressible in SQL with recursive queries, but a graph database is built for it.
• Genuinely schemaless landing zones. Raw data whose structure you do not yet know is better landed as-is and structured later than forced into columns on arrival.

SELECT c.customer_id,
       c.full_name,
       COUNT(o.order_id) AS completed_orders
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status = 'completed'
GROUP BY c.customer_id, c.full_name
ORDER BY completed_orders DESC, c.customer_id
LIMIT 5;

Two analysts write the same report differently. One uses a subquery, the other a join. The plans the engine produces are identical, and neither analyst finds that surprising — but neither can explain why it is safe for the engine to treat two different-looking queries as the same query.

The answer is that SQL sits on an algebra. No arithmetic is required to follow this lesson; “algebra” here means only a small set of operations that take relations in and give relations out, with provable rules about which rearrangements preserve the answer. Those rules are what an optimiser is allowed to do.

Closure: everything in, everything out

The foundational property is closure: every operator takes one or two relations and returns a relation. Nothing else. Inference This is why SQL composes so freely — a query can be nested inside a query, wrapped in a CTE, joined to another query, because the output of any of them is the same kind of thing as the input. Languages without closure force a different syntax at each level of nesting; SQL does not, and that uniformity is why complex queries stay readable.

The six operators

  SELECTION  (sigma)         PROJECTION (pi)
  keep some ROWS             keep some COLUMNS
  +---+---+---+              +---+---+---+
  | a | b | c |              | a | b | c |
  |###|###|###| <- kept      |###|   |###|
  | a | b | c |              |###|   |###|
  |###|###|###| <- kept      |###|   |###|
  +---+---+---+              +---+---+---+
   WHERE ...                  SELECT a, c

  CARTESIAN PRODUCT (x)      JOIN (bowtie) = product THEN selection
  every row of R with            R x S, keeping only pairs
  every row of S                 where the keys match
  R(2 rows) x S(3 rows)          R JOIN S ON R.k = S.k
      = 6 rows

  UNION / DIFFERENCE / INTERSECTION
  set operations on two relations of the SAME shape
  R UNION S       all rows in either
  R EXCEPT S      rows in R that are not in S
  R INTERSECT S   rows in both

Selection (σ) keeps rows satisfying a condition. In SQL: WHERE. Input one relation, output one relation with the same columns and fewer or equal rows.

Projection (π) keeps a subset of columns. In SQL: the SELECT list. Strictly, projection also removes the duplicate rows that dropping columns can create — which SQL does not do unless you write DISTINCT. That single divergence is the source of a great many duplicate-row surprises.

Cartesian product (×) pairs every row of one relation with every row of another. It is almost never what you want on its own, but it is the primitive from which joins are built. Its cardinality is exactly the product of the two inputs, which you can verify:

-- |R x S| = |R| * |S|, always. A missing join condition is a
-- Cartesian product, and this is why it explodes.
SELECT (SELECT COUNT(*) FROM categories) AS categories_r,
       (SELECT COUNT(*) FROM products)   AS products_s,
       (SELECT COUNT(*) FROM categories CROSS JOIN products) AS cross_product;

Six times twelve is seventy-two. Now imagine both tables holding a million rows: the product is a trillion. Inference This is exactly what happens when you forget a join condition, and it explains the characteristic symptom — not a wrong answer but a query that never finishes.

Join (⋈) is defined as a Cartesian product followed by a selection. Conceptually the engine pairs everything then discards non-matching pairs; in practice it never does that, because it uses hash tables or sorted merges to produce the same result without materialising the product. Fact That substitution is legal precisely because the algebra proves the two are equivalent, and it is one of the most valuable rewrites an optimiser performs.

Set operations combine two relations with identical column shapes. UNION, EXCEPT (difference) and INTERSECT. Marketing Difference answers “who is in this list but not that one” directly:

-- Customers who exist but have never appeared in orders.
-- Set difference states the question almost word for word.
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders
ORDER BY customer_id;

The same two customers came back from a LEFT JOIN ... IS NULL in Lesson 0.7. Two different-looking queries, one answer — because they are the same algebraic expression written two ways. That is the entire point of the algebra.

Why this pays: predicate pushdown

Because the operators obey algebraic laws, the optimiser can rearrange your query into an equivalent, cheaper one. The most important such law is that selection can often be moved before a join.

flowchart LR
  subgraph W["What you wrote"]
    A1["orders"] --> J1["join on customer_id"]
    B1["customers"] --> J1
    J1 --> F1["filter: status = completed"]
  end
  subgraph R["What the engine runs"]
    A2["orders"] --> F2["filter: status = completed"]
    F2 --> J2["join on customer_id"]
    B2["customers"] --> J2
  end

Both forms return the same rows. The second does less work, because the join processes only completed orders. You did not have to know this: you wrote what you wanted, and a proved-correct rewrite made it cheaper. Inference This is the concrete cash value of the theory, and it is also why “optimising” SQL by manually reordering clauses is usually wasted effort — the optimiser already explored those forms.

What the algebra does not cover

Codd's original algebra is smaller than SQL. Several things you use daily are extensions bolted on afterwards:

• Aggregation. GROUP BY and SUM are not classical relational operators; they were added because business questions demand them.
• Ordering. ORDER BY produces a sequence, which is not a relation. Strictly, it is the last thing a query does and its output has left the algebra.
• Duplicates. The algebra works on sets; SQL works on bags. Every duplicate-row bug you meet lives in this gap.
• Three-valued logic. NULL introduces “unknown” alongside true and false, which complicates the neat logical equivalences the algebra assumes.

When not to think algebraically

• When writing your first draft. Express the business question plainly. Algebraic reasoning is a debugging and optimisation tool, not a drafting one.
• When the engine is not relational. Document stores, search engines and graph databases have different primitives, and importing join-shaped thinking into them produces bad designs.
• When the problem is data quality. No amount of algebra rescues a query whose inputs contain duplicated keys. Fix the data, not the expression.

-- Form 1: set difference. Reads like the question.
SELECT country FROM customers WHERE country IS NOT NULL
EXCEPT
SELECT ship_country FROM orders WHERE ship_country IS NOT NULL
ORDER BY country;

-- Form 2: anti-join. Same answer, more room to add columns later.
SELECT DISTINCT c.country
FROM customers AS c
LEFT JOIN orders AS o ON o.ship_country = c.country
WHERE c.country IS NOT NULL
  AND o.order_id IS NULL
ORDER BY c.country;

A SaaS team reports monthly revenue that is roughly forty per cent too high. The query is short and looks obviously correct. The cause is not a bug in the SQL — it is that the author joined two tables holding facts at different levels of detail and summed a column that was then repeated. No error was raised, because nothing illegal happened.

That mistake has a name, grain, and it is the last idea in this lesson. The first four are the vocabulary you need to state it.

Four words, precisely

A table represents one kind of thing — customers, or orders, or line items. If you cannot finish the sentence “each row of this table is one ___” in a single noun, the table is doing more than one job.

A row is one instance of that thing. It has no position and no address; it is identified only by its values.

A column is one attribute, with a name and a type. Every value in it comes from the same domain.

A domain is the set of values a column is permitted to hold. This is broader than the data type: status is typed as text, but its domain is the three values completed, returned and cancelled. The type is enforced by the engine automatically; the rest of the domain must be enforced deliberately, with a CHECK constraint or a lookup table, or it is not enforced at all.

-- What values does this column ACTUALLY contain?
-- Run this on any new table before you trust a filter on it.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

Three values, cleanly. On a real system this query is how you discover that the domain also contains Completed, COMPLETE, an empty string and a handful of NULLs — each of which will be silently excluded by WHERE status = 'completed'. Inference Running this before writing any filter is a two-second habit that prevents a large class of quietly-wrong reports.

Keys: how you name a row

Since rows have no address, identifying one requires values. A key is a column, or set of columns, whose value is unique across the table.

• A candidate key is any set of columns that is unique and minimal — drop any column from it and uniqueness is lost. A table can have several: in customers, both customer_id and email could serve.
• The primary key is the candidate key you nominate as the official identifier. It must be unique and must never be NULL, because “unknown” cannot identify anything.
• A foreign key is a column whose values must exist as a key in another table. orders.customer_id references customers.customer_id, which is what makes an order pointing at a non-existent customer impossible.
• A composite key spans several columns, used when no single column is unique — for example an assignment table keyed by customer and experiment together.
• A surrogate key is a meaningless generated number used as the primary key, in preference to a natural key made of real-world values.

The surrogate-versus-natural choice is worth pausing on, because the argument is not obvious. An email address is a perfectly good natural key today — and then a customer changes it, and every table referencing it must be updated in step. Inference Natural keys embed a business rule you do not control into your physical structure. Surrogate keys are usually preferred for exactly that reason: they are meaningless, so they can never become wrong. The cost is an extra join whenever you want a human-readable label.

A key is a claim, and claims are testable:

-- If total_rows and distinct ids differ, customer_id is NOT a key.
-- non_null_country shows a column that is nullable and therefore
-- can never serve as a primary key.
SELECT COUNT(*)                   AS total_rows,
       COUNT(DISTINCT customer_id) AS distinct_customer_ids,
       COUNT(country)              AS non_null_country
FROM customers;

Seventeen rows, seventeen distinct ids: the key claim holds. And note COUNT(country) returning 15, not 17 — COUNT(column) ignores NULLs while COUNT(*) counts rows. Two customers have no recorded country, which instantly disqualifies country from ever being part of a primary key.

Grain: the idea that prevents wrong numbers

The grain of a table is what one row means. Say it as a sentence:

• customers — one row per customer.
• orders — one row per order.
• order_items — one row per product line within an order.

Now the failure. Order 1001 contains two line items. Join orders to order_items and order 1001 appears twice — correctly, since the result's grain is now one row per line item. Any order-level value carried along, such as a shipping charge, is now duplicated. Sum it and you have charged the shipping twice.

Inference The reliable rule: after any join, state the new grain out loud before aggregating anything. A join to a table with multiple matching rows changes the grain, and every column from the coarser side is now repeated. This is the forty-per-cent overstatement from the opening paragraph, and it is the most common serious error in analytics work.

When to break the rules deliberately

• Wide denormalised tables in a warehouse. Analytical layers routinely flatten many tables into one for speed and convenience, accepting the redundancy. That is a deliberate trade, not an error — provided the grain is documented and the source of truth lives elsewhere.
• Natural keys in slowly-changing reference data. A currency code table keyed on the ISO code is fine; the values are externally governed and effectively immutable.
• No enforced foreign keys in some warehouses. Several cloud warehouses do not enforce foreign keys at all, accepting them only as documentation and optimiser hints. Integrity then has to be enforced by tests in the pipeline instead. Open Whether that is a reasonable trade for load throughput or a step backwards is genuinely argued between engineering teams.

-- WRONG: counts line items, because the join changed the grain.
SELECT COUNT(*) AS orders_wrong
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id;

-- RIGHT (a): count distinct orders, restoring the intended grain.
SELECT COUNT(DISTINCT o.order_id) AS orders_right
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

-- RIGHT (b): better still, do not change the grain at all.
SELECT COUNT(*) AS orders_right
FROM orders
WHERE status = 'completed';

Normalisation is usually taught as three rules to memorise for an exam and then quietly ignore. That is backwards. The three forms are not rules; they are consequences of one goal, and if you hold the goal in mind you can re-derive them whenever you need them.

The goal: every fact should be stored in exactly one place. Everything else follows.

Why duplication is dangerous

Consider a FMCG team keeping one flat table of order lines, with the customer's name and country repeated on every row:

-- One table, everything in it. Note how Amara Osei and 'GB'
-- are repeated on every line she has ever bought.
WITH flat(order_id, customer_name, customer_country, product_name, unit_price, quantity) AS (
  VALUES (1001,'Amara Osei',   'GB','Velocity Road 7',  129.00,1),
         (1001,'Amara Osei',   'GB','Nimbus 0C Bag',    199.00,1),
         (1002,'Amara Osei',   'GB','Stormshell Jacket',219.00,1),
         (1003,'Bruno Salgado','BR','Ridgeline Trail 3',149.00,2)
)
SELECT * FROM flat ORDER BY order_id, product_name;

“Amara Osei lives in GB” is one fact stored three times. That produces three named failures, and they are the entire justification for normalisation:

• Update anomaly. Amara moves to Ireland. You must change three rows. Miss one and the table now asserts she lives in two countries, with no way to tell which is right.
• Insertion anomaly. A new customer signs up but has not ordered. There is nowhere to record them, because a row requires a product and a quantity. The structure forbids a fact you need.
• Deletion anomaly. Bruno's only order is deleted. His name and country vanish with it — you have lost a fact you never intended to delete.

Deriving the three forms

First normal form (1NF): each cell holds one value from its domain, and there are no repeating groups. A cell containing “shoes, jacket, tent” violates it, as do columns named product_1, product_2, product_3. The reason is practical: you cannot filter, join or aggregate on a value buried inside a delimited string without parsing it first, and any limit like “three products” is an arbitrary cap that reality will exceed.

Second normal form (2NF): 1NF, plus every non-key column depends on the whole primary key. This only bites when the key is composite. If a table is keyed by (order_id, product_id) and also holds customer_name, then the name depends on order_id alone, not on the pair — so it is repeated once per product on the order. Split it out.

Third normal form (3NF): 2NF, plus no non-key column depends on another non-key column. If a table keyed by product_id holds both category_id and category_name, the name depends on the category, not on the product. Every product in a category repeats the name. Split it out.

The traditional summary is that every non-key column must depend on the key, the whole key, and nothing but the key. That is a mnemonic for the three paragraphs above, not a substitute for them.

Decomposition, drawn

  BEFORE  one flat table, facts repeated
  +-------------------------------------------------------------+
  | order_id | cust_name | cust_country | product | price | qty  |
  |   1001   | Amara     |      GB      | Shoe    | 129   |  1   |
  |   1001   | Amara     |      GB      | Bag     | 199   |  1   |  <- 'Amara lives in GB'
  |   1002   | Amara     |      GB      | Jacket  | 219   |  1   |  <-  stored 3 times
  +-------------------------------------------------------------+

  AFTER   each fact once, linked by keys

   customers                 orders                   order_items
  +-------------+          +-----------------+      +---------------------+
  | customer_id |<---------| customer_id     |<-----| order_id            |
  | full_name   |          | order_id  (PK)  |      | product_id  ------+ |
  | country     |          | order_date      |      | quantity          | |
  +-------------+          +-----------------+      | unit_price        | |
   one row per                one row per           +-------------------|-+
   customer                   order                                     |
                                                       products <-------+
                                                      one row per product

The same information, with each fact in exactly one place. Amara's country now lives in a single cell. Change it and every order reports the new value, because no order ever stored it. The cost is that answering the original question now requires joins:

-- The normalised tables reconstruct the flat table on demand.
-- Same rows as before, but no fact is duplicated in storage.
SELECT o.order_id,
       c.full_name  AS customer_name,
       c.country    AS customer_country,
       p.product_name,
       oi.unit_price,
       oi.quantity
FROM orders       AS o
JOIN customers    AS c  ON c.customer_id = o.customer_id
JOIN order_items  AS oi ON oi.order_id   = o.order_id
JOIN products     AS p  ON p.product_id  = oi.product_id
WHERE o.order_id IN (1001, 1002, 1003)
ORDER BY o.order_id, p.product_name;

Identical output. The difference is invisible in the result and decisive in the storage: in the flat version, correcting Amara's country means finding and fixing three rows; in the normalised version it means changing one cell.

When to denormalise on purpose

Deliberate, documented denormalisation is normal professional practice, not a failure. The recognised cases:

• Analytical star schemas. A wide fact table joined to a few denormalised dimension tables is the standard warehouse design. Reads massively outnumber writes, and the writes come from a controlled pipeline rather than from users, so the update anomaly risk is managed elsewhere.
• Point-in-time facts that must not change. order_items.unit_price duplicates products.unit_price — and it must. The price paid on the day is a different fact from the price today, and if the order borrowed the current price by joining, every historical order would silently rewrite itself at the next price change. Inference This is the single most important exception to grasp: not all repeated values are redundant. Two values that happen to be equal are only redundant if they represent the same fact.
• Expensive joins on hot paths. Storing a pre-computed aggregate or a duplicated label to avoid a join in a query that runs constantly is a legitimate optimisation — provided something guarantees the copy stays in step.
• Immutable reference data. A country code that will never be edited can safely be copied around, since the anomaly it risks cannot occur.

A Banking transfer moves £500 from one account to another. That is two changes: subtract from one, add to the other. If the machine fails between them, £500 has left the world. No amount of careful programming prevents this, because the failure happens between two instructions and there is no instruction you can put in the gap.

A transaction is the answer: a group of changes the database treats as a single indivisible unit. Either all of them are recorded or none are. ACID is the four properties transactions provide, and each letter answers a distinct disaster.

A — Atomicity: all or nothing

An atomic unit cannot be split. The transfer either completes fully or leaves no trace. There is no state in which the money is nowhere.

The mechanism is worth knowing because it explains several behaviours you will observe. Before changing anything, the database writes its intention to a write-ahead log on durable storage. Only then does it modify data. If it crashes mid-transaction, on restart it reads the log, sees a transaction that started but never committed, and undoes its partial effects. Fact This is why databases can survive an unexpected power loss without corruption, and why the log is written before the data rather than after.

-- BEGIN opens a transaction. Nothing inside it is visible to
-- anyone else, and ROLLBACK discards the lot as if it never ran.
BEGIN;
UPDATE orders SET status = 'returned' WHERE order_id = 1002;
ROLLBACK;

-- The row is untouched: the change never existed for any other session.
SELECT order_id, status FROM orders WHERE order_id = 1002;

Replace ROLLBACK with COMMIT and the change becomes permanent and visible to everyone at that instant — not gradually, not partially. The commit is the moment the transaction happens.

C — Consistency: the rules always hold

A transaction moves the database from one valid state to another valid state. “Valid” means every declared constraint is satisfied: no negative quantities where a CHECK forbids them, no order referencing a customer who does not exist, no duplicate primary key. If a transaction would leave the data in a state that breaks a rule, the whole transaction is rejected.

Inference The C is the letter people most often misunderstand, because it does not mean “your data is correct”. The database has no idea what correct means. It means “the rules you declared are never violated” — so consistency is only as strong as the constraints you bothered to write, which is the argument Lesson 0.5 makes for writing them on day one.

I — Isolation: concurrent work behaves as if serial

Many transactions run at once. Isolation is the guarantee that each behaves as though it had the database to itself. Without it, transactions read each other's half-finished work and the classic anomalies appear:

Isolation is not one setting but a ladder of levels, each preventing more anomalies at a higher cost in blocking and retries. Fact The strongest level, serializable, guarantees the outcome is equivalent to running the transactions one after another in some order; weaker levels permit specific anomalies in exchange for more concurrency. Fact Default levels differ between engines, so a program correct on one database can be subtly wrong on another without any code change.

D — Durability: committed means committed

Once a commit returns successfully, the change survives crashes, power loss and restarts. It is on durable storage, or at least in a log on durable storage from which it can be replayed. Inference This is why commits are not free: the system must genuinely persist the log before reporting success, which involves waiting for physical storage rather than merely for memory. Committing once per row in a large load is therefore dramatically slower than committing once per batch, and that single fact explains most slow bulk-insert problems.

What it costs

Every guarantee is paid for, and the bill arrives as reduced concurrency.

• Locks and waiting. Isolation is usually implemented by locking rows or by keeping multiple versions of them. Either way, transactions sometimes wait for each other, and throughput falls.
• Deadlocks. Two transactions each holding what the other needs will wait forever, so the database detects the cycle and kills one. Your application must be prepared to retry — a transaction that fails is a normal event, not an exceptional one.
• Long transactions are toxic. A transaction held open for minutes blocks others and forces the system to retain old row versions. Keep them short: do the slow work outside, then open, change, commit.
• Commit latency. Durability requires waiting for storage. Batch your writes.

When you do not need full ACID

• Append-only analytics loads. A nightly pipeline writing immutable event rows has no concurrent updaters, so most of the machinery protects against risks that cannot occur.
• Caches and derived data. Anything reconstructible from a source of truth does not need durability — if it is lost you rebuild it.
• Very high-volume distributed writes. Some systems deliberately relax isolation or offer eventual consistency to achieve scale and availability that strict guarantees would forbid. Open How far to relax is one of the genuinely contested questions in distributed systems, and the honest answer is that it depends on whether your business can tolerate a temporarily wrong answer — a social media like count can, a bank balance cannot.
• Single-user analysis. An analyst querying a local file has no concurrency to control.

-- The WHERE clause is evaluated at write time, inside the same
-- atomic step as the update. If stock is already 0, zero rows change
-- and the application knows the sale failed.
BEGIN;
UPDATE products
   SET unit_cost = unit_cost - 1     -- stand-in for a stock column
 WHERE product_id = 105
   AND unit_cost >= 1;
ROLLBACK;

An analyst at a Retail company runs a full-year revenue report against the live shop database at four in the afternoon. Checkout slows to a crawl. Nobody did anything wrong — the report is legitimate, the database is healthy, and the two workloads are simply incompatible.

The incompatibility is structural rather than accidental, and understanding it explains why nearly every organisation ends up with two systems and a pipeline between them.

Two shapes

OLTP — online transaction processing — is the system that runs the business. Place an order, update an address, take a payment. Its queries touch very few rows, identified precisely, and both read and write. There are many of them, they are concurrent, and each must return in milliseconds because a person is waiting.

OLAP — online analytical processing — is the system that studies the business. Revenue by category by month, cohort retention, campaign effectiveness. Its queries touch enormous numbers of rows but few columns, are read-only, and are allowed to take seconds.

Read the last two rows together with Lesson 0.2 and the conflict becomes obvious. OLTP wants a whole row available in one block, because it needs every column of one order. OLAP wants one column available across all rows, because it needs one field from millions of orders. Inference These are opposite physical layouts, and no single arrangement of bytes on disk is good at both. That, more than anything about software, is why the two systems separate.

Compare the two shapes directly. A point lookup:

-- OLTP: one row, every column, found by key. Must be instant.
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE order_id = 1015;

And an analytical aggregate over the same table:

-- OLAP: every relevant row, a handful of columns, grouped.
-- No single row matters; only the aggregate does.
SELECT date_trunc('month', o.order_date) AS month,
       COUNT(DISTINCT o.order_id)        AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price
                 * (1 - oi.discount_pct / 100.0)), 2) AS revenue
FROM orders      AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY date_trunc('month', o.order_date)
ORDER BY month;

Note the COUNT(DISTINCT o.order_id): the join to order_items changed the grain, exactly as Lesson 0.9 warned, so a plain COUNT(*) would have counted line items. Note also / 100.0 rather than / 100 — integer division truncates, and a 15% discount would silently become 0%.

What the split looks like in practice

flowchart LR
  A["Shop app
(OLTP database)"] -->|"extract"| B["Pipeline
(clean, reshape)"]
  B -->|"load"| C["Warehouse
(OLAP, columnar)"]
  C --> D["Dashboards"]
  C --> E["Ad-hoc analysis"]
  C --> F["Models & reporting"]

Three consequences follow that shape every data job:

• The warehouse is behind. Data arrives on a schedule, so the warehouse lags the production system by minutes or hours. “Why doesn't this match the app?” is usually answered by the pipeline schedule, not by a bug.
• The warehouse is reshaped. Normalised production tables become denormalised analytical ones. Column names and even meanings can differ, which is why lineage documentation matters.
• The warehouse is not the source of truth. If they disagree, the production system is right by definition. The warehouse is a derived copy.

When one system is enough

• Small data. If the whole database fits comfortably in memory, analytical queries may finish fast enough on the transactional system that the split adds only complexity and lag. Do not build a warehouse for a few gigabytes.
• Early-stage products. A pipeline is a permanent maintenance commitment. Before the questions have stabilised, querying production carefully off-peak is often the right call.
• Genuinely hybrid engines. Open Some modern systems aim to serve both workloads from one store, keeping row and column representations in step internally. Whether this makes the split obsolete for mainstream use or remains a specialised choice is an open question and actively marketed in both directions — treat vendor claims here with more scepticism than usual.
• Read-only reporting on a static dataset. With no writes, there is no contention to avoid.

The same query, the same data volume, the same hardware: on one engine it takes minutes, on another it returns immediately. No index explains it. The difference is which values were stored adjacent to which.

Recall from Lesson 0.2 that storage transfers whole blocks, never individual values. That single constraint means the arrangement of values inside a block determines how much of what you read is useful — and how much you paid to move and then discard.

The two layouts

  LOGICAL TABLE
  order_id | customer_id | order_date  | status    | ship_country
  ---------+-------------+-------------+-----------+-------------
    1001   |      1      | 2024-01-08  | completed | GB
    1002   |      1      | 2024-03-12  | completed | GB
    1003   |      2      | 2024-01-25  | completed | BR
    1004   |      3      | 2024-02-09  | completed | SG

  ROW STORE  -- one row's values are contiguous
  block 1: [1001,1,2024-01-08,completed,GB][1002,1,2024-03-12,completed,GB]
  block 2: [1003,2,2024-01-25,completed,BR][1004,3,2024-02-09,completed,SG]
           \______ read one block, get WHOLE rows ______/

  COLUMN STORE  -- one column's values are contiguous
  block 1 (order_id):     [1001, 1002, 1003, 1004]
  block 2 (customer_id):  [   1,    1,    2,    3]
  block 3 (order_date):   [2024-01-08, 2024-03-12, 2024-01-25, 2024-02-09]
  block 4 (status):       [completed, completed, completed, completed]
  block 5 (ship_country): [GB, GB, BR, SG]
           \___ read one block, get ONE column for MANY rows ___/

Now trace two queries through both layouts.

“Give me everything about order 1003.” The row store reads one block and has the complete answer. The column store must read five blocks — one per column — and pick the third value out of each. Inference Row storage wins point lookups decisively, and the gap widens as the table gets wider.

“How many distinct ship countries?” The row store must read every block of the entire table, then discard four values out of five. The column store reads one block. Row storage loses badly here, and the gap widens with the number of columns you are not asking for.

That is the whole mechanism. Everything below is a consequence.

Why columnar also compresses far better

A second, less obvious advantage compounds the first. In a column, all values are the same type and are frequently similar or repeated — the same country codes, the same statuses, dates in near order. Fact Compression works by exploiting repetition, so a column of similar values compresses substantially better than a row mixing an integer, a date and two strings.

Three techniques recur:

• Run-length encoding. Twenty-one consecutive “completed” values become “completed × 21”.
• Dictionary encoding. Distinct strings are numbered once and the column stores the small numbers. A status column with three distinct values needs very few bits per row.
• Zone maps. The minimum and maximum of each block are recorded, so a filter on a date range can skip entire blocks without decompressing them.

Inference The compounding matters more than either effect alone: reading fewer columns means less data, and better compression means those columns are smaller too. This is why analytical engines can scan very large tables quickly — not because they read faster, but because they arrange not to read most of it.

The query below touches one column out of six and produces three aggregates from it — the archetypal columnar workload:

-- A column store reads the quantity and unit_price columns and
-- ignores the rest of the table entirely. A row store reads it all.
SELECT COUNT(*)             AS rows_touched,
       SUM(quantity)        AS total_units,
       ROUND(AVG(unit_price), 2) AS avg_price
FROM order_items;

At thirty rows the layout is irrelevant. At thirty billion it is the difference between a dashboard and an overnight job.

The cost: writes

Inserting one row into a row store appends one contiguous piece of data to one block. Inserting one row into a column store means touching every column's storage — and since those columns are compressed in large blocks, a single-row insert may require decompressing and rewriting them.

Inference This is why columnar systems favour bulk loading and why many of them handle updates poorly or not at all, preferring append-only writes with periodic compaction. It is also why the OLTP/OLAP split from Lesson 0.12 is so durable: the layout that makes analytics fast makes transactional writes slow, and that is a property of physics and layout rather than of any vendor's implementation.

When not to use columnar

• Point lookups and single-row updates. Anything user-facing that fetches or edits one record wants a row store. This is not a close call.
• High-frequency small writes. An application recording individual events as they happen should write to a row store, with a pipeline batching them into the warehouse.
• Narrow tables. With four columns, all of which you always read, columnar's chief advantage — skipping columns — has nothing to skip.
• Small data. Under a few million rows, both layouts feel instant and the choice should be made on operational convenience instead.

SELECT c.title,
       SUM(v.minutes) AS minutes_watched
FROM views   AS v
JOIN content AS c ON c.content_id = v.content_id
GROUP BY c.title
ORDER BY minutes_watched DESC, c.title;

Job adverts name platforms, not concepts. “Snowflake experience required.” “Strong BigQuery.” This makes the landscape look like five unrelated skills, and it is not — the concepts from this part apply to all of them, and the differences are narrower than the marketing suggests.

This lesson gives you the map. Nothing in it is a benchmark or a recommendation, because both would be out of date before you read them.

Three architectures

A data warehouse stores structured, cleaned, schema-on-write data. You define the schema before loading; anything that does not fit is rejected or transformed first. Strong guarantees, strong performance, and the price is that raw or awkwardly-shaped data has nowhere to go.

A data lake stores raw files in cheap object storage — Parquet, JSON, CSV, images, anything — with schema applied at read time. Cheap and flexible, but nothing enforces structure, so a lake without governance becomes a swamp: petabytes nobody trusts and nobody can find anything in.

A lakehouse is the attempt to have both: files in object storage, with a table format layered on top that adds schemas, transactions, versioning and time travel. Inference The architecture exists because organisations found themselves running a lake and a warehouse, copying between them, and paying twice for the same data with two versions of the truth. Whether the merge fully succeeds is still being decided in practice.

flowchart TD
  S["Sources: apps, events, files, APIs"] --> L["Object storage
(raw files: Parquet, JSON)"]
  L --> T["Table format layer
(schema, transactions, versions)"]
  T --> E["Query engines
(SQL)"]
  S --> W["Warehouse
(schema-on-write)"]
  W --> E
  E --> B["Dashboards, analysis, models"]

The platforms

All five below are columnar and all speak SQL. Their differences are chiefly in how compute is provisioned and billed, and in dialect details.

Open Any ranking of these on speed or cost is contested, workload-specific, and changes with each release. Vendor benchmarks are marketing. If you need to choose, test with your own data and your own queries; if you are learning, the choice barely matters, because the transferable part is the SQL and the concepts.

What travels, and what does not

The good news dominates. Core SQL is portable: SELECT, WHERE, GROUP BY, joins, subqueries, CTEs, window functions and set operations behave the same everywhere. This query runs unchanged on every platform in the table:

-- Standard SQL. Nothing here is dialect-specific.
SELECT c.department,
       COUNT(DISTINCT o.order_id) AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price
                 * (1 - oi.discount_pct / 100.0)), 2) AS revenue
FROM order_items AS oi
JOIN orders      AS o ON o.order_id    = oi.order_id
JOIN products    AS p ON p.product_id  = oi.product_id
JOIN categories  AS c ON c.category_id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.department
ORDER BY revenue DESC;

What does not travel is the surrounding layer: date and time functions, string functions, type casting syntax, JSON handling, the exact spelling of QUALIFY or PIVOT, and everything to do with partitioning and clustering. Here is the same idea of “truncate a date to the month” in a dialect-specific form — recognisable, but not identical:

-- Same intent, platform-specific spelling. Note DATE_TRUNC's
-- argument order and the unquoted MONTH keyword.
SELECT DATE_TRUNC(order_date, MONTH) AS month,
       COUNT(*) AS orders
FROM `project.dataset.orders`
WHERE status = 'completed'
GROUP BY month
ORDER BY month;

Inference The practical implication for your learning: invest in the concepts and the standard core, and look up the dialect specifics when you need them. Someone strong in standard SQL becomes productive on an unfamiliar platform in days; someone who has memorised one dialect's functions without the concepts underneath does not.

When you do not need any of these

• Data that fits on one machine. A very large fraction of real analytical work involves datasets in the gigabytes, not the petabytes. An in-process engine on a laptop handles that comfortably, with no cluster, no bill and no waiting for a platform team.
• A transactional workload. None of these platforms is the right home for your application's live data. They are analytical systems, and Lesson 0.12 explains why that is not a fixable shortcoming.
• Small teams with simple needs. A managed relational database with a read replica serves many organisations for years. Adopting a warehouse buys scale you may not need and an operational burden you certainly will feel.
• Genuinely unstructured work. Video, audio and free text are better handled with purpose-built tools, with only their metadata in the warehouse.