Snowflake Snowflake Certifications ARA-C01 Questions & Answers

• Question 1:

  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

  A. TRANSIENT

  B. TEMPORARY

  C. PERMANENT

  Correct Answer: A

  A transient table is a type of table in Snowflake that does not have a Fail-safe period and can have a Time Travel retention period of either 0 or 1 day. Transient tables are suitable for temporary or intermediate data that can be easily reproduced or replicated1. A temporary table is a type of table in Snowflake that is automatically dropped when the session ends or the current user logs out. Temporary tables do not incur any storage costs, but they are not visible to other users or sessions2. A permanent table is a type of table in Snowflake that has a Fail-safe period and a Time Travel retention period of up to 90 days. Permanent tables are suitable for persistent and durable data that needs to be protected from accidental or malicious deletion3. In this case, the use case requires loading some data that can be visualized through Tableau. The data is updated every day and the old data is no longer required. Therefore, the best type of table to use in this case to optimize cost is a transient table, because it does not incur any Fail-safe costs and it can have a short Time Travel retention period of 0 or 1 day. This way, the data can be loaded and queried by Tableau, and then deleted or overwritten without incurring any unnecessary storage costs. References: : Transient Tables : Temporary Tables : Understanding and Using Time Travel

• Question 2:

  An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts.

  The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis.

  Which is the LEAST complex approach to use to populate the QA account with the Production account's data and database objects on a nightly basis?

  A. 1) Create a 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

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

  C. 1) Enable replication for each database in the Production account 2) Create replica databases in the QA account 3) Create clones of the replica databases on a nightly basis 4) Run tests directly on those cloned databases

  D. 1) 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

  Correct Answer: C

  This approach is the least complex because it uses Snowflake's built-in replication feature to copy the data and database objects from the Production account to the QA account. Replication is a fast and efficient way to synchronize data across accounts, regions, and cloud platforms. It also preserves the privileges and metadata of the replicated objects. By creating clones of the replica databases, the QA account can run tests on the cloned data without affecting the original data. Clones are also zero-copy, meaning they do not consume any additional storage space unless the data is modified. This approach does not require any external stages, tasks, Snowpipe, or external functions, which can add complexity and overhead to the data transfer process. References: Introduction to Replication and Failover Replicating Databases Across Multiple Accounts Cloning Considerations

• Question 3:

  How does a standard virtual warehouse policy work in Snowflake?

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

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

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

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

  Correct Answer: D

  Explanation: A standard virtual warehouse policy is one of the two scaling policies available for multi-cluster warehouses in Snowflake. The other policy is economic. A standard policy aims to prevent or minimize queuing by starting additional clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This policy can improve query performance and concurrency, but it may also consume more credits than an economic policy, which tries to conserve credits by keeping the running clusters fully loaded before starting additional clusters. The scaling policy can be set when creating or modifying a warehouse, and it can be changed at any time. References: Snowflake Documentation: Multi-cluster Warehouses Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses

• Question 4:

  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?

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

  B. The company must replicate data between Snowflake accounts.

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

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

  Correct Answer: D

  Explanation: This is the correct answer because it allows the company to ingest data from different regions using a storage integration for the external stage. A storage integration is a feature that enables secure and easy access to files in external cloud storage from Snowflake. A storage integration can be used to create an external stage, which is a named location that references the files in the external storage. An external stage can be used to load data into Snowflake tables using the COPY INTO command, or to unload data from Snowflake tables using the COPY INTO LOCATION command. A storage integration can support multiple regions and cloud platforms, as long as the external storage service is compatible with Snowflake12. References: Snowflake Documentation: Storage Integrations Snowflake Documentation: External Stages

• Question 5:

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

  

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

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

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

  C. The warehouse MY_WH will never suspend.

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

  Correct Answer: B

  Explanation: The warehouse MY_WH will only be active when there are results in the stream. This is because the task is created based on a stream, which means that the task will only be executed when there are new data in the stream.

  Additionally, the warehouse is set to auto_suspend - 60, which means that the warehouse will automatically suspend after 60 seconds of inactivity. Therefore, the warehouse will only be active when there are results in the stream. References:

  [CREATE TASK | Snowflake Documentation]

  [Using Streams and Tasks | Snowflake Documentation] [CREATE WAREHOUSE | Snowflake Documentation]

