SQL Theoretical

The reasoning

A foreign key names a parent. In PostgreSQL or MySQL, inserting an order with a `customer_id` that no customer has is rejected. Deleting a customer with orders is rejected or cascades, depending on the constraint's `ON DELETE` rule.

Referential integrity is the state where every child row has a parent. It is what lets an inner join be trusted: if integrity holds, joining orders to customers loses no orders. If it does not, an inner join silently drops orphans.

Snowflake, BigQuery and Redshift let you declare primary and foreign keys but, per their documentation, do not enforce them; the optimizer may use them as hints. A late-arriving dimension, a deleted parent, or a bad load leaves orphans that no error reports.

So in a warehouse the constraint becomes a test: count child rows whose key has no parent, and run it after every load. Then decide whether orphans block the publish or route to an unknown member.

The reasoning

An inner join is the intersection on the join condition. A left outer join is the inner join plus every unmatched left row with nulls on the right. Full outer is both sides' unmatched rows added. Cross is the Cartesian product, every row paired with every row, useful for generating combinations and dangerous by accident.

A semi join answers "which customers have orders". Written as a join it duplicates each customer once per order. Written as `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)` it returns each customer once and never fans out. Engines plan it as a semi join and stop at the first match.

An anti join answers "which customers have no orders": `WHERE NOT EXISTS (...)`. It is also written as a left join filtered on `o.id IS NULL`. `NOT IN` is the third form and the one that returns nothing when the subquery contains a null.

Spark names them `left_semi` and `left_anti` directly. In SQL the engine recognises the `EXISTS` shape, which is why writing a semi join as a join plus `DISTINCT` is slower and risks the wrong count.

The reasoning

A table's grain often needs more than one column to identify a row: one row per order per line, per customer per day, per student per course. The composite key is that set of columns declared together as the primary key.

It is right when it is the real identity of the row. A surrogate integer can still be added for joins and foreign keys, which is the usual practice in dimensional models, but the composite key is what uniqueness is tested on.

Building the key as one concatenated string is tempting and wrong. `customer_id || '-' || event_day` collides when a customer id contains a dash, cannot be range-scanned by day, and forces every consumer to parse it back. Keep the columns, and if one key is needed, hash the tuple or assign a surrogate.

Composite keys in joins need every column in the condition. Joining on one part of a two-column key is the most common source of fan-out in a data warehouse.

The reasoning

Atomicity is the rollback guarantee. A load that inserts 200,000 rows and fails on row 150,001 leaves none of them if it ran in one transaction. Without it, a retry has to find and remove the partial load first.

Consistency means the rules are kept. Primary keys stay unique, foreign keys stay satisfied, check constraints hold, at the end of every transaction. An engine that lets a constraint break between statements is not consistent.

Isolation decides what a concurrent reader sees, and it has levels. At the strongest, serializable, the outcome is as if transactions ran one at a time. Weaker levels trade that for throughput and allow specific anomalies.

Durability is the write-ahead log. The engine writes the log to disk before it acknowledges the commit, so a power cut after the acknowledgement loses nothing. On a lakehouse the same four words apply, with the transaction log and the atomic metadata swap playing the roles.

The reasoning

A dirty read sees another transaction's uncommitted write, which may be rolled back. A non-repeatable read re-reads a row and finds it changed by a committed transaction. A phantom read re-runs a query and finds new rows that another transaction committed. A serialization anomaly is a result that no serial order of the transactions could produce.

The PostgreSQL docs table maps them: read uncommitted allows all; read committed stops dirty reads; repeatable read stops non-repeatable reads too; serializable stops everything. The docs also note that PostgreSQL implements only three levels, with read uncommitted behaving as read committed, and that its repeatable read does not allow phantoms either.

Read committed, the PostgreSQL default, means each statement sees data committed before that statement began, so two reads in one transaction can differ. MySQL's InnoDB defaults to repeatable read, where the transaction sees a snapshot from its first read.

For a pipeline the practical consequence is that a long extraction at read committed can see rows committed mid-run. An incremental load should either read inside a snapshot or bound itself by a watermark captured first.

The reasoning

