SOL-C01 Sample Questions Answers

Snowflake provides intuitive mechanisms to navigate semi-structured data stored in VARIANT columns, especially JSON, XML, Parquet, and Avro. Two primary access methods aredot notationandbracket notation. Dot notation, such as column.nested.key, allows straightforward access to nested elements using a hierarchical reference. Bracket notation, such as column['key name'], allows access to elements with special characters, spaces, or numeric array indices. While Snowflake also offers powerful functions such as FLATTEN(), OBJECT_CONSTRUCT(), and ARRAY_LENGTH(), these operate on semi-structured types but do not serve as direct field-access mechanisms. Cloud Functions play no role in querying semi-structured fields inside Snowflake. Dot and bracket notation work together to provide full expressiveness, enabling users to traverse complex hierarchical structures directly in SQL, making Snowflake an ideal platform for semi-structured data analytics.

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

Snowflake’szero-copy cloningcreates a metadata-only copy of an object—database, schema, table, or many other object types. At creation time, the cloneshares the same underlying micro-partitionsas the source, consuming virtually no additional storage.

Storage consumption occurs only wheneither the clone or the original object is modified, a behavior known ascopy-on-write. This makes cloning ideal for development, testing, QA, troubleshooting, backup creation, or controlled sandboxing.

Incorrect statements:

Clones do not duplicate storage at creation.

Cloning applies to tables, schemas, databases, and other supported objects.

Clones do not synchronize with the original; after creation they diverge independently.

This architecture enables quick, cost-efficient replication of environments and data.

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

Snowflake processes queries usingMassively Parallel Processing (MPP)compute clusters, deployed as virtual warehouses. Each warehouse consists of multiple compute nodes working in parallel to execute queries efficiently. When a query is submitted, Snowflake distributes tasks across nodes, processes data subsets concurrently, and aggregates results. This architecture enables high performance, scalability, and the ability to handle complex analytical workloads. While Snowflake does incorporate elements of shared-disk storage, query execution itself depends on MPP compute clusters. Options such as third-party connectors or storage optimization do not represent the core query processing mechanism.

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

A table is the core Snowflake object used to store structured data. Tables persist rows and columns in Snowflake’s storage layer, which internally uses compressed, columnar micro-partitions managed by the platform. Tables can be permanent, transient, or temporary, but in every case, they are the primary objects that hold the actual data.

A virtual warehouse is a compute resource used to process queries and perform operations; it does not store user data. A stored procedure contains executable logic (such as complex workflows or procedural code) but does not serve as a data container. A view is a logical object that presents the results of a saved query; it references underlying tables and does not store its own data. As a result, “Table” is the correct answer.

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

Semi-structured data refers to formats that do not follow a rigid relational schema but still contain structural tags or hierarchies, allowing flexible representation of nested or irregular data. In Snowflake,JSON,Parquet, andXMLare all considered semi-structured because they incorporate metadata, hierarchical fields, or tagged data that can vary across records. JSON offers key–value pairs and nested arrays, making it ideal for logs and API responses. Parquet, being a columnar file format containing both schema definitions and metadata, is optimized for analytics while still supporting semi-structured capabilities such as variable field nesting. XML uses tags and attributes to represent hierarchical content, making it semi-structured as well. On the other hand, CSV represents strictly structured, row/column-based data without inherent metadata or hierarchy, so it is not considered semi-structured. Snowflake treats semi-structured formats by loading them into the VARIANT data type, enabling powerful SQL-based exploration using path notation.

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

Snowflake supports two official stage types:

• Internal Stages – User stages, table stages, and named internal stages stored inside Snowflake.

• External Stages – Refer to cloud storage (S3, Azure Blob, GCS) using integration credentials.

Managed, permanent, and temporary stages are not official Snowflake classifications.

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

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.

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

Internal stages are fullySnowflake-managed storage locationsused to temporarily store data files for loading or unloading operations. Examples include:

User stages (@~)

Table stages (@%table_name)

Named internal stages (@my_internal_stage)

Because they resideinside Snowflake, users do not need AWS, Azure, or GCP credentials to access them. Internal stages support all file types Snowflake can process, including CSV, JSON, Parquet, Avro, ORC, images, PDFs, and other semi-structured formats.

Incorrect statements:

They arenot limitedto structured or semi-structured data—they accept all supported formats.

External cloud credentials are only needed for external stages (S3, Blob, GCS).

