Free COF-C03 flashcards

What are the three independent layers of the Snowflake architecture?

Snowflake separates the platform into database storage, query processing (compute via virtual warehouses), and cloud services (coordination, authentication, metadata, optimisation). Each layer scales on its own without affecting the others, which is the defining trait of the architecture.

Snowflake's three layers are storage, an in-memory cache, and a reporting layer that renders dashboards.

An in-memory cache and a reporting layer are not the named architectural layers. The three layers are database storage, query processing, and cloud services. Caching does exist, but only transiently within the compute layer, not as a top-level layer.

In a Query Profile, what do 'Bytes spilled to local storage' and 'Bytes spilled to remote storage' indicate, and how is the spilling best removed?

Spilled bytes mean intermediate operator results did not fit in the virtual warehouse memory and overflowed first to local SSD and then to remote storage, which slows execution. Scaling up to a larger warehouse size provisions more memory and local SSD per cluster, so the operators stay in memory and the spilling is reduced or eliminated.

Adding a clustering key on the join columns fixes a query that is spilling bytes to local and remote storage.

Clustering improves micro-partition pruning, which reduces partitions scanned, not memory pressure. Spilled bytes signal a memory shortfall, so the fix is a larger warehouse size for more memory and local SSD, not better pruning.

In the standard Snowflake role hierarchy, which system-defined role should custom object-creating roles be granted to, and why?

Grant them to SYSADMIN. SYSADMIN owns the recommended object-creation branch of the hierarchy, so a custom role placed beneath it can build and own databases, warehouses, and schemas while every role above SYSADMIN, including ACCOUNTADMIN, retains the ability to manage those objects.

Custom roles that create objects should be granted directly to ACCOUNTADMIN so account settings stay consistent.

Granting custom object-creating roles directly under ACCOUNTADMIN concentrates excessive privilege at the very top and breaks least privilege. The standard pattern is to grant them to SYSADMIN, which owns the object-creation branch; higher roles still inherit management ability.

How do you stage local files for one specific target table in Snowflake without creating any stage object first?

Every table automatically has an implicit table stage addressed as @%table_name. It needs no CREATE STAGE step and is intended for files that load into that one table, so COPY INTO can reference the staged files directly by that name.

A Snowflake user stage (@~) is automatically scoped to whichever single table you later load from it.

The user stage exists per user and is referenced with @~, but it is not bound to any one table. It can hold files destined for many tables, so the claimed automatic single-table scoping does not exist.