Data Store Selection

Two questions that sort every store choice

A purchase service writes one order at a time and reads it back by order ID in a few milliseconds; a finance dashboard scans a quarter of those orders to total revenue by region. Those are the two ends of the spectrum every data store decision sits on, and naming them is the whole skill. The first is online transaction processing (OLTP): many small, point reads and writes at high concurrency, keyed by an identifier. The second is online analytical processing (OLAP): a few large queries that scan and aggregate across columns. AWS gives you different engines for each because the storage layout that makes one fast makes the other slow.

The rule is to decide OLTP versus OLAP first, then narrow by the shape of the data: relational rows, key-value or document items, graph edges, time series, or embedding vectors. OLTP relational goes to Amazon RDS^[1] or Amazon Aurora^[2]; OLTP key-value or document goes to Amazon DynamoDB^[3]. OLAP over modeled, structured data goes to the Amazon Redshift^[4] columnar warehouse; OLAP over cheap, open-format files goes to an Amazon S3^[5] data lake read by Amazon Athena^[6], Amazon EMR, or Redshift Spectrum. Two more shapes round it out: microsecond key reads belong to an in-memory store, and similarity search over embeddings belongs to a vector index. Everything that follows is just filling in those branches.

The trap the exam leans on is running the wrong workload on a store tuned for the other. A row-oriented OLTP engine like RDS reads whole rows, so a full-table aggregation drags every column off disk even when the query needs one; that is a job for a columnar warehouse. The reverse is also true: Redshift is built for big scans, not for thousands of single-row lookups per second, which is DynamoDB's home. When a question describes the access pattern in detail, that description is the answer key.

Decide OLTP vs OLAP first, then branch by data shape; specialty shapes get their own store.

OLTP: relational vs key-value vs in-memory

Within OLTP, the choice is set by schema flexibility and scale shape, and all three options share one job: serve small reads and writes fast and durably. Reach for RDS or Aurora when the data is relational, queries need joins and multi-row transactions, and SQL is the access language. RDS runs managed engines (PostgreSQL, MySQL, MariaDB, Oracle, SQL Server); Aurora is the cloud-native engine that separates compute from a shared, auto-growing storage volume, gives MySQL and PostgreSQL compatibility, and grows that volume up to 256 TiB on current engine versions (Aurora storage^[7]). The relational trade is flexible ad hoc queries and joins in exchange for a fixed schema and scaling that is bounded by an instance (you add read replicas for reads, but writes go to one primary).

Reach for DynamoDB when the workload is key-value or document, the access patterns are known up front, and you need single-digit-millisecond reads at effectively unlimited request scale with nothing to manage (DynamoDB^[3]). DynamoDB scales horizontally by partitioning on the key, so it has no instance ceiling, but it pays for that by asking you to design the table around the queries: you model the access patterns into the partition and sort keys (and into secondary indexes) before you store data, because a query it was not designed for means a costly scan. The line to remember: relational gives you query flexibility at the cost of a fixed schema and instance-bound scale; DynamoDB gives you horizontal scale and predictable latency at the cost of designing every access pattern in advance.

The third option is in-memory, and it is not a system of record by default. Amazon ElastiCache^[8] (Redis OSS, Valkey, or Memcached) serves microsecond reads from RAM, but it is a cache that sits in front of a database to absorb hot reads; if a node is lost, cached data can be gone. Amazon MemoryDB^[9] is the durable version: it keeps all data in memory for microsecond reads and single-digit-millisecond writes, but it also writes a Multi-AZ transactional log, so it survives failover and can be the primary database for a fast key-value workload, eliminating the need to run a separate cache and database. So: cache in front of RDS, Aurora, or DynamoDB with ElastiCache; choose MemoryDB only when the fast store must also be durable on its own.

The three OLTP families by data shape; ElastiCache caches, MemoryDB is a durable in-memory primary.

OLAP: warehouse, lake, and query-in-place

On the analytics side the framing is a query-engine choice over data that often lives in the same place. Amazon Redshift is a columnar, MPP (massively parallel processing) warehouse: it stores data by column and spreads work across nodes, so repeated aggregations over modeled, structured data run fast once you load and define distribution and sort keys (Redshift^[4]). An Amazon S3 data lake is the opposite trade: store raw and curated files cheaply in open formats and let many engines read them in place with schema-on-read, paying per query instead of per loaded table. You rarely pick only one. Lead with the lake as the durable home for everything, and add a warehouse for the curated, performance-critical queries.

