nerdexam
Amazon

DEA-C01 · Question #73

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…

The correct answer is A. Create a view in the EC2 instance-based SQL Server databases that contains the required data. The most operationally efficient way to regularly export large SQL join results to Parquet in S3 during migration is by using AWS Glue jobs and a SQL Server view.

Data Ingestion and Transformation

Question

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?

Options

  • ACreate a view in the EC2 instance-based SQL Server databases that contains the required data
  • BSchedule SQL Server Agent to run a daily SQL query that selects the desired data elements from
  • CUse a SQL query to create a view in the EC2 instance-based SQL Server databases that
  • DCreate an AWS Lambda function that queries the EC2 instance-based databases by using Java

How the community answered

(51 responses)
  • A
    82% (42)
  • B
    10% (5)
  • C
    2% (1)
  • D
    6% (3)

Why each option

The most operationally efficient way to regularly export large SQL join results to Parquet in S3 during migration is by using AWS Glue jobs and a SQL Server view.

ACreate a view in the EC2 instance-based SQL Server databases that contains the required dataCorrect

Creating a view simplifies the complex SQL joins into a single logical table, making it easier for an ETL service like AWS Glue to extract the required data daily. An AWS Glue job can then connect to the SQL Server, query the view, and transform the data into Apache Parquet format before storing it in Amazon S3, minimizing operational overhead for daily exports.

BSchedule SQL Server Agent to run a daily SQL query that selects the desired data elements from

Scheduling SQL Server Agent is for tasks within SQL Server; exporting to S3 in Parquet format would require external tools or custom scripting, increasing operational overhead compared to a managed ETL service.

CUse a SQL query to create a view in the EC2 instance-based SQL Server databases that

This choice is incomplete as it only describes creating a view, not how the data will be extracted, converted to Parquet, and stored in S3 daily.

DCreate an AWS Lambda function that queries the EC2 instance-based databases by using Java

An AWS Lambda function is typically not suitable for processing potentially very large datasets from a database, converting them to Parquet, and uploading to S3, as it can hit memory and execution time limits, requiring more complex orchestration.

Concept tested: Data export from SQL Server to S3 with AWS Glue

Source: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connections.html

Topics

#SQL Server Views#Data Transformation#Operational Efficiency#Data Preparation

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DEA-C01 Practice