Free COF-C03 practice test: 30 questions with answers

Virtual warehouses form the compute layer, and they can scale independently of stored data. The cloud services layer handles authentication, metadata and query compilation, and the storage layer holds the micro-partitions.

VARIANT stores semi-structured data such as JSON, Avro and Parquet, and it can be queried with path notation. BLOB and RAW store binary data, and XMLTYPE is an Oracle type that Snowflake does not provide.

A schema is the second level of the database object hierarchy and holds tables, views, stages and similar objects. Warehouses provide compute, roles govern privileges, and resource monitors track credit usage.

Standard edition supports one day of Time Travel, which is the default for permanent tables. Enterprise and higher editions can extend retention up to 90 days, and a zero-day setting disables Time Travel.

The cloud services layer coordinates authentication, metadata, query optimization and transactions across the platform. The compute layer runs the query work in warehouses, the storage layer holds data, and client drivers only connect to the service.

USERADMIN manages users and roles and is designed for identity administration without owning data objects. ACCOUNTADMIN has the broadest account rights, SYSADMIN owns databases and warehouses, and PUBLIC is granted to everyone by default.

Key-pair authentication lets a service sign with a private key while Snowflake stores only the public key, and keys can be rotated. Shared passwords and personal tokens are weaker, and browser SSO needs an interactive user.

A network policy allows or blocks connections based on IP address ranges and can be applied at account or user level. Masking policies hide column values, resource monitors control credit usage, and security integrations configure external authentication.

A row access policy evaluates each row against the querying role or user and returns only the rows allowed. Masking changes column values rather than filtering rows, tags only label objects, and network policies control connection sources.

ACCOUNTADMIN is the top system role and controls account-level settings and billing, so it should be limited to a few people. SYSADMIN manages objects, SECURITYADMIN manages grants, and USERADMIN manages users and roles.

The result cache stores query results for up to 24 hours and returns them without a warehouse when the data is unchanged. The local disk cache lives on the warehouse, and the metadata cache helps compilation rather than returning results.

Suspended warehouses do not consume credits, and resizing changes compute without relocating stored data. Warehouses do not keep permanent copies of tables, and billing is based on warehouse size and running time, not rows scanned.

A clustering key on the filter column helps Snowflake co-locate similar values, so more micro-partitions can be pruned. Bigger warehouses add cost without fixing scan volume, disabling the cache removes a benefit, and a full materialized view duplicates the data.

Query Profile shows the execution plan with per-operator timings, row counts and data scanned. Resource monitors track credits, network policies control access, and the tasks view lists scheduled task history.

The search optimization service builds access paths that speed up selective point lookups and some substring searches. Multi-cluster warehouses address concurrency, and full materialized views or clustering every column add cost without targeting this access pattern.

COPY INTO table reads staged files and loads them into the target table. PUT uploads local files to a stage, GET downloads files from a stage, and LIST shows the files present.

SKIP_HEADER with a value of 1 skips the first line of each file during COPY INTO. The other option names are not valid Snowflake file format parameters for this purpose.

User and table stages are internal stages that Snowflake manages automatically with each user or table. External stages point to storage outside Snowflake, and a pipe is a loading object rather than a stage.

Snowpipe uses cloud event notifications to detect new files and loads them automatically. Schedules, manual commands and queries do not drive Snowpipe's continuous loading model.

COPY INTO a stage location unloads query results, and the file format sets Parquet as the output type. GET downloads from a stage, PUT uploads to one, and a stream tracks changes rather than exporting data.

A stream records changes to a table since its last offset, which enables change data capture. A task runs SQL on a schedule, a pipe loads files, and a sequence generates numbers.

A user-defined table function returns a set of rows that can be selected from like a table. Scalar functions return one value per call, pipes load data, and stages hold files.

Dynamic tables are defined by a query and refresh automatically to meet the configured target lag. They require no manual refresh, can be joined like other tables, and store data in Snowflake rather than external stages.

MERGE performs conditional updates and inserts in a single statement, which is an upsert pattern. TRUNCATE empties a table, EXPORT is not a Snowflake statement for this, and CREATE SEQUENCE generates numbers.

Tasks run on a schedule or after a predecessor, and they can be triggered when a stream has change data. Tasks use a warehouse for their SQL, they are available on standard editions, and they can reference streams.

A clone is a metadata copy that shares underlying micro-partitions, so storage grows only as the clone or source changes. Databases can be cloned as well as tables, and clones can be created in other schemas and databases.

Secure data sharing lets consumers query the provider's live data without copying it. Exports and emails create copies, and replication creates a separate copy refreshed on a schedule.

Time Travel lets users query and restore historical data within the retention period. Fail-safe is a fixed recovery period managed by Snowflake, and customers cannot configure or disable it. Transient tables are limited to one day of Time Travel, and UNDROP restores dropped objects during Time Travel rather than Fail-safe.

Object tags attach classification labels to tables and columns, which supports centralized governance and reporting. Resource monitors track credits, clustering keys affect pruning, and dedicated warehouses isolate compute rather than label data.

Time Travel with AT or BEFORE lets users query a table as of a past time within the retention period. Fail-safe is a last-resort recovery that needs Snowflake support, and replication and clone deletion do not query historical state.