SnowPro Advanced: Architect (ARA-C01) Practice Exam

What the SnowPro Advanced exam covers

• Accounts and Security179 questions
• Snowflake Architecture173 questions
• Data Engineering161 questions
• Performance Optimization160 questions

Free SnowPro Advanced sample questions

A sample of 10 questions with answers and explanations. Sign up free to practice all 673.

1. Question 1Accounts and Security

   A healthcare ISV must sign a BAA for HIPAA workloads and needs customer-managed encryption keys layered on top of Snowflake-managed keys, but does not require fully isolated compute infrastructure. Which edition satisfies these requirements at the lowest cost?

   • AStandard Edition, no add-on
   • BEnterprise Edition
   • CBusiness Critical EditionCorrect
   • DVirtual Private Snowflake
   ✓ Correct answer: C

   Business Critical Edition adds enhanced security for regulated data, including support for signing a BAA and Tri-Secret Secure, which combines a customer-managed key with Snowflake's key. Enterprise lacks these controls, Standard cannot add them via a simple add-on, and VPS provides full infrastructure isolation the scenario does not require, making it unnecessarily costly given the actual compliance scope here.

   Why the other options are wrong

   • AStandard Edition has no add-on path to HIPAA support or customer-managed keys; those require an edition upgrade.
   • BEnterprise Edition unlocks governance and performance features but not HIPAA BAAs or Tri-Secret Secure.
   • DVPS provides full physical isolation, which exceeds the stated need and adds cost and onboarding complexity.
2. Question 2Accounts and Security

   A team lead role needs to be able to grant SELECT on a table to other roles on the architect's behalf, without being made the object's owner. What should the architect use?

   • AGRANT MANAGE GRANTS ON table1 TO ROLE lead
   • BGRANT OWNERSHIP ON table1 TO ROLE lead
   • CGRANT SELECT ON table1 TO ROLE lead WITH ADMIN OPTION
   • DGRANT SELECT ON table1 TO ROLE lead WITH GRANT OPTIONCorrect
   ✓ Correct answer: D

   This is narrower and safer than transferring ownership, since the lead role only gains the ability to propagate SELECT, not full control of the object. WITH ADMIN OPTION is the syntax used for role grants, letting a role manage another role's membership, not object privilege grants. MANAGE GRANTS is a global privilege for account-wide grant management, not something scoped to a single table.

   Why the other options are wrong

   • AMANAGE GRANTS is an account-wide administrative privilege, not a table-scoped grant-propagation option.
   • BThis transfers full ownership of the table, far more control than simply re-granting SELECT.
   • CWITH ADMIN OPTION applies to role-to-role grants for managing membership, not object privilege grants like SELECT.
3. Question 3Accounts and Security

   A newly hired architect inherits an account where SAML2 SSO, External OAuth, and key-pair authentication are all configured simultaneously for different populations of users. A specific user reports being unable to authenticate at all, even though their credentials appear correct in every system. What should the architect check first?

   • AWhether a user- or account-level network policy is blocking the user's current IPCorrect
   • BWhether the SAML2_ISSUER value matches the External OAuth issuer exactly
   • CWhether the user's RSA_PUBLIC_KEY_2 slot has ever been populated
   • DWhether SCIM has assigned the user a role with the SECURITYADMIN privilege
   ✓ Correct answer: A

   Network policies are enforced at connection time regardless of which authentication mechanism, SAML2, External OAuth, or key pair, the user would otherwise use, so a blocked IP prevents authentication entirely and can look identical to a credential problem across every configured method. Checking the effective network policy for that user is the fastest way to rule out this common cross-cutting cause first.

   Why the other options are wrong

   • BSAML2 and External OAuth issuer values serve unrelated integrations for different login paths and are not expected to match each other.
   • CAn unused second key slot is normal and does not, by itself, prevent authentication through any of the configured methods.
   • DRole assignment affects what a user can do after logging in, not whether the initial authentication attempt itself succeeds.
4. Question 4Snowflake ArchitectureSelect all that apply

   Which TWO statements correctly characterize micro-partitions? (Choose TWO)

   • AEach one carries its own column-level statisticsCorrect
   • BThey are organized in a columnar layout internallyCorrect
   • CThey can be edited in place by later UPDATE statements
   • DTheir size is chosen per-query by the optimizer
   ✓ Correct answer: A, B

   Every micro-partition stores its data in a columnar layout and carries its own per-column metadata (min/max, distinct counts), which the optimizer uses for pruning. Micro-partitions are immutable and never edited in place by UPDATE; new ones are written instead. Their target size range is a fixed internal design choice, not something the optimizer varies per query.

   Why the other options are wrong

   • CMicro-partitions are immutable; an UPDATE writes new partitions rather than editing existing ones.
   • DPartition sizing follows a fixed internal target range; the optimizer does not resize partitions per query.
5. Question 5Snowflake Architecture

   A consumer creates a database from an inbound share within the same region and cloud platform as the provider. How much storage does that imported database consume in the consumer account?

   • ANone, it only references the provider's storageCorrect
   • BA full copy sized exactly the same as the provider's data
   • CAbout half, due to Snowflake's shared compression
   • DA cache-sized copy proportional to query volume
   ✓ Correct answer: A

   CREATE DATABASE ... FROM SHARE builds a read-only database whose micro-partitions still physically live in the provider's storage; the consumer account only holds metadata pointing at those files. No storage bill accrues to the consumer for the shared data itself, though the consumer is still billed separately for the compute used to run queries against it, regardless of how much data those queries scan.

   Why the other options are wrong

   • BA full duplicate copy is what replication produces, not what a metadata-based share produces.
   • CThere is no partial-copy compression behavior for shared databases; nothing is physically duplicated at all.
   • DQuery results are not cached into consumer-owned storage as a side effect of sharing.
6. Question 6Snowflake Architecture

   A 20 TB permanent table currently has DATA_RETENTION_TIME_IN_DAYS set to 90 for a table that is rarely queried historically, and the storage bill is under scrutiny. The business only truly needs a 3-day recovery window for this table. What is the appropriate trade-off?

   • ALower the retention to 3 days to cut Time Travel storage costCorrect
   • BConvert the table to transient to keep the full 90-day window
   • CLeave retention at 90 days since Fail-safe cost is unaffected either way
   • DIncrease retention further, since higher retention always lowers cost
   ✓ Correct answer: A

   Time Travel storage cost scales with how many historical micro-partitions must be retained, so trimming DATA_RETENTION_TIME_IN_DAYS from 90 down to the 3 days the business actually needs directly reduces the retained history and the associated storage bill, while still meeting the real recovery requirement. This is the standard cost-versus-protection trade-off architects tune per table based on actual need.

   Why the other options are wrong

   • BConverting to transient caps retention at one day maximum; it cannot preserve a 90-day window, and it also changes CDP behavior.
   • CRetention length does directly affect Time Travel storage cost, even though it does not change the separate, fixed Fail-safe cost.
   • DHigher retention retains more historical data and therefore costs more, not less; this reverses the actual cost relationship.
7. Question 7Data EngineeringSelect all that apply

   A task is scheduled to run every minute with WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') guarding its body. Which TWO statements correctly describe this pattern? (Select two.)

   • AThe task body runs its SQL only when the stream has changesCorrect
   • BThis avoids running the full task logic on empty polling cyclesCorrect
   • CThe WHEN clause guarantees zero cost on every single skipped run
   • DSYSTEM$STREAM_HAS_DATA permanently consumes the stream's offset on every call it makes
   ✓ Correct answer: A, B

   The WHEN clause lets a frequently-scheduled task check SYSTEM$STREAM_HAS_DATA cheaply and skip its main SQL body entirely when the stream is empty, which is the standard pattern for near-real-time, low-waste CDC polling. Checking the function is a lightweight metadata read, not a consuming DML operation, so it never advances the stream's offset. Evaluating the WHEN condition itself still has a small associated cost on every scheduled run, even when the body is skipped.

   Why the other options are wrong

   • CEvaluating the WHEN condition on every scheduled tick still carries a small cost even when the task body is skipped.
   • DSYSTEM$STREAM_HAS_DATA only inspects whether unconsumed changes exist; it is a read check and does not advance the stream's offset.
