Snowflake ARA-C01: Snowflake Architecture Practice Questions

The fundamental problem is workload interference: a resource-intensive batch job is competing with interactive analyst queries on the same warehouse. The cleanest architectural solution is workload isolation — assign the batch job its own dedicated virtual warehouse, sized appropriately for batch throughput, and leave the existing warehouse for analysts. This eliminates contention entirely without affecting either workload's performance. Option D is counterproductive: the Economy scaling policy is more conservative about adding clusters (waits until existing clusters are fully loaded), which would make the queuing problem worse, not better — it would delay spin-up of additional clusters, increasing analyst wait times. Option A (increasing max clusters) does not solve the root cause; if a batch job dominates resources within a cluster, adding more clusters helps concurrency but does not prevent a single heavy batch job from consuming disproportionate slots on a shared cluster. The 90-second delay suggests startup/spin-up time, not a capacity issue. Option B is a misconception: the Query Acceleration Service offloads portions of individual queries to serverless compute to reduce per-query latency on large scans — it does not prioritize one query type over another and cannot enforce workload isolation.

Option C is correct. When a Snowflake Iceberg Table is configured with an external catalog (such as AWS Glue), Snowflake acts as a query engine over the externally managed Iceberg metadata and Parquet data. In this mode, the table is read-only from Snowflake — write operations (INSERT, UPDATE, DELETE) are not supported because Snowflake does not own the catalog and cannot write Iceberg metadata back to the external catalog. To enable writes from Snowflake, Snowflake must be the Iceberg catalog (catalog integration type = SNOWFLAKE). Option A is wrong because Snowflake does not automatically convert or copy external Iceberg data into its own managed storage; the Parquet files remain in S3 and are queried in place. Option D is wrong because full DML on externally-cataloged Iceberg tables is not supported in Snowflake; the table is read-only when backed by an external catalog. Option B is wrong because Snowflake does leverage Iceberg partition metadata for partition pruning even when the catalog is external — this is one of the key performance advantages of Iceberg over plain external tables.

SYSTEM$CLUSTERING_INFORMATION returns average_depth and average_overlaps as the primary health indicators. An average_depth close to 1 and average_overlaps close to 0 indicate that micro-partitions are well-organized around the clustering key — data is highly pruned when filtering on CARRIER_CODE. No immediate reclustering is warranted. Option A is wrong because the metrics are excellent, not poor; poor clustering is indicated by high average_depth (e.g., > 3–4) and high average_overlaps. Option C contains a common misconception: low-cardinality columns can be excellent clustering keys because Snowflake's micro-partition pruning is most powerful when a small number of distinct values map cleanly to distinct micro-partition ranges, which is exactly what the metrics confirm here. Option D (adding a high-cardinality column) might be appropriate if queries also filter on a second dimension, but the metrics indicate no clustering problem exists with the current key, so prescribing a change is unjustified.

Option B is correct. A multi-cluster warehouse with Standard scaling policy spins up additional clusters immediately when queues form, which is ideal for latency-sensitive, high-concurrency workloads. Setting auto-suspend to 1 minute reclaims credits quickly during off-hours. Option A is wrong because a single 4XL warehouse adds compute nodes (not clusters) and does not help with query queuing from concurrent users — it improves single-query performance, not concurrency. Option C is wrong because Economy scaling policy delays spinning up additional clusters until the queue is substantial, sacrificing the required low latency. Option D is wrong because manually segmenting users across ten warehouses is operationally complex, prevents load balancing, and does not dynamically scale down during off-hours.

Option B is correct. For existing Snowflake customers, a native Snowflake share (or a Marketplace listing) is the appropriate mechanism — no data is copied and consumers can query using their own compute credits. For consumers without a Snowflake account, reader accounts (previously called 'managed accounts') are the correct solution: the provider creates and manages a reader account, and consumers query through it using the provider's compute. Option A is wrong: native shares require that the consumer already has a Snowflake account; the 20 non-Snowflake businesses cannot receive or access a standard Snowflake share. Option C is wrong: using a Marketplace listing is a valid option for the 180 existing consumers but adds unnecessary complexity compared to direct shares when the data is not intended for public monetization — however, the more critical error is the same as A: neither a listing nor a direct share works for the 20 non-Snowflake consumers. Option D is wrong: provisioning 200 reader accounts for all consumers ignores the fact that the 180 existing Snowflake customers already have accounts; using reader accounts for them forces the provider to pay for those consumers' compute, which is more expensive than allowing consumers to use their own warehouses — and reader accounts are specifically designed for non-Snowflake consumers.