Question bank
SnowPro Advanced: Data Engineer
42 SnowPro Advanced: Data Engineer questions with the correct answer and a written explanation, organised across 5 exam domains.
- 42 questions
- 5 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Data Governance
6 questions in this domain
- Question 1Single Choice
What is the primary function of a row access policy in Snowflake's data governance framework?
- AFilters rows based on user contextCorrect answer
- BMasks column values dynamically
- CCopies data to a new table
- DEncrypts data at rest
Why this is the answer
A row access policy is a schema-level object that conditionally filters which rows are visible based on the executing user's role or attributes.
- Question 2Multiple Select
Which statements correctly describe how Snowflake masking policies operate on sensitive data? Select two that apply.
- AThey are evaluated at query runtimeCorrect answer
- BThey permanently alter stored data
- CThey can be associated with tagsCorrect answer
- DThey only apply to external tables
Why this is the answer
Masking policies dynamically transform column values at query time and can be linked to tags; they do not change physical storage.
- Question 3Interactive
Given the following SQL masking policy definition, what does this policy do when a user queries the column?
- AUnmasked only for compliance auditorCorrect answer
- BMasked for all users
- CUnmasked for all users
- DReturns null for compliance auditor
Why this is the answer
The policy returns the original value only when the current role is COMPLIANCE_AUDITOR; all other roles see 'REDACTED'.
- Question 4Single Choice
Which privilege is required to apply a masking policy to a column in a Snowflake table?
- AAPPLY MASKING POLICYCorrect answer
- BSELECT
- CCREATE TABLE
- DUSAGE
Why this is the answer
To assign a masking policy, the role needs the APPLY MASKING POLICY privilege or ownership on the policy and target.
- Question 5Multiple Select
Which two statements about Snowflake object tags and the system classification engine are accurate? Select two.
- ATags are key-value metadata pairsCorrect answer
- BClassification detects PII automaticallyCorrect answer
- CTag inheritance propagates from child to parent
- DTags cannot enforce masking policies
Why this is the answer
Tags store metadata as key-value pairs, and Snowflake classification automatically detects PII. Inheritance flows downward, and tags can trigger masking policies.
- Question 6Interactive
Which privilege is required to execute the following SQL command that assigns a tag to a column?
- AAPPLY TAGCorrect answer
- BSELECT
- CCREATE SCHEMA
- DMANAGE GRANTS
Why this is the answer
Applying a tag to a column requires the APPLY TAG privilege on the account or ownership of the object.
Data Movement
12 questions in this domain
- Question 7Single Choice
What is the primary purpose of the COPY INTO command in Snowflake?
- ABulk load data from stages into tablesCorrect answer
- BCreate external tables from cloud storage
- CContinuously stream row-level records into Snowflake
- DPurge stale data from Snowflake tables
Why this is the answer
COPY INTO is the foundational high-performance DML for bulk loading data from internal or external stages into Snowflake tables.
- Question 8Multiple Select
Which of the following are valid internal stage types in Snowflake?
- AUser stageCorrect answer
- BTable stageCorrect answer
- CNamed internal stageCorrect answer
- DExternal S3 stage
Why this is the answer
Internal stages include user, table, and named internal stages. External stages reference customer-managed cloud storage such as S3.
- Question 9Interactive
Given the provided COPY INTO SQL statement, what happens when a stage file has already been loaded successfully?
- ASkip previously loaded files based on load historyCorrect answer
- BLoad all files again because metadata is not checked
- CDelete source files from the stage
- DAbort with an error due to duplicate file names
Why this is the answer
Snowflake's load history prevents duplicate loads; files already loaded are skipped automatically unless FORCE = TRUE is specified.
- Question 10Single Choice
How long does Snowflake retain COPY INTO load history metadata for a target table?
- A14 days
- B64 daysCorrect answer
- C90 days
- D365 days
Why this is the answer
Snowflake tracks loaded files using load history metadata maintained for each target table for up to 64 days.
- Question 11Multiple Select
Which file formats are columnar and natively supported by Snowflake?
- AParquetCorrect answer
- BORCCorrect answer
- CJSON
- DAvro
Why this is the answer
Parquet and ORC are columnar formats supported by Snowflake. JSON and Avro are not columnar formats.
- Question 12Interactive
In the provided COPY INTO command, what is the effect of ON_ERROR = 'SKIP_FILE'?
- ASkips the malformed file and continues loading other filesCorrect answer
- BSkips invalid rows within a file and loads valid rows
- CAborts the entire statement after the first error
- DDeletes files with errors from the stage
Why this is the answer
ON_ERROR = 'SKIP_FILE' instructs COPY INTO to skip files that contain errors and continue loading the remaining files.
- Question 13Single Choice
Which Snowflake service is a serverless, event-driven file-based ingestion mechanism?
- ASnowpipeCorrect answer
- BSnowpipe Streaming
- CCOPY INTO
- DExternal tables
Why this is the answer
Snowpipe is the serverless, event-driven ingestion service that uses cloud notifications to load files staged in storage.
- Question 14Multiple Select
Which statements about Snowpipe Streaming are true?
- AIt ingests row-level records directly into SnowflakeCorrect answer
- BIt bypasses the need to first write files to a stageCorrect answer
- CIt uses a client-side SDK and open channelsCorrect answer
- DIt requires files to be uploaded to cloud storage before ingestion
Why this is the answer
Snowpipe Streaming pushes row-level data via an SDK directly into Snowflake and does not require staging files first.
- Question 15Interactive
After a new file is added to the external storage location, what must happen for the provided external table to see the new data?
- AThe external table metadata must be refreshed manually or via notification integrationCorrect answer
- BSnowflake automatically detects file changes without configuration
- CThe external table must be dropped and recreated
- DA new stage must be created for each file
Why this is the answer
External table metadata is synchronized manually with ALTER EXTERNAL TABLE REFRESH or automatically through notification integrations.
- Question 16Single Choice
Which DML operations can be executed directly against an external table in Snowflake?
- AINSERT and UPDATE only
- BDELETE and MERGE only
- CNo direct DML; they are read-onlyCorrect answer
- DFull DML support
Why this is the answer
External tables are strictly read-only in Snowflake; they do not support INSERT, UPDATE, DELETE, or MERGE directly.
- Question 17Multiple Select
Which pseudo-columns are available for tracking file lineage in external tables?
- AMETADATA$FILENAMECorrect answer
- BMETADATA$FILE_ROW_NUMBERCorrect answer
- CMETADATA$PARTITION
- DMETADATA$TIMESTAMP
Why this is the answer
External tables expose METADATA$FILENAME and METADATA$FILE_ROW_NUMBER to track source lineage and row position.
- Question 18Interactive
What is the effect of the PARTITION BY clause in the provided COPY INTO statement?
- AWrites files into hive-style partitions based on regionCorrect answer
- BCombines all data into one single file
- CSorts data by region within each file
- DCreates a Snowflake-managed internal table named region
Why this is the answer
PARTITION BY organizes unloaded files into hive-partitioned folder structures based on the specified column values.
Data Transformation
10 questions in this domain
- Question 19Single Choice
When a window function includes an ORDER BY clause but no explicit window frame, which frame does Snowflake use by default?
- ARANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWCorrect answer
- BROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- CRANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- DROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Why this is the answer
When ORDER BY is present and no explicit frame is specified, Snowflake defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which can affect both results and performance compared with ROW-based framing.
- Question 20Multiple Select
Which metadata columns are automatically added to a tracked table by a Snowflake stream?
- AMETADATA$ACTIONCorrect answer
- BMETADATA$ISUPDATECorrect answer
- CMETADATA$ROW_IDCorrect answer
- DMETADATA$TIMESTAMP
Why this is the answer
Snowflake streams add METADATA$ACTION, METADATA$ISUPDATE, and METADATA$ROW_ID to the source table schema to track DML operations and row identity.
- Question 21Interactive
Based on the query shown, what is the running_total returned for order_id = 2?
- A100
- B300Correct answer
- C600
- D200
Why this is the answer
Without an explicit frame, ORDER BY uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the sum for order_id = 2 includes 100 + 200 = 300.
- Question 22Single Choice
Which statement is true about a stale stream in Snowflake?
- AA stale stream cannot be queried, and the underlying data changes are permanently lost.Correct answer
- BA stale stream automatically resets its offset and continues tracking changes.
- CA stale stream can still be queried but cannot be consumed by a DML statement.
- DA stale stream preserves all changes until the underlying table is recreated.
Why this is the answer
When a stream falls behind the Time Travel retention period of its source table, it becomes stale, cannot be queried, and the underlying data changes are permanently lost.
- Question 23Multiple Select
Which statements about append-only streams are true?
- AThey track only row insertions.Correct answer
- BThey can be created on directory tables.Correct answer
- CThey record updates and deletes.
- DThey offer lower overhead for insert-only ingestion pipelines.Correct answer
Why this is the answer
Append-only streams track only INSERT operations, can be created on standard tables or directory tables, and provide lower overhead for write-only ingestion pipelines.
- Question 24Interactive
A Snowflake task DAG contains a root task and a child task. The following resumption commands are executed. Which statement is true?
- AThe resumption order is correct because child tasks must be resumed before their root task.Correct answer
- BThe resumption order is incorrect because the root task must be resumed before the child task.
- CTask resumption order does not matter in Snowflake.
- DOnly the root task needs to be resumed for the entire DAG to execute.
Why this is the answer
To activate a task DAG, you must resume child tasks first and then resume the root task. The shown order follows that requirement.
- Question 25Single Choice
What does SYSTEM$GET_PREDECESSOR_RESULT allow a task to do?
- AIt allows a child task to access output metadata, status flags, or small result sets from completed parent tasks.Correct answer
- BIt returns the complete result set of every task in the account.
- CIt triggers a predecessor task to run again.
- DIt removes failed predecessor tasks from the DAG.
Why this is the answer
SYSTEM$GET_PREDECESSOR_RESULT lets downstream tasks retrieve output metadata, status flags, or small result sets from direct predecessors within the same DAG run.
- Question 26Multiple Select
Which features apply to Snowflake dynamic tables?
- AThey materialize the result of a SQL query.Correct answer
- BSnowflake automatically schedules and refreshes them based on target lag.Correct answer
- CThey can be built on top of other dynamic tables.Correct answer
- DThey require a manual stream and task pipeline for every refresh.
Why this is the answer
Dynamic tables persist query results, refresh automatically, and can depend on other dynamic tables, removing the need for manual stream and task orchestration.
- Question 27Interactive
What does the TARGET_LAG setting mean for this dynamic table?
- ASnowflake refreshes the dynamic table at most one hour after base tables change.Correct answer
- BThe dynamic table is refreshed exactly on the hour regardless of source changes.
- CThe dynamic table takes one hour to run every refresh.
- DThe dynamic table automatically switches to a stream and task after one hour.
Why this is the answer
TARGET_LAG defines the maximum allowed time that the dynamic table content can lag behind updates to base tables, and Snowflake uses it to schedule refreshes.
- Question 28Single Choice
Which execution rights do Snowflake stored procedures use by default?
- AOwner's RightsCorrect answer
- BCaller's Rights
- CInvoker's Rights
- DAdmin Rights
Why this is the answer
Snowflake stored procedures default to Owner's Rights, meaning they execute with the privileges of the procedure owner rather than the calling user.
Performance Optimization
8 questions in this domain
- Question 29Single Choice
Which Snowflake diagnostic tool provides runtime metrics after query execution, such as spilled bytes and operator-level timing?
- AEXPLAIN plan
- BQuery ProfileCorrect answer
- CResource Monitor
- DMetadata Cache
Why this is the answer
The Query Profile visualizes post-execution metrics including operator timing, bytes scanned, and spill to local or remote storage. EXPLAIN only provides a static execution plan without runtime telemetry.
- Question 30Multiple Select
Select the two statements that correctly describe the EXPLAIN plan in Snowflake.
- AIt generates the logical and optimized execution plan without consuming compute resources.Correct answer
- BIt reports actual bytes spilled and memory usage from the last execution.
- CIt may include estimated cardinality and join ordering for the query.Correct answer
- DIt replaces the Query Profile for identifying live concurrency bottlenecks.
Why this is the answer
EXPLAIN shows logical and optimized plans, estimated cardinality, and join order without executing the query. It does not capture runtime metrics such as spilled bytes or concurrency bottlenecks.
- Question 31Interactive
The code block contains a metadata-only query. Which Snowflake capability resolves it from stored statistics rather than scanning micro-partitions?
- AMetadata CacheCorrect answer
- BSearch Optimization Service
- CAutomatic Clustering
- DMaterialized View
Why this is the answer
The Metadata Cache stores structural, statistical, and partition-level information that allows Snowflake to resolve metadata-only operations without scanning micro-partitions or provisioning a virtual warehouse.
- Question 32Single Choice
During query profile review, a large volume of bytes spilled to remote storage is an indicator of which condition?
- ASevere memory exhaustion requiring a larger warehouse sizeCorrect answer
- BA healthy execution plan with efficient memory use
- CEffective partition pruning with low cluster overlap
- DSuccessful result cache reuse
Why this is the answer
Remote storage spills indicate intermediate results exceeded available memory, requiring more memory or compute through a larger warehouse size or query tuning.
- Question 33Multiple Select
Select the two approaches that directly relieve query queuing caused by high user concurrency.
- AEnabling multi-cluster warehouses with Auto-ScaleCorrect answer
- BConfiguring resource monitors to track credit usage
- CScaling the warehouse horizontally by adding clustersCorrect answer
- DScaling a single warehouse to a larger size
Why this is the answer
Query queuing caused by high concurrency is solved by horizontal scaling, such as multi-cluster warehouses. Resource monitors control cost, and vertical scaling speeds individual queries but does not remove queuing.
- Question 34Interactive
Read the SQL statement in the code block. What does this operation create for the sales table?
- ADefines a custom clustering keyCorrect answer
- BEnables the Search Optimization Service
- CCreates a materialized view
- DBuilds a secondary index
Why this is the answer
The SQL statement defines a custom clustering key on the specified columns. Automatic clustering then uses that key to reorder micro-partitions and minimize overlap.
- Question 35Single Choice
Which metric is used to evaluate how well a table's micro-partitions are organized for a clustering key?
- AClustering depthCorrect answer
- BQuery compilation time
- CRemote disk I/O
- DWarehouse load percentage
Why this is the answer
Clustering depth measures the average overlap of adjacent micro-partitions for clustering columns. A lower depth means better clustering and more efficient pruning.
- Question 36Multiple Select
Select the two workloads where the Search Optimization Service is most appropriate.
- AEquality lookup on an order_id column in a large tableCorrect answer
- BAggregating billions of rows to compute monthly totals
- CSearching for a value inside a semi-structured VARIANT payloadCorrect answer
- DScanning large date ranges for reporting
Why this is the answer
The Search Optimization Service accelerates high-selectivity point lookups and searches within semi-structured data. Range scans and large aggregations are not its target workloads.
Storage and Data Protection
6 questions in this domain
- Question 37Single Choice
What is the maximum Time Travel retention period supported by Snowflake Standard Edition for permanent tables?
- A0 days
- B1 dayCorrect answer
- C7 days
- D90 days
Why this is the answer
Standard Edition supports a maximum of one day of Time Travel. Enterprise Edition and higher editions allow retention up to 90 days.
- Question 38Multiple Select
Which of the following statements correctly describe the Fail-safe feature in Snowflake's data protection model? Select two.
- AFixed seven days, non-configurable.Correct answer
- BDirectly queryable by administrators.
- CAvailable only for permanent tables.Correct answer
- DStarts before Time Travel expires.
Why this is the answer
Fail-safe is a fixed seven-day period applied to permanent tables. It is not accessible by SQL and does not apply to transient or temporary tables.
- Question 39Interactive
Review the provided SQL statement and determine the purpose of the operation being executed. Consider how the temporal clause affects the data returned.
- ARecreates the customers table
- BRestores data after the statement
- CCreates table with pre-statement dataCorrect answer
- DDrops the customers table
Why this is the answer
The BEFORE(statement =>) clause reads the table as it existed immediately before the specified statement, and CREATE TABLE AS stores that state.
- Question 40Single Choice
When a zero-copy clone is created in Snowflake, what happens at the storage layer immediately after creation?
- ADuplicates all data immediately
- BCreates metadata pointers onlyCorrect answer
- CCreates no object until changes
- DDeletes source storage
Why this is the answer
Zero-copy cloning is metadata-only. It creates a pointer to existing micro-partitions and does not duplicate physical storage until data changes.
- Question 41Multiple Select
Which of the following statements about the DATA_RETENTION_TIME_IN_DAYS parameter in Snowflake are true? Select two.
- AUp to 90 days on Enterprise or aboveCorrect answer
- BUp to 90 days on Standard Edition
- CZero disables Time TravelCorrect answer
- DTransient tables support seven days
Why this is the answer
DATA_RETENTION_TIME_IN_DAYS can be set up to 90 days on Enterprise Edition and above. Setting it to 0 disables Time Travel.
- Question 42Interactive
Examine the displayed SQL command and identify the disaster recovery scenario in which it is used.
- APromotes secondary to primaryCorrect answer
- BRefreshes replica database
- CCreates a secondary account
- DEnables account-level Time Travel
Why this is the answer
The FAILOVER command promotes a secondary account to primary during disaster recovery. Refresh only synchronizes data and does not promote.
Frequently asked questions
Everything about the SnowPro Advanced: Data Engineer question bank and how to use it.