10 free sample questions from a bank of 65, with the correct answers and explanations. No signup required — start practising right now.
Disk spilling in the Query Profile means the warehouse ran out of memory, so answer B — increase the warehouse size — gives each node more memory.
Query Profile spilling, bytes spilled to local vs remote storage, warehouse sizing vs multi-cluster scaling, memory-intensive operations (joins, sorts, aggregations).
Spilling happens when an operation cannot fit in warehouse memory and falls back to local disk then remote storage, adding heavy I/O. A larger warehouse provides more memory per node, allowing the same operation to fit in memory and eliminating spilling. Adding clusters does not add memory per query since a query uses one cluster.
A — Add additional virtual warehouses: adds separate warehouses / concurrency capacity, does not increase memory available to the single spilling query. C — Rewrite the query using CTEs: CTEs do not change memory footprint or execution resources; spilling is a resource issue. D — Change join order to start with smaller tables: Snowflake's optimizer already handles join ordering; it does not add memory to prevent spilling.
For the exam, spilling = size up, queuing = scale out. If you see Bytes spilled to local/remote storage, choose a bigger warehouse, not more clusters.
In Snowpark you create DataFrames with session.table(), session.sql(), and the session.read readers such as session.read.json() — answers B, C, F.
Snowpark Session entry points, DataFrame creation vs DataFrame actions, session.read file readers, session.table vs session.sql.
B session.read.json() reads semi-structured files into a DataFrame via the DataFrameReader. C session.table() points a DataFrame at an existing table or view. F session.sql() creates a DataFrame from an arbitrary SQL query. These are the three documented creation methods among the choices.
A — session.jdbc_connection(): not a Snowpark DataFrame factory; JDBC is for external connectivity, not listed as a DataFrame constructor. D — DataFrame.write(): this saves / writes a DataFrame, it does not create one. E — session.builder(): there is a Session.builder for building sessions, not a session.builder() method that returns a DataFrame.
Memorize the trio: table(), sql(), read.<file>. Anything with write, col, select, or builder creates nothing — write saves, the rest transform.
In a managed access schema, only the schema owner and roles with global MANAGE GRANTS can grant access — answers A, E, F.
Managed access schemas, loss of owner grant rights, schema owner vs object owner, SECURITYADMIN, MANAGE GRANTS privilege.
With MANAGED ACCESS, object owners lose grant authority. Only the schema owner (A) or a role holding the global MANAGE GRANTS privilege can grant on the CUSTOMER table. SECURITYADMIN (E) holds MANAGE GRANTS by default, and USERADMIN with MANAGE GRANTS (F) explicitly qualifies.
B — The role that owns the database: database ownership alone confers no grant rights inside a managed schema without MANAGE GRANTS. C — The role that owns the CUSTOMER table: in managed schemas table owners cannot grant — that is the core restriction. D — The SYSADMIN role: SYSADMIN does not hold MANAGE GRANTS by default, so it cannot grant unless explicitly given that privilege.
See WITH MANAGED ACCESS and immediately eliminate the object owner. The only grantors are schema owner + anyone with MANAGE GRANTS.
BUILD_STAGE_FILE_URL builds a long-lived file URL that does not expire — answer C, permanent URL.
File URL vs scoped URL vs presigned URL, BUILD_STAGE_FILE_URL syntax, prolonged access, stage file access.
Per docs, BUILD_STAGE_FILE_URL takes stage name + relative path and returns a Snowflake file URL permitting prolonged access — i.e., it does not expire. That matches permanent URL among the choices, unlike temporary/scoped URLs.
A — It generates an encrypted URL: describes masking / encryption concepts, not this function. B — It generates a staged URL: vague distractor; the function generates a file URL, not a stage object reference. D — It generates a temporary URL: that describes BUILD_SCOPED_FILE_URL / presigned URLs which expire, the opposite of this function.
URL trio: FILE_URL = permanent, SCOPED_FILE_URL = temporary/scoped, PRESIGNED = expiring external access. FILE in the name = forever.
Answer is A + C + E. File structure (file format) can be given inline in COPY, as a named FILE FORMAT object, or as a default on a STAGE.
For where-can-format-be-defined choose-three, default to COPY + FILE FORMAT + STAGE. Do not pick PIPE just because its COPY text contains a format — PIPE has no FILE_FORMAT property.
Snowpark DataFrames support full relational ops like joins and are lazily evaluated on the server — answers A and B.
Snowpark lazy evaluation, server-side pushdown, DataFrame relational operations, Scala to Snowpark migration.
A: DataFrames expose join, select, filter, etc., so multi-table joins translate directly from Scala. B: DataFrames are lazy — they build a query plan and only execute on an action, with all computation pushed to Snowflake. Both are core documented characteristics.
C — UDFs are not pushed down: false; Snowpark UDFs/UDFTs run inside Snowflake. D — Snowpark requires a separate cluster: false; compute is the Snowflake warehouse, no external Spark cluster needed. E — Columns with same name need squared brackets: false; use df.col("name") / df1.col vs df2.col to disambiguate, not bracket syntax.
If a Snowpark option says external cluster, eager execution, or no joins/UDFs, it is wrong. Think lazy + server-side + full DataFrame API.
total_constant_partition_count counts micro-partitions that will not benefit from reclustering — high value means well clustered, answer A.
SYSTEM$CLUSTERING_INFORMATION, constant vs overlapping partitions, clustering quality, partition pruning.
Docs define total_constant_partition_count as micro-partitions in a constant state that will not benefit significantly from reclustering. 493 such partitions means O_ORDERDATE values are well colocated, enabling effective pruning, hence very well clustered.
B — Not clustered well / overlap every partition: that describes high average_overlaps or average_depth, not constant partitions. C — Data does not change often / not modified: constant refers to clustering state, not DML recency. D — Very low cardinality / single distinct value: low cardinality is not what this counter measures; a partition can be constant without single-value content.
constant = good, overlaps/depth = bad. When you see total_constant_partition_count, read it as already optimal partitions.
Thousands of concurrent analysts with steady load and cache priority need maximized multi-cluster — always-on capacity, answer B.
Multi-cluster modes (maximized vs auto-scale economy/standard), concurrency vs elasticity, result and warehouse cache preservation, responsiveness.
Maximized mode (min = max > 1) starts all clusters up front, giving full compute immediately with no scale-up queuing and preserving warm caches. That fits large steady concurrency where responsiveness is top priority and demand does not fluctuate much.
A — Economy auto-scaling: saves credits but queues queries before scaling, hurting responsiveness. C — A warehouse per 250 analysts: fragmentation destroys cache reuse and adds management overhead with no responsiveness gain. D — Single XL warehouse: one cluster cannot handle thousands of concurrent queries; concurrency queuing will occur regardless of size.
Responsiveness + steady high concurrency = maximized. Fluctuating load + cost sensitivity = auto-scale. Never answer single huge warehouse for concurrency.
Reusing a persisted result resets its 24-hour clock up to 31 days from first execution — with 12-hour reuse, answer D.
Persisted query results, 24-hour retention reset on reuse, 31-day absolute limit, unchanged data requirement.
Docs: each reuse resets the 24-hour retention, capped at 31 days from first execution. A dashboard hitting the same SQL every 12 hours continuously resets the clock, so results remain available for the full 31-day maximum if data is unchanged.
A — 12 hours: confuses the dashboard schedule with result lifetime. B — 24 hours: is only the single-use window without reuse; reuse extends it. C — 14 days: this is the Time Travel / historical data window, not persisted-results reuse limit.
Memorize 24 hours resettable to 31 days. Any same-query interval under 24 hours with unchanged data = 31-day answer.
An abruptly disconnected session leaves a detached transaction holding locks — abort it with SYSTEM$ABORT_TRANSACTION, answer A.
Detached transactions, transaction locks, SYSTEM$ABORT_TRANSACTION vs cancel, LOCK_TIMEOUT, TRANSACTION_ABORT_ON_ERROR.
Docs direct that a transaction left in a detached state by abrupt disconnect must be aborted via SYSTEM$ABORT_TRANSACTION by the starter or admin. That immediately releases locks so a new transaction can run.
B — SYSTEM$CANCEL_TRANSACTION: no such function for this purpose; cancel applies to queries/statements, not detached transactions. C — Set LOCK_TIMEOUT to FALSE: LOCK_TIMEOUT controls wait time, it does not release an existing detached lock. D — Set TRANSACTION_ABORT_ON_ERROR to TRUE: affects future error behavior inside procedures, does not abort the already-detached transaction.
Detached = ABORT_TRANSACTION. Remember the 5-minute blocked / 4-hour idle auto-abort rules as backup, but the immediate action is abort.
Want the full bank of 65 questions for SnowPro Advanced Data Engineer? See all practice exams.