DEA-C01 Hard Questions — 25 Difficult Questions

Question 1

An e-commerce company uses AWS Glue to run ETL jobs that transform clickstream data from Amazon S3. The job reads Parquet files, performs aggregations, and writes the results to Amazon Redshift. The job runs successfully but takes longer than expected. The data volume is increasing. Which design change would MOST improve the job's performance?

Accepted Answer

Increase the number of Glue worker nodes (DPUs) for the job.. Increasing the number of Glue worker nodes (DPUs) directly scales the distributed processing capacity of the ETL job, allowing it to process larger volumes of Parquet data in parallel. This is the most straightforward way to reduce execution time when data volume is growing, as AWS Glue automatically partitions the workload across the additional workers.

Answer

Write the aggregated results to a single large file instead of multiple partitions.

Answer

Convert the Parquet files to CSV to simplify the schema.

Answer

Replace the Redshift target with Amazon Redshift Spectrum.

Question 2

A data engineering team uses Amazon Kinesis Data Analytics for Apache Flink to process streaming data. They notice that the application's checkpointing is failing intermittently, causing data reprocessing. The application uses a large state. Which configuration change should the team make to improve checkpoint reliability?

Accepted Answer

Increase the checkpointing interval.. Increasing the checkpointing interval reduces the frequency of checkpoint operations, giving the system more time to complete each checkpoint before the next one starts. This alleviates backpressure and resource contention, which is critical when dealing with large state, as checkpointing large state is I/O and CPU intensive and can fail if intervals are too tight.

Answer

Disable checkpointing to avoid failures.

Answer

Switch the state backend from in-memory to RocksDB.

Answer

Increase the parallelism of the application.

Question 3

A company runs a nightly AWS Glue ETL job that reads from a JDBC source (PostgreSQL) and writes to S3 in Parquet format. The job takes over 6 hours, but the SLA requires completion within 4 hours. The source table has 500 million rows and is updated frequently. Which approach will most reliably reduce job duration?

Accepted Answer

Partition the source table by year and use pushdown predicates in the Glue job.. Option C is correct because partitioning the source table by year and using pushdown predicates allows AWS Glue to read only the relevant partitions from PostgreSQL, drastically reducing the data scanned and transferred. This directly addresses the 500 million row volume and frequent updates by minimizing the JDBC read workload, which is the primary bottleneck in the 6-hour runtime.

Answer

Enable job bookmark and schedule the job to run more frequently.

Answer

Use multiple JDBC connections in parallel by setting 'hashexpression' and 'hashfield'.

Answer

Increase the number of DPUs for the Glue job to 100.

Question 4

A company uses Amazon DynamoDB with on-demand capacity. They notice higher than expected costs due to a sudden spike in read traffic from a reporting job. The reporting job scans the entire table daily. What is the most cost-effective way to reduce costs while maintaining the same reporting output?

Accepted Answer

Use a Global Secondary Index (GSI) with a sort key that matches the reporting query pattern.. Option B is correct because using a Global Secondary Index (GSI) with a sort key tailored to the reporting query pattern allows the reporting job to query only the relevant items instead of scanning the entire table. This reduces the read capacity units consumed per operation, directly lowering costs under on-demand capacity, which charges per RCU consumed. The reporting output remains identical because the GSI returns the same data filtered by the query pattern.

Answer

Enable DynamoDB Accelerator (DAX) for caching.

Answer

Set a TTL attribute to automatically expire old data.

Answer

Reduce the read capacity units (RCU) in the table.

Question 5

A data engineer attaches the above IAM policy to an IAM user. The user tries to download an object from my-bucket using the AWS CLI without specifying SSE headers. The object is stored with SSE-S3. Will the download succeed?

Accepted Answer

No, because the request does not include the required encryption header.. Option B is correct because when an object is stored with SSE-S3, AWS S3 requires that any request to download it without specifying the `x-amz-server-side-encryption` header (or the equivalent CLI parameter) will fail. The IAM policy grants `s3:GetObject` but does not override the S3 API's requirement for the encryption header to be present in the request. Without the header, S3 rejects the request with a `400 Bad Request` error, even though the user has the necessary IAM permissions.

Answer

No, because the object is encrypted and the user does not have decrypt permission.

Answer

Yes, because the object is encrypted with SSE-S3, which uses AES256.

Answer

Yes, because the policy allows s3:GetObject on the bucket.

Question 6

A data engineer is designing a data ingestion pipeline for IoT sensor data. The data arrives as JSON via AWS IoT Core, and must be stored in Amazon S3 in partitioned Parquet format. The pipeline must handle late-arriving data (up to 1 hour) and ensure exactly-once processing. Which combination of services should the engineer use?

