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 heuristics follow from the table. First, cost favors S3 + Athena for data queried occasionally — a running Redshift cluster for a weekly report wastes money, while steady, concurrent BI load is where a warehouse earns its keep. Second, performance favors purpose-built stores: forcing key/value lookups through a relational database, or analytics through DynamoDB scans, works against the engine.

Watch single words that seem decisive but aren't. "ACID" alone does not mean relational — DynamoDB supports ACID transactions across items too. What points to RDS or Aurora is ACID plus foreign keys, joins and ad hoc SQL over normalized tables. Likewise "in-memory" alone does not mean cache: whether the in-memory store must be the durable system of record decides between MemoryDB and ElastiCache.

Amazon Redshift is configured along two axes — how data is placed across the cluster and which deployment shape you run — because it is a massively parallel (MPP) columnar warehouse where every query runs across all compute slices.

Placement in brief. Distribution style decides which node stores each row (KEY co-locates rows sharing a join column, ALL copies small dimensions to every node, EVEN spreads round-robin, AUTO lets Redshift choose), and sort keys order rows on disk so zone maps can skip blocks outside a filtered range. Designing those keys is Task 2.4 (schema design) and is taught in the data-modeling lesson; Task 2.1 is about choosing and configuring the store itself.

Node types and managed storage. RA3 nodes keep data in Redshift Managed Storage (RMS): durable S3-backed storage with a local SSD cache that Redshift fills and evicts automatically. Storage therefore grows without adding nodes, and you size the cluster for compute alone. DC2 is the legacy dense-compute family with storage fixed to each node's local SSD; AWS steers new and upgraded clusters to RA3. Knowing which capabilities depend on RA3 prevents a common mistake:

Data sharing gives another cluster or workgroup live, read-only access to a producer's tables with no copy and no pipeline. Consumers query the producer's managed storage with their own compute, so each team's queries run on, and are billed to, the consumer — isolating workloads and chargeback. That differs from scaling the producer: concurrency scaling clusters added to absorb other teams' queries on the producer bill the producer.

Redshift Serverless removes the cluster: capacity is measured in Redshift Processing Units (RPUs), scales automatically, and is billed per RPU-second only while queries run. The base capacity setting is the RPU level a workgroup starts from — a performance knob (higher base, faster queries, higher cost per second), not a spending cap. Serverless is the natural fit for intermittent or unpredictable analytics with no one to manage a cluster.

Redshift cost is controlled by matching the billing model to the shape of the workload, and each control solves a different shape.

Two distinctions decide most questions. A reservation or reserved node lowers the unit price but bills around the clock, so it only pays off when the warehouse is busy most of the time; for a mostly idle workload it is pure waste. And pause/resume needs a schedule you can predict — when analysts arrive at random, a paused cluster is either down when needed or running when not, which is the case Serverless was built for.

Redshift can query data it has never loaded, and the right method depends on where the data lives and whether it may be copied into the warehouse.

Redshift Spectrum queries S3 files through an external schema backed by the AWS Glue Data Catalog. Spectrum scales its own fleet and charges by data scanned, so it suits huge, infrequently queried history: keep hot data in local tables, leave cold years in S3 as partitioned Parquet, and one SQL statement joins both.

Federated queries reach live operational rows in RDS and Aurora without ETL. Redshift connects with credentials from AWS Secrets Manager, pushes predicates down where it can, and joins the live rows with warehouse tables — nothing is stored in Redshift, and every query puts load on the source database.

Aurora zero-ETL integration with Redshift is the near-twin to know. It continuously replicates the source tables into Redshift, so queries run on Redshift's own storage and compute and stop touching the production database — at the cost of holding a replica in the warehouse. Choose federated queries when the data must not be copied or only occasional lookups are needed; choose zero-ETL when a replica is acceptable and heavy analytics should not hit the OLTP database.

Materialized views precompute and store a query result inside Redshift, refreshed incrementally where the SQL allows. Two behaviors matter:

• Automatic query rewrite lets Redshift answer an eligible query written against the base tables from the materialized view, without anyone changing the SQL — useful when a BI tool's queries cannot be edited.
• AUTO REFRESH YES is best effort: Redshift refreshes when base tables change and resources allow, prioritizing user workloads. When a pipeline needs the view guaranteed current at a known moment, run REFRESH MATERIALIZED VIEW as the last step of the ETL job.

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. A sound design: local tables for the hot 90 days, a Spectrum external schema over the S3 history, a federated schema to Aurora for the occasional live inventory lookup, and a materialized view over the recurring aggregation, refreshed explicitly at the end of the nightly load. If analysts later hammer inventory all day, a zero-ETL integration moves that load off Aurora.

Operational stores are configured around their access patterns, and Task 2.1 tests the configuration choices, not just the service names.

Amazon RDS and Aurora

Amazon RDS runs managed relational engines (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Db2); Amazon Aurora is the AWS-built MySQL- and PostgreSQL-compatible option whose storage replicates six ways across three Availability Zones, with up to 15 Aurora Replicas behind a reader endpoint. Match the configuration to the problem:

Amazon DynamoDB

DynamoDB delivers consistent single-digit-millisecond performance regardless of table size, but only if the design matches the access pattern.

• Keys. The partition key spreads data and traffic across partitions; a composite key (partition key + sort key) stores an entity's items together in sort-key order, so "all readings for device X between two timestamps" is one Query. Global secondary indexes serve alternate access patterns; ad hoc filters and joins are not what DynamoDB is for.
• Hot partitions. Each partition has its own throughput ceiling (about 3,000 read units and 1,000 write units per second). The symptom of a hot key is throttling while table-level consumed capacity stays far below what is provisioned — and it happens in on-demand mode too, because the per-partition limit still applies. The fix is a higher-cardinality key (or write sharding), not more capacity.
• Capacity modes. On-demand suits unpredictable or spiky traffic with no capacity management: it instantly absorbs up to double the table's previous peak, warm throughput can pre-warm a table ahead of a planned extreme peak, and an optional maximum throughput setting caps cost. Provisioned capacity with auto scaling is cheaper for steady, forecastable traffic.
• Item size. An item is capped at 400 KB. Store larger payloads (images, documents, big JSON) in Amazon S3 and keep the object key plus queryable attributes in the item.
• DAX. DynamoDB Accelerator is an in-memory cache that speaks the DynamoDB API, so an application keeps DynamoDB as its system of record and gains microsecond reads with minimal code change. DynamoDB on its own is single-digit milliseconds, so DAX (or MemoryDB) is justified only when milliseconds are too slow. DAX caches only eventually consistent reads; strongly consistent reads pass straight through to DynamoDB, uncached. TTL expires items automatically.

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

Amazon MemoryDB is the store for fast key/value access when the in-memory data must be durable, and vector indexes are how Aurora PostgreSQL serves similarity search.

MemoryDB, ElastiCache, and DAX

The deciding question is what happens to an acknowledged write when a node fails. ElastiCache replicates from primary to replicas asynchronously, so writes acknowledged just before a failover can be lost — acceptable for a cache that can be rebuilt from a database, not for a system of record. MemoryDB commits every write to a distributed Multi-AZ transaction log before acknowledging it, so it can be the primary database.

Vector indexes in Aurora PostgreSQL (pgvector)

Vector search stores embeddings and finds nearest neighbors for semantic search and retrieval-augmented generation. With the pgvector extension, a vector column with no index is searched exactly by scanning every row — perfect recall, but slow as the table grows. Approximate-nearest-neighbor indexes trade a little recall for speed. General-purpose index types (B-tree, GIN, full-text) cannot serve distance ordering, so they do nothing for nearest-neighbor queries.

So: HNSW when recall and query speed matter on data that keeps changing; IVF for quick, low-memory builds on a stable, fully loaded table. If IVF recall is too low and there is latency to spare, raise probes per query or session — no rebuild needed.

Task 2.1's service list also includes stores that don't look like databases: stream stores, EMR cluster storage, and the Lake Formation governance layer.

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 MSK when the scenario requires Apache Kafka compatibility or an existing Kafka ecosystem; otherwise Kinesis is the lower-operational-overhead choice.

MSK tiered storage makes long Kafka retention affordable: older log segments move from broker storage to a low-cost tier, and consumers still read them through the normal Kafka APIs, so replaying months of events needs no separate archive. Prerequisites: MSK provisioned clusters (not MSK Serverless), Apache Kafka 3.6.0 or later (or 2.8.2.tiered), broker sizes larger than kafka.t3.small, and topics using the delete cleanup policy — compacted topics are not supported.

Amazon EMR has a storage decision inside the cluster. HDFS on the cluster's local disks (instance store and/or attached EBS volumes) is fast but ephemeral — it disappears with the cluster — while EMRFS reads and writes Amazon S3 directly, decoupling storage from compute. Keep durable inputs and outputs in S3 and treat clusters as disposable, including transient clusters that terminate after each job. Node types follow from this:

• Primary node — coordinates the cluster.
• Core nodes — run tasks and store HDFS data, so losing them (for example to a Spot interruption) can lose data and fail jobs.
• Task nodes — compute only, no HDFS. They are the safe place for Spot Instances to add cheap capacity.

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 scenario pairs a data lake on S3 with fine-grained access control across engines, that is Lake Formation; the data itself still lives in 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 commits, safe concurrent writers, row-level changes, 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 adds a metadata layer of snapshots and manifest files: every commit produces a new snapshot, readers see a consistent snapshot, and writers commit atomically. That buys row-level updates and deletes, schema evolution (add, rename, drop columns as metadata-only changes), hidden partitioning (queries prune partitions without filtering on a derived column, and the partition scheme can change without rewriting data), and time travel.

What Iceberg does not add matters too: it has no per-column or secondary indexes — pruning comes from partitioning and file-level column statistics (min/max values) kept in the manifests — and it has no per-row TTL; expiring old rows is a DELETE you run (or a lifecycle job), unlike DynamoDB TTL.

Time travel vs rollback

Time travel reads a past snapshot for one query and changes nothing; rollback changes the table's current state for every reader.

