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

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 structural details for a view. With secure views, the view definition and details are visible only to authorized 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 granted 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.

When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions. The reason is that the owner of the data being shared does not typically control the users or roles in the account with which the view is being shared.

Snowflake strongly recommend partitioning your external tables, which requires that your underlying data is organized using logical paths that include date, time, country, or similar dimensions in the path.

Partitioning divides your external table data into multiple parts using partition columns.

An external table definition can include multiple partition columns, which impose a multidimensional structure on the external data.

Partitions are stored in the external table metadata.

Benefits of partitioning include improved query performance.

Because the external data is partitioned into separate slices/parts, query response time is faster when processing a small part of the data instead of scanning the entire data set.

Based on your individual use cases, you can either:

· Add new partitions automatically by refreshing an external table that defines an expression for each partition column.

· Add new partitions manually.

Partition columns are defined when an external table is created, using the CREATE EXTERNAL TABLE … PARTITION BY syntax.

After an external table is created, the method by which partitions are added cannot be changed.

Partitions Added Automatically

An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn.

A partition consists of all data files that match the path and/or filename in the expression for the partition column.

The CREATE EXTERNAL TABLE syntax for adding partitions automatically based on expres-sions is as follows:

CREATE EXTERNAL TABLE

<table_name>

( <part_col_name> <col_type> AS <part_expr> )

[ , ... ]

[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]

..

Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.

By default, the metadata is refreshed automatically when the object is created.

In addition, the object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.

The owner can alternatively refresh the metadata manually by executing the ALTER EXTERNAL TABLE … REFRESH command.

The metadata for an external table can be refreshed automatically using the event notification service for your cloud storage service.

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.

A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a material-ized view is faster than executing a query against the base table of the view. This performance dif-ference can be significant when a query is run frequently or is sufficiently complex. As a result, ma-terialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.

Materialized views require Enterprise Edition.

Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns. However, materializing intermediate results incurs additional costs.

As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.

Materialized views are particularly useful when:

· Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

· Query results contain results that require significant processing, including:

1. Analysis of semi-structured data.

2. Aggregates that take a long time to calculate.

· The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.

The view's base table does not change frequently.

METADATA$FILENAME

A pseudocolumn that identifies the name of each staged data file included in the external table, including its path in the stage.

An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn. A partition consists of all data files that match the path and/or filename in the expression for the parti-tion column.

Defining a clustering key on a materialized view is supported and can increase performance in many situations. However, it also adds costs.

If you cluster both the materialized view(s) and the base table on which the materialized view(s) are defined, you can cluster the materialized view(s) on different columns from the columns used to cluster the base table.

You can create a materialized view on shared data.

Also You can use Snowflake's data sharing feature to share a materialized view.

Rest all are correct.

All Steps will be executed successfully by Alex without any error.

Materialized Views, like other database objects (tables, views, UDFs, etc.), are owned by a role and have privileges that can be granted to other roles.

You can grant the following privileges on a materialized view:

SELECT

As with non-materialized views, a materialized view does not automatically inherit the privileges of its base table. You should explicitly grant privileges on the materialized view to the roles that should use that view.

As with non-materialized views, a user who wishes to access a materialized view needs privileges only on the view, not on the underlying object(s) that the view references.

Rest is correct.