Skip to content
BytePatterns

DEA-C01 · Domain 2: Data Store Management · 26% of the exam

Task 2.1: Choose a data store

Matching the store to the access pattern and the budget: Redshift, RDS and Aurora, DynamoDB, MemoryDB, streams and S3; federated queries, materialized views and Redshift Spectrum; transfers with Transfer Family; locks; Apache Iceberg tables; and vector indexes such as HNSW and IVF.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the AWS documentation page that proves it.

Question 1 · choose 1

An Amazon Redshift cluster holds the last 2 years of sales data. Eight older years are kept in Amazon S3 as Apache Parquet files and are queried a few times a month. Analysts sometimes need to join the old data with current Redshift tables in one SQL statement. The company does not want to load the old data into Redshift storage. What should a data engineer do?

  1. ACreate an external schema that points to the S3 bucket through a Redshift federated query
  2. BLoad the older files into Redshift with a COPY command each time analysts ask for them
  3. CShare the older data from a second Redshift cluster through a datashare to the main cluster
  4. DCreate an external schema and tables for the S3 data and query them with Redshift Spectrum
Show the answer and why

  • ACreate an external schema that points to the S3 bucket through a Redshift federated query

    Incorrect

    Federated queries reach live data in RDS and Aurora PostgreSQL and MySQL databases. They do not read files in Amazon S3.

  • BLoad the older files into Redshift with a COPY command each time analysts ask for them

    Incorrect

    COPY puts the data into Redshift storage, which the company wants to avoid, and repeated loads add work for every request.

  • CShare the older data from a second Redshift cluster through a datashare to the main cluster

    Incorrect

    Data sharing gives other Redshift clusters live access to data that is stored in Redshift. The old data would first have to be loaded into the second cluster.

  • DCreate an external schema and tables for the S3 data and query them with Redshift Spectrum

    Correct

    Redshift Spectrum queries structured and semi-structured files in S3 without loading them into Redshift tables, and the external tables can be joined with local tables in the same query.

Spectrum keeps rarely used data in S3 at S3 prices while letting SQL in Redshift treat it as a table. Federated queries are for operational databases, and data sharing is for data already in Redshift.

AWS documentation

Task 2.1Report a problem with this question

Question 2 · choose 1

Marketing dashboards in Amazon Redshift must join campaign results with the current status of each customer, which lives in an Amazon Aurora PostgreSQL database used by the order system. The status must be up to the minute, and the company does not want to copy or store the operational data in Redshift or build an ETL pipeline. What should a data engineer do?

  1. AExport the Aurora tables to Amazon S3 every night and query them with Redshift Spectrum
  2. BRun an AWS DMS task with ongoing replication from Aurora into Redshift tables
  3. CCreate an external schema for the Aurora database and use Redshift federated queries
  4. DCreate a datashare on the Aurora database and add it as a consumer database in Redshift
Show the answer and why

  • AExport the Aurora tables to Amazon S3 every night and query them with Redshift Spectrum

    Incorrect

    A nightly export copies the data and is up to a day old, so it fails both requirements.

  • BRun an AWS DMS task with ongoing replication from Aurora into Redshift tables

    Incorrect

    Replication keeps a copy of the operational data inside Redshift, which the company does not want.

  • CCreate an external schema for the Aurora database and use Redshift federated queries

    Correct

    Federated queries let Redshift query live data in Aurora PostgreSQL, RDS for PostgreSQL and the MySQL engines, and combine it with Redshift data without an ETL pipeline.

  • DCreate a datashare on the Aurora database and add it as a consumer database in Redshift

    Incorrect

    Datashares share data between Redshift clusters and workgroups. Aurora is not a datashare producer.

When the requirement is live data and no copy, the query has to go to the source. Redshift federated queries do that for PostgreSQL and MySQL engines on RDS and Aurora.

AWS documentation

Task 2.1Report a problem with this question

Question 3 · choose 1

