DEA-C01 — frequently asked questions

An S3 gateway endpoint is attached to route tables; the Glue job's subnet needs a route to the endpoint's S3 prefix list so packets can reach S3 without a NAT or internet gateway.

Glue serverless jobs do not use a security group to reach S3 through a gateway endpoint; gateway endpoints rely on route tables, so inbound security group rules do not apply.

S3 Select reduces data scanned during queries, but storing large volumes of historical audit data in S3 Standard incurs high monthly storage fees compared to Glacier.

No, Athena operates on data in S3 Standard or similar tiers. It cannot directly query objects in Glacier without first restoring them, which defeats the purpose of cost optimization.

Lambda needs custom code, IAM permissions, and VPC or Data API connectivity to reach Redshift, adding operational overhead even if invocation cost is low.

Yes, scheduled queries in query editor v2 run SQL against your Redshift cluster and can include CALL statements to invoke stored procedures.

The trust policy only lets the Lambda service assume the execution role to run code. EventBridge invokes Lambda through the function's resource-based policy, not that trust policy.

Option B covers both sides: the EventBridge rule needs invoke permissions, and the Lambda resource-based policy must allow the EventBridge principal. Missing either can produce AccessDeniedException.

The S3 Encryption Client encrypts data on the client side before upload, so only the SSE-KMS portion is server-side; the requirement demands two server-side encryption layers.

Yes. DSSE-KMS is applied by Amazon S3 at upload time, so the Lambda function can use a normal PutObject request and S3 transparently applies both server-side encryption layers.

Missing ACLs typically cause access denied errors, not silent reprocessing. Bookmark state persistence requires an explicit commit.

No. Concurrency controls parallel task execution but does not influence how bookmark checkpoints are saved or retrieved.

Step Functions requires rewriting workflows into JSON state machines, forcing code refactoring. MWAA supports existing Python/Bash DAGs directly.

No, MWAA is a managed service where AWS handles the infrastructure, allowing you to focus only on your workflow code.

Yes, Athena supports ACID transactions for Hive transactional tables stored in S3, ensuring data integrity.

While serverless, Athena is more cost-effective for ad-hoc S3 queries and offers native ACID support without needing a Redshift cluster context.

DMS instances are regional resources. For Redshift, the instance MUST be in the same region as the target cluster due to VPC and endpoint constraints.

No. While often placed near the source for performance, it must be in the same region as the target if the target is Amazon Redshift.

Firehose natively converts JSON to Parquet/ORC. For CSV, it typically requires transformation to JSON first, but exam logic prioritizes built-in features over Lambda for 'least effort'.

Lambda requires custom code development and maintenance. The question asks for the LEAST development effort, making built-in Firehose features preferable if applicable.

QuickSight is designed for BI and typically has refresh intervals ranging from minutes to seconds, whereas Grafana can query time-series databases like Timestream for sub-second real-time updates.

No, Kinesis Data Firehose does not support Amazon Timestream as a native destination. You must use a consumer application like Apache Flink or Kinesis Client Library to ingest and write to Timestream.

Activity Streams capture SQL operations for security auditing, not business metrics like network usage volumes.

No, polling every minute introduces significant delay compared to the near-instant processing of a Flink stream.

No. Visibility timeout only affects when a message becomes available after being received, not how long it stays in the queue before expiration.

DLQs do not prevent expiration, but they capture messages that fail processing repeatedly, preventing permanent deletion and allowing recovery.

DELETE only marks rows as deleted. The actual space is reclaimed when VACUUM runs, making DELETE slow for large-scale removal.

Generally no, TRUNCATE applies to base tables. This question likely assumes a context where the underlying table is truncated or refers to streaming ingestion specifics.

Logstash is open-source and typically requires managing EC2 instances or clusters to run, whereas Kinesis Data Firehose is a fully managed service that handles scaling and maintenance automatically.

You can use Lambda to process Kinesis streams, but integrating it directly with OpenSearch often requires custom code for batching and retry logic. Firehose provides built-in buffering, compression, and retry mechanisms, reducing overhead.

Yes, it refers to executing the CREATE IDENTITY_PROVIDER SQL command against the Redshift cluster, rather than just clicking buttons in the console without backend config.

While you must configure the IdP to trust Redshift first, the question asks what the data engineer should do to set up the mechanism in Redshift. The exam key prioritizes the Redshift-side registration step.

Git pull creates a merge commit, which can clutter the history with non-linear merge nodes. Rebasing moves your commits to the tip of master, keeping history linear.

Rebasing rewrites commit history. If others have cloned your feature branch, their local copies will diverge, requiring force pushes and causing collaboration issues.

CloudTrail is for security auditing and logging API calls, not for real-time operational workflows. It lacks the low-latency event routing needed for immediate load status updates.

No, the Redshift Data API natively integrates with Amazon EventBridge for monitoring and event notifications. It does not have a direct connector to SQS.

NAT Gateways allow outbound internet access for instances in private subnets (e.g., for software updates) without exposing them to inbound traffic. It does not violate the requirement of preventing external access to the data.

The scenario explicitly states the company stores data in S3. Option E says 'Do not upload the data to the S3 bucket,' which contradicts the business requirement described in the question.

DMS provides out-of-the-box Change Data Capture (CDC) for JDBC sources, requiring zero code for ingestion. Glue requires writing and managing Spark/Python ETL scripts.

