Data Fundamentals 0.4 — Understanding Data Storage

How Databases, Files & Schemas Organize Information

Welcome Back

In the previous articles, we explored:

• 0.1 — How the Data World Works: how data moves through modern organizations.
• 0.2 — Data Careers Explained: the different roles in data.
• 0.3 — Is Data Engineering Right for You?: Whether this career path fits your interests and strengths.

You now understand why companies collect data and who works with it.

But before we start writing SQL queries, building Python pipelines, or working with cloud platforms, we need to answer a more fundamental question:

Where does data actually live?

Every Data Engineer works with concepts like:

• Files
• Databases
• Tables
• Rows and columns
• Schemas
• Data warehouses
• Data lakes
• Lakehouses

These are the building blocks of modern data platforms.

If you understand them, many advanced topics become much easier later. If you skip them, tools like SQL, Spark, Databricks, and cloud services can feel like a collection of unrelated technologies.

By the end of this article, you will understand:

• What data is
• The different ways data can be stored
• How databases organize information
• What tables, rows, and columns are
• What primary and foreign keys do
• What schemas are
• The difference between operational databases and analytical systems
• How all of these concepts fit together in a modern data architecture

Why Data Needs Structure

Imagine an online store receives one million orders today. For each order, the company needs to remember:

• Who the customer is
• What products were purchased
• The potential size of said products
• How much was paid
• When the order was placed
• Where it should be shipped
• Whether it was refunded

That is already millions of pieces of information from a single day. Now multiply that by several years. Companies cannot manage this reliably using random spreadsheets and folders. They need a structured way to store, find, update, and analyze information.

That is why databases and other data storage systems exist.

What Is Data?

At its simplest, data is information that can be stored and processed by a computer.

For example:

Customer

Customer ID: 1001

Name: Anna Jensen

Country: Denmark

In data engineering, we often use a few important terms to discuss data and the way it is formatted:

Entity

An entity is something we want to store information about.

Examples:

• Customer
• Product
• Order
• Payment
• Employee

Attribute

An attribute describes an entity.

Customer attributes:

• Customer ID
• Name
• Email
• Country
• Date of birth

Record

A record is one individual instance of an entity.

One customer record:

Customer ID: 1001

Name: Anna Jensen

Country: Denmark

Later, when we work with tables, records become rows and attributes become columns.

Three Types of Data

Not all data looks the same.

1. Structured Data

Structured data has a predefined format.

Examples include customer databases, sales transactions, and financial records.

2. Semi-Structured Data

Semi-structured data has some organization, but it does not fit neatly into rows and columns.

Example JSON:

JSON

{
  "customerId": 1001,
  "name": "Anna",
  "country": "Denmark"
}

{
  "customerId": 1001,
  "name": "Anna",
  "country": "Denmark"
}

Examples include API responses, application logs, and XML files.

Important: The QuakeFlow project will use JSON data from a real earthquake API.

3. Unstructured Data

Unstructured data has no predefined format.

Examples:

• Images
• Videos
• PDF documents
• Emails
• Audio recordings

Modern organizations often store and analyze all three types of data.

Where Can Data Be Stored?

Data engineers commonly work with four major storage approaches.

1. Files

Common formats:

• CSV
• Excel
• JSON
• Parquet

Advantages:

• Simple to share
• Easy to create
• Portable between systems
• Great for raw data ingestion

Disadvantages:

• Limited data validation
• No built-in relationships
• Harder to manage at large scale
• Can become messy over time

CSV is often the first format beginners encounter.

CustomerID,Name,Country
1001,Anna,Denmark
1002,Michael,Germany

CustomerID,Name,Country
1001,Anna,Denmark
1002,Michael,Germany

2. Databases

A database is an organized collection of data that allows information to be stored, searched, updated, and retrieved efficiently.

Examples:

• PostgreSQL
• SQL Server
• MySQL
• Oracle
• SQLite

Databases are excellent when you need:

• Fast queries
• Multiple users
• Reliable updates
• Data consistency
• Relationships between data

3. Data Warehouses

A data warehouse is a database optimized for analytics and reporting.

Instead of supporting millions of small transactions, it supports questions like:

• What were total sales last quarter?
• Which products are growing fastest?
• Which regions are most profitable?

4. Data Lakes

A data lake stores large amounts of raw data in many formats.

Examples:

• CSV files
• JSON files
• Parquet files
• Logs
• Images
• Sensor data

The idea is to store the data first. Decide how to use it later.

How Databases Organize Data

Inside a relational database, information is organized in a hierarchy.

Database -> Schema -> Table -> Column -> Row -> Value

Example:

EarthquakeDB
  └── staging
      └── earthquakes_raw
          └── magnitude
              └── 5.3

Understanding this hierarchy makes SQL much easier later.

Tables: One Table, One Thing

A table stores information about one specific entity. Some examples:

Keeping tables focused makes systems easier to understand and maintain.

Rows and Columns

Columns

Columns describe what information is stored.

