DEA-C01 Data Operations and Support Practice Test 15 — 15 Questions

Question 1

A company uses Amazon Redshift for data warehousing. They notice that queries are running slowly, and the STL_LOAD_ERRORS table shows many 'Parse error' entries. The data is loaded from Amazon S3 using COPY commands. What is the MOST likely cause of the parse errors?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The source data files have a different schema or delimiter than what is specified in the COPY command.. Parse errors during COPY typically indicate that the source data does not match the target table schema (e.g., data type mismatch or delimiter issues). Option B is incorrect because insufficient compute nodes would cause performance degradation, not parse errors. Option C is incorrect because IAM permission issues would cause access denied errors, not parse errors. Option D is incorrect because unsupported compression formats would cause decompression errors, not parse errors.

Answer

The Redshift cluster does not have enough compute nodes to process the data.

Answer

The IAM role used by Redshift does not have permission to decrypt the S3 objects.

Answer

The source data files are compressed using an unsupported compression format.

Question 2

A data engineer is monitoring Amazon CloudWatch metrics for an Amazon Redshift cluster and notices high CPU utilization. The engineer wants to reduce CPU usage. Which TWO actions should the engineer take?

Accepted Answer

Enable concurrency scaling to offload read queries to additional clusters.. Options A and C are correct. Enabling concurrency scaling offloads read queries to additional clusters, reducing CPU load on the main cluster. Optimizing table design with sort keys and compression reduces the amount of data scanned per query, lowering CPU usage. Option B (increasing node count) increases overall CPU capacity but does not reduce usage; it may even increase cost without addressing inefficiency. Option D (running VACUUM) primarily reclaims disk space and maintains data distribution, not CPU reduction. Option E (enabling audit logging) adds CPU overhead, making it counterproductive.

Answer

Increase the number of nodes in the cluster.

Answer

Run the VACUUM command on all tables.

Answer

Enable audit logging to monitor queries.

Question 3

A data engineer notices that an AWS Glue ETL job that processes streaming data from Amazon Kinesis Data Streams is failing intermittently with a 'ResourceNotFoundException' error for the Kinesis stream. The job has been running successfully for weeks. Which action should the engineer take to resolve the issue?

Accepted Answer

Add the 'kinesis:DescribeStream' permission to the IAM role used by the Glue job.. The most common cause of intermittent 'ResourceNotFoundException' for a Kinesis stream is that the IAM role used by the Glue job does not have the kinesis:DescribeStream permission, which is required for the job to check stream details. Option A is incorrect because increasing the Kinesis shard count would not resolve a permissions issue. Option B is incorrect because the Kinesis stream name must match exactly; case sensitivity would cause a consistent error, not intermittent. Option D is incorrect because the timeout setting on the Glue job would not cause a resource not found error.

Answer

Increase the number of shards in the Kinesis data stream to handle higher throughput.

Answer

Rename the Kinesis data stream to match the stream name used in the Glue job exactly, including case.

Answer

Increase the timeout for the Glue job in the job configuration.

Question 4

A company uses AWS Glue to run ETL jobs on a schedule. Recently, a job failed with the error: 'AnalysisException: cannot resolve '`column_name`' given input columns: ...'. The job reads from an Amazon S3 source that has a schema defined in the AWS Glue Data Catalog. What is the MOST likely cause?

Accepted Answer

The schema of the source data has changed and is not reflected in the Data Catalog.. The error 'cannot resolve column_name' indicates that the Spark SQL query is trying to reference a column that does not exist in the schema provided by the AWS Glue Data Catalog. This typically happens when the source data schema has changed (e.g., column renamed or dropped) but the Data Catalog schema is not updated accordingly. Option B is incorrect because a corrupted file would cause a read or parse error, not a schema resolution error. Option C is incorrect because an IAM permissions issue would result in an AccessDenied error. Option D is incorrect because a data type mismatch would cause a type casting error, not a 'cannot resolve' error which is about column names.

Answer

The source data file is corrupted and cannot be parsed.

Answer

The IAM role associated with the Glue job does not have permissions to read the S3 bucket.

Answer

The data type of the column in the source does not match the Data Catalog definition.

Question 5

A company uses AWS DMS to replicate data from an on-premises Oracle database to Amazon RDS for MySQL. The full load completes successfully, but ongoing replication (CDC) is failing with a 'Failed to add supplemental logging' error. What should the data engineer do to resolve this issue?

Accepted Answer

Enable supplemental logging on the source Oracle database manually.. The 'Failed to add supplemental logging' error occurs because AWS DMS requires supplemental logging on the source Oracle database for CDC to capture changes. DMS typically attempts to add supplemental logging automatically, but if it fails (e.g., due to insufficient privileges), the engineer must enable it manually. Option A is correct: enabling supplemental logging manually resolves the issue. Option B is incorrect because recreating the endpoint does not address the missing supplemental logging. Option C is incorrect because the target MySQL engine version is unrelated to the source Oracle logging requirement. Option D is incorrect because increasing the task log interval only affects log output frequency, not the underlying logging configuration.

