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.