Practice Test #2

A database, also called a schema in many contexts, is a logical container used to organize tables, views, and other objects. It does not itself represent a derived dataset created from combining source tables. While a database can contain the final object, it does not materialize query results to storage. Therefore it does not satisfy the requirement to create a reusable physical data entity.

A function encapsulates reusable logic, such as a scalar or table-valued computation, but it does not create and persist a dataset. Functions are invoked at query time and return computed results rather than storing data files in a physical location. Even if shared across sessions, they are not the right abstraction for a saved data entity built from tables. This fails the physical persistence requirement in the question.

A standard view is a persistent metadata object that stores only the SQL definition of a query, not the query results themselves. Other engineers can use it across sessions, but each query against the view reads from the underlying tables and recomputes the result. Because the data is not materialized to a physical storage location, a view does not meet the stated requirement. Candidates sometimes confuse persistent metadata with persisted data, which is the key distinction here.

A temporary view is limited to the current Spark session or notebook context and disappears when that session ends. It is useful for intermediate transformations during development, but it is not available to other engineers in other sessions. It also does not write the resulting dataset to a physical storage location. For both scope and persistence reasons, it is not correct here.

EMR + Spark can read from DynamoDB, RDS (JDBC), Redshift, and S3 and perform joins, but it requires provisioning and managing a cluster (even if transient). For a one-time analysis, the cluster spin-up time, operational overhead, and compute cost are usually higher than a serverless query approach. This is better when you need complex transformations at scale or repeated batch processing.

Copying DynamoDB/RDS/Redshift data into S3 and then querying with Athena can work, but it introduces extra steps: extraction jobs, temporary storage, and potential data format conversions. For a one-time analysis, this is typically less cost-effective and slower to deliver than querying in place. This approach makes more sense if you plan to reuse the S3 dataset repeatedly or build a longer-term data lake.

Redshift Spectrum enables Redshift to query data in S3 via external tables, but it does not natively query DynamoDB and RDS directly in the same way. While Redshift can integrate with other sources through additional patterns (ETL, data sharing, or federated query features depending on setup), Spectrum itself is not the right tool for directly joining DynamoDB/RDS/S3 data. It also implies maintaining a Redshift environment, which may not be cost-optimal for one-off work.

Apache Airflow can refresh Redshift materialized views by scheduling a DAG that runs REFRESH MATERIALIZED VIEW via a Redshift/Postgres operator. This works well for complex, multi-step pipelines and cross-system dependencies. However, it requires deploying and operating Airflow (or MWAA), configuring connections/secrets, and maintaining DAG code—more effort than using Redshift’s native scheduled query capability.

A Lambda user-defined function (UDF) in Redshift is not an appropriate mechanism to schedule or orchestrate refreshes. UDFs are invoked within SQL execution to extend computation, not to run administrative commands on a timer. You would still need an external scheduler to call something, and using a UDF for refresh is an awkward fit and higher effort/risk than native scheduling.

AWS Glue workflows can orchestrate jobs and triggers and could run a JDBC step or a Glue job that issues REFRESH MATERIALIZED VIEW. This is useful when refresh is part of a broader ETL workflow. But it adds Glue components (jobs, connections, IAM, triggers) and is more setup/maintenance than a simple scheduled query in Redshift.

Storage Lens plus custom lifecycle policies requires ongoing analysis and continuous refinement. With unpredictable access patterns, age-based or manually tuned transitions can easily move data that later becomes “hot,” increasing cost (retrieval/transition) or harming performance if moved to archival classes. Operational overhead is high across thousands of buckets compared to enabling Intelligent-Tiering.

This option explicitly includes moving data to S3 Glacier, which does not meet the millisecond retrieval requirement (Glacier retrieval is minutes to hours and often requires restore operations). Even the Standard-IA portion is fine for latency, but the Glacier transition breaks the core constraint. It also adds lifecycle management overhead.

Intelligent-Tiering with Deep Archive Access tier can transition objects into an archival tier with long retrieval times (hours) and restore requirements, which violates the requirement to retrieve all data within milliseconds. Archive tiers are only appropriate when delayed retrieval is acceptable. Therefore, enabling Deep Archive is not compatible here.

Using a scheduled EventBridge rule based on historical completion times does not actually detect when a transfer has finished successfully. File transfer durations can vary because of file size, network conditions, retries, or operational delays, so a schedule can trigger too early or too late. This creates brittle orchestration and may cause Glue jobs to run before data is fully available. It also adds ongoing maintenance because the schedule may need to be adjusted over time.

