Databases & Data Science

Section 1

The one idea

If you retain nothing else, retain this. Every subsequent argument in the field is a variation on it.

A database is not a place where data is kept. Filing cabinets keep data. A database is a machine for answering questions about data without the questioner needing to know how the data is stored. That separation — between the question and the retrieval — is the entire intellectual content of the field, and it is why the discipline exists at all.

Before 1970, it did not exist. The dominant systems were navigational: IBM's IMS (hierarchical) and the CODASYL network model. To get an answer, a programmer wrote code that walked a pointer structure — start at this record, follow this link, then that one. Charles Bachman, who designed the first of these and won the 1973 Turing Award for it, titled his award lecture The Programmer as Navigator, and he meant it admiringly: the programmer steered through the data.

The consequence was brutal and is worth feeling. The access path was baked into the application code. If someone reorganised the storage — added an index, changed the ordering, split a file — every program that touched it broke. Data and the programs that read it were welded together. Asking a question nobody had anticipated meant writing a new program.

Edgar Codd, a mathematician at IBM San Jose, published A Relational Model of Data for Large Shared Data Banks in Communications of the ACM in June 1970. His opening sentence names the target directly:

The move was to represent all data as relations — mathematical sets of tuples, which is to say tables with no inherent order and no pointers — and to let users state what they wanted as a logical expression, leaving the system to figure out how to get it. This is called physical data independence, and it is the load-bearing idea.

Three things fall out of it, and each one is still generating money and arguments in 2026:

• Declarative querying. You describe the result, not the route. SELECT is not an instruction; it is a specification.
• The optimiser. Someone has to convert the specification into an actual route. That someone is a program, and it is the most sophisticated component in the system.
• Storage independence. You can change the physical layout — add indexes, re-partition, move to different hardware, switch to columnar — without rewriting the questions.

Codd's model was resisted for a decade, largely by people with working navigational systems and performance numbers to defend. The 1974 SIGMOD "Great Debate" between Codd and Bachman is the canonical confrontation. Codd won, and the reason he won is the reason to care: the value of being able to ask an unanticipated question grows without limit, and the cost of the optimiser is paid once by the vendor.

Section 2

How a database actually works

Five mechanisms. Understand these and most production surprises stop being surprising.

A relational database is not one thing; it is a stack of five machines that mostly ignore each other. Nearly every performance mystery, outage, and vendor argument you will encounter lives in one of these five, and knowing which one is most of the diagnostic work.

2.1 The optimiser, and why it is the source of most mystery

In 1979 Patricia Selinger and colleagues at IBM published Access Path Selection in a Relational Database Management System — the System R optimiser paper. It is arguably the most practically consequential systems paper of the era, because it is what made Codd's idea viable rather than merely elegant.

The problem it solved: a query joining five tables has hundreds of possible execution orders, and the difference between the best and worst is routinely a factor of thousands. Selinger's answer was a cost-based optimiser — estimate the cost of candidate plans using statistics about the data, and pick the cheapest. Dynamic programming prunes the search.

Now the consequence, which is the thing to actually carry with you. The optimiser's decisions depend on estimates, and the estimates are frequently wrong. It guesses how many rows a filter will return. If the guess is off by 100×, the plan is wrong, and the query does not fail — it just becomes slow, sometimes catastrophically, sometimes overnight, with no code change. Stonebraker and Pavlo, reviewing fifty years of the field in 2024, are blunt: "The optimizer remains the hardest part of building a DBMS."

This is why databases have a reputation for unpredictability, and why it is not fixable by buying a bigger machine. It is intrinsic to the bargain of declarative querying: you handed over control of the route in exchange for not having to think about it, and occasionally the system chooses badly on your behalf.

What a plan looks like

You do not need to write these. You need to be able to look at one and see the shape of the problem, which is a genuinely learnable skill and one of the highest-leverage things on this page.

-- The question, stated declaratively. Note that nothing here says HOW.
SELECT c.country, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.placed_at >= '2026-01-01'
GROUP BY c.country;

-- What the optimiser decided to actually do (Postgres, abbreviated):
HashAggregate  (cost=18422 rows=41)
  Group Key: c.country
  -> Hash Join  (cost=17903 rows=284119)        ← join strategy: hash
       Hash Cond: (o.customer_id = c.id)
       -> Index Scan using orders_placed_at_idx on orders o
            Index Cond: (placed_at >= '2026-01-01')   ← index used. good.
            rows=284119  actual rows=1904322          ← ESTIMATE OFF BY 6.7×
       -> Hash  (rows=51204)
            -> Seq Scan on customers c          ← whole table into RAM

Three things are legible here without any expertise. The filter on placed_at used an index, which is good. The customers table was read in full and hashed into memory, which is fine at 51,000 rows and would not be at 51 million. And the estimate was wrong by nearly seven-fold — the optimiser planned for 284,000 rows and got 1.9 million. That gap is the thing that will eventually bite: at some point the hash table no longer fits in memory, it spills to disk, and the query time goes up by an order of magnitude with no warning.

2.2 Indexes: why the data structure decides which questions are cheap

Rudolf Bayer and Edward McCreight published the B-tree in 1972 (Organization and Maintenance of Large Ordered Indices, Acta Informatica). Fifty-four years later it is still the default index in essentially every relational database, which is unusual longevity for anything in computing and worth a moment's respect.

