DEA-C01 practice question 24 of 25

A company is migrating its database servers from Amazon EC2 instances that run Microsoft SQL Server to Amazon RDS for Microsoft SQL Server DB…

A company is migrating its database servers from Amazon EC2 instances that run Microsoft SQL Server to Amazon RDS for Microsoft SQL Server DB instances. The company's analytics team must export large data elements every day until the migration is complete. The data elements are the result of SQL joins across multiple tables. The data must be in Apache Parquet format. The analytics team must store the data in Amazon S3. Which solution will meet these requirements in the MOST operationally efficient way?

Answer options

A: Create a view in the EC2 instance-based SQL Server databases that contains the required data elements. Create an AWS Glue job that selects the data directly from the view and transfers the data in Parquet format to an S3 bucket. Schedule the AWS Glue job to run every day. Correct answer

B: Schedule SQL Server Agent to run a daily SQL query that selects the desired data elements from the EC2 instance-based SQL Server databases. Configure the query to direct the output .csv objects to an S3 bucket. Create an S3 event that invokes an AWS Lambda function to transform the output format from .csv to Parquet.

C: Use a SQL query to create a view in the EC2 instance-based SQL Server databases that contains the required data elements. Create and run an AWS Glue crawler to read the view. Create an AWS Glue job that retrieves the data and transfers the data in Parquet format to an S3 bucket. Schedule the AWS Glue job to run every day.

D: Create an AWS Lambda function that queries the EC2 instance-based databases by using Java Database Connectivity (JDBC). Configure the Lambda function to retrieve the required data, transform the data into Parquet format, and transfer the data into an S3 bucket. Use Amazon EventBridge to schedule the Lambda function to run every day.

Correct Answer:

A. Create a view in the EC2 instance-based SQL Server databases that contains the required data elements. Create an AWS Glue job that selects the data directly from the view and transfers the data in Parquet format to an S3 bucket. Schedule the AWS Glue job to run every day.

Explanation

AWS Glue is a fully managed ETL service designed for large-scale data processing. An AWS Glue job can connect to the SQL Server database on EC2 using a JDBC connection, execute a query against a predefined view to get the complex join results, transform the data into Parquet format, and write it to Amazon S3. This entire workflow can be encapsulated in a single, schedulable job. This approach is highly efficient as it uses a purpose-built, serverless tool, minimizing infrastructure management and operational complexity.

Why Incorrect

B. This multi-service approach (SQL Agent, S3 Events, Lambda) is more complex and has more potential points of failure than a single, integrated AWS Glue job.
C. Using an AWS Glue crawler is unnecessary because the schema is already defined by the SQL view. The Glue job can query the source directly, making the crawler a redundant step.
D. AWS Lambda has time and memory limits that may be insufficient for "large data elements." AWS Glue is specifically designed for large-scale ETL and is the more robust and scalable choice.

References

1. AWS Documentation. (2023). AWS Glue Developer Guide. Adding a Job in AWS Glue. Retrieved from https://docs.aws.amazon.com/glue/latest/dg/add-job.html (This section describes creating jobs that perform ETL operations).

2. AWS Documentation. (2023). AWS Glue Developer Guide. Connection types and options for ETL in AWS Glue for Spark. Retrieved from https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect.html (See the section on "JDBC connection type" which confirms connectivity to databases like SQL Server).

3. AWS Documentation. (2023). AWS Glue Developer Guide. Data format options for inputs and outputs in AWS Glue for Spark. Retrieved from https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-format-options-parquet.html (This confirms native support for writing data in Parquet format).

Keep practicing

Take the free DEA-C01 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test