SnowPro Core · 25% of the exam

Snowflake AI Data Cloud features and architecture: free practice questions

5 sample questions from our 63-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. An analyst runs SELECT COUNT(*) FROM orders; on a table that has not been modified in several days. The query completes in milliseconds and shows 0 bytes scanned. Which caching mechanism is responsible for this behavior?

  • A. Warehouse (local disk) cache — the micro-partition data was cached on the warehouse SSD.
  • B. Result cache — an identical query result was stored in Cloud Services and returned without executing.
  • C. Metadata cache — Snowflake maintained the row count as table-level metadata in Cloud Services and answered without scanning any data.✓ Correct
  • D. Materialized view cache — a pre-computed COUNT(*) was stored as a materialized view.
Explanation

COUNT(*) on a table is answered directly from the metadata cache maintained in the Cloud Services layer. Snowflake tracks statistics like row counts in micro-partition metadata, so no data scan is needed — hence 0 bytes scanned. Option A is incorrect; the warehouse cache stores raw micro-partition data and would still require some scanning. Option B is plausible (result cache can also return 0 bytes scanned), but the key differentiator here is the 0 bytes scanned behavior for an aggregate like COUNT(*) which is definitively served from metadata. If the result cache were used, the bytes scanned would also be 0, but the metadata cache is the root mechanism for COUNT(*) specifically. Option D is incorrect; no materialized view was mentioned and they must be explicitly created.

2. An organization wants to share live Snowflake data with a partner company that does NOT have a Snowflake account, without copying or moving the data. The partner needs to access the data through their existing SQL-based BI tool. Which Snowflake feature enables this use case?

  • A. Snowflake Data Sharing using a Direct Share to the partner's non-Snowflake account
  • B. A Snowflake Listing published to the Snowflake Marketplace, which the partner accesses via a Reader Account✓ Correct
  • C. Exporting the shared tables to an S3 bucket and granting the partner IAM access
  • D. Creating a Snowflake External Table pointing to the partner's BI tool database
Explanation

When the data consumer does NOT have a Snowflake account, the provider can create a Reader Account (a managed Snowflake account provisioned and paid for by the provider). The provider publishes a listing or share, and the partner connects to the Reader Account using standard JDBC/ODBC drivers supported by their BI tool—no data is copied. Option A is wrong because Direct Share requires the consumer to have their own full Snowflake account; it cannot target a non-Snowflake user directly. Option C is wrong because exporting to S3 creates a physical copy, violates the 'no copying' requirement, and introduces data freshness and security concerns. Option D is wrong because External Tables in Snowflake reference data stored outside Snowflake in cloud object storage—they are not a mechanism for sharing data with an external party's BI tool.

3. A Snowflake virtual warehouse is sized at X-Small and a developer submits a query that requires more memory than the warehouse can provide. What is the MOST likely outcome?

  • A. Snowflake automatically upgrades the warehouse to a larger size for the duration of the query and then downgrades it afterward.
  • B. The query fails immediately with an 'out of memory' error and cannot be retried on the same warehouse.
  • C. Snowflake may spill intermediate results to local SSD or remote cloud storage to complete the query, but performance will degrade.✓ Correct
  • D. The Cloud Services layer takes over the computation entirely, bypassing the virtual warehouse's memory limitation.
Explanation

When a query's intermediate data exceeds the in-memory capacity of a virtual warehouse, Snowflake spills data first to local SSD (warehouse disk) and then, if that is also insufficient, to remote cloud storage. The query completes but with significantly degraded performance. This behavior is visible in the Query Profile as 'Bytes spilled to local storage' or 'Bytes spilled to remote storage.' Option A is wrong—Snowflake does not auto-resize a warehouse mid-query; resizing can be done manually but applies to future queries. Option B is wrong—Snowflake does not immediately fail memory-intensive queries; it uses spilling as a fallback. Option D is wrong—the Cloud Services layer handles metadata and orchestration, not analytical query computation.

4. A Snowflake administrator notices that a query returns results almost instantaneously the second time it is executed, even though the virtual warehouse was suspended between the two executions. Which caching mechanism is responsible for this behavior?

  • A. Warehouse (local disk) cache, which persists data on SSD storage attached to the virtual warehouse nodes
  • B. Result cache, which stores the final output of queries in the Cloud Services layer for up to 24 hours✓ Correct
  • C. Metadata cache, which pre-computes aggregations and stores them in the Cloud Services layer
  • D. Remote disk cache, which stores compressed micro-partitions in Amazon S3 between warehouse restarts
Explanation

The Result Cache (also called the Query Result Cache) is maintained in the Cloud Services layer, completely independent of any virtual warehouse. It stores the exact result set of a completed query for 24 hours (reset on each re-use). Because it lives in the Cloud Services layer—not inside the warehouse—results can be served even after the warehouse has been suspended or terminated. Option A is wrong because the warehouse (local SSD) cache is destroyed when a warehouse is suspended; it only accelerates repeated table scans within the same running warehouse session. Option C is wrong because the metadata cache stores statistical information about micro-partitions (min/max values, row counts, etc.) to enable pruning and answer simple metadata-only queries, not full query results. Option D is wrong because Snowflake does not have a 'remote disk cache' concept; remote storage (S3/Azure Blob/GCS) holds the actual micro-partition files, not cached query results.

5. A data team is loading a 2 TB CSV file into Snowflake. Their data engineer splits the file into 200 × 10 GB chunks before loading. The virtual warehouse is sized Medium (4 nodes). What is the PRIMARY reason Snowflake recommends splitting large files before loading with COPY INTO?

  • A. Snowflake has a hard 1 GB per-file limit enforced at the COPY INTO command level.
  • B. Splitting files allows parallel ingestion across the warehouse's compute nodes, improving load throughput.✓ Correct
  • C. Splitting files prevents the Cloud Services layer from needing to parse file metadata.
  • D. Snowflake charges per file loaded, so fewer, smaller files reduce overall cost.
Explanation

Snowflake distributes file loading across the virtual warehouse's compute nodes in parallel; more, smaller files allow better parallelism and faster overall load times. Option A is false — Snowflake does not enforce a hard 1 GB per-file limit, though very large single files are inefficient. Option C is incorrect; Cloud Services always parses file metadata regardless of file size. Option D is false; Snowflake does not charge per file — costs are based on compute (warehouse credits) and storage.

58 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free
Snowflake AI Data Cloud features and architecture — Free SnowPro Core Practice Questions | DataCertPrep — Certification Prep