Data and Databases reference

155 commands, numbers and terms from the 14 lessons, one line each. The right-hand column opens the page that teaches it.

1Data and tables

Structured, semi-structured, unstructuredStructure lives in the table / in each record / nowhereKinds of data
TableRows that all share one set of typed columnsTables
Primary keyColumn(s) that pick out exactly one rowTables
Natural, surrogate, composite keyReal-world value / generated id / several columns togetherTables
DenormaliseCopy a fact on purpose to save joins, and keep the copies in stepTables
Foreign keyA column that must match a key in another tableKeys
ON DELETE CASCADE / SET NULLDelete the child rows too / keep them with an empty keyON DELETE actions
Many-to-manyNeeds a junction table holding both keysRelationships
NOT NULL UNIQUE CHECK DEFAULTRules declared once that stop bad rowsTypes and constraints
INTEGER, BIGINT4 bytes to ±2.1 billion; 8 bytes to ±9.2 × 10¹⁸Data types

2SQL basics

SELECT … FROM … WHERE …Describe the result; the database picks howSELECT and WHERE
DDL, DML, DQL, DCLDefine, change, query, controlSELECT and WHERE
INNER, LEFT, RIGHT, FULL JOINDiffer only in which unmatched rows they keepJoins
Fan-outA join repeats rows, so sums over the repeated side growJoins
Semi join, anti joinEXISTS: rows with a match, once / NOT EXISTS: rows with noneCross, self, semi and anti joins
GROUP BY with COUNT SUM AVGOne row per groupGrouping
WHERE vs HAVINGFilter rows before grouping / filter groups afterGrouping
UNION INTERSECT EXCEPTCombine result sets; `UNION ALL` keeps duplicatesGrouping
ROLLUP, CUBE, GROUPING SETSSubtotals by hierarchy / every combination / a chosen listROLLUP and CUBE
Logical orderFROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMITLogical query order
NULLUnknown: `= NULL` is never true, use `IS NULL`NULL
COALESCE(x, 0)First value that is not NULLNULL

3Designing tables

Update, insert, delete anomalyA fact stored in many rows lets the copies disagreeAnomalies
NormalisationEach kind of thing in its own table, pointed at by keyNormalisation
Expand, migrate, contractAdd the new shape, backfill, then remove the oldSchema migrations
1NF, 2NF, 3NFOne value per cell / no part-key facts / no facts about non-keysNormal forms
Natural, surrogate, composite keyReal-world value / generated id / several columns togetherNormal forms
DenormaliseCopy a fact on purpose to save joins, and keep the copies in stepNormal forms
View, index, stored procedure, triggerObjects that live beside the dataDatabase objects
Function, procedure, triggerReturns a value / run with CALL / fires on a row changeFunctions, procedures, triggers

4SQL for analysis

SubqueryA query inside another, in WHERE, FROM or SELECTSubqueries
WITH step AS (…)Common table expression: name each stepCommon table expressions
WITH RECURSIVEWalk a hierarchy, such as manager chainsCommon table expressions
… OVER (PARTITION BY … ORDER BY …)Window function: compute per group, keep every rowWindow functions
Running total`SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)`Running totals
LAG LEADValue from the row before / afterRunning totals
ROWS vs RANGEFrame by physical rows / by value, ties together (the default)ROWS, RANGE and GROUPS
ROW_NUMBER RANK DENSE_RANKTies: 1,2,3 / 1,1,3 / 1,1,2Ranking
View vs materialised viewStores the question / stores the answer, refreshedViews
Secure viewSnowflake view that hides its definition and blocks filter leaksViews in practice
MERGEUpdate what matches, insert what is new, in one statementMERGE
INSERT … ON CONFLICTPostgreSQL and SQLite upsert on a unique keyUpsert syntax

5Indexes and how a query runs

Full table scanRead every row to find a fewFull table scans
B-tree indexEach page narrows the search by hundreds; a few reads find a rowB-tree indexes
Clustered indexThe table itself stored in key order; one per tableIndex types
Seek vs scanAn index wins only when the query wants a small share of rowsSeek or scan
EXPLAIN ANALYZEShow the plan the database actually ran, with timesQuery plans
Nested loop, hash join, merge joinLookup per row / hash the small side / walk two sorted inputsJoin algorithms
Composite indexColumn order decides which queries it serves (leftmost first)Composite indexes
SargableLeave the column bare: `d >= '2025-01-01'`, not `year(d) = 2025`Composite indexes
Covering index, index-only scanINCLUDE the columns a query reads; the table is not openedCovering indexes
Index costFaster reads, slower writes, more storageIndex costs
PartitioningSplit by a key so queries skip what they cannot needPartitioning

