SQL databases (relational databases) store data in tables with a fixed schema and link tables through keys, and you query them with SQL. NoSQL databases store data in other shapes, such as JSON documents, key-value pairs, wide rows or graphs, usually with a flexible schema, and each family has its own query language or API.
The short answer to "which one?": data that is structured, related and must stay consistent, such as orders, payments, accounts and inventory, belongs in a relational database. NoSQL earns its place for specific access patterns: documents whose fields vary, very high write volumes spread across many servers, sub-millisecond lookups by key, and relationships you traverse many hops deep. Most companies run both, and copy both into a SQL data warehouse for analytics.
| SQL (relational) | NoSQL | |
|---|---|---|
| Data model | Tables of rows and columns, linked by keys | Documents, key-value pairs, wide-column rows or graphs |
| Schema | Defined up front, enforced by the database | Flexible; often enforced by the application, if at all |
| Query language | SQL, standard across databases | Specific to each database (MongoDB query API, CQL, Cypher) |
| Relationships | Joins across tables | Embedded in the record, or followed as graph edges |
| Transactions | ACID across many tables by default | Varies: often per record, multi-record in some databases |
| Scaling | Mostly vertical, horizontal with extra work | Built to spread data across many servers |
| Examples | PostgreSQL, MySQL, SQL Server, Oracle | MongoDB, Redis, DynamoDB, Cassandra, Neo4j |
This guide explains each side, the four NoSQL families with a real database for each, ACID versus BASE, scaling, and a decision table by workload. Then it stores one order both ways, answers the same question with each, and shows what both look like once loaded into a data warehouse.
What is a SQL database?
A relational database organises data into tables. Each table has a fixed set of typed columns, each row is one record, and relationships are expressed with keys: an order_items row carries the order_id of the order it belongs to. The database enforces the rules you declare, such as "every item must point at an existing order" or "quantity must be positive", and rejects writes that break them.
Because related data lives in separate tables, you combine it at read time with joins. That is what makes relational databases good at questions nobody planned for: any column can be filtered, grouped or joined to any other table.
Common relational databases are PostgreSQL, MySQL and MariaDB, Microsoft SQL Server, Oracle and SQLite. They are the default for application backends that handle money, bookings or stock.
What is a NoSQL database?
NoSQL ("not only SQL") covers databases that do not use the relational table model. They appeared to solve problems relational databases handled poorly at the time: spreading huge data volumes over many cheap servers, storing records whose fields change often, and serving very fast reads by key.
The trade is usually this: you design the data around the queries the application will run, instead of around the entities. Data that is read together is stored together. Reads that match the design are fast and simple, and questions the design did not anticipate are harder.
The four types of NoSQL databases
| Type | How it stores data | Real databases | Good at | Weak at |
|---|---|---|---|---|
| Document | JSON-like documents, nested fields and arrays, grouped in collections | MongoDB, Couchbase, Firestore | Records with varying fields read as one unit: catalogs, profiles, content | Queries across many documents and joins |
| Key-value | A value (string, blob, small structure) stored under a unique key | Redis, Amazon DynamoDB, Memcached | Very fast lookups by key: caching, sessions, carts, rate limits | Anything that is not a lookup by key |
| Wide-column | Rows grouped by a partition key, each row with its own set of columns | Apache Cassandra, ScyllaDB, HBase, Google Bigtable | Huge write volumes spread across servers and regions: events, IoT, messages | Ad hoc queries outside the partition key |
| Graph | Nodes and relationships (edges), both with properties | Neo4j, Amazon Neptune | Following relationships many hops deep: fraud rings, recommendations, permissions | Bulk aggregation over all records |
A few notes that the usual comparisons skip:
- Wide-column is not columnar. A wide-column store like Cassandra keeps each row's data together by partition key, which suits writes and lookups by key. A columnar database, like the analytics warehouses BigQuery and Snowflake, stores each column together, which suits scanning a few columns across billions of rows. They are opposite ideas with similar names.
- Many databases span types. DynamoDB supports both key-value and document models, Azure Cosmos DB offers several APIs, and Redis has data structures beyond plain strings.
- Search engines and time-series databases are often grouped with NoSQL. Elasticsearch and OpenSearch index documents for full-text search; InfluxDB stores time-stamped measurements. TimescaleDB does the same job as an extension on top of PostgreSQL.
ACID vs BASE in plain words
ACID is the guarantee relational databases give for a transaction, a group of writes that belong together:
- Atomicity: all of the writes happen, or none do. Money never leaves one account without arriving in the other.
- Consistency: every rule you declared (keys, constraints) holds after the transaction.
- Isolation: two transactions running at the same time do not see each other's half-finished work.
- Durability: once the database says "committed", the data survives a crash.
BASE describes the looser model many distributed NoSQL databases chose in exchange for availability and scale:
- Basically available: the system keeps answering even when some servers are down.
- Soft state: copies of the data on different servers may differ for a while.
- Eventually consistent: if writes stop, all copies converge on the same value.
In practice the line has blurred. MongoDB has supported multi-document ACID transactions since version 4.0, DynamoDB has transactional writes across items, and Cassandra lets you choose the consistency level per query. Relational databases, in turn, can serve slightly stale reads from replicas. The useful question is not "ACID or BASE" but "which of my writes must be all-or-nothing, and which reads can be a second out of date?"
Scaling: vertical and horizontal
Vertical scaling means a bigger server: more CPU, memory and faster disks. It is simple and is how most relational databases grow. A single modern PostgreSQL server handles far more than most companies need, and read replicas take reporting load off the primary.
Horizontal scaling means more servers, with the data split (sharded) across them by a key. Most NoSQL databases were built for this from the start, which is why they handle very large write volumes and multi-region setups well. The price is that queries spanning many shards, and transactions across them, are harder or slower.
Relational databases can scale out too. Partitioning and sharding extensions exist for PostgreSQL and MySQL, and distributed SQL databases such as Google Spanner, CockroachDB and YugabyteDB keep SQL and ACID transactions across many nodes. Scale alone is rarely the reason to leave SQL any more; the shape of the data and the access pattern usually decide.
Which database for which workload
| Workload | Best fit | Example databases | Why |
|---|---|---|---|
| Transactions (OLTP): orders, payments, bookings, inventory | Relational | PostgreSQL, MySQL, SQL Server | ACID transactions across tables, constraints that reject bad data |
| Analytics (OLAP): reporting, BI, metrics over history | Columnar SQL data warehouse | BigQuery, Snowflake, Amazon Redshift, ClickHouse | Scans a few columns over billions of rows quickly; joins across sources |
| Documents: product catalogs, CMS content, user profiles with varying fields | Document database, or PostgreSQL JSONB | MongoDB, Couchbase, PostgreSQL | The record is read and written as one unit; fields can differ per record |
| Caching and sessions | In-memory key-value | Redis, Memcached | Sub-millisecond reads by key; data can be rebuilt if lost |
| High-volume key lookups at scale | Managed key-value or document | DynamoDB | Predictable latency at any size when every query is by key |
| Time series: metrics, IoT readings | Time-series database | TimescaleDB, InfluxDB | Fast inserts in time order, rollups and retention by age |
| Graph: fraud rings, recommendations, access rights | Graph database | Neo4j, Amazon Neptune | Traversing many hops is a single query instead of many self-joins |
| Full-text search | Search engine | Elasticsearch, OpenSearch | Relevance ranking, fuzzy matching, facets |
| Massive writes across regions: events, messages | Wide-column | Cassandra, ScyllaDB, Bigtable | Writes spread evenly across many servers with no single primary |
If you are unsure, start relational. PostgreSQL covers transactions, JSON documents (with jsonb and indexes on it), full-text search and, with extensions, time series. Move a workload to a specialised database when a concrete access pattern outgrows it.
Which type of data is best stored in a relational database?
Data that is structured, consistent and related to other data:
- Every record has the same fields. Customers, orders, invoices and products all have a known set of attributes.
- The relationships matter. An order belongs to a customer and has line items that point at products, and you need to ask questions across those links.
- Correctness beats raw speed. Payments, balances, stock levels and bookings must never be half-written or double-counted, so they need ACID transactions and constraints.
- The questions change. Finance, sales and support will all query the same data in ways nobody predicted; SQL handles new questions without redesigning storage.
What fits poorly: large files such as images, video and PDFs (keep them in object storage and store the path and metadata in a table), long free text that you mainly search (a search engine does that better), and records whose structure varies so much that most columns would be empty.
Worked example: one order stored both ways
A shop sells desk lamps, notebooks and pen sets. Order 1042 contains two lamps and three notebooks. Here is that order, and two more, stored relationally and as documents. Then both answer the same question: revenue by product for paid orders.
Relational: two tables and a join
1create table orders (
2 order_id integer primary key,
3 customer_id integer not null,
4 ordered_at timestamp not null,
5 status text not null
6);
7
8create table order_items (
9 order_id integer not null references orders (order_id),
10 line_no integer not null,
11 sku text not null,
12 product_name text not null,
13 quantity integer not null check (quantity > 0),
14 unit_price numeric(10, 2) not null,
15 primary key (order_id, line_no)
16);
17
18insert into orders values
19 (1042, 7, '2026-10-05 09:12', 'paid'),
20 (1043, 9, '2026-10-06 14:30', 'paid'),
21 (1044, 7, '2026-10-07 08:05', 'cancelled');
22
23insert into order_items values
24 (1042, 1, 'A1', 'Desk lamp', 2, 45.00),
25 (1042, 2, 'B7', 'Notebook', 3, 12.50),
26 (1043, 1, 'A1', 'Desk lamp', 1, 45.00),
27 (1043, 2, 'C3', 'Pen set', 1, 18.00),
28 (1044, 1, 'B7', 'Notebook', 10, 12.50);The database now refuses an item for an order that does not exist, a quantity of zero, or a price that is not a number. Revenue by product is one join and a group by:
1select
2 i.sku,
3 i.product_name,
4 sum(i.quantity * i.unit_price) as revenue
5from orders o
6join order_items i on i.order_id = o.order_id
7where o.status = 'paid'
8group by i.sku, i.product_name
9order by revenue desc;| sku | product_name | revenue |
|---|---|---|
| A1 | Desk lamp | 135.00 |
| B7 | Notebook | 37.50 |
| C3 | Pen set | 18.00 |
The cancelled order 1044, with ten notebooks, is excluded by the status filter.
Document: one order, items nested
In MongoDB the same order is one document, with the line items embedded:
1{
2 "_id": 1042,
3 "customer_id": 7,
4 "ordered_at": "2026-10-05T09:12:00Z",
5 "status": "paid",
6 "items": [
7 { "sku": "A1", "name": "Desk lamp", "qty": 2, "unit_price": 45.0 },
8 { "sku": "B7", "name": "Notebook", "qty": 3, "unit_price": 12.5 }
9 ]
10}Showing the order on a receipt page is one read by key, with no join: db.orders.findOne({ _id: 1042 }). That is the document model's strength. Revenue by product needs an aggregation pipeline that unwinds the array into one record per item before grouping:
1db.orders.aggregate([
2 { $match: { status: "paid" } },
3 { $unwind: "$items" },
4 {
5 $group: {
6 _id: "$items.sku",
7 name: { $first: "$items.name" },
8 revenue: { $sum: { $multiply: ["$items.qty", "$items.unit_price"] } },
9 },
10 },
11 { $sort: { revenue: -1 } },
12]);It returns the same three totals: A1 135, B7 37.5, C3 18. In production you would store prices as Decimal128 rather than plain numbers, so the sums do not pick up floating-point errors.
What the example shows
- Reading one order: the document wins. One read returns everything, and the shape matches what the app renders.
- Questions across orders: SQL wins. The join and group by are short, and adding "by month" or "by customer country" is one more column. In the document model each new question is another pipeline, and data that lives in other collections needs
$lookup. - Changing the structure: the document wins at first. Adding a
gift_messageto new orders needs no migration. But old documents do not have the field, so every reader has to handle both shapes. - Keeping data correct: SQL wins. The constraints in the
create tablestatements are enforced by the database; in the document model, the application has to validate a missingqtyor a string price. - Duplication: the document copies
nameinto every order. That is deliberate (the receipt should show the name at the time of purchase), but renaming a product for reporting purposes means updating many documents, or joining to a product collection anyway.
After both land in a data warehouse
Analytics rarely runs on either production database. Both get copied into a warehouse, where the two models meet in SQL.
The relational tables arrive as two tables, and the same join runs unchanged. The MongoDB collection arrives as one table with a row per document. How the nested items array lands depends on the loader; a common choice is top-level fields as columns and nested values as JSON. In BigQuery, you then unnest the JSON array with SQL:
1select
2 json_value(item, '$.sku') as sku,
3 sum(
4 cast(json_value(item, '$.qty') as int64)
5 * cast(json_value(item, '$.unit_price') as numeric)
6 ) as revenue
7from shop.orders,
8 unnest(json_query_array(items)) as item
9where status = 'paid'
10group by sku
11order by revenue descIn Snowflake, with the array in a VARIANT column, LATERAL FLATTEN does the same job:
1select
2 f.value:sku::string as sku,
3 sum(f.value:qty::number * f.value:unit_price::number(10, 2)) as revenue
4from shop.orders o,
5 lateral flatten(input => o.items) f
6where o.status = 'paid'
7group by sku
8order by revenue desc;Both return 135, 37.50 and 18. The usual next step is a model that flattens the array once into an order_items table, so analysts query a plain table and never touch the JSON. The document data ends up relational again, which is the point of the next section.
We ran the relational example on DuckDB and SQLite, and the warehouse query logic on DuckDB with the same three orders loaded as JSON. The MongoDB pipeline and the BigQuery and Snowflake syntax were checked against the vendors' documentation but not run on those systems.
Is a data warehouse SQL or NoSQL?
A data warehouse is SQL. BigQuery, Snowflake, Amazon Redshift, Databricks SQL and ClickHouse all store data in tables and are queried with SQL. What sets them apart from an application database like PostgreSQL is how they store it: columnar, each column compressed and stored together, so a query that sums one column over a billion rows reads only that column.
They do borrow one NoSQL idea: semi-structured columns that hold JSON without a fixed schema.
- BigQuery has a
JSONdata type for schema-on-read data, queried with field access (items[0].sku),JSON_VALUEandJSON_QUERY_ARRAY, plusSTRUCTandARRAYtypes for nested data with a fixed schema (BigQuery: working with JSON data). - Snowflake has
VARIANT,OBJECTandARRAYtypes, queried with colon notation (src:customer.name) andFLATTEN(Snowflake: semi-structured data). - Amazon Redshift has a
SUPERtype for the same purpose.
So the honest answer is: a SQL engine with columnar storage that can also hold document-shaped data. That is why a warehouse is where SQL and NoSQL sources end up side by side: the MongoDB orders and the PostgreSQL payments sit in the same warehouse and join in one query. If you are choosing a warehouse, see what a data warehouse is, BigQuery vs Snowflake and PostgreSQL vs BigQuery.
Using SQL and NoSQL together
Most companies above a certain size run several databases, each for the job it does best: PostgreSQL for orders and billing, Redis for sessions and caching, MongoDB or DynamoDB for a catalog or user-generated content, Elasticsearch for search. This is sometimes called polyglot persistence.
It works when two rules hold:
- One system owns each piece of data. The order lives in PostgreSQL; the cache in Redis is a copy that can be thrown away.
- Analytics reads copies, not production. Data from every database is replicated into the warehouse, where it is modeled into tables the business can query. Running reports against a production MongoDB or a busy PostgreSQL primary slows down the application.
For the replication itself, change data capture reads the database's change log instead of re-scanning tables, which keeps deletes and updates accurate with little load on the source.
Syncing SQL and NoSQL databases with Weld
Weld syncs tables from relational and NoSQL databases into your data warehouse. The database sources include PostgreSQL, MySQL and SQL Server in their cloud-hosted variants (such as Amazon RDS, Aurora, Azure and Google Cloud SQL), and the NoSQL databases MongoDB, DynamoDB and Azure Cosmos DB. Databases set up for it can sync with Change Data Capture, which reads inserts, updates and deletes from the database's change log.
For MongoDB, you choose how documents land. In unpacked mode, Weld unpacks one layer: each top-level field becomes a column with an inferred type, and anything nested below that arrives as JSON. The order above lands with columns such as _id, customer_id, status and items, where items holds the array as JSON. In packed mode, each document arrives whole in a single json column next to its id. From there, a SQL data model flattens the items into a table, like the queries above, and joins them with data from your other sources.
Sign up to Weld
FAQ
Structured data with the same fields in every record, clear relationships between records, and a need for consistency: orders, payments, invoices, customers, inventory and bookings. Relational databases enforce keys and constraints, run multi-table transactions safely, and answer new questions with SQL joins.
There are four main types. Document databases such as MongoDB store JSON-like documents; key-value stores such as Redis and DynamoDB store values under a unique key; wide-column stores such as Cassandra group rows by a partition key; and graph databases such as Neo4j store nodes and relationships. Search engines and time-series databases are often counted as NoSQL too.
A data warehouse is SQL: BigQuery, Snowflake and Redshift store data in tables and are queried with SQL. They use columnar storage for fast analytics, and they also have semi-structured types, such as JSON in BigQuery and VARIANT in Snowflake, so they can hold document data from NoSQL sources.
A relational database such as PostgreSQL, MySQL or SQL Server. Transactional data needs ACID guarantees across several tables, so that an order, its items and the stock change are written together or not at all. Distributed SQL databases such as CockroachDB or Spanner keep those guarantees when one server is not enough.
They can store it, but they are not built to query it. PostgreSQL, MySQL and SQL Server have text, binary and JSON column types, and PostgreSQL's jsonb can be indexed. Large files such as images and PDFs are better kept in object storage, with their path and metadata in a relational table.
For JSON documents that an application reads and writes as a unit, a document database such as MongoDB or Couchbase, or PostgreSQL with jsonb if you also need relational tables in the same database. For files such as PDFs or Word documents, use object storage like Amazon S3 and keep the metadata in a database.



