Improve Your Knowledge with SnowPro Advanced Architect Exam Dumps

Category:

Comments:

0 Comments

Post Date:

April 13, 2023

Practicing with SnowPro Advanced Architect questions can help you identify areas where you need to improve your knowledge. By answering SnowPro Advanced Architect questions and reviewing your responses, you can identify gaps in your understanding and focus your study efforts on those areas. The SnowPro Advanced Architect exam has a strict time limit, and you need to manage your time effectively to answer all the questions. Practicing with Snowflake SnowPro Advanced Architect dumps questions can help you develop time management skills by simulating the exam's time constraints. You'll learn how to pace yourself, manage your time effectively, and ensure that you complete the SnowPro Advanced Architect exam within the allotted time. Test Snowflake SnowPro Advanced Architect exam free dumps below.

Page 1 of 11

1. You have a table "ORG"."PUBLIC"."JS_DATA" The VARIANT column RAW_DATA with JSON has: { "extract_date": "20", "id": "10", "organizations": "Jade" } What would be the result of following query: SELECT RAW_DATA:ID FROM "ORG"."PUBLIC"."JS_DATA";

NULL

"20"

ERROR

"10"

2. If a server in warehouse cluster runs for 64 seconds, shuts down, and then restarts and runs for less than 60 seconds, for how many seconds it will be billed?

64 Seconds

124 Seconds

120 Seconds

4 Seconds

3. Which are the correct statements about TASKS?

Can not be triggered manually

it can be used with STREAMS

It is used for Change Data Capture (CDC)

TASKS is used to identify and act on changed table records

TASKS is used to scheduled SQL execution

4. Which command can give the list of all consumer accounts who have created database from share?

SHOW GRANTS OF DATABASE(*)

SHOW DATABASE OF SHARE

SHOW GRANTS OF SHARE

SELECT * FROM SHARE

5. Mike is having SECURITYADMIN role. He created a custom DBA_ROLE. He Granted SYSADMIN role to DBA_ROLE. He created a user 'Jessica'. Mike granted DBA_ROLE to Jessica. Jessica creates a Database Jessica_DB. Jessica then created a Table T1 in Jessica_DB under PUBLIC schema.

What should Mike do to access Table T1 created by Jessica?

GRANT ROLE DBA_ROLE TO Mike; USE DATABASE Jessica_db; Select * from t1;

USE ROLE SECURITYADMIN; USE DATABASE Jessica_db; Select * from t1;

USE ROLE dba_role; USE DATABASE Jessica_db; Select * from t1;

GRANT TOLE DBA_ROLE TO Mike; USE ROLE DBA_ROLE; USE DATABASE Jessica_db; Select * from t1;

6. Cross-Cloud and Cross-Region replication is type of:

Synchronous Replication

Asynchronous Replication

7. Mike wants to create a multi-cluster warehouse and wants to make sure that whenever new queries are queued, additional clusters should start immediately.

How should he configure the Warehouse?

Snowflake takes care of this automatically so, Mike does not have to worry about it

Set the SCALING POLICY as ECONOMY

Set the SCALING POLICY as STANDARD

Configure as SCALE-MAX so that the warehouse is always using maximum number of specified clusters

8. When a cloned table is replicated to a secondary database, the physical data is also replicated. (TRUE / FALES)

FALSE

TRUE

9. Mike wants to unload data from Snowflake in Single file.

Which SQL parameter with COPY INTO statement will help him getting unload in a single file?

MAX_NUMBER_OF _FILES = 0

MAX_NUMBER_OF _FILES = 1

MULTIPLE_FILES = TRUE

SINGLE = TRUE

10. Which of the following Snowflake Editions automatically store data in an encrypted state?

Standard

Business Critical

Enterprise

Page 2 of 11

11. When a cloned table is replicated to a secondary database, the physical data is also replicated. (TRUE / FALES)

FALSE

TRUE

12. A stream stores data in the same shape as the source table (i.e. the same column names and ordering)

with the following additional columns:

METADATA$ALTER

METADATA$INSERT

METADATA$ACTION

METADATA$ISUPDATE

METADATA$ROW_ID

13. When a cloned table is replicated to a secondary database, the physical data is also replicated. (TRUE / FALES)