The mechanism, in one paragraph: a B-tree keeps keys in sorted order in a shallow, wide tree. Finding a value takes a number of steps proportional to the logarithm of the table size — so going from a million rows to a billion rows adds roughly two or three disk reads, not a thousand times more work. That logarithm is the whole trick.

2.3 Transactions: the guarantee, and the fine print

Jim Gray's 1981 paper The Transaction Concept: Virtues and Limitations formalised the idea; Theo Härder and Andreas Reuter coined the acronym ACID in 1983. It means:

• Atomicity — all of a change happens, or none of it. No half-transfers.
• Consistency — declared rules (constraints) hold before and after.
• Isolation — concurrent transactions do not corrupt each other's view.
• Durability — once committed, it survives a crash.

Atomicity and durability are close to absolute in mature systems. Isolation is where the honesty lives, and where you should push.

Perfect isolation — serializable, meaning the result is identical to running the transactions one at a time — is expensive. So databases offer weaker levels that are faster. In 1995 Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil and Patrick O'Neil published A Critique of ANSI SQL Isolation Levels, showing that the standard's definitions were ambiguous and did not describe what real systems actually did.

2.4 The write-ahead log, and why writes are the hard side

Durability is achieved by writing the intended change to an append-only log before modifying the data itself. If the machine dies, the log is replayed on restart. The canonical treatment is ARIES (C. Mohan et al., IBM, ACM TODS 1992).

inference The advisory consequence: the log is a serialised bottleneck, and it is why read scaling and write scaling are completely different problems. Adding read capacity is easy — copy the data, serve from replicas. Adding write capacity means either a bigger single machine, or splitting the data across machines and giving up the ability to transact across the split. Almost every distributed-database war story is a version of that second sentence.

Section 3

Data modelling — the load-bearing layer

The discipline underneath both database engineering and data science. It is where most expensive failures are actually born, and it is the one nobody owns.

You asked whether databases become easy if you are a good data scientist. The honest answer is that the question has the layering wrong, and this section is why.

Between "we have a database" and "we can answer questions" sits a decision nobody thinks of as technical: what are the things, what is true about them, and at what level of detail do we record it? This is data modelling. It is not statistics and it is not engineering. It is closer to ontology, and it is done — well or badly, explicitly or by accident — before either discipline can do anything useful.

3.1 Grain: the single most consequential decision

Grain means: what does one row represent? It sounds trivial. It is not, and getting it wrong is unrecoverable without a rebuild.

Consider a consulting firm recording client work. One row per engagement? Per invoice? Per consultant-day? Per timesheet entry? Each choice makes an entire class of question answerable and another class permanently unanswerable. If you record one row per invoice, you cannot ever ask about utilisation by consultant, because the information was never captured — no amount of analytical sophistication recovers it. The data scientist arrives two years later, is asked about utilisation, and produces an estimate. The estimate is presented with confidence intervals. The confidence intervals are meaningless, because the uncertainty is not statistical — it is that the quantity was never measured.

3.2 Normalisation: what it actually prevents

Codd's normal forms (1970–74) are widely taught as tidiness rules. They are not. They are a defence against a specific class of corruption: update anomalies — states in which the database can hold two contradictory answers to the same question.

The underlying concept is the functional dependency: attribute B is functionally dependent on A if knowing A determines B. Knowing an employee ID determines their date of birth. Normalisation says, roughly: every fact should be stored in exactly one place, determined by the key of the table it lives in. Third normal form is often summarised as — every non-key attribute depends on the key, the whole key, and nothing but the key.

-- UNNORMALISED. One row per order line; client data repeated on every line.
order_id | line | product   | client_name    | client_city | client_vat
---------+------+-----------+----------------+-------------+-------------
   4471  |   1  | Advisory  | Nordisk A/S    | Aarhus      | DK18475902
   4471  |   2  | Workshop  | Nordisk A/S    | Aarhus      | DK18475902
   4488  |   1  | Advisory  | Nordisk AS     | Århus       | DK18475902
                              ^^^^^^^^^^^     ^^^^^  same client — which is true?

-- The three anomalies this creates:
--   UPDATE: client moves city — you must change every row. Miss one and the
--           database holds two contradictory cities, neither flagged wrong.
--   INSERT: you cannot record a new client until they place an order. The fact
--           "this client exists" has nowhere to live.
--   DELETE: deleting the last order for a client erases the client entirely.

-- NORMALISED. Each fact stated once, in the table whose key determines it.
CREATE TABLE clients (
  client_id   INT PRIMARY KEY,        ← the determinant
  name        TEXT NOT NULL,
  city        TEXT,
  vat_number  TEXT UNIQUE          ← one client per VAT number, enforced
);
CREATE TABLE order_lines (
  order_id    INT,
  line_no     INT,
  product     TEXT NOT NULL,
  client_id   INT REFERENCES clients(client_id),   ← the DB refuses orphan rows
  PRIMARY KEY (order_id, line_no)
);

Read what the normalised version buys, because it is not aesthetics. UNIQUE on the VAT number means the database will now refuse to create a duplicate client. REFERENCES means it will refuse to record an order against a client that does not exist. These are business rules, enforced by the engine, for every application that ever touches this data — including ones written after everyone involved has left the company.

inference This is the part that matters for your advisory work. A constraint in the database is a rule the organisation cannot accidentally break. A rule enforced in application code is a rule that holds until someone writes a second application. Most "our data quality is terrible" situations are organisations that chose the second option, usually without knowing they were choosing.

