Home › Data engineering › Fundamentals › Database families

Data engineering fundamentals · free guide

Database families: RDBMS, NoSQL, MPP, open formats, object storage, graph and vector

Every data store is a bet on one access pattern. Relational databases bet on correct, small, many-user transactions. Warehouses bet on big scans. Key-value stores bet on instant lookups. Graph and vector stores bet on relationships and similarity. This page shows each family as a mind map branch: what it is built for, what it is bad at, and how to choose. No product comparison; families only.

Start here

The data physics

Five plain facts that explain almost everything below. Read these and the rest follows.

No store is good at everything.A store that is fast at one thing pays for it somewhere else. Pick by how the data will be read and written, not by fashion.
Read pattern decides the family.Fetch one record by key, scan billions of rows, follow relationships, find the nearest meaning: four different needs, four different families.
Write pattern decides the cost.Many small concurrent writes want a transactional store. Large batch loads want a column store or files.
Storage and compute can be together or apart.Older systems bundle them. Lakehouse and cloud warehouses separate them so each scales alone.
Most platforms use several.A source database feeds object storage and tables, which feed a warehouse layer, a search or vector index and a serving store. Each does the job it is shaped for.

The mind map

The whole topic on one screen

Each card is a branch. Walk them in order when you are stuck: the question tells you what the branch is about, and the first check tells you where to look.

Database families

1. Relational (RDBMS)

Correct, structured, transactional data

Tables, keys, SQL, ACID transactions, constraints

First check: Operational systems of record

2. NoSQL

Flexible shape or extreme scale for simple access

Key-value, document, wide-column, in-memory

First check: Lookups by key at any scale

3. MPP warehouse

Big analytical queries across many nodes

Columnar storage, parallel scans, SQL

First check: Reporting and BI on large data

4. Big data open formats

Open files plus a table log on shared storage

Parquet with Delta, Iceberg or Hudi style tables

First check: Lakehouse: many engines, one copy

5. Object storage

Cheap, durable, endless files

Buckets, objects, tiers, lifecycle

First check: The base layer under open formats

6. Graph

Relationships are the question

Nodes, edges, traversals

First check: Networks, lineage, fraud, knowledge graphs

7. Vector

Find things by meaning

Embeddings, nearest-neighbour search

First check: Search over text and images; retrieval for AI

8. Others worth knowing

Specialised shapes

Time series, search engines, in-memory caches, embedded single-node engines

First check: Match to a clear need

How to choose

Start from the question, not the product

Ask what the most important read and write is. The answer names the family.

Relationalcorrect small transactionsrows, keys, SQLKey-value / NoSQLinstant lookup by keykey to valueMPP warehousebig scans in parallelcolumns, many nodesOpen table formatsmany engines, one copyfiles plus a logObject storagecheap, durable floorbuckets of filesGraphwho connects to whomnodes and edgesVectorfind by meaningembeddingsEmbedded enginesmall data, one machineno clusterPick by the main read and write pattern. Most platforms combine several.

The families at a glance, each a bet on one access pattern.

If your main need is...Think...BecauseVerdict
Many small correct updates, many usersRelationalTransactions and constraints protect correctnessPursue
Fetch one thing by key, instantly, at huge scaleKey-value / wide-column NoSQLAccess by key is simple to spread over many nodesPursue
Flexible records that change shape oftenDocument storeNested records without a fixed schemaPursue; keep a data model discipline
Scan and aggregate billions of rows with SQLMPP warehouse or lakehouse engineColumn storage and parallel scansPursue
Many tools reading one copy of data, cheaplyOpen table format on object storageOpen files and a shared logPursue
How things connect, several hops deepGraphTraversal beats many self-joinsPursue for relationship questions
Find similar meaning, not exact wordsVectorNearest-neighbour over embeddingsPursue for AI retrieval
A little data, one machine, fast answersEmbedded single-node engineNo cluster, no overheadPursue for small and medium data

Relational (RDBMS)

The system of record