Accepted Answer

Amazon Kinesis Data Firehose with data transformation via AWS Lambda, delivering to Amazon S3.. Amazon Kinesis Data Firehose is the correct choice because it can directly ingest streaming data from AWS IoT Core, use a built-in AWS Lambda function to transform JSON to Parquet, and deliver the data to Amazon S3 with automatic partitioning. It also supports buffering and retry logic to handle late-arriving data (up to 1 hour) and provides exactly-once delivery to S3 when configured with the appropriate error handling and idempotent transformations.

Answer

Amazon Kinesis Data Streams with AWS Lambda for transformation and Amazon S3.

Answer

Amazon Simple Queue Service (SQS) with AWS Lambda for transformation and Amazon S3.

Answer

AWS Glue streaming jobs consuming from Amazon Kinesis Data Streams and writing to Amazon S3.

Question 7

A company has a Glue ETL job that reads from an Amazon RDS for MySQL table and writes to Amazon S3. The job runs hourly and processes new records based on a 'last_modified' timestamp column. Recently, the job started missing some records because the timestamp in MySQL is stored with microsecond precision but Glue's job bookmark only tracks second precision. Which solution addresses this issue?

Accepted Answer

Use a job parameter to store the last processed timestamp with millisecond precision and query records greater than that value.. Option A is correct because AWS Glue job bookmarks track timestamps with only second precision, so records with microsecond differences within the same second are missed. By using a custom job parameter to store the last processed timestamp with millisecond precision and querying records greater than that value, you bypass Glue's bookmark limitation and capture all new or modified records.

Answer

Increase the job frequency to every 30 minutes.

Answer

Run a full refresh of the table each time instead of incremental.

Answer

Modify the MySQL table to use a DATE data type instead of TIMESTAMP.

Question 8

A data engineer is troubleshooting an AWS Glue job that reads from Amazon S3 and writes to Amazon Redshift. The job runs successfully but 5% of records are missing after the load. The engineer suspects data consistency issues. Which THREE actions could help diagnose and resolve the problem? (Choose THREE.)

Accepted Answer

Use the Redshift COPY command with a manifest file to load data.. Option A is correct because using the Redshift COPY command with a manifest file ensures that only the exact files listed in the manifest are loaded, eliminating the risk of partial or duplicate reads from S3. This is a common pattern to guarantee data consistency when the Glue job may not reliably track which files have been processed, especially in scenarios with concurrent writes or retries.

Answer

Increase the number of DPUs for the Glue job.

Answer

Use a staging table in Redshift with a transaction to commit.

Question 9

A company uses Amazon RDS for MySQL as a source for AWS DMS to replicate data to S3. The replication task is failing with 'OutOfMemory' errors on the DMS instance. The source table has 10 million rows with large BLOB columns. Which THREE changes would most likely resolve the issue?

Accepted Answer

Set the LOB column settings to 'Limited LOB mode' and specify a max LOB size.. Option A is correct because setting LOB columns to 'Limited LOB mode' with a specified max LOB size prevents DMS from loading entire LOBs into memory. Instead, DMS truncates LOBs to the specified size, reducing memory consumption and avoiding OutOfMemory errors when replicating large BLOB columns from MySQL to S3.

Answer

Disable logging for the DMS task to free memory.

Answer

Enable Full LOB mode to handle LOBs more efficiently.

Question 10

A data engineer is setting up an Amazon Kinesis Data Analytics application to process streaming data from a Kinesis data stream named "input-stream". The application uses a reference data source from an S3 bucket. The engineer has attached the IAM policy shown in the exhibit to the application's IAM role. When starting the application, the engineer receives an 'AccessDeniedException' error. Which additional permission is required?

Accepted Answer

s3:GetObject on the S3 bucket containing the reference data. The Kinesis Data Analytics application needs to read reference data from the S3 bucket, which requires the s3:GetObject permission on the bucket and its objects. The error 'AccessDeniedException' indicates the IAM role lacks this specific permission to retrieve the reference data file. Option B correctly adds the missing s3:GetObject action to allow the application to fetch the reference data from S3.

Answer

kinesis:PutRecord on the input stream

Answer

kinesis:CreateStream on the input stream

Answer

kinesis:PutRecords on the input stream

Question 11

A company is migrating a legacy data warehouse to Amazon Redshift. They need to choose a distribution style to minimize data movement during joins. Which THREE factors should they consider?

Accepted Answer