6Transactions

ACIDAtomic, consistent, isolated, durableTransactions
BEGIN … COMMIT / ROLLBACKAll the changes happen, or noneTransactions
SavepointRoll back part of a transactionTransactions
Write-ahead logLog record first, data pages later; replayed after a crashWrite-ahead log
Lost updateTwo read-then-write transactions overwrite each otherLost updates
SELECT … FOR UPDATELock the rows you will changeLost updates
FOR UPDATE, SKIP LOCKEDLock rows read until COMMIT / pass over rows others holdLocking patterns
Read committed, repeatable read, serializableEach stronger level rules out one more anomalyIsolation levels
Dirty read, non-repeatable read, phantomThe anomalies the levels rule outIsolation levels
Write skewTwo transactions read the same rows, write different ones, break a ruleWrite skew
READ_COMMITTED_SNAPSHOTSQL Server option: readers see a snapshot instead of waitingIsolation by engine
MVCCReaders see a snapshot, so they never wait for writersMVCC
DeadlockEach waits for the other; the database kills oneDeadlocks
Avoid deadlocksLock rows in the same order; keep transactions shortDeadlocks

7NoSQL databases

Scale outSplit data across servers; joins across them get expensiveScaling out
Key-value, document, wide-column, graphEach model is cheap for one kind of questionData models
Partition keyMany evenly used values spread load; a busy one makes a hot partitionPartition keys
Access-pattern designList the questions first, then shape keys so each is one readAccess patterns
RCU, WCUOne 4 KB strongly consistent read / one 1 KB write, per secondDynamoDB capacity
R + W > NReads see the latest write when read and write sets overlapReplication
Single-leader, multi-leader, leaderlessWho accepts writes: one node / each region / any nodeReplication layouts
Strong to eventualConsistency is a dial; session and bounded staleness sit betweenConsistency levels
Blob, Files, Table storageAzure objects, file shares, key-value rowsConsistency levels
Graph databaseStored links make each hop cheapGraph databases
DynamoDB, Cosmos DB, Firestore, BigtableManaged NoSQL servicesNoSQL services
Request unit (RU)Cosmos DB cost unit: 1 RU reads one 1 KB item by id and keyCosmos DB request units

8Data for analysis

OLTP vs OLAPFew whole rows, many writes / few columns, many rows readOLTP and OLAP
Columnar storageEach column stored together, so queries read lessColumnar storage
ParquetOpen column file format with row groups and statisticsColumnar storage
Snowflake schemaDimensions split into further tablesColumnar storage
Power Query, semantic model, reportGet data, model it, show itColumnar storage
Bytes readOne sum over a million orders: the column store read 161× fewer bytesBytes read
Dictionary, run-length encodingWhy alike values in a column compress wellColumn compression
Star schemaOne fact table of numbers, dimensions of descriptions around itStar schema
Conformed dimensionOne dimension shared by several fact tablesFact and dimension types
Fact, dimension, measure, grainNumber, description, what you add up, what one row meansFacts and dimensions
SCD type 1 / type 2Overwrite / add a row with valid-from and valid-toSlowly changing dimensions
Power BI relationshipsCarry filters from dimensions to factsPower BI data models
Measure (DAX)A calculation evaluated in the current filterPower BI data models
Choosing a visualTrend: line; compare: bar; part of whole: stacked bar; relation: scatterData visualisations

9Warehouses, lakes and lakehouses

Warehouse, lake, lakehouseManaged tables / cheap open files / files plus a transaction logWarehouse, lake, lakehouse
Schema on write vs on readCheck on load / interpret at query timeWarehouse, lake, lakehouse
Small filesCompact them, or every query pays to open thousandsWarehouse, lake, lakehouse
Separate storage and computeMany clusters of any size share one copyStorage and compute
Delta Lake, Apache IcebergTable = what its log of commits says nowDelta Lake and Iceberg
Schema enforcement, evolutionReject a mismatched write / add its new columns on requestSchema enforcement
Snowflake layersCloud services, virtual warehouses, storageSnowflake architecture
Time TravelRead, restore or clone a past versionTime Travel
Transient tableSnowflake table with no Fail-safe and at most 1 day of Time TravelSnowflake table types
Microsoft Fabric, OneLakeWarehouse, Spark and Power BI on one lakeMicrosoft Fabric
Unity Catalog`catalog.schema.table` and one place of controlUnity Catalog
Redshift, Synapse, BigQuery, AthenaCloud analytics servicesAnalytics services
DISTKEY, SORTKEYRedshift: which slice holds a row / the order rows are stored inRedshift keys

