Choosing an AWS Data Store: Redshift, DynamoDB, Aurora, S3, and MemoryDB

You choose a data store by matching four things — access pattern, latency requirement, data volume, and cost model — against what each engine is built for, and DEA-C01 questions almost always hide the answer in the access-pattern wording. "Complex aggregations across years of data" means columnar analytics; "look up a session by ID in milliseconds" means key/value; "query files ad hoc without loading them" means S3 in place.

Two exam heuristics. First, cost questions favor S3 + Athena for data queried occasionally — a running Redshift cluster for a weekly report is the classic wrong answer. Second, performance questions favor purpose-built stores: forcing key/value lookups through a relational database, or analytics through DynamoDB scans, is always the trap option.

Amazon Redshift performs best when you configure how data is distributed and ordered across the cluster, because it is a massively parallel (MPP) columnar warehouse — every query runs across all compute slices, and data placement decides how much network shuffling a join requires.

Distribution style controls which node stores each row. KEY distribution co-locates rows that share a join column, so joining two large tables on their common distribution key avoids redistributing data at query time. ALL copies the entire table to every node — right for small, frequently joined dimension tables. EVEN spreads rows round-robin when no clear join key exists. AUTO (the default) lets Redshift pick and adapt as the table grows; it is the least-operational-overhead answer unless the question describes a specific large-table join to optimize.

Sort keys order rows on disk so range-restricted scans skip blocks. Sort on the column queries filter by most — typically the date or timestamp column in time-series fact tables. Compound sort keys favor queries that filter on the leading column; AUTO sort keys let Redshift choose from observed workload.

Deployment shape drives cost. RA3 nodes decouple compute from Redshift Managed Storage, so you scale compute without paying for storage-driven node counts, and they enable data sharing — live, read-only access to another cluster's data with no copying. Redshift Serverless bills only while queries run, making it the cost answer for intermittent or unpredictable analytics. Concurrency scaling adds transient capacity when queues build during peak dashboard hours, and short query acceleration keeps small queries from waiting behind long ones.

Redshift can query data it has never loaded, and the exam expects you to pick the right remote-access method for where the data lives.

Redshift Spectrum queries files in Amazon S3 through external tables defined in an external schema (backed by the AWS Glue Data Catalog). Spectrum scales its own fleet independently of your cluster and charges by data scanned, so it suits huge, infrequently queried history: keep hot recent data in local Redshift tables, leave cold years in S3 as Parquet, and one SQL statement joins both. Partitioning and columnar formats cut the bytes scanned and therefore the cost.

Federated queries reach live operational data in Amazon RDS and Aurora (PostgreSQL and MySQL) without any ETL. Redshift connects to the database (credentials from AWS Secrets Manager), pushes down what predicates it can, and lets you join live transactional rows against warehouse history — the answer when the question says "query current orders in Aurora from Redshift without moving data."

Materialized views precompute and store a query result inside Redshift, refreshed incrementally where possible (and on a schedule or automatically). Automatic query rewriting silently redirects eligible dashboard queries to the materialized view. They are the answer when the same expensive aggregation runs repeatedly and freshness within a refresh interval is acceptable.

Scenario

A retailer keeps 90 days of orders in Redshift, seven years of order history as partitioned Parquet in S3, and live inventory in Aurora PostgreSQL. Nightly finance queries need all three. Exam-correct design: local tables for the hot 90 days, a Spectrum external schema over the S3 history, a federated schema to Aurora for live inventory — one warehouse query, nothing copied, and a materialized view over the recurring aggregation to keep the dashboard fast.

Operational stores are configured around their access patterns, and DEA-C01 tests the configuration choices, not just the service names.

Amazon RDS runs managed relational engines (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Db2) with automated backups, patching, and Multi-AZ standby failover. Amazon Aurora is the AWS-built MySQL- and PostgreSQL-compatible option with a distributed storage layer that replicates six ways across three Availability Zones, up to 15 low-lag read replicas behind a reader endpoint, and Aurora Serverless v2, which scales capacity in fine-grained increments for spiky or intermittent relational workloads. Configure for the access pattern: read-heavy reporting against an OLTP database gets read replicas, not a bigger writer; connection storms from Lambda get RDS Proxy.

DynamoDB delivers consistent single-digit-millisecond performance regardless of table size, but only if you design for it. The partition key must distribute traffic evenly — a hot key throttles no matter how much capacity you buy. Choose on-demand capacity for unpredictable or spiky traffic (pay per request, zero capacity management) and provisioned capacity (with auto scaling) for steady, forecastable traffic where it is cheaper. Global secondary indexes serve alternate access patterns; DynamoDB Accelerator (DAX) caches microsecond reads for read-heavy, read-mostly workloads; TTL expires items automatically.

The dividing line the exam draws: if the scenario needs flexible ad hoc queries, multi-table joins, or strict relational constraints, DynamoDB is wrong — use RDS or Aurora. If it needs known-key access at massive scale with no operational tuning, a relational database is the wrong answer — use DynamoDB.

Two newer Task 2.1 skills are applying Amazon MemoryDB for fast key/value access and describing vector index types.

MemoryDB is a durable, Valkey- and Redis-compatible in-memory database: microsecond reads, single-digit-millisecond writes, and — unlike a plain cache — durability from a Multi-AZ transaction log, so it can serve as a primary database, not just a cache in front of one. Reach for it when the question demands ultra-low-latency key/value access with data safety (session state, leaderboards, real-time feature lookups). ElastiCache remains the answer for pure caching in front of another system of record; MemoryDB is the answer when the in-memory store is the system of record.