FALSE

TRUE

14. Which tables will experience the most benefit from clustering?

Tables in the multi-terabyte (TB) range

Tables with sizes between the range of 100 MB to 1 GB compressed

Tables with sizes between the range of 1 GB to 10 GB compressed

All sizes of tables

15. How to choose the right size of warehouse to achieve the best results based on the Query processing?

Execute varieties of queries on same warehouse to achieve the best result

Execute relatively homogenous queries on the same warehouse

16. Which objects are securable objects in Snowflake?

Table

Database

File Format

Warehouse

17. Snowflake supports transforming data while loading it into a table using the COPY command.

What all options you have?

Column reordering

Column omission

Casts

String Truncation

Join

18. Snowflake calculates usage of data stored in the system for:

Clones of database tables that reference data deleted in the table that owns the clones

Cloud storage used in External Stage

Database tables, including historical data for Time Travel

Fail-safe for database tables

Files staged for bulk data loading/unloading

19. Which DML operations will result in re-clustering?

INSERT

COPY

DELETE

MERGE

UPDATE

20. Materialized views doesn't incur any costs. (TRUE / FALSE)

TRUE

FALSE

Page 3 of 11

21. What is the best practice after creating a custom role in a Snowflake account?

Grant PUBLIC to the role so all database objects owned by PUBLIC will be available to the new role

Grant the role to the USERADMIN role so users can be added to the role.

Grant ownership to the SYSADMIN role or a child role under SYSADMIN

Grant ownership of the role to itself so a member of the role can control access to the role

22. Please choose the correct Table Type from the given options.

TEMPORARY

PERMANENT

TRANSIENT

INTERNAL

EXTERNAL

23. When a cloned table is replicated to a secondary database, the physical data is also replicated. (TRUE / FALES)

TRUE

FALSE

24. What are the best strategies to select clustering keys?

Cluster columns that are most actively used in selective filters.

Avoid columns which are used in join predicates

Columns with timestamps in nano seconds

Consider columns frequently used in join predicates

25. What level of Privilege do you need to view Resource monitor?

OWNERSHIP

MONITOR

OPERATE

USAGE

26. Defining a clustering key directly on top of VARIANT columns is not supported. (TRUE / FALSE)

FALSE

TRUE

27. Jessica created table T1 and inserted 2 records with ids 1 and 2. Then she dropped the table T1. She again created table T1 and inserted records with ids 3 and 4. She again dropped the table T1. She again created table T1 and inserted records with ids 5 and 6. She again dropped the table T1. Now, she wants to get the table t1 back from the time-travel at the time when the table has records with ids 3 and 4.

What commands should she run?

UNDROP TABLE T1;

UNDROP TABLE T1; UNDROP TABLE T1;

UNDROP TABLE T1. DROP TABLE T1; UNDROP TABLE T1;

ALTER TABLE T1 RENAME TO T2; UNDROP TABLE T1;

UNDROP TABLE T1; ALTER TABLE T1 RENAME TO T2; UNDROP TABLE T1;

28. What is the default compression algorithm Snowflake apply while unloading data?

GZIP

GZIP3

BZ2

NONE

ZSTD

29. Which of the following Snowflake Editions encrypt all data transmitted over the network within a Virtual Private Cloud (VPC)?

Standard

Enterprise

Business Critical

30. What is the best use of SCALING OUT?

Better Performance

Better Concurrency

Page 4 of 11

31. How many files each server in a cluster can process in parallel?

1 File

Depends on how large is the file. If file is 32 MB in size, the server can process 4 such files in parallel

16 Files

4 Files

8 Files

32. User-Defined Function (UDF) supports

SQL

JavaScript

Java

Python

33. How many maximum columns (or expressions) are recommended for a cluster key?

3 to 4

12 to 16

7 to 8

Higher the number of columns (or expressions) in the key, better will be the performance

34. Jessica mistakenly dropped a table T1 last week. The database has Time-Travel retention period set to 90 days.

How can Jessica recover the table which she dropped last week.

Jessica can use the UI > ACCOUNT menu to recover the table T1

Jessica can execute UNDROP TABLE T1 command after setting up the right context for Database and schema

