Snowflake Snowflake Certifications ARA-C01 Questions & Answers

• Question 41:

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

  A. Consolidate data in the company's data lake and use EXTERNAL TABLES.

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

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

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

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

  Correct Answer: BC

  Explanation: These two approaches are recommended by Snowflake for data modeling in a data lake scenario. Creating a raw database allows the data engineering team to ingest data from various sources without any transformation or cleansing, preserving the original data quality and format. This enables the data science team to access the raw data for ML model development. Creating a set of profile-specific databases allows the data engineering team to apply different transformations and optimizations for different use cases and data consumer requirements. For example, the finance and vendor management team can access a dimensional database that supports reporting and visualization, while the sales team can access a secure database that supports data monetization. References: Snowflake Data Lake Architecture | Snowflake Documentation Snowflake Data Lake Best Practices | Snowflake Documentation

• Question 42:

  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?

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

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

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

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

  Correct Answer: A

  Explanation: The minimum value supported for the buffer.flush.time property is 1 (in seconds). For higher average data flow rates, we suggest that you decrease the default value for improved latency. If cost is a greater concern than latency, you could increase the buffer flush time. Be careful to flush the Kafka memory buffer before it becomes full to avoid out of memory exceptions.https://docs.snowflake.com/en/user-guide/data-load- snowpipe-streaming-kafka

• Question 43:

  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?

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

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

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

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

  Correct Answer: B

  Explanation: According to the SnowPro Advanced: Architect documents and learning resources, the minimum object privileges required for the Snowpipe user to execute Snowpipe are:

  OWNERSHIP on the named pipe. This privilege allows the Snowpipe user to create, modify, and drop the pipe object that defines the COPY statement for loading data from the stage to the table1.

  USAGE and READ on the named stage. These privileges allow the Snowpipe user to access and read the data files from the stage that are loaded by Snowpipe2. USAGE on the target database and schema. These privileges allow the

  Snowpipe user to access the database and schema that contain the target table3. INSERT and SELECT on the target table. These privileges allow the Snowpipe user to insert data into the table and select data from the table4. The other

  options are incorrect because they do not specify the minimum object privileges required for the Snowpipe user to execute Snowpipe. Option A is incorrect because it does not include the READ privilege on the named stage, which is required

  for the Snowpipe user to read the data files from the stage. Option C is incorrect because it does not include the OWNERSHIP privilege on the named pipe, which is required for the Snowpipe user to create, modify, and drop the pipe object.

  Option D is incorrect because it does not include the OWNERSHIP privilege on the named pipe or the READ privilege on the named stage, which are both required for the Snowpipe user to execute Snowpipe. References : CREATE PIPE |

  Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE DATABASE | Snowflake Documentation, CREATE TABLE | Snowflake Documentation

• Question 44:

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

  A. Clone a table from a share.

  B. Grant modify permissions on the share.

  C. Create a table from the shared database.

  D. Create additional views inside the shared database.

  E. Create a table stream on the shared table.

  Correct Answer: AD

  Explanation: These two actions are possible with an inbound share, according to the Snowflake documentation and the web search results. An inbound share is a share that is created by another Snowflake account (the provider) and imported into your account (the consumer). An inbound share allows you to access the data shared by the provider, but not to modify or delete it. However, you can perform some actions with the inbound share, such as: Clone a table from a share. You can create a copy of a table from an inbound share using the CREATE TABLE ... CLONE statement. The clone will contain the same data and metadata as the original table, but it will be independent of the share. You can modify or delete the clone as you wish, but it will not reflect any changes made to the original table by the provider1. Create additional views inside the shared database. You can create views on the tables or views from an inbound share using the CREATE VIEW statement. The views will be stored in the shared database, but they will be owned by your account. You can query the views as you would query any other view in your account, but you cannot modify or delete the underlying objects from the share2. The other actions listed are not possible with an inbound share, because they would require modifying the share or the shared objects, which are read-only for the consumer. You cannot grant modify permissions on the share, create a table from the shared database, or create a table stream on the shared table34. References: Cloning Objects from a Share | Snowflake Documentation Creating Views on Shared Data | Snowflake Documentation Importing Data from a Share | Snowflake Documentation Streams on Shared Tables | Snowflake Documentation