Internal stages simplify secure data ingestion and operational pipelines because Snowflake handles encryption, metadata, and lifecycle management.

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

Snowsight’s Query History view allows filtering queries bystatus,date range, andexecuting user. Additional filters include SQL text search, warehouse, duration, statement type, session ID, query ID, query tag, parameterized query hash, and whether a query was generated by a client or task. These filters help users identify long-running, failed, queued, or specific types of queries for troubleshooting and performance optimization. Network or hardware configurations have no role in Query History filtering.

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

On the Snowflake Marketplace, users can apply a Price filter when searching for data products. This filter helps distinguish between free offerings and paid listings, making it easier to align data acquisition with budget and commercial terms. Marketplace metadata includes pricing information that this filter uses to refine search results.

Dataset “Size” is not exposed as a primary Marketplace search filter. “Compatibility” is implied, as all Marketplace products are consumable within Snowflake. “Performance” is not a filterable attribute, because performance is influenced mainly by the consumer’s compute configuration and query design, rather than a fixed property of the Marketplace listing.

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

The Database Storage Layer is responsible for persistently storing all customer data in Snowflake. This layer automatically manages micro-partitioning, compression, and metadata. Data is stored in optimized, columnar formats inside cloud object storage (AWS S3, Azure Blob Storage, or GCP Storage). Users cannot manipulate physical storage directly; access is only through SQL operations. Other layers serve different purposes: Cloud Services manages metadata and orchestration, Query Processing Layer executes workloads, and Virtual Warehouses provide compute but do not store data.

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

Snowflake uses the SQL command CREATE WAREHOUSE warehouse_name to create a new virtual warehouse. Warehouses provide the compute resources for executing SQL queries, loading data, and performing transformations. When creating a warehouse, additional parameters may be specified, such as warehouse size, auto-suspend timeout, auto-resume, scaling policy, or multi-cluster mode. The options involving “CREATE VIRTUALWAREHOUSE” are invalid syntax in Snowflake. CREATE DATABASE and CREATE SCHEMA create storage containers, not compute resources. Therefore, CREATE WAREHOUSE is the only correct command for provisioning compute.

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

When you preview a table in Snowsight, Snowflake returns up to the first 100 rows of the table’s data. This preview is designed for quick inspection of the table’s contents and structure without requiring the user to manually write a SELECT query. The preview interface allows basic exploration such as scrolling, simple sorting, and viewing column metadata.

Tables are not “stored” in a specific virtual warehouse; warehouses provide compute only. The table definition (DDL) can be viewed in the Table Details section but is not the main output of the Preview action. Snowsight does not natively generate an Entity Relationship (ER) diagram for the table as part of the preview.

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

Snowflake is best described as anAI Data Cloud Platform. While Snowflake originated as a cloud data warehouse, its capabilities have expanded to include secure data sharing, application development, machine learning workloads, unstructured data processing, Snowpark, and integrated LLM/AI functions (such as Snowflake Cortex and Streamlit). This broad ecosystem supports analytic workloads, governance, multi-cloud architecture, and AI-driven applications. It is not simply an ETL tool or a transactional database.

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

Snowflake'sINFORMATION_SCHEMAcontains metadata views about databases, schemas, tables, columns, constraints, and other objects. To retrieve a list of tables, the correct view isINFORMATION_SCHEMA.TABLES, which returns:

Table name

Schema name

Table type (BASE TABLE, VIEW, MATERIALIZED VIEW)

Creation timestamp

Owner and retention properties

The query:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

returns metadata for all tables in thecurrent databasecontext.

INFORMATION_SCHEMA.COLUMNS returns column-level metadata, not table listings.

PUBLIC.INFORMATION_SCHEMA is invalid—INFORMATION_SCHEMA is not inside PUBLIC schema.

PUBLIC.TABLES is not a system object unless created manually.

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

Snowsight includes a modern, fully capableSQL editordesigned for rich query development, execution, and analysis. The editor offers features such as syntax highlighting, intelligent auto-complete, contextual error display, and result-set visualization. Users can organize work with worksheets, folders, tagging, and versioning-like behavior.

Snowsight is built for both simple and complex SQL workloads. It supports multi-statement execution, script-like workflows, stored procedure development, materialized views, and advanced analytics queries.

Unsupported options include:

Manual data storage configuration: Snowflake abstracts storage management completely.

Pre-built queries only: Snowsight allows fully custom SQL.