Without an index, `WHERE customer_id = 42` reads every row. With a B-tree on `customer_id`, the engine compares 42 at the root, follows the branch, and repeats for two or three levels. It arrives at a leaf page that lists where the matching rows live. A billion rows is about four page reads.

Because the leaves are sorted and linked, a range such as `BETWEEN 100 AND 200` or `> '2026-01-01'` walks the leaves in order. An `ORDER BY` on the indexed column can read in index order and skip the sort.

The index helps only when the predicate can be matched against the stored values. `WHERE lower(email) = 'x'` cannot use an index on `email`, because the stored values are not lowercased. `LIKE 'abc%'` can use it; `LIKE '%abc'` cannot. A type mismatch that casts the column defeats it too. This property is called sargability.

Every index costs writes, because each insert and update maintains it, and space. The write cost is why a table with 15 indexes loads slowly, and why warehouses use clustering and statistics instead of B-trees.

The reasoning

In InnoDB every table is a clustered index on its primary key: the leaf pages hold the full rows, physically ordered by the key. Range scans on the primary key read rows in sequence. Inserts with a random key such as a UUID scatter writes across pages, which is why auto-increment keys load faster.

A secondary, non-clustered, index stores the indexed columns plus the primary key value at its leaves. A lookup finds the key in the secondary index, then looks the row up in the clustered index. Two B-tree descents instead of one.

PostgreSQL has no clustered index in this sense; its heap is unordered and every index is secondary, with `CLUSTER` as a one-time physical reorder. Warehouses replace both with sort or cluster keys that order the storage without a tree.

The choice of clustered key is a layout decision: it decides which range scans are sequential and how inserts land. Pick the key by which the data is most often read in ranges, usually time or a hot identifier.

The reasoning

A query `SELECT amount FROM orders WHERE customer_id = 42` with an index on `customer_id` finds the matching entries quickly, then fetches each row from the table to read `amount`. For 10,000 matching orders that is 10,000 random reads.

An index on `(customer_id) INCLUDE (amount)`, or a composite on `(customer_id, amount)`, holds `amount` in the leaf. The engine reads the index entries and is done. The plan shows an index-only scan.

The order of key columns still matters: filters and ordering use the leading columns, included columns only need to be present. PostgreSQL's `INCLUDE` clause, SQL Server's `INCLUDE`, and MySQL's composite keys all express this.

Each included column grows the index and must be maintained on every write. Covering a wide query means copying much of the table, which is why covering indexes are built for a few hot queries, not for every report.

The reasoning

A view is a name for a `SELECT`. It gives a stable interface, hides a complex join, and applies security filters, and it costs nothing to create. Reading it costs what the query costs, every time, which is fine for a cheap query and ruinous for a 20-table join a dashboard runs every minute.

A materialized view runs that query once and keeps the rows. Reads are plain table scans or lookups. The cost moves to refresh: PostgreSQL's `REFRESH MATERIALIZED VIEW` rebuilds it, while Snowflake and BigQuery maintain theirs incrementally for supported query shapes. dbt's materialized view materialization wraps whichever the engine offers.

The trade is freshness against cost. A view is never stale; a materialized view is stale until the next refresh and costs storage. Pick the view when the query is cheap or must be current, and the materialized view when the same expensive query is read many times between changes.

A materialized view is a special case of the pre-aggregate: a derived table the engine knows how to keep in sync. The general case is a dbt table model refreshed by the pipeline.

The reasoning

`EXPLAIN` prints the plan; `EXPLAIN ANALYZE` runs it and prints actual rows and time per step. The plan is a tree: scans at the leaves, joins and aggregations above them, the final output at the root. Each node carries an estimated row count and cost.

A sequential scan reads pages in order, which disks and object stores do fast. An index scan descends the B-tree per lookup and then fetches rows, which is fast for few rows and slow for many, because each fetch is a random read. Engines switch to the scan somewhere around 5 to 20 percent selectivity in a row store.

The estimates come from statistics: row counts, distinct values, histograms. When statistics are stale the estimate is wrong, the engine picks the wrong join or the wrong scan, and the fix is `ANALYZE` or its equivalent, not a hint.

