Free COF-C03 practice test

Temporary tables exist only for the duration of the session that created them. They are session-scoped and invisible to other sessions. When the session ends, the table—and all its data—is automatically dropped, so no ongoing storage charges apply after that point. Option A (Permanent table) is wrong because permanent tables persist until explicitly dropped and incur storage costs plus support full Time Travel and Fail-safe. Option D (Transient table) is wrong because transient tables persist across sessions until explicitly dropped; they only differ from permanent tables in that they have no Fail-safe period and a maximum Time Travel of 1 day, but they do incur ongoing storage costs. Option B (External table) is wrong because external tables reference data stored outside Snowflake (e.g., in a cloud object store) and also persist until dropped.

Temporary tables in Snowflake are session-scoped (visible only within the session that created them), automatically dropped at session end, and have 0 days of Fail-safe and up to 1 day of Time Travel (configurable to 0). This matches all three stated requirements. Option A (permanent table) persists beyond the session, has up to 90 days of Time Travel, and 7 days of Fail-safe. Option C (transient table) persists across sessions (visible to users with privileges), has 0 days of Fail-safe, and up to 1 day of Time Travel—it is NOT automatically dropped when the session ends. Option B (external table) references data stored outside Snowflake in cloud object storage; it has no Fail-safe and no Time Travel, but it is a permanent metadata object and is not session-scoped.

Multi-cluster virtual warehouses (option A) are an Enterprise-only feature not available in Standard. Time Travel up to 90 days (option B) requires Enterprise; Standard supports only up to 1 day of Time Travel. Materialized views (option C) are available starting with Enterprise edition. Option D (Fail-safe) is available in all editions for permanent tables — it is not Enterprise-exclusive. Option E (Dynamic data masking) is actually an Enterprise feature as well; however, among the distractors here, Fail-safe and External stages are the clearly available-in-Standard options. Option F (External stages) are available in all editions including Standard. Therefore the three Enterprise-exclusive features listed are A, B, and C.

A short auto-suspend window (commonly 5–10 minutes) is the standard starting recommendation: it lets the warehouse stay warm through a burst of related queries (avoiding repeated cold starts) while still suspending — and stopping credit consumption — once the analyst is done for a while. Option A (never suspend) burns credits continuously even when idle. Option C (24 hours) leaves the warehouse running, and billing, for nearly a full day of inactivity, which is wasteful for sporadic use. Option D is factually wrong — auto-suspend is a configurable warehouse parameter (AUTO_SUSPEND).

Auto-resume means the warehouse automatically starts back up the moment a query is submitted to it while suspended, so users don't have to manually restart compute. Option A describes a scheduled trigger, which isn't how auto-resume works — it's query-driven, not time-driven. Option C describes the opposite of auto-resume (manual resume). Option D is wrong: auto-resume/auto-suspend apply to warehouses generally, single-cluster or multi-cluster.

Standard Edition provides full SQL support, warehouses, storage, standard encryption, and network policies — everything this startup listed. Enterprise (D), Business Critical (A), and VPS (C) each add capabilities (extended Time Travel, HIPAA/PCI-type compliance support, dedicated infrastructure) that cost more and are not needed here, so they would not minimize cost.

Option B is correct. A Snowflake Stream (specifically a standard stream) records DML changes — inserts, updates, and deletes — at the row level against the source table. Downstream processes consume the stream by reading the special metadata columns (METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID) and applying those changes incrementally. Option A (materialized view) is an automatically refreshed read-only copy of query results; it does not expose individual CDC change records for downstream consumption. Option C (dynamic table) continuously materializes query results similar to a materialized view with automatic refresh, but it does not expose change metadata rows; it is a target result set, not a CDC source. Option D (COPY INTO) is for bulk loading data from staged files, not for tracking DML change events on an existing table.

Option A uses colon (path) notation followed by dot notation, which is the standard Snowflake syntax for traversing nested VARIANT objects. Option B uses bracket notation throughout, which is equally valid and produces the same result. Option C is incorrect because plain dot notation without the colon prefix is not valid for VARIANT columns — Snowflake requires the colon (:) as the entry point into a VARIANT path. Option D is incorrect because GET() only retrieves a top-level key by name; it does not support dot-separated nested path strings like 'contact.email'. Option E, while it mixes notations (colon then bracket), is actually also valid in Snowflake — however, since the question asks to select TWO and A and B are the most canonical and unambiguous forms, E is a distractor that works but is not the primary answer expected; in actual Snowflake, A, B, and E all work, but this question tests knowledge of the two most standard access patterns.

