When a role is dropped, which role inherits ownership of objects owned by the dropped role?
B
Explanation:
According to the Snowflake documentation1, when a role is dropped, ownership of all objects owned
by the dropped role is transferred to the role that is directly above the dropped role in the role
hierarchy. This is to ensure that there is always a single owner for each object in the system.
1: Drop Role | Snowflake Documentation
.
Company A uses Snowflake to manage audio files of call recordings. Company A hired Company B,
who also uses Snowflake, to transcribe the audio files for further
analysis.
Company A's Administrator created a share.
What object should be added to the share to allow Company B access to the files?
B
Explanation:
According to the Snowflake documentation1, pre-signed URLs are required to access external files in
a share. A secure view can be used to generate pre-signed URLs for the audio files stored in an
external stage and expose them to the consumer account. Option A is incorrect because file URLs
alone are not sufficient to access external files in a share. Option C is incorrect because
METADATA$FILENAME only returns the file name, not the full path or URL. Option D is incorrect
because the stage name and file path are not enough to generate pre-signed URLs.
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID
column (varchar(50)). The vast majority of analyses on this table are
grouped by STORE_ID to look at store performance.
There are 1000 stores operated by the retailer but most sales come from only 20 stores. The
Administrator notes that most queries are currently experiencing poor pruning,
with large amounts of bytes processed by even simple queries.
Why is this occurring?
C
Explanation:
According to the Snowflake documentation1, clustering keys are most effective when the data is
evenly distributed across the key values. If the data is skewed, such as in this case where most sales
come from only 20 stores out of 1000, then the micro-partitions will not be well-clustered and the
pruning will be poor. This means that more bytes will be scanned by queries, even if they filter by
STORE_ID. Option A is incorrect because the data type of the clustering key does not affect the
pruning. Option B is incorrect because the table is large enough to benefit from clustering, if the data
was more balanced. Option D is incorrect because the cardinality of the clustering key is not relevant
for pruning, as long as the key values are distinct.
1: Considerations for Choosing Clustering for a Table | Snowflake Documentation
A team is provisioning new lower environments from the production database using cloning. All
production objects and references reside in the database, and do not have
external references.
What set of object references needs to be re-pointed before granting access for usage?
C
Explanation:
According to the Snowflake documentation1, when an object in a schema is cloned, any future
grants defined for this object type in the schema are applied to the cloned object unless the COPY
GRANTS option is specified in the CREATE statement for the clone operation. However, some objects
may still reference the source object or external objects after cloning, which may cause issues with
access or functionality. These objects include:
•
Sequences: If a table column references a sequence that generates default values, the cloned
table may reference the source or cloned sequence, depending on where the sequence is defined. To
avoid conflicts, the sequence reference should be re-pointed to the desired sequence using the
ALTER TABLE command2.
•
Storage integrations: If a stage or a table references a storage integration, the cloned object
may still reference the source storage integration, which may not be accessible or valid in the new
environment. To avoid errors, the storage integration reference should be re-pointed to the desired
storage integration using the ALTER STAGE or ALTER TABLE command34.
•
Views, secure views, and materialized views: If a view references another view or table, the
cloned view may still reference the source object, which may not be accessible or valid in the new
environment. To avoid errors, the view reference should be re-pointed to the desired object using
the CREATE OR REPLACE VIEW command5.
1: Cloning Considerations | Snowflake Documentation 2: [ALTER TABLE | Snowflake Documentation]
3: [ALTER STAGE | Snowflake Documentation] 4: [ALTER TABLE | Snowflake Documentation] 5:
[CREATE VIEW | Snowflake Documentation]
Which function is the role SECURITYADMIN responsible for that is not granted to role USERADMIN?
B
Explanation:
According to the Snowflake documentation1, the SECURITYADMIN role is responsible for managing
all grants on objects in the account, including system grants. The USERADMIN role can only create
and manage users and roles, but not grant privileges on other objects. Therefore, the function that is
unique to the SECURITYADMIN role is to manage system grants. Option A is incorrect because both
roles can reset a user’s password. Option C is incorrect because both roles can create new users.
Option D is incorrect because both roles can create new roles.
An Administrator has a table named SALES_DATA which needs some edits, but the Administrator
does not want to change the main table data. The Administrator decides to
make a transient copy of this table and wants the transient table to have all the same permissions as
the original table.
How can the Administrator create the transient table so it inherits the same permissions as the
original table, and what considerations need to be made concerning the
requirements? (Select TWO).
BD
Explanation:
According to the Snowflake documentation1, the COPY GRANTS option can be used to copy all
privileges, except OWNERSHIP, from the existing table to the new transient table. This option also
preserves any future grants defined for the object type in the schema. Option A is incorrect because
it does not copy any grants from the original table. Option C is incorrect because it does not copy the
data from the original table, only the structure and grants. Option E is incorrect because transient
tables are not session-based and do not have a Fail-safe period, but they do have a Time Travel
retention period2.
1: CREATE TABLE | Snowflake Documentation 2: Working with Temporary and Transient Tables |
Snowflake Documentation
Which actions are considered breaking changes to data that is shared with consumers in the
Snowflake Marketplace? (Select TWO).
AD
Explanation:
According to the Snowflake documentation1, breaking changes are changes that affect the schema or
structure of the shared data, such as dropping or renaming a column or a table. These changes may
cause errors or unexpected results for the consumers who query the shared data. Deleting data from
a table, unpublishing the data listing, or adding region availability to the listing are not breaking
changes, as they do not alter the schema or structure of the shared data.
1: Managing Data Listings in Snowflake Data Marketplace | Snowflake Documentation
What are the MINIMUM grants required on the database, schema, and table for a stream to be
properly created and managed?
A
An Administrator has been asked to support the company's application team need to build a loyalty
program for its customers. The customer table contains Personal
Identifiable Information (PII), and the application team's role is DEVELOPER.
CREATE TABLE customer_data (
customer_first_name string,
customer_last_name string,
customer_address string,
customer_email string,
... some other columns,
);
The application team would like to access the customer data, but the email field must be obfuscated.
How can the Administrator protect the sensitive information, while maintaining the usability of the
data?
D
An organization's sales team leverages this Snowflake query a few times a day:
SELECT CUSTOMER ID, CUSTOMER_NAME, ADDRESS, PHONE NO
FROM CUSTOMERS
WHERE LAST UPDATED BETWEEN TO_DATE (CURRENT_TIMESTAMP) AND (TO_DATE
(CURRENT_TIMESTAMP) -7);
What can the Snowflake Administrator do to optimize the use of persisted query results whenever
possible?
D
Explanation:
According to the web search results from my predefined tool search_web, one of the factors that
affects the reuse of persisted query results is the exact match of the query syntax1. If the query
contains functions that return different values for successive runs, such as CURRENT_TIMESTAMP,
then the query will not match the previous query and will not benefit from the cache. To avoid this,
the query should use functions that return consistent values for the same day, such as
CURRENT_DATE, which returns the current date without the time component2. Option A is incorrect
because wrapping the query in a UDF does not guarantee the syntax match, as the UDF may also
contain dynamic functions. Option B is incorrect because the virtual warehouse does not affect the
persisted query results, which are stored at the account level1. Option C is incorrect because the
security role does not affect the persisted query results, as long as the role has the necessary
privileges to access the tables and views used in the query1.
1: Using Persisted Query Results | Snowflake Documentation 2: Date and Time Functions | Snowflake
Documentation
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.