Free Dec-2024 DEA-C01 Certification Sample Questions certification Exam [Q35-Q56]

Q35. Michael, a Data Engineer Running a Data query to achieve Union of Data sets coming from Multi-ple data sources, later he figured out that Data processing query is taking more time than expected. He started analyzing the Query performance using query profile interface. He discovered & realized that he used UNION when the UNION ALL semantics was sufficient.
Which Extra Data Processing Operator Michael figured out while doing query profile analysis in this case which helps him to identify this performance bottlenecks?

Aggregate

UNION ALL

Flatten

Join

Filter

Q36. A data engineer runs Amazon Athena queries on data that is in an Amazon S3 bucket. The Athena queries use AWS Glue Data Catalog as a metadata table.
The data engineer notices that the Athena query plans are experiencing a performance bottleneck. The data engineer determines that the cause of the performance bottleneck is the large number of partitions that are in the S3 bucket. The data engineer must resolve the performance bottleneck and reduce Athena query planning time.
Which solutions will meet these requirements? (Choose two.)

Create an AWS Glue partition index. Enable partition filtering.

Bucket the data based on a column that the data have in common in a WHERE clause of the user query.

Use Athena partition projection based on the S3 bucket prefix.

Transform the data that is in the S3 bucket to Apache Parquet format.

Use the Amazon EMR S3DistCP utility to combine smaller objects in the S3 bucket into larger objects.

Q37. Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables. He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?

MAX_DATA_EXTENSION_TIME_IN_DAYS

DATA_RETENTION_TIME_IN_DAYS

DATA_EXTENSION_TIME_IN_DAYS

None of the above

Q38. A data engineer is configuring Amazon SageMaker Studio to use AWS Glue interactive sessions to prepare data for machine learning (ML) models.
The data engineer receives an access denied error when the data engineer tries to prepare the data by using SageMaker Studio.
Which change should the engineer make to gain access to SageMaker Studio?

Add the AWSGlueServiceRole managed policy to the data engineer’s IAM user.

Add a policy to the data engineer’s IAM user that includes the sts:AssumeRole action for the AWS Glue and SageMaker service principals in the trust policy.

Add the AmazonSageMakerFullAccess managed policy to the data engineer’s IAM user.

Add a policy to the data engineer’s IAM user that allows the sts:AddAssociation action for the AWS Glue and SageMaker service principals in the trust policy.

Q39. Which methods can be used to create a DataFrame object in Snowpark? (Select THREE)

session.jdbc_connection()

session.read.json{)

session,table()

DataFraas.writeO

session.builder()

session.sql()

Q40. In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?

Data Hub

Data Vault

Star Schema

Snowflake Schema

Q41. Which Function would Data engineer used to recursively resume all tasks in Chain of Tasks rather than resuming each task individually (using ALTER TASK … RESUME)?

SYSTEM$TASK_DEPENDENTS

SYSTEM$TASK_DEPENDENTS_ENABLE

SYSTEM$TASK_DEPENDENTS_RESUME

SYSTEM$TASK_RECURSIVE_ENABLE

Q42. UDTFs also called a table function, returns zero, one, or multiple rows for each input row?

YES

NO

Q43. A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by tope according to what data each Analyst needs Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved.
How should the Data Engineer configure the compute resources to support this dashboard?

Assign queries to a multi-cluster virtual warehouse with economy auto-scaling Allow the system to automatically start and stop clusters according to demand.

Assign all queries to a multi-cluster virtual warehouse set to maximized mode Monitor to determine the smallest suitable number of clusters.

Create a virtual warehouse for every 250 Analysts Monitor to determine how many of these virtual warehouses are being utilized at capacity.

Create a size XL virtual warehouse to support all the dashboard queries Monitor query runtimes to determine whether the virtual warehouse should be resized.

Q44. A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.
Which recommendations apply to this scenario? (Select THREE)

Load large files (1 GB or larger)

Ensure that data files are 100-250 MB (or larger) in size compressed

Load a single huge array containing multiple records into a single table row

Verify each value of each unique element stores a single native data type (string or number)