Limited to simple queries: It supports enterprise workloads, BI query chains, and operational SQL.

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

Snowflake’s foundational security capability isalways-on end-to-end encryption, applied to all customer data both at rest and in transit. Snowflake encrypts data stored in micro-partitions using AES-256, and all network communication uses TLS encryption. Key management is automated through Snowflake’s hierarchical key model, providing additional rotation, rekeying, and defense-in-depth mechanisms.

While RBAC, masking policies, and network policies are crucial components of Snowflake security, encryption is the platform’s guaranteed, baseline, mandatory protection feature. It applies universally to all data, regardless of workload, role configuration, or user settings.

Option A: automated masking is optional and policy-driven, not a primary universal mechanism.

Option B: network perimeter defense exists but is not the platform’s core foundation.

Option C: RBAC controls access but does not protect data itself.

Thus, Snowflake’s primary and most universal security capability is end-to-end encryption.

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

Snowflake provides three main table types:Permanent,Transient, andTemporary. Permanent tables support full Time Travel and Fail-safe, and are designed for long-term storage. Transient tables do not include Fail-safe and are best suited for intermediate or less durable datasets. Temporary tables exist only for the duration of the user session that created them, making them ideal for staging or ephemeral workloads. Snowflake does not have a table type called “Virtual”; therefore, the correct options are Permanent, Temporary, and Transient.

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

SnowflakeCortex functionsenable AI-powered operations directly within SQL and Python, supporting multiple languages via translation, classification, summarization, and document extraction functions. They operate seamlessly onstructuredandsemi-structureddata stored in VARIANT columns, allowing LLMs to process text, JSON, documents, and rich content.

Cortex functions can be combined within SQL pipelines—for example, using PARSE_DOCUMENT to extract data, followed by CLASSIFY_TEXT to categorize extracted fields, and then SUMMARIZE to produce narrative summaries. This composability enables sophisticated multi-stage workflows for document intelligence, support ticket analysis, compliance automation, and semantic enrichment.

They arenot limited to a single domain; Cortex functions apply to finance, retail, support operations, healthcare, legal analysis, and general AI-assisted data processing.

Thus, Cortex functions are versatile, multi-domain, multi-language tools integrated with Snowflake’s compute and governance layers.

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

SHOW GRANTS is used to list privileges, including roles assigned to a user.

SHOW GRANTS TO USER reveals all the roles that user inherits.

SHOW ROLES lists roles, SHOW USERS lists users, SHOW DATABASES lists databases—not SHOW GRANTS.

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

For Snowflake Standard Edition accounts, the maximum Time Travel retention period is1 day(24 hours). Time Travel allows users to query historical data, restore dropped objects, and clone objects at a point in time. Although the retention period can be adjusted at the account, database, schema, or table level, Standard Edition restricts the upper limit to 1 day. A value of 0 disables Time Travel entirely. A 7-day retention period applies to Fail-safe, not Time Travel, and Fail-safe is a disaster-recovery mechanism managed exclusively by Snowflake. The 90-day Time Travel retention is available only for Enterprise Edition and above. Therefore, the correct maximum retention period for Standard Edition is 1 day.

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

Standard permanent tables in Snowflake have a default1-day Time Travel retention period. During this period, users may query historical data, restore dropped tables, or clone past states. Higher editions allow extending this period up to 90 days. Temporary and transient tables have 0-day retention by default.

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

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 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.

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

Snowflake Notebooks support cross-cell interaction between Python and SQL by using Jinja-style templating syntax. To reference a Python variable inside a SQL cell, you wrap the variable name in double curly braces, like {{myvar}}. During execution, the Notebook engine substitutes the Python variable’s value into the SQL statement before sending it to Snowflake.

This mechanism allows dynamic SQL generation, parameterization of queries, incorporating Python logic into SQL workflows, and building interactive analytics pipelines.

Other provided options are invalid in Snowflake Notebooks: $myvar resembles shell syntax and is not supported; 'myvar' inserts a literal string rather than the variable's value; using myvar alone would cause SQL to interpret it as a column or object name.

Therefore, only {{myvar}} correctly represents Snowflake Notebook variable substitution syntax.

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

On the Snowsight Schema Details page, one of the supported operations is renaming the schema. The UI exposes controls that allow users with appropriate privileges to change the schema name, which can help maintain consistent naming conventions or reflect project reorganizations.