Vector search stores embeddings and finds nearest neighbors for semantic search and retrieval-augmented generation. Aurora PostgreSQL with the pgvector extension supports approximate-nearest-neighbor indexes, and the exam names HNSW (Hierarchical Navigable Small Worlds) explicitly: a multi-layer graph index navigated from coarse to fine layers. HNSW gives high recall and fast queries, can be built on an empty table and updated incrementally, but costs more memory and build time.

IVF (Inverted File, pgvector's IVFFlat) clusters vectors into lists and searches only the nearest lists at query time. It builds faster and uses less memory, but it must be trained on data already present (build it after loading), and recall depends on how many lists you probe — more probes, better recall, slower queries.

Exam rule of thumb: HNSW for query-speed and recall priorities on evolving data; IVF for faster, cheaper index builds on a stable, fully loaded dataset.

Task 2.1's service list also includes stores that don't look like databases.

Kinesis Data Streams and Amazon MSK are short-term storage, not just pipes: both retain records for a configurable window (Kinesis from 24 hours up to 365 days; Kafka topics per retention policy), letting multiple consumers read independently and rewind for replay. Choose them as the storage layer when the requirement is ordered, replayable event data consumed by several systems — and choose MSK specifically only when the scenario requires Apache Kafka compatibility or an existing Kafka ecosystem; otherwise Kinesis is the lower-operational-overhead answer.

Amazon EMR offers a storage decision inside the cluster: HDFS on the cluster's instance stores is fast but ephemeral — it vanishes with the cluster — while EMRFS reads and writes Amazon S3 directly, decoupling storage from compute. The exam-preferred pattern keeps persistent data in S3 via EMRFS and treats clusters as disposable (including transient clusters that terminate after each job, the cost-optimization answer); HDFS is for temporary shuffle and scratch data within a job's lifetime.

AWS Lake Formation is not a storage engine — it is the governance layer over an S3 data lake. It registers S3 locations, and grants database-, table-, column-, row-, and cell-level permissions that Athena, Redshift Spectrum, Glue, and EMR enforce, replacing per-service bucket-policy sprawl with one permission model. When a question pairs "data lake on S3" with "fine-grained access control," Lake Formation is the answer; when it asks where the data physically lives, the answer is still S3.

Managing open table formats means running Apache Iceberg tables on S3, and Iceberg exists to give data-lake files the behaviors of a database table: ACID transactions, safe concurrent writers, schema evolution, and time travel.

Plain Parquet-on-S3 tables break down when you need to update or delete individual rows, evolve schemas without rewriting data, or read consistently while a write is in flight. Iceberg fixes this with a metadata layer of snapshots and manifest files: every commit produces a new table snapshot, readers see a consistent snapshot, and writers commit atomically. That buys you row-level updates and deletes (the standard answer for GDPR-style deletes in a data lake), schema evolution (add, rename, drop columns as metadata-only changes), hidden partitioning (queries prune partitions without filtering on a derived partition column, and partition schemes can change without rewriting the table), and time travel (query the table as of a prior snapshot or timestamp).

On AWS, Iceberg is a first-class format: Athena creates and queries Iceberg tables with full DML, AWS Glue jobs read and write them and the Glue Data Catalog serves as the Iceberg catalog, Amazon EMR (Spark) supports them, and Amazon Redshift can query them. Amazon S3 Tables provides S3 bucket-native managed Iceberg tables with built-in maintenance.

The management duties the exam probes: small streaming writes accumulate many small files, so run compaction (Athena OPTIMIZE ... REWRITE DATA, or Glue/S3 Tables automatic compaction), and expire old snapshots so time-travel metadata and orphaned files don't grow storage forever.

Locks prevent conflicting access to data, and the exam tests how to find and clear blocking locks in Redshift and RDS.

Amazon Redshift takes table-level locks: reads take a shared AccessShareLock, while DDL and operations such as TRUNCATE or ALTER TABLE take an AccessExclusiveLock that blocks everything else. The classic symptom is an ETL job or BI query hanging because a long transaction — often an idle session that never committed — holds an exclusive lock. Diagnose with the lock and transaction system views (SVV_TRANSACTIONS, STV_LOCKS), identify the blocking process ID, and clear it with PG_TERMINATE_BACKEND(pid). Prevent recurrences by keeping transactions short and committing promptly in ETL sessions.

Amazon RDS and Aurora engines use row-level locking under MVCC, so readers don't block writers; problems appear as lock waits and deadlocks between writers. Find blockers through the engine's lock views (for example pg_locks in PostgreSQL or InnoDB status in MySQL) and Performance Insights, terminate the blocking session if needed, and design transactions to touch rows in a consistent order to avoid deadlocks.

AWS Transfer Family is the migration-and-ingestion tool in this task: a fully managed SFTP, FTPS, FTP, and AS2 endpoint that writes received files directly into Amazon S3 (or Amazon EFS). It is the answer whenever partners or legacy systems must keep pushing files over SFTP but you want them landing in the data lake with zero servers to run. From there the pipeline is event-driven: the S3 upload triggers EventBridge or S3 Event Notifications, which start the Glue job that processes the file.