Three Redshift features let it reach data without copying it, and the exam loves to test which one fits. Redshift Spectrum queries files directly in S3: you create an external schema backed by the AWS Glue Data Catalog and external tables over the S3 location, then run normal SQL that joins those external tables to local Redshift tables, with the scan pushed down to the Spectrum layer (Redshift Spectrum^[10]). The data stays in S3; nothing is loaded. Federated queries go the other direction, letting Redshift read live from operational Amazon RDS or Aurora PostgreSQL and MySQL databases^[11] so you can join the warehouse to current transactional data without a pipeline. Materialized views precompute and store the result of an expensive query and can refresh incrementally, so dashboards read the cached answer instead of rescanning (materialized views^[12]). Read the stem for the verb: "query S3 without loading" is Spectrum, "join to the live OLTP database" is a federated query, "speed up a repeated expensive query" is a materialized view.

Keep the lake/warehouse boundary straight. Loading everything into Redshift is expensive and slow when most of the data is cold; querying hot, repeated dashboards straight off raw S3 is slower and costlier per run than a tuned warehouse table. The pattern AWS guidance points to is keep large, infrequently joined fact data in S3 and reach it with Spectrum, and keep the small, frequently joined dimension and curated tables in Redshift.

Spectrum reads S3 in place, federated queries read live RDS/Aurora, materialized views cache repeated results.

Open table formats: Iceberg and S3 Tables

Raw files on S3 are cheap but dumb: a folder of Parquet or CSV objects has no transactions, no row-level updates or deletes, and no safe way to change a column type, so two engines writing at once can leave a reader seeing a half-written table. An open table format such as Apache Iceberg fixes this by adding a metadata layer over the same S3 objects that brings ACID transactions, snapshot isolation, time travel to an earlier snapshot, hidden partitioning, and schema and partition evolution (Apache Iceberg^[13]). The point for a data engineer: Iceberg lets the lake keep S3's cost and openness while gaining a warehouse table's correctness, and many engines (Athena, EMR, Redshift, Spark) can read and write the same Iceberg tables consistently.

Amazon S3 Tables^[14] is the managed way to run Iceberg on S3. It introduces a new bucket type, the table bucket, that stores tables as first-class resources and delivers higher transactions per second and better query throughput than self-managed Iceberg tables in a general-purpose bucket. Its headline feature is that S3 continuously performs the table maintenance you would otherwise script yourself: automatic compaction of many small files into fewer large ones, snapshot management, and removal of unreferenced files, which keeps queries fast and trims storage cost. So the decision is layered: choose plain S3 files for write-once, read-mostly raw data; choose Iceberg when many engines must update the same tables with ACID guarantees or you need row-level updates, deletes, and schema evolution; and choose S3 Tables when you want those Iceberg benefits without operating the compaction and cleanup yourself.

The naming trap to debunk at first sight: "S3 Tables" is not a different database engine and it is not DynamoDB. It is Iceberg-format tabular storage living in S3, queried by the same Iceberg-aware SQL engines, with AWS running the maintenance. If a question asks for ACID updates and time travel over a data lake with the least operational overhead, S3 Tables is the answer; if it asks merely to query existing files in place, that is Athena or Redshift Spectrum, no table format required.

Each layer adds capability over the same S3 data: Iceberg adds ACID, S3 Tables adds managed maintenance.

Specialty stores and exam-pattern recognition

When the data has a natural non-relational shape, AWS has a purpose-built store, and the exam tests whether you reach for it instead of bending a relational table. Use Amazon Neptune when relationships are the data, such as fraud rings or social graphs where you traverse edges (Neptune^[15]); a deep multi-hop traversal that would be a chain of self-joins in SQL is what a graph database is for. Use Amazon Keyspaces for Cassandra-compatible wide-column data at scale and Amazon DocumentDB for MongoDB-compatible documents when you want those APIs without managing the cluster. These are shape matches, not performance upgrades over DynamoDB; pick them when the application already speaks Cassandra, MongoDB, or Gremlin/openCypher.

For similarity search over embeddings, store the vectors where your query engine already lives. Amazon Aurora PostgreSQL with the pgvector extension lets you keep vectors next to relational data and run nearest-neighbor SQL, and Amazon OpenSearch Service offers vector fields for the same job (pgvector on Aurora^[16]). Note one stable boundary: among PostgreSQL options, only Aurora PostgreSQL (not plain RDS for PostgreSQL) is a selectable vector store for Amazon Bedrock Knowledge Bases. The vector index type is its own decision. HNSW (hierarchical navigable small world) builds a layered proximity graph that gives high recall and fast queries, at the cost of more memory and slower index build; IVF (inverted file, IVFFlat in pgvector) partitions vectors into lists and searches only the nearest few, which builds faster and uses less memory but can trade away some recall. Choose HNSW when query speed and recall matter most, IVF when index build time or memory is the binding constraint.

