Comprehensive and Updated ARA-C01 Exam Dumps V11.03 – Pass SnowPro Advanced Architect Certification Exam on the First Attempt

1. An Architect on a new project has been asked to design an architecture that meets

Snowflake security, compliance, and governance requirements as follows:

1) Use Tri-Secret Secure in Snowflake

2) Share some information stored in a view with another Snowflake customer

3) Hide portions of sensitive information from some columns

4) Use zero-copy cloning to refresh the non-production environment from the production environment

To meet these requirements, which design elements must be implemented? (Choose three.)

2. A user has the appropriate privilege to see unmasked data in a column.

If the user loads this column data into another column that does not have a masking policy, what will occur?

3. What are purposes for creating a storage integration? (Choose three.)

4. What are some of the characteristics of result set caches? (Choose three.)

5. Which Snowflake data modeling approach is designed for BI queries?

6. 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.)

7. What is a valid object hierarchy when building a Snowflake environment?

8. A company’s daily Snowflake workload consists of a huge number of concurrent queries triggered between 9pm and 11pm. At the individual level, these queries are smaller statements that get completed within a short time period.

What configuration can the company’s Architect implement to enhance the performance of this workload? (Choose two.)

9. An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects.

The STAGING schema has 50 days of retention.

The Architect runs the following statement:

CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-06-01 08:00:00');

The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time.

The Architect then checks the schema history and sees the following:

CREATED_ON|NAME|DROPPED_ON

2021-06-02 23:00:00 | STAGING | NULL

2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00

How can cloning the STAGING schema be achieved?

10. An Architect has a VPN_ACCESS_LOGS table in the SECURITY_LOGS schema containing timestamps of the connection and disconnection, username of the user, and summary statistics.

What should the Architect do to enable the Snowflake search optimization service on this table?

11. An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously.

Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)

12. A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto-ingest to Snowpipe.

What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?

13. The IT Security team has identified that there is an ongoing credential stuffing attack on many of their organization’s system.

What is the BEST way to find recent and ongoing login attempts to Snowflake?

14. An Architect would like to save quarter-end financial results for the previous six years.

Which Snowflake feature can the Architect use to accomplish this?

15. A company has a table with that has corrupted data, named Data. The company wants to recover the data as it was 5 minutes ago using cloning and Time Travel.

What command will accomplish this?

16. An Architect entered the following commands in sequence:

USER1 cannot find the table.

Which of the following commands does the Architect need to run for USER1 to find the tables using the Principle of Least Privilege? (Choose two.)

17. What is a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka?

18. 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?

19. A media company needs a data pipeline that will ingest customer review data into a Snowflake table, and apply some transformations. The company also needs to use Amazon Comprehend to do sentiment analysis and make the de-identified final data set available publicly for advertising companies who use different cloud providers in different regions.

The data pipeline needs to run continuously ang efficiently as new records arrive in the object storage leveraging event notifications. Also, the operational complexity, maintenance of the infrastructure, including platform upgrades and security, and the development effort should be minimal.

Which design will meet these requirements?

20. When using the Snowflake Connector for Kafka, what data formats are supported for the messages? (Choose two.)

21. At which object type level can the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges be granted?

22. What Snowflake features should be leveraged when modeling using Data Vault?

23. A company has several sites in different regions from which the company wants to ingest data.

Which of the following will enable this type of data ingestion?

24. Which system functions does Snowflake provide to monitor clustering information within a table (Choose two.)

25. Which of the following are characteristics of Snowflake’s parameter hierarchy?

26. The Data Engineering team at a large manufacturing company needs to engineer data coming from many sources to support a wide variety of use cases and data consumer requirements which include:

1) Finance and Vendor Management team members who require reporting and visualization

2) Data Science team members who require access to raw data for ML model development

3) Sales team members who require engineered and protected data for data monetization What Snowflake data modeling approaches will meet these requirements? (Choose two.)

27. A Snowflake Architect is designing an application and tenancy strategy for an organization where strong legal isolation rules as well as multi-tenancy are requirements.

Which approach will meet these requirements if Role-Based Access Policies (RBAC) is a viable option for isolating tenants?

28. An Architect is designing a pipeline to stream event data into Snowflake using the Snowflake Kafka connector. The Architect’s highest priority is to configure the connector to stream data in the MOST cost-effective manner.