In a columnar warehouse the vocabulary changes: partition pruning and file skipping replace index scans, and a full scan of a few columns is normal and cheap. The plan shows bytes scanned and partitions read instead of index names.

The reasoning

A table partitioned by `event_date` stores each day apart. `WHERE event_date = '2026-11-01'` reads one day. `WHERE event_date BETWEEN ... AND ...` reads a range. The engine decides from metadata before touching data, which is why the bytes-scanned estimate drops before the query runs.

`WHERE DATE(event_ts) = '2026-11-01'` on a table partitioned by `event_ts` does not prune in BigQuery, because the filter is on a function of the column. Its docs say to filter on the partitioning column itself. `WHERE event_date = (SELECT MAX(...))` often cannot prune at plan time either. `CAST(event_date AS STRING) = ...` defeats it the same way a function defeats an index.

Pruning on a join needs the partition filter on the partitioned table's own column, or dynamic partition pruning, which Spark and the warehouses do only in some shapes. A filter applied after the join does not help the scan.

The partition column should be the one most queries filter on, usually the event date, at a granularity that keeps partitions in the hundreds of MB. Over-partitioning produces the small-files problem; under-partitioning prunes nothing.

The reasoning

A CTE makes a long query readable: name each step, read top to bottom. A subquery nests the same logic inside `FROM` or `WHERE`. Logically they are equivalent, and a recursive CTE is the one thing only a CTE can express.

Whether a CTE is a cost depends on the engine. PostgreSQL inlines a CTE referenced once since version 12 and materializes one referenced more than once or marked `MATERIALIZED`. Snowflake and BigQuery may recompute a CTE each time it is referenced. A CTE read four times can mean four scans.

A temporary table breaks the statement in two. You write the intermediate result once, the engine collects statistics on it, and later queries read it like any table. It costs a write and lives only for the session, which makes it the choice for a result reused across several statements or too large to recompute.

In pipeline code the usual order is: CTEs for readability inside one model, then a temporary or staging table when a result feeds more than one statement. A persisted model comes when more than one pipeline needs it.

The reasoning

The tell is a reference to the outer table inside the subquery. The subquery's result depends on which outer row is being evaluated, so there is no single result to compute up front.

Planners rewrite the common shapes. `EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)` becomes a semi join. A correlated aggregate often becomes a group-by joined back. When the rewrite works, the plan is as fast as the join you would have written.

When it does not, the plan shows a nested loop with the subquery as the inner side, executed once per outer row. On a million outer rows that is a million scans. The rewrite by hand is the join or the window function: `AVG(amount) OVER (PARTITION BY customer_id)` gives each row its customer's average in one pass.

Correlated subqueries in `SELECT` are the usual culprit in slow reports: one lookup per row for a label or a latest value. A join or a window would read each table once.

The reasoning

The engine starts by building the row set: the `FROM` tables joined on their conditions. `WHERE` then removes rows, one at a time, before any grouping. That is why a filter on an aggregate cannot go in `WHERE`.

`GROUP BY` collapses rows into groups, and `HAVING` filters groups, so it can reference aggregates. Window functions are computed after grouping and filtering, over the rows that remain, which is why a window cannot be filtered in `WHERE` without a subquery or `QUALIFY`.

`SELECT` computes expressions and names them. `DISTINCT` deduplicates the selected rows. `ORDER BY` sorts and may use aliases. `LIMIT` cuts the sorted result, which is why `LIMIT` without `ORDER BY` returns an arbitrary subset.

This is the logical order; the planner may execute differently as long as the result is the same. The logical order is what decides which names are visible where.

The reasoning

The `ON` condition is the key: it says which source row corresponds to which target row. For each target row, the engine finds matching source rows and runs the matched clause; for each source row with no target match, the not-matched clause. One statement, one transaction.

Each clause can carry an extra condition, `WHEN MATCHED AND source.updated_at > target.updated_at THEN UPDATE`, which is how a merge skips stale updates. `WHEN NOT MATCHED BY SOURCE THEN DELETE` turns a merge into a full sync that removes rows absent from the source.