Yes, QuickSight can connect directly to DynamoDB via Amazon Athena or its native connector, allowing for quick dashboard creation without intermediate storage.

PutRecords batches multiple records into a single API call, reducing network overhead and improving throughput optimization from each device.

No, Firehose does not support enhanced fan-out; it is designed for loading data to destinations like S3, whereas Data Streams supports real-time consumption with fan-out.

Yes. AWS documentation states Athena Federated Query can be used to build pipelines that extract data from multiple sources and store them in Amazon S3.

While Glue is a strong ETL tool, it requires more setup for diverse connectors and scripting compared to Athena's SQL-based federated queries, violating the 'least operational effort' constraint.

Scheduled crawlers run at fixed intervals, causing operational overhead and potential redundant executions. Event-driven crawlers run only when changes occur, aligning better with 'least operational overhead'.

Yes, AWS Glue Crawlers can be configured to trigger based on Amazon SQS messages, allowing them to process S3 events asynchronously without polling.

Lambda adds operational overhead by requiring separate function creation, IAM role management, and code maintenance. Glue Triggers are native and serverless.

Yes, AWS Glue supports connections to various data sources including MongoDB, allowing you to store connection details securely in Glue Connections.

The question states the S3 bucket policy is already updated to grant access. Option B suggests adding the bucket to the role's resources, which is redundant or refers to a different mechanism than the bucket policy already applied.

Yes. The IAM policy allows the role to request the action (e.g., kms:Decrypt), while the KMS key policy must authorize that specific principal to perform the action on the key.

Yes. Lambda calls start_query_execution and exits; the query runs asynchronously in Athena regardless of Lambda's timeout.

MWAA is more expensive and complex. For simple sequential querying, serverless Step Functions are more cost-effective.

Macie discovers and classifies sensitive data but does not have the functionality to automatically redact or remove that data from the S3 objects.

Object Lambda processes data on-demand with no infrastructure to manage, while DataBrew requires configuring jobs, scheduling, and managing output storage.

Yes, Amazon EMR Serverless allows you to run Spark, Hive, and other applications without managing clusters, reducing operational overhead.

AWS Glue is optimized for Spark and PySpark jobs and does not provide native runtime support for Apache Pig, Oozie, or HBase.

No. AWS Glue Data Quality is used for validating data quality and monitoring metrics, not for transforming or obfuscating data values.

Lambda requires custom code for detection and obfuscation logic, whereas Glue Studio offers pre-built, no-code transforms for PII handling, reducing development time.

Yes, AWS Glue Workflows support Amazon EMR applications as steps, allowing you to chain Glue jobs and EMR processing tasks together.

Step Functions require more configuration and coding effort. Glue Workflows are purpose-built for ETL and offer lower operational overhead for this specific stack.

S3 One Zone-IA stores data in a single Availability Zone, so it does not meet the question's explicit high-availability requirement even though it is cheaper than S3 Standard-IA.

The files are accessed only once or twice yearly and no retrieval-time requirement is stated, so Deep Archive's lower storage cost makes it more cost-effective than Flexible Retrieval.

Spectrum requires data in S3 and is best for ad-hoc queries on large datasets. Data Sharing is optimized for live, structured data exchange between clusters with lower overhead.

No, it does not duplicate the data. It provides a pointer to the producer's data, allowing the consumer to query it directly using its own compute resources.

While Firehose is managed, moving data to S3 first introduces latency and requires additional steps (COPY command) to make it available in Redshift, failing the 'near real-time' requirement.

Yes, via Streaming Ingestion. You create an External Schema for the Kinesis stream and then query it using a Materialized View with auto-refresh enabled.

DataSync is for transferring data between storage systems (on-prem to AWS), not for discovering or subscribing to external third-party data catalogs.

Data Exchange focuses on finding and using third-party data subscriptions, while DataSync focuses on automated data transfer tasks between storage endpoints.

Pandas requires managing the compute environment and script maintenance, increasing operational overhead compared to the managed Glue FindMatches transform.

FindMatches uses ML but is designed to be a managed transform within Glue, reducing the need for custom model building and maintenance compared to other options.

Option B only opens the DB port but doesn't ensure the Lambda function is in the same VPC/subnet to reach the private IP. Without C, the Lambda might still need public routing.

While distinct SGs are better for strict isolation, the exam prioritizes 'least operational overhead'. Sharing an SG with a self-referencing rule is the simplest way to meet the connectivity requirement here.

Provisioned Concurrency adds cost for idle capacity. For 'occasional' invocations, the cold start latency is acceptable, making standard Lambda (D) cheaper and less complex to manage.

No. It is a simple scheduled task. Managing ECS/EKS clusters or configuring Provisioned Concurrency involves significantly more ongoing operational maintenance.

Lambda is not cost-effective or scalable for processing tens of terabytes of JSON data due to execution time limits and high compute costs.

Formats like Apache Iceberg, Delta Lake, or Hudi provide ACID transactions, schema evolution, and efficient upserts on S3 data.

Parquet reduces data scanned during execution, but does not reduce the time Athena spends scanning the Glue catalog to find those files.

Use Partition Index for existing tables with manageable growth; use Partition Projection for new tables or massive datasets where catalog scaling is a concern.

Glue Crawlers are used to discover schema changes in data stores like S3 or dynamic databases. For a static SQL View with a known, unchanging schema, the Glue Job can define the schema directly in its script or JDBC connector, making the crawler redundant.

