Which tasks can be performed by the ORGADMIN role? (Select THREE).
ABD
Explanation:
A user with the ORGADMIN role can perform the following tasks1:
•
Create one or more accounts in the organization.
•
View a list of all regions enabled for the organization.
•
View usage information for all accounts in the organization.
Option C is incorrect because creating secure views on application tables is not a function of the
ORGADMIN role, but rather a function of the roles that have access to the tables and schemas within
the accounts. Option E is incorrect because performing zero-copy cloning on account data is not a
function of the ORGADMIN role, but rather a function of the roles that have the CLONE privilege on
the objects within the accounts. Option F is incorrect because creating a reader account to share data
with another organization is not a function of the ORGADMIN role, but rather a function of the roles
that have the CREATE SHARE privilege on the objects within the accounts.
What role or roles should be used to properly create the object required to setup OAuth 2.0
integration?
D
Explanation:
According to the Using OAuth 2.0 with Snowflake - Blog, only the ACCOUNTADMIN role can create
and manage integrations, so an administrator must assume that role when creating a security
integration for OAuth. The other roles do not have the necessary privileges to create the object
required to setup OAuth 2.0 integration.
The following SQL command was executed:
Use role SECURITYADMIN;
Grant ownership
On future tables
In schema PROD. WORKING
To role PROD_WORKING_OWNER;
Grant role PROD_WORKING_OWNER to role SYSADMIN;
Use role ACCOUNTADMIN;
Create table PROD.WORKING.XYZ (value number) ;
Which role(s) can alter or drop table XYZ?
C
Explanation:
According to the GRANT OWNERSHIP documentation, the ownership privilege grants full control
over the table and can only be held by one role at a time. However, the current owner can also grant
the ownership privilege to another role, which transfers the ownership to the new role. In this case,
the SECURITYADMIN role granted the ownership privilege on future tables in the PROD.WORKING
schema to the PROD_WORKING_OWNER role. This means that any table created in that schema
after the grant statement will be owned by the PROD_WORKING_OWNER role. Therefore, the
PROD_WORKING_OWNER role can alter or drop table XYZ, which was created by the
ACCOUNTADMIN role in the PROD.WORKING schema. Additionally, the ACCOUNTADMIN role can
also alter or drop table XYZ, because it is the top-level role that has all privileges on all objects in the
account. Furthermore, the SYSADMIN role can also alter or drop table XYZ, because it was granted
the PROD_WORKING_OWNER role by the SECURITYADMIN role. The SYSADMIN role can activate the
PROD_WORKING_OWNER role and inherit its privileges, including the ownership privilege on table
XYZ. The SECURITYADMIN role cannot alter or drop table XYZ, because it does not have the
ownership privilege on the table, nor does it have the PROD_WORKING_OWNER role.
When adding secure views to a share in Snowflake, which function is needed to authorize users from
another account to access rows in a base table?
C
Explanation:
According to the Working with Secure Views documentation, secure views are designed to limit
access to sensitive data that should not be exposed to all users of the underlying table(s). When
sharing secure views with another account, the view definition must include a function that returns
the identity of the user who is querying the view, such as CURRENT_USER, CURRENT_ROLE, or
CURRENT_ACCOUNT. These functions can be used to filter the rows in the base table based on the
user’s identity. For example, a secure view can use the CURRENT_USER function to compare the user
name with a column in the base table that contains the authorized user names. Only the rows that
match the user name will be returned by the view. The CURRENT_CLIENT function is not suitable for
this purpose, because it returns the IP address of the client that is connected to Snowflake, which is
not related to the user’s identity.
In which scenario will use of an external table simplify a data pipeline?
D
Explanation:
According to the Introduction to External Tables documentation, an external table is a Snowflake
feature that allows you to query data stored in an external stage as if the data were inside a table in
Snowflake. The external stage is not part of Snowflake, so Snowflake does not store or manage the
stage. This simplifies the data pipeline by eliminating the need to load the data into Snowflake before
querying it. External tables can access data stored in any format that the COPY INTO <table>
command supports, such as CSV, JSON, AVRO, ORC, or PARQUET. The other scenarios do not involve
external tables, but rather require data loading, unloading, or federation.
A Snowflake user runs a complex SQL query on a dedicated virtual warehouse that reads a large
amount of data from micro-partitions. The same user wants to run another
query that uses the same data set.
Which action would provide optimal performance for the second SQL query?
D
Explanation:
According to the Using Persisted Query Results documentation, the RESULT_SCAN function allows
you to query the result set of a previous command as if it were a table. This can improve the
performance of the second query by avoiding reading the same data from micro-partitions again. The
other actions do not provide optimal performance for the second query because:
•
Assigning additional clusters to the virtual warehouse does not affect the data access speed,
but only the query execution speed. It also increases the cost of the warehouse.
•
Increasing the STATEMENT_TIMEOUT_IN_SECONDS parameter in the session does not
improve the performance of the query, but only allows it to run longer before timing out. It also
increases the risk of resource contention and deadlock.
•
Preventing the virtual warehouse from suspending between the running of the first and
second queries does not guarantee that the data will be cached in memory, as Snowflake uses a least
recently used (LRU) cache eviction policy. It also increases the cost of the warehouse.
https://docs.snowflake.com/en/user-guide/querying-persisted-results
For Snowflake network policies, what will occur when the account_level and user_level network
policies are both defined?
B
Explanation:
According to the Network Policies documentation, a network policy can be applied to an account, a
security integration, or a user. If there are network policies applied to more than one of these, the
most specific network policy overrides more general network policies. The following summarizes the
order of precedence:
•
Account: Network policies applied to an account are the most general network policies. They
are overridden by network policies applied to a security integration or user.
•
Security Integration: Network policies applied to a security integration override network
policies applied to the account, but are overridden by a network policy applied to a user.
•
User: Network policies applied to a user are the most specific network policies. They
override both accounts and security integrations.
Therefore, if both the account_level and user_level network policies are defined, the user_level
policy will take effect and the account_level policy will be ignored. The other options are incorrect
because:
•
The account_level policy will not override the user_level policy, as explained above.
•
The user_level network policies will be supported, as they are part of the network policy
feature.
•
A network policy error will not be generated, as there is no conflict between the
account_level and user_level network policies.
MY_TABLE is a table that has not been updated or modified for several days. On 01 January 2021 at
07:01, a user executed a query to update this table. The query ID is
'8e5d0ca9-005e-44e6-b858-a8f5b37c5726'. It is now 07:30 on the same day.
Which queries will allow the user to view the historical data that was in the table before this query
was executed? (Select THREE).
BDF
Explanation:
According to the AT | BEFORE documentation, the AT or BEFORE clause is used for Snowflake Time
Travel, which allows you to query historical data from a table based on a specific point in the past.
The clause can use one of the following parameters to pinpoint the exact historical data you wish to
access:
•
TIMESTAMP: Specifies an exact date and time to use for Time Travel.
•
OFFSET: Specifies the difference in seconds from the current time to use for Time Travel.
•
STATEMENT: Specifies the query ID of a statement to use as the reference point for Time
Travel.
Therefore, the queries that will allow the user to view the historical data that was in the table before
the query was executed are:
•
B. SELECT * FROM my_table AT (TIMESTAMP => ‘2021-01-01 07:00:00’ :: timestamp); This
query uses the TIMESTAMP parameter to specify a point in time that is before the query execution
time of 07:01.
•
D. SELECT * FROM my table PRIOR TO STATEMENT ‘8e5d0ca9-005e-44e6-b858-
a8f5b37c5726’; This query uses the PRIOR TO STATEMENT keyword and the STATEMENT parameter
to specify a point in time that is immediately preceding the query execution time of 07:01.
•
F. SELECT * FROM my_table BEFORE (STATEMENT => ‘8e5d0ca9-005e-44e6-b858-
a8f5b37c5726’); This query uses the BEFORE keyword and the STATEMENT parameter to specify a
point in time that is immediately preceding the query execution time of 07:01.
The other queries are incorrect because:
•
A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30); This query uses the
OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is
07:30. This is after the query execution time of 07:01, so it will not show the historical data before
the query was executed.
•
C. SELECT * FROM TIME_TRAVEL (‘MY_TABLE’, 2021-01-01 07:00:00); This query is not valid
syntax for Time Travel. The TIME_TRAVEL function does not exist in Snowflake. The correct syntax is
to use the AT or BEFORE clause after the table name in the FROM clause.
•
E. SELECT * FROM my_table AT (OFFSET => -60*30); This query uses the AT keyword and the
OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is
07:30. This is equal to the query execution time of 07:01, so it will not show the historical data before
the query was executed. The AT keyword specifies that the request is inclusive of any changes made
by a statement or transaction with timestamp equal to the specified parameter. To exclude the
changes made by the query, the BEFORE keyword should be used instead.
What are characteristics of Dynamic Data Masking? (Select TWO).
BE
Explanation:
According to the Using Dynamic Data Masking documentation, Dynamic Data Masking is a feature
that allows you to alter sections of data in table and view columns at query time using a predefined
masking strategy. The following are some of the characteristics of Dynamic Data Masking:
•
A single masking policy can be applied to columns in different tables. This means that you
can write a policy once and have it apply to thousands of columns across databases and schemas.
•
A single masking policy can be applied to columns with different data types. This means that
you can use the same masking strategy for columns that store different kinds of data, such as strings,
numbers, dates, etc.
•
A masking policy that is currently set on a table can be dropped. This means that you can
remove the masking policy from the table and restore the original data visibility.
•
A masking policy can be applied to the VALUE column of an external table. This means that
you can mask data that is stored in an external stage and queried through an external table.
•
The role that creates the masking policy will always see unmasked data in query results. This
is not true, as the masking policy can also apply to the creator role depending on the execution
context conditions defined in the policy. For example, if the policy specifies that only users with a
certain custom entitlement can see the unmasked data, then the creator role will also need to have
that entitlement to see the unmasked data.
A Snowflake Administrator needs to set up Time Travel for a presentation area that includes facts and
dimensions tables, and receives a lot of meaningless and erroneous
loT data. Time Travel is being used as a component of the company's data quality process in which
the ingestion pipeline should revert to a known quality data state if any
anomalies are detected in the latest load. Data from the past 30 days may have to be retrieved
because of latencies in the data acquisition process.
According to best practices, how should these requirements be met? (Select TWO).
BE
Explanation:
According to the Understanding & Using Time Travel documentation, Time Travel is a feature that
allows you to query, clone, and restore historical data in tables, schemas, and databases for up to 90
days. To meet the requirements of the scenario, the following best practices should be followed:
•
The fact and dimension tables should have the same DATA_RETENTION_TIME_IN_DAYS. This
parameter specifies the number of days for which the historical data is preserved and can be
accessed by Time Travel. To ensure that the fact and dimension tables can be reverted to a consistent
state in case of any anomalies in the latest load, they should have the same retention period.
Otherwise, some tables may lose their historical data before others, resulting in data inconsistency
and quality issues.
•
The fact and dimension tables should be cloned together using the same Time Travel options
to reduce potential referential integrity issues with the restored data. Cloning is a way of creating a
copy of an object (table, schema, or database) at a specific point in time using Time Travel. To ensure
that the fact and dimension tables are cloned with the same data set, they should be cloned together
using the same AT or BEFORE clause. This will avoid any referential integrity issues that may arise
from cloning tables at different points in time.
The other options are incorrect because:
•
Related data should not be placed together in the same schema. Facts and dimension tables
should each have their own schemas. This is not a best practice for Time Travel, as it does not affect
the ability to query, clone, or restore historical data. However, it may be a good practice for data
modeling and organization, depending on the use case and design principles.
•
The DATA_RETENTION_TIME_IN_DAYS should be kept at the account level and never used
for lower level containers (databases and schemas). This is not a best practice for Time Travel, as it
limits the flexibility and granularity of setting the retention period for different objects. The retention
period can be set at the account, database, schema, or table level, and the most specific setting
overrides the more general ones. This allows for customizing the retention period based on the data
needs and characteristics of each object.
•
Only TRANSIENT tables should be used to ensure referential integrity between the fact and
dimension tables. This is not a best practice for Time Travel, as it does not affect the referential
integrity between the tables. Transient tables are tables that do not have a Fail-safe period, which
means that they cannot be recovered by Snowflake after the retention period ends. However, they
still support Time Travel within the retention period, and can be queried, cloned, and restored like
permanent tables. The choice of table type depends on the data durability and availability
requirements, not on the referential integrity.