Pass the Snowflake SnowPro Advanced Certification SOL-C01 Questions and answers with CertsForce

The only valid Snowflake SQL command among the options forcreating a schemais:

CREATE SCHEMA schema_name;

This command creates a schema within the currently active database. Optional modifiers like IF NOT EXISTS or OR REPLACE can be used to control behavior.

Other options:

USE SCHEMAswitches to an existing schema; it does not create one.

USE SCHis invalid because SCH is not a Snowflake keyword.

CREATE SCHis invalid; SCH is not a valid abbreviation.

Thus, the only valid schema creation command isCREATE SCHEMA.

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

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.

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

Role hierarchy enables a parent role to inherit privileges assigned to child roles. This simplifies RBAC administration, reduces redundant grants, and ensures scalable security management. It has no relation to SQL sequencing, user categorization, or compute sizing.

Snowflake stores data in acompressed columnarformat, which is optimal for analytical workloads. Columnar storage allows Snowflake to read only the relevant columns needed for query execution, improving performance significantly for aggregation and filtering operations. Compression further reduces storage consumption, enabling cost efficiency. This approach differs from row-store formats used in OLTP systems and provides better analytic processing. Snowflake automatically manages partitioning, micro-partitioning, and metadata without user intervention.

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

Time Travel enables Snowflake users to query, clone, or restore historical versions of data. This includes retrieving previous states of tables, schemas, or databases—even after updates, deletes, or drops. Time Travel operates within a retention period (default: 1 day, up to 90 days on higher editions).

Users can query historical data using the AS OF or BEFORE clause, restore dropped objects, and clone databases at specific points in time for backup or analysis.

Time Travel doesnotautomatically manage timestamp data types. It does not guarantee indefinite recovery—after the retention window expires, data moves into Fail-safe. It also is not primarily designed for loading historical datasets; its purpose is to access past states of Snowflake-managed data.

Thus, the correct purpose is to enable access to historical data inside Snowflake.

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

Scalingin/outin Snowflake refers to modifying thenumber of compute clustersassociated with a multi-cluster virtual warehouse. Scalingoutincreases cluster count to accommodate higher concurrency or workload spikes, allowing more queries to run simultaneously without queuing. Scalinginreduces cluster count during periods of lower demand, optimizing compute usage and costs. This is distinct fromscaling up/down, which refers to changing warehouse size (e.g., Small, Medium). Scaling does not involve data movement or region changes; warehouse compute is stateless and operates independently of storage. Multi-cluster warehouses allow Snowflake to automatically add or remove clusters based on demand when auto-scale policies are configured.

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

SHOW GRANTS lists privileges assigned to roles, users, objects, or shares. It is essential for permissions auditing and RBAC management.

SHOW USERS lists users, not privileges.

Warehouse usage is in ACCOUNT_USAGE views.

Load history is retrieved via COPY_HISTORY or LOAD_HISTORY.

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

Aprivate listingis shared only with explicitly selected Snowflake accounts, giving providers fine-grained control over who can access their data. This is ideal for partner organizations, internal business units, or customers requiring restricted access. Apublic listing, on the other hand, is accessible to all Snowflake users via the Marketplace, making it available for broad distribution. Pricing models for both types can be free or paid; neither category inherently requires payment. Both listing types share data, not code. Public listings are not restricted to Snowflake employees—they are open to the entire Snowflake ecosystem. The distinction lies entirely in access scope: targeted (private) vs. universal (public).

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

Snowsight’sQuery Historyinterface provides comprehensive visibility into executed queries. Users can view:

Query text

Execution status (running, succeeded, failed)

Duration and start/end timestamps

Warehouse used and credits consumed

Partition pruning details

Error messages

Query profile for performance diagnostics

This allows developers, analysts, and administrators to troubleshoot slow queries, identify bottlenecks, and monitor compute usage.

Incorrect options:

Query History shows failed and running queries, not just successful ones.

Storage configuration is unrelated to query operations.

Network settings are managed by the Cloud Services Layer, not Query History.

Thus, Query History is a key operational tool in Snowsight.

Snowflake provides a predefined set ofsystem-defined rolesthat enforce Role-Based Access Control (RBAC). These roles ensure structured governance and centralized privilege management across accounts. The primary system-defined roles include:

ACCOUNTADMIN, the highest-privileged role, responsible for global account-level activities such as billing, governance, replication, and cross-region/cloud configuration. It has implicit ownership of all objects.

SECURITYADMINmanages users, roles, MFA, and all privilege grants. This role ensures operational control over user lifecycle management while supporting separation of duties from ACCOUNTADMIN.

SYSADMINmanages objects such as databases, schemas, tables, warehouses, and other compute objects. It is the default role for data engineering and data platform teams needing full control of object creation and maintenance.

DATABASEADMINdoes not exist as a system-defined role—it is typically user-created for customization. System roles form a foundational security model for controlled privilege escalation and governance.

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