AWS Database Types, Workloads, and Service Selection – SAP-C02 Study Guide

Purpose of This Lesson

AWS database selection starts with understanding two independent dimensions:

1. Data model: relational or non-relational.
2. Workload: operational/transactional or analytical.

The correct AWS service depends on schema flexibility, query patterns, transaction requirements, scaling behavior, latency, operational control, and whether the workload is serving an application or analyzing accumulated data.

Key Concepts

Relational databases

Relational databases organize data into tables containing rows and columns. They use a defined schema and SQL for data definition, querying, inserts, updates, and deletes.

They are a strong fit when an application requires:

• Structured and predictable data.
• Relationships between entities.
• Complex queries and joins.
• Referential integrity and transaction semantics.
• Familiar SQL tooling and database engines.

Relational systems commonly scale vertically by assigning more CPU, memory, or storage performance to the database instance. Read scaling can often be added with read replicas, caching, or application changes. Splitting a relational data model across databases can provide horizontal scale, but introduces distributed joins and greater application complexity.

AWS relational options include Amazon RDS and Amazon Aurora. RDS supports engines such as PostgreSQL, MySQL, MariaDB, Oracle, and SQL Server. Aurora is an AWS-designed relational engine compatible with MySQL or PostgreSQL.

Non-relational databases

Non-relational, or NoSQL, databases use models other than the traditional table-and-relationship model. Common models include:

• Key-value: an item is retrieved using a unique key.
• Document: records contain semi-structured documents, often with varying attributes.
• Wide-column: data is organized for high-scale access by partition and clustering keys.
• Graph: entities and their relationships are first-class data structures.

NoSQL databases generally provide flexible schemas and horizontal scaling. This does not mean that the data has no structure: the application must still define valid attributes, access patterns, and data relationships. With services such as DynamoDB, the design is usually driven by known query patterns rather than ad hoc relational joins.

AWS examples include Amazon DynamoDB for key-value and document workloads, Amazon Neptune for graph workloads, and Amazon ElastiCache for in-memory data access.

Operational versus analytical workloads

These are workload classifications, not mutually exclusive database categories. A relational or non-relational system may be used operationally, while analytical systems may use relational warehouse structures or distributed processing technologies.

#### Operational databases: OLTP

Online transaction processing systems support application transactions such as placing orders, updating account balances, or recording inventory. They typically require:

• Low-latency reads and writes.
• Short, frequent transactions.
• Concurrent access.
• Transaction isolation and correctness.
• Efficient queries for known application operations.

Amazon RDS, Aurora, and DynamoDB are common AWS choices for operational workloads.

#### Analytical databases: OLAP

Online analytical processing systems support complex queries over large volumes of historical or aggregated data. They are used for reporting, trend analysis, business intelligence, and exploratory analysis.

Analytical queries often scan many rows or columns and may run longer than application transactions. A common architecture extracts data from operational systems, loads it into an analytical store, and runs reporting queries there rather than against the production database.

Amazon Redshift is AWS’s managed data warehouse service for analytical SQL workloads. Amazon EMR can process large datasets using distributed frameworks, but it is a data-processing platform rather than a direct replacement for every database or data warehouse.

Relational and non-relational are separate from OLTP and OLAP

For example:

• Aurora is relational and commonly used for OLTP.
• Redshift is relational in its SQL warehouse model and is designed for OLAP.
• DynamoDB is non-relational and commonly used for OLTP-style application access.
• A graph database such as Neptune is non-relational and operationally optimized for relationship queries.

Avoid treating “SQL” as synonymous with “transactional” or “NoSQL” as synonymous with “analytical.” Choose based on the actual workload.

Graph databases

Graph databases model entities as nodes and relationships as edges. Nodes and edges can have properties. This is useful when the primary question involves traversing relationships, such as:

• Social connections.
• Recommendation paths.
• Fraud rings.
• Identity and authorization relationships.
• Network topology.

Amazon Neptune is designed for graph workloads. A relational database can represent relationships, but repeated multi-hop traversals may be more natural and efficient in a graph model.

Database on Amazon EC2

Running a database on EC2 provides the greatest infrastructure-level control. It may be appropriate when:

• The required database engine is not supported by Amazon RDS or Aurora.
• A vendor requires a particular installation or operating system configuration.
• Specialized extensions, plugins, or filesystem behavior are needed.
• The team must control the database and operating system directly.

The tradeoff is operational responsibility. The team must handle tasks that managed services normally simplify, including patching, backups, replication, failover design, monitoring, storage configuration, and recovery testing.

ElastiCache

Amazon ElastiCache provides managed in-memory data stores, including Redis OSS and Memcached options. It is commonly placed in front of a database to reduce latency and database load.

Typical uses include:

• Caching frequently read data.
• Storing session data.
• Maintaining short-lived application state.
• Supporting counters, queues, or other Redis-compatible use cases.