Jessica cant recover it from TIME-TRAVEL as the table T1 moved to Fail-safe

Jessica should contact Salesforce support to get it done

35. Secured view can be used to hide the definition but its performance can get degraded?

TRUE

FALSE

36. When unloading data into multiple files, use the MAX_FILE_SIZE copy option to specify the maximum size of each file created. (TRUE / FALSE)

FALSE

TRUE

37. By default System cancels long running queries after execution till specific duration. Select the correct duration.

2 Days

12 Hours

never

7 days

38. Please choose all the securable objects from the given options.

EXTERNAL TABLE

DATABASE

STORED PROCEDURE

SCHEMA

UDF

39. Semi-structured data types can be cast using what method?

() Column_name

Column_name CAST TO

Column_name::

Column_name AS

Column_name AS_

40. Mike has setup process to load specific set of files using both Bulk and Snowpipe. This is best practice to avoid any missed loading either by Bulk loading or Snowpipe. (TRUE /FALSE)

TRUE

FALSE

Page 5 of 11

41. External Stages require customers to have an account with a cloud storage service provider.

Which of the following are available currently or have been announced by Snowflake as under development?

AWS S3

MS Azure Blob

GCP Buckets

BOX

DROPBOX

42. Which of these must return a value?

Stored Procedure

User-Defined Function

Both

43. Clustering keys are not intended for all tables. (TRUE / FALSE)

TRUE

FALSE

44. Stored Procedure supports

Python

Java

JavaScript

SQL

45. Which objects can not be replicated?

Warehouses

Resource monitors

Users

Databases

Roles

46. Mike is having SECURITYADMIN role. He created a custom DBA_ROLE. He Granted SYSADMIN role to DBA_ROLE. He created a user 'Jessica'. Mike granted DBA_ROLE to Jessica. Jessica creates a Database Jessica_DB. Jessica then created a Table T1 in Jessica_DB under PUBLIC schema.

What should Mike do to access Table T1 created by Jessica?

GRANT TOLE DBA_ROLE TO Mike; USE ROLE DBA_ROLE; USE DATABASE Jessica_db; Select * from t1;

USE ROLE dba_role; USE DATABASE Jessica_db; Select * from t1;

USE ROLE SECURITYADMIN; USE DATABASE Jessica_db; Select * from t1;

GRANT ROLE DBA_ROLE TO Mike; USE DATABASE Jessica_db; Select * from t1;

47. When a warehouse is resized, which queries make use of the new size?

Only subsequent queries

Both current and subsequent queries

Only currently running queries

48. What is Clustering Depth?

It is the total number of micro-partitions that comprise the table

It can be used to determine whether a large table would benefit from explicitly defining a clustering key

The depth of the overlapping micro-partitions

The bigger the average depth, the better clustered the table

49. A Virtual Warehouse can't access data loaded in the table using different warehouse.

TRUE

FALSE

50. Which types of stages are automatically available in Snowflake and do not need to be created or configured?

Named Internal

User

Named External

Table

Page 6 of 11

51. Some compute occurs in the cloud services layer. When customer is charged for compute which occurred in the cloud services layer?

Customers are charged for cloud computing that exceeds 10% of total compute costs for the account

Customers are charged for cloud computing that exceeds 10% of total storage costs for the account

Customers are charged for cloud computing that exceeds 50% of total compute costs for the account

There is no charge to customer for cloud services layer

52. Dynamic Data Masking is supported by:

Standard Edition

Business Critical

Enterprise Edition

VPS

53. Which of the following Snowflake Editions automatically store data in an encrypted state?

Standard

Enterprise

Business Critical

54. How often does Snowflake release new features?

Weekly

Yearly

Bi-Weekly

Monthly

55. Which view does behave like a table and get auto-refreshed?

None

Materialized

Secured

Standard

56. You have a table "ORG"."PUBLIC"."JS_DATA" The VARIANT column RAW_DATA with JSON has : { "extract_date": "20", "id": "10", "organizations": "Jade" }

What would be the result of following query: SELECT RAW_DATA:ID FROM "ORG"."PUBLIC"."JS_DATA";

"10"

"20"

ERROR

NULL

57. Semi-structured data types can be cast using what method?