3.3 The counter-argument, stated properly

Normalisation has a real cost: answering a question now requires joins, and joins are work. The standard response is denormalisation — deliberately duplicating data to avoid joins. Every NoSQL system of the 2010s made this its headline pitch.

Stonebraker and Pavlo's 2024 review dismantles the pitch in three clauses that are worth memorising, because you will hear the pitch again:

Point (3) is the deep one and it connects straight back to Section 1. Pre-joining bakes an access path into the stored layout — which is precisely what Codd's 1970 paper set out to abolish. You have re-invented the navigational database, with better hardware.

established That said, denormalisation is genuinely correct in one setting, and this is not a concession — it is the industry's settled position. In analytical systems, where data is written once and read many times and nothing is being updated in place, the update anomalies cannot occur. This is why the analytical world uses a deliberately denormalised design.

3.4 The star schema, and why it won

Two architectures fought over enterprise data warehousing in the 1990s. Bill Inmon (Building the Data Warehouse, 1992) argued for a normalised, enterprise-wide warehouse as the single source of truth, with departmental marts derived from it. Ralph Kimball (The Data Warehouse Toolkit, 1996) argued for dimensional modelling: build conformed subject-area marts directly, organised as facts surrounded by dimensions.

established Kimball's dimensional approach won the practical argument, and the star schema remains the default shape of analytical data in 2026 — it survived Hadoop, the death of Hadoop, the cloud warehouse, and the lakehouse essentially unchanged. inference My read on why: Inmon's design optimises for correctness-at-the-enterprise-level, which requires enterprise-level agreement before delivering value. Kimball's optimises for delivering one usable subject area quickly. Organisations reliably choose the thing that ships, and the enterprise model that requires universal agreement before it produces anything is a model that never produces anything.

3.5 Where the meaning goes to die

Two mechanisms silently destroy the meaning of data over time. Both are modelling problems, and both are invisible in any dashboard.

Slowly changing dimensions. A client moves from "mid-market" to "enterprise". If you overwrite the attribute, every historical report changes retroactively — last year's revenue mix shifts because a category label changed today. If you version the row instead (Kimball's "Type 2"), history is preserved but every query must now specify as of when. Most organisations overwrite, because it is easier, and then are puzzled that the same report run twice gives different answers. established This is a documented, named, solved problem that is nonetheless still one of the most common causes of untrustworthy corporate reporting.

Semantic drift. The column is called active_customer. In 2019 it meant "purchased in the last 12 months." In 2022 someone changed it to "has a live contract." The column name did not change. No documentation records the change. Every trend line crossing that boundary is now measuring two different things and presenting them as one. inference I would argue this is the single most under-diagnosed cause of bad executive decisions from good-looking data, precisely because it is invisible: the chart is smooth, the pipeline is green, the number is wrong.

Section 4

Six decades, six arguments

The history is not a march of progress. It is one argument, restaged roughly every twelve years, with better hardware each time.

There is a reason to learn this history rather than just the current state, and it is not cultural literacy. The field has a documented pattern: a new workload appears, someone declares the relational model obsolete, builds a system that abandons it, discovers over five to eight years why the abandoned parts existed, and re-adds them. Michael Stonebraker and Andrew Pavlo have now written this paper twice — in 2005 and again in 2024 — and the second one is largely a list of which predictions from the first came true.

inference For an advisor this is directly monetisable. If you can recognise which stage of the cycle a proposal sits in, you can price its risk without understanding its implementation. A technology in year two of the cycle is being sold on the things it removed; the same technology in year seven is being sold on having added them back.

4.1 What actually happened in the NoSQL decade

This is the era most worth understanding properly, because it is the one whose vocabulary is still in circulation and whose lessons are most frequently mis-stated.

Between 2003 and 2007 Google and Amazon published a run of papers describing systems built for problems almost nobody else had. Google's The Google File System (2003), MapReduce (2004) and Bigtable (2006); Amazon's Dynamo (2007). These were honest engineering documents about running at a scale where individual machine failure is constant rather than exceptional.

The industry read them as a general recommendation. It was not. Amazon's Dynamo paper is explicit that it was built for the shopping cart — a workload where losing a write is worse than briefly showing an inconsistent state, and where there are no joins because there is nothing to join. inference The mass adoption that followed was, in most cases, organisations with a few million rows adopting an architecture designed for constant hardware failure at planetary scale, and paying its costs without needing its benefits.

The costs were real and specific:

• "Schemaless" moved the schema, it did not remove it. The structure still exists; it now lives implicitly in every application that reads the data, undocumented and unenforced. Five years later, nobody knows which of the seven shapes in the collection are still valid.
• No joins meant application-side joins. Fetch, fetch again, combine in application code — which is a hand-written nested-loop join without an optimiser, over the network.
• Eventual consistency is a programming model, not a setting. It requires the application to handle stale reads and conflicting writes explicitly. Most did not; they simply had rare, undiagnosable bugs.

established The resolution is documented and not seriously disputed: by the end of the 2010s essentially every major NoSQL system had added a SQL or SQL-like query interface — DynamoDB (PartiQL), Cassandra (CQL), Couchbase (N1QL), MongoDB (an aggregation framework plus later SQL support). Hadoop, the era's flagship, is characterised by Stonebraker and Pavlo as having "died about a decade ago." The relational parts came back because the reasons for them had not gone away.