Yes, AWS Glue supports JDBC connections to various relational databases, including Amazon RDS for SQL Server and EC2-hosted SQL Server instances, allowing direct data extraction without intermediate file storage.

Yes, if the state machine explicitly interacts with S3 (e.g., passing data or triggering jobs that write to S3), the execution role needs S3 permissions.

SNS sends notifications but doesn't explain why the job failed. You need to identify the root cause (permission/network) first.

EBS is persistent, but the root volume has a default DeleteOnTermination=true. Attaching a separate EBS volume uses the default false, preserving the data.

No, Instance Store is ephemeral. Data is lost if the instance is stopped, hibernated, or terminated.

An Athena data source allows external Spark applications (e.g., on EMR) to read Athena tables. It does not enable the Spark engine inside Athena itself.

No. You must first create a workgroup specifically configured with the Spark engine before you can open notebooks or run Spark code in Athena.

MSCK REPAIR TABLE scans the entire S3 path to find new partitions, which takes time. It also requires a separate trigger, adding more latency than an immediate API call.

Yes, but crawlers are heavier than direct API calls. For 'least latency,' directly calling create_partition is faster than starting a full crawler job.

If the column storing the date is a DATE or TIMESTAMP type, comparing it directly to an integer (2023) causes a type mismatch or returns no rows. You must extract the year component first.

No, HAVING filters grouped results after aggregation. It cannot fix a WHERE clause that fails to retrieve rows due to incorrect date filtering logic.

Yes, S3 Select supports both CSV and Apache Parquet formats, allowing efficient column-level filtering.

No, it is not discontinued. While availability may vary by region or account type, it remains a valid feature for querying objects.

Native Auto-Refresh is generally preferred as it is event-driven and requires no ongoing management, but Query Editor v2 is also a valid low-effort option for scheduled refreshes.

While possible via API calls, it is not the least effort solution and introduces unnecessary complexity compared to native Redshift capabilities.

No, AWS Glue Workflows only support Glue jobs, crawlers, and triggers. They cannot invoke Lambda functions.

Airflow is more flexible but requires managing infrastructure (EC2/EKS). Step Functions is fully managed and offers less overhead for simple AWS-native workflows.

Aurora and DynamoDB are database services, not metadata catalogs. Using them would require building custom applications to manage metadata, increasing operational overhead significantly.

Yes, AWS Glue Crawlers can infer schemas from semi-structured data formats such as JSON, XML, and Parquet stored in Amazon S3 and update the Data Catalog automatically.

On-Demand is more expensive for predictable workloads. You pay a premium for flexibility that you don't need when traffic follows a known schedule.

No. Once configured, AWS Application Auto Scaling automatically adjusts the provisioned capacity up and down according to the defined schedule.

DMS migrates structured data tables, not the complex schema and metadata definitions stored in a Hive metastore database.

While Aurora Serverless exists, AWS Glue is the dedicated, fully managed serverless metadata catalog service, making it more appropriate and cost-effective for this specific use case.

Sort keys optimize data retrieval speed for filtered queries but do not determine which compute node stores the data rows.

It refers to the column with the highest cardinality (most unique/distinct values), which minimizes data skew when hashed.

Athena is a query engine, not a metadata catalog service. It uses tables defined in its own registry or external catalogs, but it doesn't actively crawl and manage schema evolution for ingestion pipelines like Glue does.

Yes, for variable workloads. Redshift Serverless scales compute automatically and you only pay for what you use, avoiding the fixed hourly costs of maintaining a provisioned cluster that might be underutilized.

Caching stores frequently accessed data to reduce database reads. Since write-heavy workloads generate most traffic through insert/update operations, caching cannot significantly offload the CPU burden from these transactional processes.

No. You should always diagnose first using Performance Insights (Option A) to ensure the issue isn't just inefficient queries. Upgrading (Option D) is the corrective action if the workload legitimately exceeds the current instance's capacity after optimization.

VACUUM FULL reclaims space but does not reindex interleaved sort keys. You must use VACUUM REINDEX to analyze the key distribution as well.

DELETE ONLY removes marked rows but doesn't sort or reindex. REINDEX analyzes interleaved keys and then performs a full vacuum to reclaim space.

A standalone Glue Job uses the current Data Catalog definition. If the source schema changes, the job may fail or produce incorrect results because it doesn't auto-update the catalog.

Option B is time-based (every 15 minutes), while Option D is event-based (triggered when files land in S3). Both meet the requirements, but D is more responsive to file arrival.

Parquet reduces scan costs but requires modifying the Glue job, increasing operational overhead. Query reuse has zero extra overhead.

No, it only works for identical queries (same text) executed within 24 hours. It does not help with varied BI dashboards.

NEST_TO_ARRAY creates an ordered list of key-value objects, while NEST_TO_MAP creates a single JSON object (map) where columns become keys and values.

PIVOT is used to rotate column headers into rows or vice versa, not to combine multiple columns into a single nested JSON structure.

Deep Archive requires 12+ hours for retrieval, violating the millisecond requirement. Only the default tier's Archive Instant Access tier supports instant retrieval.

No, it is fully automated. It monitors access patterns and moves objects between tiers without any manual intervention or lifecycle policy management.

A missing QuickSight service role causes an authentication or authorization failure before Athena queries run, not the specific Athena insufficient-permissions error; QuickSight normally auto-creates aws-quicksight-service-role-v0.

If the S3 objects or Athena query results are encrypted with SSE-KMS, QuickSight's role must have kms:Decrypt and the key policy must allow it, otherwise queries fail with insufficient permissions.

