Snowflake DEA-C01 Practice Test - Questions Answers, Page 9

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

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.

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

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.

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.

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.

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.

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.

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.