Question bank
SnowPro Advanced: Architect
41 SnowPro Advanced: Architect questions with the correct answer and a written explanation, organised across 4 exam domains.
- 41 questions
- 4 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Accounts & Security
10 questions in this domain
- Question 1Single Choice
Which statement best describes idempotency in a data pipeline?
- ARe-running the same load produces the same result without duplication.Correct answer
- BThe pipeline can run only once and will never fail.
- CThe pipeline automatically scales to handle large workloads.
- DData is encrypted during transit and at rest.
Why this is the answer
Idempotency in pipelines ensures that repeated executions do not duplicate data, using techniques such as metadata tracking and staging tables.
- Question 2Multiple Select
Which two techniques are commonly used to make Snowflake pipelines idempotent?
- AUsing metadata tracking to record already-processed filesCorrect answer
- BUsing staging tables to deduplicate before final insertCorrect answer
- CDisabling all Tasks after a failed pipeline run
- DRelying solely on random file names in external storage
Why this is the answer
Metadata tracking and staging tables are recommended to ensure re-runs are idempotent; disabling tasks or using random file names does not provide consistency.
- Question 3Interactive
Review the SQL statement in the code block. Which Snowflake object is created and what is its main purpose?
- AA stream that captures DML changes on the orders tableCorrect answer
- BA task that runs scheduled SQL every minute
- CA pipe that loads files from an external stage
- DA dynamic table that refreshes automatically
Why this is the answer
CREATE STREAM creates a Snowflake Stream, which captures DML changes and supports change data capture for incremental processing.
- Question 4Single Choice
What do Snowflake Streams provide for downstream pipelines?
- AIncremental processing by capturing DML changesCorrect answer
- BA permanent audit log of all historical transactions
- CAutomatic query acceleration for large scans
- DReplacement for virtual warehouses
Why this is the answer
Streams enable incremental processing by capturing DML changes, reducing the need for full table scans.
- Question 5Multiple Select
Which two Snowflake features are used for orchestration and scheduled transformation logic?
- ATasksCorrect answer
- BDynamic TablesCorrect answer
- CPipes
- DStreams
Why this is the answer
Tasks provide scheduled or conditional execution, while Dynamic Tables provide declarative continuous refresh. Pipes handle loading; Streams handle CDC.
- Question 6Interactive
The code block shows a scheduling object being created and enabled. What type of Snowflake object is it?
- AA Snowflake TaskCorrect answer
- BA Snowpipe
- CA Stream
- DA Dynamic Table
Why this is the answer
CREATE TASK and ALTER TASK RESUME create and enable a Snowflake Task for scheduled SQL execution.
- Question 7Single Choice
What is required to enable automated Snowpipe ingestion from cloud storage?
- AA cloud messaging service such as SQS or Event GridCorrect answer
- BA dedicated multi-cluster warehouse
- CA Dynamic Table with a refresh schedule
- DA Snowflake Task running every minute
Why this is the answer
Automated Snowpipe requires a cloud notification service like SQS or Event Grid to signal the arrival of new files.
- Question 8Multiple Select
Which two statements about Snowpipe compute are correct?
- AIt uses Snowflake-managed compute resourcesCorrect answer
- BIts compute is billed using serverless compute creditsCorrect answer
- CIt uses a user-provisioned virtual warehouse
- DIt requires an always-running warehouse
Why this is the answer
Snowpipe uses Snowflake-managed serverless compute and is billed through serverless credits, not virtual warehouse billing.
- Question 9Interactive
The function in the code block is used for which observability purpose?
- AStatus and error information for a SnowpipeCorrect answer
- BThe number of rows loaded by a COPY command
- CThe current warehouse credit usage
- DThe last query ID on a task
Why this is the answer
SYSTEM$PIPE_STATUS returns pipe status and error information, helping architects monitor continuous ingestion pipelines.
- Question 10Single Choice
What is the result of multiple downstream processes consuming the same Stream without a branching strategy?
- AA Stream's changes are lost after consumption, causing data lossCorrect answer
- BSnowpipe stops ingesting files
- CAutomatic clustering is disabled
- DA Task will run indefinitely
Why this is the answer
A Stream is consumed when read; without proper branching, a second consumer may miss changes, leading to data loss.
Data Engineering
10 questions in this domain
- Question 11Single Choice
What does idempotency guarantee in a data pipeline?
- ARe-running the same load produces the same result without duplicate dataCorrect answer
- BThe pipeline automatically retries failed tasks every second
- CSnowpipe uses the customer-provisioned virtual warehouse
- DAll semi-structured data is flattened immediately upon ingestion
Why this is the answer
Idempotency ensures that re-running a load yields the same final state without duplication, typically using metadata tracking or staging tables.
- Question 12Multiple Select
Which statements about Snowflake Streams for Change Data Capture are true?
- AThey capture DML changes on tables or viewsCorrect answer
- BThey enable incremental processing and reduce compute overheadCorrect answer
- CThey are consumed when read by a downstream DML operationCorrect answer
- DThey provide a permanent, exhaustive log of every change forever
Why this is the answer
Streams capture changes, allow incremental processing, and are consumed upon use; they are not permanent logs.
- Question 13Interactive
The SQL statement shown is used to accomplish which task?
- AMonitor the status of a pipe and detect errorsCorrect answer
- BCreate a new pipe object
- CValidate a COPY statement before loading
- DDrop an existing pipe permanently
Why this is the answer
SYSTEM$PIPE_STATUS returns metadata about a pipe, enabling architects to monitor its state and identify ingestion problems.
- Question 14Single Choice
How is Snowpipe compute usage billed?
- AServerless compute creditsCorrect answer
- BVirtual warehouse credits
- CStorage credits
- DData transfer credits
Why this is the answer
Snowpipe uses Snowflake-managed compute and is billed through serverless compute credits, not customer virtual warehouses.
- Question 15Multiple Select
Which of the following are recognized traps when using Snowpipe for ingestion?
- AAssuming Snowpipe uses the user's virtual warehouseCorrect answer
- BOverlooking the need for a cloud messaging service such as SQS or Event GridCorrect answer
- CUsing Snowpipe for massive, bulk historical migrationsCorrect answer
- DAssuming a Stream can be consumed by multiple tasks without data loss
Why this is the answer
Snowpipe uses managed compute, requires notifications, and is not suited for multi-terabyte initial loads; Streams also have consumption limitations.
- Question 16Interactive
What does the SQL statement shown create?
- AA stream object that captures changes to the tableCorrect answer
- BA pipe for automatically loading external files
- CA task with a scheduled execution
- DA dynamic table that refreshes continuously
Why this is the answer
CREATE STREAM creates a stream object that records DML changes on the specified table for incremental processing.
- Question 17Single Choice
What is a key characteristic of Dynamic Tables in Snowflake?
- AThey provide declarative, continuous data transformationCorrect answer
- BThey require procedural conditional logic for each refresh
- CThey replace the need for Snowflake Tasks entirely
- DThey only work with external cloud storage stages
Why this is the answer
Dynamic Tables use a declarative model where Snowflake manages continuous refreshes automatically, contrasting with procedural Tasks.
- Question 18Multiple Select
Why is the Star Schema generally preferred over the Snowflake Schema in Snowflake analytical workloads?
- AIt minimizes the depth of joinsCorrect answer
- BIt reduces the complexity of the execution planCorrect answer
- CIt helps the optimizer use pruning techniques across large fact tablesCorrect answer
- DIt eliminates the need for clustering keys on all tables
Why this is the answer
Star Schema reduces join depth, simplifies execution plans, and improves the optimizer's ability to prune large fact tables.
- Question 19Interactive
The SQL statement shown represents which data loading pattern?
- APull-based batch loading using COPY INTOCorrect answer
- BPush-based continuous ingestion using Snowpipe
- CEvent-driven loading triggered by SQS notifications
- DStream-based replication from an OLTP source
Why this is the answer
The COPY INTO command is a pull-based batch loading pattern, typically scheduled for large-scale or periodic loads.
- Question 20Single Choice
Which Slowly Changing Dimension type moves history to a separate table?
- AType 4Correct answer
- BType 1
- CType 2
- DType 0
Why this is the answer
SCD Type 4 moves history to a separate table, while Type 2 adds columns to the main dimension.
Performance Optimization
8 questions in this domain
- Question 21Single Choice
In the Snowflake data loading model, which type of compute resources are used when Snowpipe executes an ingestion workload?
- ASnowflake-managed serverless computeCorrect answer
- BCustomer-provisioned virtual warehouse
- CDedicated multi-cluster warehouse
- DUser-specified warehouse from the COPY statement
Why this is the answer
Snowpipe ingests data using Snowflake-managed compute, not a customer virtual warehouse, and is billed through serverless credits.
- Question 22Multiple Select
To automate Snowpipe so that file arrival in a stage triggers a load, which two components must be configured?
- AA cloud messaging service (SQS or Event Grid)Correct answer
- BA PIPE object with AUTO_INGEST enabledCorrect answer
- CA virtual warehouse assigned to the pipe
- DA scheduled task invoking the pipe
Why this is the answer
Automated Snowpipe requires an external notification mechanism and a PIPE object; it does not use a user-provisioned warehouse or scheduled task.
- Question 23Interactive
The SQL defines a scheduled task that reads from a stream. What happens to the stream after the task successfully executes?
- AStream offset advances and prior changes are hiddenCorrect answer
- BRemains unchanged until a SELECT reads it
- CDropped automatically
- DKeeps all records for the next run
Why this is the answer
A stream is consumed by DML operations; after the INSERT executes, the offset advances and the previous changes are no longer visible.
- Question 24Single Choice
For files that have already been loaded by a Snowpipe, what delivery semantics does the internal metadata guarantee?
- AExactly-once deliveryCorrect answer
- BAt-least-once delivery
- CAt-most-once with manual deduplication
- DNo deduplication guarantee
Why this is the answer
Snowpipe tracks file metadata to prevent duplicate ingestion, providing exactly-once delivery for files already processed.
- Question 25Multiple Select
Which two statements are accurate when describing how Snowpipe operates and should be charged?
- AIt uses Snowflake-managed compute creditsCorrect answer
- BLarger micro-batches are more cost-effectiveCorrect answer
- CIt runs on the user's virtual warehouse
- DIt is ideal for multi-terabyte initial migrations
Why this is the answer
Snowpipe uses serverless compute, and aggregating files into larger micro-batches reduces cost; bulk historical migrations should use COPY INTO.
- Question 26Interactive
The function shown returns pipe diagnostic information. Which two fields are most useful for detecting an ingestion failure?
- AstatusCorrect answer
- BlastErrorCorrect answer
- CpipeName
- Downer
Why this is the answer
In SYSTEM$PIPE_STATUS, status and lastError indicate whether the pipe is running and if an error has occurred.
- Question 27Single Choice
For analytical query performance in Snowflake, which schema design is generally preferred over a normalized model?
- AStar SchemaCorrect answer
- BSnowflake Schema
- CData Vault
- DWide Table
Why this is the answer
Star schema is generally preferred in Snowflake because it reduces join depth and lets the optimizer prune data more effectively.
- Question 28Multiple Select
Which two statements correctly describe the difference between SCD Type 2 and SCD Type 4 implementations?
- AType 2 adds rows to the main dimensionCorrect answer
- BType 4 stores history in a separate tableCorrect answer
- CType 2 stores history only in a separate table
- DType 4 adds columns to the main dimension
Why this is the answer
SCD Type 2 tracks history in the main dimension, while Type 4 moves history to a separate table, preventing dimension bloat.
Snowflake Architecture
13 questions in this domain
- Question 29Single Choice
In Snowflake pipeline design, what does it mean for a data load to be idempotent?
- ARe-running load produces same result without duplicatesCorrect answer
- BLoad always scans the entire table
- CPipeline uses external orchestration only
- DPipeline never encounters failures
Why this is the answer
Idempotency means re-running the same load yields the same target state, preventing duplicate or corrupted results.
- Question 30Multiple Select
Which two statements are true about using Snowflake Streams for change data capture in a pipeline?
- AStreams capture DML changes for incremental processingCorrect answer
- BAll downstream readers consume streams automatically
- CStreams reduce compute overhead versus full scansCorrect answer
- DStreams permanently retain all change records
Why this is the answer
Streams capture DML changes for incremental pipelines, but they are consumed by reads and are not permanent logs.
- Question 31Interactive
Review the PIPE object in the code block. What is its main purpose for continuous ingestion?
- AEncapsulates COPY statement and integration detailsCorrect answer
- BProvisions a virtual warehouse
- CCreates a stream for change tracking
- DFlattens semi-structured data
Why this is the answer
A PIPE object encapsulates the COPY statement, integration details, and load metadata, preventing duplicate ingestion of processed files.
- Question 32Single Choice
Which Snowflake feature provides declarative continuous data transformation without the need for procedural task code?
- ATasks
- BStreams
- CDynamic TablesCorrect answer
- DSnowpipe
Why this is the answer
Dynamic Tables provide declarative, set-based continuous refresh and simplify transformation pipelines compared to procedural Tasks.
- Question 33Multiple Select
Which two statements correctly compare Snowpipe and COPY INTO for data loading pipelines in Snowflake?
- ASnowpipe is best for near real-time micro-batchesCorrect answer
- BCOPY INTO is best for large batch loadsCorrect answer
- CSnowpipe is always cheaper than COPY INTO
- DCOPY INTO requires a cloud notification service
Why this is the answer
Snowpipe is event-driven and suited for low latency micro-batches; COPY INTO is pull-based and better for large bulk loads.
- Question 34Interactive
The function in the code block inspects a pipe. What kind of information does it return?
- ACurrent pipe status and pending file countCorrect answer
- BQuery execution plan details
- CUser login and session history
- DDaily warehouse credit usage
Why this is the answer
SYSTEM$PIPE_STATUS returns pipe status and pending file counts, which helps monitor continuous ingestion overall health.
- Question 35Single Choice
Why can multiple downstream processes fail when they share a single Snowflake Stream without branching?
- AStream automatically duplicates all changes
- BOne consumer consumes changes; others miss themCorrect answer
- CStream becomes read-only after first read
- DStream grows until manually truncated
Why this is the answer
Streams are consumed when read; without a branching strategy, one consumer can consume changes another still needs.
- Question 36Multiple Select
Which two components are required for automated, event-driven Snowpipe ingestion from external cloud storage?
- ACloud messaging service such as SQSCorrect answer
- BStorage notification from the cloud providerCorrect answer
- CUser-managed virtual warehouse
- DScheduled task from Snowflake
Why this is the answer
Automated Snowpipe requires cloud storage notifications and a messaging service; it does not require a user warehouse.
- Question 37Interactive
The Task in the code block uses a CRON schedule. What execution time is defined?
- AOnce every day at 2:00 AMCorrect answer
- BOnce every hour at 2 minutes past
- CEvery two seconds continuously
- DOnly on Mondays at 2:00 PM
Why this is the answer
The CRON expression `0 2 * * *` schedules the task to run once every day at 02:00 AM, which is a daily schedule.
- Question 38Single Choice
Which situation best illustrates the over-engineering trap in a modern Snowflake analytical pipeline architecture?
- AUsing external Airflow when Tasks and Streams sufficeCorrect answer
- BUsing Snowpipe for near real-time ingestion
- CUsing COPY INTO for initial historical migration
- DUsing Dynamic Tables for continuous refresh
Why this is the answer
The over-engineering trap is selecting a complex external orchestrator when Snowflake-native Tasks and Streams are simpler and cheaper.
- Question 39Multiple Select
Which two practices help identify continuous pipeline stalls and schema mismatches in Snowflake?
- AMonitoring pipe status with SYSTEM$PIPE_STATUSCorrect answer
- BUsing VALIDATION_MODE to catch schema issuesCorrect answer
- CDisabling error notifications on all pipes
- DIgnoring task history for all scheduled jobs
Why this is the answer
SYSTEM$PIPE_STATUS and VALIDATION_MODE allow architects to catch pipe stalls and schema mismatches before BI is affected.
- Question 40Interactive
The query in the code block references a denormalized fact table. How does columnar storage help?
- AOnly referenced columns are scannedCorrect answer
- BEvery column in the table is scanned
- CCompression is disabled for all fact tables
- DClustering key is required for query execution
Why this is the answer
Columnar storage scans only columns referenced by the query, which makes wide denormalized tables efficient for analytics.
- Question 41Single Choice
What is a primary benefit of using Data Vault modeling for enterprise-scale integration in Snowflake?
- AHigh agility and auditability with parallel loadingCorrect answer
- BEliminates all query joins
- CRequires only one source system
- DPrevents all historical tracking
Why this is the answer
Data Vault supports agile integration, parallel loading, and scalable historical tracking without redesigning the core model.
Frequently asked questions
Everything about the SnowPro Advanced: Architect question bank and how to use it.