A game backend keeps player sessions and leaderboards in sorted sets through the Redis OSS API. The team wants microsecond reads, and the data must survive node failures without a separate durable database behind a cache. Which data store meets these requirements?

  1. AAmazon MemoryDB
  2. BAmazon DynamoDB with DynamoDB Accelerator (DAX)
  3. CAmazon ElastiCache in front of an Amazon RDS database
  4. DAmazon Keyspaces (for Apache Cassandra)
Show the answer and why

  • AAmazon MemoryDB

    Correct

    MemoryDB is a durable in-memory database compatible with Valkey and Redis OSS. It gives microsecond reads and stores data across multiple Availability Zones in a transaction log, so it can be the primary database.

  • BAmazon DynamoDB with DynamoDB Accelerator (DAX)

    Incorrect

    DAX is an in-memory cache with microsecond responses for DynamoDB, but it uses the DynamoDB API, not Redis OSS commands and sorted sets.

  • CAmazon ElastiCache in front of an Amazon RDS database

    Incorrect

    ElastiCache is an in-memory cache. Putting it in front of RDS is the cache-plus-database pair the team wants to avoid.

  • DAmazon Keyspaces (for Apache Cassandra)

    Incorrect

    Keyspaces is a Cassandra-compatible database used through CQL. It does not offer the Redis OSS API and its data structures.

MemoryDB removes the need to run a cache and a durable database side by side: it keeps all data in memory and makes it durable with a Multi-AZ transaction log.

AWS documentation

Task 2.1Report a problem with this question

Question 4 · choose 1

A team stores document embeddings in Amazon Aurora PostgreSQL with the pgvector extension. The table is created empty, and new vectors are inserted all day. The team needs an approximate nearest neighbor index that can be created right away on the empty table, does not need a rebuild as vectors are added, and gives a good balance of query speed and recall. Which index should the team create?

  1. AAn IVFFlat index built right away
  2. BAn IVFFlat index created after the first day's load and never rebuilt
  3. CNo index, so every query performs an exact nearest neighbor scan
  4. DAn HNSW index on the embedding column
Show the answer and why

  • AAn IVFFlat index built right away

    Incorrect

    IVFFlat finds its cluster centroids from existing data, so the data must be loaded before the index is created.

  • BAn IVFFlat index created after the first day's load and never rebuilt

    Incorrect

    IVFFlat may need a rebuild as vectors are added or changed, because the clusters were computed from the earlier data.

  • CNo index, so every query performs an exact nearest neighbor scan

    Incorrect

    An exact search compares the query with every stored vector. It gives full recall but slows down as the table grows.

  • DAn HNSW index on the embedding column

    Correct

    HNSW builds a layered graph as vectors are inserted, so it can be created on an empty table and maintained as rows arrive. Aurora PostgreSQL supports HNSW from pgvector 0.5.0.

IVFFlat partitions vectors into clusters computed from data that already exists; HNSW links vectors in a graph built incrementally. That difference decides which one fits a table that starts empty and grows all the time.

AWS documentation

Lesson: Approximate NeighboursTask 2.1Report a problem with this question

Question 5 · choose 2

A data lake table in Amazon S3 is queried with Amazon Athena. A new privacy rule requires deleting the rows of individual customers on request, and auditors must be able to query the table as it was on a past date. Which actions meet these requirements? (Choose TWO.)

  1. AKeep the Hive table and run DELETE FROM statements against it in Athena
  2. BRun MSCK REPAIR TABLE after each deletion request is processed
  3. CRecreate the table as an Apache Iceberg table and run DELETE statements in Athena
  4. DTurn on S3 Object Lock in compliance mode for the table's bucket
  5. EQuery earlier states of the Iceberg table with FOR TIMESTAMP AS OF
Show the answer and why

  • AKeep the Hive table and run DELETE FROM statements against it in Athena

    Incorrect

    Athena does not support DELETE FROM on regular Hive tables; record-level deletes need an Iceberg table.

  • BRun MSCK REPAIR TABLE after each deletion request is processed

    Incorrect

    MSCK REPAIR TABLE adds Hive-compatible partitions found in S3 to the table metadata. It does not delete rows or keep history.

  • CRecreate the table as an Apache Iceberg table and run DELETE statements in Athena

    Correct

    Athena supports record-level DELETE on Iceberg tables; it writes position delete files instead of rewriting data files.

  • DTurn on S3 Object Lock in compliance mode for the table's bucket

    Incorrect

    Object Lock prevents objects from being deleted or overwritten, which works against the deletion requirement.

  • EQuery earlier states of the Iceberg table with FOR TIMESTAMP AS OF

    Correct

    Iceberg time travel reads a consistent snapshot of the table as of a given time, which is what the auditors need.

