Snowflake SnowPro Advanced Architect ARA-C01 Exam Questions

dumps2024-12-02T14:20:49+08:00

SnowPro Advanced: Architect (ARA-C01) is available to validate advanced knowledge and skills to apply comprehensive architect solutions using Snowflake. IT students need to answer 65 questions in total to complete the SnowPro Advanced: Architect exam. The exam duration is 115 minutes. The passing score should be 750, while the scaled scoring is from 0 – 1000. Choosing ARA-C01 exam questions is a great way to prepare for SnowPro Advanced Architect Certification exam. You can read free ARA-C01 demo questions below first.

Page 1 of 7

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

The MERGE command

The UPSERT command

The CHANGES clause

A STREAM object

The CHANGE_DATA_CAPTURE command

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

CSV

XML

Avro

JSON

Parquet

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

Global

Database

Schema

Table

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

COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;

COPY INTO tablea FROM @%tablea;

COPY INTO tablea FROM @%tablea FILES = ('file5.csv');

COPY INTO tablea FROM @%tablea FORCE = TRUE;

COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;

COPY INTO tablea FROM @%tablea MERGE = TRUE;

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

Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.

Deploy a Private Data Exchange in combination with data shares for the European accounts.

Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.

Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

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

Unmasked data will be loaded in the new column.

Masked data will be loaded into the new column.

Unmasked data will be loaded into the new column but only users with the appropriate privileges will be able to see the unmasked data.

Unmasked data will be loaded into the new column and no users will be able to see the unmasked data.

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

Create multiple clustering keys for a table.

Create multiple materialized views with different cluster keys.

Create super projections that will automatically create clustering.

Create a clustering key that contains all columns used in the access paths.

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

Create a pipeline to write shared data to a cloud storage location in the target cloud provider.

Ensure that all views are persisted, as views cannot be shared across cloud platforms.

Setup data replication to the region and cloud platform where the consumer resides.

Company A and Company B must agree to use a single cloud platform: Data sharing is only possible if the companies share the same cloud provider.

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

Time Travel queries can be executed against the result set cache.

Snowflake persists the data results for 24 hours.

Each time persisted results for a query are used, a 24-hour retention period is reset.

The data stored in the result cache will contribute to storage costs.

The retention period can be reset for a maximum of 31 days.

The result set cache is not shared between warehouses.

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

Changing the name of the organization

Creating an account

Viewing a list of organization accounts

Changing the name of an account

Deleting an account

Enabling the replication of a database

Page 2 of 7

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

OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table

OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

CREATE on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

USAGE on the named pipe, named stage, target database, and schema, and INSERT and SELECT on the target table

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

Call the LOGIN_HISTORY Information Schema table function.

Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.

View the History tab in the Snowflake UI and set up a filter for SQL text that contains the text "LOGIN".

View the Users section in the Account tab in the Snowflake UI and review the last login column.

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

Assume role with OWNERSHIP on future tables and ADD SEARCH OPTIMIZATION on the SECURITY_LOGS schema.

Assume role with ALL PRIVILEGES including ADD SEARCH OPTIMIZATION in the SECURITY LOGS schema.

Assume role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.

Assume role with ALL PRIVILEGES on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.

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

C5, C4, C2

C3, C4, C5

C1, C3, C2

C2, C1, C3

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

Extended Time Travel (up to 90 days)

Customer-managed encryption keys through Tri-Secret Secure

Periodic rekeying of encrypted data

AWS, Azure, or Google Cloud private connectivity to Snowflake

Federated authentication and SSO

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

Set up replication to allow users to connect from outside the company VP

Provision a unique company Tri-Secret Secure key.

Use private connectivity from a cloud provider.

Set up SSO for federated authentication.

Use a proxy Snowflake account outside the VPN, enabling client redirect for user logins.

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

Shared databases are read-only.

Shared databases must be refreshed in order for new data to be visible.

Shared databases cannot be cloned.

Shared databases are not supported by Time Travel.

Shared databases will have the PUBLIC or INFORMATION_SCHEMA schemas without explicitly granting these schemas to the share.

Shared databases can also be created as transient databases.

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

Stage integration

Security integration

Storage integration

API integration

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

The warehouse MY_WH will be made active every five minutes to check the stream.

The warehouse MY_WH will only be active when there are results in the stream.

The warehouse MY_WH will never suspend.

The warehouse MY_WH will automatically resize to accommodate the size of the stream.

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

The Connector only works in Snowflake regions that use AWS infrastructure.

