Data Store Management

89. An Amazon Redshift fact table is joined to a large dimension table on customer_id, and query plans show most time spent redistributing rows between nodes. Which solution meets these requirements?

1. A Set the distribution style to EVEN on both tables so rows are spread uniformly.
2. B Set the distribution style to KEY on customer_id for both tables so matching rows are co-located.
3. C Set the distribution style to ALL on both tables so every node holds a full copy.
4. D Add a sort key on customer_id to both tables so the join column is ordered.

90. A pipeline must store JSON documents with varying structure and support queries from an application that already uses MongoDB drivers. Which solution meets these requirements?

1. A Store the documents in Amazon DynamoDB and rewrite the application to use the DynamoDB API.
2. B Store the documents in Amazon DocumentDB, which is compatible with MongoDB workloads.
3. C Store the documents in Amazon Redshift after flattening them into a relational schema.
4. D Store the documents as objects in Amazon S3 and query them with Amazon Athena.

91. A Redshift workload runs heavily for a few hours each weekday and is idle otherwise. Which solution meets these requirements at the lowest cost?

1. A Run the workload on a provisioned RA3 cluster sized for the busiest period.
2. B Run the workload on a provisioned cluster and pause it manually each evening.
3. C Run the workload on Amazon Redshift Serverless so capacity is provisioned on demand.
4. D Run the workload on a provisioned DC2 cluster with concurrency scaling enabled.

92. Historical data in Amazon S3 must be queried from Amazon Redshift without loading it into cluster storage. Which solution meets these requirements?

1. A Create a materialized view over the historical data in the cluster.
2. B Run a nightly COPY command that loads the historical data into the cluster.
3. C Create an external schema and query the data with Amazon Redshift Spectrum.
4. D Create a Redshift datashare that exposes the historical data to the cluster.

93. An operational store must serve key-value lookups at single-digit millisecond latency with unpredictable traffic and no capacity planning. Which solution meets these requirements?

1. A Store the items in Amazon RDS with a read replica for the lookups.
2. B Store the items in Amazon DynamoDB configured with on-demand capacity mode.
3. C Store the items in Amazon DynamoDB configured with provisioned capacity sized for the peak.
4. D Store the items in Amazon Redshift and query them with a sort key on the lookup column.

94. Operational data in Amazon Aurora must be analysed in Amazon Redshift without the team building and maintaining an ETL pipeline. Which solution meets these requirements?

1. A Create a Redshift federated query to the Aurora database for each analytical query.
2. B Create an AWS Glue job that reads Aurora over JDBC and writes to Redshift each hour.
3. C Create an AWS DMS task that replicates the Aurora tables into Redshift on a schedule.
4. D Configure an Aurora zero-ETL integration with Amazon Redshift so data replicates continuously.

95. A search workload requires full-text search with aggregations and near-real-time indexing of incoming documents. Which solution meets these requirements?

1. A Store the documents in Amazon S3 and query them with Amazon Athena.
2. B Index the documents in Amazon OpenSearch Service and query them with its search and aggregation APIs.
3. C Store the documents in Amazon DynamoDB and query them with a global secondary index.
4. D Load the documents into Amazon Redshift and query them with SQL LIKE predicates.

96. A caching layer must serve sub-millisecond reads and must survive the loss of a node without losing the cached data. Which solution meets these requirements?

1. A Store the data in Amazon ElastiCache for Memcached with several nodes.
2. B Store the data in Amazon S3 and read it through a CloudFront distribution.
3. C Store the data in Amazon MemoryDB, which provides in-memory performance with durable multi-AZ storage.
4. D Store the data in Amazon DynamoDB with DynamoDB Accelerator in front of it.

97. Schemas across thousands of Amazon S3 prefixes must be discovered and made queryable without manual table definition. Which solution meets these requirements?

1. A Apply Amazon S3 object tags describing the schema of each file.
2. B Run AWS Glue crawlers over the prefixes to infer schemas and populate the AWS Glue Data Catalog.
3. C Create an Amazon RDS instance that holds the schema definitions for each prefix.
4. D Define external schemas in Amazon Redshift Spectrum for each prefix by hand.

98. New hourly partitions written to Amazon S3 are not returned by Amazon Athena until a crawler runs. Which solution meets these requirements with the LEAST operational overhead?