The classic failure is a non-unique source: two source rows for one target key. Standard SQL raises an error; some engines update twice or pick one, which makes results depend on order. Deduplicate the source first, keeping the latest by a version or timestamp.

A merge is idempotent only when the `ON` condition is the row's identity and the actions produce the same state on a repeat. Matching on a partial key fans out; inserting on every run with a loose condition duplicates. On a lakehouse, the merge rewrites the files that hold matched rows, so clustering the target on the key keeps it cheap.

The reasoning

In a normal join both sides are computed independently and then matched. The subquery on the right cannot use `c.id`, so "the last 3 orders for this customer" has no place to say which customer. A lateral subquery is correlated by design and runs per left row.

That gives per-row operations: top-N per group with a `LIMIT` inside, calling a table-valued function with the row's values, or unnesting an array column into rows. PostgreSQL spells it `LATERAL`; SQL Server spells it `CROSS APPLY` and `OUTER APPLY` for the left-join form; BigQuery's `UNNEST` and Snowflake's `FLATTEN` are the same idea for arrays.

The cost is per-row evaluation. With an index on `(customer_id, created_at)` each lateral lookup is cheap. Without one, it is a nested loop of scans, and a window function with `ROW_NUMBER` over the whole orders table is faster.

`CROSS JOIN LATERAL` drops left rows with an empty right result; `LEFT JOIN LATERAL ... ON true` keeps them, which is the form to use when some customers have no orders.

The reasoning

The table appears twice in `FROM` under different names. Every reference must say which copy it means, or the engine cannot tell `id` from `id`. `ON e.manager_id = m.id` reads the manager's row for each employee.

For comparisons within a group, the condition pairs rows by a shared key and an inequality. `ON a.customer_id = b.customer_id AND a.order_date < b.order_date` pairs each order with every later order of the same customer. Adding `AND a.id <> b.id` avoids the row matching itself on an equality key.

A self join walks one level. A hierarchy of unknown depth needs a recursive CTE, which is a self join repeated until no new rows appear. Sequential comparisons such as "the previous order" are usually cheaper with `LAG` than with a self join plus a `MAX` to pick the nearest.

Row counts multiply: a self join on an inequality within a customer with 1,000 orders produces about 500,000 pairs. Window functions exist to avoid that.

The reasoning

A window function computes across a set of rows related to the current row without collapsing them. `PARTITION BY` splits the rows into groups, `ORDER BY` orders within each group, and the function reads the neighbourhood.

`LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date)` gives each order the previous order's amount, or null for the first. A third argument supplies a default. `LEAD` is the same looking forward. Differences, gaps and status transitions are all `LAG` compared to the current row.

A running total adds every row from the partition's start to the current row. That needs a frame. `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` means exactly those rows. The default frame when `ORDER BY` is present is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Under it, rows that tie on the order column are peers, and all receive the total through the last peer.

So two orders on the same day get the same running total under the default frame and different totals under `ROWS`. Moving averages use `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`, and the same tie rule applies.

The reasoning

Scalar functions live in `SELECT`, `WHERE` and anywhere an expression goes. They see only the current row. Wrapping an indexed column in one in a `WHERE` clause is what defeats the index.

Aggregates need a grouping. Without `GROUP BY` the whole result is one group and the query returns one row. With it, one row per distinct combination of the grouped columns. A non-aggregated, non-grouped column in `SELECT` is an error, because the engine has no rule to pick one value for the group.

Window functions are aggregates or rankings applied over a window, written with `OVER`. They do not group; every input row comes out with its own computed value. That is how a row shows its own amount next to its customer's total, or its rank among them.

The same name can be both: `SUM` with `GROUP BY` is an aggregate, `SUM` with `OVER` is a window function. Ranking functions such as `ROW_NUMBER` exist only as windows.

The reasoning

`NULL` means unknown, not zero and not empty. Asking whether an unknown equals 5 has no answer, so the comparison is unknown. `NOT unknown` is still unknown, which is why `WHERE col <> 'x'` drops the nulls as well as the x rows. Use `IS NULL` and `IS DISTINCT FROM` to test for null and to compare null-safely.