4.2 The one genuinely new thing: separating storage from compute

Not everything is a restaging. The cloud data warehouse — Snowflake's architecture paper is the canonical description (Dageville et al., SIGMOD 2016) — made a change that has no clean historical precedent in its consequences.

Traditional databases couple storage and compute in one machine: to store more you buy a bigger box, and the box's CPU sits idle most of the time. Snowflake put the data in cloud object storage (S3) and made compute clusters ephemeral things you spin up against it. The effects:

• Storage and compute scale independently. Ten times the data does not require ten times the CPU.
• Multiple isolated compute clusters read the same data — finance's heavy month-end job cannot slow down the product team.
• Cost becomes a variable, per-second operating expense rather than a capital purchase.

inference That last point is the one with organisational consequences, and it is under-discussed. When query cost is metered per second, a badly-written query stops being a technical annoyance and becomes a line item. Several of the "our cloud data bill exploded" stories you will encounter are not procurement failures — they are the direct result of removing the natural ceiling that owning a fixed-size machine used to provide.

Stonebraker and Pavlo note the pattern-recognition point here too: this is the return of the 1970s shared-disk architecture, which failed at the time and works now because networks got fast enough. Their prediction is that shared-disk now dominates and shared-nothing is unlikely to return. That is a real, falsifiable, non-obvious claim from people with standing.

Section 5

The types, and what each is really for

Category names are marketing. The real taxonomy has two axes: what shape the data is stored in, and what the system gives up.

Vendors present the landscape as a dozen distinct product categories. It is more useful to see it as a small number of engineering decisions, recombined. The two that explain most behaviour are row-oriented versus column-oriented storage, and single-node versus distributed. Almost everything else is a feature, not an architecture — a point Stonebraker and Pavlo make sharply about vector databases in particular.

5.1 Rows versus columns — the split that defines the industry

This is the most useful single distinction in the field and it is entirely graspable.

5.2 The categories, honestly described

Section 6

Possibilities and hard limits

Some constraints are engineering problems that money solves. Others are theorems. Knowing which is which is most of the value of this section.

6.1 The theorem: you cannot have everything during a network failure

Eric Brewer proposed the CAP conjecture in a 2000 keynote; Seth Gilbert and Nancy Lynch proved a formalised version in 2002 (SIGACT News 33(2)). The claim, stated precisely, is narrower than the way it is usually quoted:

When a network partition occurs — some nodes cannot talk to others — a distributed system must choose between remaining available (answer, possibly with stale data) and remaining consistent (refuse to answer rather than risk being wrong). You cannot have both, and this is a proof, not an engineering limitation. No vendor will ever solve it.

6.2 The physics: latency numbers worth memorising

These are order-of-magnitude figures, stable for a decade, and they explain more architectural decisions than any vendor document.

6.3 The scaling ladder

Each rung costs more in complexity than the one before, and most organisations climb further than they need to. inference The most common architectural error I would expect you to encounter in mid-size companies is not being on too low a rung — it is being on rung 4 or 5 with a workload that rung 2 handles comfortably.

1. One machine, bigger. Unfashionable and frequently correct. A current commodity server handles workloads that required a cluster in 2012. Complexity cost: nearly zero.
2. Read replicas. Copies serving reads. Cheap and effective, because most workloads are read-heavy. Cost: replicas lag, so reads may be slightly stale — which is fine until someone writes then immediately reads and gets their own old data.
3. Caching. Redis or similar in front. Big win, and introduces the second-hardest problem in computing: knowing when the cached copy is wrong.
4. Splitting workloads. Operational database plus a separate analytical one, fed by a pipeline. This is where most mid-size companies correctly land. Cost: two systems, a pipeline, and a permanent question about which numbers are authoritative.
5. Sharding / distributed SQL. Data split across machines by key. Real horizontal write scale. Cost: cross-shard transactions and cross-shard joins become slow or impossible, and rebalancing is a project.
6. Multi-region. Geographic distribution. Cost: the speed of light, permanently, on every consistent write.

6.4 What databases genuinely cannot do

• Tell you whether the data is true. Constraints enforce internal consistency, never correspondence with reality. A database will faithfully store a decade of a mis-calibrated sensor.
• Preserve meaning across a definition change. §3.5. No system detects this.
• Give strong consistency and low latency across continents. Theorem plus physics.
• Make an unrecorded fact recoverable. If the grain was wrong, the information is gone.
• Answer a question nobody has defined. "Active customer" has no technical meaning. The organisation has to decide, and usually has not.

Section 7

Choosing and using: the advisor's heuristics

You will rarely be the one choosing. You will often be the one deciding whether to believe the person who did.

7.1 The default, and the honest case against it

plausible For a company of 200–1,000 people, the correct starting architecture is almost always: one well-run PostgreSQL instance with read replicas, plus a separate columnar system when analytics outgrows it. Postgres now covers JSON documents, full-text search, geospatial (PostGIS), time-series (TimescaleDB), and vector search (pgvector) through extensions, which removes the original reason most organisations ran four databases.

This has become close to conventional wisdom, which is exactly when to look for the strongest counter-argument. It exists, and it comes from people with standing on both sides. In discussion of the 2026 "just use Postgres" position, a former Citus Data engineer — Citus being a Postgres scaling company, so the person is if anything predisposed to the pro-Postgres view — makes the concrete objection: organisations underestimate "the CAPEX and OPEX required to make Postgres work well for workloads it wasn't designed for, at even reasonable scale," and describes companies running "solid-sized teams of Postgres experts whose primary job was constant tuning, operating, and essentially babysitting the system."