Deletes, compaction, and snapshot expiry

An Athena DELETE on an Iceberg table is merge-on-read: it writes delete files that hide the rows from every query immediately, while the original data files — and older snapshots — still physically contain them. Physically purging deleted rows (for example, for an erasure request) takes two steps:

1. OPTIMIZE t REWRITE DATA USING BIN_PACK rewrites data files with the deletes applied (it also compacts the small files that streaming writes create) — but only files that meet its thresholds. The table property optimize_rewrite_delete_file_threshold (default 2) skips a data file with fewer associated delete files than that, and optimize_rewrite_data_file_threshold (default 5) skips the rewrite when fewer files need optimizing. A data file touched by a single DELETE can therefore survive at defaults; for a guaranteed purge, lower both thresholds to 1 first (or run Spark's rewrite_data_files procedure on Glue or EMR).
2. VACUUM t expires snapshots older than the table's vacuum_max_snapshot_age_seconds property and removes the files no snapshot references. It always keeps at least vacuum_min_snapshots_to_keep snapshots (default 1), which takes precedence over the age limit.

That retention property defaults to only a few days (432,000 seconds), so running VACUUM at defaults also removes time-travel history older than that — set it to the history you must keep before vacuuming. Never delete data files from S3 directly: the metadata still references them, and the table breaks. Athena, Glue, EMR, and Redshift all read Iceberg tables, the Glue Data Catalog serves as the Iceberg catalog, and Amazon S3 Tables offers bucket-native managed Iceberg tables with automatic compaction and snapshot management.

Locks prevent conflicting access to data, and managing them means finding the session that holds a blocking lock, ending it the right way, and changing the workload so it doesn't recur.

Amazon Redshift

Redshift takes table-level locks. Reads take AccessShareLock; writes take ShareRowExclusiveLock; DDL such as DROP TABLE, TRUNCATE and ALTER TABLE takes AccessExclusiveLock, which blocks even reads. A lock is held until its transaction ends, so a DROP TABLE inside a long ETL transaction blocks every dashboard query on that table until COMMIT. Find the blocker in STV_LOCKS and SVV_TRANSACTIONS, then pick the right function:

To prevent recurrences, keep DDL transactions short: build the replacement under a temporary name, then swap it in with a quick ALTER TABLE … RENAME inside a brief transaction, so the exclusive lock lasts seconds. To add staged rows to an existing table instead, ALTER TABLE APPEND moves (not copies) them from the staging table into the target; it commits automatically, cannot be rolled back, and cannot run inside a BEGIN … END transaction block, so run it as its own statement. Capacity settings do not help — WLM queues, extra slots and concurrency scaling never bypass a lock; a waiting query waits wherever it runs.

Amazon RDS and Aurora (PostgreSQL)

RDS and Aurora engines lock rows under MVCC, so readers don't block writers; problems show up as lock waits and deadlocks between writers.

• Find the blocker. Query pg_stat_activity with pg_blocking_pids(pid) (or join pg_locks); Performance Insights shows the lock waits. A session in state idle in transaction is the classic culprit: it opened a transaction, took row locks, and stopped.
• End it the right way. As in Redshift, pg_cancel_backend only cancels a running query, so it does nothing to an idle-in-transaction session; pg_terminate_backend ends the session and rolls the transaction back, releasing its locks.
• Prevent recurrence. Set idle_in_transaction_session_timeout (in the DB parameter group) so the server ends such sessions automatically, and fix the application to commit promptly.
• Deadlocks come from transactions locking the same rows in opposite orders. PostgreSQL detects them and aborts one transaction; deadlock_timeout only sets how long it waits before checking, and lock_timeout just makes waits fail sooner — neither prevents the deadlock, and neither does a bigger instance. The fix is to lock rows in a consistent order (for example, always by ascending key) and keep transactions short.

On RDS and Aurora MySQL, the same steps use different tools: find the blocker in sys.innodb_lock_waits (or SHOW ENGINE INNODB STATUS and performance_schema, with Performance Insights for the waits), end it with CALL mysql.rds_kill(<id>) because the master user cannot run KILL on other users' threads, and bound waits with innodb_lock_wait_timeout.

AWS Transfer Family is the managed file-transfer service that moves files over SFTP, FTPS, FTP and AS2 directly into Amazon S3 (or Amazon EFS), making it the migration tool that plugs partner and legacy file feeds into a data pipeline with no servers to run.

It works in two directions:

• Server endpoints — Transfer Family hosts an SFTP/FTPS/FTP/AS2 server; partners push files to you, and each file lands as an S3 object.
• SFTP connectors — Transfer Family acts as the SFTP client: it retrieves files from, or sends files to, a server the partner hosts. Use a connector when the partner will not push and instead exposes their own SFTP server.

From S3 the pipeline is event-driven: the new object triggers EventBridge or S3 Event Notifications, which start the Glue job or Step Functions workflow that processes the file. Transfer Family managed workflows can also run post-upload steps (copy, tag, decrypt, custom Lambda).

Its near-twins solve different problems: