Snowflake ARA-C01 Practice Exam
SnowPro Advanced: Architect ARA-C01
The SnowPro Advanced: Architect certification validates advanced knowledge and skills used to apply comprehensive architecture solutions using Snowflake: designing end-to-end data flows, meeting security and compliance requirements, choosing tools for performance and sharing data through the Snowflake Marketplace. The ARA-C01 exam covers four domains: Accounts and Security (25%), Snowflake Architecture (30%), Data Engineering (25%), and Performance Optimization (20%). This practice exam mirrors its scenario style with single-answer, multiple-select and drag-and-drop questions.
687 questions in the bank
Question bank updated : questions, answers, explanations and chapters.
Certification Overview
- Exam name: SnowPro Advanced Architect ARA-C01
- Vendor: Snowflake
- Exam code: ARA-C01
- Duration: 115 minutes
- Total questions: about 65
- Official passing score: 750 on a scale of 0-1000 (source)
- Our practice exam pass mark: 75%
Who Should Take This Exam?
Solution Architect, Database Architect, System Architect, Data Architect, Cloud Architect, Data Platform Architect, Senior Data Engineer, Technical Lead
Prerequisites
Active SnowPro Core certification. Snowflake recommends 2 or more years of practical experience with Snowflake as an Architect in a production environment.
Topics Covered
- Accounts and Security
- Snowflake Architecture
- Data Engineering
- Performance Optimization
Question Types
- Multiple Choice (Single Answer)
- Multiple Choice (Multiple Answers)
- Drag and Drop
SnowPro Advanced Architect ARA-C01 Practice Questions
Our question bank contains 687+ practice questions for this certification. Sample questions from each exam chapter. Expand a question to see the answer choices. With full access, you get unlimited practice exams with randomized questions from our full question bank.
Accounts and Security
STATEMENT_TIMEOUT_IN_SECONDS is 3600 on the account and 600 on the warehouse BI_WH. A BI user runs ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 1800 and then starts a long query on BI_WH. After how many seconds does Snowflake cancel the query?
- A. 600 seconds Correct answer
- B. 1800 seconds
- C. 3600 seconds
- D. 172800 seconds
Option A is correct because when STATEMENT_TIMEOUT_IN_SECONDS is set at more than one level, Snowflake applies the lowest non-zero value that applies between the session and the warehouse (the account value is overridden by the lower levels). The warehouse limit of 600 is lower than the session value of 1800, so the query is cancelled after 600 seconds. A session setting can lower the limit but cannot raise it above a warehouse limit, and 172800 seconds is only the default when nothing is set.
Forty schemas and twelve job functions must be governed so that auditors review privileges in one place and a new job function can be onboarded without regranting object privileges. Which role design fits?
- A. Define future grants on each schema for the PUBLIC role so the functional roles inherit the privileges through PUBLIC
- B. Create one role per schema holding its privileges and have SECURITYADMIN grant each of those roles directly to users
- C. Grant schema privileges to read and write access roles, then grant those to functional roles assigned to users Correct answer
- D. Grant the object privileges directly to each functional role, then assign each functional role to its users
Option C is correct because the access role pattern keeps object privileges on a small set of read and write access roles per schema, which auditors can review in one place, and functional roles simply inherit those access roles. A new job function then needs only a grant of existing access roles, with no regranting of object privileges. Granting privileges directly to each functional role scatters the grants and forces repeats for every new function, and anything granted to PUBLIC is available to every user in the account, so it cannot be a governed design. Granting per-schema roles straight to users bypasses functional roles and leaves access managed user by user.
Holding both FIN_READ (finance schemas) and HR_READ (HR schemas), one analyst must join tables from both in a single query. Objects this analyst creates must stay owned by FIN_READ, and other FIN_READ users must keep seeing finance data only. What should the architect do?
- A. Set the analyst's DEFAULT_SECONDARY_ROLES to ('ALL') and keep FIN_READ as the default role Correct answer
- B. Create a view joining both schemas, owned by SYSADMIN, and grant SELECT on it to FIN_READ
- C. Create a role FIN_HR_READ holding both privilege sets and make it the analyst's default role
- D. Grant HR_READ to FIN_READ so the analyst inherits the HR privileges while using FIN_READ
Option A is correct because secondary roles let a session use the privileges of every role granted to the user at once, so the analyst can join finance and HR tables in one query. The primary role stays FIN_READ, and objects are owned by the primary role, so new objects stay with FIN_READ. Nothing changes for other FIN_READ users, so they still see finance data only. Granting HR_READ to FIN_READ, or granting a view to FIN_READ, would expose HR data to every FIN_READ user, and a combined FIN_HR_READ default role would make new objects owned by that role instead of FIN_READ.
Which system role should own databases, schemas and warehouses so that custom roles can be rolled up beneath it?
- A. ACCOUNTADMIN
- B. USERADMIN
- C. SECURITYADMIN
- D. SYSADMIN Correct answer
Option D is correct because SYSADMIN is the system role intended to own and manage databases, schemas, warehouses and other objects, and custom roles are meant to be granted to SYSADMIN so it keeps control of everything they own. ACCOUNTADMIN sits above it and should not own everyday objects. USERADMIN and SECURITYADMIN manage users and roles and grants, not the objects themselves.
Which TWO masking policy return values let analysts still join and group on CUSTOMERS.EMAIL without seeing any actual address? (Select TWO).
- A. SHA2(val, 256) computed over the address Correct answer
- B. The output of an external tokenization function that maps each address to a stable token Correct answer
- C. A NULL value in place of the address
- D. REGEXP_REPLACE(val, '.+@', '*****@') applied to the address
- E. A constant string such as '*****' in place of the address
Options A and B are correct because both return a deterministic value for each address: a SHA2 hash or a stable token is the same every time for the same input, so joins and GROUP BY on the masked column still match addresses one to one without showing any actual address. A NULL or a constant string gives every row the same value, so joins match nothing useful and all rows fall into one group. The REGEXP_REPLACE option still shows each domain and merges every address at that domain into one value, so it neither keeps addresses distinct nor hides all of the address.
Snowflake Architecture
Operational tables arrive in third normal form, and dashboards now need eight joins to answer common questions. The team wants to keep the loaded layer unchanged. Which design handles this best in Snowflake?
- A. Keep the normalized tables as the integration layer and add a denormalized star schema above them for BI Correct answer
- B. Move the normalized tables into hybrid tables so that the eight joins become index lookups on the primary keys
- C. Convert each normalized table into one VARIANT column so that analysts query attributes without any joins
- D. Cluster each normalized table on its foreign key columns so that Snowflake eliminates the joins at compile time
Option A is correct because keeping the normalized tables as the integration layer and publishing a denormalized star schema above them removes the joins for BI without touching the loaded layer. Hybrid tables (B) are built for single-row transactional access, not for removing joins from analytics. A VARIANT column per table (C) throws away typing and structure, and clustering (D) improves pruning but never eliminates a join.
What is the main trade-off of a fully normalized model compared with a denormalized one for analytics?
- A. Fewer joins per query, but duplicated values that need updating in many rows
- B. Faster scans of every column, but slower inserts into micro-partitions
- C. Less duplicated data and simpler updates, but more joins per query Correct answer
- D. Lower compute use per query, but loss of support for primary keys
Option C is correct because a fully normalized model stores each fact once, which means less duplication and simple updates, but analytics queries must join more tables. Option A describes the denormalized side of the trade-off. Options B and D invent costs that normalization does not cause, since primary keys are fully supported.
Clickstream events arrive as JSON whose attributes change monthly, and finance also needs monthly revenue by product and region. Which modeling split serves both needs?
- A. Keep the events in VARIANT and point the revenue dashboards at the raw JSON paths with casts in each query
- B. Land the JSON unchanged in VARIANT columns, then publish typed fact and dimension tables for the stable revenue attributes Correct answer
- C. Define typed columns for every attribute at load time and fail any file that arrives with an attribute the table does not have
- D. Create one table per attribute set and add a new table to the load whenever the JSON changes shape
Option B is correct because VARIANT absorbs the monthly attribute changes while typed fact and dimension tables built from the stable attributes give finance fast, reliable revenue reporting. Querying raw JSON for every dashboard (A) pushes casting and fragility into each report. Failing files on new attributes (C) breaks the load every month, and a new table per JSON shape (D) multiplies maintenance.
Which situation most strongly favors a data vault over a Kimball star schema as the core warehouse model in Snowflake?
- A. The main requirement is the fastest possible response for a few repeated dashboard queries over one fact table
- B. Many sources change often, and the warehouse needs full history with insert-only loads that run in parallel Correct answer
- C. Business users query the model directly from a BI tool and expect the smallest number of joins per question
- D. A single stable source feeds one reporting team that needs a handful of conformed dashboards on a fixed schedule
Option B is correct because a data vault suits many changing sources, full history and insert-only loads that can run in parallel across hubs, links and satellites. Options A, C and D describe stable, small or BI-facing needs where a Kimball star schema with few joins is the better fit.
Live access to quality data must go to a parts supplier that runs its own Snowflake account, and read-only access for one quarter must go to an external auditor with no Snowflake account. Which TWO sharing mechanisms fit these two consumers? (Select TWO).
- A. Add the auditor's email address to the share so that the auditor can open its own free Snowflake account
- B. Add the supplier's account to a direct share on the quality database Correct answer
- C. Create a reader account for the auditor and add it to a share on the quality database Correct answer
- D. Create a reader account for the supplier so that its analysts can join the quality data with their own tables
- E. Replicate the quality database to the supplier's account and to the auditor's account on a refresh schedule
Options B and C are correct because a supplier with its own Snowflake account receives live data through a direct share, while an auditor with no account needs a reader account that the provider creates and adds to a share. An email address cannot be added to a share, a reader account is for consumers without an account so the supplier does not need one, and replication copies data between accounts of one organization on a schedule rather than giving live access.
Data Engineering
Small JSON event files land in an S3 bucket throughout the day, and analysts want each file queryable within about a minute without anyone managing a warehouse. Which loading approach fits?
- A. An external table over the bucket whose metadata is refreshed once each night with ALTER EXTERNAL TABLE ... REFRESH
- B. Snowpipe with auto-ingest driven by S3 event notifications Correct answer
- C. A task scheduled every hour that runs COPY INTO on a dedicated warehouse that suspends when idle
- D. A bulk COPY INTO statement that an analyst runs from a worksheet whenever new files are expected
Option B is correct because Snowpipe with S3 event notifications loads each file within about a minute using serverless compute, so nobody manages a warehouse. Option A leaves the metadata stale until the nightly refresh, and options C and D wait for an hourly task or a person to run COPY.
Three terabytes of Parquet history, partitioned by date in an S3 data lake, must be queried in Snowflake with clustering on order date and Time Travel for analysts. No other engine will read the files afterwards. Which approach meets the requirements?
- A. Create a directory table on the external stage and query the files through it with a clustering key on the file path
- B. Create an external table over the bucket and define a clustering key on order date to speed up date filters
- C. Create an externally managed Iceberg table over the Parquet files and set DATA_RETENTION_TIME_IN_DAYS on it for Time Travel
- D. Load the Parquet files into a native table with COPY INTO and define a clustering key on order date Correct answer
Option D is correct because only a native table gives both a clustering key on order date and Time Travel, and the files are not needed by another engine. External tables and directory tables cannot be clustered, and an externally managed Iceberg table has no Snowflake-controlled Time Travel.
Compliance archives of 40 TB sit in an S3 bucket under the Glacier Deep Archive storage class, and an investigation requires loading a subset of them into Snowflake. What must happen first?
- A. Define an external table with AUTO_REFRESH enabled so that Snowflake reads the archived objects in place without restoring them
- B. Restore the needed objects to a readable storage class, then load them through an external stage Correct answer
- C. Run COPY INTO with the CREDENTIALS option set to the IAM role so that archived objects are returned to the stage on demand
- D. Grant the storage integration role the s3:RestoreObject permission so that COPY INTO restores each archived object as it reads
Option B is correct because objects in Glacier Deep Archive cannot be read until they are restored, and Snowflake cannot restore them for you. Once restored they can be loaded through an external stage, whereas AUTO_REFRESH, the CREDENTIALS option or an s3:RestoreObject grant never makes archived objects readable.
Orders are updated and deleted in table RAW_ORDERS, and a standard stream on it feeds a nightly merge job. Which TWO statements about this stream are accurate? (Select TWO).
- A. An updated row appears as a DELETE row and an INSERT row for the same key, both with METADATA$ISUPDATE set to TRUE Correct answer
- B. A plain SELECT on the stream moves the offset forward, so the next query returns only newer changes to RAW_ORDERS
- C. An updated row appears as one row with METADATA$ACTION set to UPDATE and the new column values for the same key
- D. Deleted rows are dropped from the stream once the table's Time Travel retention period has passed for each of them
- E. The offset advances when a DML statement that reads the stream, such as MERGE or INSERT ... SELECT, commits Correct answer
Options A and E are correct because a standard stream shows an update as a DELETE row and an INSERT row for the same key, both flagged METADATA$ISUPDATE = TRUE, and the offset moves only when a DML statement that reads the stream commits. Option B is wrong because a plain SELECT does not advance the offset, option C is wrong because there is no UPDATE action value, and option D is wrong because stream contents do not depend on Time Travel expiry of each row.
Which TWO statements describe a stream created with APPEND_ONLY = TRUE on a table that receives inserts, updates and deletes? (Select TWO).
- A. Rows inserted and then deleted before the offset advances are still returned when the stream is consumed Correct answer
- B. It can be created on a table only when Time Travel retention for that table is set to at least 7 days
- C. It requires the source table to have CHANGE_TRACKING turned off so that it can read micro-partition metadata
- D. It returns an updated row as a DELETE row and an INSERT row, in the same way as a standard stream does
- E. It records rows added by INSERT operations, and rows removed by DELETE or TRUNCATE do not appear in it Correct answer
Options A and E are correct because an append-only stream records only rows added by INSERT, and a row inserted and then deleted before the offset advances is still returned, since deletes are ignored. Option B is wrong because there is no retention minimum, option C is wrong because change tracking is enabled on the table by the stream, and option D describes a standard stream.
Performance Optimization
Nightly batches of orders are appended to a 4 TB table in sale_date order, and date-range queries prune well although no clustering key was ever defined. What explains the pruning?
- A. Snowflake builds an implicit index on the first DATE column of each table, and the optimizer uses it to locate matching partitions at query time
- B. Natural clustering: partitions are written in load order, so each one holds a narrow, non-overlapping range of sale_date Correct answer
- C. Search optimization is applied by default to the date columns of large tables and keeps a lookup structure for range predicates on them
- D. Automatic Clustering is switched on by default for any table above 1 TB and has already reordered the rows by sale_date in the background
Option B is correct because micro-partitions are created in load order, so data appended in sale_date order is naturally clustered and each partition covers a narrow, non-overlapping date range that the optimizer can prune with its min/max metadata. Snowflake has no implicit index (A), does not apply search optimization by default (C), and Automatic Clustering only runs on tables that have a clustering key defined (D).
Several parallel pipelines load a 6 TB clickstream table in arbitrary event_date order, and queries that filter on a 3-day range scan nearly all micro-partitions. Which change addresses the root cause?
- A. Create a materialized view that selects all columns, because a materialized view stores its rows ordered by the filter column
- B. Resize the warehouse from Medium to 2X-Large so that each query reads fewer micro-partitions from the table
- C. Define a clustering key on event_date so Snowflake reorganizes the micro-partitions into narrow date ranges Correct answer
- D. Convert the table to a hybrid table with an index on event_date so that range predicates are resolved through the index
Option C is correct because the cause is that rows arrive in arbitrary date order, so every micro-partition spans most dates; a clustering key on event_date lets Snowflake regroup rows into narrow date ranges that can be pruned. A bigger warehouse (B) scans the same partitions faster but does not reduce how many are read, a materialized view (A) does not store rows sorted by a filter column, and hybrid tables (D) are a different row-store engine, not a fix for this analytic table.
Which table gains the least from adding a clustering key?
- A. A 400 MB dimension table of postal codes, held in a few dozen micro-partitions, that most queries read in full to join to facts Correct answer
- B. A 9 TB fact table filtered by selective sale_date ranges in dashboards, loaded from several regional pipelines in arbitrary date order
- C. A 7 TB claims table read by selective filters on policy_region, whose rows were loaded in random order by many concurrent COPY jobs
- D. A 12 TB event table queried by equality on tenant_id and by hourly ranges, whose partitions overlap heavily after months of late data
Option A is correct because a table of a few dozen micro-partitions that queries read in full has nothing to prune, so a clustering key adds reclustering cost with no benefit. The large tables in B, C and D are filtered selectively on columns whose values are scattered across partitions, which is the situation where clustering pays off.
A BI warehouse on Enterprise Edition handles 150 concurrent short dashboard queries each morning and sits nearly idle after lunch. Credits should follow demand, and an extra cluster should start as soon as queries begin to queue. Which TWO settings fit? (Select TWO).
- A. SCALING_POLICY STANDARD, which prefers starting an additional cluster over letting a queued query wait for a busy one Correct answer
- B. MIN_CLUSTER_COUNT 1 with MAX_CLUSTER_COUNT 6, so that the warehouse runs in Auto-scale mode and adds clusters while queries wait Correct answer
- C. A resize of the warehouse to 3X-Large, so that each dashboard query is given more nodes on its single cluster during the morning peak
- D. MIN_CLUSTER_COUNT 6 with MAX_CLUSTER_COUNT 6, so that all six clusters are already running when the first morning queries arrive and stay up through the whole day
- E. SCALING_POLICY ECONOMY, which keeps the running clusters fully loaded and starts a new one when several minutes of queued work have built up for it
Options A and B are correct because Auto-scale mode (minimum 1, maximum 6) lets credits follow demand, and the Standard scaling policy starts an additional cluster as soon as a query queues. Option D keeps six clusters running all day and wastes credits. Option C scales up, which does not add concurrency for many short queries, and option E, the Economy policy, waits several minutes of queued work before it starts a cluster.
Which TWO statements about multi-cluster warehouses are correct? (Select TWO).
- A. Clusters of one warehouse can be given different sizes, so that heavy queries run on the larger ones
- B. They require Enterprise Edition or higher Correct answer
- C. They can be created on Standard Edition when MAX_CLUSTER_COUNT is set to 2 or lower
- D. All clusters of one warehouse have the same size Correct answer
- E. They divide one long-running query between several clusters so that it finishes sooner
Options B and D are correct because multi-cluster warehouses need Enterprise Edition or higher, and every cluster in one warehouse has the same size. Option A is wrong because sizes cannot differ within a warehouse. Option C is wrong because Standard Edition does not offer multi-cluster warehouses at any maximum. Option E is wrong because a single query runs on one cluster, and extra clusters only add concurrency.
Free Study Guide
Read the free Snowflake ARA-C01 study guide: every exam topic explained in plain English, with diagrams, exam tips and the mistakes to avoid.
More Snowflake Practice Exams
Practice with realistic mock exams to prepare for your Snowflake certification.