Caching requires an explicit consistency and invalidation strategy. Cached data can become stale, and a cache should not automatically be treated as the system of record. ElastiCache can also operate as a primary data store for suitable use cases, but that decision requires careful durability, availability, persistence, and recovery analysis.

Exam-Relevant Takeaways

• Use Amazon RDS or Aurora when the workload needs a relational schema, SQL, joins, and transactional consistency.
• Use DynamoDB when the workload needs managed horizontal scale, low-latency key-value or document access, and access patterns that can be designed around partition keys and indexes.
• Use Amazon Neptune when traversing relationships is the core requirement.
• Use Amazon Redshift for data warehousing and complex analytical queries over consolidated data.
• Use ElastiCache to accelerate repeated access and reduce load on a backing database; it is not automatically a database replacement.
• Use a database on EC2 when maximum control or an unsupported engine outweighs the operational burden.
• Keep OLTP traffic separate from OLAP workloads so reporting queries do not degrade production transactions.
• A read replica, cache, or warehouse solves a different problem from a Multi-AZ deployment. Multi-AZ primarily improves availability and failover, not general read scaling.
• A flexible schema does not eliminate the need for data modeling. DynamoDB design must begin with access patterns, partition-key distribution, item size, indexes, and throughput behavior.

Architecture Decision Guide

Requirement	Likely AWS choice	Why	Important tradeoff
Structured data, SQL, joins, and ACID transactions	Amazon RDS or Aurora	Managed relational database capabilities	Scaling and schema changes require relational design considerations
AWS-managed relational database with high availability and strong performance	Aurora or an appropriate RDS engine	Managed operations and engine-specific capabilities	Engine compatibility, cost, and feature differences must be evaluated
Key-value or document access at large scale	DynamoDB	Managed horizontal scaling and low-latency access	Query patterns and partition-key design are critical
Multi-hop relationship queries	Neptune	Graph-native nodes, edges, and traversals	Specialized service and data model
Centralized warehouse for historical analytics	Redshift	Columnar analytical processing and SQL	Requires data loading, modeling, and workload management
Distributed big-data processing	EMR	Managed cluster-based processing frameworks	More platform and workload management than a serverless query service
Frequently accessed, temporary, or session data	ElastiCache	In-memory access and reduced database load	Cache invalidation, stale data, and failover behavior
Unsupported engine or full OS/database control	Database on EC2	Maximum customization	The customer owns administration, backup, patching, and recovery

Common Exam Traps

• Choosing DynamoDB because the schema is flexible: Flexibility alone is insufficient. Confirm that the access patterns are compatible with key-based retrieval and that joins are not central to the application.
• Using Redshift as the production transaction database: Redshift is intended for analytical workloads, not high-frequency OLTP transactions.
• Putting analytics directly on the production RDS database: Large scans and complex reports can consume resources needed by application transactions. Replicate, export, or load data into an analytical system.
• Treating ElastiCache as automatically durable: A cache may lose data or contain stale data depending on its configuration and failure mode. Define whether it is a cache, a session store, or a system of record.
• Selecting EC2 for maximum control without accounting for operations: EC2 transfers database administration responsibilities to the customer.
• Assuming non-relational means no schema or no design: DynamoDB still requires deliberate partition-key, sort-key, index, item-size, and access-pattern design.
• Confusing Multi-AZ and read replicas: Multi-AZ is primarily for high availability and failover. Read replicas are primarily for read scaling and can support other patterns such as reporting or migration.
• Assuming horizontal scaling solves every database problem: A distributed database can scale throughput, but poor partition-key distribution, hot partitions, inefficient queries, or unsuitable access patterns can still limit performance.

Real-World Engineer Notes

• Start database selection with workload questions: What are the read and write patterns? Are joins required? How much latency is acceptable? Is the workload transactional or analytical? What are the recovery objectives?
• Separate the system of record from acceleration layers. A relational database or DynamoDB table may be authoritative, while ElastiCache serves repeat reads.
• For DynamoDB, model entities around access patterns rather than attempting to reproduce a normalized relational schema. Consider composite keys, sparse indexes, and denormalization where appropriate.
• For relational systems, plan connection management, indexing, backups, failover testing, storage growth, and maintenance windows. Application connection pooling is especially important at scale.
• Keep analytical ingestion isolated from production traffic. Common patterns include change data capture, scheduled extraction, streaming ingestion, and object-storage-based data lakes before loading or querying analytical stores.
• Service selection does not replace security design. Use private networking where appropriate, encryption at rest and in transit, least-privilege IAM or database credentials, secret rotation, logging, and tested backup restoration.
• Evaluate cost using the actual workload: provisioned versus on-demand capacity, storage, I/O, replicas, data transfer, caching nodes, warehouse concurrency, and operational labor can materially change the decision.