Options A and B are correct. A: MAX_FILE_SIZE = 104857600 sets the maximum size to 100 MB (104,857,600 bytes); Snowflake splits output into multiple files when this limit is reached. B: When unloading to Parquet format, Snowflake applies Snappy compression by default. Option C is wrong because HEADER = TRUE is a CSV-specific option; it is not required and does not apply to Parquet files. Option D is partially true (Snowflake does generate prefixed filenames), but this statement is not strictly accurate — the naming convention uses the stage path prefix provided by the user, not a fixed internal prefix, so it is not categorically true as stated. Option E is wrong because the FROM clause in a COPY INTO <location> command explicitly supports subqueries, as shown in the scenario.

OBJECT is specifically a key-value (JSON-object-like) structure, while VARIANT is the broader, general-purpose semi-structured container that can hold an OBJECT, an ARRAY, or a scalar value — VARIANT is effectively the superset type used when the exact shape isn't fixed to 'always an object.' Option B collapses a real distinction. Option C mischaracterizes both types, which are not restricted to a single scalar kind. Option A is false; VARIANT remains a core, actively used type, not deprecated.

A LATERAL join lets a table function's arguments (like FLATTEN's input) reference columns from a preceding table/row in the same FROM clause, evaluating the function once per input row — this row-by-row correlation is exactly what's needed to flatten an array from each row of t. A plain (non-lateral) join cannot pass per-row correlated values as arguments into a table function call, so LATERAL isn't just a tuning option, it's functionally required for this pattern. Options B, C, and D describe effects LATERAL does not have.

BOOLEAN is the dedicated Snowflake type for two-valued (plus NULL) logic, and it's the clearest, most storage-efficient, and most self-documenting choice for a true/false flag. NUMBER(1,0) (option A) could technically encode 0/1 but loses the semantic clarity and built-in TRUE/FALSE literal support that BOOLEAN provides, and invites ambiguity about what values are valid. VARCHAR(5) (option B) could store 'TRUE'/'FALSE' as text but is a poor fit — it wastes space and doesn't get boolean-specific optimizations or comparison semantics. VARIANT (option C) is for semi-structured data and is unnecessary overhead for a simple flag.

Time Travel retains data for up to the configured DATA_RETENTION_TIME_IN_DAYS (here, 90 days). After the Time Travel window closes, Fail-Safe provides an additional 7-day window (for permanent tables in Enterprise edition and above), making total coverage 90 + 7 = 97 days. At 95 days, Time Travel is expired but Fail-Safe is still active (95 < 97). However, Fail-Safe is NOT self-service — it is a Snowflake-managed disaster-recovery mechanism accessible only through Snowflake Support, and recovery is not guaranteed. — Option D is wrong: the 90-day Time Travel window has already expired at 95 days. — Option B is wrong: Fail-Safe is not a self-service feature; users cannot query or restore Fail-Safe data themselves. — Option A is wrong: the combined 97-day window (90 + 7) does cover the 95-day point; data is still in Fail-Safe, not permanently lost — though recovery requires Support.

Correct (A): Setting DATA_RETENTION_TIME_IN_DAYS = 0 disables Time Travel entirely for that table. No historical versions are retained, so UNDROP, AT, and BEFORE queries cannot be used to recover data. Correct (D): Time Travel stores previous versions of changed/deleted data, which counts toward storage billing. Eliminating Time Travel retention removes these historical versions and their associated storage charges. Incorrect (B): Setting retention to 0 does not cause immediate deletion of the live table data; only the historical change versions are not retained. The table and its current data remain intact. Incorrect (E): Fail-safe is a separate 7-day recovery window managed entirely by Snowflake after the Time Travel window expires. Setting DATA_RETENTION_TIME_IN_DAYS = 0 still subjects the table's dropped or expired data to a fail-safe period (for standard tables); fail-safe cannot be disabled by users. Incorrect (C): The table remains fully visible in INFORMATION_SCHEMA regardless of the Time Travel setting.

