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

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 auto-suspend feature saves compute costs byautomatically suspending a warehouse when it becomes idlefor a configured period. Since Snowflake charges for compute time while a warehouse is running, auto-suspend prevents unnecessary credit consumption by stopping the warehouse when no queries are executing. The warehouse can resume automatically when a new query is submitted, provided auto-resume is enabled. Auto-suspend does not change warehouse size (scaling up/down) nor adjust clusters in a multi-cluster warehouse (scaling in/out). It strictly controls when compute resources turn off due to inactivity, making it an essential cost-optimization feature.

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

Cortex LLM functions integrate large language model capabilities directly inside Snowflake. These functions enable:

• Sentiment analysis

• Summarization

• Document question answering

• Text classification

• Embedding generation for vector search

They operate directly on Snowflake data, require no external infrastructure, and support secure in-platform AI workloads.

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

In Snowflake Notebooks, Python and SQL operate in an integrated environment. Python variable substitution is performed usingstring placeholders or f-string interpolationwithin SQL statements. This allows dynamic query construction where Python values are embedded directly into SQL code executed through the Snowpark session.

Example:

table_name = "CUSTOMERS"

session.sql(f"SELECT * FROM {table_name}").show()

This method enables parameterized, context-aware SQL generation—ideal for iterative development, automation, and pipeline construction. It avoids manual text editing and provides consistency across Python and SQL execution layers.

Incorrect responses:

Network settings and HTML are unrelated to variable handling.

Editing data files does not apply to dynamic query parameterization.

Variable substitution is essential for combining logic, iteration, and conditional flows within notebooks.

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

The correct SQL command for removing a schema in Snowflake is:

DROP SCHEMA temp_schema;

This command deletes the schema and all objects contained within it, including tables, views, stages, file formats, and sequences. Snowflake performs this operation atomically, ensuring metadata consistency during the drop process. Users can also includeIF EXISTSor theCASCADEkeyword to handle dependencies more explicitly:

DROP SCHEMA IF EXISTS temp_schema CASCADE;

This safely handles scenarios where the schema may not exist or contains objects that would normally block deletion.

Incorrect options:

DROP DATABASE temp_schemaremoves an entire database, not a schema.

DELETE SCHEMAis not valid SQL; SQL uses DROP for schema removal.

DROP VIEW temp_schemaapplies only to removing a view object.

Dropping a schema requires USAGE and OWNERSHIP privileges, typically granted to roles such as SYSADMIN or ACCOUNTADMIN.

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

The DATA_RETENTION_TIME_IN_DAYS parameter controls the Time Travel retention period for Snowflake objects such as tables, schemas, and databases. It specifies the number of days that historical data (prior versions of rows or dropped objects) is retained and accessible via Time Travel. Within this retention window, users can query data “as of” a previous time, restore dropped objects, or clone objects at a historical point.

DATE_OUTPUT_FORMAT determines the display format for date values and is unrelated to historical retention. TIMEZONE affects how timestamps are interpreted and displayed, not how long data history is preserved. USE_CACHED_RESULT governs whether Snowflake may return cached query results, not Time Travel behavior.

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

Snowflake is a fully cloud-native platform that supports deployment across all three major cloud providers:AWS, GCP, and Azure. Snowflake delivers consistent functionality across each provider, allowing organizations to choose the cloud best suited for regulatory, architectural, and business requirements. Although small feature differences may exist between cloud providers, Snowflake’s core capabilities—virtual warehouses, storage layers, security, data sharing, and governance—operate uniformly across all three platforms. This multi-cloud support gives Snowflake strong flexibility for hybrid, multi-region, and multi-cloud deployments.

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

The SQL command DROP SCHEMA IF EXISTS marketing_schema; instructs Snowflake to drop the specified schema only if it already exists in the database. The inclusion of the IF EXISTS clause prevents Snowflake from returning an error when the schema is not found. If the schemadoesexist, Snowflake drops it along with all objects it contains, unless a RESTRICT clause is enforced (RESTRICT is the default when not explicitly specified, but Snowflake also supports CASCADE for forcing deletion of all objects). Importantly, this command never creates a schema; it strictly performs a conditional drop. In addition, Snowflake supports Time Travel, so the dropped schema can typically be restored—using UNDROP SCHEMA—within the defined retention window. Therefore, the correct interpretation is that the schema is dropped only if it exists, and no error is thrown otherwise.

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

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.

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

A fully qualified name uniquely identifies Snowflake objects by specifyingdatabase.schema.object. This prevents ambiguity when multiple schemas or databases contain objects with identical names. Fully qualified names ensure that SQL statements operate on the intended object. They are not used for storing data, managing permissions, or configuring network settings. Their core purpose is precise object identification.