---
title: "09 — Databases and Big Data — From SQL to Spark"
uid: databases-and-big-data
tags: ["sql", "mapreduce", "nosql", "etl", "roadmap:software-architect", "hadoop", "spark", "data-warehouse", "databases"]
excerpt: "From transactional storage to analytical processing: every database type is optimized for a point on that spectrum, not 'better' in the abstract. Place the workload, then the choice gets honest."
date: 2026-08-13T03:27:41+0000
source: https://www.aveshina.my.id/en/blog/databases-and-big-data
---

SQL versus NoSQL as rivals was how I compared databases, and the rivalry hid the real structure. The model that made the cluster coherent: **there's a spectrum from transactional storage to analytical processing, and every tool on it is optimized for a point on that spectrum, not "better" in the abstract** [1]. Once I stopped comparing SQL to NoSQL as if they were rivals and started placing them on the spectrum by workload, the choices got honest.

Working with data covers the tools and concepts an architect needs to design systems that store, process, and analyze large amounts of information — choosing database types, understanding data-processing frameworks, and knowing how analytics fits in [1]. Data-heavy systems require different architectural trade-offs than typical transactional applications.

## Transactional storage: SQL and NoSQL

The left end of the spectrum is **transactional storage** — the systems that hold the application's live data and answer the queries the app makes on every request.

**SQL** (Structured Query Language) is used for **relational databases** — a collection of tables storing structured data, with a fixed schema and relationships between tables [2]. MySQL, PostgreSQL, MariaDB are the examples. SQL's strength is strong consistency, expressive querying with joins, and ACID transactions (tying back to the consistency notes). It's the right default when data is naturally relational and consistency matters.

**NoSQL** databases store data in flexible formats — documents, key-value pairs, or graphs — without requiring a fixed schema [3]. They often trade strict consistency for better scalability and performance (the CAP/AP side of the earlier notes). Architects choose NoSQL when the data model doesn't fit neatly into relational tables or when horizontal scaling is a priority [3]. A document store like MongoDB fits semi-structured data; a key-value store fits simple lookups at massive scale; a graph database fits relationship-heavy queries like social or fraud graphs.

```figure
<svg viewBox="0 0 680 250" xmlns="http://www.w3.org/2000/svg" class="my-6 w-full max-w-2xl" role="img" aria-label="SQL versus NoSQL. Left: SQL — fixed schema, tables with rows and columns, strong consistency, joins, ACID. Right: NoSQL — flexible schema, documents or key-value pairs, horizontal scaling, eventual consistency often. The choice depends on data shape and scaling needs, not on one being universally better.">
  <g font-family="ui-sans-serif, system-ui, sans-serif" text-rendering="geometricPrecision">
    <!-- SQL -->
    <text x="170" y="28" font-size="13" font-weight="700" fill="#134e4a" text-anchor="middle">SQL — relational</text>
    <rect x="60" y="44" width="220" height="180" rx="10" fill="#ccfbf1" stroke="#0d9488" stroke-width="1.5"/>
    <g font-size="10" fill="#134e4a" text-anchor="middle">
      <text x="170" y="70">fixed schema · tables, rows, columns</text>
      <text x="170" y="92">strong consistency · ACID</text>
      <text x="170" y="114">expressive queries · joins</text>
      <text x="170" y="136">e.g. PostgreSQL, MySQL, MariaDB</text>
      <text x="170" y="170" font-style="italic" fill="#0f766e">fit: naturally relational data,</text>
      <text x="170" y="186" font-style="italic" fill="#0f766e">consistency matters</text>
    </g>

    <!-- NoSQL -->
    <text x="510" y="28" font-size="13" font-weight="700" fill="#1e1b4b" text-anchor="middle">NoSQL — non-relational</text>
    <rect x="400" y="44" width="220" height="180" rx="10" fill="#e0e7ff" stroke="#6366f1" stroke-width="1.5"/>
    <g font-size="10" fill="#1e1b4b" text-anchor="middle">
      <text x="510" y="70">flexible schema · documents / KV / graph</text>
      <text x="510" y="92">horizontal scaling · often eventual consistency</text>
      <text x="510" y="114">trade strict consistency for scale</text>
      <text x="510" y="136">e.g. MongoDB, DynamoDB, Neo4j</text>
      <text x="510" y="170" font-style="italic" fill="#3730a3">fit: non-tabular data shape,</text>
      <text x="510" y="186" font-style="italic" fill="#3730a3">massive scale priority</text>
    </g>
  </g>
</svg>
```

The choice is about fit, not ranking. A ledger wants SQL; a user-profile document with varying fields wants NoSQL; many systems use both, each for the workload it suits.

## Moving data in: ETL and the data warehouse

Once you have transactional systems, the next concern is **analytics** — asking questions across all that operational data without slowing the live systems down. **ETL (Extract, Transform, Load)** is the process that enables this: it **extracts** data from original sources, **transforms** it (cleaning, deduplication, combining), and **loads** it into a target database — typically a **data warehouse** — where it's ready for analysis [4].