Exam stems telegraph the right store through the access pattern, so train on the verbs. "Single-digit-millisecond key-value lookups, serverless, unpredictable scale" is DynamoDB; "relational with joins and transactions" is RDS or Aurora; "fast SQL analytics over modeled data" is Redshift; "query files in S3 without loading" is Athena or Redshift Spectrum; "join the warehouse to live operational data" is a federated query; "ACID updates and time travel on the lake with least operational effort" is S3 Tables; "microsecond reads with durability, no separate cache" is MemoryDB; "traverse relationships" is Neptune; "nearest-neighbor search over embeddings" is pgvector on Aurora or OpenSearch. The most common wrong answer is the store tuned for the opposite workload (Redshift for high-concurrency lookups, DynamoDB for ad hoc analytics), so let the described pattern, not service familiarity, pick the answer.

Choosing the store by access pattern and data shape

Access pattern / shape	RDS / Aurora	DynamoDB	Redshift	S3 data lake	MemoryDB / ElastiCache
Workload type	OLTP relational	OLTP key-value / document	OLAP warehouse	Lake (schema-on-read)	In-memory cache / primary
Read shape it favors	Joins, ad hoc SQL	Point lookups by key	Large columnar scans	Any engine reads files	Microsecond key reads
Schema model	Fixed relational schema	Key schema designed up front	Star/columnar, modeled	Open formats (Parquet, Iceberg)	Key-value / data structures
Scaling model	Instance / Aurora storage to 256 TiB	Serverless, horizontal	MPP nodes / Serverless	Effectively unlimited S3	Nodes in RAM
Durability as system of record	Yes	Yes	Yes	Yes (11 9s)	MemoryDB yes; ElastiCache no
Query-in-place reach	Federated query target	n/a	Spectrum + federated to S3/RDS	Athena / EMR / Spectrum	n/a

Decision tree

Cheat sheet

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Decide OLTP vs OLAP before you pick a store

Sort every store question by the dominant access pattern first: online transaction processing (OLTP) is many small point reads and writes at high concurrency keyed by an identifier, while online analytical processing (OLAP) is a few large queries that scan and aggregate across columns. OLTP wants RDS, Aurora, or DynamoDB; OLAP wants the Redshift columnar warehouse or query-in-place over an S3 lake. The storage layout that makes one fast (row-oriented for point lookups, columnar for scans) makes the other slow, so settle the workload type before comparing services.

Trap Running warehouse-style aggregation scans on a row-oriented OLTP engine like RDS; it reads whole rows off disk and drags every column even when the query needs one.

Choose RDS or Aurora for relational, joins, and SQL transactions

When the data is relational and queries need joins, multi-row transactions, and ad hoc SQL, use Amazon RDS or Amazon Aurora; Aurora is the cloud-native engine that separates compute from a shared storage volume and is MySQL- and PostgreSQL-compatible. The relational trade is flexible ad hoc querying in exchange for a fixed schema and writes bound to a single primary instance (you add read replicas for read scale, not write scale). Reach here whenever the application's value is in relationships between tables rather than raw key-lookup throughput.

3 questions test this

Aurora storage grows to 256 TiB on current engine versions

An Aurora cluster volume grows automatically up to 256 TiB on current engine versions (older versions topped out at 128 TiB), so you do not pre-provision storage the way you do for RDS. Many third-party sources still quote 64 or 128 TiB; use 256 TiB for current content. This matters when a question pits Aurora against another store on a capacity ceiling.

Choose DynamoDB for key-value access patterns known up front

Use DynamoDB when the workload is key-value or document, the access patterns are known in advance, and you need single-digit-millisecond reads at effectively unlimited request scale with no servers to manage. It scales horizontally by partitioning on the key, so it has no instance ceiling, but you must design the partition and sort keys (and secondary indexes) around the queries before storing data. The trade versus relational is horizontal scale and predictable latency in exchange for committing to the access patterns up front.

Trap Choosing DynamoDB when analysts need to query the data many unforeseen, ad hoc ways; a query it was not key-designed for forces an expensive full scan.

ElastiCache is a cache; MemoryDB is a durable in-memory primary

Both Amazon ElastiCache and Amazon MemoryDB serve microsecond reads from RAM, but they differ in durability: ElastiCache (Redis OSS, Valkey, or Memcached) is a cache that sits in front of a database and a node loss can drop cached data, while MemoryDB stores data durably across Availability Zones with a Multi-AZ transactional log and delivers microsecond reads with single-digit-millisecond writes. Put ElastiCache in front of RDS, Aurora, or DynamoDB to absorb hot reads; choose MemoryDB only when the fast key-value store must also be durable enough to be the system of record, which lets it replace a separate cache plus database.

