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 modelTables of rows and columns, linked by keysDocuments, key-value pairs, wide-column rows or graphs
SchemaDefined up front, enforced by the databaseFlexible; often enforced by the application, if at all
Query languageSQL, standard across databasesSpecific to each database (MongoDB query API, CQL, Cypher)
RelationshipsJoins across tablesEmbedded in the record, or followed as graph edges
TransactionsACID across many tables by defaultVaries: often per record, multi-record in some databases
ScalingMostly vertical, horizontal with extra workBuilt to spread data across many servers
ExamplesPostgreSQL, MySQL, SQL Server, OracleMongoDB, 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

TypeHow it stores dataReal databasesGood atWeak at
DocumentJSON-like documents, nested fields and arrays, grouped in collectionsMongoDB, Couchbase, FirestoreRecords with varying fields read as one unit: catalogs, profiles, contentQueries across many documents and joins
Key-valueA value (string, blob, small structure) stored under a unique keyRedis, Amazon DynamoDB, MemcachedVery fast lookups by key: caching, sessions, carts, rate limitsAnything that is not a lookup by key
Wide-columnRows grouped by a partition key, each row with its own set of columnsApache Cassandra, ScyllaDB, HBase, Google BigtableHuge write volumes spread across servers and regions: events, IoT, messagesAd hoc queries outside the partition key
GraphNodes and relationships (edges), both with propertiesNeo4j, Amazon NeptuneFollowing relationships many hops deep: fraud rings, recommendations, permissionsBulk 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

WorkloadBest fitExample databasesWhy
Transactions (OLTP): orders, payments, bookings, inventoryRelationalPostgreSQL, MySQL, SQL ServerACID transactions across tables, constraints that reject bad data
Analytics (OLAP): reporting, BI, metrics over historyColumnar SQL data warehouseBigQuery, Snowflake, Amazon Redshift, ClickHouseScans a few columns over billions of rows quickly; joins across sources
Documents: product catalogs, CMS content, user profiles with varying fieldsDocument database, or PostgreSQL JSONBMongoDB, Couchbase, PostgreSQLThe record is read and written as one unit; fields can differ per record
Caching and sessionsIn-memory key-valueRedis, MemcachedSub-millisecond reads by key; data can be rebuilt if lost
High-volume key lookups at scaleManaged key-value or documentDynamoDBPredictable latency at any size when every query is by key
Time series: metrics, IoT readingsTime-series databaseTimescaleDB, InfluxDBFast inserts in time order, rollups and retention by age
Graph: fraud rings, recommendations, access rightsGraph databaseNeo4j, Amazon NeptuneTraversing many hops is a single query instead of many self-joins
Full-text searchSearch engineElasticsearch, OpenSearchRelevance ranking, fuzzy matching, facets
Massive writes across regions: events, messagesWide-columnCassandra, ScyllaDB, BigtableWrites 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.

The same order stored two ways. Relational: an orders table with order_id 1042, customer_id 7 and status paid, and an order_items table with two rows for order 1042, sku A1 quantity 2 at 45.00 and sku B7 quantity 3 at 12.50; reading the order means joining on order_id. Document: one document in an orders collection with _id 1042, customer_id 7, status paid and an items array holding the same two items; one read returns the whole order.

Relational: two tables and a join

SQL
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:

SQL
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;
skuproduct_namerevenue
A1Desk lamp135.00
B7Notebook37.50
C3Pen set18.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:

JSON
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:

JavaScript
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_message to 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 table statements are enforced by the database; in the document model, the application has to validate a missing qty or a string price.
  • Duplication: the document copies name into 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:

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 desc

In Snowflake, with the array in a VARIANT column, LATERAL FLATTEN does the same job:

SQL
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 JSON data type for schema-on-read data, queried with field access (items[0].sku), JSON_VALUE and JSON_QUERY_ARRAY, plus STRUCT and ARRAY types for nested data with a fixed schema (BigQuery: working with JSON data).
  • Snowflake has VARIANT, OBJECT and ARRAY types, queried with colon notation (src:customer.name) and FLATTEN (Snowflake: semi-structured data).
  • Amazon Redshift has a SUPER type 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:

  1. One system owns each piece of data. The order lives in PostgreSQL; the cache in Redis is a copy that can be thrown away.
  2. 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

Ready to start transforming your data into insights? Get started with Weld for free today.

FAQ