inference Both positions are correct at different scales, and the resolution is a question rather than a rule. The generalist default is right until you have a workload whose specialist demands exceed your ability to staff generalist operations for it. For a 400-person company that is a genuinely high bar. For a company whose product is data — analytics at scale, high-frequency telemetry, real-time personalisation — it can be crossed in year one. Note also that both sides of this argument have commercial interests, which is normal and does not make either wrong.

7.2 Eight questions that separate signal from pitch

Ordered roughly by how much they reveal per second spent.

1. "What does one row represent?" — §3.1. Hesitation here predicts everything downstream.
2. "Is this workload transactional or analytical?" If the answer is "both", the architecture will be a compromise and someone should say so out loud.
3. "What are we giving up?" Every database is a set of trades. A pitch that names no trade-off is a pitch that has not been thought through, or is not being told to you.
4. "How much data, really?" Ask for a number. The gap between perceived and actual volume is routinely two orders of magnitude, and it changes the answer completely. Modern single-machine tools handle hundreds of gigabytes comfortably.
5. "What isolation level do we run at?" — §2.3. A precise answer signals a team that thinks about correctness. A blank look is a genuine finding.
6. "Who owns the definition of this metric, and when did it last change?" — §3.5. The most valuable question on the list, and the one least likely to have an owner.
7. "What happens when this is wrong?" Distinguishes a system needing strong consistency from one where eventual is fine. Most teams have never separated the two.
8. "Show me the query plan for the slowest report." — §2.1. You are not auditing it. You are finding out whether anyone has ever looked.

7.3 Six things that should make you slow down

• The pitch is framed as modernisation, not as a trade. §4.1. This is the single most reliable tell in the entire field.
• "Schemaless" or "flexible schema" offered as a benefit. The structure has been moved, not removed — into application code, undocumented.
• Scale requirements stated without numbers. "Web scale", "enterprise grade", "billions of records" with no measurement behind them.
• A new database category with one product name attached. The category may be a feature. Ask whether the incumbent's extension does it, and what the gap actually is.
• Benchmarks supplied by the vendor being benchmarked. Always. Not because vendors lie, but because they choose the workload.
• An AI-analytics claim with an accuracy number but no description of the question set. §11 shows how far apart those numbers can be depending on what was asked.

Section 8

Where databases sit in the stack

The database is never alone. Understanding its neighbours is what lets you locate a problem rather than just observe it.

The canonical modern arrangement has two halves that mirror the row/column split of §5.1: an operational side that records what happens, and an analytical side that explains it. A pipeline connects them, and that pipeline is where truth most often goes missing.

8.1 Three neighbours worth understanding properly

The ORM. Object-relational mappers (Hibernate, Django ORM, ActiveRecord, SQLAlchemy) let developers work with objects while the library generates SQL. This is the practical resolution of the "impedance mismatch" that object databases failed to solve in the 1990s. The cost is that query cost becomes invisible: a loop over 500 objects can silently issue 501 queries — the "N+1 problem", and the most common performance bug in application development. Stonebraker and Pavlo's position is unusually direct: "Developers need to query their database directly", because ORMs conceal the optimisation opportunities that raw SQL exposes.

Change data capture. Rather than periodically copying tables, CDC reads the database's own write-ahead log (§2.4) and emits every change as a stream. It is elegant — the log already exists for durability, so this is nearly free — and it turns "sync nightly" into "sync continuously". inference Its second-order effect is more interesting than its first: once changes stream continuously, the distinction between the operational and analytical halves starts to blur, which is much of what the "real-time data" market is actually selling.

Retrieval-augmented generation. The now-standard pattern for pointing a language model at private data: chunk documents, embed each chunk as a vector, store the vectors, retrieve the nearest ones to a question, and put them in the prompt. Worth being precise about what this is and is not. RAG retrieves similar text. It does not compute, aggregate, or reason over structured records. inference A great deal of disappointment in 2024–25 enterprise AI came from pointing RAG at questions that were arithmetic over a database — "what was Q3 revenue in the Nordics" is not a semantic-similarity problem, and a vector index is the wrong instrument for it. The right instrument is a query, which is §11's subject.

Section 9

The leading edge, mid-2026

Four movements with genuine momentum, and one honest assessment of what is still unresolved.

9.1 Open table formats have effectively won the storage layer

established The proprietary-storage era of the cloud warehouse is closing. Data increasingly lives as Parquet files in object storage, with an open table format — principally Apache Iceberg — supplying tables, schema evolution, and transactions on top. Databricks put Iceberg v3 into public preview in April 2026, adopting natively the three features that had been Delta Lake's differentiators (deletion vectors, row lineage, and a VARIANT type for semi-structured data), which substantially collapses the Delta-versus-Iceberg distinction that occupied the market for five years.

inference The strategic consequence is the one to carry into a board conversation. When storage is an open format that any engine can read, the query engine becomes a swappable component and vendor lock-in shifts from the data itself to the catalog — the metadata service that tracks tables, schemas and permissions. That is where the commercial fight has moved. "Which catalog, and can we leave it?" is now a more consequential procurement question than which warehouse.

9.2 The small-data counter-revolution