The size of the table (number of rows).. Option A is correct because the size of the table (number of rows) directly influences the distribution strategy. In Amazon Redshift, large tables benefit from a distribution style that evenly distributes rows across slices to avoid data skew, which can cause performance bottlenecks during joins. Choosing a distribution key that aligns with the join columns minimizes data movement, but the table size determines whether an ALL distribution (for small tables) or a KEY distribution (for large tables) is more appropriate to reduce shuffling.

Answer

The number of columns in the table.

Answer

The data type of the distribution key column.

Question 12

A data engineer is designing a data lake on Amazon S3. The data must be immutable and support high-throughput streaming ingestion. Which THREE features should the engineer consider? (Select THREE.)

Accepted Answer

S3 Lifecycle policies to transition data to Amazon S3 Glacier. S3 Object Lock in governance mode (Option D) is correct because it enforces immutability by preventing objects from being deleted or overwritten for a specified retention period, which is essential for a data lake requiring immutable data. S3 Multipart Upload API (Option C) is correct because it enables high-throughput streaming ingestion by allowing large objects to be uploaded in parallel parts, improving throughput and resilience. S3 Lifecycle policies to transition data to Amazon S3 Glacier (Option B) is correct because it supports cost-effective storage management for immutable data that is rarely accessed, aligning with the data lake's lifecycle needs.

Answer

S3 Transfer Acceleration

Answer

S3 Cross-Region Replication (CRR)

Question 13

A data team runs a daily AWS Glue ETL job that processes data from an Amazon Redshift cluster and writes results to Amazon S3. The job completes successfully but takes 2 hours longer than expected. The job uses the JDBC connection to Redshift. The Redshift cluster is 4 dc2.large nodes. The Glue job has 10 workers of type G.1X. Which change would MOST likely reduce the job duration?

Accepted Answer

Use the S3 staging option in the Glue connection to unload data from Redshift to S3 first. The JDBC connection in AWS Glue reads data row-by-row from Redshift, which is slow for large datasets. By enabling the S3 staging option in the Glue connection, the job uses Redshift's UNLOAD command to export data to S3 in parallel, then Glue reads from S3. This bypasses the JDBC bottleneck and leverages Redshift's massively parallel processing (MPP) to export data much faster.

Answer

Use Redshift Spectrum to query data directly from S3

Answer

Increase the Redshift cluster size to 8 nodes

Answer

Increase the number of Glue workers to 20

Question 14

A data engineer is designing a data lake on Amazon S3 for analytics. The data includes sensitive PII that must be encrypted at rest. The company requires that the encryption keys be managed by the company's own hardware security module (HSM) and rotated every 90 days. Which TWO options meet these requirements? (Choose TWO.)

Accepted Answer

Use S3 server-side encryption with customer-provided keys (SSE-C). Option B is correct because SSE-C allows you to provide your own encryption keys, which can be managed and rotated from your own HSM. The keys are used server-side by S3 to encrypt objects at rest, but S3 does not store the keys—you manage them entirely, meeting the requirement for key management on your own HSM with 90-day rotation.

Answer

Use S3 server-side encryption with AWS KMS and an AWS managed key

Answer

Use client-side encryption with keys stored in AWS Secrets Manager

Answer

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

Question 15

A data engineer is troubleshooting an AWS Glue job that reads from an Amazon RDS for PostgreSQL database using a JDBC connection. The job fails with the error 'java.sql.SQLException: No suitable driver'. Which TWO actions should the engineer take to resolve this issue? (Select TWO.)

Accepted Answer

Verify that the connection string in the job's JDBC URL uses the correct format and includes the driver class. Option A is correct because the 'No suitable driver' error in JDBC indicates that the driver class specified in the JDBC URL is either missing or incorrect. For PostgreSQL, the JDBC URL must follow the format 'jdbc:postgresql://host:port/database' and the driver class must be 'org.postgresql.Driver'. If the URL is malformed or the driver class is not properly referenced, the Glue job cannot load the driver, leading to this specific SQLException.

Answer

Check that the Glue job's VPC and security groups allow outbound traffic to the RDS instance

Answer

Restart the Glue job with a higher timeout value

Answer

Update the IAM role associated with the Glue job to allow 'rds:*' permissions

Question 16

A data engineer is troubleshooting an AWS Lake Formation permissions issue. A user is able to query an Amazon Athena table but cannot see the underlying S3 data in the AWS Glue Data Catalog. The user has been granted SELECT permission on the table in Lake Formation. What is the most likely cause?

Accepted Answer