The Connector works with all file formats, including text, JSON, Avro, Ore, Parquet, and XM

The Connector creates and manages its own stage, file format, and pipe objects.

Loads using the Connector will have lower latency than Snowpipe and will ingest data in real time.

Page 3 of 7

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

The healthcare company will need to change the institute’s Snowflake edition in the accounts panel.

By default, sharing is supported from a Business Critical Snowflake edition to a Standard edition.

Contact Snowflake and they will execute the share request for the healthcare company.

Set the share_restriction parameter on the shared object to false.

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

Utilize a higher Buffer.flush.time in the connector configuration.

Utilize a higher Buffer.size.bytes in the connector configuration.

Utilize a lower Buffer.size.bytes in the connector configuration.

Utilize a lower Buffer.count.records in the connector configuration.

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

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

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

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

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

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

ACCOUNTADMIN, SECURITYADMIN

SYSADMIN, SECURITYADMIN

ACCOUNTADMIN, USER with PRIVILEGE

SECURITYADMIN, USER with PRIVILEGE

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

Ingest the data using COPY INTO and use streams and tasks to orchestrate transformations. Export the data into Amazon S3 to do model inference with Amazon Comprehend and ingest the data back into a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

Ingest the data using Snowpipe and use streams and tasks to orchestrate transformations. Create an external function to do model inference with Amazon Comprehend and write the final records to a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

Ingest the data into Snowflake using Amazon EMR and PySpark using the Snowflake Spark connector. Apply transformations using another Spark job. Develop a python program to do model inference by leveraging the Amazon Comprehend text analysis AP

Then write the results to a Snowflake table and create a listing in the Snowflake Marketplace to make the data available to other companies.

Ingest the data using Snowpipe and use streams and tasks to orchestrate transformations. Export the data into Amazon S3 to do model inference with Amazon Comprehend and ingest the data back into a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

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

Create accounts for each tenant in the Snowflake organization.

Create an object for each tenant strategy if row level security is viable for isolating tenants.

Create an object for each tenant strategy if row level security is not viable for isolating tenants.

Create a multi-tenant table strategy if row level security is not viable for isolating tenants.

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

They can include ORDER BY clauses.

They cannot include nested subqueries.

They can include context functions, such as CURRENT_TIME().

They can support MIN and MAX aggregates.

They can support inner joins, but not outer joins.

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

Consolidate data in the company’s data lake and use EXTERNAL TABLE

Create a raw database for landing and persisting raw data entering the data pipelines.

Create a set of profile-specific databases that aligns data with usage patterns.

Create a single star schema in a single database to support all consumers’ requirements.

Create a Data Vault as the sole data pipeline endpoint and have all consumers directly access the Vault.

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

Define row access policies.

Use the Business-Critical edition of Snowflake.

Create a secure view.

Use the Enterprise edition of Snowflake.

Use Dynamic Data Masking.

Create a materialized view.

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

An external table can be created with a row access policy, and the policy can be applied to the VALUE column.

A row access policy can be applied to the VALUE column of an existing external table.

A row access policy cannot be directly added to a virtual column of an external table.

External tables are supported as mapping tables in a row access policy.

While cloning a database, both the row access policy and the external table will be cloned.

A row access policy cannot be applied to a view created on top of an external table.

Page 4 of 7

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

IMPORT SHARE;

IMPORT PRIVILEGES;

CREATE DATABASE;

CREATE SHARE;

IMPORT DATABASE;

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

Use Snowpipe with auto-ingest.

Use a COPY command with a task.

Use a materialized view on an external table.

Use the COPY INTO command.

Use a combination of a task and a stream.

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

All rows loaded using a specific COPY statement will have varying timestamps based on when the rows were inserted.

Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were read from the source.

Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were created in the source.

All rows loaded using a specific COPY statement will have the same timestamp value.

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

It conserves credits by keeping running clusters fully loaded rather than starting additional clusters.

It starts only if the system estimates that there is a query load that will keep the cluster busy for at least 6 minutes.

It starts only f the system estimates that there is a query load that will keep the cluster busy for at least 2 minutes.

It prevents or minimizes queuing by starting additional clusters instead of conserving credits.

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

External table

Materialized view

Search optimization

Result cache

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

Search optimization service

Materialized view

Time Travel

Zero-copy cloning

Secure views

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

Alter the Azure security integration to use the Private Link URLs.

Add the IP address in the error message to the allowed list in the network policy.