DuckDB — an in-process analytical database, columnar, no server, from CWI Amsterdam — has made a serious argument that most organisations calling themselves big-data are not. A large fraction of real analytical workloads fit on one machine, and a single machine with no network is dramatically simpler and faster than a cluster.

The same group has pushed this further with DuckLake (v1.0, April 2026), whose manifesto makes a pointed argument about lakehouse design. Iceberg and Delta encode metadata as files in object storage — "a maze of JSON and Avro files" — which makes small changes expensive and requires many sequential HTTP round-trips to plan a query. But both already require a database for their catalog. Their conclusion:

inference Whether or not DuckLake specifically wins, note the shape of the argument — it is §4's cycle, exactly on schedule. An era abandoned the database for scale reasons, built increasingly elaborate machinery to recover what the database provided, and is now being told to use a database.

9.3 Consolidation, not proliferation

established The direction of travel is toward fewer systems. Vector search arrived in 2023 as a new product category and was absorbed into mainstream relational databases within roughly a year — considerably faster than JSON was a decade earlier. Stonebraker and Pavlo's read is that specialised vector systems are "essentially document-oriented DBMSs with specialized ANN indexes," and that "such indexes are a feature, not the foundation of a new system architecture." Their broader prediction: "NoSQL systems are on a collision course with RDBMSs. The differences … have diminished over time and should become nearly indistinguishable in the future."

9.4 The unresolved one: machines as the primary query author

This is the live frontier and the one where I would counsel the most caution, because the evidence is genuinely mixed and the marketing is not.

Stonebraker and Pavlo — who have no product to sell here — note that natural-language database interfaces are "an old research topic that dates back to the 1970s, but which historically has poor outcomes," and add: "We acknowledge LLMs have impressive results for this task but caution those who think NL will replace SQL." They also observe a specific enterprise hesitancy: "There is a reluctance to depend on current LLM technology for decision-making inside the enterprise, especially with financial data," because the outputs are not explainable to a human.

The countervailing development is architectural rather than model-level: the semantic layer of Figure 7. Giving a model executable definitions of business concepts, rather than raw table structures, measurably improves accuracy. §11 has the numbers, including what they still leave on the table.

Section 10

Data science: the essentials

Four different jobs wear the same job title. Most confusion in the field — and most disappointment with it — comes from not distinguishing them.

10.1 The four jobs

These require different methods, different data, and different standards of evidence. Conflating them is the field's most expensive habit.

inference The one worth pressing on in a client setting is the gap between rows two and four. A model that predicts churn accurately does not tell you what to do about churn. Customers who contact support churn more — that is predictive and real. It does not follow that reducing support contact reduces churn; the contact is a symptom. Organisations act on predictive models as if they were causal ones routinely, and the resulting decisions are occasionally the exact opposite of correct.

10.2 Breiman's two cultures

Leo Breiman — a Berkeley statistician who invented random forests — published Statistical Modeling: The Two Cultures in Statistical Science in 2001. It is the single most useful paper for understanding what data science actually is and why it caused friction.

His argument: statistics had split into two cultures. The data modelling culture assumes the data were generated by a stochastic model (linear regression, logistic regression), fits its parameters, and interprets them — you get an explanation, in exchange for assuming a structure that is probably wrong. The algorithmic modelling culture treats the mechanism as unknown and optimises purely for predictive accuracy — random forests, boosted trees, neural networks. You get accuracy, and you often lose interpretability.

Breiman argued the first culture's near-monopoly in academic statistics had led to irrelevant theory and poor predictions. established He was substantially vindicated: the algorithmic culture became machine learning and then modern AI. But the trade he named is unchanged, and it is the trade that matters in advisory work. Accuracy and explanation are in tension, and which you need depends on what the answer is for. A model you must defend to a regulator, a board, or a court has an interpretability requirement that a recommendation engine does not.

10.3 The methodological core, in five ideas

• Overfitting. A model complex enough will memorise its training data, including its noise, and perform beautifully there and badly everywhere else. This is why performance is only meaningful on data the model has never seen — the held-out test set. A quoted accuracy figure without a described holdout is not a result.
• The bias–variance trade-off. Too simple and the model misses real structure; too complex and it chases noise. Most of practical modelling is navigating this.
• Cross-validation. Rotating which portion is held out, to get a stable estimate of out-of-sample performance from limited data.
• Leakage. Information from the future, or from the answer, contaminating the training data. The classic case: a feature computed after the outcome it predicts. The symptom is suspiciously excellent performance in development and collapse in production. inference In my reading this is the most common single cause of machine-learning projects that "worked in the pilot".
• Distribution shift. Models assume the future resembles the training past. When the world changes — a pandemic, a pricing change, a competitor exit — the model does not report an error. It reports a confident, wrong number.

10.4 The traps that survive technical competence

These are not beginner errors. They defeat well-trained people, which is why they are worth knowing by name.

• Confounding. A third variable drives both things you are comparing. Ice cream sales and drowning both rise with temperature.
• Simpson's paradox. A trend present in every subgroup reverses when groups are pooled. This is not a curiosity; it appears in real business data whenever group sizes differ, and it means the aggregate number and the segment numbers can both be correct and tell opposite stories.
• Survivorship bias. Analysing only the entities that remain. Studying your current customers to understand churn is studying the people who did not churn.
• Multiple comparisons. Test twenty hypotheses at 95% confidence and roughly one will be "significant" by chance. Dashboards with a hundred metrics are running this experiment continuously and reporting the winners.
• Base rate neglect. A 99%-accurate test for a condition affecting 1 in 1,000 produces about ten false positives per true one. This governs fraud detection, screening, and anomaly alerting, and it is why high-accuracy alerting systems get ignored by the people receiving the alerts.

