ARA-C01 Snowflake Architecture Practice Test 3 — 15 Questions

Question 1

A data engineering team runs an ELT pipeline that loads small batches of JSON files into a Snowflake table every 15 minutes. They notice that each load triggers an automatic reclustering of the table's micro-partitions, which increases credit consumption. They want to stop this automatic reclustering without disabling the table's ability to be manually reclustered later. What should they do?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Suspend the automatic clustering service by executing ALTER TABLE ... SUSPEND RECLUSTER.. Automatic reclustering can be paused without removing the clustering key by using the SUSPEND RECLUSTER clause. This preserves the table's clustering definition and allows manual reclustering later. The other options either destroy the clustering configuration, affect unrelated retention settings, or replace the table with an unsuitable type, none of which meet the requirement to stop automatic credits while keeping future manual reclustering possible.

Answer

Disable automatic clustering by setting the table's CLUSTER BY to NULL.

Answer

Set the table's DATA_RETENTION_TIME_IN_DAYS to 0 to prevent background maintenance.

Answer

Recreate the table as a transient table to disable automatic clustering.

Question 2

A Snowflake architect is designing a solution for a financial institution that requires near-zero recovery point objective (RPO) and recovery time objective (RTO) for a critical database. The architect plans to use database replication with failover to a secondary region. Which configuration should the architect implement to achieve the lowest possible RPO and RTO?

Accepted Answer

Enable replication with a refresh frequency of 1 minute and configure automatic failover.. The lowest RPO and RTO are achieved by replicating with a high frequency (1 minute) to minimize data loss and enabling automatic failover to minimize downtime. This ensures that the secondary region is nearly up-to-date and can take over quickly. Other options either have longer refresh intervals, require manual actions, or do not replicate data.

Answer

Enable replication with a refresh frequency of 10 minutes and configure manual failover.

Answer

Use Snowflake's Client Redirect feature to redirect clients to the secondary region without replication.

Answer

Configure a multi-cluster warehouse in the primary region with automatic scaling and enable cross-region data sharing.

Question 3

A Snowflake architect is designing a multi-cluster warehouse to handle fluctuating query workloads. The warehouse is configured with a minimum cluster count of 1 and a maximum of 4. The architect wants to ensure that queries are not queued when the workload spikes, but also wants to minimize credit consumption during idle periods. Which setting should the architect adjust to achieve this?

Accepted Answer

Set the warehouse scaling policy to STANDARD.. The STANDARD scaling policy is designed to minimize queuing by adding clusters as soon as queries are waiting, whereas ECONOMY waits longer to conserve credits. Since the requirement is to avoid queuing during spikes while minimizing idle costs, STANDARD with auto-suspend is appropriate. The maximum cluster count of 4 is already set, so adjusting the policy is key.

Answer

Set the warehouse to automatically suspend after 5 minutes of inactivity.

Answer

Enable the warehouse to scale out by setting the maximum cluster count to a higher value, such as 10.

Answer

Set the warehouse scaling policy to ECONOMY.

Question 4

A healthcare organization is deploying Snowflake in a regulated environment. They must ensure that all data access is audited and that they can monitor login attempts, query history, and data transfers. The architect needs to configure Snowflake to meet these compliance requirements. Which two Snowflake features should the architect use to capture and retain audit logs? (Choose two.)

Accepted Answer

Snowflake's ACCESS_HISTORY view in the ACCOUNT_USAGE schema.. ACCOUNT_USAGE views provide a comprehensive set of historical audit data, including login history, query history, and data access. ACCESS_HISTORY specifically tracks column-level access, which is crucial for compliance. Together, these features enable the organization to audit all data access and retain logs for up to 365 days.

Answer

Snowflake's LOGIN_HISTORY view in the ACCOUNT_USAGE schema.

Answer

INFORMATION_SCHEMA views.

Answer

Query Profile in the Snowflake web interface.

Question 5

An architect is designing a Snowflake solution that requires reading data from an external stage in Amazon S3. The security team wants to avoid storing AWS credentials in Snowflake and instead use an IAM role for authentication. Which two statements accurately describe the configuration required to enable Snowflake to access the S3 bucket using an IAM role? (Choose two.)