Redshift Spectrum only queries external tables in Amazon S3 through the Glue Data Catalog; it cannot directly query DynamoDB tables or RDS SQL Server. You would need extra federation or export steps, so option B adds overhead.

Option C adds Glue jobs to convert JSON into Parquet or CSV, which is unnecessary ETL, and it still only queries S3. It does not provide a unified query path to RDS, DynamoDB, or Redshift.

It grants broad SageMaker permissions but not the sts:AssumeRole call a Studio user needs before AWS Glue interactive sessions can run, so AccessDenied can persist.

No. AddAssociation is an Amazon SageMaker API operation, not an STS action; cross-service access is granted with sts:AssumeRole, which option B specifies.

The shell job can run on 1/16 DPU and pandas handles sub-100 MB CSVs in memory, while a PySpark job bills full DPUs plus Spark startup overhead the small files never need.

When files or volumes are large enough that data cannot fit in a single node's memory or the transform must be distributed across a Spark cluster; that scalability is wasted on under-100 MB daily CSVs.

Glue Workflows only coordinates AWS Glue jobs and crawlers, so it cannot directly manage EMR cluster steps or non-Glue AWS services. The question specifically requires orchestrating an existing EMR ETL pipeline.

MWAA runs an always-on managed Airflow environment with worker and scheduler charges, while Step Functions charges per state transition and scales to zero when idle.

EventBridge can target an AWS Glue workflow directly, so a Lambda function adds code, permissions, and monitoring overhead without improving the event-driven trigger.

Yes, S3 File Gateway publishes successful upload events to EventBridge, allowing rules to react when a file transfer finishes.

Spectrum only reads S3 Standard, S3 Intelligent-Tiering, and Glacier Instant Retrieval, so Glacier Flexible Retrieval objects must be restored and copied to a supported class before Spectrum can query them.

No. Federated Query adds direct read access to live Aurora PostgreSQL for analytics, but the current Redshift data and the S3 history still come from the existing ETL run and the monthly UNLOAD job.

Parquet is columnar and compressed, so Athena reads fewer bytes per query and S3 stores less data. Because Athena charges by data scanned, this lowers analytics cost.

Yes. When you create a flow log with an S3 destination, you can choose Parquet as the output format, so no separate conversion service is required.

Kinesis Data Streams stores records for consumers but has no native S3 sink. You need a KCL application or Firehose to read records and write them to S3.

Firehose historically enforced a 60-second minimum, and AWS later added zero buffering; either way, adding Managed Flink before Firehose introduces more processing latency than a direct KCL write.

S3 event notifications can invoke a Lambda function directly by using the function ARN as the destination. Adding SNS only introduces an extra topic, subscription and permissions to manage for no functional gain.

No. S3 supports specific event families such as s3:ObjectCreated: and s3:ObjectRemoved:; s3:* cannot be configured, which is why option C would never trigger the Lambda conversion.

Direct SQL makes each dashboard interaction query Athena, scanning S3 data repeatedly and increasing cost and latency. SPICE caches the dataset once and serves users from memory with a daily refresh.

Redshift requires a provisioned cluster and ongoing management, which is not the most cost-effective option when the data already sits in S3. Athena queries S3 serverlessly and scales with daily updates.

AWS Glue is a serverless AWS-native ETL and catalog service; it is not an open-source workflow engine that can run on-premises with the same definitions.

Yes, MWAA is managed Apache Airflow, so the same DAG definitions and plugins can generally run on-premises and in AWS, preserving orchestration portability.

Keyspaces targets Cassandra-compatible workloads and can require more capacity and migration design; the question asks for the least-overhead global OLTP solution, which DynamoDB addresses natively.

DocumentDB can deliver low-latency document reads, but it is optimized for MongoDB compatibility rather than the global, serverless OLTP profile described in the question.

The result limit only caps output size or bytes scanned; queueing is driven by whether the workgroup has available query-processing capacity, which option A does not change.

No. Adding users changes who can submit queries, not how much capacity the workgroup has; it can increase contention and make queueing worse.

Compression only shrinks bytes on disk; CSV is still row-based, so Athena reads every column for a single-column filter. Parquet adds column pruning plus predicate pushdown, cutting scanned data far more.

JSON is row-oriented text and usually larger than CSV, so it keeps the same full-row scan pattern. Only the columnar layout of Parquet lets Athena read just the selected column.

A scheduled Glue job runs in batches, so it cannot continuously capture PLM transaction changes in near real time, and it requires writing and maintaining ETL logic, which is more development effort than an AWS DMS CDC task.

Yes. AWS DMS supports MySQL as a source and Amazon Redshift as a target, and a full load plus CDC task can apply ongoing changes to Redshift continuously.

Redshift can parallelize one COPY across all slices and read every manifest-listed S3 object in a single operation, avoiding repeated command startup and commit overhead.

A Glue job adds cost and an intermediate copy step, while the manifest lets Redshift load the existing S3 files directly without extra services.

A certificate supplies the identity and key material for the endpoint, but it does not restrict which protocol versions the server accepts. Only the Transfer Family security policy sets the minimum TLS version.

No. Security groups filter traffic at Layers 3 and 4 using IPs, ports, and protocols such as TCP, so they cannot inspect or limit the TLS version negotiated inside a connection.