Rows in tables, joined by keys, queried with SQL, protected by transactions. Usually row-oriented, tuned for many small reads and writes.

AspectWhat it givesLimitData engineering view
ACID transactionsChanges are all-or-nothing and isolatedCoordination costs at very high scaleTrust it as the source of truth
Constraints and keysData stays consistentRigid shapeRead keys and rules to learn what the data means
IndexesFast lookups of a few rowsSlow for scans of most rows; cost on writesDo not run heavy analytics here
Row storageWhole row togetherReads every column to answer a one-column questionExtract changes, do analytics elsewhere
ScalingVertical, with read replicasHard to scale writes outRead from a replica, not the primary

NoSQL

Four sub-families, one idea: give up some generality for scale or shape

NoSQL is not one thing. Each kind trades away joins, flexible queries or strict consistency for something specific.

KindShapeStrong atWeak atTypical use
Key-valueKey to opaque valueInstant get and put, massive scaleQueries on anything but the keySessions, caches, counters
DocumentNested records (JSON-like)Flexible shape, whole-record readsCross-record joins and heavy analyticsCatalogues, profiles, content
Wide-columnRows with many sparse columns, partitioned by keyHuge write throughput, time-ordered reads by keyAd hoc queriesEvent and IoT data, activity feeds
In-memoryData held in RAMMicrosecond answersSize and durability costCaching, leaderboards, rate limits

MPP warehouse

Massively parallel analytics

Data is spread over many nodes; each scans its slice in parallel and results are combined. Column storage plus compression makes scans cheap.

AspectWhat it givesLimitData engineering view
Columnar storageReads only needed columnsSingle-row updates are costlyLoad in batches, not row by row
Distribution keyWhich node holds which rowsPoor key causes skew and shufflesChoose a key that spreads evenly and matches common joins
Concurrency scalingMany analysts at onceCost grows with usageTag and watch spend
Separated storage and computeScale each aloneNetwork between themCache and cluster layout matter
Proprietary storageHighly tunedData is locked behind the engineWeigh against open formats

Big data open formats

Open files plus a table log

Parquet-like columnar files sit on object storage, and a table format adds a log for transactions, schema and versions. Many engines can read the same copy.

Source DBrelationalextractObject storagefiles + logAnalyticswarehouse or lakehouseVector indexAI searchServing storefast lookupsEach store does the one job it is shaped for.

How the families work together in one platform.

AspectWhat it givesLimitData engineering view
Open column filesCompressed, columnar, readable by anyoneImmutable filesUpdates rewrite files
Table format logAtomic commits, time travel, schema evolutionMaintenance neededPlan compaction and clean-up
Engine choiceSpark, SQL warehouses, single-node engines can share dataFeature support varies by engineCheck what each engine supports
Lakehouse patternWarehouse behaviour on lake storageMore to operate than a managed warehouseUse managed services where they exist
Governance layerOne catalog, permissions and lineageMust be set up deliberatelyDo it first; retrofitting is painful

Object storage

The base layer

Flat buckets of immutable objects addressed by path-like keys. It is not a file system and not a database; it is the cheap, durable floor underneath open formats and backups.

AspectWhat it givesLimitData engineering view
Durability and costExtremely durable and cheap per bytePer-request cost and latencyRead big files, not many small ones
Immutable objectsSafe concurrent readsNo in-place edit or true appendTable formats add the missing behaviour
Tiers and lifecycleCheaper cold storage, automatic ageingRetrieval delay or feeMatch tier to read frequency
Listing is slowFolder-like paths are not real foldersMillions of objects hurt listingFewer, bigger files; use a table log

Graph

When the relationships are the data

Nodes and edges stored so that following connections is fast. A question like 'who is connected to this account within three hops' is awkward and slow as joins and natural as a traversal.

