Relational Database in Big Data: Still at the Center

The relational database in big data projects is still where most businesses keep their orders, payments and accounts. Most analytics platforms get their core business data from there.

Is the Relational Database Obsolete?

Does big data mean the relational database is obsolete? People still ask, and my answer isn’t “it depends”. Anyone who wants to work with big data should learn relational databases first.

The reason is simple. Much of the data that matters most is created by applications. Many of them write it to a relational database first. Much of what you analyze later starts there. Learning the relational database in big data work first pays off. The SQL you write carries over to warehouses, lakes and Spark SQL.

What the Relational Database Does Well

Start with transactions. When a customer pays, the order, the payment and the stock change must all succeed or all fail. That’s ACID, which stands for atomicity, consistency, isolation and durability. Other kinds of databases offer transactions too, some with limits. A relational database makes it the default.

Next come constraints. A constraint is a rule the database enforces itself. A foreign key, for example, blocks an order for a customer who doesn’t exist. A check constraint works the same way, so a total below zero never gets in. A bug in the app can’t break either rule, because the database says no.

Last are many small writes and open questions. An order arrives every second, and each one is a tiny, safe write. Later, someone asks for sales by city on weekends. SQL answers that, even when nobody planned an index for it.

SQL itself is part of the reason. Nearly every programming language has a driver for it, and most developers already know it. A question that takes one query in SQL can take pages of code in other tools.

Where It Sits in a Modern Platform

Follow the data around the picture. Apps write to the relational database. Change data capture then copies every insert, update and delete out to a data lake or warehouse. It reads the transaction log after each commit, so the app doesn’t wait for the copy to finish. The copy isn’t free, though. Capture shares the server’s CPU and I/O, and a capture job that falls behind keeps log space in use. Watch its lag.

Analysts and models work on the copy. Their results flow back into the database so the app can use them. A recommendation or a risk score is a typical example. The database is the system of record, the one place that says what is true right now.

A Flexible Schema Inside a Table

The NoSQL movement had two selling points: scale across many machines and a flexible schema. SQL Server 2025 answers the second with a json data type. It stores JSON in a binary format and checks that the text is valid. You still query it with SQL.

The script below creates a database called SqlBigDataRelational, used only for this example, so run it on a test server. Customers own orders, and the rules are easy to state as keys and checks. A bookstore like this fits the relational model well. Customer and CustomerOrder are ordinary tables with keys and a check. OrderEvent keeps whatever the app sends in a json column, so each event can carry different fields.

IF DB_ID(N'SqlBigDataRelational') IS NULL CREATE DATABASE SqlBigDataRelational;
GO
USE SqlBigDataRelational;
GO
DROP TABLE IF EXISTS dbo.OrderEvent;
DROP TABLE IF EXISTS dbo.CustomerOrder;
DROP TABLE IF EXISTS dbo.Customer;
CREATE TABLE dbo.Customer
(
    CustomerID int NOT NULL PRIMARY KEY,
    FullName nvarchar(60) NOT NULL,
    City nvarchar(40) NOT NULL
);
CREATE TABLE dbo.CustomerOrder
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL CONSTRAINT FK_CustomerOrder_Customer REFERENCES dbo.Customer (CustomerID),
    OrderDate date NOT NULL,
    Total decimal(10,2) NOT NULL CONSTRAINT CK_CustomerOrder_Total CHECK (Total >= 0)
);
CREATE TABLE dbo.OrderEvent
(
    EventID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OrderID int NOT NULL CONSTRAINT FK_OrderEvent_CustomerOrder REFERENCES dbo.CustomerOrder (OrderID),
    Payload json NOT NULL
);
INSERT INTO dbo.Customer (CustomerID, FullName, City)
VALUES (1, N'Maya Rivera', N'Austin'), (2, N'Sam Okafor', N'Boston'), (3, N'Lena Park', N'Seattle');
INSERT INTO dbo.CustomerOrder (OrderID, CustomerID, OrderDate, Total)
VALUES (101, 1, '20261001', 18.50), (102, 1, '20261003', 42.00), (103, 2, '20261002', 9.75), (104, 3, '20261004', 27.25);
INSERT INTO dbo.OrderEvent (OrderID, Payload)
VALUES (101, N'{"type":"page_view","page":"/tea"}'),
       (102, N'{"type":"coupon","code":"SAVE10","percent":10}'),
       (102, N'{"type":"delivery","carrier":"BikeCo","stops":3}'),
       (104, N'{"type":"page_view","page":"/honey"}');