Iceberg adds what plain Hive tables lack in Athena: row-level DELETE, UPDATE and MERGE, and snapshots that time travel queries can read.

AWS documentation

Task 2.1Report a problem with this question

Question 6 · choose 1

A ticketing application uses a MySQL-compatible relational database. The database is quiet most of the day but sees sudden, unpredictable spikes when sales open. The team wants capacity to follow the load automatically and does not want to pay for idle peak capacity. Which data store should a data engineer choose?

  1. AAn Aurora provisioned DB instance sized for the sales peaks
  2. BAmazon DynamoDB with on-demand capacity
  3. CAmazon Aurora Serverless for the MySQL-compatible database
  4. DAmazon Keyspaces (for Apache Cassandra)
Show the answer and why

  • AAn Aurora provisioned DB instance sized for the sales peaks

    Incorrect

    A fixed instance sized for the peaks keeps that capacity all day, which is the idle cost that Aurora serverless is meant to avoid.

  • BAmazon DynamoDB with on-demand capacity

    Incorrect

    DynamoDB is a NoSQL database. The application needs a MySQL-compatible relational database.

  • CAmazon Aurora Serverless for the MySQL-compatible database

    Correct

    Aurora serverless adjusts database capacity automatically based on demand, and you are charged only for the resources the cluster consumes, which suits variable and unpredictable workloads.

  • DAmazon Keyspaces (for Apache Cassandra)

    Incorrect

    Amazon Keyspaces is compatible with Apache Cassandra, not MySQL.

Spiky relational workloads with long quiet periods are the case for Aurora serverless: the engine stays MySQL-compatible while capacity scales with demand.

AWS documentation

Lesson: RDS vs DynamoDBTask 2.1Report a problem with this question

Question 7 · choose 1

A data platform keeps its order and staging tables in an Amazon Aurora PostgreSQL cluster on a recent engine version, with the Aurora Standard storage configuration. ETL jobs read and write heavily around the clock, and charges for read and write I/O operations are now about 40% of the cluster's Aurora bill. The team wants to cut those I/O charges and make the bill predictable, without migrating data or changing the application. What should a data engineer do?

  1. ASwitch the cluster to the Aurora I/O-Optimized storage configuration
  2. BConvert the cluster's DB instances to Aurora serverless instances
  3. CAdd Aurora Replicas and send the ETL reads to the reader endpoint
  4. DShorten the automated backup retention period of the Aurora cluster
Show the answer and why

  • ASwitch the cluster to the Aurora I/O-Optimized storage configuration

    Correct

    Aurora I/O-Optimized has no extra charges for read and write I/O operations and is the recommended choice when I/O is 25% or more of Aurora spending. It is selected by modifying the existing cluster.

  • BConvert the cluster's DB instances to Aurora serverless instances

    Incorrect

    Aurora serverless adjusts compute capacity to variable demand. This load is steady, and per-request I/O charges come from the storage configuration, not from the instance type.

  • CAdd Aurora Replicas and send the ETL reads to the reader endpoint

    Incorrect

    Replicas spread reads across more instances, but they all read the same cluster volume, so under Aurora Standard those reads are still I/O operations billed per request.

  • DShorten the automated backup retention period of the Aurora cluster

    Incorrect

    Backup storage is billed separately, by GB-month. A shorter retention period can lower that charge but leaves the I/O charges as they are.

The deciding number is the I/O share of the Aurora bill. At 25% or more, AWS points to Aurora I/O-Optimized, which drops per-request I/O charges for predictable pricing; capacity mode, replicas and backups act on other parts of the bill.

AWS documentation

Task 2.1Report a problem with this question

Question 8 · choose 1

