SnowPro Core Certification COF-C02 Syllabus Exam Questions Answers

Snowflake supports a variety of subquery types, including both correlated and uncorrelated subqueries. The correct answers are B and E, which highlight Snowflake's flexibility in handling subqueries within SQL queries.

Uncorrelated Scalar Subqueries: These are subqueries that can execute independently of the outer query. They return a single value and can be used anywhere a value expression is allowed, offering great flexibility in SQL queries.

EXISTS, ANY/ALL, and IN Subqueries: These subqueries are used in WHERE clauses to filter the results of the main query based on the presence or absence of matching rows in a subquery. Snowflake supports both correlated and uncorrelated versions of these subqueries, providing powerful tools for complex data analysis scenarios.

Examples and Usage:

Uncorrelated Scalar Subquery:

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated EXISTS Subquery:

SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customer c WHERE c.id = o.customer_id AND c.region = 'North America');

The query acceleration service in Snowflake is beneficial for workloads that include on-demand data analyses. This service optimizes query performance by dynamically allocating additional resources to execute queries faster, particularly useful for ad-hoc analysis where data volume and complexity can vary.

References:

Snowflake Documentation: Query Acceleration Service

The parameter CLIENT_SESSION_KEEP_ALIVE ensures that a session remains active indefinitely as long as the connection is active, even if there is no user activity.

Setting this to TRUE overrides session inactivity timeouts.

References:

Snowflake Documentation: Session Parameters

Snowflake Session Management

When creating a virtual warehouse in Snowflake, users have the option to set several properties to manage its behavior and resource usage. Two of these optional properties are Auto-suspend and Resource monitor.

Auto-suspend: This property defines the period of inactivity after which the warehouse will automatically suspend. This helps in managing costs by stopping the warehouse when it is not in use.

CREATE WAREHOUSE my_warehouse

WITH WAREHOUSE_SIZE = 'XSMALL'

AUTO_SUSPEND = 300; -- Auto-suspend after 5 minutes of inactivity

Resource monitor: Users can assign a resource monitor to a warehouse to control and limit the amount of credit usage. Resource monitors help in setting quotas and alerts for warehouse usage.

CREATE WAREHOUSE my_warehouse

WITH WAREHOUSE_SIZE = 'XSMALL'

RESOURCE_MONITOR = 'my_resource_monitor';

References:

Snowflake Documentation: Creating Warehouses

Snowflake Documentation: Resource Monitors