10Moving data

ETL vs ELTTransform before loading / load raw, transform in the warehouseETL and ELT
Bronze, silver, goldAs it came / cleaned / shaped for useMedallion architecture
WatermarkLoad only rows changed since the last runIncremental loads
Idempotent loadRe-running gives the same result: `MERGE` or overwrite a partitionIdempotent loads
DAGPipeline graph: each task starts when its inputs succeedOrchestration
Airflow, Data Factory, Step FunctionsOrchestratorsOrchestration
catchup, {{ ds }}Run every missed interval / the run’s logical dateAn Airflow DAG
dbt model, ref() , testSQL built in dependency order, checked afterdbt
is_incremental()dbt: true once the table exists, so only new rows are selecteddbt incremental models
Auto Loader, SnowpipeIngestion that remembers which files it loadedFile ingestion
Glue, Data Factory, DataflowManaged pipeline servicesPipeline services
DPU, job bookmarkGlue: 4 vCPU and 16 GB / remembers what a job has processedAWS Glue

11Batch processing with Spark

Driver, executor, task, partitionPlans / runs / one unit of work / one slice of dataSpark architecture
DataFrame and Spark SQLSame plan whichever you writeSpark architecture
SaltingAdd a random suffix to split a hot keySpark architecture
repartition vs coalesceFull shuffle, even sizes / merge without a shuffle, reduce onlyrepartition and coalesce
Transformation vs actionDescribes the result / runs the plan (`count`, `write`)Lazy evaluation
cache(), persist()Keep a computed DataFrame for later actions, in memory or on diskcache and persist
ShuffleRows move between machines for a group or join; starts a new stageShuffles and stages
spark.sql.shuffle.partitionsPartitions after each shuffle; default 200Spark settings
Broadcast joinCopy the small table to every executorBroadcast joins
Data skewOne oversized partition makes the stage waitData skew
Spark UIMax far above median = skew; spill = memory ran outThe Spark UI
AQERe-plans at each shuffle with real sizesAdaptive Query Execution
Job vs all-purpose computeScheduled pipeline / people working interactivelyDatabricks compute

12Streaming

Batch vs streamProcess on a schedule / moments after each eventBatch and streaming
Topic, partition, consumer groupA log split for scale; each group reads all of itEvent streams
Kafka, Kinesis, Event HubsEvent stream servicesEvent streams
Structured StreamingSpark query that runs on each micro-batch, with a checkpointEvent streams
OffsetA group's position in a partition; reset it to replayOffsets and replay
Tumbling, hopping, sliding, sessionWindow types that decide which events count togetherWindows
Event time vs processing timeWhen it happened / when it arrivedLate data and watermarks
WatermarkHow long to wait for late eventsLate data and watermarks
Exactly-onceReplayable source plus a sink that tolerates repeatsExactly-once processing
acks=all, min.insync.replicasWait for every in-sync replica / how many must be in syncKafka durability settings
Change data captureEvery committed change in a database becomes an eventChange data capture
SEQUENCE BYThe source-assigned order used to apply change eventsApplying changes

13Data quality and governance

Quality dimensionsComplete, valid, unique, consistent, timely, accurateData quality
Data contractAgreed schema and rules between producer and consumerData quality
Failed checkRecord it, drop the row, or stop the pipelineQuality checks
LineageEach column back to its source and forward to its usersData lineage
Data catalogueWhat data exists, what it means, who owns itData catalogues
GRANT SELECT ON … TO groupLeast access, to groups, on the object and its containersAccess control
Row filter, column maskDifferent rows and values per user from one tableRow and column security
Pseudonymisation vs anonymisationReplaced but re-linkable / no longer about an identifiable personMasking and anonymising
In transit, at restTLS / storage encryption; key holder holds the dataEncryption
Envelope encryptionData encrypted by a data key, itself encrypted by a KMS keyEnvelope encryption
Right to erasurePurge history, copies and backups too, not just the rowPrivacy and retention
Controller, processorDecides why and how data is processed / processes on its instructionsGDPR in numbers

NeuroqAI · Data and Databases · neuroqai.com/courses/data-and-databases/reference/