[Nov-2024] DEA-C01 Dumps Full Questions - SnowPro Advanced Exam Study Guide
Exam Questions and Answers for DEA-C01 Study Guide
NEW QUESTION # 65
You have been tasked with migrating an on-premises MySQL database to Amazon Aurora PostgreSQL using AWS Database Migration Service (DMS). The stakeholder emphasizes that the source database must remain fully operational during the migration process.
Which of the following statements about DMS is accurate with respect to this scenario?
- A. AWS DMS can convert the MySQL database schema directly to PostgreSQL without any manual intervention.
- B. When using DMS, the target Amazon Aurora PostgreSQL instance cannot be accessed or queried until the migration is complete.
- C. AWS DMS only supports full-load migrations, which would require downtime for the source database.
- D. AWS DMS supports both full-load and continuous replication, allowing the source MySQL database to remain operational during migration.
- E. AWS DMS requires the source MySQL database to be version 5.7 or higher for migrating to Amazon Aurora PostgreSQL.
Answer: D
NEW QUESTION # 66
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove par-titions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A. FALSE
- B. TRUE
Answer: B
Explanation:
Explanation
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
1.CREATE EXTERNAL TABLE
2.<table_name>
3.( <part_col_name> <col_type> AS <part_expr> )
4.[ , ... ]
5.[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
6.PARTITION_TYPE = USER_SPECIFIED
Included the required PARTITION_TYPE = USER_SPECIFIED parameter.
NEW QUESTION # 67
When created, a stream logically takes an initial snapshot of every row in the source object and the contents of a stream change as DML statements execute on the source table.
A Data Engineer, Sophie Created a view that queries the table and returns the CURRENT_USER and CURRENT_TIMESTAMP values for the query transaction. A Stream has been created on views to capture CDC.
Tony, another user inserted the data e.g.
insert into <table> values (1),(2),(3);
Emily, another user also inserted the data e.g.
insert into <table> values (4),(5),(6);
What will happened when Different user queries the same stream after 1 hour?
- A. User would be displayed with the one who queried during the session, but Recorded timestamp would be of past 1 hour i.e. actual records insertion time.
- B. All the 6 records would be shown with METADATA$ACTION as 'INSERT' out of which 3 records would be displayed with username 'Tony' & rest 3 records would be displayed with username 'Emily'.
- C. All the Six records would be displayed with User 'Sohpie' Who is the owner of the View.
- D. All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.
Answer: D
Explanation:
Explanation
When User queries the stream, the stream returns the username for the user. The stream also returns the current timestamp for the query transaction in each row, NOT the timestamp when each row was inserted.
NEW QUESTION # 68
Snowflake does not provide which of following set of SQL functions to support retrieving infor-mation about tasks?
- A. TASK_DEPENDENTS
- B. SYSTEM$CURRENT_USER_TASK_NAME
- C. SYSTEM$TASK_DEPENDENTS_ENABLE
- D. TASK_HISTORY
- E. TASK_QUERY_HISTORY
Answer: A
Explanation:
Explanation
SYSTEM$CURRENT_USER_TASK_NAME
Returns the name of the task currently executing when invoked from the statement or stored proce-dure defined by the task.
SYSTEM$TASK_DEPENDENTS_ENABLE
Recursively resumes all dependent tasks tied to a specified root task.
TASK_DEPENDENTS
This table function returns the list of child tasks for a given root task in a DAG of tasks.
TASK_HISTORY
This table function can be used to query the history of task usage within a specified date range.
NEW QUESTION # 69
If external software i.e. TIBCO, exports Data fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, How Snowflake handle it? [Select 2]
- A. field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.
- B. COPY command trims the leading space and removes the quotation marks enclosing each field
1.copy into SFtable
2.from @%SFtable
3.file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22'); - C. Snowflake reads the leading space rather than the opening quotation character as the beginning of the field and the quotation characters are interpreted as string data.
(Correct) - D. Snowflake automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.
Answer: B
Explanation:
Explanation
If your external software exports fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, Snowflake reads the leading space rather than the open-ing quotation character as the beginning of the field. The quotation characters are interpreted as string data.
Use the TRIM_SPACE file format option to remove undesirable spaces during the data load.
NEW QUESTION # 70
Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?
- A. initialize ()
- B. finalize ()
- C. processRow ()
- D. handler
Answer: C
Explanation:
Explanation
The processRow () callback function is required within a JavaScript UDF for it to execute successfully. This function defines how each row of input data is processed and what output is returned. The other callback functions are optional and can be used for initialization, finalization, or error handling.
NEW QUESTION # 71
What are Common Query Problems a Data Engineer can identified using Query Profiler?
- A. Inefficient Pruning
- B. Ineffective Data Sharing
- C. "Exploding" Joins i.e Joins resulting due to a "Cartesian product"
- D. Queries Too Large to Fit in Memory
Answer: A,C,D
Explanation:
Explanation
"Exploding" Joins
One of the common mistakes SQL users make is joining tables without providing a join condition (resulting in a "Cartesian product"), or providing a condition where records from one table match multiple records from another table. For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.
This can be observed by looking at the number of records produced by a Join operator in the profile interface, and typically is also reflected in Join operator consuming a lot of time.
Queries Too Large to Fit in Memory
For some operations (e.g. duplicate elimination for a huge data set), the amount of memory available for the compute resources used to execute the operation might not be sufficient to hold intermediate results. As a result, the query processing engine will start spilling the data to local disk. If the local disk space is not sufficient, the spilled data is then saved to remote disks.
This spilling can have a profound effect on query performance (especially if remote disk is used for spilling).
Spilling statistics can be checked in Query Profile Interface.
Inefficient Pruning
Snowflake collects rich statistics on data allowing it not to read unnecessary parts of a table based on the query filters. However, for this to have an effect, the data storage order needs to be correlat-ed with the query filter attributes.
The efficiency of pruning can be observed by comparing Partitions scanned and Partitions total sta-tistics in the TableScan operators. If the former is a small fraction of the latter, pruning is efficient. If not, the pruning did not have an effect.
Of course, pruning can only help for queries that actually filter out a significant amount of data. If the pruning statistics do not show data reduction, but there is a Filter operator above TableScan which filters out a number of records, this might signal that a different data organization might be beneficial for this query.
NEW QUESTION # 72
While running an external function, me following error message is received:
Error:function received the wrong number of rows
What iscausing this to occur?
- A. External functions do not support multiple rows
- B. Nested arrays are not supported in the JSON response
- C. The return message did not produce the same number of rows that it received
- D. The JSON returned by the remote service is not constructed correctly
Answer: C
Explanation:
Explanation
The error message "function received the wrong number of rows" is caused by the return message not producing the same number of rows that it received. External functions require that the remote service returns exactly one row for each input row that it receives from Snowflake. If the remote service returns more or fewer rows than expected, Snowflake will raise an error and abort the function execution. The other options are not causes of this error message. Option A is incorrect because external functions do support multiple rows as long as they match the input rows. Option B is incorrect because nested arrays are supported in the JSON response as long as they conform to the return type definition of the external function. Option C is incorrect because the JSON returned by the remote service may be constructed correctly but still produce a different number of rows than expected.
NEW QUESTION # 73
Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?
- A. Secure views should not be used for views that are defined solely for query conven-ience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.
- B. For a secure view, internal optimizations can indirectly expose data & the view defini-tion is visible to other users.
- C. The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner's Query Profile.
- D. For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.
- E. To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW com-mand.
Answer: A,C,D,E
Explanation:
Explanation
Why Should I Use Secure Views?
For a non-secure view, internal optimizations can indirectly expose data.
Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.
For a non-secure view, the view definition is visible to other users.
By default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces.
For security or privacy reasons, you might not wish to expose the underlying tables or internal struc-tural details for a view. With secure views, the view definition and details are visible only to author-ized users (i.e.
users who are granted the role that owns the view).
When Should I Use a Secure View?
Views should be defined as secure when they are specifically designated for data privacy (i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s)).
Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to understand the underlying data representation. Secure views can execute more slowly than non-secure views.
Secure views are defined using the SECURE keyword with the standard DDL for views:
To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MA-TERIALIZED VIEW command.
To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.
The definition of a secure view is only exposed to authorized users (i.e. users who have been grant-ed the role that owns the view). If an unauthorized user uses any of the following commands or in-terfaces, the view definition is not displayed:
SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.
GET_DDL utility function.
VIEWS Information Schema view.
VIEWS Account Usage view.
For non-materialized views, the IS_SECURE column in the Information Schema and Account Us-age views identifies whether a view is secure.
The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner's Query Profile.
NEW QUESTION # 74
A company uses a data lake that is based on an Amazon S3 bucket. To comply with regulations, the company must apply two layers of server-side encryption to files that are uploaded to the S3 bucket. The company wants to use an AWS Lambda function to apply the necessary encryption.
Which solution will meet these requirements?
- A. Use dual-layer server-side encryption with AWS KMS keys (DSSE-KMS).
- B. Use server-side encryption with AWS KMS keys (SSE-KMS).
- C. Use server-side encryption with customer-provided keys (SSE-C) before files are uploaded.
- D. Use both server-side encryption with AWS KMS keys (SSE-KMS) and the Amazon S3 Encryption Client.
Answer: A
Explanation:
https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingDSSEncryption.html
NEW QUESTION # 75
What are Invalid rules applicable when using stored procedure contains transaction?
- A. All Rules are Applicable.
- B. A transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.
- C. You cannot start a transaction inside the stored procedure, then complete the transac-tion after returning from the procedure.
- D. A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction.
- E. You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure.
- F. If a transaction is started inside a stored procedure and is still active when the stored procedure finishes, then an error occurs, and the transaction is rolled back.
Answer: A
NEW QUESTION # 76
A Data Engineer is working on a Snowflake deployment in AWS eu-west-1 (Ireland). The Engineer is planning to load data from staged files into target tables using the copy into command Which sources are valid? (Select THREE)
- A. External stage in an Amazon S3 bucket on AWS eu-central 1 (Frankfurt)
- B. Internal stage on GCP us-central1 (Iowa)
- C. Internal stage on AWS eu-central-1 (Frankfurt)
- D. External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland)
- E. External stage on GCP us-central1 (Iowa)
- F. SSO attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland)
Answer: A,D,E
Explanation:
Explanation
The valid sources for loading data from staged files into target tables using the copy into command are:
External stage on GCP us-central1 (Iowa): This is a valid source because Snowflake supports cross-cloud data loading from external stages on different cloud platforms and regions than the Snowflake deployment.
External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland): This is a valid source because Snowflake supports data loading from external stages on the same cloud platform and region as the Snowflake deployment.
External stage in an Amazon S3 bucket on AWS eu-central 1 (Frankfurt): This is a valid source because Snowflake supports cross-region data loading from external stages on different regions than the Snowflake deployment within the same cloud platform. The invalid sources are:
Internal stage on GCP us-central1 (Iowa): This is an invalid source because internal stages are always located on the same cloud platform and region as the Snowflake deployment. Therefore, an internal stage on GCP us-central1 (Iowa) cannot be used for a Snowflake deployment on AWS eu-west-1 (Ireland).
Internal stage on AWS eu-central-1 (Frankfurt): This is an invalid source because internal stages are always located on the same region as the Snowflake deployment. Therefore, an internal stage on AWS eu-central-1 (Frankfurt) cannot be used for a Snowflake deployment on AWS eu-west-1 (Ireland).
SSO attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland): This is an invalid source because SSO stands for Single Sign-On, which is a security integration feature in Snowflake, not a data staging option.
NEW QUESTION # 77
Snowpipe API provides a REST endpoint for defining the list of files to ingest that Informs Snow-flake about the files to be ingested into a table. A successful response from this endpoint means that Snowflake has recorded the list of files to add to the table. It does not necessarily mean the files have been ingested. What is name of this Endpoint?
- A. REST endpoints --> ingestfiles
- B. REST endpoints --> insertReport
- C. REST endpoints--> insertfiles
- D. REST endpoints --> loadHistoryScan
Answer: C
Explanation:
Explanation
The Snowpipe API provides a REST endpoint for defining the list of files to ingest.
Endpoint: insertFiles
Informs Snowflake about the files to be ingested into a table. A successful response from this end-point means that Snowflake has recorded the list of files to add to the table. It does not necessarily mean the files have been ingested. For more details, see the response codes below.
In most cases, Snowflake inserts fresh data into the target table within a few minutes.
To Know more about SnowFlake Rest API used for Data File ingestion, do refer:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-apis.html#data-file-ingestion
NEW QUESTION # 78
Pivoting in SQL is mainly used to transform data from:
- A. Only one column to one row
- B. Multiple rows into multiple columns
- C. Multiple columns into multiple rows
- D. One row to one column
Answer: B
NEW QUESTION # 79
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)?
- A. SYSTEM$TASK_DEPENDENTS
- B. SYSTEM$TASK_DEPENDENTS_RESUME
- C. SYSTEM$TASK_DEPENDENTS_ENABLE
- D. SYSTEM$TASK_RECURSIVE_ENABLE
Answer: C
Explanation:
Explanation
To recursively resume all tasks in a DAG(A Directed Acyclic Graph (DAG) is a series of tasks com-posed of a single root task and additional tasks, organized by their dependencies.), query the SYS-TEM$TASK_DEPENDENTS_ENABLE function rather than resuming each task individually (us-ing ALTER TASK ... RESUME).
NEW QUESTION # 80
Can Masking policies be applied to virtual columns?
- A. TRUE
- B. FALSE
Answer: B
NEW QUESTION # 81
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.)
- A. Store the credentials in AWS Secrets Manager.
- B. Store the credentials in the AWS Glue job parameters.
- C. Grant the AWS Glue job IAM role access to the stored credentials.
- D. Access the credentials from a configuration file that is in an Amazon S3 bucket by using the AWS Glue job.
- E. Store the credentials in a configuration file that is in an Amazon S3 bucket.
Answer: A,C
NEW QUESTION # 82
......
Snowflake DEA-C01 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
| Topic 4 |
|
| Topic 5 |
|
SnowPro Advanced: Data Engineer Certification Exam Free Update With 100% Exam Passing Guarantee: https://itcertspass.itcertmagic.com/Snowflake/real-DEA-C01-exam-prep-dumps.html