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.
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.
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.
The families at a glance, each a bet on one access pattern.
| If your main need is... | Think... | Because | Verdict |
|---|---|---|---|
| Many small correct updates, many users | Relational | Transactions and constraints protect correctness | Pursue |
| Fetch one thing by key, instantly, at huge scale | Key-value / wide-column NoSQL | Access by key is simple to spread over many nodes | Pursue |
| Flexible records that change shape often | Document store | Nested records without a fixed schema | Pursue; keep a data model discipline |
| Scan and aggregate billions of rows with SQL | MPP warehouse or lakehouse engine | Column storage and parallel scans | Pursue |
| Many tools reading one copy of data, cheaply | Open table format on object storage | Open files and a shared log | Pursue |
| How things connect, several hops deep | Graph | Traversal beats many self-joins | Pursue for relationship questions |
| Find similar meaning, not exact words | Vector | Nearest-neighbour over embeddings | Pursue for AI retrieval |
| A little data, one machine, fast answers | Embedded single-node engine | No cluster, no overhead | Pursue 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.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| ACID transactions | Changes are all-or-nothing and isolated | Coordination costs at very high scale | Trust it as the source of truth |
| Constraints and keys | Data stays consistent | Rigid shape | Read keys and rules to learn what the data means |
| Indexes | Fast lookups of a few rows | Slow for scans of most rows; cost on writes | Do not run heavy analytics here |
| Row storage | Whole row together | Reads every column to answer a one-column question | Extract changes, do analytics elsewhere |
| Scaling | Vertical, with read replicas | Hard to scale writes out | Read 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.
| Kind | Shape | Strong at | Weak at | Typical use |
|---|---|---|---|---|
| Key-value | Key to opaque value | Instant get and put, massive scale | Queries on anything but the key | Sessions, caches, counters |
| Document | Nested records (JSON-like) | Flexible shape, whole-record reads | Cross-record joins and heavy analytics | Catalogues, profiles, content |
| Wide-column | Rows with many sparse columns, partitioned by key | Huge write throughput, time-ordered reads by key | Ad hoc queries | Event and IoT data, activity feeds |
| In-memory | Data held in RAM | Microsecond answers | Size and durability cost | Caching, 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.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| Columnar storage | Reads only needed columns | Single-row updates are costly | Load in batches, not row by row |
| Distribution key | Which node holds which rows | Poor key causes skew and shuffles | Choose a key that spreads evenly and matches common joins |
| Concurrency scaling | Many analysts at once | Cost grows with usage | Tag and watch spend |
| Separated storage and compute | Scale each alone | Network between them | Cache and cluster layout matter |
| Proprietary storage | Highly tuned | Data is locked behind the engine | Weigh 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.
How the families work together in one platform.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| Open column files | Compressed, columnar, readable by anyone | Immutable files | Updates rewrite files |
| Table format log | Atomic commits, time travel, schema evolution | Maintenance needed | Plan compaction and clean-up |
| Engine choice | Spark, SQL warehouses, single-node engines can share data | Feature support varies by engine | Check what each engine supports |
| Lakehouse pattern | Warehouse behaviour on lake storage | More to operate than a managed warehouse | Use managed services where they exist |
| Governance layer | One catalog, permissions and lineage | Must be set up deliberately | Do 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.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| Durability and cost | Extremely durable and cheap per byte | Per-request cost and latency | Read big files, not many small ones |
| Immutable objects | Safe concurrent reads | No in-place edit or true append | Table formats add the missing behaviour |
| Tiers and lifecycle | Cheaper cold storage, automatic ageing | Retrieval delay or fee | Match tier to read frequency |
| Listing is slow | Folder-like paths are not real folders | Millions of objects hurt listing | Fewer, 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.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| Property graph | Nodes and edges with attributes | Weaker for big aggregates | Use for connection questions only |
| Traversal queries | Multi-hop paths cheaply | Hot nodes with millions of edges | Watch super-nodes |
| Typical uses | Fraud rings, lineage, recommendations, knowledge graphs | Often fed from tables, not a primary source | |
| Alternative | Edge tables in a warehouse with recursive queries | Slower for deep traversal | Fine 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.
| Aspect | What it gives | Limit | Data engineering view |
|---|---|---|---|
| Embeddings | Meaning as numbers | Tied to the model that made them | Version the model; re-embed on change |
| Approximate nearest neighbour | Fast similarity search over millions | Results are close, not exact | Measure recall for your data |
| Metadata filters | Combine meaning with exact filters | Filters can slow or weaken search | Design filters with the index |
| Keeping it fresh | Index follows the source | Stale or deleted content may linger | Sync deletes and updates, not just inserts |
| Where it sits | Retrieval for AI answers and semantic search | Not a system of record | Source of truth stays elsewhere |
Others worth knowing
Specialised stores
A short list of shapes you will meet, each built for one pattern.
| Store | Built for | Note |
|---|---|---|
| Time-series database | Timestamped measurements with fast range queries and downsampling | Often replaced by a column store with time partitioning at modest scale |
| Search engine | Full-text search and filters | Not a system of record |
| Embedded single-node engine | In-process analytics on files or one machine | Excellent below the point where a cluster is needed |
| Streaming log | Ordered, replayable event stream | A 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 rule | What it catches, in plain words |
|---|---|
uc_external_bi_tool_direct_query_heavy | Heavy analytics sent to the wrong layer |
uc_dashboard_query_reads_bronze_instead_of_gold | Dashboard on raw data |
uc_single_node_with_distributed_workload | Single node doing a distributed job |
uc_workload_should_be_serverless | Spiky workload on fixed compute |
to_pandas_without_limit | Pulling a big table into one machine |
Open formats and files
| CostSherlock rule | What it catches, in plain words |
|---|---|
using_unoptimised_format | Not a columnar, compressed format |
too_many_files | Small files on object storage |
uc_external_locations_multi_cloud | Storage across clouds |
uc_external_locations_region_mixed | Storage across regions |
Vector and AI stores
| CostSherlock rule | What it catches, in plain words |
|---|---|
uc_vector_search_endpoint_no_indexes | Vector endpoint with no indexes |
uc_vector_search_endpoint_stale | Vector endpoint gone stale |
uc_vector_search_endpoint_no_cost_tags | Vector endpoint without owner tags |
uc_materialized_feature_online_never_materialized | Online store never filled |
uc_materialized_feature_online_stale | Online 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.