• Question 45:

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

  A. There needs to be fewer objects per tenant.

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

  C. Compute costs must be optimized.

  D. Tenant data shape may be unique per tenant.

  E. Storage costs must be optimized.

  Correct Answer: DE

  An Account Per Tenant strategy means creating a separate Snowflake account for each tenant (customer or business unit) of the multi-tenant application. This approach has some advantages and disadvantages compared to other strategies,

  such as Database Per Tenant or Schema Per Tenant. One advantage is that each tenant can have a unique data shape, meaning they can define their own tables, views, and other objects without affecting other tenants. This allows for more

  flexibility and customization for each tenant.

  Therefore, option D is correct.

  Another advantage is that storage costs can be optimized, because each tenant can use their own storage credits and manage their own data retention policies. This also reduces the risk of data spillover or cross-tenant access. Therefore,

  option E is correct.

  However, this approach also has some drawbacks, such as:

  References: : Multi-Tenant Application Strategies

• Question 46:

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

  A. Changing the name of the organization

  B. Creating an account

  C. Viewing a list of organization accounts

  D. Changing the name of an account

  E. Deleting an account

  F. Enabling the replication of a database

  Correct Answer: BCF

  Explanation: According to the SnowPro Advanced: Architect documents and learning resources, the organization-related tasks that can be performed by the ORGADMIN role are: Creating an account in the organization. A user with the ORGADMIN role can use the CREATE ACCOUNT command to create a new account that belongs to the same organization as the current account1. Viewing a list of organization accounts. A user with the ORGADMIN role can use the SHOW ORGANIZATION ACCOUNTS command to view the names and properties of all accounts in the organization2. Alternatively, the user can use the Admin ?Accounts page in the web interface to view the organization name and account names3. Enabling the replication of a database. A user with the ORGADMIN role can use the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function to enable database replication for an account in the organization. This allows the user to replicate databases across accounts in different regions and cloud platforms for data availability and durability4. The other options are incorrect because they are not organization-related tasks that can be performed by the ORGADMIN role. Option A is incorrect because changing the name of the organization is not a task that can be performed by the ORGADMIN role. To change the name of an organization, the user must contact Snowflake Support3. Option D is incorrect because changing the name of an account is not a task that can be performed by the ORGADMIN role. To change the name of an account, the user must contact Snowflake Support5. Option E is incorrect because deleting an account is not a task that can be performed by the ORGADMIN role. To delete an account, the user must contact Snowflake Support. References: CREATE ACCOUNT | Snowflake Documentation, SHOW ORGANIZATION ACCOUNTS | Snowflake Documentation, Getting Started with Organizations | Snowflake Documentation, SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER | Snowflake Documentation, ALTER ACCOUNT | Snowflake Documentation, [DROP ACCOUNT | Snowflake Documentation]

• Question 47:

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

  A. Stage integration

  B. Security integration

  C. Storage integration

  D. API integration

  Correct Answer: B

  Explanation: According to the SnowPro Advanced: Architect documents and learning resources, the integration object that should be used to place restrictions on where data may be exported is the security integration. A security integration is a Snowflake object that provides an interface between Snowflake and third-party security services, such as Okta, Duo, or Google Authenticator. A security integration can be used to enforce policies on data export, such as requiring multi-factor authentication (MFA) or restricting the export destination to a specific network or domain. A security integration can also be used to enable single sign-on (SSO) or federated authentication for Snowflake users1. The other options are incorrect because they are not integration objects that can be used to place restrictions on where data may be exported. Option A is incorrect because a stage integration is not a valid type of integration object in Snowflake. A stage is a Snowflake object that references a location where data files are stored, such as an internal stage, an external stage, or a named stage. A stage is not an integration object that provides an interface between Snowflake and third-party services2. Option C is incorrect because a storage integration is a Snowflake object that provides an interface between Snowflake and external cloud storage, such as Amazon S3, Azure Blob Storage, or Google Cloud Storage. A storage integration can be used to securely access data files from external cloud storage without exposing the credentials, but it cannot be used to place restrictions on where data may be exported3. Option D is incorrect because an API integration is a Snowflake object thatprovides an interface between Snowflake and third-party services that use REST APIs, such as Salesforce, Slack, or Twilio. An API integration can be used to securely call external REST APIs from Snowflake using the CALL_EXTERNAL_API function, but it cannot be used to place restrictions on where data may be exported4. References: CREATE SECURITY INTEGRATION | Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE STORAGE INTEGRATION | Snowflake Documentation, CREATE API INTEGRATION | Snowflake Documentation