Aggregates skip nulls: `COUNT(col)` counts non-null values, `AVG` averages the non-null ones, and `SUM` of an all-null column is `NULL`, not 0. `COUNT(*)` counts rows regardless. Grouping and `DISTINCT` treat nulls as one group, under distinctness rather than equality.

`COALESCE(discount, 0)` substitutes a default so arithmetic does not go null. `NULLIF(denominator, 0)` turns a zero into a null so `amount / NULLIF(qty, 0)` yields null instead of an error. Together they state the business rule in the query instead of hiding it.

In joins, a null key never matches, even another null. Rows with a null foreign key disappear from inner joins and show nulls on the right of left joins, which is why dimensions carry an explicit unknown member.

The reasoning

Data definition language creates and changes tables, views, indexes and schemas. In many engines DDL commits implicitly, so a `CREATE TABLE` inside a transaction ends it, which matters for migrations.

Data manipulation language reads and writes rows. These are the statements a pipeline runs, and they take part in transactions, so a failed `INSERT` can roll back.

Data control language grants and revokes privileges on objects to roles. Row-level security policies and column masking sit beside it in modern engines.

Transaction control language opens, commits and rolls back transactions and sets savepoints. `TRUNCATE` is the classic trick question: it empties a table like a `DELETE` but is DDL, usually faster, often not rollbackable, and does not fire row triggers.

The reasoning

`DELETE FROM orders WHERE status = 'test'` is a row operation. The engine finds each row, writes it to the transaction log, and marks it deleted. It is the only one of the three that can be selective, and the only one that respects row-level triggers and foreign-key checks per row.

`TRUNCATE TABLE orders` does not visit rows. It releases the pages and starts the table empty, which is why it takes milliseconds on a billion rows. PostgreSQL makes it transactional; MySQL and SQL Server treat it as DDL that commits.

`DROP TABLE orders` removes the definition. The data, the indexes, the constraints and the grants go with it. Recreating it means recreating all of them.

In a pipeline, `TRUNCATE` then load is the full-refresh pattern for staging tables. `DELETE WHERE` on a date range is the partition-replace pattern for row stores that have no partition overwrite.

The reasoning

With `CHAR(10)`, the value `'abc'` is stored and returned as `'abc '` in many engines. Comparisons usually ignore trailing spaces, but string functions, concatenation and some joins across types do not. That produces the famous mismatch where `'abc'` from one table fails to equal `'abc '` from another.

`VARCHAR(10)` stores `'abc'` as three characters plus a length. It costs a little more per value and saves everything else. Modern engines optimise it well enough that fixed length buys nothing measurable.

Warehouses differ: Snowflake's `VARCHAR` has a large default limit and `CHAR` is a padded alias, BigQuery has only `STRING`, and Spark treats `CHAR` with padding semantics since 3.1. The safest column for identifiers and text is a variable-length string.

The length limit is a constraint, not a storage optimisation. Set it where the business has a rule, such as a 3-character currency code, and leave it generous otherwise.

The reasoning

A missing discount, a customer with no phone number, a date not yet known: these are `NULL`. The database records that there is no value, not that the value is zero or blank.

Arithmetic with `NULL` yields `NULL`: `price * NULL` is `NULL`. Comparisons yield unknown, which `WHERE` treats as false. `COUNT(col)` skips nulls and `COUNT(*)` does not. String concatenation with `NULL` yields `NULL` in standard SQL, though some engines treat it as empty.

The empty string is a real string. `'' = ''` is true; `'' IS NULL` is false, except in Oracle, where an empty string is stored as `NULL`. Zero is a real number: a balance of 0 is known to be nothing, which is different from unknown.

In a pipeline, nulls are classified: acceptable where the field is optional, invalid where it is required, and converted to an explicit default only when the business meaning is clear.

The reasoning

First the rows are assembled from the tables and their joins. Then `WHERE` filters rows. Then rows are grouped, and `HAVING` filters groups using aggregates.

Then the `SELECT` list is computed. Window functions run here, over the filtered and grouped rows, which is why they cannot be referenced in `WHERE` or `HAVING` without a subquery or `QUALIFY`.