• Question 6:

  What Snowflake features should be leveraged when modeling using Data Vault? (Choose two.)

  A. Snowflake's support of multi-table inserts into the data model's Data Vault tables

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

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

  D. Snowflake's ability to hash keys so that hash key joins can run faster than integer joins

  Correct Answer: AC

  Explanation: These two features are relevant for modeling using Data Vault on Snowflake. Data Vault is a data modeling approach that organizes data into hubs, links, and satellites. Data Vault is designed to enable high scalability, flexibility, and performance for data integration and analytics. Snowflake is a cloud data platform that supports various data modeling techniques, including Data Vault. Snowflake provides some features that can enhance the Data Vault modeling, such as: Snowflake's support of multi-table inserts into the data model's Data Vault tables. Multi-table inserts (MTI) are a feature that allows inserting data from a single query into multiple tables in a single DML statement. MTI can improve the performance and efficiency of loading datainto Data Vault tables, especially for real-time or near-real-time data integration. MTI can also reduce the complexity and maintenance of the loading code, as well as the data duplication and latency12. Scaling up the virtual warehouses will support parallel processing of new source loads. Virtual warehouses are a feature that allows provisioning compute resources on demand for data processing. Virtual warehouses can be scaled up or down by changing the size of the warehouse, which determines the number of servers in the warehouse. Scaling up the virtual warehouses can improve the performance and concurrency of processing new source loads into Data Vault tables, especially for large or complex data sets. Scaling up the virtual warehouses can also leverage the parallelism and distribution of Snowflake's architecture, which can optimize the data loading and querying34. References: Snowflake Documentation: Multi-table Inserts Snowflake Blog: Tips for Optimizing the Data Vault Architecture on Snowflake Snowflake Documentation: Virtual Warehouses Snowflake Blog: Building a Real-Time Data Vault in Snowflake

• Question 7:

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

  A. CSV

  B. XML

  C. Avro

  D. JSON

  E. Parquet

  Correct Answer: CD

  Explanation: The data formats that are supported for the messages when using the Snowflake Connector for Kafka are Avro and JSON. These are the two formats that the connector can parse and convert into Snowflake table rows. The connector supports both schemaless and schematized JSON, as well as Avro with or without a schema registry1. The other options are incorrect because they are not supported data formats for the messages. CSV, XML, and Parquet are not formats that the connector can parse and convert into Snowflake table rows. If the messages are in these formats, the connector will load them as VARIANT data type and store them as raw strings in the table2. References: Snowflake Connector for Kafka | Snowflake Documentation, Loading Protobuf Data using the Snowflake Connector for Kafka | Snowflake Documentation

• Question 8:

  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. Define row access policies.

  B. Use theBusiness-Criticaledition of Snowflake.

  C. Create a secure view.

  D. Use the Enterprise edition of Snowflake.

  E. Use Dynamic Data Masking.

  F. Create a materialized view.

  Correct Answer: BCE

  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.

• Question 9:

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

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

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

  C. Generate a new SCIM access token using system$generate_scim_access_token and save it to Azure AD.

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

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

  Correct Answer: BD

  Explanation: The error message indicates that the IP address in the error message is not allowed to access Snowflake because it is not in the allowed list of the network policy. The network policy is a feature that allows restricting access to Snowflake based on IP addresses or ranges. To resolve this error, the Architect should take the following steps: Add the IP address in the error message to the allowed list in the network policy. This will allow the IP address to access Snowflake using the Private Link URLs. Alternatively, the Architect can disable the network policy if it is not required for security reasons. Update the configuration of the Azure AD SSO to use the Private Link URLs. This will ensure that the SSO authentication process uses the Private Link URLs instead of the public URLs. The configuration can be updated by following the steps in the Azure documentation1. These two steps should resolve the error and ensure that the account is accessed using only Private Link. The other options are not necessary or relevant for this scenario. Altering the Azure security integration to use the Private Link URLs is not required because the security integration is used for SCIM provisioning, not for SSO authentication. Generating a new SCIM access token using system$generate_scim_access_token and saving it to Azure AD is not required because the SCIM access token is used for SCIM provisioning, not for SSO authentication. Opening a case with Snowflake Support to authorize the Private Link URLs' access to the account is not required because the authorization can be done by the account administrator using the SYSTEM$AUTHORIZE_PRIVATELINK function2.

• Question 10:

  Which Snowflake data modeling approach is designed for BI queries?

  A. 3 NF

  B. Star schema

  C. Data Vault

  D. Snowflake schema

  Correct Answer: B

  Explanation: A star schema is a Snowflake data modeling approach that is designed for BI queries. A star schema is a type of dimensional modeling that organizes data into fact tables and dimension tables. A fact table contains the measures or metrics of the business process, such as sales amount, order quantity, or profit margin. A dimension table contains the attributes or descriptors of the business process, such as product name, customer name, or order date. A star schema is called so because it resembles a star, with one fact table in the center and multiple dimension tables radiating from it. A star schema can improve the performance and simplicity of BI queries by reducing the number of joins, providing fast access to aggregated data, and enabling intuitive query syntax. A star schema can also support various types of analysis, such as trend analysis, slice and dice, drill down, and roll up12. References: Snowflake Documentation: Dimensional Modeling Snowflake Documentation: Star Schema