Generate a new SCIM access token using system$generate_scim_access_token and save it to Azure A

Update the configuration of the Azure AD SSO to use the Private Link URLs.

Open a case with Snowflake Support to authorize the Private Link URLs’ access to the account.

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

Choose columns that are frequently used in join predicates.

Choose lower cardinality columns to support clustering keys and cost effectiveness.

Choose TIMESTAMP columns with nanoseconds for the highest number of unique rows.

Choose cluster columns that are most actively used in selective filters.

Choose cluster columns that are actively used in the GROUP BY clauses.

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

3NF

Star schema

Data Vault

Snowflake schema

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

A task scheduled in a UTC-based schedule will have no issues with the time changes.

Task schedules can be designed to follow specified or local time zones to accommodate the time changes.

A task will move to a suspended state during the daylight savings time change.

A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.

A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.

Page 5 of 7

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

Create a stored procedure that runs with caller’s rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMI

The ORDER_MANAGER role owns the procedure.

Create a stored procedure that can be run using both caller’s and owner’s rights (allowing the user to specify which rights are used during execution), and grant USAGE on this procedure to ORDER_ADMI

The ORDER_MANAGER role owns the procedure.

Create a stored procedure that runs with owner’s rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMI

The ORDER_MANAGER role owns the procedure.

This scenario would actually not be possible in Snowflake C any user performing a DELETE on a table requires the DELETE privilege to be granted to the role they are using.

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

Enable a multi-clustered virtual warehouse in maximized mode during the workload duration.

Set the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level.

Increase the size of the virtual warehouse to size X-Large.

Reduce the amount of data that is being processed through this workload.

Set the connection timeout to a higher value than its default.

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

SYSTEM$CLUSTERING_INFORMATION

SYSTEM$CLUSTERING_USAGE

SYSTEM$CLUSTERING_DEPTH

SYSTEM$CLUSTERING_KEYS

SYSTEM$CLUSTERING_PERCENT

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

CREATE CLONE TABLE Recover_Data FROM Data AT(OFFSET => -60*5);

CREATE CLONE Recover_Data FROM Data AT(OFFSET => -60*5);

CREATE TABLE Recover_Data CLONE Data AT(OFFSET => -60*5);

CREATE TABLE Recover Data CLONE Data AT(TIME => -60*5);

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

Clone a table from a share.

Grant modify permissions on the share.

Create a table from the shared database.

Create additional views inside the shared database.

Create a table stream on the shared table.

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

The company must have a Snowflake account in each cloud region to be able to ingest data to that account.

The company must replicate data between Snowflake accounts.

The company should provision a reader account to each site and ingest the data through the reader accounts.

The company should use a storage integration for the external stage.

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

Snowflake’s support of multi-table inserts into the data model’s Data Vault tables

Data needs to be pre-partitioned to obtain a superior data access performance

Scaling up the virtual warehouses will support parallel processing of new source loads

Snowflake’s ability to hash keys so that hash key joins can run faster than integer joins

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

1) OAuth (either Snowflake OAuth or External OAuth) 2) External browser 3) Okta native authentication 4) Key Pair Authentication, mostly used for service account users 5) Password

1) External browser, SSO 2) Key Pair Authentication, mostly used for development environment users 3) Okta native authentication 4) OAuth (ether Snowflake OAuth or External OAuth) 5) Password

1) Okta native authentication 2) Key Pair Authentication, mostly used for production environment users 3) Password 4) OAuth (either Snowflake OAuth or External OAuth) 5) External browser, SSO

1) Password 2) Key Pair Authentication, mostly used for production environment users 3) Okta native authentication 4) OAuth (either Snowflake OAuth or External OAuth) 5) External browser, SSO

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

Account --> Database --> Schema --> Warehouse

Organization --> Account --> Database --> Schema --> Stage

Account --> Schema > Table --> Stage

Organization --> Account --> Stage --> Table --> View

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

Session parameters override virtual warehouse parameters.

Virtual warehouse parameters override user parameters.

Table parameters override virtual warehouse parameters.

Schema parameters override account parameters.

Page 6 of 7

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

There needs to be fewer objects per tenant.

Security and Role-Based Access Control (RBAC) policies must be simple to configure.

Compute costs must be optimized.

Tenant data shape may be unique per tenant.

Storage costs must be optimized.

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

Undrop the STAGING schema and then rerun the CLONE statement.

Modify the statement: CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-05-01 10:00:00');