The result cache is bypassed when a query contains non-deterministic functions (e.g., CURRENT_TIMESTAMP(), RANDOM(), UUID_STRING()) because Snowflake cannot guarantee the result would be identical on re-execution. Option A is incorrect — suspending and resuming a warehouse does not clear the result cache, which is maintained at the Cloud Services layer independently of warehouse state. Option C is incorrect — automatic clustering can invalidate the result cache if underlying micro-partitions change, but the question states no data changes have occurred; moreover, clustering alone does not continuously invalidate cache on every run without data modification. Option D is incorrect — the result cache is role-agnostic and available to all users, not restricted to ACCOUNTADMIN.

A, B and C are correct. Snowflake supports (A) username/password authentication with a second factor such as Duo; (B) key-pair authentication, where an RSA public key is registered on the user object; and (C) OAuth access tokens issued by Snowflake OAuth or a supported external authorization server. D is wrong: Kerberos is not a Snowflake authentication method. E is wrong: Snowflake does not accept raw X.509 client certificates at the driver layer; certificate-style authentication uses the key-pair mechanism registered in Snowflake. F is wrong: Snowflake has no anonymous access; every connection must authenticate as a user. (SAML 2.0 SSO through a configured identity provider is also a supported method, but it is not one of the options here.)

Option C is correct: SAML2_SIGN_REQUEST = TRUE on the SAML security integration instructs Snowflake to require that SAML assertions from the IdP are signed, ensuring assertion integrity. Option E is correct: To prevent SSO users from bypassing SSO with a Snowflake password, you can set PASSWORD = NULL on the user (removing their local password) or use authentication policies with DISABLE_LOCAL_AUTH to block password-based login. Option D is incorrect; SAML2_SNOWFLAKE_ISSUER_URL identifies Snowflake as the service provider to the IdP — it does not enforce signed assertions. Option B is incorrect; SAML2_FORCE_AUTHN forces a fresh IdP authentication (no SSO caching) but does not sign assertions or prevent local login. Option A is incorrect; DISABLE_MFA removes MFA requirements but has no effect on whether a local Snowflake password can be used. Option F is incorrect; HAS_RSA_PUBLIC_KEY is for key-pair authentication configuration and is unrelated to preventing password login or enforcing signed SAML assertions.

The correct answers are A and C. A is correct because ALTER DATABASE <secondary_db> PRIMARY (or ALTER DATABASE <name> PROMOTE TO PRIMARY in newer syntax) is the command used to promote a secondary database to become the new primary, enabling write operations. C is correct because after promotion, clients must be redirected to the account hosting the new primary database to read and write data. B is wrong because ENABLE REPLICATION OF SCHEMAS is part of the initial replication setup, not the failover process. D is wrong because you do not need to drop the original primary; promotion handles the role swap and the old primary automatically becomes a secondary. E is wrong because REFRESH is used to synchronize a secondary from its primary during normal operation, not as a failover step (and the primary may be unavailable during an outage). F is wrong because database replication in Snowflake does replicate grants along with the database objects.

For a JDBC-compliant BI tool to connect to Snowflake, the Snowflake JDBC driver needs to be made available to the tool (as a JAR/library), and a properly formed JDBC connection URL (including the account identifier and relevant parameters like warehouse, database, role) must be configured — standard JDBC integration practice. Option D is unnecessary and incorrect; SnowSQL is a separate CLI tool, not a substitute for JDBC integration within a BI product. Option B is false — Snowflake explicitly supports JDBC connectivity, which is exactly why the driver exists. Option C is false; both JDBC and ODBC (among other clients) are supported, not just ODBC.

Snowsight's Activity/Query History area lets appropriately privileged users browse account-wide (not just their own) query history and open the graphical Query Profile for a selected query, which is the standard way to investigate performance. Option A is an unnecessary workaround. Option B is false — Query Profile is a Snowsight (and classic web UI) feature, not something limited to SnowSQL, which is command-line only and has no graphical profile view. Option C overstates ACCOUNTADMIN exclusivity; visibility depends on privileges/role, not solely on being ACCOUNTADMIN.