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.

Why can a Snowflake reporting job switch to a larger virtual warehouse without copying or reloading any table data?

Snowflake's storage layer is centralised and decoupled from compute. Virtual warehouses of any size query the same persisted data directly, so resizing or switching compute never requires moving, copying, or reloading the data.

Common misconceptionThat a bigger warehouse needs its own copy of the data. Storage is centralised, so warehouses of any size read the same persisted data directly.

Which source supports account-wide query reporting over nine months of history where some data latency is acceptable?

The SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view retains query history for the entire account for one year, covering queries from users and sessions that have ended. It carries a documented latency before the newest queries appear, which suits long-range auditing where recent-row delay is tolerable.

Common misconceptionThat INFORMATION_SCHEMA.QUERY_HISTORY covers months of account-wide queries with no latency, when it is scoped and limited to about the last seven days.

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.

Common misconceptionThat converting to a multi-cluster warehouse stops a query from spilling bytes, though extra clusters add concurrency, not memory, to the single cluster running the query.

Which Snowflake system-defined role is purpose-built to create and manage users and roles below ACCOUNTADMIN?

SECURITYADMIN. It inherits the USERADMIN role and additionally holds the global MANAGE GRANTS privilege, so it can create and manage users and roles and modify grants account-wide, delivering onboarding capability without exposing the full ACCOUNTADMIN privilege set.

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.

Common misconceptionThat granting custom roles straight to ACCOUNTADMIN keeps account settings consistent. It concentrates privilege at the top and breaks least privilege.

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.

How can COPY INTO parse staged files and report all parsing errors without writing any rows to the target table?

Set VALIDATION_MODE (for example RETURN_ALL_ERRORS) on COPY INTO. It runs the load as a validation pass: it reads and parses the actual staged files against the file format and table, returns the errors it would have hit, and commits nothing to the target.

Common misconceptionThat ON_ERROR CONTINUE performs a dry run with no writes. It loads every good row and only skips bad ones; VALIDATION_MODE is the true dry run.