An on-demand AWS Glue workflow requires a human to monitor transfer completion and manually start the workflow. That directly violates the requirement for the process to launch automatically after each successful transfer. It also introduces unnecessary operational burden, risk of missed runs, and inconsistent execution timing. Manual triggering is the opposite of the low-overhead event-driven automation the question is asking for.

Although using S3 object creation to invoke Lambda and then start the Glue workflow would work, it introduces an extra service and custom code that must be deployed, secured, monitored, and maintained. The question specifically asks for the least operational overhead, so adding Lambda is less optimal than using a native EventBridge-driven workflow trigger. Lambda also adds concerns such as retries, logging, permissions, and idempotency handling that are unnecessary if direct event routing is available. Therefore, this option is functional but not the simplest managed solution among the choices.

Kinesis Data Streams + Firehose is simple and fully managed, but the default Firehose buffer interval is typically too large for “least latency” requirements. Firehose batches data before writing to S3, so objects appear in S3 only after buffering thresholds are met. This is good for cost/efficiency and operational simplicity, but not for minimizing end-to-end delivery time.

Kinesis Data Streams alone does not natively deliver to S3; you still need a consumer/connector to read from the stream and write to S3. Configuring 5 shards addresses throughput and parallelism, not the S3 delivery mechanism or latency. Without a delivery application (or Firehose), this option is incomplete for the stated requirement.

Managed Service for Apache Flink is for stream processing (transformations, aggregations) and does not inherently reduce S3 delivery latency versus a direct consumer. Adding Firehose reintroduces buffering, and even with a 5-second interval, Firehose still batches and may not beat a custom KCL consumer that flushes on a strict schedule. This option adds complexity without being the lowest-latency path.

EXPLAIN SELECT * FROM sales; returns the query execution plan without running the query. It is useful to understand operator flow (scan, filter, join, exchange), but it generally does not provide the actual runtime computational cost per operator because the query is not executed. This fails the requirement to see the cost of each operation.

EXPLAIN ANALYZE FROM sales; is not valid SQL syntax in Athena/Presto/Trino because EXPLAIN (or EXPLAIN ANALYZE) must be followed by a complete query statement such as SELECT ... FROM .... Since it is syntactically incorrect, it cannot produce either a plan or cost metrics.

EXPLAIN FROM sales; is not valid SQL syntax because EXPLAIN must precede a full query (e.g., SELECT ...). Without a complete statement, Athena cannot generate an execution plan. Therefore it cannot satisfy either requirement and is an example of a common exam distractor based on incomplete syntax.

Amazon S3 Select can retrieve a subset of data from a single S3 object using a SQL-like expression. However, it does not support JOINs across multiple objects/buckets as a general query engine, does not provide table/partition metadata management, and does not deliver ACID transactional guarantees. It is best for filtering within individual objects to reduce data transfer, not for building partitioned, ACID-compliant analytical datasets.

Amazon Redshift Spectrum lets you run SQL against data in S3 via external tables, and it can leverage partitions in the Glue Data Catalog. However, Spectrum is tied to a Redshift cluster, which introduces ongoing provisioning and cost (even if queries are infrequent). While powerful for integrated Redshift analytics, it is typically less cost-effective than Athena for once-daily serverless querying directly on S3 with minimal operational overhead.

Amazon EMR (Spark/Hive/Presto/Trino) can query S3 data, manage partitions, and achieve ACID semantics using table formats like Iceberg/Delta/Hudi. However, EMR typically requires cluster management and can be more expensive/operationally heavy than Athena for simple daily KPI queries. Even with EMR Serverless, it is usually more complex than Athena for straightforward serverless SQL querying and catalog-based partitioning.

Amazon Kinesis Data Streams is a fully managed streaming service, but it is not Apache Kafka. Migrating from Kafka to Kinesis typically requires refactoring producers and consumers to use Kinesis APIs and concepts (streams, shards, partition keys). That violates the requirement to use a replatform strategy and increases migration effort even if ongoing ops are low.

Amazon MSK provisioned clusters are Kafka-compatible and support replatforming with minimal application changes. However, you must choose broker instance types, broker counts, storage, and scaling plans, and you manage capacity (even though AWS manages patching and availability). This creates more management overhead than MSK Serverless for variable or unknown workloads.

Amazon Kinesis Data Firehose is primarily a managed delivery service to load streaming data into destinations like S3, Redshift, or OpenSearch with optional transformations. It is not a general-purpose Kafka replacement for consumer groups and multiple independent subscribers. Using Firehose would require architectural changes (refactor) and would not meet the Kafka migration requirement.