The columns are CustomerID, Name, and Country.

Rows

Rows represent individual records.

Each row is one customer.

Data Types

Databases need to know what kind of data each column contains.

Choosing appropriate data types improves performance and data quality.

Primary Keys: Unique Identifiers

A primary key uniquely identifies each row in a table.

Why not use names? Because names are not guaranteed to be unique. A company could have multiple customers named Anna Jensen.

Primary keys are often automatically generated IDs, or combinations of columns which together are unique.

Foreign Keys: Connecting Tables

A foreign key creates a relationship between tables.

Customers

Orders

The CustomerID in Orders points back to the Customers table.

This allows the database to know which customer placed each order.

Relationships Between Tables

One-to-Many

The most common relationship.

One customer can have many orders.

Customer – 1 -> Orders (Many)

Many-to-Many

One order can contain many products, and one product can appear in many orders.

This is usually solved with a bridge table.

What Is a Schema?

A schema is a way of organizing database objects.

Think of it as a folder inside a database.

sales

• customers

• orders

• products

finance

• payments

• invoices

hr

• employees

Schemas help with:

• Organization
• Security
• Ownership
• Maintenance

A full table name often looks like:

sales.orders

which means the orders table inside the sales schema.

Why Companies Don’t Use One Giant Table

Imagine storing everything together.

The customer information is repeated again and again.

This causes:

• Duplicate data
• Wasted storage
• Inconsistent updates
• Harder maintenance

Separating data into related tables reduces these problems.

Operational Databases vs Analytical Databases

We covered this in an earlier article, but let’s cover the essentials again:

Operational Databases (OLTP)

Purpose: Run the business.

Examples:

• Place an order
• Process a payment
• Update a customer address
• Book a flight

Characteristics:

• Many small transactions
• Fast writes
• Current data
• Used by applications

To make these systems optimised for inserting data, tables are often heavily normalised. This means that each entity has its own table, without including information on other entities. Instead, information of other entities are joined onto the table by using SQL and foreign keys.

Analytical Databases (OLAP)

Purpose: Analyze the business.

Examples:

• Total sales by month
• Revenue by country
• Customer lifetime value
• Trend analysis

Characteristics:

• Large queries
• Historical data
• Read-heavy workloads
• Used by analysts and BI tools

To improve performance for analytical use cases, tables are often denormalized. This reduces the amount of joins needed when analyzing data, and thus increasing performance.

Data Warehouses

A data warehouse is an analytical system designed for reporting and business intelligence.

Data is usually copied from operational systems into the warehouse, where it is cleaned, standardized, and organized for analysis.

Benefits:

• Historical data
• Consistent business definitions
• Better performance for reporting
• Centralized analytics

Data Lakes

A data lake stores large amounts of raw data in many formats.

Examples:

• JSON from APIs
• CSV exports
• Application logs
• Sensor data
• Parquet files

Data lakes are especially useful when you want to keep the original data before deciding how to transform it.

Lakehouses

Modern platforms often combine the flexibility of a data lake with the reliability of a data warehouse.

This approach is called a lakehouse.

Benefits include:

• Large-scale storage
• Transaction support
• Reliable tables
• Analytics performance
• Support for many data formats

Technologies in this space include Delta Lake, Apache Iceberg, and Apache Hudi.

How Everything Fits Together

Let’s combine everything we have learned.

Applications, APIs, CSV files, Sensors

Data Pipeline

Data Lake (raw data)

Transformations (SQL / Python)

Data Warehouse / Lakehouse

Power BI / Analytics

Each component has a different responsibility.

How This Connects to QuakeFlow

In the next phase of this series, we will build QuakeFlow, an end-to-end data engineering project using real earthquake data.

The flow will look like this:

USGS Earthquake API

Python ingestion

Raw JSON storage

Database tables

SQL transformations

Analytical model

Power BI dashboard

Notice how almost every concept from this article appears somewhere in that pipeline.

That is the point of the Fundamentals phase: understand the pieces before you build the system.

Key Takeaways

• Data can be stored in files, databases, warehouses, and lakes.
• Tables organize information into rows and columns.
• Data types tell databases what kind of values a column contains.
• Primary keys uniquely identify records.
• Foreign keys connect tables together.
• Schemas organize related database objects.
• Operational databases run the business.
• Data warehouses support analytics.
• Data lakes store large amounts of raw data.
• Lakehouses combine ideas from warehouses and lakes.
• Data Engineers build the systems that connect all of these pieces together.

What Comes Next?

You now understand where data lives and how it is organized.

The next step is understanding how data moves between systems.

In Data Fundamentals 0.5 — Data Pipelines Explained, we will learn:

• What a data pipeline is
• ETL vs ELT
• Batch vs streaming
• Ingestion and transformation
• Orchestration
• Monitoring
• Data quality

Before building QuakeFlow, you need to understand the journey data takes.

Understand the storage. Next, understand the movement.

That is the foundation of Data Engineering.