`DISTINCT` removes duplicate output rows. `ORDER BY` sorts the output, and may use `SELECT` aliases because they now exist. `LIMIT` takes the first rows of the sorted result, and without `ORDER BY` takes an arbitrary set.

Engines optimise freely underneath, pushing filters down and reordering joins, but the names visible in each clause follow this logical order.

The reasoning

`CREATE VIEW active_customers AS SELECT ... FROM customers WHERE status = 'active'` saves the query under a name. Reading `active_customers` substitutes the query and runs it. Nothing is cached.

Views are the abstraction layer: a consumer reads `orders_v` while the underlying table is renamed or split. They are the security layer: grant a role the view that hides the salary column. And they are documentation: the join logic lives once, in the view.

The cost is repeated computation and opaque plans. A dashboard that reads a view built on three other views runs the whole stack every time. The planner may not optimise across the layers as well as it would a single query.

dbt's `view` materialization is this object; its `table` materialization is the same query persisted. The rule of thumb in the dbt docs is views until the read cost justifies a table.

The reasoning

Procedures were the original ETL: a nightly job that called a procedure which truncated staging, loaded, transformed and merged, all inside the database. They run close to the data and need no external scheduler for the SQL itself.

A function is narrower: it takes arguments, returns a scalar or a table, and can be called in `SELECT` or `WHERE`. It cannot usually manage transactions. Scalar functions in predicates are also a classic cause of slow queries, because the engine calls them per row and cannot use indexes.

The drawbacks of procedures are operational. Their code lives in the database, outside git unless exported, with no unit tests, no lineage, and deployment by running scripts in order. A bug is debugged by reading the procedure body in a GUI.

The modern pattern keeps SQL as files: models with explicit dependencies, tests, documentation and CI. Procedures remain for operations the engine must do in one place, such as Snowflake tasks or BigQuery scripting for administration.

The reasoning

A join answers "show me orders with their customer's name": both tables' columns end up in the output. A subquery answers "show me customers who have orders" without needing anything from orders in the output.

Written as a join, the existence question duplicates each customer once per order and needs `DISTINCT`. Written as `WHERE EXISTS`, it returns each customer once and the planner uses a semi join, which stops at the first match. That is the case where the subquery is right.

Scalar subqueries in `SELECT`, one lookup per row, are the case where a join or a window is right. The subquery runs per row when the planner cannot rewrite it.

Derived tables in `FROM` and CTEs are subqueries used as tables, and they are joined like any other. The choice between them is readability, not performance, in most engines.

The reasoning

`WITH recent AS (SELECT ...) SELECT ... FROM recent` defines `recent` for that statement only. The next statement cannot see it. The planner may inline it into the main query or materialize it, depending on the engine and how many times it is referenced.

`CREATE TEMP TABLE recent AS SELECT ...` writes the rows to session-scoped storage. Later statements query it, join it, index it. The engine gathers statistics on it, so a plan over a temp table is often better than over a CTE whose size the planner guessed.

Temp tables cost a write and disappear at session end, which in a connection-pooled pipeline may be sooner than expected. CTEs cost nothing to define and are the readable default.

In warehouse pipelines the temp table's role is usually taken by a staging table or an ephemeral dbt model, and the CTE's role by the model's own SQL.

The reasoning

`NOT NULL` rejects a missing value. `UNIQUE` rejects a duplicate in a column or set of columns. `PRIMARY KEY` is unique plus not null, one per table. `FOREIGN KEY` requires the value to exist in a parent table. `CHECK` requires a condition, such as `amount >= 0`. `DEFAULT` supplies a value when none is given.

In PostgreSQL or MySQL these are the first line of data quality: a bad row is refused at the moment it is written, and the error names the constraint.

Warehouse documentation is explicit that primary, unique and foreign keys are informational: they help the optimizer and document intent, but a duplicate key loads without error. BigQuery enforces `NOT NULL` only; Snowflake enforces `NOT NULL` and, on hybrid tables, keys.

So a pipeline replaces the missing enforcement with assertions after the load: uniqueness on the grain, referential checks to dimensions, accepted values. dbt's generic tests are exactly these constraints, run as queries.