Column_name CAST TO

Column_name AS

Column_name AS_

() Column_name

Column_name::

58. Snowflake can query the data from External Tables. (TRUE / FALSE)

FALSE

TRUE

59. We loaded an XML file that included a header entity called . In order to bypass this entity and treat each EMPLOYEE object as a separate record (loading each into a separate row), what FILE_FORMAT property did we change?

DISABLE AUTO CONVERT

PRESERVE SPACE

STRIP OUTER ELEMENT

DISABLE SNOWFLAKE DATA

IGNORE UTF-8 ERROR

60. The BI group is complaining about their queries taking too long to run. Checking the virtual warehouse information shows the queued time is pretty high.

What is the best way to fix this issue?

Determine which users have the high priority queries and set the other users

Increase the virtual warehouse MAXCONCURRENCY_LEVEL parameter

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value to cancel those queries if they get in the queue

Increase the size of the virtual warehouse

Increase the virtual warehouse MAX_CLUSTER_COUNT property

Provide a virtual warehouse for every user in the group

Page 7 of 11

61. What are the three Snowflake Stage types? (Select 3)

Table

Secure

Materialized

Permanent

Named

User

62. Mike has table T1 with Time-Travel retention time period set to 20 days. He increases the retention period by 10 days to make it 30 days.

What impacts will happen on Table data. Please select 2.

No impact on any data that is 20 days older and has already moved into Fail-safe

Data that have moved to Fail-safe after 20 days will now be available in Time-Travel for additional 10 days

No impact on existing data which moved from table to Time-Travel before the increase of Time-Travel retention period

Data that would have been removed after 20 days is now retained for an additional 10 days before moving into Fail-safe

Changes will be ONLY effective for new data coming to Time-Travel

63. What are the types of Caches? (Select 2)

History Cache

Storage Cache

Metadata Cache

Results Cache

64. Each time the persisted result for a query is reused, Snowflake resets the 24-hour retention period for the result, up to a maximum of 31 days from the date and time that the query was first executed. (TRUE / FALSE)

FALSE

TRUE

65. Snowflake provides standard and powerful features that ensure the highest levels of security for your account and users if used properly.

Which are the true statements about Snowflake Security?

Tri-secret requires that customers manage their own keys

Snowflake supports user-based access control

Federated authentication in Snowflake is compliant with SAML 2.0

66. Select the objects which can be replicated from one region to other region.

Transient Table

Views

Temporary Table

Sequences

External Tables

Permanent Table

67. Which are the correct statements about TASKS?

It is used for Change Data Capture (CDC)

TASKS is used to scheduled SQL execution

Can not be triggered manually

TASKS is used to identify and act on changed table records

it can be used with STREAMS

68. Mike ran a query which took around 30 mins. He referred to Query profiler, and found the 'Bytes spilled to local storage' has big number.

What could be the issue?

Mike should contact Snowflake Personnel

Mike is using comparatively smaller warehouse

Mike is using very large warehouse

Warehouse size has no impact on Bytes spilling

69. What is the best practice after creating a custom role in a Snowflake account?

Grant ownership to the SYSADMIN role or a child role under SYSADMIN

Grant ownership of the role to itself so a member of the role can control access to the role

Grant the role to the USERADMIN role so users can be added to the role.

Grant PUBLIC to the role so all database objects owned by PUBLIC will be available to the new role

70. Select the objects which can be replicated from one region to other region.

Views

Transient Table

Permanent Table

Sequences

External Tables

Temporary Table

Page 8 of 11

71. At what places can you specify File Format?

In the COPY INTO command

All of these

As part of a named stage or pipe definition

As part of Internal Table stage

As part of a table definition

72. How can you store Result Scan into a table?

CREATE OR REPLACE TABLE MY_RESULT RESULT_SCAN(last_query_id()));

CREATE OR REPLACE TABLE MY_RESULT FROM table (RESULT_SCAN(last_query_id()));

CREATE OR REPLACE TABLE MY_RESULT FROM table (RESULT_SCAN(last_query_id(-1)));

CREATE OR REPLACE TABLE MY_RESULT AS (RESULT_SCAN(last_query_id()));

