[Q15-Q31] Use the best ways of preparing for DEA-C01 Exam Dumps with TrainingQuiz Snowflake DEA-C01 PDF Dumps [2024]

QUESTION 15
A retail company uses Amazon Aurora PostgreSQL to process and store live transactional data.
The company uses an Amazon Redshift cluster for a data warehouse.
An extract, transform, and load (ETL) job runs every morning to update the Redshift cluster with new data from the PostgreSQL database. The company has grown rapidly and needs to cost optimize the Redshift cluster.
A data engineer needs to create a solution to archive historical data. The data engineer must be able to run analytics queries that effectively combine data from live transactional data in PostgreSQL, current data in Redshift, and archived historical data. The solution must keep only the most recent 15 months of data in Amazon Redshift to reduce costs.
Which combination of steps will meet these requirements? (Choose two.)

Configure the Amazon Redshift Federated Query feature to query live transactional data that is in the PostgreSQL database.

Configure Amazon Redshift Spectrum to query live transactional data that is in the PostgreSQL database.

Schedule a monthly job to copy data that is older than 15 months to Amazon S3 by using the UNLOAD command. Delete the old data from the Redshift cluster. Configure Amazon Redshift Spectrum to access historical data in Amazon S3.

Schedule a monthly job to copy data that is older than 15 months to Amazon S3 Glacier Flexible Retrieval by using the UNLOAD command. Delete the old data from the Redshift cluster. Configure Redshift Spectrum to access historical data from S3 Glacier Flexible Retrieval.

Create a materialized view in Amazon Redshift that combines live, current, and historical data from different sources.

QUESTION 16
A security company stores IoT data that is in JSON format in an Amazon S3 bucket. The data structure can change when the company upgrades the IoT devices. The company wants to create a data catalog that includes the IoT data. The company’s analytics department will use the data catalog to index the data.
Which solution will meet these requirements MOST cost-effectively?

Create an AWS Glue Data Catalog. Configure an AWS Glue Schema Registry. Create a new AWS Glue workload to orchestrate the ingestion of the data that the analytics department will use into Amazon Redshift Serverless.

Create an Amazon Redshift provisioned cluster. Create an Amazon Redshift Spectrum database for the analytics department to explore the data that is in Amazon S3. Create Redshift stored procedures to load the data into Amazon Redshift.

Create an Amazon Athena workgroup. Explore the data that is in Amazon S3 by using Apache Spark through Athena. Provide the Athena workgroup schema and tables to the analytics department.

Create an AWS Glue Data Catalog. Configure an AWS Glue Schema Registry. Create AWS Lambda user defined functions (UDFs) by using the Amazon Redshift Data API. Create an AWS Step Functions job to orchestrate the ingestion of the data that the analytics department will use into Amazon Redshift Serverless.

QUESTION 17
Bob, a Lead Data Engineer is looking out to get the function definition & queried below statement to check if this function is secure enough to use in his script or not.
select is_secure from information_schema.functions where function_name = ‘JOHNFUNCTION’; From the query output he is sure that, Function is secure UDF, what are the way provided by snow-flake to get the function definition of secure UDF?

He can get the secure UDF definition using GET_DDL utility function.

UDF definition or text, is visible to users via Query Profile (in the web interface).

SHOW FUNCTIONS Commands

Declaring a UDF as “secure” hide the definition from Bob & all the required Definition commands will throw error.

QUESTION 18
Select the incorrect statement while working with warehouses?

Compute resources waiting to shut down are considered to be in “quiesce” mode.

Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.

Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.

Resizing a suspended warehouse does not provision any new compute resources for the warehouse.

QUESTION 19
A company wants to migrate an application and an on-premises Apache Kafka server to AWS.
The application processes incremental updates that an on-premises Oracle database sends to the Kafka server. The company wants to use the replatform migration strategy instead of the refactor strategy.
Which solution will meet these requirements with the LEAST management overhead?

Amazon Kinesis Data Streams

Amazon Managed Streaming for Apache Kafka (Amazon MSK) provisioned cluster

Amazon Kinesis Data Firehose

Amazon Managed Streaming for Apache Kafka (Amazon MSK) Serverless

QUESTION 20
Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly. Which Best techniques she should consider here?

She can go for clustering on underlying tables which can speedup Equality searches.

A materialized view speeds both equality searches and range searches.

The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches.
(Correct)

Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.

QUESTION 21
By default, a newly-created Custom role is not assigned to any user, nor granted to any other role?

TRUE

FALSE

QUESTION 22
A company created an extract, transform, and load (ETL) data pipeline in AWS Glue. A data engineer must crawl a table that is in Microsoft SQL Server. The data engineer needs to extract, transform, and load the output of the crawl to an Amazon S3 bucket. The data engineer also must orchestrate the data pipeline.
Which AWS service or feature will meet these requirements MOST cost-effectively?

AWS Step Functions