• Question 48:

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

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

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

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

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

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

  Correct Answer: AB

  Explanation: These two configuration options can enhance the performance of the workload that consists of a huge number of concurrent queries that are smaller and faster. Enabling a multi-clustered virtual warehouse in maximized mode allows the warehouse to scale out automatically by adding more clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This can improve the concurrency and throughput of the workload by minimizing or preventing queuing. The maximized mode is suitable for workloads that require high performance and low latency, and are less sensitive to credit consumption1. Setting the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level allows the warehouse to run more queries concurrently on each cluster. This can improve the utilization and efficiency of the warehouse resources, especially for smaller and faster queries that do not require a lot of processing power. The MAX_CONCURRENCY_LEVEL parameter can be set when creating or modifying a warehouse, and it can be changed at any time2. References: Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses Snowflake Documentation: MAX_CONCURRENCY_LEVEL

• Question 49:

  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?

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

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

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

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

  Correct Answer: A

  To create a read-only role for certain employees working in the human resources department, the role needs to have the following permissions on the hr_db database: Option A is the correct answer because it grants the minimum permissions required for a read-only role on the hr_db database. Option B is incorrect because SELECT on schemas is not a valid permission. Schemas only support USAGE and CREATE permissions. Option C is incorrect because MODIFY on the database is not a valid permission. Databases only support USAGE, CREATE, MONITOR, and OWNERSHIP permissions. Moreover, USAGE on tables is not sufficient for querying the data. Tables support SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and OWNERSHIP permissions. Option D is incorrect because REFERENCES on tables is not relevant for querying the data. REFERENCES permission allows the role to create foreign key constraints on the tables. References: : https://docs.snowflake.com/en/user-guide/security-access-control- privileges.html#database-privileges : https://docs.snowflake.com/en/user-guide/security-access-control- privileges.html#schema-privileges : https://docs.snowflake.com/en/user-guide/security-access-control- privileges.html#table-privileges

• Question 50:

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

  A. Create multiple clustering keys for a table.

  B. Create multiple materialized views with different cluster keys.

  C. Create super projections that will automatically create clustering.

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

  Correct Answer: B

  Explanation: According to the SnowPro Advanced: Architect documents and learning resources, the best way to enable optimal clustering to enhance performance for different access paths on a given table is to create multiple materialized views with different cluster keys. A materialized view is a pre-computed result set that is derived from a query on one or more base tables. A materialized view can be clustered by specifying a clustering key, which is a subset of columns or expressions that determines how the data in the materialized view is co-located in micro-partitions. By creating multiple materialized views with different cluster keys, an Architect can optimize the performance of queries that use different access paths on the same base table. For example, if a base table has columns A, B, C, and D, and there are queries that filter on A and B, or on C and D, or on A and C, the Architect can create three materialized views, each with a different cluster key: (A, B), (C, D), and (A, C). This way, each query can leverage the optimal clustering of the corresponding materialized view and achieve faster scan efficiency and better compression. References: Snowflake Documentation: Materialized Views Snowflake Learning: Materialized Views https://www.snowflake.com/blog/using-materialized-views-to-solve-multi-clustering- performance-problems/