Sharing data with other accounts is typically done using secure shares at the database or object level, not from a simple “Share this schema” function on the Schema Details page. Truncation is a table-level operation (e.g., TRUNCATE TABLE), not something that applies at the schema level. Metrics visible on the Schema Details page (such as object counts or storage usage) are informational and not directly editable; they are derived from system metadata.

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

DESCRIBE FILE FORMAT returns configuration settings such as FIELD_DELIMITER, TYPE, SKIP_HEADER, and COMPRESSION for the specified format.

SHOW FILE FORMATS only lists existing formats, not their internals.

LIST FILE FORMAT is invalid. ALTER modifies but does not display details.

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

Snowflake uses a Role-Based Access Control (RBAC) model, whererolesare the containers of privileges. Assigning roles to users ensures that permissions on database objects (such as tables, schemas, warehouses, and functions) are enforced consistently and securely. Users do not receive privileges directly; instead, privileges are granted to roles, and roles are assigned to users.

This enables scalable, auditable, and manageable access control.

Roles do not determine tasks, do not affect query optimization, and do not govern which data types a user may query—permissions are object-based, not datatype-based.

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

The PARSE_JSON() function ingests a string containing JSON text and converts it into Snowflake’sVARIANTdata type, enabling the JSON to be queried, navigated, and transformed using SQL. Snowflake does not store JSON in its raw textual representation; instead, VARIANT allows Snowflake to apply optimized parsing, indexing, and querying operations against semi-structured content. This function is particularly useful when JSON arrives inline (e.g., supplied directly within SQL statements or loaded from CSV files containing JSON strings). PARSE_JSON does not perform data loading from stages—that is handled through COPY INTO—nor does it convert JSON into XML. Once JSON is converted to VARIANT, Snowflake allows access to nested structures using dot notation, bracket notation, and functions like FLATTEN(). Thus, the function serves as a bridge between raw JSON strings and Snowflake’s relational and analytical capabilities.

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

A Snowflake database is alogical containergrouping one or more schemas. It does not store data physically in the traditional sense—Snowflake abstracts physical storage into its cloud-native micro-partitioned storage layer. Because schemas contain tables, views, and other objects, a database organizes schemas logically, enabling governance, naming conventions, and security boundaries.

A virtual warehouse is compute, not storage. A “collection of tables and views” better describes a schema, not a database. Snowflake separates logical structure from physical storage, so a database is not a physical file repository.

Thus, a database is best defined as a logical grouping of schemas.

Snowflake provides multipledate and time data typesto handle various temporal workloads. TheDATEtype stores only the calendar date (year, month, day), suitable for dimensional modeling, slowly changing dimensions, and calendar-related analytics.

TIMESTAMPrepresents a point in time and includes sub-second precision. Snowflake supports multiple variants:

TIMESTAMP_NTZ(no time zone)

TIMESTAMP_LTZ(local time zone)

TIMESTAMP_TZ(explicit time zone)

These options allow flexibility for global operations, event logs, and time-series analytics.

DATETIMEis an alias forTIMESTAMP_NTZ, meaning it stores both date and time but without time-zone awareness. It is commonly used in ETL, application logs, and system-generated events.

TIMEDATEis not a valid Snowflake data type and does not exist in Snowflake’s type system.

These temporal types support extensive built-in datetime functions, automatic casting, and integration with semi-structured data through VARIANT.

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

LIST @stage_name displays all files present in a stage (internal or external).

PUT uploads local files to an internal stage.

GET downloads files from an internal stage.

COPY INTO loads or unloads data but does not list files.

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

The Snowflake multi-cluster shared data architecture separates compute and storage, enablingindependent scaling of each. Compute resources (virtual warehouses) operate independently from the centralized storage layer, allowing users to increase compute power without impacting storage costs—or vice versa. This flexibility provides major advantages for cost optimization and workload performance tuning.

The architecture also supportsnear-unlimited concurrency and elasticity. When many users or workloads run simultaneously, Snowflake can automatically add additional clusters to a multi-cluster warehouse, ensuring that no queries experience queuing or performance degradation. This capability is crucial for BI dashboards, ETL pipelines, and large organizations with varied usage patterns.

Columnar storage (option D) is indeed a Snowflake feature but is not unique to the multi-cluster architecture. Enhanced security arrives via Snowflake’s platform-wide mechanisms, not specifically because of multi-cluster architecture. Unstructured data loading (option B) is also not governed by multi-cluster behavior.

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

