Snowflake DEA-C01 Practice Test - Questions Answers, Page 9
List of questions
Question 81

Data engineer designed the data pipelines using Snowpipe to load data files into Snowflake tables, what will happen in case few files with same name but modified data are queued for reloading?
Explanation:
Snowflake uses file loading metadata to prevent reloading the same files (and duplicating data) in a table. Snowpipe prevents loading files with the same name even if they were later modified (i.e.
have a different eTag).
The file loading metadata is associated with the pipe object rather than the table. As a result:
· Staged files with the same name as files that were already loaded are ignored, even if they have been modified, e.g. if new rows were added or errors in the file were corrected.
· Truncating the table using the TRUNCATE TABLE command does not delete the Snowpipe file loading metadata.
Question 82

Data Engineer decided to call the public REST endpoints to load data and retrieve load history reports.
Which of the following REST endpoints and a Snowflake Information Schema table function for viewing your load history can be used by her? [Select All that apply]
Explanation:
Snowflake provides REST endpoints and an Snowflake Information Schema table function for viewing your load history:
REST endpoints:
insertReport
loadHistoryScan
Information Schema table function:
COPY_HISTORY
Account Usage view:
COPY_HISTORY
Question 83

Mark the incorrect statement when Data Engineer implement Automating Continuous Data Loading Using Cloud Messaging?
Explanation:
Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake accounts hosted on Amazon Web Services (AWS) only.
Rest is correct statements.
Question 84

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?
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 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. 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
Question 85

The Snowpipe API provides REST endpoints for fetching load reports. One of the Endpoint named insertReport helps to retrieves a report of files submitted via insertFiles end point whose contents were recently ingested into a table. A success response (200) contains information about files that have recently been added to the table. Response Looks like below:
Explanation:
Permissible Load status for the file:
LOAD_IN_PROGRESS: Part of the file has been loaded into the table, but the load process has not completed yet.
LOADED: The entire file has been loaded successfully into the table.
LOAD_FAILED: The file load failed.
PARTIALLY_LOADED: Some rows from this file were loaded successfully, but others were not loaded due to errors. Processing of this file is completed.
Please not the different Response Codes available with their meaning.
200 — Success. Report returned.
400 — Failure. Invalid request due to an invalid format, or limit exceeded.
404 — Failure. pipeName not recognized.
This error code can also be returned if the role used when calling the endpoint does not have sufficient privileges. For more information, see Granting Access Privileges.
429 — Failure. Request rate limit exceeded.
500 — Failure. Internal error occurred.
As you could understand from the questions, there is 200 Success response returned, Status in the response body would be LOADED.
Question 86

Data Engineer try to load data from external stage using Snowpipe & later find out that some Set of Files Not Loaded. To debug the issue, she used COPY_HISTORY function & cross verified that its output indicates a subset of files was not loaded. What is possible reason of arising this situation in both REST API call and Auto-Ingest methods? [Select 2]
Explanation:
COPY_HISTORY Record Indicates Unloaded Subset of Files:
If the COPY_HISTORY function output indicates a subset of files was not loaded, you may try to "refresh" the pipe.
This situation can arise in any of the following situations:
· The external stage was previously used to bulk load data using the COPY INTO table command.
· REST API:
o External event-driven functionality is used to call the REST APIs, and a backlog of data files al-ready existed in the external stage before the events were configured.
· Auto-ingest:
o A backlog of data files already existed in the external stage before event notifications were configured.
o An event notification failure prevented a set of files from getting queued.
To load the data files in your external stage using the configured pipe, execute an ALTER PIPE … REFRESH statement.
Question 87

Data Engineer is looking out to delete staged files automatically/periodically when the data is successfully loaded into tables by the Snowpipe. For achieving the same, which options/command is best suited: [Select 2]
Explanation:
Deleting Staged Files After Snowpipe Loads the Data
Pipe objects do not support the PURGE copy option. Snowpipe cannot delete staged files automatically when the data is successfully loaded into tables.
To remove staged files that you no longer need, It is recommended to periodically executing the REMOVE command to delete the files.
Alternatively, configure any lifecycle management features provided by cloud storage service provider.
Question 88

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.
Explanation:
Recreating a pipe (using a CREATE OR REPLACE PIPE statement) is necessary to modify most pipe properties.
Recreating Pipes for Automated Data Loads When recreating a pipe that automates data loads using event notifications, it's recommended that Data Engineer complete the following steps:
1. Pause the pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = true).
2. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is PAUSED.
3. Recreate the pipe (using CREATE OR REPLACE PIPE).
4. Pause the pipe again.
5. Review the configuration steps for your cloud messaging service to ensure the settings are still accurate.
6. Query the SYSTEM$PIPE_STATUS function again and verify that the pipe execution state is RUNNING.
Question 89

Dominic, a Data Engineer wants to resume the pipe named stalepipe3 which got stale after 14 days.
To do the same, he called the SYSTEM$PIPE_FORCE_RESUME function select system$ pipe_force_resume('snowmydb.mysnowschema.stalepipe3','staleness_check_override');
Let's say If the pipe is resumed 16 days after it was paused, what will happened to the event notification that were received on the first and second days after the pipe was paused?
Explanation:
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:
select sys-tem$pipe_force_resume('mydb.myschema.stalepipe3','staleness_check_override');
As an event notification received while a pipe is paused reaches the end of the limited retention period, Snowflake schedules it to be dropped from the internal metadata. If the pipe is later resumed,
Snowpipe processes these older notifications on a best effort basis. Snowflake cannot guarantee that they are processed.
For example, if a pipe is resumed 15 days after it was paused, Snowpipe generally skips any event notifications that were received on the first day the pipe was paused (i.e. that are now more than 14 days old). If the pipe is resumed 16 days after it was paused, Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused. And so on.
Question 90

Data Engineer looking out for quick tool for understanding the mechanics of queries & need to know more about the performance or behaviour of a particular query.
He should go to which feature of snowflake which can help him to spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities?
Explanation:
Query Profile, available through the classic web interface, provides execution details for a query. For the selected query, it provides a graphical representation of the main components of the pro-cessing plan for the query, with statistics for each component, along with details and statistics for the overall query.
Query Profile is a powerful tool for understanding the mechanics of queries. It can be used whenever you want or need to know more about the performance or behavior of a particular query. It is designed to help you spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities.
Question