1. A Run an ALTER TABLE ADD PARTITION statement after each hourly write.
2. B Recreate the table definition each hour so it reflects the current prefixes.
3. C Schedule the AWS Glue crawler to run every hour so new partitions are added to the catalog.
4. D Enable partition projection on the table so Athena derives partitions from the prefix pattern.

99. An AWS Glue crawler must catalog tables in an Amazon RDS database reachable only from a private subnet. Which solution meets these requirements?

1. A Make the database publicly accessible and point the crawler at its endpoint.
2. B Create an AWS Glue connection specifying the JDBC target, subnet, and security group, and attach it to the crawler.
3. C Run the crawler from an Amazon EC2 instance placed inside the private subnet.
4. D Export the database tables to Amazon S3 and crawl the exported files instead.

100. One table definition must serve Amazon Athena, Amazon Redshift Spectrum, and Amazon EMR without being maintained separately in each. Which solution meets these requirements?

1. A Store the schema in an Amazon S3 object that each engine parses before querying.
2. B Register the table once in the AWS Glue Data Catalog and configure each engine to use it as its metastore.
3. C Define the table separately in each engine and keep the definitions synchronized manually.
4. D Define the table in Athena only and export the definition to the other engines as needed.

101. An existing Apache Hive metastore on premises must be made available to AWS analytics services during a migration. Which solution meets these requirements?

1. A Recreate every table definition by hand in Amazon Athena.
2. B Migrate the metastore into the AWS Glue Data Catalog, or configure the services to reference the existing Hive metastore.
3. C Export the metastore to CSV and load it into an Amazon RDS instance.
4. D Run Amazon EMR with a local metastore on each cluster.

102. A new AWS Glue connection must be created so that a crawler can catalog data held in an Amazon DocumentDB cluster. Which configuration is required?

1. A Create an Amazon S3 export of the cluster and crawl the exported objects.
2. B Create a Glue Data Catalog database and add the tables manually.
3. C Create a Glue connection of the appropriate type with the cluster endpoint, credentials, and network placement, then reference it from the crawler.
4. D Create a Glue crawler pointed at the cluster endpoint without a connection.

103. Objects in a data lake are queried heavily for 30 days, occasionally for 90 more, and rarely afterwards, and all must be retained for seven years. Which solution meets these requirements at the lowest cost?

1. A Create an S3 Lifecycle configuration that expires the objects after 120 days.
2. B Store all objects in S3 Intelligent-Tiering for the full retention period.
3. C Create an S3 Lifecycle configuration that transitions objects to Standard-Infrequent Access at 30 days and to Glacier Deep Archive at 120 days.
4. D Store all objects in S3 Standard for the full retention period.

104. IoT readings in an Amazon DynamoDB table lose business value after 90 days and must be removed at the lowest cost. Which solution meets these requirements?

1. A Reduce the table's provisioned read capacity once the data reaches 90 days.
2. B Run a nightly job that scans the table and deletes expired items with BatchWriteItem.
3. C Create a new table each quarter and delete the previous table when it expires.
4. D Enable Time to Live on an attribute holding the expiry timestamp so DynamoDB deletes expired items in the background.

105. Curated results must be moved from Amazon Redshift into Amazon S3 for downstream consumers each night. Which solution meets these requirements?

1. A Create an Amazon Athena federated query that reads the Redshift tables each night.
2. B Run an UNLOAD command that writes the query results to Amazon S3 in a partitioned columnar format.
3. C Run a SELECT query and have the pipeline write the returned rows to Amazon S3 from the client.
4. D Create a Redshift materialized view and point the downstream consumers at the cluster.

106. Objects in an Amazon S3 bucket must be recoverable after an accidental overwrite for 30 days, after which the previous versions should be removed. Which combination of steps meets these requirements? (Select TWO.)

1. A Add an S3 Lifecycle rule that expires noncurrent versions after 30 days.
2. B Enable S3 Versioning on the bucket so previous versions are retained on overwrite.
3. C Add an S3 Lifecycle rule that expires current versions after 30 days.
4. D Enable S3 Object Lock in compliance mode with a 30-day retention period.
5. E Configure S3 Cross-Region Replication to a backup bucket.

107. A dataset must be deleted permanently at a specific age to satisfy a legal retention limit. Which solution meets these requirements?

1. A Add an S3 Lifecycle rule that transitions the objects to Glacier Deep Archive at the required age.
2. B Add an S3 Lifecycle rule that expires the objects, and their noncurrent versions, at the required age.
3. C Apply an S3 Object Lock retention period equal to the required age.
4. D Move the objects to a separate bucket and delete the bucket manually each year.