Answer

Recreate the DMS endpoint for the source database with a new connection.

Answer

Modify the target MySQL database to use a different engine version.

Answer

Increase the task log interval in the DMS task settings.

Question 6

A data engineer needs to back up an Amazon DynamoDB table daily. The backup must be restorable to a specific point in time within the last 24 hours. Which solution meets these requirements with the LEAST operational overhead?

Accepted Answer

Enable point-in-time recovery (PITR) on the table.. DynamoDB's point-in-time recovery (PITR) provides continuous backups that allow restoration to any point within the last 35 days with no manual scheduling. Option A is incorrect because on-demand backups are manual and not continuous. Option B is incorrect because DynamoDB Streams captures changes in near-real time but does not provide a backup mechanism; it is used for event-driven processing and replication, not for point-in-time restoration. Option D is incorrect because exporting to S3 requires manual scheduling and is not a backup feature.

Answer

Create an on-demand backup of the table every 24 hours.

Answer

Use DynamoDB Streams to replicate data to another table.

Answer

Export the table data to Amazon S3 every 6 hours using a Lambda function.

Question 7

A company runs a critical PostgreSQL database on Amazon RDS. The database experiences high read latency during peak hours. The data engineer needs to reduce read latency with minimal changes to the application. Which solution is MOST effective?

Accepted Answer

Create a read replica of the RDS instance and redirect read queries to it.. Creating a read replica offloads read queries from the primary instance, reducing read latency with minimal application changes. The application simply needs to direct read-only queries to the replica endpoint. Option A is incorrect because deleting unused indexes may help write performance but does not directly address high read latency during peak hours. Option B is incorrect because enabling Multi-AZ is for high availability and failover, not for improving read performance. Option C is incorrect because increasing the DB instance class can improve performance but is more disruptive and costly compared to adding a read replica, and may require downtime for resizing.

Answer

Delete unused indexes to improve query performance.

Answer

Enable Multi-AZ deployment for automatic failover.

Answer

Increase the DB instance class to a larger size with more memory.

Question 8

A data engineer is monitoring an Amazon Redshift cluster and notices that some queries are experiencing high disk usage and slow performance. The engineer wants to identify the queries that are causing the most disk spills to temporary files. Which system table should the engineer query to get this information?

Accepted Answer

SVL_QUERY_SUMMARY. SVL_QUERY_SUMMARY. This system view provides information about disk spills for each query step, including the number of bytes spilled to temporary files. It is used to identify queries causing high disk usage and slow performance due to spills. Option B, SYS_QUERY_DETAIL, contains general query execution details but not spill information. Option C, STL_SCAN, tracks table scan operations, not disk spills. Option D, STV_TBL_PERM, shows permanent table storage statistics, not temporary spill data.

Answer

SYS_QUERY_DETAIL

Answer

STL_SCAN

Answer

STV_TBL_PERM

Question 9

A data engineer is troubleshooting a data pipeline that uses Amazon Kinesis Data Firehose to deliver data to Amazon S3. The engineer notices that the S3 bucket contains many small files (less than 1 MB). This is causing performance issues in downstream processing. What is the BEST way to reduce the number of small files?

Accepted Answer

Increase the buffer size to at least 128 MB in the Firehose delivery stream configuration.. Increasing the buffer size (e.g., to 128 MB) causes Firehose to deliver fewer, larger files, reducing the number of small files. Option B is incorrect because using a Lambda transformation does not inherently change buffering behavior. Option C is incorrect because changing the compression format (e.g., from GZIP to Snappy) affects storage size but not the number of files delivered. Option D is incorrect because decreasing the buffer interval would cause more frequent deliveries, creating more small files.

Answer

Use an AWS Lambda function to transform the data before delivery.

Answer

Change the compression format from GZIP to Snappy.

Answer

Decrease the buffer interval in the Firehose delivery stream configuration.

Question 10

A company is running an Amazon EMR cluster with Spark for data processing. The data engineer wants to automatically scale the core and task nodes based on the YARN memory and CPU utilization. Which scaling metric should the engineer use for the EMR managed scaling policy?

Accepted Answer

YARNMemoryAvailablePercentage. EMR managed scaling uses YARNMemoryAvailablePercentage and YARNContainersPending as the default metrics for scaling. Option B is incorrect because CPUUtilization is not a default metric for EMR managed scaling. Option C is incorrect because HDFSUtilization is for HDFS, not YARN. Option D is incorrect because IOPS is not a metric for EMR managed scaling.

Answer

CPUUtilization

Answer

DiskIOPS

Answer

HDFSUtilization

Question 11

