Most Recent Snowflake ARA-R01 Exam Dumps

The Time Travel retention period for T1 will be 30 days, which is the retention period set at the table level. The Time Travel retention period determines how long the historical data is preserved and accessible for an object after it is modified or dropped. The Time Travel retention period can be set at the account level, the database level, the schema level, or the table level. The retention period set at the lowest level of the hierarchy takes precedence over the higher levels. Therefore, the retention period set at the table level overrides the retention periods set at the schema level, the database level, or the account level. When the user drops the database DB1, the table T1 is also dropped, but the historical data is still preserved for 30 days, which is the retention period set at the table level. The user can use the UNDROP command to restore the table T1 within the 30-day period. The other options are incorrect because:

10 days is the retention period set at the database level, which is overridden by the table level.

20 days is the retention period set at the schema level, which is also overridden by the table level.

37 days is not a valid option, as it is not the retention period set at any level.

Understanding & Using Time Travel

AT | BEFORE

Snowflake Time Travel & Fail-safe

To create a read-only role for certain employees working in the human resources department, the role needs to have the following permissions on the hr_db database:

USAGEon the database: This allows the role to access the database and see its schemas and objects.

USAGEon all schemas in the database: This allows the role to access the schemas and see their objects.

SELECTon all tables in the database: This allows the role to query the data in the tables.

Option A is the correct answer because it grants the minimum permissions required for a read-only role on the hr_db database.

Option B is incorrect because SELECT on schemas is not a valid permission. Schemas only support USAGE and CREATE permissions.

Option C is incorrect because MODIFY on the database is not a valid permission. Databases only support USAGE, CREATE, MONITOR, and OWNERSHIP permissions. Moreover, USAGE on tables is not sufficient for querying the data. Tables support SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and OWNERSHIP permissions.

Option D is incorrect because REFERENCES on tables is not relevant for querying the data. REFERENCES permission allows the role to create foreign key constraints on the tables.

: https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#database-privileges

: https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#schema-privileges

: https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#table-privileges

The SYSTEM$GET_PRIVATELINK function is used to retrieve the list of Snowflake service endpoints that need to be accessible when configuring private connectivity (such as AWS PrivateLink or Azure Private Link) for a Snowflake account. The function returns information necessary for setting up the networking infrastructure that allows secure and private access to Snowflake without using the public internet. SnowCD can then be used to verify connectivity to these endpoints.

The architecture in the image shows a Snowflake data platform with two databases, DB1 and DB2, and two schemas, SH1 and SH2. DB1 contains a table TBL1 and a stage STAGE1. DB2 contains a table TBL2. The image also shows a snippet of code written in SQL language that copies data from STAGE1 to TBL2 using a file format FF PIPE 1.

To copy data from DB1 to TBL2, there are two possible options among the choices given:

Option B: Use a named external stage that references STAGE1. This option requires creating an external stage object in DB2.SH2 that points to the same location as STAGE1 in DB1.SH1.The external stage can be created using theCREATE STAGEcommand with theURLparameter specifying the location of STAGE11. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

use database DB2;

use schema SH2;

create stage EXT_STAGE1

url = @DB1.SH1.STAGE1;

Then, the data can be copied from the external stage to TBL2 using theCOPY INTOcommand with theFROMparameter specifying the external stage name and theFILE FORMATparameter specifying the file format name2. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

copy into TBL2

from @EXT_STAGE1

file format = (format name = DB1.SH1.FF PIPE 1);

Option E: Use a cross-database query to select data from TBL1 and insert into TBL2. This option requires using theINSERT INTOcommand with theSELECTclause to query data from TBL1 in DB1.SH1 and insert it into TBL2 in DB2.SH2.The query must use the fully-qualified names of the tables, including the database and schema names3. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

use database DB2;

use schema SH2;

insert into TBL2

select * from DB1.SH1.TBL1;

The other options are not valid because:

Option A: It uses an invalid syntax for theCOPY INTOcommand.TheFROMparameter cannot specify a table name, only a stage name or a file location2.

Option C: It uses an invalid syntax for theCOPY INTOcommand.TheFILE FORMATparameter cannot specify a stage name, only a file format name or options2.

Option D: It uses an invalid syntax for theCREATE STAGEcommand.TheURLparameter cannot specify a table name, only a file location1.

1: CREATE STAGE | Snowflake Documentation

2: COPY INTO table | Snowflake Documentation

3: Cross-database Queries | Snowflake Documentation

According to the Snowflake documentation, tasks are objects that enable scheduling and execution of SQL statements or JavaScript user-defined functions (UDFs) in Snowflake. Tasks can be used to automate data loading, transformation, and maintenance operations. Snowflake scripting is a feature that allows writing procedural logic using SQL statements and JavaScript UDFs. Snowflake scripting can be used to create complex workflows and orchestrate tasks. Therefore, the best option to automate the daily import of two files from an external stage into Snowflake, join and aggregate the data, and produce a final result set is to create a task using Snowflake scripting that will import the files using the COPY INTO command, and then call a UDF to perform the join and aggregation logic. The UDF can return a table or a variant value as the final result set.Reference:

Tasks

Snowflake Scripting

User-Defined Functions