108. An Amazon Redshift dimension table of 200 rows is joined to every large fact table query, and queries are slowed by data redistribution. Which solution meets these requirements?

1. A Set the dimension table's distribution style to KEY on the join column.
2. B Set the dimension table's distribution style to EVEN across the nodes.
3. C Set the dimension table's distribution style to ALL so it is replicated to every node.
4. D Add a sort key to the dimension table on the join column.

109. Analysts must query both the current state of a dimension and its history of changes. Which solution meets these requirements?

1. A Overwrite the dimension row in place whenever an attribute changes.
2. B Store only the latest snapshot of the dimension each night.
3. C Delete and recreate the dimension table from the source system each day.
4. D Model the dimension as a Type 2 slowly changing dimension with effective date ranges and a current-row flag.

110. A source system adds new optional columns to its output every few weeks, and the pipeline must absorb them without failing. Which combination of steps meets these requirements? (Select TWO.)

1. A Convert every column to a single string field so the schema never changes.
2. B Reject records containing any field not present in the original schema.
3. C Run an AWS Glue crawler on a schedule so the catalog table definition is updated.
4. D Write the data in a self-describing format such as Apache Parquet and enable schema merging on read.
5. E Hardcode the expected column list in the ETL script and reject unexpected fields.

111. An Oracle schema using stored procedures and unsupported data types must be converted before migration to Amazon Redshift. Which solution meets these requirements?

1. A Recreate the schema by hand in Redshift based on the Oracle data dictionary.
2. B Export the Oracle schema to CSV and infer the Redshift schema with an AWS Glue crawler.
3. C Convert the schema with the AWS Schema Conversion Tool and review the objects it reports as requiring manual rework.
4. D Migrate the schema with AWS DMS, which converts unsupported objects automatically.

112. An audit must establish which source data produced a given curated table, across several transformation steps. Which solution meets these requirements?

1. A Record the row count of each dataset after every transformation step.
2. B Enable AWS CloudTrail data events for the buckets holding each dataset.
3. C Enable S3 Versioning on the curated table's prefix.
4. D Record data lineage linking each output dataset to the job run and input datasets that produced it.

113. A large Amazon S3 table is queried with filters on region and event date, and query cost must be reduced. Which solution meets these requirements?

1. A Add a secondary index over the region and event date columns.
2. B Partition the table by region and event date so the query engine prunes unneeded prefixes.
3. C Store the table as a single large uncompressed file for sequential reads.
4. D Increase the number of files in each prefix so reads are more parallel.

114. A DynamoDB table stores events keyed by device identifier, and a small number of devices generate most of the traffic. Which solution meets these requirements?

1. A Add a write-sharding suffix to the partition key so traffic is spread across more partitions.
2. B Enable point-in-time recovery on the table.
3. C Increase the table's provisioned write capacity until the throttling stops.
4. D Add a local secondary index on the timestamp attribute.

115. A data lake table accumulates thousands of small files per partition from a streaming writer, and query times have degraded. Which solution meets these requirements?

1. A Enable S3 Versioning so previous file versions are retained for comparison.
2. B Add more partitions so the files are spread across more prefixes.
3. C Move the partition to S3 Glacier Instant Retrieval to reduce storage cost.
4. D Compact the small files into objects of roughly 128 MB or larger on a regular schedule.

116. A pipeline must store event data queried by time range with automatic tiering of older data. Which data store is appropriate?

1. A Amazon RDS with a partitioned table.
2. B Amazon Timestream, which tiers data between memory and magnetic storage by age.
3. C Amazon DynamoDB with a global secondary index on timestamp.
4. D Amazon S3 with hourly prefixes.

117. A pipeline must serve an operational dashboard with sub-second aggregate queries over recent data. Which approach is appropriate?

1. A Query the raw data lake with Amazon Athena on each dashboard load.
2. B Export the raw data to a spreadsheet nightly.
3. C Pre-aggregate the recent data into a store optimised for the dashboard's queries rather than querying raw data on each load.
4. D Query the source transactional database directly.

118. An Amazon Redshift cluster's storage is nearly full, and most data is rarely queried. Which approach is appropriate?

1. A Move the cold data to Amazon S3 and query it through Redshift Spectrum when needed.
2. B Add nodes to the cluster to increase storage.
3. C Compress the tables more aggressively.
4. D Delete the rarely queried data.