10.5 What the work actually consists of

The definitive statement is D. Sculley and colleagues at Google, Hidden Technical Debt in Machine Learning Systems (NeurIPS 2015). Its central observation, which has held up for a decade: in a real production machine-learning system, the modelling code is a small fraction of the total, surrounded by a vastly larger mass of data collection, verification, feature extraction, serving infrastructure, configuration and monitoring.

The paper also names failure modes specific to ML that have no equivalent in ordinary software — entanglement (changing any one input changes the meaning of all the others: "CACE" — changing anything changes everything), hidden feedback loops (the model's own outputs influence the data it is later trained on), and undeclared consumers (systems quietly depending on your model's output, so you cannot change it).

Monica Rogati — formerly of LinkedIn, a practitioner rather than an academic — framed the consequence memorably in 2017 as the AI hierarchy of needs: you cannot do machine learning on top of an organisation that cannot reliably collect, move, store, and label its data. The layers below are not preparatory work. They are the work.

Section 11

The problem taxonomy, and the reliability boundary

Which data problems can an AI agent solve reliably today, which need a human check, and which still require a data scientist and a domain expert in the same room.

This is the question you actually asked, so it gets the most careful treatment and the most explicit evidence. The short version: the boundary is not drawn by task difficulty. It is drawn by two things — whether the answer can be cheaply verified, and how much undocumented organisational context the question secretly requires.

11.1 What the evidence actually shows

Three independent measurements, none from a vendor selling the capability, with numbers that are worth holding precisely because the marketing numbers are so different.

Multi-step analysis is largely unsolved. DABstep (Egg et al., Adyen and Hugging Face, 2025) built 450+ analysis tasks from Adyen's real payments-operations workload, requiring agents to combine structured transaction data with unstructured documentation. Frontier models of that vintage scored roughly 67–81% on the easy split — single-dataset, minimal documentation — and the best managed 14.55% on the hard split, which requires cross-referencing sources and multi-step reasoning. The failure modes named are specific and instructive: agents follow explicit instructions well and fail on implicit rules that must be inferred from documentation; they degrade at planning as steps accumulate.

Text-to-SQL is far harder on real schemas than on benchmarks. The original Spider benchmark, on small clean databases, was largely solved — GPT-4o scored 86.6%. Spider 2.0 rebuilt the task from real enterprise environments: databases with over 1,000 columns, multiple SQL dialects, solutions often exceeding 100 lines. The same model scored 10.1%. inference That 86.6 → 10.1 collapse, on the same underlying skill, is the most useful single number in this entire section, because it quantifies the gap between a demo and an enterprise.

And the benchmarks themselves are unreliable. contested Jin, Choi, Zhu and Kang (University of Illinois, CIDR 2026) hand-audited two leading benchmarks and found annotation error rates of 52.8% in BIRD Mini-Dev and 66.1% in the audited portion of Spider 2.0-Snow — incorrect ground-truth queries, queries that do not match the database, ambiguous questions. Re-scoring five leading agents on corrected examples moved absolute accuracy by −2 to +19 points and shifted rankings by up to three places. Their conclusion: the error rate "undermines the reliability of the leaderboard and fails to accurately reflect the true performance of the agents."

The architectural fix, honestly reported. Giving models a semantic layer — written definitions of business measures and disambiguation rules rather than raw table structures — measurably helps. A 2026 paired benchmark from Cube, a semantic-layer vendor, ran 100 natural-language questions against a retail dataset with and without a 4 KB hand-authored semantic document. Accuracy rose from roughly 45–50% to roughly 68% across three frontier models. inference The improvement is substantial and the direction is almost certainly right. But note what the vendor's own best case is: on a clean retail dataset, with a purpose-written semantic layer, roughly one answer in three is still wrong. That is the number to bring to a client considering unsupervised natural-language analytics.

11.2 The consequence for how you advise

inference Three things follow, and they are more actionable than any tool recommendation.

First, the constraint is the data estate, not the model. The same agent is reliable against a documented star schema with a semantic layer and unreliable against an undocumented warehouse. When a client's AI-analytics pilot disappoints, the model is rarely the variable worth changing — and the vendor will nonetheless propose changing it.

Second, the investment that pays is unglamorous. Writing down what the columns mean, what the metrics mean, which joins are legitimate, which periods are unreliable. This work has never had a sponsor because it never had a visible payoff. It now has one, and this is the strongest practical argument in the primer: semantic documentation has become a capability investment rather than a hygiene cost.

Third, "verify" must mean something specific. "A human reviews the output" is not a control if the human is reviewing a fluent paragraph containing a number they cannot independently derive. The verifiable version is: the agent shows its query, the query runs against known-good definitions, and someone who understands the domain can read the query. That is a real control. It is also the reason a non-technical executive is well served by being able to read SQL — which, of everything on this page, is the thing most worth an afternoon of your time.

Section 12

The map

Everything above, on one page, arranged by what causes what.

The shape of this map is the argument of the primer. Two outer spines — how the machine works, and what people do with it — and a spine down the middle that both depend on and neither owns. That middle spine is meaning, and the crossing lines are where it leaks.

