Question 160

A Redshift ETL appears idle while other sessions run, and logs suggest a lock wait on a target table. What should be inspected?

  1. Partitioned load scheduling
  2. Redshift lock monitoring
  3. Shorter database transaction
  4. Relational isolation-level selection

Explanation

Correct Answer

B:

Redshift system views and query monitoring expose sessions waiting on resource or transaction locks; in this scenario, examine active queries, transactions and lock status to identify the blocker before terminating or rescheduling workloads.

Incorrect Answers

A:

Loading separate table partitions at coordinated intervals can reduce contention with user-facing operations; this can support avoiding bulk-load conflicts with live warehouse queries, but would not address the need for diagnosing warehouse query waits caused by locks.

C:

Reducing transaction length releases row and table locks sooner in conventional relational engines; although useful for limiting lock contention from long-running batch updates, it leaves the need for diagnosing warehouse query waits caused by locks unmet.

D:

Transaction isolation controls the concurrency anomalies a database transaction permits under its engine semantics; it would target choosing consistency guarantees for simultaneous source updates, while the question is about diagnosing warehouse query waits caused by locks.

Question 161

A product team needs warehouse joins and transformations but traffic rises unpredictably and operations staffing is limited. Which deployment model fits?

  1. Provisioned Redshift cluster
  2. Lambda short execution
  3. EMR persistent cluster
  4. Athena on-demand SQL
  5. Redshift Serverless

Explanation

Correct Answer

E:

Redshift Serverless runs warehouse workloads without manually managing provisioned clusters; in this scenario, use a Redshift Serverless workgroup and namespace, controlling compute settings and usage limits to balance demand and cost.

Incorrect Answers

A:

Provisioned Redshift compute provides explicit sizing and reservations for more predictable warehouse workloads; this can support optimizing sustained analytics demand under stable capacity, but would not address the need for running variable warehouse SQL workloads with less infrastructure management.

B:

Lambda charges for invoked execution and suits bounded tasks under platform runtime constraints; this helps with handling sporadic lightweight event transformations without idle servers, not running variable warehouse SQL workloads with less infrastructure management.

C:

A persistent EMR cluster can run continuing workloads with custom node and framework configurations; although useful for maintaining specialized continuously running Spark infrastructure, it leaves the need for running variable warehouse SQL workloads with less infrastructure management unmet.

D:

Athena can query data in S3 without administering a persistent warehouse cluster; it would target serving occasional lake SQL queries with variable demand, while the question is about running variable warehouse SQL workloads with less infrastructure management.

Question 162

A data lake is partitioned by unique user ID, leaving almost one object in each prefix and poor planning performance. What design change fits?

  1. Relational composite index order
  2. S3 partition cardinality
  3. Parquet compression codec
  4. Redshift distribution key choice

Explanation

Correct Answer

B:

Partitioning by extremely high-cardinality fields can create excessive small partitions and metadata overhead; in this scenario, partition on coarser query-selective dimensions and use file-level filtering for high-cardinality keys.

Incorrect Answers

A:

The order of columns in a composite B-tree index affects which query predicates can use its leading keys; this helps with optimizing multi-column predicates in an operational database, not avoiding millions of tiny lake partitions.

C:

A compression codec changes encoded data size and decompression trade-offs for columnar lake files; it would target reducing bytes stored and scanned for Parquet files, while the question is about avoiding millions of tiny lake partitions.

D:

Redshift distribution style influences where rows reside and how much data moves during joins; its benefit is reducing redistribution for frequent large-table joins; that is different from avoiding millions of tiny lake partitions.

Question 163

Transaction events enter the lake hours before corresponding customer dimension updates. Which ETL strategy avoids losing facts?

  1. Time zone normalization
  2. Late-arriving dimension handling
  3. Deduplication across sources
  4. Stable join key

Explanation

Correct Answer

B:

Late-arriving dimensions require a defined strategy for facts received before their lookup rows; in this scenario, preserve unmatched facts with a retry or unknown-member mechanism so later dimension arrivals can resolve them.

Incorrect Answers

A:

Normalizing timestamps to a common time basis prevents false ordering across systems using different zones; it would target correlating event timestamps from multiple geographic sources, while the question is about loading facts that precede their dimension records.

C:

Entity deduplication uses an agreed business key and conflict-resolution rule across integrated sources; the current need is for loading facts that precede their dimension records, not merging duplicate customer updates from two systems.

D:

Joining data from separate systems requires a trustworthy key that identifies the same entity on each side; this can support preventing incorrect joins across customer feeds, but would not address the need for loading facts that precede their dimension records.

Question 164

An Airflow cleanup operation must run even if one or more upstream transforms failed. Which DAG feature should change?

  1. EventBridge Scheduler flexible time window
  2. Step Functions execution history
  3. Airflow task retry policy
  4. Airflow dependency trigger rule
  5. Glue conditional trigger

Explanation

Correct Answer

D:

Airflow trigger rules determine when a downstream task may run relative to upstream task states; in this scenario, set an appropriate downstream trigger rule rather than the default all-success dependency behavior.

Incorrect Answers

A:

Flexible time windows let a scheduled invocation occur within a configured interval instead of at an exact instant; the current need is for ensuring a cleanup task runs after upstream failures, not spreading noncritical batch launches to avoid thundering herds.

B:

Step Functions maintains execution event history for supported workflows and helps locate failed states; this can support tracing the precise failure transition in a workflow, but would not address the need for ensuring a cleanup task runs after upstream failures.

C:

Airflow tasks support retry counts and delays within a DAG's execution schedule; this helps with retrying a transiently failing DAG task, not ensuring a cleanup task runs after upstream failures.

E:

Glue conditional triggers can start downstream jobs when designated upstream jobs or crawlers reach configured states; it would target running a publish job only after upstream Glue success, while the question is about ensuring a cleanup task runs after upstream failures.

Question 165

An ingestion failure must generate a task that remains available even when ticket-processing workers are offline. What notification destination fits?

  1. SNS topic fan-out
  2. CloudWatch alarm action
  3. SQS durable queue
  4. Step Functions workflow status alert
  5. EventBridge event-to-target routing

Explanation

Correct Answer

C:

SQS retains messages for asynchronous processing by consumers that may be temporarily unavailable; in this scenario, queue a durable work item and process it with acknowledgments and retries rather than assuming a live subscriber.

Incorrect Answers

A:

SNS publishes notifications to multiple subscribed endpoints or queues using topic subscriptions; the current need is for buffering remediation work until a worker can run, not delivering one alert to several independent recipients.

B:

CloudWatch alarms evaluate metrics against thresholds and can invoke configured notification actions; this can support alerting after repeated error metric breaches, but would not address the need for buffering remediation work until a worker can run.

D:

An EventBridge rule can match Step Functions execution status changes for monitoring and notifications; although useful for notifying when a whole workflow fails rather than one retrying task, it leaves the need for buffering remediation work until a worker can run unmet.

E:

EventBridge rules route matching state-change events to designated consumers; it would target notifying only after a specific job state transition, while the question is about buffering remediation work until a worker can run.

Question 166

A continuous event feed has no known record count, but engineers want an unbiased sample held in limited memory. Which algorithm fits?

  1. Reservoir sampling
  2. Random sampling
  3. Stratified random sampling
  4. Systematic sampling caveat
  5. Spark key salting

Explanation

Correct Answer

A:

Reservoir sampling can maintain a bounded random sample from a stream of unknown total length; in this scenario, maintain a reservoir as events arrive, using a valid replacement probability that does not require buffering the entire stream.

Incorrect Answers

B:

Simple random sampling selects records with equal inclusion probability when an unbiased population estimate is needed; its benefit is building a general quality sample without subgroup constraints; that is different from sampling a large unknown-length event stream with fixed memory.

C:

Stratified sampling draws observations from defined subgroups to preserve rare but important categories; the current need is for sampling a large unknown-length event stream with fixed memory, not assessing a dataset with very uneven country distributions.

D:

Systematic sampling takes observations at regular intervals and may bias results when the stream is periodic; this can support avoiding periodic bias when checking machine readings, but would not address the need for sampling a large unknown-length event stream with fixed memory.

E:

Key salting distributes records for a heavily skewed join key across multiple synthetic partitions; this helps with reducing one-key hotspots in a distributed join, not sampling a large unknown-length event stream with fixed memory.

Question 167

Athena costs spike because queries for one day's data scan a year of objects. What dataset and query practice helps?

  1. Partition pruning
  2. EMR managed scaling
  3. Glue worker sizing and job profiling
  4. Column projection before scan

Explanation

Correct Answer

A:

Partition pruning excludes dataset partitions whose values cannot satisfy a query predicate; in this scenario, partition by commonly filtered date dimensions and include partition predicates that the engine can use to prune files.

Incorrect Answers

B:

EMR managed scaling can adjust cluster capacity within configured limits based on workload demand; it would target reducing idle cluster capacity between Spark stages, while the question is about avoiding reads of unrelated months in a data lake.

C:

Glue worker types and counts affect job parallelism, memory and billed compute consumption; its benefit is right-sizing an oversized Glue ETL run; that is different from avoiding reads of unrelated months in a data lake.

D:

Reading only required fields can reduce bytes processed by columnar storage and SQL queries; the current need is for avoiding reads of unrelated months in a data lake, not minimizing scanned data during analytic transformations.

Question 168

An existing EMR/Hive estate uses an established metastore that downstream Hadoop tools already reference. What component supplies its metadata?

  1. AWS Glue Data Catalog
  2. Catalog API lookup
  3. Apache Hive metastore
  4. Catalog permissions boundary

Explanation

Correct Answer

C:

Hive metastore maintains table and partition metadata for compatible Hadoop ecosystem tools; in this scenario, keep or migrate Hive metastore definitions through supported integrations; the metastore represents schemas rather than file contents.

Incorrect Answers

A:

Glue Data Catalog is a managed technical metadata repository for datasets and ETL integration; this can support centralizing technical table and partition definitions, but would not address the need for retaining a legacy Hive-compatible metadata system.

B:

A data-processing application can query catalog APIs to discover schemas and data locations at runtime; this helps with avoiding hard-coded column lists in a dynamic ETL client, not retaining a legacy Hive-compatible metadata system.

D:

Access to technical metadata and access to stored objects may be separately controlled by IAM and Lake Formation; it would target diagnosing why a visible table cannot be read, while the question is about retaining a legacy Hive-compatible metadata system.

Question 169

A batch job writes exactly three new day prefixes and wants deterministic catalog registration without scanning the bucket. Which SQL action fits?

  1. Athena partition projection
  2. Partition pruning predicate
  3. MSCK REPAIR TABLE
  4. ALTER TABLE ADD PARTITION
  5. Glue crawler partition update

Explanation

Correct Answer

D:

Adding a partition explicitly registers specified partition values and their locations in the catalog; in this scenario, add each partition with its location, avoiding a broad repair operation and validating that data is present.

Incorrect Answers

A:

Partition projection computes partition locations from table properties instead of storing every partition explicitly; it would target avoiding per-day catalog updates for predictable partitions, while the question is about registering a small known list of partitions precisely.

B:

Query filters on partition columns allow compatible engines to avoid scanning unrelated object prefixes; its benefit is reducing reads after partition registration; that is different from registering a small known list of partitions precisely.

C:

Athena's MSCK REPAIR TABLE can discover compatible Hive-style S3 partitions and add missing catalog entries; the current need is for registering a small known list of partitions precisely, not registering newly created Hive-style partition folders.

E:

A Glue crawler can discover supported new partitions and maintain catalog metadata during a run; this helps with keeping partition metadata synchronized after scheduled ingestion, not registering a small known list of partitions precisely.

Question 170

After metadata inspection, XML files need one parser and JSON files another. Which state expresses the conditional route?

  1. Step Functions Catch transition
  2. Lambda timeout setting
  3. Step Functions execution input/output paths
  4. Step Functions Choice state
  5. Step Functions Map state

Explanation

Correct Answer

D:

A Choice state routes execution based on a condition in the state input; in this scenario, evaluate the detected format and dispatch to the matching branch without creating an external polling controller.

Incorrect Answers

A:

Catch handlers move execution to a recovery state when a configured task error occurs; it would target sending failed processing to a quarantine path, while the question is about selecting a branch after checking data type.

B:

A Lambda timeout caps the maximum duration of a single function invocation; its benefit is preventing a stalled serverless task from running indefinitely; that is different from selecting a branch after checking data type.

C:

InputPath, ResultPath and OutputPath can shape data passed between workflow states; the current need is for selecting a branch after checking data type, not retaining only relevant fields between processing tasks.

E:

A Map state processes collection items using controlled parallel iterations in a workflow; this helps with applying the same transform to many independent input files, not selecting a branch after checking data type.

AWS Certified Data Engineer - Associate DEA-C01 Practice Tests