Data Store Management
Data Store Selection and Access Patterns
CoreChoose and configure stores, migration paths, Redshift access methods, locks, Apache Iceberg tables, and vector indexes from access, cost, latency, consistency, and ownership requirements.
Aligned to AWS Certified Data Engineer - Associate (DEA-C01) Version 1.1, verified August 25, 2026.
Why this matters
No storage service is universally best. Correct store selection starts with query and write patterns, then accounts for throughput, transactions, durability, table management, governance, and operating model.
Must Know
- Use Amazon Redshift for analytical warehouse workloads, RDS or Aurora for relational transactions, DynamoDB for modeled key-value/document access, and MemoryDB when durable Redis-compatible in-memory access fits.
- Design DynamoDB partition and sort keys from access patterns and distribute traffic; choosing DynamoDB does not make table scans or hot keys efficient.
- Configure Redshift distribution and sort behavior for joins and common predicates; warehouse physical design affects data movement and pruning.
- Use Redshift federated query for supported remote relational access, Spectrum for external S3 data, and materialized views for persisted query results whose refresh lag is acceptable.
- Use AWS Transfer Family for managed SFTP, FTPS, or FTP access to supported storage when partner protocol requirements call for it.
- Keep transactions short and diagnose the lock owner and blocked operation before terminating work or widening concurrency.
- Apache Iceberg uses table metadata and atomic commits to support table operations and evolution over data files; Parquet files alone do not provide that table layer.
- HNSW and IVF are approximate-nearest-neighbor index families with different build, memory, latency, and recall tradeoffs. Select from measured workload needs.
- Aurora PostgreSQL can combine relational data and supported vector search when both belong in one transactional application boundary.
Compare and Distinguish
- Redshift versus RDS/Aurora versus DynamoDB versus MemoryDB: warehouse analytics, relational transactions, modeled key access, and in-memory access solve different workloads.
- Federated query versus Spectrum versus materialized view: remote relational access, S3 external data, and persisted results differ.
- Apache Iceberg versus Parquet: transactional table metadata differs from a columnar file encoding.
- HNSW versus IVF: graph traversal and inverted-list probing use different resource and recall tradeoffs.
Scenario examples
- A transactional application with similarity search keeps relational constraints and vectors in Aurora PostgreSQL with a supported vector index.
- Redshift queries current RDS data through federation and long S3 history through Spectrum; a repeated expensive result is materialized only when bounded staleness is acceptable.
- An S3 analytical table uses Iceberg metadata for partition evolution rather than renaming folders.
Exam traps
- Choosing a store because it supports SQL without distinguishing OLTP from analytical access.
- Treating a materialized view as automatically current.
- Confusing an Iceberg table format with a vector index.
- Adding capacity before fixing a hot partition key or blocking transaction.
Key takeaways
- Start with access patterns, then select and configure the store.
- Keep storage engine, table format, and vector index decisions separate.
- Use remote access and persisted results according to freshness and source location.
How it works
- Model reads, writes, predicates, joins, consistency, growth, and retention before selecting the store and physical layout.
- Measure hot keys, locks, scans, data movement, and refresh lag to verify the design against real workload evidence.
When to use it
- Use Redshift for analytical warehousing, RDS or Aurora for relational transactions, DynamoDB for modeled key access, and MemoryDB for durable in-memory access.
- Use federation for supported remote relational data, Spectrum for external S3 data, and materialized views for reusable persisted results.
Security and governance implications
- Combine store authorization with network reachability, encryption, and any Lake Formation or database-level permissions.
- Grant partner transfer or remote-query access only to the required protocol, source, schema, table, or storage location.
Common failure modes and diagnosis
- For uneven latency, inspect partition-key distribution, scans, indexes, locks, and data movement before increasing capacity.
- For stale or missing query results, verify the remote source, external location, materialized-view refresh, and caller permissions separately.
More detail
- 2.1.1: Implement the appropriate storage services for specific cost and performance requirements (for example, Amazon Redshift, Amazon EMR, AWS Lake Formation, Amazon RDS, Amazon DynamoDB, Amazon Kinesis Data Streams, Amazon Managed Streaming for Apache Kafka [Amazon MSK]).
- 2.1.2: Configure the appropriate storage services for specific access patterns and requirements (for example, Amazon Redshift, Amazon EMR, Lake Formation, Amazon RDS, DynamoDB).
- 2.1.3: Apply storage services to appropriate use cases (for example, using indexing algorithms like Hierarchical Navigable Small Worlds [HNSW] with Amazon Aurora PostgreSQL and using Amazon MemoryDB for fast key/value pair access).
- 2.1.4: Integrate migration tools into data processing systems (for example, AWS Transfer Family).
- 2.1.5: Implement data migration or remote access methods (for example, Amazon Redshift federated queries, Amazon Redshift materialized views, Amazon Redshift Spectrum).
- 2.1.6: Manage locks to prevent access to data (for example, Amazon Redshift, Amazon RDS).
- 2.1.7: Manage open table formats (for example Apache Iceberg).
- 2.1.8: Describe vector index types (for example, HNSW, IVF).
Ready for the quiz?
- Which access pattern, consistency, latency, transaction, and analytical requirements eliminate the nearest storage alternatives?
- Does the design need remote live access, external S3 scanning, or a persisted result with acceptable refresh lag?
Related objectives
- D2.1 — Task 2.1: Choose a data store
- 2.1.1 — Implement the appropriate storage services for specific cost and performance requirements (for example, Amazon Redshift, Amazon EMR, AWS Lake Formation, Amazon RDS, Amazon DynamoDB, Amazon Kinesis Data Streams, Amazon Managed Streaming for Apache Kafka [Amazon MSK]).
- 2.1.2 — Configure the appropriate storage services for specific access patterns and requirements (for example, Amazon Redshift, Amazon EMR, Lake Formation, Amazon RDS, DynamoDB).
- 2.1.3 — Apply storage services to appropriate use cases (for example, using indexing algorithms like Hierarchical Navigable Small Worlds [HNSW] with Amazon Aurora PostgreSQL and using Amazon MemoryDB for fast key/value pair access).
- 2.1.4 — Integrate migration tools into data processing systems (for example, AWS Transfer Family).
- 2.1.5 — Implement data migration or remote access methods (for example, Amazon Redshift federated queries, Amazon Redshift materialized views, Amazon Redshift Spectrum).
- 2.1.6 — Manage locks to prevent access to data (for example, Amazon Redshift, Amazon RDS).
- 2.1.7 — Manage open table formats (for example Apache Iceberg).
- 2.1.8 — Describe vector index types (for example, HNSW, IVF).