| Structured, semi-structured, unstructured | Structure lives in the table / in each record / nowhere | Kinds of data |
| Table | Rows that all share one set of typed columns | Tables |
| Primary key | Column(s) that pick out exactly one row | Tables |
| Natural, surrogate, composite key | Real-world value / generated id / several columns together | Tables |
| Denormalise | Copy a fact on purpose to save joins, and keep the copies in step | Tables |
| Foreign key | A column that must match a key in another table | Keys |
| ON DELETE CASCADE / SET NULL | Delete the child rows too / keep them with an empty key | ON DELETE actions |
| Many-to-many | Needs a junction table holding both keys | Relationships |
NOT NULL UNIQUE CHECK DEFAULT | Rules declared once that stop bad rows | Types and constraints |
| INTEGER, BIGINT | 4 bytes to ±2.1 billion; 8 bytes to ±9.2 × 10¹⁸ | Data types |
SELECT … FROM … WHERE … | Describe the result; the database picks how | SELECT and WHERE |
| DDL, DML, DQL, DCL | Define, change, query, control | SELECT and WHERE |
| INNER, LEFT, RIGHT, FULL JOIN | Differ only in which unmatched rows they keep | Joins |
| Fan-out | A join repeats rows, so sums over the repeated side grow | Joins |
| Semi join, anti join | EXISTS: rows with a match, once / NOT EXISTS: rows with none | Cross, self, semi and anti joins |
GROUP BY with COUNT SUM AVG | One row per group | Grouping |
WHERE vs HAVING | Filter rows before grouping / filter groups after | Grouping |
UNION INTERSECT EXCEPT | Combine result sets; `UNION ALL` keeps duplicates | Grouping |
| ROLLUP, CUBE, GROUPING SETS | Subtotals by hierarchy / every combination / a chosen list | ROLLUP and CUBE |
| Logical order | FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT | Logical query order |
| NULL | Unknown: `= NULL` is never true, use `IS NULL` | NULL |
COALESCE(x, 0) | First value that is not NULL | NULL |
| Update, insert, delete anomaly | A fact stored in many rows lets the copies disagree | Anomalies |
| Normalisation | Each kind of thing in its own table, pointed at by key | Normalisation |
| Expand, migrate, contract | Add the new shape, backfill, then remove the old | Schema migrations |
| 1NF, 2NF, 3NF | One value per cell / no part-key facts / no facts about non-keys | Normal forms |
| Natural, surrogate, composite key | Real-world value / generated id / several columns together | Normal forms |
| Denormalise | Copy a fact on purpose to save joins, and keep the copies in step | Normal forms |
| View, index, stored procedure, trigger | Objects that live beside the data | Database objects |
| Function, procedure, trigger | Returns a value / run with CALL / fires on a row change | Functions, procedures, triggers |
| Subquery | A query inside another, in WHERE, FROM or SELECT | Subqueries |
WITH step AS (…) | Common table expression: name each step | Common table expressions |
WITH RECURSIVE | Walk a hierarchy, such as manager chains | Common table expressions |
… OVER (PARTITION BY … ORDER BY …) | Window function: compute per group, keep every row | Window functions |
| Running total | `SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)` | Running totals |
LAG LEAD | Value from the row before / after | Running totals |
| ROWS vs RANGE | Frame by physical rows / by value, ties together (the default) | ROWS, RANGE and GROUPS |
ROW_NUMBER RANK DENSE_RANK | Ties: 1,2,3 / 1,1,3 / 1,1,2 | Ranking |
| View vs materialised view | Stores the question / stores the answer, refreshed | Views |
| Secure view | Snowflake view that hides its definition and blocks filter leaks | Views in practice |
MERGE | Update what matches, insert what is new, in one statement | MERGE |
| INSERT … ON CONFLICT | PostgreSQL and SQLite upsert on a unique key | Upsert syntax |
| Full table scan | Read every row to find a few | Full table scans |
| B-tree index | Each page narrows the search by hundreds; a few reads find a row | B-tree indexes |
| Clustered index | The table itself stored in key order; one per table | Index types |
| Seek vs scan | An index wins only when the query wants a small share of rows | Seek or scan |
EXPLAIN ANALYZE | Show the plan the database actually ran, with times | Query plans |
| Nested loop, hash join, merge join | Lookup per row / hash the small side / walk two sorted inputs | Join algorithms |
| Composite index | Column order decides which queries it serves (leftmost first) | Composite indexes |
| Sargable | Leave the column bare: `d >= '2025-01-01'`, not `year(d) = 2025` | Composite indexes |
| Covering index, index-only scan | INCLUDE the columns a query reads; the table is not opened | Covering indexes |
| Index cost | Faster reads, slower writes, more storage | Index costs |
| Partitioning | Split by a key so queries skip what they cannot need | Partitioning |
| ACID | Atomic, consistent, isolated, durable | Transactions |
BEGIN … COMMIT / ROLLBACK | All the changes happen, or none | Transactions |
| Savepoint | Roll back part of a transaction | Transactions |
| Write-ahead log | Log record first, data pages later; replayed after a crash | Write-ahead log |
| Lost update | Two read-then-write transactions overwrite each other | Lost updates |
SELECT … FOR UPDATE | Lock the rows you will change | Lost updates |
| FOR UPDATE, SKIP LOCKED | Lock rows read until COMMIT / pass over rows others hold | Locking patterns |
| Read committed, repeatable read, serializable | Each stronger level rules out one more anomaly | Isolation levels |
| Dirty read, non-repeatable read, phantom | The anomalies the levels rule out | Isolation levels |
| Write skew | Two transactions read the same rows, write different ones, break a rule | Write skew |
| READ_COMMITTED_SNAPSHOT | SQL Server option: readers see a snapshot instead of waiting | Isolation by engine |
| MVCC | Readers see a snapshot, so they never wait for writers | MVCC |
| Deadlock | Each waits for the other; the database kills one | Deadlocks |
| Avoid deadlocks | Lock rows in the same order; keep transactions short | Deadlocks |
| Scale out | Split data across servers; joins across them get expensive | Scaling out |
| Key-value, document, wide-column, graph | Each model is cheap for one kind of question | Data models |
| Partition key | Many evenly used values spread load; a busy one makes a hot partition | Partition keys |
| Access-pattern design | List the questions first, then shape keys so each is one read | Access patterns |
| RCU, WCU | One 4 KB strongly consistent read / one 1 KB write, per second | DynamoDB capacity |
| R + W > N | Reads see the latest write when read and write sets overlap | Replication |
| Single-leader, multi-leader, leaderless | Who accepts writes: one node / each region / any node | Replication layouts |
| Strong to eventual | Consistency is a dial; session and bounded staleness sit between | Consistency levels |
| Blob, Files, Table storage | Azure objects, file shares, key-value rows | Consistency levels |
| Graph database | Stored links make each hop cheap | Graph databases |
| DynamoDB, Cosmos DB, Firestore, Bigtable | Managed NoSQL services | NoSQL services |
| Request unit (RU) | Cosmos DB cost unit: 1 RU reads one 1 KB item by id and key | Cosmos DB request units |
| OLTP vs OLAP | Few whole rows, many writes / few columns, many rows read | OLTP and OLAP |
| Columnar storage | Each column stored together, so queries read less | Columnar storage |
| Parquet | Open column file format with row groups and statistics | Columnar storage |
| Snowflake schema | Dimensions split into further tables | Columnar storage |
| Power Query, semantic model, report | Get data, model it, show it | Columnar storage |
| Bytes read | One sum over a million orders: the column store read 161× fewer bytes | Bytes read |
| Dictionary, run-length encoding | Why alike values in a column compress well | Column compression |
| Star schema | One fact table of numbers, dimensions of descriptions around it | Star schema |
| Conformed dimension | One dimension shared by several fact tables | Fact and dimension types |
| Fact, dimension, measure, grain | Number, description, what you add up, what one row means | Facts and dimensions |
| SCD type 1 / type 2 | Overwrite / add a row with valid-from and valid-to | Slowly changing dimensions |
| Power BI relationships | Carry filters from dimensions to facts | Power BI data models |
| Measure (DAX) | A calculation evaluated in the current filter | Power BI data models |
| Choosing a visual | Trend: line; compare: bar; part of whole: stacked bar; relation: scatter | Data visualisations |
| Warehouse, lake, lakehouse | Managed tables / cheap open files / files plus a transaction log | Warehouse, lake, lakehouse |
| Schema on write vs on read | Check on load / interpret at query time | Warehouse, lake, lakehouse |
| Small files | Compact them, or every query pays to open thousands | Warehouse, lake, lakehouse |
| Separate storage and compute | Many clusters of any size share one copy | Storage and compute |
| Delta Lake, Apache Iceberg | Table = what its log of commits says now | Delta Lake and Iceberg |
| Schema enforcement, evolution | Reject a mismatched write / add its new columns on request | Schema enforcement |
| Snowflake layers | Cloud services, virtual warehouses, storage | Snowflake architecture |
| Time Travel | Read, restore or clone a past version | Time Travel |
| Transient table | Snowflake table with no Fail-safe and at most 1 day of Time Travel | Snowflake table types |
| Microsoft Fabric, OneLake | Warehouse, Spark and Power BI on one lake | Microsoft Fabric |
| Unity Catalog | `catalog.schema.table` and one place of control | Unity Catalog |
| Redshift, Synapse, BigQuery, Athena | Cloud analytics services | Analytics services |
| DISTKEY, SORTKEY | Redshift: which slice holds a row / the order rows are stored in | Redshift keys |
| ETL vs ELT | Transform before loading / load raw, transform in the warehouse | ETL and ELT |
| Bronze, silver, gold | As it came / cleaned / shaped for use | Medallion architecture |
| Watermark | Load only rows changed since the last run | Incremental loads |
| Idempotent load | Re-running gives the same result: `MERGE` or overwrite a partition | Idempotent loads |
| DAG | Pipeline graph: each task starts when its inputs succeed | Orchestration |
| Airflow, Data Factory, Step Functions | Orchestrators | Orchestration |
| catchup, {{ ds }} | Run every missed interval / the run’s logical date | An Airflow DAG |
dbt model, ref() , test | SQL built in dependency order, checked after | dbt |
| is_incremental() | dbt: true once the table exists, so only new rows are selected | dbt incremental models |
| Auto Loader, Snowpipe | Ingestion that remembers which files it loaded | File ingestion |
| Glue, Data Factory, Dataflow | Managed pipeline services | Pipeline services |
| DPU, job bookmark | Glue: 4 vCPU and 16 GB / remembers what a job has processed | AWS Glue |
| Quality dimensions | Complete, valid, unique, consistent, timely, accurate | Data quality |
| Data contract | Agreed schema and rules between producer and consumer | Data quality |
| Failed check | Record it, drop the row, or stop the pipeline | Quality checks |
| Lineage | Each column back to its source and forward to its users | Data lineage |
| Data catalogue | What data exists, what it means, who owns it | Data catalogues |
GRANT SELECT ON … TO group | Least access, to groups, on the object and its containers | Access control |
| Row filter, column mask | Different rows and values per user from one table | Row and column security |
| Pseudonymisation vs anonymisation | Replaced but re-linkable / no longer about an identifiable person | Masking and anonymising |
| In transit, at rest | TLS / storage encryption; key holder holds the data | Encryption |
| Envelope encryption | Data encrypted by a data key, itself encrypted by a KMS key | Envelope encryption |
| Right to erasure | Purge history, copies and backups too, not just the row | Privacy and retention |
| Controller, processor | Decides why and how data is processed / processes on its instructions | GDPR in numbers |
Nothing matches. Try a shorter word.