Get all the Information About Snowflake ARA-C01 Exam 2023 Practice Test Questions
Check Real Snowflake ARA-C01 Exam Question for Free (2023)
NEW QUESTION # 36
Removing files from a stage after you are done loading the files improves performance when subsequently loading data
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 37
create or replace table result_scan_table as
select * from table(result_scan(last_query_id()));
Will the above query cost you any compute credits?
- A. It will depend on the warehouse chosen
- B. It will cost compute credit because processing is required to insert the data into result_scan_table
- C. It will not cost any compute credit because this query is a metadata operation
Answer: B
NEW QUESTION # 38
A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below:
Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses.
Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries.
The Architect must design a clustering key for this table to improve the query performance.
Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?
- A. C2, C1, C3
- B. C5, C4, C2
- C. C1, C3, C2
- D. C3, C4, C5
Answer: C
Explanation:
Explanation
According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1:
* Clustering is optimal when either:
* You require the fastest possible response times, regardless of cost.
* Your improved query performance offsets the credits required to cluster and maintain the table.
* Clustering is most effective when the clustering key is used in the following types of query predicates:
* Filter predicates (e.g. WHERE clauses)
* Join predicates (e.g. ON clauses)
* Grouping predicates (e.g. GROUP BY clauses)
* Sorting predicates (e.g. ORDER BY clauses)
* Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC, TO_CHAR, etc.).
* For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key.
Adding more than 3-4 columns tends to increase costs more than benefits.
Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because:
* These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering.
* These columns have high cardinality, which means they have many distinct values and can help reduce the clustering skew and improve the compression ratio.
* These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency.
* These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.
References: 1: Considerations for Choosing Clustering for a Table | Snowflake Documentation
NEW QUESTION # 39
Which of the below objects cannot be replicated?
- A. Roles
- B. Databases
- C. Users
- D. Shares
- E. Warehouses
- F. Resource Monitors
Answer: A,C,D,E,F
NEW QUESTION # 40
If you run the below commands in your worksheet, what will happen?
CREATE TEMP TABLE STUDENT_TABLE(STUID NUMBER, COURSE VARCHAR); CREATE TABLE
STUDENT_TABLE_CLONE
CLONE STUDENT_TABLE;
- A. A temporary table will be created with name as STUDENT_TABLE. The clone will create a clone of the STUDENT_TABLE
- B. SQL compilation error: Temp table cannot be cloned to a permanent table; clone to a transient table instead.
- C. You will get an error as below
- D. Only the STUDENT_TABLE_CLONE will be created, the other one will be deleted
Answer: B
NEW QUESTION # 41
select metadata$filename, metadata$file_row_number from @filestage/data1.json.gz;
Please select the correct statements for the above-mentioned query.
- A. FILESTAGE is the stage name, METADATA$FILE_ROW_NUMBER will give the path to the data file in the stage
- B. FILESTAGE is the stage name, METADATA$FILE_ROW_NUMBER will give the row number for each record in the container staged data file
- C. FILESTAGE is the file name, METADATA$FILE_ROW_NUMBER will give the path to the data file in the stage
Answer: B
NEW QUESTION # 42
A company has an inbound share set up with eight tables and five secure views. The company plans to make the share part of its production data pipelines.
Which actions can the company take with the inbound share? (Choose two.)
- A. Create additional views inside the shared database.
- B. Grant modify permissions on the share.
- C. Create a table from the shared database.
- D. Clone a table from a share.
- E. Create a table stream on the shared table.
Answer: C,E
NEW QUESTION # 43
Which organization-related tasks can be performed by the ORGADMIN role? (Choose three.)
- A. Enabling the replication of a database
- B. Viewing a list of organization accounts
- C. Changing the name of the organization
- D. Creating an account
- E. Changing the name of an account
- F. Deleting an account
Answer: A,B,D
Explanation:
Explanation
According to the SnowPro Advanced: Architect documents and learning resources, the organization-related tasks that can be performed by the ORGADMIN role are:
* Creating an account in the organization. A user with the ORGADMIN role can use the CREATE ACCOUNT command to create a new account that belongs to the same organization as the current account1.
* Viewing a list of organization accounts. A user with the ORGADMIN role can use the SHOW ORGANIZATION ACCOUNTS command to view the names and properties of all accounts in the organization2. Alternatively, the user can use the Admin a Accounts page in the web interface to view the organization name and account names3.
* Enabling the replication of a database. A user with the ORGADMIN role can use the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function to enable database replication for an account in the organization. This allows the user to replicate databases across accounts in different regions and cloud platforms for data availability and durability4.
The other options are incorrect because they are not organization-related tasks that can be performed by the ORGADMIN role. Option A is incorrect because changing the name of the organization is not a task that can be performed by the ORGADMIN role. To change the name of an organization, the user must contact Snowflake Support3. Option D is incorrect because changing the name of an account is not a task that can be performed by the ORGADMIN role. To change the name of an account, the user must contact Snowflake Support5. Option E is incorrect because deleting an account is not a task that can be performed by the ORGADMIN role. To delete an account, the user must contact Snowflake Support. References: CREATE ACCOUNT | Snowflake Documentation, SHOW ORGANIZATION ACCOUNTS | Snowflake Documentation, Getting Started with Organizations | Snowflake Documentation, SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER | Snowflake Documentation, ALTER ACCOUNT | Snowflake Documentation, [DROP ACCOUNT | Snowflake Documentation]
NEW QUESTION # 44
What will happen if you try to ALTER a COLUMN(which has NULL values) to set it to NOT NULL
- A. Snowflake drops the row and let the change happen
- B. Snowflake automatically assigns a default value and let the change happen
- C. An error is returned and no changes are applied to the column
Answer: C
NEW QUESTION # 45
Which of the two are limitations of the insertReport API of SnowPipe?
- A. Events are retained for a maximum of 24 hours
- B. The 10,000 most recent events are retained
- C. Events are retained for a maximum of 10 minutes
Answer: B,C
NEW QUESTION # 46
How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?
- A. Create multiple materialized views with different cluster keys.
- B. Create super projections that will automatically create clustering.
- C. Create a clustering key that contains all columns used in the access paths.
- D. Create multiple clustering keys for a table.
Answer: A
NEW QUESTION # 47
Which command do you run to remove files from stage?
- A. CLEAN
- B. REMOVE
- C. DELETE
- D. PURGE
Answer: B
NEW QUESTION # 48
You ran the below query. I have a warehouse with auto suspend set at 5 seconds
SELECT * FROM INVENTORY;
The query profile looks like as below. Please see below 'Percentage scanned from cache' is 0%
You ran the query again before 5 seconds has elapsed and the query profile looks as below. Look at the 'Percentage scanned for cache', it is 75%
You ran the query again after 5 seconds. The query profile looks as below. Look at the 'Percentage scanned from cache', it is zero again.
Why is this happening?
- A. The third run of the query used query result cache
- B. The second run of the query used data cache to retrieve part of the result since it ran before the warehouse was suspended
- C. The second run of the query used query result cache
Answer: B
NEW QUESTION # 49
What is the data size limit for loading into a variant column?
- A. 16 MB(Compressed)
- B. 32 GB
- C. 10 MB - 100 MB compressed
- D. 1 GB(Compressed)
Answer: A
NEW QUESTION # 50
Consider the following COPY command which is loading data with CSV format into a Snowflake table from an internal stage through a data transformation query.
This command results in the following error:
SQL compilation error: invalid parameter 'validation_mode'
Assuming the syntax is correct, what is the cause of this error?
- A. The VALIDATION_MODE parameter does not support COPY statements with CSV file formats.
- B. The value return_all_errors of the option VALIDATION_MODE is causing a compilation error.
- C. The VALIDATION_MODE parameter supports COPY statements that load data from external stages only.
- D. The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.
Answer: D
Explanation:
Explanation
* The VALIDATION_MODE parameter is used to specify the behavior of the COPY statement when loading data into a table. It is used to specify whether the COPY statement should return an error if any of the rows in the file are invalid or if it should continue loading the valid rows. The VALIDATION_MODE parameter is only supported for COPY statements that load data from external stages1.
* The query in the question uses a data transformation query to load data from an internal stage. A data transformation query is a query that transforms the data during the load process, such as parsing JSON or XML data, applying functions, or joining with other tables2.
* According to the documentation, VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error1.
Therefore, option C is the correct answer.
References: : COPY INTO <table> : Transforming Data During a Load
NEW QUESTION # 51
You have a need to make external file data available to your users with the lowest latency. The files are on an external stage in AWS.
What feature of Snowflake is the most appropriate to use
- A. Secure View
- B. Materialized View
- C. SnowPipe
Answer: C
NEW QUESTION # 52
An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts.
The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis.
Which is the LEAST complex approach to use to populate the QA account with the Production account's data and database objects on a nightly basis?
- A. 1) Create a stage in the Production account
2) Create a stage in the QA account that points to the same external object-storage location
3) Create a task that runs nightly to unload each table in the Production account into the stage
4) Use Snowpipe to populate the QA account - B. 1) In the Production account, create an external function that connects into the QA account and returns all the data for one specific table
2) Run the external function as part of a stored procedure that loops through each table in the Production account and populates each table in the QA account - C. 1) Enable replication for each database in the Production account
2) Create replica databases in the QA account
3) Create clones of the replica databases on a nightly basis
4) Run tests directly on those cloned databases - D. 1) Create a share in the Production account for each database
2) Share access to the QA account as a Consumer
3) The QA account creates a database directly from each share
4) Create clones of those databases on a nightly basis
5) Run tests directly on those cloned databases
Answer: D
NEW QUESTION # 53
Which copy options are not supported by CREATE PIPE...AS COPY FROM command?
- A. FORCE = TRUE | FALSE
- B. VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS
- C. ON_ERROR = ABORT_STATEMENT
- D. FILES = ( 'file_name1' [ , 'file_name2', ... ] )
- E. MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE
Answer: A,B,C,D,E
NEW QUESTION # 54
While choosing a cluster key, what is recommended by snowflake?
- A. Choose a key with high cardinality
- B. Cluster columns that are most actively used in selective filters
- C. If there is room for additional cluster keys, then consider columns frequently used in join predicates
Answer: B,C
NEW QUESTION # 55
You ran a query and the query
SELECT * FROM inventory WHERE BIBNUMBER = 2805127;
The query profile looks as below. If you would like to further tune the query, what is the best thing to do?
- A. Divide the table into multiple smaller tables
- B. Execute the below query to enable auto clustering
- C. Create an index on column BIBNUMBER
- D. alter table inventory cluster by (BIBNUMBER);
Answer: D
NEW QUESTION # 56
Who can provide permission to EXECUTE TASK?
- A. ACCOUNTADMIN
- B. SYSADMIN
- C. THE TASK OWNER
Answer: A
NEW QUESTION # 57
......
Use Free ARA-C01 Exam Questions that Stimulates Actual EXAM : https://passtorrent.testvalid.com/ARA-C01-valid-exam-test.html