CREATE OR REPLACE TABLE MY_RESULT AS SELECT * FROM table (RESULT_SCAN(last_query_id()));

73. Who pays for the compute usage of the Reader account?

Consumer

Provider

74. Snowflake supports many methods of authentication.

Which are the supported authentication methods in ALL Snowflake Editions?

SSO

OAuth

MFA (Multi-factor authentication)

Only MFA is supported by all the Snowflake editions

Only MFA and SSO are supported by all the Snowflake editions

75. Mike has table T1 with Time-Travel retention time period set to 20 days. He increases the retention period by 10 days to make it 30 days.

What impacts will happen on Table data. Please select 2.

Changes will be ONLY effective for new data coming to Time-Travel

No impact on existing data which moved from table to Time-Travel before the increase of Time-Travel retention period

No impact on any data that is 20 days older and has already moved into Fail-safe

Data that have moved to Fail-safe after 20 days will now be available in Time-Travel for additional 10 days

Data that would have been removed after 20 days is now retained for an additional 10 days before moving into Fail-safe

76. What is the best recommended size of data file in case of SNOWPIPE continuous loading?

1 GB Compressed

Same as of Bulk Loading (10 MB - 100 MB uncompressed)

if file taking more than a minute, then split the files into more files

Same as of Bulk Loading (10 MB - 100 MB compressed)

77. Cross-Cloud and Cross-Region replication is type of:

Synchronous Replication

Asynchronous Replication

78. A stream stores data in the same shape as the source table (i.e. the same column names and ordering) with the following additional columns:

METADATA$ISUPDATE

METADATA$INSERT

METADATA$ALTER

METADATA$ACTION

METADATA$ROW_ID

79. Jessica has a EMPLOYEE_DATA table. Jessica wants to create another table

EMPLOYEE_DATA_OTHER which should be same as EMPLOYEE_DATA table with same data.

What is the best option for Jessica?

Create the table with LIKE SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER LIKE EMPLOYEE_DATA;

CREATE SHARE EMPLOYEE_DATA;

Create the table with same data with SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER AS SELECT * FROM EMPLOYEE_DATA;

Clone the table with same data with SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER CLONE EMPLOYEE_DATA;

80. Cross-Cloud and Cross-Region replication is type of:

Synchronous Replication

Asynchronous Replication

Page 9 of 11

81. Which of the following are Snowflake view types? (Select 3)

Transient

Secure

Standard

Permanent

Materialized

82. Stored Procedure supports

Java

Python

JavaScript

SQL

83. Snowflake supports many methods of authentication.

Which are the supported authentication methods in ALL Snowflake Editions?

SSO

MFA (Multi-factor authentication)

OAuth

Only MFA is supported by all the Snowflake editions

Only MFA and SSO are supported by all the Snowflake editions

84. What is the recommendation for file size for Parquet files for loading?

3 GB

16 MB

2 GB

1 GB

85. Snowflake offers rich set of drivers and connectors to connect with external applications. Select the Connectors from the list. (Choose 3)

Kafka

Node.js

Python

Spark

JDBC

86. You set up a Snowflake account, choosing AWS as your cloud platform provider.

What stages can you use to load data files? (Check all that apply)

NAMED EXTERNAL - using Azure BLOB storage

NAMED EXTERNAL - using GCS/GCP Buckets

NAMED EXTERNAL - using S3 Buckets

TABLE

NAMED INTERNAL

USER

87. If you find a data-related tool that is not listed as part of the Snowflake ecosystem, what industry standard options could you check for as a way to easily connect to Snowflake? (Select 2)

Check to see if the tool can connect to other solutions via JDBC

Check to see if the tool can connect to other solutions via ODBC

Check to see if there is a petition in the community to create a driver

Check to see if you can develop a driver and put it on GitHub

88. Which of these must return a value?

User-Defined Function

Stored Procedure

Both

89. SQL Clause which helps defining the clustering key:

CLUSERTING ON

CLUSTERING BY

CLUSTER ON

CLUSTER BY

90. What is Clustering Depth?

It can be used to determine whether a large table would benefit from explicitly defining a clustering key

The bigger the average depth, the better clustered the table

It is the total number of micro-partitions that comprise the table