The finance team runs an Amazon Redshift provisioned cluster. A data science team wants to query the same sales tables from its own Redshift Serverless workgroup, always seeing the latest data, without copying it and without using the finance cluster's compute. What should a data engineer do?

  1. AShare the tables through a Redshift datashare that the workgroup consumes
  2. BUNLOAD the tables to Amazon S3 nightly and query them with Redshift Spectrum
  3. CRestore the latest cluster snapshot into the workgroup every morning
  4. DAdd a workload management queue for the data scientists on the cluster
Show the answer and why

  • AShare the tables through a Redshift datashare that the workgroup consumes

    Correct

    Data sharing gives other warehouses access to live data without copying or moving it, with read workload isolation and separately sized compute.

  • BUNLOAD the tables to Amazon S3 nightly and query them with Redshift Spectrum

    Incorrect

    UNLOAD writes a copy of the data to S3, so the data scientists would see last night's copy, not live data.

  • CRestore the latest cluster snapshot into the workgroup every morning

    Incorrect

    A snapshot is a point-in-time backup. Restoring it daily creates a copy that is out of date as soon as finance writes again.

  • DAdd a workload management queue for the data scientists on the cluster

    Incorrect

    WLM queues divide the cluster's own resources among workloads, so the data scientists would still use the finance cluster's compute.

Datashares separate compute from data: each team sizes its own warehouse while reading one live copy of the tables.

AWS documentation

Task 2.1Report a problem with this question

Question 9 · choose 1

A company runs a self-managed Apache Cassandra cluster for device telemetry. It wants a serverless, managed database and wants to keep its existing CQL application code and tools. Which data store should a data engineer choose?

  1. AAmazon DocumentDB (with MongoDB compatibility)
  2. BAmazon Keyspaces (for Apache Cassandra)
  3. CAmazon Neptune with a graph data model
  4. DAmazon MemoryDB as the primary data store
Show the answer and why

  • AAmazon DocumentDB (with MongoDB compatibility)

    Incorrect

    DocumentDB runs MongoDB-compatible application code and drivers, not Cassandra code.

  • BAmazon Keyspaces (for Apache Cassandra)

    Correct

    Amazon Keyspaces is a serverless, managed, Cassandra-compatible service that runs Cassandra workloads with the same application code and tools.

  • CAmazon Neptune with a graph data model

    Incorrect

    Neptune is a graph database for highly connected datasets. It does not run Cassandra code.

  • DAmazon MemoryDB as the primary data store

    Incorrect

    MemoryDB is an in-memory database compatible with Valkey and Redis OSS, not with Cassandra.

Matching the engine's API keeps a migration small: Cassandra code moves to Amazon Keyspaces, MongoDB code to DocumentDB.

AWS documentation

Lesson: SQL vs NoSQLTask 2.1Report a problem with this question

Question 10 · choose 1

Dozens of services send application logs to a central store. Engineers must search the log text within seconds of arrival and build dashboards for real-time application monitoring. Which AWS service should a data engineer use as the store?

  1. AAmazon Neptune
  2. BAmazon Keyspaces (for Apache Cassandra)
  3. CAmazon S3 Glacier Deep Archive
  4. DAmazon OpenSearch Service
Show the answer and why

  • AAmazon Neptune

    Incorrect

    Neptune is a graph database for highly connected data, not a log search engine.

  • BAmazon Keyspaces (for Apache Cassandra)

    Incorrect

    Amazon Keyspaces is a Cassandra-compatible wide-column database. It is not a search engine for log text.

  • CAmazon S3 Glacier Deep Archive

    Incorrect

    Glacier Deep Archive objects are archived and not available for real-time access, which rules out search within seconds.

  • DAmazon OpenSearch Service

    Correct

    OpenSearch Service runs OpenSearch, a search and analytics engine for use cases such as log analytics and real-time application monitoring.

Fast text search plus dashboards over fresh logs is the core OpenSearch use case; archive tiers suit logs kept only for compliance.

AWS documentation

Lesson: Observability BasicsTask 2.1Report a problem with this question

Practise domain 2 →Practise all domains →