Snowflake SnowPro Core practice questions and exam guide

A user reruns an identical query minutes later with every warehouse suspended, and results return in under a second. Which layer served that response?

1. The cloud services layer, which retains the query result independently of compute
2. The virtual warehouse layer, which retained the result in its local SSD cache
3. The storage layer, which returned the answer directly from micro-partition metadata
4. The client application, which cached the prior result set locally in the driver

Answer: A — The cloud services layer, which retains the query result independently of compute

A) Correct — the query result cache is maintained by cloud services, so a matching reuse needs no running warehouse. B) is the classic mix-up with the warehouse local disk cache, which is lost on suspend and could not serve this. C) confuses the metadata cache, which answers only certain aggregate lookups, not full result reuse. D) is wrong because drivers do not cache result sets across sessions in this way.

What's the relationship between a database and a schema in Snowflake?

1. Each schema contains one or more separate databases
2. A database contains schemas, which hold tables
3. Database and schema are two names for one object
4. Schemas are the compute layer known as warehouses

Answer: B — A database contains schemas, which hold tables

B) Correct — the hierarchy is database, then schema, then tables, views and stages. A) Inverts the containment. C) They are distinct object types. D) Warehouses are compute and sit outside this hierarchy.

Snowflake's architecture has how many distinct layers?

1. 1
2. 2
3. 3
4. 5

Answer: C — 3

A/B/D) Wrong. C) Correct — Cloud Services, Compute (virtual warehouses), Storage. Separation of compute and storage is foundational.

What is the result of dividing a table-level Time Travel from 90 days to 0 days?

1. History is purged instantly and Fail-safe starts immediately
2. No change; the previous 90-day window keeps applying to the table
3. Fail-safe is disabled permanently for the table and its clones
4. Versions outside the new window stop being available to queries

Answer: D — Versions outside the new window stop being available to queries

D) Correct — shortening retention means older versions fall outside the window and can no longer be reached by Time Travel queries. A) The framing overstates an immediate purge guarantee. B) Changing the parameter definitely changes behavior. C) Fail-safe depends on table type, not on this setting.

Snowflake's Unistore initiative is built on which table type?

1. External tables over read-only cloud storage files
2. Iceberg tables in open Parquet-based table format
3. Hybrid tables combining row and column storage
4. Temporary tables scoped to a single user session

Answer: C — Hybrid tables combining row and column storage

C) Correct — Unistore is built on hybrid tables, which pair row-oriented storage for fast point operations with columnar storage for analytics. A) External tables are read-only. B) Iceberg is open-format integration. D) Temporary tables are just session-scoped.

Micro-partitions in Snowflake are:

1. Manually sized and managed by the table's owner role
2. Immutable compressed columnar files of ~50-500 MB each
3. Stored on on-premises disk arrays managed by the customer
4. Editable in place when rows are updated or deleted

Answer: B — Immutable compressed columnar files of ~50-500 MB each

B) Correct — Snowflake automatically creates immutable, columnar, compressed micro-partitions as data is loaded. A) Creation and sizing are automatic. C) They live in cloud object storage. D) Updates write new micro-partitions rather than editing existing ones.

An ELT job and an executive dashboard share one warehouse, and dashboard latency spikes during loads. The MOST appropriate change is:

1. Increase the shared warehouse's size so both workloads have headroom
2. Schedule the dashboard to refresh only outside the ELT window
3. Move the ELT job to its own warehouse sized for that workload
4. Enable query acceleration on the shared warehouse for the ELT job

Answer: C — Move the ELT job to its own warehouse sized for that workload

C) Correct — separating workloads onto dedicated warehouses is the core isolation pattern, letting each be sized, scaled and suspended independently. A) raises cost for both and still lets the two workloads contend. B) degrades the business requirement instead of fixing the contention. D) may speed some ELT scans but leaves the two workloads competing on the same clusters.

Setting a multi-cluster warehouse's MIN_CLUSTER_COUNT equal to its MAX_CLUSTER_COUNT produces which behavior?