Quick Reference Summary

• Relational: tables, rows, columns, fixed schema, SQL, joins, transactions.
• NoSQL: key-value, document, wide-column, or graph models; flexible schema and horizontal scaling.
• OLTP: short, frequent application transactions.
• OLAP: complex analysis over large or historical datasets.
• RDS/Aurora: managed relational databases.
• DynamoDB: managed key-value and document database.
• Neptune: graph database for relationship-centric workloads.
• Redshift: analytical data warehouse.
• ElastiCache: in-memory caching and data-store capabilities.
• EC2-hosted database: maximum control with maximum operational responsibility.

Flashcards

1. Q: What are the two major database-model categories?

A: Relational and non-relational databases.

1. Q: What is a defining characteristic of a relational database?

A: Data is organized into tables with a defined schema and queried using SQL.

1. Q: What workload is OLTP designed to support?

A: Frequent, short, low-latency application transactions such as orders or account updates.

1. Q: What workload is OLAP designed to support?

A: Complex analytical queries over large volumes of current or historical data.

1. Q: Which AWS service is commonly used for managed key-value and document workloads?

A: Amazon DynamoDB.

1. Q: Why is DynamoDB data modeling access-pattern-driven?

A: Queries are designed around keys and indexes rather than arbitrary relational joins.

1. Q: Which AWS service is designed for graph relationships?

A: Amazon Neptune.

1. Q: Which AWS service is a managed analytical data warehouse?

A: Amazon Redshift.

1. Q: What is a common role for ElastiCache?

A: Serving frequently accessed data from memory to reduce latency and load on a backing database.

1. Q: When might a database be installed on EC2?

A: When an unsupported engine, specialized configuration, or full operating-system control is required.

1. Q: Does Multi-AZ primarily provide read scaling?

A: No. It primarily provides high availability and failover; read replicas are used for read scaling.

1. Q: Why should analytical queries usually be separated from OLTP databases?

A: Analytical scans can consume CPU, memory, I/O, and connections needed for production transactions.

Practice Questions

Question 1

An online marketplace needs a database for customers, orders, and payments. The application requires SQL joins, referential integrity, and multi-step transactions. Which choice is most appropriate?

A. Amazon DynamoDB with application-managed joins
B. Amazon Redshift
C. Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible
D. Amazon ElastiCache for Redis

Correct answer: C

Explanation: The workload is relational and transactional, with joins and strong transaction requirements. RDS or Aurora provides managed PostgreSQL-compatible relational capabilities. Redshift is designed for analytics, DynamoDB is not a relational join-oriented database, and ElastiCache is an acceleration or specialized in-memory layer.

Question 2

A globally distributed gaming application must retrieve player state using a player ID, handle very high request volume, and support rapidly changing item attributes. Queries are known in advance and do not require joins. Which service is the best fit?

A. Amazon DynamoDB
B. Amazon Redshift
C. Amazon Neptune
D. A self-managed PostgreSQL database on EC2

Correct answer: A

Explanation: DynamoDB is designed for managed, horizontally scalable key-value and document access. A well-designed partition key and known access patterns match the requirements. Redshift is analytical, Neptune is graph-oriented, and EC2 adds unnecessary operational responsibility.

Question 3

A company runs order processing on Aurora. Business analysts need to execute large historical sales queries without affecting order-placement latency. Which architecture is most appropriate?

A. Run the queries directly against the Aurora writer
B. Place ElastiCache in front of Aurora and run all reports from the cache
C. Load operational data into Amazon Redshift and run reports there
D. Migrate the order system to Amazon Redshift

Correct answer: C

Explanation: Redshift is designed for analytical workloads and can isolate large scans and aggregations from the OLTP system. ElastiCache is not a general-purpose analytical warehouse, and the order-processing system should not be moved to an OLAP service.

Question 4

An application repeatedly reads product metadata that changes only every few hours. The database is experiencing unnecessary read load, and the application requires lower response latency. Which addition is most appropriate?

A. Amazon ElastiCache
B. Amazon Neptune
C. Amazon Redshift
D. A second database on EC2 with no replication

Correct answer: A

Explanation: ElastiCache can hold frequently requested data in memory and reduce repeated reads against the primary database. The application must define expiration or invalidation behavior so stale product metadata is handled correctly.

Question 5

A vendor requires a database engine and operating-system configuration that are not supported by Amazon RDS. The organization has a team capable of managing database backups, patching, replication, and failover. Which option best satisfies the requirement?

A. Amazon DynamoDB
B. Amazon Redshift
C. Database software installed on Amazon EC2
D. Amazon ElastiCache Memcached

Correct answer: C

Explanation: EC2 permits installation of an engine and operating-system configuration outside the managed RDS catalog. The tradeoff is that the organization assumes responsibility for the database lifecycle, availability, security, backups, and recovery processes.