[Jan-2024] Pass Snowflake ARA-C01 Exam in First Attempt Guaranteed!
Full ARA-C01 Practice Test and 65 unique questions with explanations waiting just for you, get it now!
NEW QUESTION # 14
An Architect runs the following SQL query:
How can this query be interpreted?
- A. FILEROWS is a stage. FILE_ROW_NUMBER is line number in file.
- B. FILEROWS is the table. FILE_ROW_NUMBER is the line number in the table.
- C. FILERONS is the file format location. FILE_ROW_NUMBER is a stage.
- D. FILEROWS is a file. FILE_ROW_NUMBER is the file format location.
Answer: A
NEW QUESTION # 15
It is recommended to assign ACCOUNTADMIN role to atleast two user
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 16
Which command below will load data from result_scan to a table?
- A. CREATE OR REPLACE TABLE STORE_FROM_RESULT_SCAN AS select * from result_scan(last_query_id());
- B. INSERT INTO STORE_FROM_RESULT_SCAN select * from result_scan(last_query_id());
- C. CREATE OR REPLACE TABLE STORE_FROM_RESULT_SCAN AS select * from table(result_scan(last_query_id()));
Answer: C
NEW QUESTION # 17
By executing the 'SHOW TABLES' command, we can list all the tables in all the schemas even if we do not have access to all the tables
- A. TRUE
- B. FALSE
Answer: B
NEW QUESTION # 18
Data loading transformation as part of copying data to a table from stage supports selecting data from user stage and named stages(internal and external) only
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 19
What is a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka?
- A. The Connector creates and manages its own stage, file format, and pipe objects.
- B. Loads using the Connector will have lower latency than Snowpipe and will ingest data in real time.
- C. The Connector only works in Snowflake regions that use AWS infrastructure.
- D. The Connector works with all file formats, including text, JSON, Avro, Ore, Parquet, and XML.
Answer: A
Explanation:
Explanation
According to the SnowPro Advanced: Architect documents and learning resources, a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka is that the Connector creates and manages its own stage, file format, and pipe objects. The stage is an internal stage that is used to store the data files from the Kafka topics. The file format is a JSON or Avro file format that is used to parse the data files. The pipe is a Snowpipe object that is used to load the data files into the Snowflake table. The Connector automatically creates and configures these objects based on the Kafka configuration properties, and handles the cleanup and maintenance of these objects1.
The other options are incorrect because they are not characteristics of loading data into Snowflake using the Snowflake Connector for Kafka. Option A is incorrect because the Connector works in Snowflake regions that use any cloud infrastructure, not just AWS. The Connector supports AWS, Azure, and Google Cloud platforms, and can load data across different regions and cloud platforms using data replication2. Option B is incorrect because the Connector does not work with all file formats, only JSON and Avro. The Connector expects the data in the Kafka topics to be in JSON or Avro format, and parses the data accordingly. Other file formats, such as text, ORC, Parquet, or XML, are not supported by the Connector3. Option D is incorrect because loads using the Connector do not have lower latency than Snowpipe, and do not ingest data in real time. The Connector uses Snowpipe to load data into Snowflake, and inherits the same latency and performance characteristics of Snowpipe. The Connector does not provide real-time ingestion, but near real-time ingestion, depending on the frequency and size of the data files4. References: Installing and Configuring the Kafka Connector | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation, Overview of the Kafka Connector | Snowflake Documentation, Using Snowflake Connector for Kafka With Snowpipe Streaming | Snowflake Documentation
NEW QUESTION # 20
You have turned on auto clustering in your table.
How will you monitor how much credit is getting burnt for the auto clustering in the past week for a specific table?
- A. Run the below query select * from table(information_schema.automatic_clustering_history(date_range_start=>dateadd(d, -7, current_date), date_range_end=>current_date, table_name=>'mydb.myschema.mytable'));
- B. SHOW AUTO CLUSTERING INFO
- C. SHOW CLUSTERING INFO
Answer: A
NEW QUESTION # 21
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. C3, C4, C5
- B. C5, C4, C2
- C. C2, C1, C3
- D. C1, C3, C2
Answer: D
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 # 22
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.)
- A. Use Dynamic Data Masking.
- B. Use theBusiness-Criticaledition of Snowflake.
- C. Define row access policies.
- D. Create a secure view.
- E. Use the Enterprise edition of Snowflake.
- F. Create a materialized view.
Answer: A,B,D
Explanation:
Explanation
These three design elements are required to meet the security, compliance, and governance requirements for the project.
* To use Tri-Secret Secure in Snowflake, the Business Critical edition of Snowflake is required. This edition provides enhanced data protection features, such as customer-managed encryption keys, that are not available in lower editions. Tri-Secret Secure is a feature that combines a Snowflake-maintained key and a customer-managed key to create a composite master key to encrypt the data in Snowflake1.
* To share some information stored in a view with another Snowflake customer, a secure view is recommended. A secure view is a view that hides the underlying data and the view definition from unauthorized users. Only the owner of the view and the users who are granted the owner's role can see the view definition and the data in the base tables of the view2. A secure view can be shared with another Snowflake account using a data share3.
* To hide portions of sensitive information from some columns, Dynamic Data Masking can be used.
Dynamic Data Masking is a feature that allows applying masking policies to columns to selectively mask plain-text data at query time. Depending on the masking policy conditions and the user's role, the data can be fully or partially masked, or shown as plain-text4.
NEW QUESTION # 23
When a database gets cloned, what accesses are replicated
- A. All objects in the database and their child objects(schemas, tables etc)
- B. Only the data base object access
- C. No access gets replicated
- D. Only the child object access
Answer: A
NEW QUESTION # 24
What is the best practice to follow when calling the SNOWPIPE REST API loadHistoryScan
- A. Reading the last 10 minutes of history every 8 minutes
- B. Read the last 7 days of history every hour
- C. Read the last 24 hours of history every minute
Answer: A
NEW QUESTION # 25
You have a medium warehouse with auto suspend of 5 minutes. You ran a query on table#1. After 10 minutes, you ran a query that joins table#1 and table#2, but you see that the query did not use data
cache.
Why?
- A. The query that ran the second time is completely different from query that ran on table#1, hence it will not be able to use data cache
- B. Joins cannot use any type of cache
- C. When a warehouse is suspended, it may lose its data cache
Answer: C
NEW QUESTION # 26
Data sharing is supported only between provider and consumer accounts in same region
- A. TRUE
- B. FALSE
Answer: B
NEW QUESTION # 27
Following objects can be cloned in snowflake
- A. Transient table
- B. Temporary table
- C. Permanent table
- D. External tables
- E. Internal stages
Answer: A,B,C
NEW QUESTION # 28
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?
- A. 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 API. Then write the results to a Snowflake table and create a listing in the Snowflake Marketplace to make the data available to other companies. - B. 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.
- C. 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.
- D. 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.
Answer: B
Explanation:
Explanation
This design meets all the requirements for the data pipeline. Snowpipe is a feature that enables continuous data loading into Snowflake from object storage using event notifications. It is efficient, scalable, and serverless, meaning it does not require any infrastructure or maintenance from the user. Streams and tasks are features that enable automated data pipelines within Snowflake, using change data capture and scheduled execution.
They are also efficient, scalable, and serverless, and they simplify the data transformation process. External functions are functions that can invoke external services or APIs from within Snowflake. They can be used to integrate with Amazon Comprehend and perform sentiment analysis on the data. The results can be written back to a Snowflake table using standard SQL commands. Snowflake Marketplace is a platform that allows data providers to share data with data consumers across different accounts, regions, and cloud platforms. It is a secure and easy way to make data publicly available to other companies.
References:
* Snowpipe Overview | Snowflake Documentation
* Introduction to Data Pipelines | Snowflake Documentation
* External Functions Overview | Snowflake Documentation
* Snowflake Data Marketplace Overview | Snowflake Documentation
NEW QUESTION # 29
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?
- A. Create a pipeline to write shared data to a cloud storage location in the target cloud provider.
- B. 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.
- C. Setup data replication to the region and cloud platform where the consumer resides.
- D. Ensure that all views are persisted, as views cannot be shared across cloud platforms.
Answer: C
NEW QUESTION # 30
You have create a table in snowflake as below
CREATE TABLE EMPLOYEE(EMPLOYEE_NAME STRING, SALARY NUMBER); When you do a DESCRIBE TABLE EMPLOYEE, what will you see as the data type of EMPLOYEE_NAME?
- A. STRING
- B. VARCHAR
- C. VARCHAR(16777216)
- D. VARCHAR(10)
Answer: C
NEW QUESTION # 31
When using the Snowflake Connector for Kafka, what data formats are supported for the messages? (Choose two.)
- A. Parquet
- B. Avro
- C. XML
- D. CSV
- E. JSON
Answer: B,E
NEW QUESTION # 32
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?
- A. Create a pipeline to write shared data to a cloud storage location in the target cloud provider.
- B. 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.
- C. Setup data replication to the region and cloud platform where the consumer resides.
- D. Ensure that all views are persisted, as views cannot be shared across cloud platforms.
Answer: C
Explanation:
Explanation
According to the SnowPro Advanced: Architect documents and learning resources, the requirement to allow data sharing between two companies that are not on the same cloud platform is to set up data replication to the region and cloud platform where the consumer resides. Data replication is a feature of Snowflake that enables copying databases across accounts in different regions and cloud platforms. Data replication allows data providers to securely share data with data consumers across different regions and cloud platforms by creating a replica database in the consumer's account. The replica database is read-only and automatically synchronized with the primary database in the provider's account. Data replication is useful for scenarios where data sharing is not possible or desirable due to latency, compliance, or security reasons1. The other options are incorrect because they are not required or feasible to allow data sharing between two companies that are not on the same cloud platform. Option A is incorrect because creating a pipeline to write shared data to a cloud storage location in the target cloud provider is not a secure or efficient way of sharing data. It would require additional steps to load the data from the cloud storage to the consumer's account, and it would not leverage the benefits of Snowflake's data sharing features. Option B is incorrect because ensuring that all views are persisted is not relevant for data sharing across cloud platforms. Views can be shared across cloud platforms as long as they reference objects in the same database. Persisting views is an option to improve the performance of querying views, but it is not required for data sharing2. Option D is incorrect because Company A and Company B do not need to agree to use a single cloud platform. Data sharing is possible across different cloud platforms using data replication or other methods, such as listings or auto-fulfillment3. References: ReplicatingDatabases Across Multiple Accounts | Snowflake Documentation, Persisting Views | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation
NEW QUESTION # 33
One of your query is taking a long time to finish, when you open the query profiler you see that lot of data is spilling to the remote disk(Bytes spilled to remote storage).
What may be the cause of this?
- A. The size of the AWS bucket used to hold the data is not sufficient for the query
- B. Number of disks attached to the virtual warehouse is not enough for the processing
- C. The amount of memory available for the servers used to execute the operation might not be sufficient to hold intermediate results
Answer: C
NEW QUESTION # 34
For the best performance while loading data into table from a stage, you should avoid applying patterns that filter on a large number of files.
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 35
What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)
- A. The MERGE command
- B. A STREAM object
- C. The CHANGES clause
- D. The UPSERT command
- E. TheCHANGE_DATA_CAPTURE command
Answer: B,C
Explanation:
Explanation
The built-in Snowflake features that make use of the change tracking metadata for a table are the CHANGES clause and a STREAM object. The CHANGES clause enables querying the change tracking metadata for a table or view within a specified interval of time without having to create a stream with an explicit transactional offset1. A STREAM object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data. This process is referred to as change data capture (CDC)2. The other options are incorrect because they do not make use of the change tracking metadata for a table. The MERGE command performs insert, update, or delete operations on a target table based on the results of a join with a source table3. The UPSERT command is not a valid Snowflake command. The CHANGE_DATA_CAPTURE command is not a valid Snowflake command. References: CHANGES | Snowflake Documentation, Change Tracking Using Table Streams | Snowflake Documentation, MERGE | Snowflake Documentation
NEW QUESTION # 36
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?
- A. 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 API. Then write the results to a Snowflake table and create a listing in the Snowflake Marketplace to make the data available to other companies.
- B. 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.
- C. 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.
- D. 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.
Answer: B
NEW QUESTION # 37
Snowflake has row level security
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 38
Which copy options are not supported by CREATE PIPE...AS COPY FROM command?
- A. VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS
- B. MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE
- C. FILES = ( 'file_name1' [ , 'file_name2', ... ] )
- D. ON_ERROR = ABORT_STATEMENT
- E. FORCE = TRUE | FALSE
Answer: A,B,C,D,E
NEW QUESTION # 39
......
Prepare for your Snowflake certification with the updated itPass4sure ARA-C01 exam questions: https://drive.google.com/open?id=1pc8NHIOlTovlWouULaWiPYp3Y-g4yl2S
Get Latest ARA-C01 Dumps Exam Questions in here: https://www.itpass4sure.com/ARA-C01-practice-exam.html