Accepted Answer

Configure the S3 bucket policy to grant access to the IAM role specified in the storage integration.. To use an IAM role for S3 access, you must create a storage integration in Snowflake and configure the S3 bucket policy to allow the IAM role. The storage integration holds the IAM role ARN and the bucket policy grants permissions. These two steps establish the trust and permissions needed without storing AWS credentials.

Answer

Define an external stage that references the storage integration and the S3 bucket path.

Answer

Create an IAM policy that allows the Snowflake IAM user to assume the IAM role, and attach it to the IAM user.

Answer

Generate an AWS access key and secret key for the IAM user created by Snowflake and store them in the storage integration.

Question 6

An architect is designing a Snowflake environment that must support near-zero recovery time objective (RTO) and recovery point objective (RPO) for a mission-critical application. The architect plans to use Snowflake's replication and failover capabilities. Which two statements are true regarding Snowflake's replication and failover features? (Choose two.)

Accepted Answer

Client redirect and connection failover can be configured to automatically redirect clients to the secondary account during a failover.. The two correct statements are that replication of databases and shares is supported across regions and cloud platforms with failover requiring Business Critical Edition or higher, and that client redirect and connection failover can automatically redirect clients to the secondary account. These directly address the architectural requirements for high availability and disaster recovery. The other options contain inaccuracies about default replication of external objects, automatic failover, or are less central to the failover capabilities.

Answer

Failover groups can contain multiple databases and shares, and they support automatic failover with no manual intervention.

Answer

Replication of databases includes replication of external tables and external stages by default.

Answer

Replication is asynchronous, and the secondary account can be queried for read-only access while replication is active.

Question 7

A company is migrating its on-premises data warehouse to Snowflake. The architect needs to choose a Snowflake virtual warehouse size that provides the best performance for a complex ETL job that processes large volumes of data. The ETL job runs nightly and can tolerate some variability in runtime. Which warehouse size should the architect select to optimize for cost and performance?

Accepted Answer

Large. For a nightly ETL job processing large data volumes, a Large warehouse offers a good balance of performance and cost. It provides more compute than Medium or X-Small, reducing runtime, while being less expensive than 4X-Large. The architect can monitor and adjust size based on actual performance.

Answer

X-Small

Answer

4X-Large

Answer

Medium

Question 8

Which statement accurately describes the characteristics of micro-partitions in Snowflake's architecture?

Accepted Answer

They are immutable files that are encrypted and stored in the storage layer.. Micro-partitions are the fundamental unit of storage in Snowflake. They are automatically created, immutable, and columnar in nature. This architecture enables Snowflake to perform efficient pruning and supports features like Time Travel and Zero-copy cloning. Understanding micro-partitions is vital for understanding how Snowflake achieves high performance without requiring manual indexing or partitioning strategies.

Answer

They are physical files that users must manually define using partition keys.

Answer

They use a row-based storage format to optimize for high-frequency OLTP writes.

Answer

They are stored in the Cloud Services layer to allow for faster metadata access.

Question 9

A database architect needs to design a high-churn table that receives millions of updates daily. To minimize storage costs associated with Fail-safe and Time Travel while maintaining some recovery capability, which table type and configuration should be used?

Accepted Answer

Transient table with Time Travel set to 1 day.. Transient tables are designed for data that is important but does not require the high level of protection provided by Fail-safe. They support Time Travel (up to 1 day) but do not have a Fail-safe period. For high-churn tables, this significantly reduces storage costs because Fail-safe records all changes for 7 days, which can be expensive with frequent updates.

Answer

Permanent table with Time Travel set to 0 days.

Answer

Temporary table with Time Travel set to 90 days.

Answer

Permanent table with a cluster key defined on the update timestamp.

Question 10

In Snowflake's architecture, what happens to the existing data in a table when a new Cluster Key is defined and the Automatic Clustering service is enabled?

Accepted Answer

Snowflake uses a serverless background service to incrementally re-cluster the data.. When a cluster key is defined, Snowflake does not immediately rewrite the table. Instead, the Automatic Clustering service, which is a serverless background task, identifies micro-partitions that are poorly clustered according to the new key. It then incrementally reorganizes the data by creating new micro-partitions and marking the old ones for deletion, all without impacting active workloads.

