SnowPro Advanced Architect — Free Practice Questions
10 free sample questions from a bank of 109, with the correct answers and explanations. No signup required — start practising right now.
1What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)
The MERGE command
The UPSERT command
The CHANGES clause
A STREAM object
Thee CHANGE_DATA_CAPTURE command
Answer: C, D
The short version
CD — Change tracking powers streams and CHANGES. Streams and the CHANGES clause both read the hidden change metadata Snowflake maintains for tables, so they are the two native consumers. MERGE and similar DML merely generate changes rather than consuming that metadata directly.
Key concepts in this question
Change tracking metadata: hidden row-level delta information Snowflake maintains when change tracking is enabled, recording inserts, updates and deletes between versions.
STREAM object: persistent object recording table deltas since its last offset for CDC-style incremental processing.
CHANGES clause: SQL clause for SELECT that queries historical deltas directly without creating a persistent stream, useful for one-off auditing and point lookups.
MERGE command: DML statement that applies inserts, updates and deletes in one pass, producing new versions but not itself reading change metadata.
CHANGE_DATA_CAPTURE wording: no such Snowflake command exists, and UPSERT is a MERGE pattern, so both fail on naming.
Why CD is correct
Snowflake docs state that change tracking metadata feeds two query mechanisms, streams and the CHANGES clause. Both require change tracking to be enabled on the source table and both expose inserts, updates and deletes with metadata columns. MERGE and UPSERT patterns write changes but do not consume the tracking log, so only C and D qualify as consumers of that metadata.
Why the others are wrong
A. MERGE writes new row versions but does not read change tracking metadata to operate.
B. UPSERT is not a separate Snowflake command, it is a MERGE pattern and does not consume tracking data.
E. CHANGE_DATA_CAPTURE as named is not a Snowflake command, and the spelling plus concept are fictitious.
SnowPro-Advanced-Architect exam tip
Remember streams equal persistent CDC and CHANGES equals ad-hoc CDC, both need CHANGE_TRACKING equals TRUE.
2Which organization-related tasks can be performed by the ORGADMIN role? (Choose three.)
Changing the name of the organization
Creating an account
Viewing a list of organization accounts
Changing the name of an account
Deleting an account
Enabling the replication of a database
Answer: B, C, F
The short version
BCF — ORGADMIN creates accounts, lists them and enables replication. Those three are the documented organization-level powers of the role. Renaming organization or accounts and deleting accounts sit outside its grant set.
Key concepts in this question
ORGADMIN role: organization-level administrative role for account lifecycle and organization visibility.
Account creation: ORGADMIN can provision new accounts within the organization quota and policy.
Account listing: ORGADMIN can view organization accounts for governance and billing oversight.
Replication enablement: ORGADMIN enables database replication across accounts, a cross-account organization concern.
Naming and deletion limits: renaming organization or accounts and deleting accounts require higher or different authority.
Why BCF is correct
Organization documentation assigns to ORGADMIN exactly the abilities to create accounts, view the account list and enable replication. Those map to B, C and F as the settled trio. Renames and deletions are excluded from that list, which is why A, D and E are not part of the key despite sounding administrative.
Why the others are wrong
A. Changing organization name is not an ORGADMIN capability.
D. Changing account name is not granted to ORGADMIN.
E. Deleting an account is outside ORGADMIN privileges.
SnowPro-Advanced-Architect exam tip
ORGADMIN equals create plus list plus enable replication, never rename or delete.
3A retail company has over 3000 stores all using the same Point Of Sale (POS) system. The company wants to deliver near real-time sales results to category managers. The stores operate in a variety of time zones and exhibit a dynamic range of transactions each minute, with some stores having higher sales volumes than others.
Sales results are provided in a uniform fashion using data engineered fields that will be calculated in a complex data pipeline. Calculations include exceptions, aggregations, and scoring using external functions interfaced to scoring algorithms. The source data for aggregations has over 100M rows.
Every minute, the POS sends all sales transactions files to a cloud storage location with a naming convention that includes store numbers and timestamps to identify the set of transactions contained in the files. The files are typically less than 10MB in size.
How can the near real-time results be provided to the category managers? (Choose two.)
All files should be concatenated before ingestion into Snowflake to avoid micro-ingestion.
A Snowpipe should be created and configured with AUTO_INGEST = TRUE. A stream should be created to process INSERTS into a single target table using the stream metadata to inform the store number and timestamps.
A STREAM should be created to accumulate the near real-time data and a TASK should be created that runs at a frequency that matches the real-time analytics needs.
An external scheduler should examine the contents of the cloud storage location and issue SnowSQL commands to process the data at a frequency that matches the real-time analytics needs.
The COPY INTO command with a task scheduled to run every second should be used to achieve the near-real time requirement.
Answer: B, C
The short version
BC — Auto-ingest plus stream-plus-task handles tiny high-frequency POS files. Snowpipe with AUTO_INGEST absorbs thousands of sub-10MB files per minute without manual orchestration, while a stream plus scheduled task turns inserts into engineered aggregates. Together they cover continuous load plus transform for category managers.
Key concepts in this question
Snowpipe AUTO_INGEST: serverless loader driven by storage events, ideal for many small files where COPY would be delayed.
STREAM on target table: captures only new rows with METADATA markers, letting the pipeline process the fresh batch.
TASK scheduling: serverless scheduler running transform SQL on a cadence matching the near real-time SLA.
Micro-ingestion tradeoff: concatenating files adds latency and operational coupling, defeating the per-minute freshness goal for 3000 stores.
External scheduler and per-second COPY: add fragility and cost, since SnowSQL polling cannot match event-driven scale and tasks cannot tick every second.
Why BC is correct
The workload is small files with uneven rates and sub-minute freshness, the textbook Snowpipe AUTO_INGEST pattern. Once landed, the engineered pipeline with external-function scoring needs incremental input, which a stream provides, plus a task to run it. B covers the ingest half and C covers the transform half, and examiners intend both halves as required building blocks.
Why the others are wrong
A. Pre-concatenating files adds latency and manual work and defeats event-driven near real-time delivery.
D. External scheduler plus SnowSQL polling reintroduces ops burden and lag versus native event notifications.
E. COPY with a per-second task is not supported at that frequency and would keep a warehouse needlessly busy.
SnowPro-Advanced-Architect exam tip
When you see thousands of tiny files plus per-minute SLA, pick Snowpipe auto-ingest for load and stream plus task for transform.
4A company needs to share its product catalog data with one of its partners. The product catalog data is stored in two database tables: PRODUCT_CATEGORY, and PRODUCT_DETAILS. Both tables can be joined by the PRODUCT_ID column. Data access should be governed, and only the partner should have access to the records.
The partner is not a Snowflake customer. The partner uses Amazon S3 for cloud storage.
Which design will be the MOST cost-effective and secure, while using the required Snowflake features?
Use Secure Data Sharing with an S3 bucket as a destination.
Publish PRODUCT_CATEGORY and PRODUCT_DETAILS data sets on the Snowflake Marketplace.
Create a database user for the partner and give them access to the required data sets.
Create a reader account for the partner and share the data sets as secure views.
Answer: D
The short version
D — Reader account plus secure views serves a non-Snowflake S3-based partner. Reader accounts give governed compute to consumers without Snowflake accounts, while secure views join PRODUCT tables and enforce row scoping. Marketplace, direct sharing to S3 and local users all miss the partner constraint.
Key concepts in this question
Reader account: Snowflake-managed account for consumers without their own Snowflake deployment, billed to the provider.
Secure views: governed join of PRODUCT_CATEGORY and PRODUCT_DETAILS on PRODUCT_ID with hidden logic and filtered rows.
Non-customer constraint: partner has no Snowflake account, so direct share to an account is impossible without a reader.
S3 destination myth: Secure Data Sharing does not deliver to S3 buckets as a share target.
Local user risk: creating a database user for external parties bypasses reader isolation and governance.
Why D is correct
Only a reader account creates a Snowflake footprint the S3-based partner can use without becoming a customer, and secure views satisfy the governed-access plus join requirement in one object. Marketplace publication is overkill and insecure for a single partner, while S3-as-share-target misunderstands sharing mechanics. That combination is the most cost-effective secure pattern.
Why the others are wrong
A. Shares cannot target S3 buckets as destinations.
B. Marketplace listing is excessive for one governed partner relationship.
C. Local database users for external partners weaken isolation versus reader accounts.
SnowPro-Advanced-Architect exam tip
Non-Snowflake partner plus governed join equals reader account plus secure view.
5A company has a Snowflake environment running in AWS us-west-2 (Oregon). The company needs to share data privately with a customer who is running their Snowflake environment in Azure East US 2 (Virginia).
What is the recommended sequence of operations that must be followed to meet this requirement?
1. Create a share and add the database privileges to the share
2. Create a new listing on the Snowflake Marketplace
3. Alter the listing and add the share
4. Instruct the customer to subscribe to the listing on the Snowflake Marketplace
1. Ask the customer to create a new Snowflake account in Azure EAST US 2 (Virginia)
2. Create a share and add the database privileges to the share
3. Alter the share and add the customer's Snowflake account to the share
1. Create a new Snowflake account in Azure East US 2 (Virginia)
2. Set up replication between AWS us-west-2 (Oregon) and Azure East US 2 (Virginia) for the database objects to be shared
3. Create a share and add the database privileges to the share
4. Alter the share and add the customer's Snowflake account to the share
1. Create a reader account in Azure East US 2 (Virginia)
2. Create a share and add the database privileges to the share
3. Add the reader account to the share
4. Share the reader account's URL and credentials with the customer
Answer: C
The short version
C — Cross-cloud private sharing needs a local account plus replication then share. AWS Oregon to Azure Virginia cannot share directly, so providers replicate to a matching Azure account before sharing. Marketplace, same-account adds and reader-account shortcuts skip that prerequisite.
Key concepts in this question
Cross-cloud sharing limit: direct Secure Data Sharing requires same cloud and region topology or a replication bridge.
Provider-side replica account: new account in Azure East US 2 that hosts the replicated database near the consumer.
Replication step: keeps objects synchronized so the Azure-side share sees fresh data without manual copies.
Share and add privileges: final steps grant database privileges to the share and add the customer account.
Marketplace and reader detours: neither satisfies the documented cross-region cross-cloud sequence on its own.
Why C is correct
Secure sharing across regions and platforms documentation prescribes creating an account in the consumer region and cloud, replicating the objects there, then sharing locally. Option C reproduces that four-step sequence exactly. The alternatives omit replication or substitute marketplace and reader mechanics that do not bridge cloud boundaries.
Why the others are wrong
A. Marketplace listing alone does not bridge AWS to Azure without replication.
B. Asking the customer to make an account plus direct share skips the provider replica requirement.
D. Reader account in Virginia without replication still leaves the cross-cloud gap unresolved.
SnowPro-Advanced-Architect exam tip
Cross-cloud share equals new provider account plus replicate plus share locally.
6Company A has recently acquired company B. The Snowflake deployment for company B is located in the Azure West Europe region.
As part of the integration process, an Architect has been asked to consolidate company B's sales data into company A's Snowflake account which is located in the AWS us-east-1 region.
How can this requirement be met?
Replicate the sales data from company B's Snowflake account into company A's Snowflake account using cross-region data replication within Snowflake. Configure a direct share from company B's account to company A's account.
Export the sales data from company B's Snowflake account as CSV files, and transfer the files to company A's Snowflake account. Import the data using Snowflake's data loading capabilities.
Migrate company B's Snowflake deployment to the same region as company A's Snowflake deployment, ensuring data locality. Then perform a direct database-to-database merge of the sales data.
Build a custom data pipeline using Azure Data Factory or a similar tool to extract the sales data from company B's Snowflake account. Transform the data, then load it into company A's Snowflake account.
Answer: A
The short version
A — Cross-cloud replication plus share consolidates Azure West Europe into AWS us-east-1. Snowflake database replication can copy data across regions and across clouds, after which a direct share or a local replica serves Company A consumers. That avoids manual extract, custom ETL and disruptive region migration.
Key concepts in this question
Database replication: native Snowflake capability to replicate databases across regions and cloud providers for consolidation and disaster recovery.
Secure Data Sharing: zero-copy sharing where consumers create databases from shares without moving files, preserving governance.
Cross-cloud path: Azure West Europe to AWS us-east-1 is supported by replication, handling storage and metadata transfer transparently.
CSV export alternative: manual unload, transfer and reload loses freshness, adds failure modes and forfeits zero-copy efficiency.
Region migration: moving an entire deployment just to merge sales data is disruptive and unnecessary when replication exists.
Why A is correct
Snowflake supports cross-region cross-cloud database replication, so Company B sales data can be replicated into Company A in AWS us-east-1. Once replicated, Company A can consume it directly or via a share, meeting the consolidation goal with native freshness and security. The other paths add manual steps or rebuilds that an Architect would reject for a one-time consolidation that must stay maintainable.
Why the others are wrong
B. CSV export plus reload is manual, slow and error-prone compared with native replication.
C. Migrating the whole deployment region just to merge one dataset is excessive and disruptive.
D. Custom Azure Data Factory pipeline adds build and maintenance cost with no advantage over native replication.
SnowPro-Advanced-Architect exam tip
For cross-region plus cross-cloud consolidation, think replicate first, then share locally.
7A Snowflake Architect created a new data share and would like to verify that only specific records in secure views are visible within the data share by the consumers.
What is the recommended way to validate data accessibility by the consumers?
Create reader accounts as shown below and impersonate the consumers by logging in with their credentials.
create managed account reader_acct1 admin_name = user1 , admin_password = 'Sdfed43da!44' , type = reader;
Create a row access policy as shown below and assign it to the data share.
create or replace row access policy rap_acct as (acct_id varchar) returns boolean -> case when 'acct1_role' = current_role() then true else false end;
Set the session parameter called SIMULATED_DATA_SHARING_CONSUMER as shown below in order to impersonate the consumer accounts.
alter session set simulated_data_sharing_consumer = 'Consumer Acct1'
Alter the share settings as shown below, in order to impersonate a specific consumer account.
alter share sales_share set accounts = 'Consumer1' share_restrictions = true
Answer: C
The short version
C — SIMULATED_DATA_SHARING_CONSUMER validates secure-view filtering as each consumer sees it. Setting that session parameter makes shared secure views return rows as if queried inside the named consumer account. It is the documented lightweight way to verify row filtering without creating reader accounts or logging in as consumers.
Key concepts in this question
Secure views: views whose optimizer and access rules prevent consumers from seeing underlying logic or bypassing filters, required for controlled sharing.
SIMULATED_DATA_SHARING_CONSUMER: session parameter that simulates a consumer context for testing shared objects, especially CURRENT_ACCOUNT and related functions.
Reader accounts: managed accounts for non-Snowflake consumers, heavier than needed for a quick validation by the provider.
Row access policy on share: policies attach to tables and views, not to the share object itself, so the described syntax is invalid.
ALTER SHARE accounts pattern: controls which accounts are added, not impersonation for testing visibility.
Why C is correct
Snowflake provides SIMULATED_DATA_SHARING_CONSUMER so providers can test what a named consumer sees from secure views without switching accounts. When set, functions depending on consumer identity evaluate as that consumer, exposing whether only intended records appear. That matches the need to verify specific-record visibility quickly and safely, which is why docs and community converge on C.
Why the others are wrong
A. Creating reader accounts and impersonating logins is heavyweight and unnecessary for validation.
B. Row access policies are not assigned to the share object with that syntax.
D. ALTER SHARE with accounts and share_restrictions manages membership, not simulated query context.
SnowPro-Advanced-Architect exam tip
To test what a consumer sees, alter the session with SIMULATED_DATA_SHARING_CONSUMER, not the share.
8A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data in JSON format that is stored in an Amazon S3 bucket in the AWS Singapore region that the team wants to analyze.
The Architect has been given the following requirements:
1. Provide access to frequently changing data
2. Keep egress costs to a minimum
3. Maintain low latency
How can these requirements be met with the LEAST amount of operational overhead?
Use a materialized view on top of an external table against the S3 bucket in AWS Singapore.
Use an external table against the S3 bucket in AWS Singapore and copy the data into transient tables.
Copy the data between providers from S3 to Azure Blob storage to collocate, then use Snowpipe for data ingestion.
Use AWS Transfer Family to replicate data between the S3 bucket in AWS Singapore and an Azure Netherlands Blob storage, then use an external table against the Blob storage.
Answer: C
The short version
Banked answer C is kept. Copying the JSON data from S3 in Singapore to Azure Blob collocated with the Azure Netherlands account and then loading with Snowpipe best meets frequent changes plus low egress plus low latency with least overhead.
Key concepts in this question
Cross cloud and cross region reads incur repeated egress and added latency when compute in Azure Netherlands must scan S3 in Singapore on every query. Collocation means paying transfer once and then querying locally. Snowpipe with auto ingest handles micro batches with event notifications and minimal coding. Materialized views over external tables still scan remote files on refresh.
Why C is correct
Option C collocates storage with compute by moving data between providers from S3 to Azure Blob, so later queries are local and avoid repeated cross cloud egress. Snowpipe then provides continuous ingestion for frequently changing JSON with serverless auto ingest and low operational overhead. This addresses all three needs simply.
Why the others are wrong
Option A with a materialized view over a cross cloud external table still must read Singapore data for each refresh, so egress repeats and latency stays high. Option B with an external table plus copies into transient tables also repeats cross region scans on every copy and adds manual orchestration. Option D uses AWS Transfer Family, which is designed for SFTP style transfers, adding needless service setup, and then still leaves an external table with remote style query overhead instead of a fast native table fed by Snowpipe.
SnowPro-Advanced-Architect exam tip
When the stem names two different clouds or regions plus words like frequently changing plus minimize egress plus low latency, choose collocate storage with compute and then use Snowpipe auto ingest. Eliminate any answer that leaves repeated cross cloud queries via external tables when a native collocated load is offered.
9Based on the Snowflake object hierarchy, what securable objects belong directly to a Snowflake account? (Choose three.)
Database
Schema
Table
Stage
Role
Warehouse
Answer: A, E, F
The short version
AEF — Databases, roles and warehouses attach directly to the account. In the Snowflake hierarchy those three are account-level securables, while schemas, tables and stages nest deeper. That placement drives grant scopes and ownership chains.
Key concepts in this question
Account-level objects: databases for storage, roles for access and warehouses for compute all belong to the account root.
Database children: schemas live inside databases, not directly under accounts.
Schema children: tables and stages live inside schemas, two levels below the account.
Grant scoping: knowing direct children predicts where USAGE and OWNERSHIP grants apply.
Hierarchy questions: exam often tests one-level versus two-level nesting to catch memorization gaps.
Why AEF is correct
Object hierarchy diagrams place database, role and warehouse as immediate children of the account, with schema beneath database and table and stage beneath schema. That yields exactly A, E and F as direct account members. The other options sit at lower levels and therefore cannot be direct account securables.
Why the others are wrong
B. Schema belongs to a database, not directly to the account.
C. Table belongs to a schema, two levels below the account.
D. Stage belongs to a schema, not directly to the account.
SnowPro-Advanced-Architect exam tip
Direct account children are database plus role plus warehouse, everything else nests deeper.
10What is a characteristic of Role-Based Access Control (RBAC) as used in Snowflake?
Privileges can be granted at the database level and can be inherited by all underlying objects.
A user can use a "super-user" access along with SECURITYADMIN to bypass authorization checks and access all databases, schemas, and underlying objects.
A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles.
A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles.
Answer: C
The short version
C — Managed access schemas centralize grant authority in the schema owner. Object creators in such schemas cannot grant access on their own tables, so every grant flows through the schema owner or a MANAGE GRANTS holder, which simplifies control of shared data sets.
Key concepts in this question
Snowflake RBAC: Privileges go to roles and roles go to users, with ownership carrying grant authority under standard schemas.
Managed access schema: A schema option that strips grant power from object owners to enforce centralized administration.
Schema owner authority: Only the OWNERSHIP holder on the schema, or a MANAGE GRANTS role, may grant on current and future objects inside.
Future grants: Rules defined once on the schema that auto-apply to tables created later, vital for evolving pipelines.
Centralized governance: One accountable owner replaces many independent object owners, reducing drift and accidental sharing.
Why C is correct
C matches Snowflake behavior because managed access preserves future grants while moving grant power to the schema owner. A role with CREATE TABLE can still create objects, yet its GRANT SELECT on those objects fails unless it also owns the schema or holds MANAGE GRANTS. Architects therefore define future grants once on the schema, giving a reader role automatic SELECT on each new table without per-object statements, exactly as C describes.
Why the others are wrong
A. Database-level grants do not create the described inheritance, and the option says nothing about centralized grant control.
B. No super-user bypass exists beside SECURITYADMIN, since all queries still pass authorization checks.
D. D keeps grant power with object owners, which is the opposite of managed access and the precise error the feature removes.
SnowPro-Advanced-Architect exam tip
Remember managed equals manager: object owners build, only the schema owner shares.