Which of the following is recommended for optimizing the cost associated with the Snowflake Kafka connector?

29. 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?

30. A healthcare company is deploying a Snowflake account that may include Personal Health Information (PHI). The company must ensure compliance with all relevant privacy standards.

Which best practice recommendations will meet data protection and compliance requirements? (Choose three.)

31. There are two databases in an account, named fin_db and hr_db which contain payroll and employee data, respectively. Accountants and Analysts in the company require different permissions on the objects in these databases to perform their jobs. Accountants need read-write access to fin_db but only require read-only access to hr_db because the database is maintained by human resources personnel.

An Architect needs to create a read-only role for certain employees working in the human resources department.

Which permission sets must be granted to this role?

32. An Architect needs to allow a user to create a database from an inbound share.

To meet this requirement, the user’s role must have which privileges? (Choose two.)

33. What integration object should be used to place restrictions on where data may be exported?

34. A company’s client application supports multiple authentication methods, and is using Okta.

What is the best practice recommendation for the order of priority when applications authenticate to Snowflake?

35. How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

36. Which security, governance, and data protection features require, at a MINIMUM, the Business Critical edition of Snowflake? (Choose two.)

37. A user can change object parameters using which of the following roles?

38. Which statements describe characteristics of the use of materialized views in Snowflake? (Choose two.)

39. A healthcare company wants to share data with a medical institute. The institute is running a Standard edition of Snowflake; the healthcare company is running a Business Critical edition.

How can this data be shared?

40. A company is using a Snowflake account in Azure. The account has SAML SSO set up using ADFS as a SCIM identity provider.

To validate Private Link connectivity, an Architect performed the following steps:

* Confirmed Private Link URLs are working by logging in with a username/password account

* Verified DNS resolution by running nslookups against Private Link URLs

* Validated connectivity using SnowCD

* Disabled public access using a network policy set to use the company’s IP address range

However, the following error message is received when using SSO to log into the company account:

IP XX.XXX.XX.XX is not allowed to access snowflake. Contact your local security administrator.

What steps should the Architect take to resolve this error and ensure that the account is accessed using only Private Link? (Choose two.)

41. An Architect runs the following SQL query:

How can this query be interpreted?

42. An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group’s manager (ORDER_MANAGER) has full DELETE privileges on the table.

How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?

43. How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?

44. The following DDL command was used to create a task based on a stream:

Assuming MY_WH is set to auto_suspend C 60 and used exclusively for this task, which statement is true?

45. Which steps are recommended best practices for prioritizing cluster keys in Snowflake? (Choose two.)

46. A company has a Snowflake account named ACCOUNTA in AWS us-east-1 region. The company stores its marketing data in a Snowflake database named MARKET_DB. One of the company’s business partners has an account named PARTNERB in Azure East US 2 region. For marketing purposes the company has agreed to share the database MARKET_DB with the partner account.

Which of the following steps MUST be performed for the account PARTNERB to consume data from the MARKET_DB database?

47. Company A would like to share data in Snowflake with Company B. Company B is not on the same cloud platform as Company A.

What is required to allow data sharing between these two companies?

48. How does a standard virtual warehouse policy work in Snowflake?

49. When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME() or CURRENT_TIMESTAMP() what will occur?

50. A DevOps team has a requirement for recovery of staging tables used in a complex set of data pipelines. The staging tables are all located in the same staging schema. One of the requirements is to have online recovery of data on a rolling 7-day basis.

After setting up the DATA_RETENTION_TIME_IN_DAYS at the database level, certain tables remain unrecoverable past 1 day.

What would cause this to occur? (Choose two.)

51. 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?

52. How do Snowflake databases that are created from shares differ from standard databases that are not created from shares? (Choose three.)

53. A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors.

The company’s Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions.

According to Snowflake recommended best practice, how should these requirements be met?

54. A company wants to deploy its Snowflake accounts inside its corporate network with no visibility on the internet. The company is using a VPN infrastructure and Virtual Desktop Infrastructure (VDI) for its Snowflake users. The company also wants to re-use the login credentials set up for the VDI to eliminate redundancy when managing logins.

What Snowflake functionality should be used to meet these requirements? (Choose two.)

55. Files arrive in an external stage every 10 seconds from a proprietary system. The files range in size from 500 K to 3 MB. The data must be accessible by dashboards as soon as it arrives.

