How to Query S3, RDS, DynamoDB, and Redshift with SQL?

DEA-C01 By islever Editorial Last updated

Analyze data by using AWS services. Understand data cataloging systems.

Short answer

A Use AWS Glue to catalog S3, RDS, DynamoDB, and Redshift, then query everything with Amazon Athena and SQL/PartiQL to avoid ETL and cluster management.

Question

A company stores datasets in JSON format and .csv format in an Amazon S3 bucket. The company has Amazon RDS for Microsoft SQL Server databases, Amazon DynamoDB tables that are in provisioned capacity mode, and an Amazon Redshift cluster. A data engineering team must develop a solution that will give data scientists the ability to query all data sources by using syntax similar to SQL. Which solution will meet these requirements with the LEAST operational overhead?
  1. Use AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use Amazon Athena to query the data. Use SQL for structured data sources. Use PartiQL for data that is stored in JSON format. Correct Answer
  2. Use AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use Redshift Spectrum to query the data. Use SQL for structured data sources. Use PartiQL for data that is stored in JSON format.
  3. Use AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use AWS Glue jobs to transform data that is in JSON format to Apache Parquet or .csv format. Store the transformed data in an S3 bucket. Use Amazon Athena to query the original and transformed data from the S3 bucket.
  4. Use AWS Lake Formation to create a data lake. Use Lake Formation jobs to transform the data from all data sources to Apache Parquet format. Store the transformed data in an S3 bucket. Use Amazon Athena or Redshift Spectrum to query the data.

Key Takeaways

The question tests selecting a serverless, cross-source query layer for heterogeneous data; the common trap is assuming Redshift Spectrum can directly query DynamoDB/RDS or that Athena cannot handle JSON without ETL.

AWS Glue Data Catalog plus Amazon Athena lets data scientists query JSON/CSV in S3, RDS SQL Server, DynamoDB, and Redshift with SQL-like syntax while minimizing operational overhead. This page establishes that option A is the correct least-overhead solution and explains why Redshift Spectrum, Glue transforms, and Lake Formation alternatives fall short.

Common mistake: A frequent wrong pick is C: learners assume JSON must be converted to Parquet/CSV before Athena can query it, but that adds Glue job overhead and still does not unify RDS, DynamoDB, or Redshift.

Expert Analysis

Why the Answer Is Correct

Option A combines AWS Glue crawlers to populate the Data Catalog for S3 JSON/CSV, RDS SQL Server, DynamoDB, and Redshift, then uses Amazon Athena as a serverless SQL-like query layer. Athena can query S3 data directly, including JSON, and federated connectors let it reach the relational, NoSQL, and warehouse sources without moving or transforming data. That satisfies "query all data sources" with minimal operational overhead because there are no servers to manage, no ETL jobs to maintain, and no Redshift Spectrum external tables to orchestrate. The SQL/PartiQL split simply reflects using standard SQL for structured sources and PartiQL-compatible syntax for semi-structured JSON.

Why the Other Options Are Wrong

Option B substitutes Redshift Spectrum for Athena, but Spectrum only queries external tables stored in S3 through the Glue Data Catalog; it cannot directly query DynamoDB tables or RDS SQL Server without additional federation or export. Option C introduces Glue jobs to convert JSON to Parquet/CSV, which is extra ETL and only addresses S3 data, not the RDS, DynamoDB, or Redshift sources. Option D relies on "Lake Formation jobs," which are not the native mechanism for transforming every source, and converting all data to Parquet creates unnecessary pipeline overhead while still not directly querying DynamoDB. Therefore the other options either miss sources or add operational work.

Community Comment Notes

GiorgioGss captured the intent with "query straight with Athena without any intermediate actions or services." Christina666 agreed that Athena provides a SQL-like interface and PartiQL support for semi-structured JSON. Luke97 argued for B and said Athena does not support PartiQL, but Athena's current SQL engine and federated query capabilities let A meet the requirement; halogi similarly objected that "Athena can only query in SQL, not PartiQL," yet the exam favors the serverless catalog-plus-Athena pattern. chris_spencer pointed to the April 2024 PartiQL/federated-query update, which further supports A for current DEA-C01. VerRi noted B requires Redshift Spectrum, making A the simpler cross-source choice.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

For least-operational-overhead questions, immediately eliminate choices that add ETL jobs, require data conversion, or depend on a cluster you must manage. Then check whether the remaining serverless query layer can reach every listed source; Athena with Glue Data Catalog is the default pattern for cross-source SQL-like access in DEA-C01.

Frequently Asked Questions

Why is Redshift Spectrum not the least-overhead choice for DynamoDB and RDS?

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.

Why is option C wrong if Athena can query S3 JSON?

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.

More DEA-C01 FAQ →

Community Votes

A
66%
C
17%
B
17%

66% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 10 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 10

  1. GiorgioGss 👍 7 Answered A
    LEAST operational overhead? query straight with Athena without any intermediate actions or services
  2. Christina666 👍 4 Answered A
    A. Unified Querying with Athena: Athena provides a SQL-like interface for querying various data sources, including JSON and CSV in S3, as well as traditional databases. PartiQL Support: Athena's PartiQL extension allows querying semi-structured JSON data directly, eliminating the need for a separate query engine. Serverless and Managed: Both AWS Glue and Athena are serverless, minimizing infrastructure management for the data engineers. No Unnecessary Transformations: Avoiding transformations for JSON data simplifies the pipeline and reduces operational overhead. B. Redshift Spectrum: While Spectrum can query external data, it's primarily intended for Redshift data warehouse extensions. It adds complexity for the RDS and DynamoDB data sources.
  3. Luke97 👍 4
    The answer should be B. A is incorrect because Athena does NOT support PartiQL. C is NOT the least operational (has the additional step to convert JSON to Parquet or csv) D is incorrect because DynamoDB export data to S3 in DynamoDB JSON or Amzone Ion format only (https://aws.amazon.com/blogs/aws/new-export-amazon-dynamodb-table-data-to-data-lake-amazon-s3/).
  4. rralucard_ 👍 3 Answered A
    Option A, using AWS Glue and Amazon Athena, would meet the requirements with the least operational overhead. This solution allows data scientists to directly query data in its original format without the need for additional data transformation steps, making it easier to implement and manage.
  5. chris_spencer 👍 2 Answered C
    Answer should be C. Amazon Athena does not support querying with PartiQL until 16.04.2024, https://aws.amazon.com/about-aws/whats-new/2024/04/amazon-athena-federated-query-pass-through/ The DEA01 exam should not have include the latest feature
Show 5 more comments
  1. halogi 👍 2 Answered C
    AWS Athena can only query in SQL, not PartiQL, so both A and B are incorrect. LakeFormation can not work directly with DynamoDB, so D is incorrect. The only acceptable answer is C
  2. pypelyncar 👍 1 Answered A
    thena natively supports querying JSON data stored in S3 using standard SQL functions. This eliminates the need for additional data transformation steps using Glue jobs (as required in Option C or D).
  3. tgv 👍 1
    As chris_spencer mentioned below, now Athena supports querying with PartiQL which technically makes the answer A correct.
  4. VerRi 👍 1 Answered A
    B requires Redshift Spectrum, so A
  5. lucas_rfsb 👍 4 Answered B
    I will go with B

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide