Free Snowflake SOL-C01 Practice Exam with Questions & Answers | Set: 6

Snowflake’s architecture consists of:

• Database Storage Layer – Stores optimized columnar data.

• Query Processing Layer – Executes workloads via virtual warehouses.

• Cloud Services Layer – Handles metadata, optimization, access control.

ETL tools and UI are external integrations. Snowflake is cloud-native and has no on-premises components.

====================================================

When auto-suspend is enabled on a virtual warehouse, Snowflake automatically suspends (stops) the warehouse after it has been idle for the configured period of time. Once suspended, the warehouse stops consuming compute credits, thereby reducing costs, since Snowflake bills only for active compute usage. When new queries arrive, auto-resume (if enabled) restarts the warehouse automatically.

Auto-suspend does not change the warehouse size; scaling up or down is a separate configuration. The warehouse is not dropped—its metadata and settings remain intact. Costs may be reduced and made more efficient but are not guaranteed to “stabilize month-to-month,” as the bill still depends on actual usage and workload patterns.

==================

When auto-resume is enabled, Snowflake automatically starts a suspended warehouse when a query or task is submitted. This ensures smooth operations without manual intervention.

It does not scale the warehouse, prevent suspension, or require manual restarting.

====================================================

Snowflake’scompute layerusesMassively Parallel Processing (MPP), meaning queries are divided into smaller tasks distributed across multiple compute nodes in the Virtual Warehouse. Each node processes a portion of the data simultaneously, maximizing parallelism and drastically reducing query times.

Although Snowflake uses a central storage layer (shared-disk model), the compute engine behaves like ashared-nothing MPP system, where each node handles local processing independently, minimizing contention.

Incorrect options:

Snowflake does not rely on single-thread execution.

Storage optimization occurs at the Storage Layer, not compute.

Snowflake does not use traditional shared-disk execution; compute nodes work in parallel independently.

This architecture enables high performance for large analytical workloads.

====================================================

The correct SQL syntax to create a zero-copy clone of an existing table is:

CREATE TABLE MY_TABLE_CLONE CLONE MY_TABLE;

This command instantly creates a new table that references the same underlying micro-partitions as the original. Because of Snowflake’smetadata-only cloning, no storage is consumed at the time of creation. Storage only increases when either the original or the clone diverges through DML operations, following acopy-on-writemodel.

Cloning is available for multiple object types—tables, schemas, databases, stages, streams, tasks, and more. This capability enables rapid creation of development sandboxes, QA environments, rollback copies, or controlled experimentation without duplicating data.

Incorrect options:

“COPY TABLE” is not a valid Snowflake command.

BACKUP/RESTORE are not Snowflake SQL commands.

RESTORE applies only to Time Travel or Fail-safe, not to cloning.

Thus, the CLONE keyword is the only correct method for zero-copy duplication.

====================================================

The Snowsight Query Profile is a powerful diagnostic tool that provides a visual breakdown of how Snowflake executed a query. Its primary purpose is to help users visualize and analyze query performance. It displays execution steps, including scan operations, join strategies, pruning results, aggregation methods, and data movement between processing nodes.

The profile shows metrics such as execution time per step, partition pruning effectiveness, bytes scanned, and operator relationships. This allows developers, analysts, and DBAs to identify bottlenecks—such as unnecessary full-table scans, non-selective filters, or inefficient joins—and tune SQL accordingly.

Query Profile does not execute queries; execution happens in worksheets or programmatic interfaces. It does not create objects or manage data loading; those tasks involve separate SQL commands and UI interfaces.

Overall, Query Profile is essential for performance tuning, helping teams reduce compute costs, optimize warehouse sizing, and improve query efficiency.

==================

Snowflake’s database storage layer is responsible for encrypting data at rest. All persisted data—whether structured, semi-structured, or unstructured—is encrypted using strong encryption algorithms such as AES-256. This process is automatic and transparent to users, ensuring that files and micro-partitions stored in Snowflake-managed cloud storage are encrypted by default. Data in transit is protected separately through TLS.

Virtual warehouses provide compute resources to execute queries and do not perform storage-level encryption. Data loading utilities (such as COPY INTO and client tools) orchestrate data movement but do not handle at-rest encryption. The Cloud Services layer manages metadata, session management, security policies, and query optimization, but the actual encryption of stored data is part of the storage subsystem.

==================

Snowsight’s file-loading interface:

•Automatically detects data typesby inspecting column values.

•Loads (inserts) file contents directly into a table, either a new table or an existing one.

Snowsight does not automatically create permanent file formats, does not merge rows, and does not optimize warehouse-level performance.

==================

Snowflake supports theEXCLUDEkeyword to simplify queries when excluding certain columns from a SELECT * operation. SELECT * EXCLUDE (column1, column2) reduces verbosity and enhances maintainability, especially when table schemas evolve. Explicitly listing all columns is possible but inefficient. Snowflake does not support REMOVE functions for columns nor an OMIT clause. EXCLUDE is the correct and official mechanism.

=======================================

The Public schema is created by default within every Snowflake database and serves primarily as a shared workspace that is accessible to all users who have the necessary privileges. One of its main roles is to act as adefault schemafor objects created without explicitly specifying a schema name. This ensures usability and prevents object creation errors when users forget to define a schema. Additionally, the Public schema is commonly used to storeshared or common resourcesthat multiple users or teams need to access. However, it should not be used for sensitive or user-specific data because access can be broad. It does not store account-level metadata; that information resides in INFORMATION_SCHEMA views or account-level metadata functions. Overall, the Public schema acts as both a convenient default location and a shared-access area within a Snowflake database.

=======================================