AWS Glue workflows

AWS Glue Studio

Amazon Managed Workflows for Apache Airflow (Amazon MWAA)

QUESTION 23
A financial company wants to implement a data mesh. The data mesh must support centralized data governance, data analysis, and data access control. The company has decided to use AWS Glue for data catalogs and extract, transform, and load (ETL) operations.
Which combination of AWS services will implement a data mesh? (Choose two.)

Use Amazon Aurora for data storage. Use an Amazon Redshift provisioned cluster for data analysis.

Use Amazon S3 for data storage. Use Amazon Athena for data analysis.

Use AWS Glue DataBrew for centralized data governance and access control.

Use Amazon RDS for data storage. Use Amazon EMR for data analysis.

Use AWS Lake Formation for centralized data governance and access control.

QUESTION 24
Which of the below concepts/functions helps while implementing advanced Column-level Security?

CURRENT_ROLE

INVOKER_ROLE

Role Hierarchy

CURRENT_CLIENT

QUESTION 25
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.

QUESTION 26
Streams cannot be created to query change data on which of the following objects? [Select All that Apply]

Query Log Tables

External tables

Standard tables, including shared tables.

Views, including secure views

Directory tables

QUESTION 27
A database contains a table and a stored procedure defined as.

No other operations are affecting the log_table.
What will be the outcome of the procedure call?

The Iog_table contains zero records and the stored procedure returned 1 as a return value

The Iog_table contains one record and the stored procedure returned 1 as a return value

The log_table contains one record and the stored procedure returned NULL as a return value

The Iog_table contains zero records and the stored procedure returned NULL as a return value

QUESTION 28
Which of the following best describes the type of data found in traditional relational databases?

Structured data

Unstructured data

Semi-structured data

Free-form data

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

session.read.json{)

session.builder()

DataFraas.writeO

session.jdbc_connection()

session,table()

session.sql()

QUESTION 30
Find out the odd one out:

1. Bulk Data Load: Loads are always performed in a single transaction.
2. SnowPipe: Loads are combined or split into a single or multiple transactions based on the number and size of the rows in each data file.

1. Bulk Data Load: Requires a user-specified warehouse to execute COPY statements.
2. SnowPipe: Uses Snowflake-supplied compute resources.

1. Bulk Data Load: Billed for the amount of time each virtual warehouse is active.
2. SnowPipe: Billed according to the compute resources used in the Snowpipe ware-house while loading the files.

1. Bulk Data Load: Load history Stored in the metadata of the target table for 365 days.
2. SnowPipe: Load history Stored in the metadata of the pipe for 64 days.

QUESTION 31
A Data Engineer is evaluating the performance of a query in a development environment.

Based on the Query Profile what are some performance tuning options the Engineer can use? (Select TWO)

Add a LIMIT to the ORDER BY If possible

Use a multi-cluster virtual warehouse with the scaling policy set to standard

Move the query to a larger virtual warehouse

Create indexes to ensure sorted access to data

Increase the max cluster count

Explanation
The performance tuning options that the Engineer can use based on the Query Profile are:
Add a LIMIT to the ORDER BY If possible: This option will improve performance by reducing the amount of data that needs to be sorted and returned by the query. The ORDER BY clause requires sorting all rows in the input before returning them, which can be expensive and time-consuming. By adding a LIMIT clause, the query can return only a subset of rows that satisfy the order criteria, which can reduce sorting time and network transfer time.
Create indexes to ensure sorted access to data: This option will improve performance by reducing the amount of data that needs to be scanned and filtered by the query. The query contains several predicates on different columns, such as o_orderdate, o_orderpriority, l_shipmode, etc. By creating indexes on these columns, the query can leverage sorted access to data and prune unnecessary micro-partitions or rows that do not match the predicates. This can reduce IO time and processing time.
The other options are not optimal because:
Use a multi-cluster virtual warehouse with the scaling policy set to standard: This option will not improve performance, as the query is already using a multi-cluster virtual warehouse with the scaling policy set to standard. The Query Profile shows that the query is using a 2XL warehouse with 4 clusters and a standard scaling policy, which means that the warehouse can automatically scale up or down based on the load. Changing the warehouse size or the number of clusters will not affect the performance of this query, as it is already using the optimal resources.
Increase the max cluster count: This option will not improve performance, as the query is not limited by the max cluster count. The max cluster count is a parameter that specifies the maximum number of clusters that a multi-cluster virtual warehouse can scale up to. The Query Profile shows that the query is using a 2XL warehouse with 4 clusters and a standard scaling policy, which means that the warehouse can automatically scale up or down based on theload. The default max cluster count for a 2XL warehouse is 10, which means that the warehouse can scale up to 10 clusters if needed. However, the query does not need more than 4 clusters, as it is not CPU-bound or memory-bound. Increasing the max cluster count will not affect the performance of this query, as it will not use more clusters than necessary.