1. Auto-scale mode, where clusters start and stop with demand
2. The warehouse refuses to start until the counts differ
3. Maximized mode, where all clusters run whenever the warehouse is running
4. Clusters start on demand but never shut down until suspend

Answer: C — Maximized mode, where all clusters run whenever the warehouse is running

C) Correct — equal minimum and maximum counts put the warehouse in maximized mode, so every cluster runs while the warehouse is resumed. A) describes what happens when the minimum is lower than the maximum. B) invents a validation error that does not exist. D) is a plausible-sounding hybrid, but maximized mode starts all clusters immediately rather than on demand.

If many queries show 'remote disk I/O' as a big stage, the fix is usually:

1. Add more concurrent users so that the cache stays warm
2. Disable the result cache so reads go straight to storage
3. Resize the warehouse, or rewrite the query to scan less
4. Move the account to a different cloud provider region

Answer: C — Resize the warehouse, or rewrite the query to scan less

C) Correct — a larger warehouse provides more local SSD cache, and filtering more aggressively reduces the remote reads outright. A) More users increase contention. B) The result cache reduces work rather than causing it. D) The cloud provider is rarely the cause.

Which Snowflake feature provides natural language SQL generation?

1. Search Optimization, which speeds point lookups
2. Cortex Analyst and Copilot, which generate SQL
3. The result cache, which reuses prior query output
4. Snowpipe, which continuously ingests staged files

Answer: B — Cortex Analyst and Copilot, which generate SQL

B) Correct — Cortex Analyst and Snowflake Copilot translate natural language questions into SQL. A) Search optimization accelerates selective lookups. C) The result cache reuses previously computed results. D) Snowpipe handles continuous ingestion.

A data analyst wants to summarize support tickets stored in a table using SQL, without provisioning any ML infrastructure. Which Snowflake feature is the BEST fit?

1. Cortex AI LLM functions such as SUMMARIZE, called from SQL
2. Snowpark Container Services hosting a self-managed model server
3. External functions calling a cloud vendor's hosted ML API
4. Materialized views precomputing aggregates over the tickets

Answer: A — Cortex AI LLM functions such as SUMMARIZE, called from SQL

A) Correct — Cortex LLM functions run serverlessly and are invoked directly in SQL with no infrastructure to provision. B) Containers work but require managing images and compute pools. C) External functions add network configuration and an outside dependency. D) Materialized views precompute query results and cannot summarize text.

Which statement about Apache Iceberg tables in Snowflake is TRUE?

1. They must always be managed by an external catalog such as AWS Glue
2. They convert Parquet files into Snowflake's proprietary micro-partition format on load
3. They store data in open formats in customer-managed cloud storage via an external volume
4. They cannot be queried alongside native Snowflake tables in the same statement

Answer: C — They store data in open formats in customer-managed cloud storage via an external volume

A) Snowflake can act as the Iceberg catalog itself; an external catalog is optional. B) Iceberg tables keep data in open Parquet/Iceberg format — no conversion to micro-partitions. C) Correct — data lives in the customer's storage referenced through an external volume. D) Iceberg and native tables can be joined in one query.

Best Snowflake role to create users + manage role hierarchies?

1. SYSADMIN, which owns databases, schemas and warehouses
2. USERADMIN, which creates users and roles but not grants
3. SECURITYADMIN, which manages users, roles and grants
4. ACCOUNTADMIN, the top-level role over the whole account

Answer: C — SECURITYADMIN, which manages users, roles and grants

C) Correct — SECURITYADMIN inherits USERADMIN and adds global privilege management, so it can create users and roles and administer the hierarchy. A) SYSADMIN manages objects. B) USERADMIN cannot manage grants broadly. D) ACCOUNTADMIN is too broad for daily RBAC work.

A query against ACCOUNT_USAGE views can be:

1. Real-time, exactly matching INFORMATION_SCHEMA for every view
2. Subject to latency, up to about 45 minutes or more per view
3. Instant always, because the views read live metadata directly
4. Deprecated in favor of querying the Snowsight activity page