Section 13

Lexicon, 1960–2026

Every term you are likely to hear, tagged by the era that produced it and whether it still means anything. Search it, or filter by era to read the field's development chronologically.

The value of reading this by era rather than alphabetically: you can see the field's arguments recur. Terms marked obsolete are worth knowing precisely because someone will use one, and the usage tells you when their mental model was formed.

Section 14

Sources

Primary sources only. Where a source has a commercial interest in its own finding, that is stated.

Foundational papers

• Codd, E. F. "A Relational Model of Data for Large Shared Data Banks." CACM 13(6), 1970.
• Bachman, C. "The Programmer as Navigator." ACM Turing Award Lecture, CACM 16(11), 1973.
• Bayer, R. & McCreight, E. "Organization and Maintenance of Large Ordered Indices." Acta Informatica 1(3), 1972.
• Chamberlin, D. & Boyce, R. "SEQUEL: A Structured English Query Language." SIGFIDET, 1974.
• Chen, P. "The Entity-Relationship Model." ACM TODS 1(1), 1976.
• Selinger, P. et al. "Access Path Selection in a Relational Database Management System." SIGMOD, 1979.
• Gray, J. "The Transaction Concept: Virtues and Limitations." VLDB, 1981.
• Härder, T. & Reuter, A. "Principles of Transaction-Oriented Database Recovery." ACM Computing Surveys 15(4), 1983.
• Mohan, C. et al. "ARIES: A Transaction Recovery Method…" ACM TODS 17(1), 1992.
• Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E. & O'Neil, P. "A Critique of ANSI SQL Isolation Levels." SIGMOD, 1995.

Warehousing and dimensional modelling

• Inmon, W. H. Building the Data Warehouse. Wiley, 1992.
• Kimball, R. The Data Warehouse Toolkit. Wiley, 1996 (and later editions).

Distributed systems and the web-scale era

• Ghemawat, S., Gobioff, H. & Leung, S.-T. "The Google File System." SOSP, 2003.
• Dean, J. & Ghemawat, S. "MapReduce: Simplified Data Processing on Large Clusters." OSDI, 2004.
• Chang, F. et al. "Bigtable: A Distributed Storage System for Structured Data." OSDI, 2006.
• DeCandia, G. et al. "Dynamo: Amazon's Highly Available Key-value Store." SOSP, 2007.
• Gilbert, S. & Lynch, N. "Brewer's Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services." SIGACT News 33(2), 2002.
• Abadi, D. "Consistency Tradeoffs in Modern Distributed Database System Design" (PACELC). IEEE Computer, 2012.
• Stonebraker, M. et al. "C-Store: A Column-oriented DBMS." VLDB, 2005.
• Corbett, J. et al. "Spanner: Google's Globally-Distributed Database." OSDI, 2012.
• Ongaro, D. & Ousterhout, J. "In Search of an Understandable Consensus Algorithm" (Raft). USENIX ATC, 2014.
• Dageville, B. et al. "The Snowflake Elastic Data Warehouse." SIGMOD, 2016.

The historical reviews — the most useful single reading

• Stonebraker, M. & Hellerstein, J. "What Goes Around Comes Around." 2005.
• Stonebraker, M. & Pavlo, A. "What Goes Around Comes Around… And Around…" ACM SIGMOD Record 53(2), 2024. db.cs.cmu.edu — if you read one thing beyond this primer, read this.

Data science

• Breiman, L. "Statistical Modeling: The Two Cultures." Statistical Science 16(3), 2001.
• Sculley, D. et al. "Hidden Technical Debt in Machine Learning Systems." NeurIPS, 2015.
• Rogati, M. "The AI Hierarchy of Needs." 2017 — practitioner framing, not peer-reviewed.

Agent and text-to-SQL evidence (§11)

• Egg, A., Iglesias Goyanes, M., Kingma, F., Mora, A. (Adyen); von Werra, L. & Wolf, T. (Hugging Face). "DABstep: Data Agent Benchmark for Multi-step Reasoning." 2025. arXiv:2506.23719
• Spider 2.0 — enterprise text-to-SQL benchmark and leaderboard. spider2-sql.github.io. Note: leaderboard entries are self-submitted, several by vendors.
• Jin, T., Choi, Y., Zhu, Y. & Kang, D. (UIUC). "Text-to-SQL Benchmarks are Broken: An In-Depth Analysis of Annotation Errors." CIDR, 2026. vldb.org
• Cube. "Why semantic layers make LLM analytics reliable: a paired benchmark across three frontier models." 2026. cube.dev — vendor source; Cube sells semantic-layer software. Reported here because its own best case (~68%) is the useful figure.

Current landscape

• Raasveldt, M. & Mühleisen, H. "The DuckLake Manifesto: SQL as a Lakehouse Format." DuckDB Labs, 2025; DuckLake v1.0, April 2026. ducklake.select — vendor source (DuckDB Labs).
• Databricks. "The next era of the open lakehouse: Apache Iceberg v3 in Public Preview." April 2026. databricks.com — vendor source.
• Hacker News discussion of "It's 2026, Just Use Postgres," including the Citus Data counter-argument quoted in §7.1. news.ycombinator.com — practitioner commentary, not peer-reviewed.

Prepared for Jakob Beck · MøllerBeck · July 2026. Advisor-evaluator lens, ~70/30 database-to-data-science weighting, primary sources only.