119. A data store must support a schema that changes frequently as the application evolves. Which store is appropriate?

1. A A document database such as Amazon DocumentDB or DynamoDB, which does not require a fixed schema.
2. B Amazon Redshift with a wide table.
3. C Amazon RDS with frequent schema migrations.
4. D Amazon S3 with a fixed Parquet schema.

120. A Glue crawler is creating a separate table for each partition directory instead of one partitioned table. Which cause should be investigated first?

1. A Investigate whether the crawler's IAM role lacks permission.
2. B Investigate whether the schemas differ between directories, since a crawler creates separate tables when it cannot reconcile them.
3. C Investigate whether the crawler is scheduled too frequently.
4. D Investigate whether the data is compressed.

121. A Data Catalog table's schema must be updated when a new column appears, without dropping existing columns. Which crawler configuration is appropriate?

1. A Configure the crawler to add new columns and ignore deletions.
2. B Configure the crawler to create a new table on schema change.
3. C Configure the crawler to update the table definition entirely on each run.
4. D Configure the crawler to log changes without updating the table.

122. An Amazon Athena query returns no results for a partition that exists in Amazon S3. Which cause should be investigated first?

1. A Investigate whether the S3 bucket has versioning enabled.
2. B Investigate whether the Athena workgroup has a query limit.
3. C Investigate whether the partition has been registered in the Data Catalog, since Athena reads only catalogued partitions.
4. D Investigate whether the query's WHERE clause is too restrictive.

123. Business terms and data ownership must be discoverable alongside technical schema information. Which approach is appropriate?

1. A Document the business terms in a separate wiki.
2. B Include the business terms in column names.
3. C Record business metadata as table and column properties or in a governance catalog alongside the technical definitions.
4. D Email the business terms to the analytics team.

124. A pipeline writes daily partitions, and partitions older than two years must be removed from both storage and the catalog. Which approach is appropriate?

1. A Apply an S3 lifecycle expiry only, leaving the catalog entries.
2. B Manually delete both each month.
3. C Apply an S3 lifecycle expiry to the prefixes and drop the corresponding catalog partitions on a schedule.
4. D Delete the catalog partitions only, leaving the data.

125. A dataset must be retained for compliance but must never be modified or deleted during the retention period. Which approach is appropriate?

1. A Store it in an S3 bucket with versioning enabled.
2. B Store it in a bucket with a deny-delete bucket policy.
3. C Store it in S3 Glacier Deep Archive.
4. D Store it in an S3 bucket with Object Lock in compliance mode for the retention period.

126. A DynamoDB table's items must expire after 30 days without consuming write capacity for deletion. Which approach is appropriate?

1. A Run a nightly scan deleting old items.
2. B Create a new table each month.
3. C Enable Time to Live on an expiry timestamp attribute.
4. D Reduce the table's provisioned capacity.

127. An Amazon Redshift table must be periodically cleaned of deleted rows to reclaim space and maintain query performance. Which operation is appropriate?

1. A COPY, which loads data from S3.
2. B VACUUM, which reclaims space from deleted rows and re-sorts data.
3. C ANALYZE, which updates table statistics.
4. D UNLOAD, which exports data to S3.

128. A Redshift table is queried mostly by date range and joined on customer_id. Which key configuration is appropriate?

1. A ALL distribution with a sort key on the date column.
2. B A distribution key on the date column and no sort key.
3. C A sort key on the date column and a distribution key on customer_id.
4. D A sort key on customer_id and EVEN distribution.

129. A DynamoDB table must support queries by customer and by order date within customer. Which key design is appropriate?

1. A A partition key of order_id with a global secondary index on customer_id.
2. B A partition key of customer_id and a sort key of order_date.
3. C A partition key of order_date and a sort key of customer_id.
4. D A single partition key combining both values.

130. A schema change adds a required column to a source, and downstream jobs written for the old schema begin failing. Which practice would have prevented this?

1. A A schema registry with compatibility checking that rejects a change breaking existing consumers.
2. B A nightly crawler refresh.
3. C A larger retention period on the source.
4. D A wider column type in the downstream tables.

131. A data lake table must be partitioned to support queries filtering by region and by date. Which partition structure is appropriate?

1. A Partition by date only.
2. B Partition by region, then by date, so either filter prunes prefixes.
3. C Partition by region only.
4. D Partition by a hash of the record identifier.