Answer: B — Subject to latency, up to about 45 minutes or more per view

B) Correct — ACCOUNT_USAGE views carry documented latency that varies by view. A) The two schemas differ in both freshness and scope. C) The data is materialized on a delay, not read live. D) ACCOUNT_USAGE is fully supported and widely used.

Object tags are used to:

1. Label objects and columns for governance and cost use
2. Speed up queries by adding metadata the optimizer reads
3. Cluster data by reordering rows across micro-partitions
4. Cache query results so repeated queries avoid a scan

Answer: A — Label objects and columns for governance and cost use

A) Correct — tags carry metadata that drives policies (such as tag-based masking) and cost or classification reporting. B) Tags are not a performance feature. C) Reordering rows is what a cluster key does. D) Caching is handled by the result cache.

A provider wants to share a subset of a table's columns and rows with a consumer account. Which object type must be added to the share?

1. A standard view over the base table, granted to the consumer role
2. A materialized view restricted to the shared columns
3. A clone of the base table filtered at creation time
4. A secure view over the base table, added to the share

Answer: D — A secure view over the base table, added to the share

D) Correct — only secure views may be shared, because sharing a standard view would risk exposing base-table structure and data to the consumer. A) fails for that reason and cannot be added to a share. B) is not a shareable object and also does not solve the exposure concern. C) technically shares data but is a static copy that must be rebuilt as the source changes.

For continuous low-latency ingestion of files from S3 to Snowflake, use:

1. COPY INTO statements run manually as each new file arrives
2. Snowpipe ingestion triggered by cloud event notifications
3. Database replication into a secondary Snowflake account
4. AWS Database Migration Service writing into tables

Answer: B — Snowpipe ingestion triggered by cloud event notifications

B) Correct — Snowpipe auto-ingests new files when the storage event notification fires, giving serverless near-real-time loading. A) Manual runs are not continuous. C) Replication copies databases between accounts. D) DMS is an AWS database migration service, not a Snowflake loader.

A team creates an external stage over cloud object storage. Compared with an internal stage, the external stage MOST notably:

1. Encrypts data automatically using a Snowflake-managed key only
2. Requires files to be uploaded through the PUT command first
3. Leaves files under the customer's own storage control
4. Restricts loads to a single file format defined at creation time

Answer: C — Leaves files under the customer's own storage control

C) Correct — an external stage points at buckets or containers the customer owns and pays for, so file lifecycle and access remain theirs. A) Internal stages are the ones always encrypted with Snowflake-managed keys; external storage encryption is configured by the customer. B) PUT targets internal stages only; external files are written by the customer's own tooling. D) A default file format can be set but is overridable per COPY statement in either stage type.

Which command UNLOADS data from a Snowflake table to a stage?

1. COPY INTO <table>, reading files from a stage
2. COPY INTO <location>, writing files to a stage
3. PUT, sending local files up to an internal stage
4. GET, pulling files from an internal stage locally

Answer: B — COPY INTO <location>, writing files to a stage

B) Correct — COPY INTO <location> unloads table data to an internal or external stage. A) That form loads in the other direction. C) PUT moves local files to an internal stage. D) GET downloads staged files to the client.

Before defining a clustering key, a team wants to know how well the table is already organized for a candidate column. The MOST appropriate tool is:

1. SYSTEM$CLUSTERING_INFORMATION, which reports depth and overlap metrics
2. The query profile's pruning statistics collected over a week of workload
3. SHOW TABLES, whose output includes a clustering quality score per table
4. The ACCOUNT_USAGE storage metrics view, which lists partition overlap

Answer: A — SYSTEM$CLUSTERING_INFORMATION, which reports depth and overlap metrics

A) Correct — that function reports average clustering depth and partition overlap for a given expression, which is exactly the pre-decision metric. B) Profile statistics are useful evidence but describe past queries rather than the candidate column's layout. C) SHOW TABLES lists the defined cluster key but not a quality score. D) Storage views report bytes and time travel, not clustering depth.