Now break the rules on purpose. The first insert is an order for customer 99, who doesn’t exist. The second has a negative total.

INSERT INTO dbo.CustomerOrder (OrderID, CustomerID, OrderDate, Total) VALUES (105, 99, '20261005', 10.00);
INSERT INTO dbo.CustomerOrder (OrderID, CustomerID, OrderDate, Total) VALUES (106, 2, '20261005', -5.00);

The Messages tab shows two errors. It is output, not code to run.

Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CustomerOrder_Customer". The conflict occurred in database "SqlBigDataRelational", table "dbo.Customer", column 'CustomerID'.
The statement has been terminated.
Msg 547, Level 16, State 1, Line 2
The INSERT statement conflicted with the CHECK constraint "CK_CustomerOrder_Total". The conflict occurred in database "SqlBigDataRelational", table "dbo.CustomerOrder", column 'Total'.
The statement has been terminated.

The database refused both. The relational rows stay protected, even though a table in the same database holds loose JSON.

Notice that both constraint errors name the rule that fired. That’s why it pays to name your constraints instead of letting SQL Server invent a name with random digits. A named rule tells whoever reads the log what went wrong.

Now the useful part. One query joins relational columns with values read from the JSON. JSON_VALUE reads one value by its path. Events carry different fields, so COALESCE picks whichever one exists.

SELECT c.FullName, o.OrderID, o.Total, JSON_VALUE(e.Payload, '$.type') AS EventType,
       COALESCE(JSON_VALUE(e.Payload, '$.page'), JSON_VALUE(e.Payload, '$.code'), JSON_VALUE(e.Payload, '$.carrier')) AS Detail
FROM dbo.Customer AS c
JOIN dbo.CustomerOrder AS o ON o.CustomerID = c.CustomerID
JOIN dbo.OrderEvent AS e ON e.OrderID = o.OrderID
ORDER BY o.OrderID, e.EventID;

Order 102 shows two events with different shapes, a coupon and a delivery. Order 103 has no events, so the inner join leaves it out. The customer and order columns are strict and typed, and the events stay loose.

Data also flows the other way. JSON_OBJECTAGG packs rows into one JSON object (the order of keys inside it can vary). That suits an app or an API that wants a single document per customer.

SELECT c.FullName, JSON_OBJECTAGG(CAST(o.OrderID AS varchar(10)) : o.Total) AS OrderTotals
FROM dbo.Customer AS c
JOIN dbo.CustomerOrder AS o ON o.CustomerID = c.CustomerID
GROUP BY c.FullName
ORDER BY c.FullName;

That’s what the json type buys. You get one query, one transaction boundary and no second system to keep in sync with the first.

When a Document Store Fits Better

Some will say a document database fits the events better, and that’s fair when the events are the main data. When every record is a free-form document and no rule ties it to anything else, a relational table adds little.

Most business data has rules, though. Orders belong to customers, and totals can’t go below zero. The json column lets you keep both worlds in one database. When one server stops being enough, I cover the next step in What Is NewSQL? Distributed SQL Databases Explained.

What to Remember

The relational database in big data is usually the system of record. It gives you transactions, rules and fast small writes. The platform around it copies its data out and sends results back.

A good first question about any data platform is where the data is created. The next is which system says what is true. That system is usually relational. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlBigDataRelational SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlBigDataRelational;

The relational database is not the old way, it is the center the new ways connect to.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.