The depth of the overlapping micro-partitions

Page 10 of 11

91. Which parameter does help in loading files whose metadata has expired?

set LOAD_UNCERTAIN_FILES to TRUE

set LAST_MODIFIED_DATE to within 64 days

set LOAD_EXPIRED_FILES to TRUE

Set LAST_MODIFIED_DATE to more than 64 days

92. By default System cancels long running queries after execution till specific duration. Select the correct duration.

never

12 Hours

2 Days

7 days

93. Mike has table T1 with Time-Travel retention time period set to 20 days. He decreases the retention period by 15 days to make it 5 days.

What impacts will happen on Table data. Please select 2.

No impact to existing Time Travel data. That will still complete the longer period of 20 days before going to Fail-safe

Changes will be ONLY effective for new data coming to Time-Travel

The data which was in Time Travel for more than 5 days will move to Fail-safe by Snowflake by background process

The data that is currently in Time Travel and if the data is still within the new shorter period, it remains in Time Travel

94. All data transparently & synchronously replicated across minimum 2 availability zones. (TRUE / FALSE)

FALSE

TRUE

95. Refreshing a secondary database is blocked for:

Materialized views

Databased created from shares

Transient Tables exists in the primary table

external table exists in the primary table

96. How often does Snowflake release new features?

Weekly

Yearly

Bi-Weekly

Monthly

97. FORCE option is used to load all files, ignoring load metadata if it exists. (TRUE / FALSE)

FALSE

TRUE

98. What are the best strategies to select clustering keys?

Avoid columns which are used in join predicates

Columns with timestamps in nano seconds

Cluster columns that are most actively used in selective filters.

Consider columns frequently used in join predicates

99. What are the features of Column-level security? (Select 2)

External Tokenization

Column Masking

Internal Tokenization

Dynamic Data Masking

100. Which of the following are Snowflake view types? (Select 3)

Secure

Standard

Materialized

Permanent

Transient

Page 11 of 11

101. What is default size of unload file?

32 MB

100 MB

16 MB

10 MB

102. SNOWPIPE (REST) method only works with Internal Stages. (TRUE/FALSE)

FALSE

TRUE

103. You set up a Snowflake account, choosing AWS as your cloud platform provider.

What stages can you use to load data files? (Check all that apply)

USER

NAMED EXTERNAL - using S3 Buckets

NAMED INTERNAL

NAMED EXTERNAL - using Azure BLOB storage

TABLE

NAMED EXTERNAL - using GCS/GCP Buckets

104. Dynamic Data Masking is supported by:

Business Critical

Standard Edition

VPS

Enterprise Edition

105. Please choose all the securable objects from the given options.

SCHEMA

STORED PROCEDURE

EXTERNAL TABLE

DATABASE

UDF

106. What all file formats are supported by Snowflake for unloading data from Snowflake?

XML

AVRO

CSV, TSV

PARQUET

JSON

107. User-Defined Function (UDF) supports

SQL

Python

Java

JavaScript

108. Running query against INFORMATION_SCHEMA.QUERY_HISTORY can help in finding the Size of Warehouse used in Query. (TRUE/FALSE)

FALSE

TRUE

TAGS:

SnowPro Advanced Architect, SnowPro Advanced Architect exam dumps

Notify of

Label

Name*

Email*

Website

Label

Name*

Email*

Website

0 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Posts

Snowflake Free Dumps

Get SnowPro Advanced Architect Dumps Full Version

Q&As: 65
Versions: PDF and Software Download Now

About Dumpsinfo

Dumpsinfo is a good platform providing the latest exam information and dumps questions for all IT certification exams. You can study all the latest exam dumps questions online.

[email protected]

Mon - Sat 9:00am - 6:00pm

Improve Your Knowledge with SnowPro Advanced Architect Exam Dumps

Related

Posts

Prepare ADA-C01 Exam with Using ADA-C01 Dump Questions

Valid DEA-C01 Exam Dumps are Your best Choice to Pass

Prepare DSA-C02 Exam with Using DSA-C02 Dump Questions

SnowPro Core Dumps Guarantee You Pass SnowPro Core Exam Easily

About Us

EMAIL

Services

Opening Hours