8. Question 8Data EngineeringSelect all that apply

   An architect is choosing an ingestion path for a new source and is weighing the classic file-based Snowpipe against the Kafka connector's Snowpipe Streaming mode. Which two statements accurately contrast them? (Select two.)

   • AStreaming skips file staging, cutting ingestion latencyCorrect
   • BClassic Snowpipe loads files already sitting in a stageCorrect
   • CStreaming only ever works for data without VARIANT content
   • DClassic Snowpipe always needs a fully manual COPY INTO run
   ✓ Correct answer: A, B

   Snowpipe Streaming writes rows through a low-latency ingest SDK path without first buffering data as stage files, which cuts end-to-end latency compared to the classic approach. Classic Snowpipe, by contrast, is built around detecting and loading files that have already landed in an internal or external stage location, typically triggered by cloud storage event notifications. Snowpipe Streaming can load rows that include VARIANT content, and classic Snowpipe is commonly automated via notifications, not limited to manual COPY INTO.

   Why the other options are wrong

   • CSnowpipe Streaming supports VARIANT-containing rows just as classic Snowpipe does; there is no such restriction.
   • DClassic Snowpipe is typically automated through event notifications, not restricted to manual COPY INTO runs.
9. Question 9Performance Optimization

   A query joins a 500-row dimension table with a 2-billion-row fact table on a low-cardinality key. Which behavior does the optimizer typically favor for efficiency?

   • AReplicating the small dimension table to every compute node so the large fact table need not be shuffled across the cluster.Correct
   • BShuffling both tables evenly across all compute nodes regardless of their relative sizes, since that is always the default behavior.
   • CSorting the fact table entirely before any join step, since sorting is always required before a hash join can run.
   • DMaterializing the entire fact table into the result cache before applying the join predicate against the dimension table.
   ✓ Correct answer: A

   When one side of a join is small enough to fit comfortably in memory, Snowflake's optimizer commonly favors replicating (broadcasting) that small table to every compute node, avoiding an expensive shuffle of the much larger fact table across the cluster. Evenly shuffling both regardless of size, mandatory full sorts before every hash join, or materializing the fact table into the result cache don't reflect how the optimizer actually behaves here.

   Why the other options are wrong

   • BThe optimizer accounts for relative table sizes rather than always shuffling both sides evenly regardless of scale.
   • CA hash join does not require the larger side to be fully sorted first; hashing, not sorting, drives matching.
   • DThe result cache stores final query results after execution, not raw intermediate table data used mid-join.
10. Question 10Performance Optimization

    An IoT table clustered on sensor_id receives continuous inserts spanning all sensor_id values evenly. What is the likely outcome?

    • AOngoing reclustering cost with little matching query benefitCorrect
    • BThe table stops accepting new inserts once clustering starts
    • CSnowflake automatically drops the clustering key after a week
    • DInsert throughput is capped to match the clustering rate
    ✓ Correct answer: A

    If new rows continuously arrive spanning the entire sensor_id range rather than a narrow, drifting slice of it, every load batch reintroduces the same overlap the background service just fixed. This produces steady automatic clustering credit consumption while the query benefit stays limited, since the table's physical order keeps getting disturbed as fast as it is repaired, making the key expensive relative to its payoff.

    Why the other options are wrong

    • BSnowflake does not stop accepting inserts because a clustering key is defined on a table.
    • CAutomatic clustering keys are not dropped automatically after any fixed time period.
    • DInsert throughput is not throttled to match the pace of background reclustering.

Who this SnowPro Advanced practice exam is for

This practice set is for anyone preparing for the SnowPro Advanced: Architect (ARA-C01) exam at the advanced level - from first-time candidates building a foundation to experienced Data practitioners doing a final review before test day. If you learn best by working through realistic questions and reading why each answer is right or wrong, it is built for you.

How to use this SnowPro Advanced practice exam

1. Start with the free sample questions above to gauge your current baseline.
2. Read the full explanation on every question, including why each wrong option is wrong.
3. Track your weak domains and focus your study where you are losing the most marks.
4. Once you are scoring consistently well, take a timed, full-length mock exam.
5. Use your readiness score to decide when you are ready to book the real SnowPro Advanced exam.

Related Data resources

• SnowPro Advanced study guideKey concepts
• Data practice examsAll Data
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• Databricks Certified Data Analyst Associate practice examRelated
• MongoDB Associate Developer practice examRelated
• Databricks Certified Data Engineer Associate practice examRelated

SnowPro Advanced practice exam FAQ