Triggers only start a crawler or job on a schedule or event; they do not remember which S3 objects were already processed, so each run would reprocess the entire dataset without job bookmarks.

Yes. Bookmark state is tracked per transformation regardless of file compression, so gzip or bzip2 objects already read are skipped and only new or changed files are ingested.

A timed-out PutRecord may have succeeded, so the application must retry and Kinesis stores both copies. Duplicate ingestion is inherent to at-least-once delivery and cannot be prevented at the source.

Checkpoints guarantee exactly-once state consistency inside the Flink job, but they cannot remove duplicates that PutRecord retries already wrote into the Kinesis stream before the job read them.

Replication copies data but doesn't inherently block deletes unless specifically configured for delete markers, adding complexity and cost compared to simple versioning.

Yes, storage costs increase as all versions are stored, but it is the most efficient operational method for preventing data loss from accidental deletions.

AWS DMS is designed for migrating data between databases. While it has some S3 capabilities, Glue is the native, serverless choice for general ETL from S3 to Redshift.

No, Lambda has strict time and memory limits (max 15 mins, 10GB RAM). It is unsuitable for processing terabytes of raw data.

Gateway Endpoints are free to create and manage, whereas Interface Endpoints incur hourly charges and per-GB data processing fees.

No, Gateway Endpoints are currently only available for Amazon S3 and DynamoDB. Other services require Interface Endpoints.

While Glue often handles common compressions, strict consistency in A ensures no ambiguity, especially for custom or less common codecs.

Yes, inconsistent prefixes can lead Glue to treat objects as separate datasets or partitions, potentially resulting in multiple inferred tables if schema differs across paths.

Athena queries S3 objects, which involves scanning and parsing files. Even with partitioning, it cannot match the sub-second response time of an indexed search engine like OpenSearch for frequent, small queries.

Yes, Amazon Managed Grafana integrates seamlessly with OpenSearch Service. However, the key factor for latency is the data store (OpenSearch), not the visualization tool itself.

No. Glue Crawlers are serverless and scale automatically. DPUs are only allocated for provisioned Glue ETL jobs.

It violates least-privilege security. The AWSGlueServiceRole provides the specific permissions needed for S3 access and Data Catalog updates.

Spark is memory-intensive, but only if memory usage is high. Here, memory is under 30%, so adding more memory is wasteful and costly.

It might help performance, but it increases total cost. The question asks for the MOST cost-effective solution, which involves right-sizing the instance type first.

A single COPY command is optimized by Redshift to distribute work across all nodes internally. Multiple commands can cause lock contention and management overhead.

No. INSERT is a row-level operation and is significantly slower than the bulk-load capability of the COPY command, which is designed for high-throughput ingestion.

No. Macie is for security and PII discovery, not for business logic record linkage or deduplication.

It is integrated with Glue ETL jobs but is a specific capability provided by AWS Lake Formation's ML features.

No. Athena scans all files in the table location regardless of Glue crawler exclusion patterns.

You can filter rows via SQL, but Athena still reads the files, causing higher latency than relocating them.

No, the S3 bucket policy only needs to allow s3:GetObject. The KMS policy must allow the Lambda role to perform kms:Decrypt.

The request will fail at the S3 layer with AccessDenied before it even attempts to contact KMS for decryption.

Redshift COPY is optimized for large files. Millions of 1 KB files cause high overhead and poor performance during parsing and loading.

No, file grouping is a logical operation during ingestion. It does not create additional physical files in S3, so storage costs remain unchanged.

Scheduler logs only show when and if DAGs were scheduled. They do not contain the runtime error messages or stack traces generated by the tasks themselves during execution.

DAGProcessing logs reveal issues with parsing DAG files, such as syntax errors or missing imports, occurring before the workflow actually starts running.

DMS can replicate to S3, but managing incremental loads usually requires extra Lambda functions or Kinesis streams. Glue Job Bookmarks handle this natively.

With tables under 50 GB, full loads are possible, but 'incremental' requirements and 'operational efficiency' favor Glue's bookmarked incremental approach.

Bookmarks track processed files to avoid duplicates on re-run, they do not detect changes in the source database like primary key updates.

Yes, DMS supports S3 as a target and can serialize data into Parquet format automatically.

Direct Connect is a networking service that provides dedicated connectivity. It does not include data synchronization, scheduling, or transfer optimization features required by the question.

No, AWS Glue is an ETL service focused on data integration and transformation for analytics. It is not designed for routine, scheduled file synchronization between storage systems.

Parquet is a columnar format that allows Spectrum to skip reading irrelevant columns, drastically reducing I/O and improving query speed compared to row-based CSV.

Yes, partitioning allows Spectrum to prune entire directories. If you query a specific date range, it only scans those partitions, avoiding a full table scan.

Athena does not support the LIKE clause syntax for copying table schemas directly within the CREATE TABLE statement like some other RDBMS engines do.

No, INSERT INTO requires the target table to already exist. You must use CREATE TABLE first.

The question asks to create a table based on a subset, but all options use INSERT. This implies the table structure is already defined, and we are populating it.

Option C is valid SQL but less safe. Explicitly listing columns (Option A) ensures data goes to the right place even if the target table has extra columns.

Views don't automatically handle cross-account sharing or central governance via Lake Formation without additional steps like Data Shares.

Workgroups manage query execution resources, not data access permissions or catalog registration.

DataSync moves files and objects, not database transactions. It cannot maintain referential integrity or provide the continuous replication needed for minimal downtime.

