A customer calls support to update their phone number. The agent changes it in the billing spreadsheet, but the shipping sheet and the marketing list still hold the old number. Three weeks later a package ships to a stranger, the customer is furious, and nobody can say which copy of the number was correct.
That failure has nothing to do with bad employees. It is what happens when data lives in disconnected files with no single source of truth. The whole history of data systems is a series of answers to that one problem, asked at larger and larger scale.
This post walks the progression from flat files to relational databases, data warehouses, data lakes, and the vector storage that powers AI search. Each step exists because the previous one broke under new pressure.
TLDR
- Flat files fail at scale because duplicated data drifts out of sync and has no shared rules.
- Relational databases add persistence, concurrency, integrity, security, and fast retrieval as guarantees, not luck.
- Different data shapes (structured, semi-structured, unstructured) need different homes: databases, warehouses, and data lakes.
- AI search adds a new requirement, retrieval by meaning, which is why vector databases now sit alongside the rest of the stack.
This is Part 1 of a two-part series. Part 1 covers why each storage layer exists. Part 2 goes under the hood into how these systems actually work.
The world before databases#
Before databases existed, organizations stored information in flat files, spreadsheets, and manual record systems. A company might keep employee details in one file, salaries in another, and department information somewhere else.
At first this seemed manageable. As businesses grew, the same systems started failing in predictable ways.
The problem with flat files
If a customer changes their phone number in an e-commerce system that uses separate Excel files (customers.xlsx, orders.xlsx), updating one file but forgetting another creates inconsistent data.
The recurring problems with file-based systems looked like this:
| Problem | Explanation |
|---|---|
| Data duplication | Same data repeated multiple times across files |
| Inconsistency | Different files contain conflicting information |
| Poor security | Hard to control user-specific permissions |
| No concurrent access | Multiple edits at once may corrupt data |
| Slow retrieval | Finding specific information becomes highly inefficient |
The need for centralized, reliable, and scalable storage eventually led to the Database Management System (DBMS).
Why databases exist#
A database exists to solve one challenge: storing large amounts of information reliably and retrieving it instantly.
Consider a banking system. When you transfer ₹10,000 to another account, several things must happen correctly without fail:
- Money must leave your account.
- Money must enter the receiver's account.
- No duplicate transaction should occur.
- Data must stay correct even if power fails halfway through.
A plain file system cannot guarantee these conditions. Modern databases provide them through a few core properties:
- Persistence: Data stays saved permanently, even after a system restarts.
- Concurrency: Many users can access the system at the same time, safely.
- Integrity: Rules block invalid data before it lands (for example, age cannot be negative).
- Security: Different users get different roles and permissions.
- Speed: Optimized search finds records instantly across billions of rows.
Understanding different types of data#
Not all data looks the same. Some is highly organized, and some is messy and unpredictable. The shape of the data decides where it should live.
Structured data#
Structured data follows a predefined, rigid format. It fits cleanly into rows and columns.
| ID | Product | Price |
|---|---|---|
| 1 | Laptop | $1,200 |
Semi-structured data#
Semi-structured data is partially organized. It does not use rigid tables, but it carries tags or keys, like JSON:
{
"name": "Ephizen Developer",
"skills": ["AI", "React", "PostgreSQL"]
}
Unstructured data#
Unstructured data has no predefined format at all. Images, videos, PDFs, and raw audio are common examples. This kind of data usually lands in systems called data lakes.
The journey of data in modern systems#
Modern systems are no longer a single database. Data travels through an entire ecosystem, where each stage is tuned for a different job. The progression below mirrors how storage technology itself evolved, from flat files to AI-era vector stores.
Inside a single modern company, that same spread of systems runs side by side:
Operational Data
User actions (uploads, purchases) hit the Database (OLTP).
Business Analytics
Cleaned, structured data is sent to a Data Warehouse (Snowflake, BigQuery) to power reports and dashboards.
Massive Raw Data
Logs, images, and raw CSV files are dumped into a cheap, highly scalable Data Lake (Amazon S3).
The Lakehouse
The Data Lakehouse (Databricks) is a hybrid system. It pairs the cheap storage of a data lake with the analytical performance of a data warehouse.
AI and the future of data systems#
AI introduced a storage need the earlier layers were never built for. Traditional databases search by exact keyword match. AI systems search by meaning.
If a user asks "I forgot my account access", the system can retrieve "How to reset password" because it understands the intent behind the words, not just the literal characters.
That works by converting text into numerical arrays called embeddings and storing them in vector databases (Pinecone, Milvus, or PostgreSQL with pgvector). The retrieval flow looks like this:
Key Takeaways#
- Flat files break at scale because duplicated copies drift apart and share no rules.
- Databases turn persistence, concurrency, integrity, security, and speed into guarantees instead of hopes.
- Data shape decides storage: structured data fits tables, unstructured data fits data lakes.
- A modern stack runs OLTP databases, warehouses, and data lakes together, each tuned for a different job.
- AI search needs retrieval by meaning, which is why vector databases now sit beside the rest of the stack.
FAQ#
Why not just use spreadsheets for everything?
Spreadsheets work until data is duplicated across files or edited by many people at once. At that point copies drift apart, there is no shared source of truth, and concurrent edits can corrupt records. A database centralizes the data and enforces rules so every reader sees the same correct version.
What is the difference between a data warehouse and a data lake?
A data warehouse stores cleaned, structured data optimized for analytics and reporting. A data lake stores raw data of any shape (logs, images, CSVs) cheaply, before it is processed. Warehouses favor query performance, while lakes favor low-cost scale and flexibility.
When do I actually need a vector database?
You need one when you want to search by meaning rather than exact keywords, for example semantic search, recommendations, or retrieval for an AI assistant. If exact-match lookups and filters cover your needs, a relational database is simpler and enough.
Can a relational database handle unstructured data?
It can store files or blobs, but it is not built to search them by content the way a data lake plus a vector store can. Many teams keep the relational database for transactions and add a lake and vector store for raw and semantic workloads.
What is covered in Part 2?
Part 2 goes under the hood: how these systems work, the difference between OLTP and OLAP, and the ACID versus BASE tradeoffs engineers navigate. Read it at Part 2.