The Query Processing Layer executes SQL statements using virtual warehouses and massively parallel processing. It handles query optimization, execution, and scaling. The Cloud Services Layer parses and plans queries but does not execute them. Storage persists data, and the Client Layer is external to Snowflake architecture.

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

Snowsight is the default web-based interface for Snowflake. It provides a graphical, browser-based environment to write and run SQL and Python, manage and explore database objects, create visualizations and dashboards, monitor query and warehouse activity, and collaborate via worksheets. It has replaced the Classic Console as the default UI for new Snowflake accounts.

SnowSQL is a command-line client used to connect to Snowflake from a terminal and execute SQL statements. Snowpark is a developer framework that supports writing data applications in languages like Python, Java, and Scala, but it is not a UI. “Snowflake CLI” refers to command-line tooling and automation utilities, again not a graphical web interface.

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

Schemas serve as logical containers within a database, grouping tables, views, file formats, functions, and other objects.

Roles control access, not organization.

Tables store data but do not group objects.

Stages store files or point to external storage; they are not organizational containers.

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

ADatabaseis the highest logical container for schemas and their objects in Snowflake.

Hierarchy:

Account → Database → Schema → Tables, Views, Stages, etc.

Schemas organize objects within a database; tables and views do not contain objects.

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

Semi-structured data like JSON stored in a VARIANT column can be queried directly usingpath notation, either dot notation or bracket notation.

Examples:

Dot notation:SELECT data:customer.name FROM table;

Bracket notation:SELECT data['customer']['name'] FROM table;

Snowflake automatically interprets the JSON structure, making relational extraction unnecessary. Complex fields can be accessed through functions such as FLATTEN, OBJECT_KEYS, ARRAY_SIZE, and TYPEOF.

Incorrect options:

Converting JSON to a relational table is optional, not required.

Stored procedures are not necessary for JSON querying.

WHERE on the full VARIANT cannot precisely extract fields.

Thus, path notation is the native, efficient method.

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

The recommended approach in Snowflake’s RBAC model is togrant privileges to roles, not to individual users or through ownership transfers. Privileges such as SELECT, INSERT, USAGE, and MODIFY are granted to roles, and roles are then assigned to users—creating scalable governance.

Transferring ownership is a powerful action and should not be used simply to provide access. The PUBLIC role should not be used to grant sensitive privileges since it is granted to all users by default. Cloning objects is unnecessary and creates new objects, not access to the original.

Thus, granting privileges to the role is the correct RBAC practice.

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

The correct SQL command for creating a new database in Snowflake isCREATE DATABASE database_name;. This initializes a new logical container for schemas and objects. USE DATABASE switches the active database context but does not create one. Snowflake does not support the shorthand CREATE DB or USE DB syntax. Therefore, CREATE DATABASE is the correct and only valid command for creating a database.

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

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.

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

Snowflake Notebooks support both SQL and Python as executable cell types. This means that users can create notebook cells written in Python for programmatic data processing and modeling, and in SQL for declarative querying and transformation. Because of this, the statements “Notebook cells can be written in Python” and “Notebook cells can be written in SQL” are both correct.

Snowflake databases do not natively execute queries written in Scala; Scala is supported via Snowpark APIs for application code, not as a direct query language. Worksheets in Snowsight are primarily SQL-based and also support procedural constructs (e.g., Snowflake Scripting), so the phrase “only in SQL” is not a precise or complete characterization. Dashboards in Snowsight are created using SQL-backed visualizations and built-in UI components; JavaScript is not a supported authoring language inside the native dashboarding layer. Therefore, options B, D, and E are not correct in this context.

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

Streamlit inside Snowflake Notebooks enables users to buildinteractive, real-time visualizations and data applicationsdirectly within Snowsight. Because Streamlit is event-driven, users can create UI components such as sliders, dropdowns, date pickers, buttons, and filters that dynamically update charts, tables, and computed outputs.

Data remains inside Snowflake, processed through Python with Snowpark, eliminating data movement to external systems. This allows rapid prototyping of dashboards, analytical workflows, and AI-driven apps. Streamlit also supports layout customization, reactive components, and seamless integration with Snowflake compute for scalable analytics.

Incorrect options:

Streamlit doesnotmanage data storage.

Streamlit does not simplify code execution; it enhances visualization and interactivity.

Warehouse clusters are not fixed-size—Snowflake warehouses can scale.

Thus, its primary benefit is enablingrich interactive analytics directly within Snowflake.

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