No, DMS is pay-as-you-go. You only pay for the replication instance duration, making it cost-effective for infrequent or scheduled migrations compared to building custom ETL pipelines.

EVEN distributes rows evenly but doesn't adapt to join patterns or growth dynamically like AUTO. AUTO minimizes maintenance by handling style changes automatically.

Yes, AUTO can start with ALL for small tables and switch to KEY or EVEN as data grows, optimizing performance without manual intervention.

Public subnets imply connectivity via an Internet Gateway, which routes traffic through the public internet. The question explicitly forbids using the public internet.

While not strictly the only way, it is the best practice for managing dynamic credentials. Hardcoded keys are insecure, and Secrets Manager provides encryption and rotation capabilities essential for production pipelines.

S3 Inventory tracks object versions and sizes, but it does not natively report on incomplete multipart uploads. You would need additional processing logic to infer this state.

S3 Storage Lens has a free tier for basic metrics. Advanced metrics may incur costs, but it remains significantly cheaper than building custom monitoring infrastructure.

EMR requires provisioning a cluster, which incurs high startup costs and latency. For 5 KB files, the overhead outweighs the benefits, making Glue + Lambda far more cost-effective.

Yes, for simple event-driven triggers. While Step Functions handles complex state machines, Lambda is sufficient here to trigger Glue jobs upon S3 upload, keeping the architecture lightweight.

DataSync requires configuring tasks, schedules, and monitoring for large data transfers. It adds latency and cost compared to running lightweight Lambda functions locally.

Yes, but it requires complex VPC peering setup, security group rules, and DNS configuration across regions, increasing operational overhead significantly.

Concurrency scaling is controlled per WLM queue to allow selective routing of queries based on priority and type, optimizing cost and performance.

No, Redshift Serverless automatically scales compute resources based on demand and does not support the traditional WLM-based concurrency scaling feature.

Macie discovers and classifies PII but does not have a native feature to mask or redact the data in place. DataBrew is designed for the transformation/masking step.

Yes, but Lambda requires custom coding and maintenance. The question specifies a 'low-maintenance' solution, making the managed visual tool DataBrew the better fit.

Lambda requires writing custom code to define PII patterns and maintain the logic, which violates the 'least manual intervention' constraint compared to Macie's automated ML approach.

No, CloudTrail logs API activity (who did what), not the actual data payload inside S3 objects. It cannot inspect file contents for PII.

In Athena, you typically need to define the schema explicitly if not using CTAS. Using CTAS with a false condition is a concise way to replicate the exact schema of an existing table instantly.

Yes, both evaluate to false for every row, resulting in zero rows being selected. Both are valid methods to create an empty table copy via CTAS.

Recreating loses configurations and doesn't fix the root cause if the path is wrong or partitions are desynced.

It primarily works for Hive-style partitioned tables stored in S3. It may not work for external tables with complex formats like Parquet without proper SerDe settings.

B omits errorcode IS NOT NULL, so successful CloudTrail events can be counted alongside errors. Option A keeps the error-only filter while retaining the descending LIMIT 10.

Not strictly: A orders by TotalEvents, so it returns the 10 highest-count error groups. No listed option orders by eventtime DESC, but A is the only one with both required filters and a descending limit.

Athena queries data in S3 but does not load it into a central data warehouse; performance also depends on Parquet layout, partitioning, and ongoing tuning, which adds operational work.

Yes. QuickSight can use direct query against Redshift Serverless, and the serverless warehouse automatically scales capacity for intermittent query spikes without manual provisioning.

Increasing shards adds capacity but doesn't fix the underlying skew. If the partition key is still deterministic, new shards will also suffer from uneven loads.

A hot shard is one that receives significantly more data than others, hitting its throughput limit and causing throttling while other shards remain underutilized.

Copying adds storage, data transfer, and ETL time before the one-time query, while Athena Federated Query can read DynamoDB, RDS, Redshift, and S3 in place.

No, Redshift Spectrum is primarily for querying S3 external tables from Redshift; it does not natively join DynamoDB and RDS without additional loading or connectors.

HDFS on EMR is ephemeral: data is lost when the cluster terminates, and long-running jobs can fill local disk. Amazon S3 decouples storage from compute and provides durable, scalable persistence.

Graviton instances are ARM-based and AWS positions them as lower-cost alternatives to comparable x86 instances while maintaining performance for many Spark workloads.

Parallel state runs a fixed set of distinct branches you define once. It does not iterate over an array of files, so it cannot apply one transformation across an unknown number of data files.

Distributed Map is a mode of the Map state for very large collections or long-running child workflows, running iterations across multiple child executions. It is still the Map state, so the answer stays C.

Gzip is not splittable for Spectrum workers, and 1–5 GB single objects limit scan parallelism; AWS favors splittable formats in roughly 100 MB–1 GB files.

Partitioning on common query predicates lets Spectrum prune entire prefixes during planning, so it never lists or reads partitions the query does not need.

The destination stream must live where the logs are stored and analyzed—the security account—while production only sends records through its subscription filter and assumed role.

CloudWatch Logs subscription filters must be created in the account that owns the log group—production—because that is where the logs are selected and sent to the cross-account Kinesis destination.

Lambda has a 15-minute maximum invocation timeout and does not maintain durable state across invocations, so a 30-minute stateful aggregation is unreliable.

Option B emphasizes duplicate handling and multiple aggregation types but does not explicitly deliver the required time-based 30-minute window; option D matches that requirement directly.