132. An analytics workload must query semi-structured data with a schema that varies between records. Which approach is appropriate?

1. A Store the data as a single string column and parse it on each query.
2. B Store the data in a columnar format with a permissive schema and query it with an engine supporting nested and optional fields.
3. C Force every record into a fixed relational schema, discarding fields that do not fit.
4. D Reject records that do not match the expected schema.

133. A workload requires both transactional writes and analytical queries over the same data. Which approach is appropriate?

1. A Run analytical queries directly against the transactional database.
2. B Maintain two independent copies updated by separate pipelines.
3. C Run transactions against the analytical warehouse.
4. D Serve transactions from an operational store and replicate to an analytical store, or use a zero-ETL integration between them.

134. A data store must support a workload reading entire columns across billions of rows. Which storage format is appropriate?

1. A An uncompressed text format.
2. B A row-oriented format such as Avro.
3. C A row-oriented format such as CSV.
4. D A columnar format such as Apache Parquet or ORC, which reads only the requested columns.

135. A Data Catalog table's partitions must be registered automatically as new prefixes appear, without running a crawler. Which approach is appropriate?

1. A Register each partition with an ALTER TABLE statement.
2. B Recreate the table definition daily.
3. C Run a crawler after each write.
4. D Enable partition projection on the table so partition values are derived from the prefix pattern at query time.

136. A crawler must not overwrite a table's manually refined column types on each run. Which configuration is appropriate?

1. A Delete the crawler after the first run.
2. B Run the crawler less frequently.
3. C Recreate the table after each crawler run.
4. D Configure the crawler to add new columns only and ignore changes to existing ones.

137. A team must discover which datasets contain a particular column name across a large catalog. Which approach is appropriate?

1. A Maintain a spreadsheet of column names.
2. B Open each table definition in turn.
3. C Search the Data Catalog by column name, since the catalog indexes table and column metadata.
4. D Query each dataset's files directly.

138. An Iceberg table accumulates metadata and orphan files over time. Which maintenance operation is appropriate?

1. A Run table maintenance to expire old snapshots and remove orphan files.
2. B Delete the table's metadata directory.
3. C Reduce the table's partition count.
4. D Rewrite the entire table each month.

139. A dataset must be moved to lower-cost storage after 90 days but remain queryable without a restore. Which storage class is appropriate?

1. A S3 Standard, which is the frequent-access class.
2. B S3 Glacier Instant Retrieval, which offers millisecond retrieval at a lower storage price.
3. C S3 Glacier Deep Archive, which has retrieval times measured in hours.
4. D S3 Glacier Flexible Retrieval, which requires a restore.

140. A pipeline must delete records for a specific customer across a partitioned data lake table to satisfy a deletion request. Which approach is appropriate?

1. A Rewrite the whole table without the customer's records.
2. B Mark the records as deleted in a separate table.
3. C Delete the entire partitions containing the customer's records.
4. D Use a table format supporting row-level deletes so the affected rows are removed without rewriting unrelated partitions.

141. A schema registry must reject a producer change that would break existing consumers. Which compatibility setting is appropriate?

1. A No compatibility checking.
2. B Backward compatibility, which ensures consumers using the previous schema can read data written with the new one.
3. C Full transitive compatibility applied only to the newest version.
4. D Forward compatibility only, which ensures new consumers can read old data.

142. A fact table's grain must be defined before the table is built. What does the grain specify?

1. A How many rows the table will contain
2. B Which storage format the table uses
3. C How the table is partitioned
4. D What a single row represents, such as one line item on one order

143. A dimension table must record the current value of an attribute and nothing more. Which slowly changing dimension type applies?

1. A Type 0, which never changes after the initial load
2. B Type 2, which inserts a new row for each change
3. C Type 3, which adds a column for the previous value
4. D Type 1, which overwrites the attribute in place

144. A Glue crawler must catalog data in a bucket owned by another account. Which configuration is required?

1. A Run the crawler from the owning account only.
2. B Grant the crawler's role read access through the bucket policy in the owning account, and decrypt access if the objects are encrypted.
3. C Make the bucket publicly readable.
4. D Copy the data into the crawler's account.

145. A Data Catalog must support tables whose underlying data is in several formats. Which consideration applies?

1. A All tables in a database must share one format.
2. B Mixed formats require a separate catalog per format.
3. C Each table records its own serialization and format, so mixed formats across tables are supported while a single table's data should be consistent.
4. D The catalog converts formats automatically.