A materialized view is created over a large fact table. Which behavior should the team expect?

1. It refreshes only when a user explicitly issues a REFRESH statement
2. It is refreshed by the warehouse that runs the next query against it
3. It is maintained automatically in the background as base data changes
4. It becomes stale silently and returns pre-change results until rebuilt

Answer: C — It is maintained automatically in the background as base data changes

C) Correct — Snowflake maintains materialized views automatically using serverless compute, so results stay consistent with the base table. A) No manual refresh command is required. B) Querying users' warehouses do not perform the maintenance. D) If maintenance lags, Snowflake reconciles against the base table rather than serving stale rows.

A merged Stream-and-Task pattern for incremental ETL typically:

1. A task reads the stream and MERGEs the new rows into the target
2. A task bypasses the stream and rescans the full source table
3. The MERGE is run manually because streams cannot be scheduled
4. Fail-safe is disabled on the target table so history is not retained

Answer: A — A task reads the stream and MERGEs the new rows into the target

A) Correct — a scheduled task consumes the stream's change rows and merges them into the target, advancing the offset on commit. B) Rescanning the source defeats the point of incremental loading. C) Tasks schedule this work automatically. D) Fail-safe is unrelated to the pattern.

A provider revokes a consumer's access to a shared table. When does the consumer lose access to the data?

1. After the consumer's Time Travel retention on the shared database expires
2. Immediately, because the consumer never held a copy of the data
3. At the end of the current billing period for the share
4. Only after the provider drops and recreates the share object

Answer: B — Immediately, because the consumer never held a copy of the data

B) Correct — sharing is access by reference, so revoking the grant removes visibility right away with nothing left behind. A) is wrong because consumers do not hold Time Travel on shared data. C) invents a billing-linked delay. D) is unnecessary: revoking access to the share is sufficient.

A provider wants to share data with a partner that has no Snowflake account and does not want one. The MOST appropriate mechanism is:

1. Publish a free Marketplace listing the partner can request access to
2. Add the partner's cloud storage account directly to the share definition
3. Unload the data to cloud storage the partner reads with its own tools
4. Create a reader account, which the provider creates and pays compute for

Answer: D — Create a reader account, which the provider creates and pays compute for

D) Correct — reader accounts exist exactly for consumers without their own Snowflake account, and the provider owns and pays for them. A) still requires the partner to have a Snowflake account to consume the listing. C) abandons live sharing and creates a copy the provider must maintain and secure. B) is not possible; shares target Snowflake accounts, not storage buckets.

What is object tagging used for, and what is tag-based masking?

Tags are schema-level key-value objects assigned to accounts, databases, schemas, tables or columns, and they inherit down the hierarchy, enabling classification, cost attribution and discovery. Tag-based masking attaches a masking policy to a tag so any column carrying that tag is automatically protected.

Which warehouse parameters govern how many queries run concurrently and how long a query may wait in the queue?

MAX_CONCURRENCY_LEVEL controls how many concurrent queries a cluster will run before queuing. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS aborts a query that has waited in the queue too long, and STATEMENT_TIMEOUT_IN_SECONDS aborts a query that has run too long.

Bulk load methods?

COPY INTO <table> from internal/external stage. Snowpipe for continuous. Snowflake Streaming for low-latency.

In the Query Profile, what do 'bytes spilled to local storage' and 'bytes spilled to remote storage' indicate, and what is the usual fix?

They indicate the query needed more memory than the warehouse had, so intermediate results were written first to local SSD and then, if that filled, to remote object storage. Remote spilling is severely slow; the usual fixes are to increase the warehouse size, reduce the data scanned, or rewrite the query to avoid huge joins and sorts.

Warehouse cache vs Storage?

Warehouse (local SSD) caches micro-partitions; lost on suspend. Storage layer is always durable.

Snowflake's 3-layer architecture?

Cloud services (auth, optimizer, metadata), Compute (virtual warehouses), Storage (cloud blob storage with micro-partitions).