A data engineer is using Amazon EMR to process large datasets. The cluster uses a mix of Spot Instances and On-Demand Instances. The engineer wants to reduce costs while ensuring the job can complete even if Spot Instances are reclaimed. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Enable Instance Fleets to use multiple instance types for Spot Instances.. Enabling Instance Fleets allows EMR to use multiple instance types for Spot Instances, reducing the risk of interruption by diversifying across instance pools. Option E is correct because using a task instance group with Spot Instances for non-critical tasks ensures that if Spot Instances are reclaimed, only those non-critical tasks are affected, while core and master nodes running on On-Demand continue processing. Option B is incorrect because using only On-Demand increases costs. Option C is incorrect because using Spot Instances for core nodes risks data loss or job failure if they are reclaimed, as HDFS data is stored on core nodes. Option D is incorrect because termination protection prevents accidental termination, but does not address Spot Instance interruptions.

Answer

Use only On-Demand Instances for all nodes.

Answer

Use Spot Instances for core nodes to reduce cost.

Answer

Enable termination protection for the cluster.

Question 12

A data engineer is troubleshooting a failed AWS Glue job that reads from an Apache Hive metastore in an Amazon EMR cluster. The error message indicates 'ClassNotFoundException: org.apache.hadoop.hive.ql.metadata.HiveException'. The Glue job uses a custom Python shell script. What is the most likely cause of this error?

Accepted Answer

Include the Hive JAR files in the 'Python library path' or use a Glue version with Hive support.. The ClassNotFoundException for Hive classes indicates that the required Hive JARs are not available in the Glue job's classpath. The solution is to include the Hive JAR files in the 'Python library path' or use a Glue version that supports Hive connectivity. Option A is incorrect because a network connectivity issue would cause a different error, such as a timeout or connection refused. Option C is incorrect because simply adding an import statement in the Python script does not provide the underlying JAR files; the JARs must be included via library path. Option D is incorrect because IAM permissions do not affect class loading; the error is related to missing dependencies, not authorization.

Answer

Check the network connectivity between Glue and the EMR cluster.

Answer

Modify the Python script to import the Hive libraries manually.

Answer

Update the IAM role to allow 'hive:Describe*' actions.

Question 13

A company uses Amazon S3 to store sensitive data. The security team requires that all data in transit between on-premises applications and S3 be encrypted. The data engineer must implement a solution that meets this requirement without changing the applications. Which TWO solutions should the engineer consider? (Choose two.)

Accepted Answer

Use an S3 VPC Endpoint and enforce the use of HTTPS through bucket policies.. Using an S3 VPC Endpoint with a bucket policy that enforces HTTPS ensures all traffic to S3 is encrypted in transit, without any application changes. Option E is correct because AWS Storage Gateway can mount S3 as a file system and be configured to use HTTPS, enforcing encryption in transit. Option A is incorrect because S3 Transfer Acceleration only improves speed over long distances, it does not enforce encryption—it optionally uses HTTPS but the application must still use HTTPS. Option B is incorrect because default encryption (SSE-S3, SSE-KMS, DSSE-KMS) encrypts data at rest, not in transit. Option C is incorrect because server-side encryption with SSE-S3 also only encrypts data at rest.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Enable default encryption on the S3 bucket.

Answer

Use server-side encryption with S3 managed keys (SSE-S3).

Question 14

A company stores sensitive data in Amazon S3 and uses AWS Lake Formation to manage fine-grained access control. A data engineer notices that users are able to access data in S3 directly via the AWS Management Console, bypassing Lake Formation permissions. What should the engineer do to enforce Lake Formation access controls for all access methods?

Accepted Answer

Register the S3 location in Lake Formation and disable IAM access control for the registered location.. To enforce Lake Formation permissions for all access methods, you must register the S3 location in Lake Formation and disable IAM access control for that location. This ensures that Lake Formation's fine-grained permissions are enforced, preventing direct S3 access. Option A is incorrect because adding a bucket policy that denies all access except from Lake Formation is not the recommended approach and can break legitimate access. Option B is incorrect because disabling CloudTrail does not affect access control. Option D is incorrect because S3 Block Public Access only prevents public access, not IAM user access.

Answer

Add a bucket policy that denies all access except from Lake Formation.

Answer

Disable AWS CloudTrail logging for S3 access.

Answer

Enable S3 Block Public Access on the bucket.

Question 15

A data engineer is designing a data pipeline that processes streaming data. The pipeline must be able to handle duplicate records and ensure exactly-once processing semantics. Which THREE AWS services or features should the engineer consider? (Choose three.)

Accepted Answer

Amazon EMR with Apache Flink for exactly-once semantics.. The three correct options are A, C, and E. Option A (Amazon EMR with Apache Flink) supports exactly-once processing semantics through checkpointing and fault tolerance. Option C (Amazon Kinesis Data Streams with sequence numbers) enables deduplication to achieve exactly-once processing. Option E (Amazon Kinesis Data Analytics for Apache Flink with idempotent sinks) ensures exactly-once delivery by recognizing and ignoring duplicates. Options B and D are incorrect: Amazon Kinesis Data Firehose provides at-least-once delivery, and Amazon DynamoDB Streams also provides at-least-once delivery, not exactly-once semantics.

Answer

Amazon Kinesis Data Firehose with automatic retries.

Answer

Amazon DynamoDB Streams for change data capture.