Rename the STAGING schema and perform an UNDROP to retrieve the previous STAGING schema version, then run the CLONE statement.

Cloning cannot be accomplished because the STAGING schema version was not active during the proposed Time Travel time period.

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

Control access to Snowflake data using a master encryption key that is maintained in the cloud provider’s key management service.

Store a generated identity and access management (IAM) entity for an external cloud provider regardless of the cloud provider that hosts the Snowflake account.

Support multiple external stages using one single Snowflake object.

Avoid supplying credentials when creating a stage or when loading or unloading data.

Create private VPC endpoints that allow direct, secure connectivity between VPCs without traversing the public internet.

Manage credentials from multiple cloud providers in one single Snowflake object.

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

Use, at minimum, the Business Critical edition of Snowflake.

Create Dynamic Data Masking policies and apply them to columns that contain PH

Use the Internal Tokenization feature to obfuscate sensitive data.

Use the External Tokenization feature to obfuscate sensitive data.

Rewrite SQL queries to eliminate projections of PHI data based on current_role().

Avoid sharing data with partner organizations.

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

USAGE on database hr_db, USAGE on all schemas in database hr_db, SELECT on all tables in database hr_db

USAGE on database hr_db, SELECT on all schemas in database hr_db, SELECT on all tables in database hr_db

MODIFY on database hr_db, USAGE on all schemas in database hr_db, USAGE on all tables in database hr_db

USAGE on database hr_db, USAGE on all schemas in database hr_db, REFERENCES on all tables in database hr_db

56. An Architect runs the following SQL query:

How can this query be interpreted?

FILEROWS is a stage. FILE_ROW_NUMBER is line number in file.

FILEROWS is the table. FILE_ROW_NUMBER is the line number in the table.

FILEROWS is a file. FILE_ROW_NUMBER is the file format location.

FILERONS is the file format location. FILE_ROW_NUMBER is a stage.

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

GRANT ROLE PUBLIC TO ROLE INTERN;

GRANT USAGE ON DATABASE SANDBOX TO ROLE INTERN;

GRANT USAGE ON SCHEMA SANDBO

PUBLIC TO ROLE INTERN;

GRANT OWNERSHIP ON DATABASE SANDBOX TO USER INTERN;

GRANT ALL PRIVILEGES ON DATABASE SANDBOX TO ROLE INTERN;

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

The staging schema has not been setup for MANAGED ACCES

The DATA_RETENTION_TIME_IN_DAYS for the staging schema has been set to 1 day.

The tables exceed the 1 TB limit for data recovery.

The staging tables are of the TRANSIENT type.

The DevOps role should be granted ALLOW_RECOVERY privilege on the staging schema.

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

The VALIDATION_MODE parameter supports COPY statements that load data from external stages only.

The VALIDATION_MODE parameter does not support COPY statements with CSV file formats.

The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.

The value return_all_errors of the option VALIDATION_MODE is causing a compilation error.

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

An external table

A pipe

A stream

A copy command at regular intervals

Page 7 of 7

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

Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA create a share of database MARKET_DB, create a new database out of this share locally in AWS us-east-1 region, and replicate this new database to AZABC123 account. Then set up data sharing to the PARTNERB account.

From account ACCOUNTA create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then make this database the provider and share it with the PARTNERB account.

Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA replicate the database MARKET_DB to AZABC123 and from this account set up the data sharing to the PARTNERB account.

Create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then replicate this database to the partner’s account PARTNER

62. 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

TRANSIENT

TEMPORARY

PERMANENT

63. Following objects can be cloned in snowflake

Permanent table

Transient table

Temporary table

External tables

Internal stages

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

clause?

CONTINUE

SKIP_FILE

ABORT_STATEMENT

FAIL

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

SHOW TABLES LIKE 'SNOWFL%';

SELECT MAX(FLAKE_ID) FROM SNOWFLAKE;

SELECT COUNT(*) FROM SNOWFLAKE;

SELECT COUNT(FLAKE_ID) FROM SNOWFLAKE GROUP BY FLAKE_ID;

66. What does a Snowflake Architect need to consider when implementing a Snowflake Connector for Kafka?

Every Kafka message is in JSON or Avro format.

The default retention time for Kafka topics is 14 days.

The Kafka connector supports key pair authentication, OAUT

and basic authentication (for example, username and password).

The Kafka connector will create one table and one pipe to ingest data for each topic. If the connector cannot create the table or the pipe it will result in an exception.

Snowflake SnowPro Advanced Architect ARA-C01 Exam Questions