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

Materialized Views works best for search query performance in case of Sort Operations. For Rest of the points Search optimization works best to improve query performance.

Regular views do not cache data, and therefore cannot improve performance by caching.

How Does Warehouse Caching Impact Queries?

Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the compute resources in the warehouse (i.e. the larger the warehouse and, therefore, more compute re-sources in the warehouse), the larger the cache.

This cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed. As the resumed warehouse runs and processes more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.

Keep this in mind when deciding whether to suspend a warehouse or leave it running. In other words, consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance.

Using Persisted Query Results

When a query is executed, the result is persisted (i.e. cached) for a period of time. At the end of the time period, the result is purged from the system.

Snowflake uses persisted query results to avoid re-generating results when nothing has changed (i.e.

"retrieval optimization"). In addition, you can use persisted query results to post-process the results (e.g. layering a new query on top of the results already calculated).

For persisted query results of all sizes, the cache expires after 24 hours.

Both materialized views and cached query results provide query performance benefits:

Materialized views are more flexible than, but typically slower than, cached results.

Materialized views are faster than tables because of their "cache" (i.e. the query results for the view); in addition, if data has changed, they can use their "cache" for data that hasn't changed and use the base table for any data that has changed.

Regular views do not cache data, and therefore cannot improve performance by caching.

The STATUS column indicates whether a particular set of files was loaded, partially loaded, or failed to load. The FIRST_ERROR_MESSAGE column provides a reason when an attempt partial-ly loaded or failed.

Note that if a set of files has multiple issues, the FIRST_ERROR_MESSAGE column only indi-cates the first error encountered. To view all errors in the files, execute a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS. The VALIDATION_MODE copy option instructs a COPY statement to validate the data to be loaded and return results based on the validation option specified. No data is loaded when this copy option is specified.

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.stalepipe1','staleness_check_override');

While the stale pipe was paused, if ownership of the pipe was transferred to another role, then resuming the pipe requires the additional OWNERSHIP_TRANSFER_CHECK_OVERRIDE argu-ment. For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:

SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override, own-ership_transfer_check_override');

Monitoring Files Staged Internally

Snowflake maintains detailed metadata for each file uploaded into internal stage (for users, tables, and stages), including:

· File name · File size (compressed, if compression was specified during upload) · LAST_MODIFIED date, i.e. the timestamp when the data file was initially staged or when it was last modified, whichever is later

In addition, Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days. The metadata can be used to monitor and manage the loading process, including de-leting files after upload completes:

· Use the LIST command to view the status of data files that have been staged.

· Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.

· Use the VALIDATE function to validate the data files you've loaded and retrieve any errors encountered during the load.

· Use the LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.

Managing Data Files

Staged files can be deleted from a Snowflake stage (user stage, table stage, or named stage) using the following methods:

· Files that were loaded successfully can be deleted from the stage during a load by specifying the PURGE copy option in the COPY INTO <table> command.

· After the load completes, use the REMOVE command to remove the files in the stage.

Removing files ensures they aren't inadvertently loaded again. It also improves load performance, because it reduces the number of files that COPY commands must scan to verify whether existing files in a stage were loaded already.

To retrieve only tasks that are completed or still running, filter the query using WHERE query_id IS NOT NULL.

SYSTEM$CURRENT_USER_TASK_NAME

Returns the name of the task currently executing when invoked from the statement or stored procedure defined by the task.

SYSTEMASK_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.