Answer

The table is immediately locked and the data is rewritten to match the new key.

Answer

The user must run the RECLUSTER command to trigger the data movement manually.

Answer

Existing data remains unchanged; only new data will be clustered using the new key.

Question 11

Which layer of the Snowflake architecture is responsible for ensuring ACID compliance and transactional integrity?

Accepted Answer

The Cloud Services Layer, which manages metadata and transaction state.. ACID compliance is managed by the Cloud Services layer. This layer handles transaction management, including locking at the table level and ensuring that all operations within a transaction either succeed completely or fail without impacting data integrity. This centralized management allows Snowflake to provide strong consistency across its distributed storage and compute layers.

Answer

The Storage Layer, which uses immutable files to prevent data corruption.

Answer

The Virtual Warehouse Layer, which executes the commit and rollback commands.

Answer

The Network Layer, which ensures that data packets are delivered without errors.

Question 12

A Data Provider wants to share data with a Consumer who does not have a Snowflake account. Which TWO architectural components are required to facilitate this type of sharing?

Accepted Answer

A Reader Account created and managed by the Data Provider.. To share data with a non-Snowflake user, a Data Provider must create a Reader Account. This is a managed Snowflake account created and paid for by the provider. The provider then uses a 'Share' object to grant access to specific database objects. This allows the consumer to query the shared data using a web interface or supported drivers without needing their own Snowflake contract.

Answer

A Private Data Exchange listing that is restricted to the consumer's IP.

Answer

A Storage Integration that allows the consumer to access the provider's S3 bucket.

Answer

A Secure View that masks the provider's metadata from the consumer.

Question 13

Refer to the exhibit. An architect needs to optimize this warehouse to handle a batch process that performs a massive table scan followed by a complex join on 10 billion rows. What is the most effective architectural change?

Accepted Answer

Change the size of the warehouse from X-SMALL to LARGE.. For a single, complex query involving a massive data set, the bottleneck is typically the compute power and memory available to the nodes. Increasing the size (vertical scaling) from X-Small to a larger size (e.g., Large or X-Large) provides more CPUs and memory per node. This allows the warehouse to process more data in parallel and reduces the need for spilling to local or remote disk.

Answer

Increase the max_cluster_count to 5 to enable multi-cluster scaling.

Answer

Set the auto_resume property to TRUE to ensure the warehouse starts automatically.

Answer

Alter the warehouse type from STANDARD to SNOWPARK-OPTIMIZED.

Question 14

A company is using Search Optimization Service (SOS) to speed up point lookups. They notice that after adding SOS to a column, the storage costs have increased significantly. Which architectural factor most likely contributes to this cost increase?

Accepted Answer

The maintenance of the search access path for a high-churn table with many updates.. Search Optimization Service works by creating and maintaining an auxiliary data structure (a search access path) that tracks the values in the optimized columns. This structure requires additional storage. More importantly, when data in the table changes, this auxiliary structure must be updated, which consumes serverless compute resources and creates new versions of the search data, increasing storage and maintenance costs.

Answer

The SOS service creates a full physical copy of the table in a separate region.

Answer

The SOS service requires a dedicated X-Large virtual warehouse to be running 24/7.

Answer

SOS automatically converts the table from columnar storage to a row-based format.

Question 15

An architect is designing a staging area for a data pipeline where data will only be needed for 24 hours before being transformed and moved. Which TWO table types would be most cost-effective for this scenario? (Select TWO)

Accepted Answer

Transient tables, as they do not incur Fail-safe storage costs.. Temporary and Transient tables are the most cost-effective choices for short-lived data. Temporary tables are tied to a session, making them ideal for ETL steps. Transient tables persist across sessions but lack Fail-safe storage, reducing costs for data that can be easily recreated. Both types avoid the 7-day Fail-safe storage charges associated with Permanent tables.

Answer

Permanent tables with a 1-day Time Travel retention period.

Answer

External tables pointing to a folder in an S3 bucket.

Answer

Materialized views created on top of the source external stage.