AspectWhat it givesLimitData engineering view
Property graphNodes and edges with attributesWeaker for big aggregatesUse for connection questions only
Traversal queriesMulti-hop paths cheaplyHot nodes with millions of edgesWatch super-nodes
Typical usesFraud rings, lineage, recommendations, knowledge graphsOften fed from tables, not a primary source
AlternativeEdge tables in a warehouse with recursive queriesSlower for deep traversalFine for shallow, occasional questions

Vector

Find by meaning

Text, images and other content are turned into lists of numbers (embeddings) so that similar meaning sits close together. A vector store finds the nearest items quickly, often approximately.

AspectWhat it givesLimitData engineering view
EmbeddingsMeaning as numbersTied to the model that made themVersion the model; re-embed on change
Approximate nearest neighbourFast similarity search over millionsResults are close, not exactMeasure recall for your data
Metadata filtersCombine meaning with exact filtersFilters can slow or weaken searchDesign filters with the index
Keeping it freshIndex follows the sourceStale or deleted content may lingerSync deletes and updates, not just inserts
Where it sitsRetrieval for AI answers and semantic searchNot a system of recordSource of truth stays elsewhere

Others worth knowing

Specialised stores

A short list of shapes you will meet, each built for one pattern.

StoreBuilt forNote
Time-series databaseTimestamped measurements with fast range queries and downsamplingOften replaced by a column store with time partitioning at modest scale
Search engineFull-text search and filtersNot a system of record
Embedded single-node engineIn-process analytics on files or one machineExcellent below the point where a cluster is needed
Streaming logOrdered, replayable event streamA pipe with memory, not a database for queries

In practice

CostSherlock rules for this topic

CostSherlock is our open set of cost and performance rules. Each rule below has a plain meaning here; the rule id is its name in CostSherlock. They are grouped by the same branches as the mind map, so a finding tells you which branch to open.

Matching family to workload

CostSherlock ruleWhat it catches, in plain words
uc_external_bi_tool_direct_query_heavyHeavy analytics sent to the wrong layer
uc_dashboard_query_reads_bronze_instead_of_goldDashboard on raw data
uc_single_node_with_distributed_workloadSingle node doing a distributed job
uc_workload_should_be_serverlessSpiky workload on fixed compute
to_pandas_without_limitPulling a big table into one machine

Open formats and files

CostSherlock ruleWhat it catches, in plain words
using_unoptimised_formatNot a columnar, compressed format
too_many_filesSmall files on object storage
uc_external_locations_multi_cloudStorage across clouds
uc_external_locations_region_mixedStorage across regions

Vector and AI stores

CostSherlock ruleWhat it catches, in plain words
uc_vector_search_endpoint_no_indexesVector endpoint with no indexes
uc_vector_search_endpoint_staleVector endpoint gone stale
uc_vector_search_endpoint_no_cost_tagsVector endpoint without owner tags
uc_materialized_feature_online_never_materializedOnline store never filled
uc_materialized_feature_online_staleOnline store stale

FAQ

Questions people ask

Is NoSQL replacing relational databases?

No. They solve different problems. Relational systems are still the system of record for most transactional data. NoSQL stores serve specific patterns such as instant key lookups at scale.

What is the difference between a warehouse and a lakehouse?

A warehouse bundles storage and engine in one product. A lakehouse keeps data in open files and table formats on object storage so several engines can use the same copy.

Do I need a graph database?

Only if multi-hop relationship questions are central. For shallow joins, ordinary tables are fine.

Is a vector database a replacement for search?

No. It finds by meaning. Exact keyword and filter search still matters, so many systems combine both.

Why not run analytics on the operational database?

It is row-oriented and tuned for small transactions. Big scans compete with live users. Copy the data to an analytics store.

Is this a product comparison?

No. It compares families by what each is built for. Products within a family differ in many ways; check current documentation before choosing.

Related

Where it connects

Performance fundamentals · Cost fundamentals · Processing fundamentals · Security fundamentals · Storage fundamentals · Where data engineering time goes · Who maintains your tables · All data engineering

The numbers and thresholds on this page are rules of thumb and orders of magnitude to build intuition, not benchmarks. Check your platform's current documentation and measure your own workload.