09 — Databases and Big Data — From SQL to Spark

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.

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

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

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

[4] Snowflake, "What is ETL?," [Online]. Available: 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

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

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