Extract semi-structured data elements containing null values into relational columns before loading

Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute

Q45. A data engineer needs Amazon Athena queries to finish faster. The data engineer notices that all the files the Athena queries use are currently stored in uncompressed .csv format. The data engineer also notices that users perform most queries by selecting a specific column.
Which solution will MOST speed up the Athena query performance?

Change the data format from .csv to JSON format. Apply Snappy compression.

Compress the .csv files by using Snappy compression.

Change the data format from .csv to Apache Parquet. Apply Snappy compression.

Compress the .csv files by using gzip compression.

Q46. A company stores details about transactions in an Amazon S3 bucket. The company wants to log all writes to the S3 bucket into another S3 bucket that is in the same AWS Region.
Which solution will meet this requirement with the LEAST operational effort?

Configure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the event to Amazon Kinesis Data Firehose. Configure Kinesis Data Firehose to write the event to the logs S3 bucket.

Create a trail of management events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.

Configure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the events to the logs S3 bucket.

Create a trail of data events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.

Q47. Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.
1. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
2. Recreate the pipe (using CREATE OR REPLACE PIPE).
3. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?

CREATE OR REPLACE PIPE command will recreate the PIPE successfully.

Terminate the existing pipe (using ALTER PIPE … SET PIPE_EXECUTION_TERMINATE = true) before recreation.

Pause the pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = false).

Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).

Q48. Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists. Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?

SYSTEM$STREAM_HAS_CHANGE_DATA

SYSTEM$STREAM_CDC_DATA

SYSTEM$STREAM_HAS_DATA

SYSTEM$STREAM_DELTA_DATA

Q49. A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the data transformation process The average file size is 300-500 MB.
The Engineer needs to ensure that Snowpipe is performant while minimizing costs.
How can this be achieved?

Increase the size of the virtual warehouse used by Snowpipe.

Split the files before loading them andset the SIZE_LIMIT option to 250 MB.

Change the file compression size and increase the frequency of the Snowpipe loads

Decrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose

Q50. When would a Data engineer use table with the flatten function instead of the lateral flatten combination?

When TABLE with FLATTENrequires another source in the from clause to refer to

WhenTABLE with FLATTENrequires no additional source m the from clause to refer to

Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to

When table withFLATTENis acting like a sub-query executed for each returned row

Q51. What is the primary purpose of data lineage in data engineering?

To optimize query performance.

To transform data formats.

To trace the source and flow of data.

To create visualizations.

Q52. The following is returned fromSYSTEMCLUSTERING_INFORMATION () for a tablenamed orders with adate column named O_ORDERDATE:

What does the total_constant_partition_count value indicate about this table?

The table is clustered very well on_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering

The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro partition in the table.

The data inO_ORDERDATEdoes not change very often as there are 493 micro-partitionscontaining rows where that column has not been modified since the row was created

The data inO_ORDERDATEhas a very low cardinality as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition

Q53. External Function is a type of UDF & can be Scaler or Tabular?

TRUE

FALSE

Q54. During a security review, a company identified a vulnerability in an AWS Glue job. The company discovered that credentials to access an Amazon Redshift cluster were hard coded in the job script.
A data engineer must remediate the security vulnerability in the AWS Glue job. The solution must securely store the credentials.
Which combination of steps should the data engineer take to meet these requirements? (Choose two.)

Store the credentials in the AWS Glue job parameters.

Store the credentials in a configuration file that is in an Amazon S3 bucket.

Access the credentials from a configuration file that is in an Amazon S3 bucket by using the AWS Glue job.

Store the credentials in AWS Secrets Manager.

Grant the AWS Glue job IAM role access to the stored credentials.

Q55. A Data Engineer wants to centralize grant management to maximize security. A user needs ownership on a table m a new schema However, this user should not have the ability to make grant decisions What is the correct way to do this?

Grant ownership to the user on the table

Revoke grant decisions from the user on the table

Revoke grant decisions from the user on the schema.

Add the with managed access parameter on the schema

Q56. Which is the non-supportable JavaScript UDF data types?

Integers

String

Binary

Double