That works but adds snapshot, restore, attach, and validation steps. Elastic Volumes changes the existing volume type in place, which is the least-overhead option.

No. Amazon EBS Elastic Volumes supports modifying the volume type while it remains attached, so you do not need to detach the volume or restart the instance.

DataBrew is a visual data-preparation service for cleaning and normalizing data; it does not provide the centralized access-control and governance layer that Lake Formation does.

They are relational databases suited to transactional workloads, while a data mesh data lake needs scalable object storage such as Amazon S3 for diverse domain datasets.

STL_QUERY_METRICS returns per-query metric values such as rows and bytes, but it does not record optimizer-generated alert events; those are in STL_ALERT_EVENT_LOG.

No, STL_PLAN_INFO provides execution-plan details, not the alert/event records that document optimizer-detected performance conditions.

JSON is row-oriented, so Athena can still scan more data for one- or two-column queries; Parquet's columnar layout reduces bytes scanned and query cost.

Yes. CSV is row-based, so Athena reads whole rows even when analysts query only one or two columns, increasing data scanned and cost.

Separate buckets multiply administration and still require per-Region IAM policies; Lake Formation data filters centralize Region scoping over one S3 data lake.

No, data filters are Lake Formation grant objects; grant each filter to the HR department principal/role rather than embedding it in the IAM role policy.

Kinesis is a streaming platform: you must build and operate producers and streams yourself, and it offers no ready-made connectors for third-party SaaS applications, so it adds overhead.

No. Glue Data Catalog only stores table and schema metadata for analytics engines; it does not ingest or transfer data, so it cannot satisfy this requirement.

Every function would still need its own environment variable and download logic, adding cold-start latency and S3 permissions without any central, versioned code artifact.

No. Layer versions are immutable, so you publish a new version and repoint the functions (or reference $LATEST), which can be scripted via the Lambda API or IaC.

It requires custom Lambda code, IAM permissions, triggers, and ongoing maintenance, while CloudTrail data events provide managed delivery to the logs S3 bucket.

No. S3 object-level actions such as PutObject are data events, so a trail must be configured for data events on the transactions bucket.

A Hive metastore on EMR is tied to cluster infrastructure and still needs migration and ongoing management, while EMR and Athena can both use the managed Glue Data Catalog.

No. Ranger is for authorization, auditing, and policies in Hadoop environments; it does not provide Athena's central Hive-compatible table catalog.

S3 bucket and IAM policies only grant or deny access to buckets, prefixes, and objects. They have no notion of columns or row filters, so they cannot satisfy a per-team row/column requirement.

Ranger requires you to run and maintain the Ranger admin server and EMR plugins, and it does not govern Athena or Redshift Spectrum queries. Lake Formation centralizes the same row/column policies across all three engines with no servers to manage.

Glue Studio is primarily a visual authoring environment for Glue jobs; orchestration of crawlers, triggers, and job dependencies is handled by Glue workflows.

Step Functions suits cross-service workflows with custom states, and MWAA suits complex Airflow DAGs; for a Glue crawler-to-job pipeline both add cost and effort.

It targets S3 request-rate throughput, not Athena scan efficiency; it can also break the date-based partition path Athena relies on for pruning.

Parquet is columnar, so Athena reads only needed columns and predicate blocks; JSON still scans more data and lacks the same block-level pruning benefits.

S3 is an object store optimized for large-scale data transfer, not low-latency queries. It does not provide single-digit millisecond response times for individual records.

Yes, DynamoDB is a NoSQL database that natively supports document-like structures, including nested JSON attributes, making it ideal for complex sensor data.

Standard IAM policies control access to S3 buckets and keys but cannot parse or filter data inside those files. Lake Formation adds the capability to enforce row and column-level restrictions.

No. QuickSight is for analytics and visualization. It does not govern access to the underlying raw data in S3 for general querying purposes like Athena does.

Amazon Redshift does not support partitioning like other databases; it uses distribution keys for data placement and sort keys for I/O optimization.

Use KEY distribution when joining large tables on common columns to keep related data together on the same node, reducing shuffle overhead.

SSE-S3 uses Amazon S3 managed keys that AWS owns and rotates; they are never exposed, so there is no key resource an IAM policy could reference.

With SSE-C the company must supply the encryption key on every request and securely store, rotate, and deliver it, whereas SSE-KMS keeps the key in KMS and encrypts transparently.

JDBC requires managing connection pools, driver updates, and network configuration, which increases operational overhead compared to the serverless Data API.

No, Amazon Redshift does not support native WebSocket connections for SQL queries; it relies on JDBC/ODBC or the Data API.

Job parameters are stored in plaintext in the job definition unless encrypted, and they lack the audit trails and rotation capabilities of Secrets Manager.

No. While possible, it requires manual encryption key management and complex bucket policies. Secrets Manager is designed specifically for this use case.

CloudFormation automates deployment but you still manage the stack, errors, and updates. Redshift Serverless removes the compute management entirely.

Yes, it uses a pay-per-use model for DPUs, so you only pay when the job runs, avoiding costs for idle provisioned clusters.

Athena does not natively read .xls; you must convert the file to a supported format and catalog it, adding operational steps compared with DataBrew.

Yes. DataBrew supports Excel files stored in Amazon S3 and provides aggregate recipe functions such as COUNT_DISTINCT for distinct customer counts.