A **data warehouse** is storage optimized for **analytical queries** rather than transactional workloads [5]. This is the OLAP (online analytical processing) end of the spectrum, distinct from the OLTP (online transaction processing) end where SQL/NoSQL live. The warehouse uses modeling concepts like **star and snowflake schemas** — layouts that put a central **fact table** (the numbers you measure) in the middle and **dimension tables** (the labels you slice the numbers by) around it — plus strategies for handling historical data changes [5]. The point is reliable reporting and business intelligence over large volumes of historical data — the kind of workload that would cripple a transactional database.

## Processing at scale: Hadoop, MapReduce, Spark

When the data is too large for one machine, you need **distributed processing frameworks**. These span clusters of machines and split the work across nodes.

- **MapReduce** is the original model — processing splits into a **map** step (transform each record) and a **reduce** step (aggregate), distributed across nodes, with intermediate results written to disk between steps [6].
- **Hadoop** is the open-source framework bundling distributed storage (**HDFS**) with the MapReduce processing model. It remains common in enterprises with established big-data infrastructure [6].
- **Spark** improves on MapReduce with **in-memory** processing — keeping intermediate results in memory instead of writing them to disk — making it significantly faster for many workloads. It supports batch, streaming, machine learning, and graph processing through one unified framework [6][7].

```
MapReduce:   map → (write to disk) → reduce → (write to disk) → ...
Spark:       map → (keep in memory) → reduce → (keep in memory) → ...
```

Architects consider these tools when a system needs to process data at a scale a single machine cannot handle [6]. The choice between MapReduce/Hadoop and Spark often comes down to whether the workload can benefit from in-memory speed — most modern pipelines lean Spark for that reason — but Hadoop's storage layer (HDFS) is still widely paired with faster engines.

## How I use this

I place each data concern on the spectrum first. Live application state wants transactional storage (SQL unless the data shape or scale pushes to NoSQL). Cross-system reporting wants a warehouse fed by ETL, kept physically separate from the transactional databases so analytics never starves the app. When volumes exceed one machine, the processing framework (Spark first, Hadoop where it's already established) takes over. The mistake I watch for is forcing one tool across the whole spectrum — running analytics on the transactional database, or trying to make a warehouse serve live app traffic.

## References

[1] "Working with Databases," roadmap.sh — Software Architect. [Online]. Available: [https://roadmap.sh/software-architect/working-with-data](https://roadmap.sh/software-architect/working-with-data)

[2] Amazon Web Services, "What is SQL?," [Online]. Available: [https://aws.amazon.com/what-is/sql/](https://aws.amazon.com/what-is/sql/)

[3] Amazon Web Services, "NoSQL Databases," [Online]. Available: [https://aws.amazon.com/nosql/](https://aws.amazon.com/nosql/)

[4] Snowflake, "What is ETL?," [Online]. Available: [https://www.snowflake.com/guides/what-etl](https://www.snowflake.com/guides/what-etl)

[5] Toptal, "Data Warehouse Concepts and Principles," [Online]. Available: [https://www.toptal.com/data-science/data-warehouse-concepts-principles](https://www.toptal.com/data-science/data-warehouse-concepts-principles)

[6] Integrate.io, "Spark vs Hadoop MapReduce," [Online]. Available: [https://www.integrate.io/blog/apache-spark-vs-hadoop-mapreduce](https://www.integrate.io/blog/apache-spark-vs-hadoop-mapreduce)

[7] The Apache Software Foundation, "Apache Spark," [Online]. Available: [https://spark.apache.org/](https://spark.apache.org/)

```quiz
Q: When would an architect favor NoSQL over SQL?
- When the data is naturally relational and strong consistency matters
- When the data shape doesn't fit tables or horizontal scaling is the priority
correct: 1
explain: NoSQL fits non-tabular data (documents, key-value, graph) or workloads where horizontal scaling matters more than strict consistency. SQL is the default for relational, consistency-sensitive data.

Q: What does ETL stand for, and what does it do?
- Execute, Test, Log — runs and validates database transactions
- Extract, Transform, Load — moves and cleans data from sources into a target like a data warehouse
correct: 1
explain: ETL extracts data from source systems, transforms it (clean, dedupe, combine), and loads it into a target optimized for analysis, decoupling analytics from live transactional systems.

Q: A data warehouse is optimized for…
- online transactional processing (OLTP) on live app data
- online analytical processing (OLAP) over large volumes of historical data
correct: 1
explain: Data warehouses use star/snowflake schemas and fact/dimension tables to serve analytical queries — the opposite workload from a transactional database, which is why the two are kept separate.

Q: What is Spark's main advantage over MapReduce?
- It uses a fixed schema like SQL
- It performs processing in memory, making it much faster for many workloads
correct: 1
explain: Spark keeps intermediate results in memory instead of writing them to disk between steps like MapReduce does, which is dramatically faster for most modern pipelines.

Q: Hadoop's storage layer (HDFS) is often…
- replaced by SQL databases immediately
- paired with faster processing engines like Spark while remaining in established big-data setups
correct: 1
explain: HDFS remains common as a storage layer in enterprises; teams often pair it with Spark rather than Hadoop's native MapReduce for the speed gain.
```