How can a Snowflake Architect meet this requirement with the LEAST amount of coding? (Choose two.)

56. A Snowflake Architect is designing a multi-tenant application strategy for an organization in the Snowflake Data Cloud and is considering using an Account Per Tenant strategy.

Which requirements will be addressed with this approach? (Choose two.)

57. A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider.

What is the MOST cost-effective way to bring this data into a Snowflake table?

58. Which feature provides the capability to define an alternate cluster key for a table with an existing cluster key?

59. Which organization-related tasks can be performed by the ORGADMIN role? (Choose three.)

60. What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)

61. Which of the following are characteristics of how row access policies can be applied to external tables? (Choose three.)

62. How do you refresh a materialized view?

63. Which alter command below may affect the availability of column with respect to time travel?

A. ALTER TABLE...DROP COLUMN

B. ALTER TABLE...SET DATA TYPE

C. ALTER TABLE...SET DEFAULT

64. Loading data using snowpipe REST API is supported for external stage only

65. Which copy options are not supported by CREATE PIPE...AS COPY FROM command?

66. Which command can be run to list all shares that have been created in your account or are available to consume by your account

67. Materialized views based on external tables can improve query performance

68. You have created a table as below

CREATE TABLE SNOWFLAKE (FLAKE_ID INTEGER, UDEMY_COURSE VARCHAR);

Which of the below select query will fail for this table?

69. With default settings, how long will a query run on snowflake

A. Snowflake will cancel the query if it runs more than 48 hours

B. Snowflake will cancel the query if it runs more than 24 hours

C. Snowflake will cancel the query if the warehouse runs out of memory

D. Snowflake will cancel the query if the warehouse runs out of memory and hard disk storage

70. With default settings for multi cluster warehouse, how does snowflake determines when to start a new cluster?

71. Which of the below commands will use warehouse credits?

72. Where can you define the file format settings?

73. Which command below will load data from result_scan to a table?

74. Which command below will only copy the table structure from the existing table to the new table?

75. When loading data from stage using COPY INTO, what options can you specify for the ON_ERROR clause?

76. A user needs access to create materialized view on a shema mydb.myschema.

What is the appropriate command to provide the access?

77. The kafka connector creates one pipe for each partition in a Kafka topic.

78. Secure views cannot take advantage of the internal optimizations which require access to the underlying data in the base tables for the view.

79. You have created a table as below

CREATE TABLE TEST_01 (NAME STRING(10));

What data type SNOWFLAKE will assign to column NAME?

80. Snowflake has row level security

81. To convert JSON null value to SQL null value, you will use

82. Following objects can be cloned in snowflake

A. Permanent table

B. Transient table

C. Temporary table

D. External tables

E. Internal stages

83. What will the below query return

SELECT TOP 10 GRADES FROM STUDENT;

84. You need to choose a high cardinality column for the clustering key

85. Below are the rest APIs provided by Snowpipe

A. insertFiles

B. insertReport

C. loadData

86. Every Snowflake table loaded by the Kafka connector has a schema consisting of two VARIANT columns.

Which are those?

87. Who can provide permission to EXECUTE TASK?

88. You have created a TASK in snowflake.

How will you resume it?

89. What will happen if you try to ALTER a COLUMN(which has NULL values) to set it to NOT NULL

90. While choosing a cluster key, what is recommended by snowflake?

91. You have create a task as below

CREATE TASK mytask1

WAREHOUSE = mywh

SCHEDULE = '5 minute'

WHEN

SYSTEM$STREAM_HAS_DATA('MYSTREAM')

AS

INSERT INTO mytable1(id,name) SELECT id, name FROM mystream WHERE METADATA$ACTION = 'INSERT';

Which statement is true below?

92. How do you validate the data that is unloaded using COPY INTO command

93. Which of the below operations are allowed on an inbound share data?

94. Data sharing is supported only between provider and consumer accounts in same region

95. When would you usually consider to add clustering key to a table

A. The performance of the query has deteriorated over a period of time.

B. The number of users querying the table has increased

C. it is a multi-terabyte size table

D. The table has more than 20 columns

96. You are a snowflake architect in an organization. The business team came to to deploy an use case which requires you to load some data which they can visualize through tableau. Everyday new data comes in and the old data is no longer required.

What type of table you will use in this case to optimize cost