Firehose delivers to Redshift only through S3 and COPY, adding buffering, extra storage, and pipeline management. Redshift streaming ingestion removes those intermediate steps.

Yes. Streaming ingestion populates materialized views for near real-time data, while the same Redshift Serverless warehouse stores and queries historical tables for yesterday's data.

Glue is serverless, so it infers changing schemas with crawlers and runs Apache Spark ETL without clusters to provision or tune, which directly meets the least-operational-overhead requirement.

No. Lambda has a 15-minute maximum runtime and limited packaging, making it impractical for a 1 TB daily pipeline with schema detection across SAP HANA, Kafka, and DynamoDB.

Bucket policies control access but do not redact PII inside objects, and multiple copies add storage, sync, and governance overhead that the least-overhead requirement rules out.

Yes. It runs Lambda code on S3 GET requests, so you can identify the caller and return redacted data dynamically without modifying the stored dataset.

IAM roles control which AWS API actions a principal can call, but they do not partition Athena's workgroup-level query history or enforce per-workgroup query limits.

Tags let an IAM policy scope Allow or Deny statements to specific workgroups, so users in one account can only run queries and view history for their own use case.

MSCK REPAIR TABLE scans the entire S3 prefix to find missing partitions, which violates the requirement to avoid scanning all folders.

ADD creates a new partition entry in the catalog, while MODIFY updates properties of an existing partition. You cannot modify a non-existent partition.

While EC2 allows you to run Airflow, it is not a managed orchestration service. Managing the Airflow backend (metadata DB, scheduler) yourself increases operational burden and doesn't align with the 'managed services' preference for least refactoring.

Yes. Amazon MWAA supports all standard Airflow operators and hooks, including SQL-based tasks. You can reuse your existing SQL queries within the DAGs without rewriting them into Python or other languages.

While Parquet improves speed, creating a Lambda function for transformation adds significant operational overhead. The question prioritizes 'least effort' over maximum potential performance gains.

A crawler updates the Data Catalog with metadata. To truly benefit from partition pruning, you should configure the crawler to infer partitions based on S3 path patterns or use partition projection features.

Facilities with high IoT volume hash to a limited set of shards, while low-volume facilities leave others idle, triggering WriteThroughputExceeded on the busy shards.

Adding shards can mask capacity problems, but a skewed facility ID key still concentrates a busy facility's records and raises cost; a random key distributes load.

EXPLAIN only generates the execution plan without running the query, so it reports no per-operator computational cost or runtime statistics.

Athena requires EXPLAIN ANALYZE to prefix a full query, so the SELECT keyword is mandatory; without it the statement is a syntax error.

STANDARD uses dedicated Glue capacity at standard pricing. Because the jobs need not start or finish at a specific time, FLEX's spare-capacity discount is cheaper.

No. Glue job properties expose worker types and execution classes such as STANDARD and FLEX; FLEX provides spare-capacity savings without a Spot Instance setting.

IAM policies control which Glue API actions and resources a role can use; they do not express per-column grants inside a table, which Lake Formation column-level permissions provide.

No. AWS RAM shares resources such as Glue Data Catalog databases or tables, but it does not itself define fine-grained column-level permissions for Athena queries.

Glue job metrics publish run-level CloudWatch metrics; they do not persist which S3 objects or partitions were already processed, so the next run still scans all data.

Yes. Job bookmarks are Glue's native state-tracking mechanism for S3-backed sources, so a DynamicFrame read can skip previously processed files without a custom DynamoDB table.

KEY distribution only helps when the chosen column is the frequent join key; the option selects the highest-cardinality column, and a small dimension can still need redistribution.

Yes, it stores a full copy on every node, but for a small table updated once or twice every few years the write and storage overhead is negligible compared with repeated broadcasts.

In A, $ is an end anchor and is placed before the group, so it does not assert that city_name starts with San or El. Redshift needs ^ at the beginning to enforce a prefix match.

The exam treats the pattern as an anchored alternation for a San or El prefix; the trailing * is redundant for that intent, while ^ and | are what distinguish option B from A, C, and D.

CDCIncomingChanges reflects change events waiting to be applied, so it can rise from Redshift target bottlenecks; it does not measure time spent reading PostgreSQL transaction logs.

It means changes are waiting between the PostgreSQL source and the DMS task, indicating source-side capture delay rather than Redshift apply latency.

Athena queries data per TB scanned and does not maintain a Data Catalog; AWS Glue crawlers update schemas in the catalog for DPU-hour pricing, which is cheaper for daily varied-format arrivals.

Transient EMR clusters run Spark or Hive steps only for the job's duration and read directly from S3, so both workloads avoid a continuously running Redshift cluster or SageMaker training infrastructure.

Built-in transforms provide standard checks, but the scenario asks for business-specific rules; custom rulesets express those exact conditions with DQDL.

No. Job bookmarks only track which data has already been processed during incremental runs; they do not evaluate row-level quality.

Default Lambda runs outside the VPC, so it has no private IP or security group for RDS to reference. Option B also relies on an invalid Lambda-invocation rule rather than a source security group.

Option C is sufficient here, but any private subnet in the same VPC with routing to RDS also works. The essential step is attaching Lambda to a VPC subnet that can reach the private RDS endpoint.

DataSync can copy files into the team's Region, but it requires per-source transfer tasks and destination EFS management. EFS replication is the native managed feature for this pattern.

No. EFS mount targets are Regional, so cross-Region mounts are not supported; this is why option A's VPC peering approach fails.