Trap Treating a plain ElastiCache cache as the only durable copy of the data; it is a cache in front of a database, so a node failure can lose what was only cached.

Use Redshift for fast, repeated SQL analytics on modeled data

Amazon Redshift is a columnar, massively parallel processing (MPP) warehouse: it stores data by column and spreads work across nodes, so repeated aggregations over modeled, structured data run fast once you load it and set distribution and sort keys. It is the OLAP answer when dashboards and reports re-run the same heavy queries. It is not built for thousands of single-row lookups per second, which is DynamoDB's job, so match it to scan-heavy analytics rather than point access.

Trap Reaching for Redshift to serve high-concurrency point lookups; an MPP columnar warehouse is tuned for big scans, not single-row OLTP reads.

Redshift Spectrum queries S3 in place without loading

Redshift Spectrum lets Redshift run SQL directly against files in Amazon S3 by defining an external schema (backed by the AWS Glue Data Catalog) and external tables over the S3 location, then joining those external tables to local Redshift tables. The data stays in S3 and nothing is loaded into the warehouse, so it fits large, cold fact data you keep in the lake while small dimension tables live in Redshift. When a stem says "query data in S3 without loading it into the warehouse," that is Spectrum.

Trap Copying the whole S3 lake into Redshift to join against it; Spectrum reads the external tables in place, avoiding the load and duplicate storage.

2 questions test this

Federated queries let Redshift read live RDS/Aurora data

A Redshift federated query reads live from operational Amazon RDS and Aurora PostgreSQL or MySQL databases, so you can join the warehouse to current transactional data without first building a pipeline to copy it. It is the opposite direction from Spectrum: Spectrum reaches out to S3 files, a federated query reaches out to a running OLTP database. Pick it when the requirement is joining analytics to up-to-the-moment operational rows.

Trap Using Redshift Spectrum to reach a live transactional database; Spectrum targets S3 files, while reading a running RDS/Aurora database is a federated query.

Materialized views cache expensive repeated query results

A Redshift materialized view precomputes and stores the result of an expensive query and can refresh incrementally, so dashboards read the cached answer instead of rescanning the base tables each time. Reach for it when the same heavy aggregation runs repeatedly and the underlying data changes slowly enough that a refreshed cache is acceptable. It speeds up reads at the cost of storing and refreshing the precomputed result.

Lead with an S3 data lake for cheap, schema-on-read storage

An Amazon S3 data lake stores raw and curated files in open formats cheaply and lets many engines (Athena, EMR, Redshift Spectrum) read them in place with schema-on-read, paying per query rather than per loaded table. Use it as the durable landing zone for everything, then add a warehouse only for the curated, performance-critical queries. The lake-versus-warehouse decision is a query-engine choice over what is often the same underlying data, not an either/or.

Add Apache Iceberg when the lake needs ACID and row updates

Raw Parquet or CSV files on S3 have no transactions, no row-level updates or deletes, and no safe schema change, so an open table format like Apache Iceberg adds a metadata layer over the same objects that brings ACID transactions, snapshot isolation, time travel, hidden partitioning, and schema evolution. Choose Iceberg when many engines must read and write the same lake tables consistently or you need to update and delete individual rows. It keeps the lake's cost and openness while gaining a warehouse table's correctness.

Trap Trying to update or delete individual records in plain S3 Parquet/CSV files; they have no transactions, so you need an open table format like Iceberg for row-level mutations.

Use S3 Tables for managed Iceberg with automatic maintenance

Amazon S3 Tables is the managed way to run Apache Iceberg on S3: a new table-bucket type stores tables as first-class resources, delivers higher transactions per second and query throughput than self-managed Iceberg in a general-purpose bucket, and has S3 continuously perform table maintenance for you (automatic compaction of small files, snapshot management, and unreferenced-file removal). Choose it when you want Iceberg's ACID and time-travel benefits on the lake with the least operational overhead. It is Iceberg-format storage queried by Iceberg-aware engines, not a new database engine.

Trap Assuming S3 Tables is a separate database like DynamoDB; it is Iceberg tabular storage in S3 with AWS running compaction and cleanup, queried by the same Iceberg SQL engines.

Match graph, document, and wide-column data to its purpose-built store