The user does not have DESCRIBE permission on the table in Lake Formation.. In AWS Lake Formation, the ability to query a table via Athena (which requires SELECT permission) is separate from the ability to view the table's metadata in the Glue Data Catalog. To see the underlying S3 data location or table properties in the catalog, a user needs DESCRIBE permission on the table. Without DESCRIBE, the table appears invisible in the Glue console or API, even though SELECT queries succeed.

Answer

The data location is not registered with Lake Formation.

Answer

The S3 bucket policy does not grant the user access.

Answer

The user does not have the aws:SourceArn condition in the IAM policy.

Question 17

A data engineer is troubleshooting an AWS Glue ETL job that reads from Amazon S3 and writes to Amazon Redshift. The job runs successfully but writes duplicate rows into Redshift. The source data is static and does not contain duplicates. Which configuration change is most likely to resolve this issue?

Accepted Answer

Modify the job to use the 'postactions' option with a SQL statement that deletes duplicates before final insert.. The job runs successfully but writes duplicate rows because AWS Glue's Spark-based ETL jobs can retry tasks on failure, and when writing to Redshift using the JDBC connector, the default behavior is to append data without deduplication. Using the 'postactions' option with a SQL DELETE statement that removes duplicates before the final INSERT ensures that only unique rows remain, resolving the duplication without altering the source data.

Answer

Enable the 'upsert' feature in the Redshift connection by setting 'update' to true.

Answer

Use partition pruning on the S3 source to reduce the number of files read.

Answer

Increase the number of DPUs (Data Processing Units) allocated to the job.

Question 18

A data engineer is setting up an Amazon Redshift cluster for a data warehouse. The cluster will store historical sales data and support complex analytical queries. To optimize query performance and manage storage, the engineer needs to choose appropriate distribution styles and sort keys for a large fact table 'sales' and several dimension tables. Which TWO of the following design decisions are BEST practices?

Accepted Answer

Use interleaved sort keys on columns that are frequently used in filter predicates (e.g., date, region, product).. Option A is correct because interleaved sort keys in Amazon Redshift give equal weight to each column in the sort key, making them ideal for queries with filter predicates on multiple columns (e.g., date, region, product). This design optimizes zone maps and minimizes the amount of data scanned, significantly improving query performance for complex analytical workloads on large fact tables.

Answer

Use ALL distribution for the 'sales' fact table to replicate data to every node and avoid data movement.

Answer

Use a compound sort key with the most frequently filtered column first.

Answer

Choose AUTO distribution style for all tables and let Amazon Redshift automatically assign distribution.

Question 19

A data engineering team uses Amazon Redshift for analytics. They notice that queries on a large fact table are slow. The table is distributed using DISTSTYLE ALL. Which design change would most likely improve query performance?

Accepted Answer

Change DISTSTYLE to KEY on a column used in frequent joins.. DISTSTYLE ALL copies the entire table to every node, which is inefficient for large fact tables because it wastes storage and network bandwidth during data loading and query execution. Changing to DISTSTYLE KEY on a column used in frequent joins collocates related rows on the same slice, reducing the need to broadcast or redistribute data across the network during joins, which directly improves query performance.

Answer

Change DISTSTYLE to EVEN to distribute rows evenly across slices.

Answer

Increase the number of nodes in the Redshift cluster.

Answer

Change the table to use a SORTKEY on the most frequently filtered column.

Question 20

A data engineer is designing a data lake on Amazon S3 with AWS Lake Formation. The data lake contains personally identifiable information (PII). The company has a policy that only users who have completed data privacy training can access the PII data. The training status is stored in an external identity provider (IdP) as an attribute. The data engineer needs to enforce this policy using Lake Formation. Which THREE steps should the data engineer take? (Choose THREE.)

Accepted Answer

Create an LF-tag called 'trainingCompleted' with values 'true' and 'false'. Grant 'SELECT' permission on the LF-tag 'trainingCompleted=true' to the federated users.. Option A is correct because LF-tags allow Lake Formation to manage access based on metadata attributes. By creating an LF-tag 'trainingCompleted' with values 'true' and 'false', and granting SELECT permission on the tag value 'true' to federated users, the data engineer can enforce that only users with the training attribute can access the tagged resources. This approach decouples access control from IAM roles and leverages tag-based authorization, which is the recommended method for attribute-based access control (ABAC) in Lake Formation.

Answer

Create a column-level filter on the PII columns that limits access based on the user's training attribute.

Answer

Create an IAM role for each user and attach a policy that allows 'lakeformation:GetDataAccess' only if the user has the training attribute.