When the data has a natural non-relational shape, use the store built for it rather than bending a relational table: Amazon Neptune for graph traversals like fraud rings or social graphs, Amazon Keyspaces for Cassandra-compatible wide-column data, and Amazon DocumentDB for MongoDB-compatible documents. These are shape matches that give you the Gremlin/openCypher, Cassandra, or MongoDB APIs, not raw performance upgrades over DynamoDB. Pick them when a deep multi-hop traversal would become a chain of self-joins, or when the application already speaks one of those data models.

Trap Modeling deep relationship traversals as repeated self-joins in a relational table; a graph database like Neptune traverses edges directly and is what those queries need.

Store vectors where your engine lives: Aurora pgvector or OpenSearch

For similarity search over embeddings, keep the vectors next to the query engine you already use: Amazon Aurora PostgreSQL with the pgvector extension runs nearest-neighbor SQL beside relational data, and Amazon OpenSearch Service offers vector fields for the same job. Among PostgreSQL options, only Aurora PostgreSQL (not plain RDS for PostgreSQL) is a selectable vector store for Amazon Bedrock Knowledge Bases. Choose the vector store by where the rest of the workload runs rather than standing up a separate similarity-search system.

Trap Assuming plain RDS for PostgreSQL is a selectable Bedrock Knowledge Bases vector store; only Aurora PostgreSQL qualifies among the PostgreSQL options.

Pick HNSW for recall and speed, IVF when memory or build time binds

The vector index type is its own decision: HNSW (hierarchical navigable small world) builds a layered proximity graph that gives high recall and fast queries but uses more memory and is slower to build, while IVF (inverted file, IVFFlat in pgvector) partitions vectors into lists and searches only the nearest few, so it builds faster and uses less memory but can trade away some recall. Choose HNSW when query latency and recall matter most; choose IVF when index build time or memory is the binding constraint. The choice is a recall-and-speed versus memory-and-build-cost tradeoff, not a correctness one.

Let the described access pattern, not service familiarity, pick the store

Exam stems telegraph the right store through the access pattern, so train on the verbs: "single-digit-millisecond key-value lookups, serverless" is DynamoDB; "relational with joins and transactions" is RDS or Aurora; "fast SQL analytics over modeled data" is Redshift; "query files in S3 without loading" is Athena or Redshift Spectrum; "join the warehouse to live operational data" is a federated query; "ACID updates and time travel on the lake with least operational effort" is S3 Tables; "microsecond reads with durability, no separate cache" is MemoryDB. The most common wrong answer is the store tuned for the opposite workload, so read the pattern before reaching for the familiar service.

Choose DynamoDB on-demand capacity for unpredictable or low-utilization traffic

On-demand capacity mode auto-scales per request with no capacity planning, making it the default and recommended choice for new or spiky workloads. Provisioned mode (optionally with reserved capacity) is more cost-effective for steady, predictable, high-utilization traffic that you can reliably forecast.

Trap Keeping provisioned capacity on a steady-but-lightly-utilized table where on-demand pay-per-request would cost less.

6 questions test this

Pick OpenSearch Serverless for variable, hard-to-forecast search and log workloads

Amazon OpenSearch Serverless auto-provisions and scales compute (in OCUs) with demand and removes cluster capacity management, fitting infrequent, intermittent, or spiky workloads. Use the Search collection type for full-text search and the Time series collection type for log analytics.

5 questions test this

Aurora Serverless v2 auto-scales relational compute for variable load

Aurora Serverless v2 (MySQL- or PostgreSQL-compatible) scales capacity in fine-grained half-ACU increments to follow demand and can scale to zero ACUs with automatic pause and resume during idle periods, so it suits unpredictable, spiky transactional workloads while supporting Multi-AZ and standard Aurora features.

4 questions test this

Add read replicas and route reads to the reader endpoint to offload reporting

Aurora Replicas share the primary's storage volume, so routing read-heavy or reporting queries to the reader endpoint scales reads with low replica lag and no schema change, while writes stay on the writer endpoint. RDS read replicas serve the same purpose for non-Aurora engines.

5 questions test this

Enable Redshift concurrency scaling (mode auto) to absorb query bursts

Concurrency scaling adds transient cluster capacity automatically when queries start queuing on a WLM queue, giving consistent performance during peaks without permanently resizing the cluster. Set the Concurrency Scaling mode to auto on the affected WLM queue and route the bursty workload there.

4 questions test this

Two questions that sort every store choice

OLTP: relational vs key-value vs in-memory

OLAP: warehouse, lake, and query-in-place

Open table formats: Iceberg and S3 Tables

Specialty stores and exam-pattern recognition

Choosing the store by access pattern and data shape

Decision tree

Cheat sheet

Also tested in

References