COF-C03 Snowflake AI Data Cloud Features and Architecture Practice Test 7 — 15 Questions

Question 1

A user runs a query that was executed by another user 10 minutes ago, and the underlying data has not changed. The query returns results instantly without using a virtual warehouse. Which Snowflake feature explains this behavior?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The query result cache stored in the Cloud Services layer.. The query result cache is a Cloud Services feature that persists query results for 24 hours. When an identical query is re-executed and the underlying data remains unchanged, Snowflake serves the cached result directly, bypassing warehouse computation and delivering sub-second response times.

Answer

The metadata cache in the Cloud Services layer.

Answer

The materialized view automatically created for the query.

Answer

The local disk cache on the virtual warehouse.

Question 2

A data engineering team is loading a 500 GB CSV file into a Snowflake table using the COPY command. They notice that the load is taking longer than expected and the warehouse is showing high CPU utilization. Which of the following is the MOST likely cause for the slow performance?

Accepted Answer

The file is too large for a single COPY command and should be split into smaller files.. For optimal load performance, Snowflake recommends splitting large data files into multiple smaller files (100-250 MB compressed) to allow parallel loading across the warehouse nodes. A single large file cannot be parallelized, leading to slower loads and potential resource contention. Increasing warehouse size or tweaking other parameters does not address the fundamental limitation of loading a single large file.

Answer

The COPY command is using a single thread by default and should be configured for multi-threading.

Answer

The CSV file contains too many columns, causing parsing overhead.

Answer

The warehouse size is too small and should be increased to handle the file size.

Question 3

A user runs a query that filters on a column with a very high cardinality, such as a timestamp with millisecond precision. The table is extremely large and is not clustered. What is the most likely impact on query performance?

Accepted Answer

The query will scan all micro-partitions because pruning is ineffective for high-cardinality columns.. For a large, unclustered table, filtering on a high-cardinality column like a millisecond timestamp leads to ineffective micro-partition pruning. Snowflake's pruning relies on min/max metadata, and high-cardinality columns have wide, overlapping ranges across micro-partitions. As a result, the query scans most or all micro-partitions, causing poor performance. Clustering or search optimization could help, but neither is present here.

Answer

The query will use the search optimization service to quickly find matching rows.

Answer

The query will benefit from the query result cache because the filter is highly selective.

Answer

Snowflake will automatically create a clustering key on the high-cardinality column to improve pruning.

Question 4

A Snowflake account has a virtual warehouse that is configured with AUTO_SUSPEND = 60 seconds and AUTO_RESUME = TRUE. A user runs a query that takes 5 minutes to complete. After the query finishes, the warehouse remains idle for 2 minutes and then suspends. During the idle period, what charges apply?

Accepted Answer

Charges apply for the entire 2-minute idle period because the warehouse is still running.. Snowflake charges for a virtual warehouse based on the time it is running, regardless of whether it is actively processing queries. During the idle period before auto-suspension, the warehouse continues to consume credits. The AUTO_SUSPEND setting controls how long the warehouse remains running after the last query, but any time it is running, charges apply. Therefore, the entire idle period incurs charges until the warehouse suspends.

Answer

No charges apply during the idle period because the warehouse is not processing queries.

Answer

Charges apply only if the warehouse is resumed during the idle period.

Answer

Charges apply only for the first 60 seconds of idle time, as per the AUTO_SUSPEND setting.

Question 5

A Snowflake administrator is configuring a new virtual warehouse to support a data science team that runs occasional, complex queries on large datasets. The team requires fast performance and minimal latency. Which TWO warehouse configuration settings should the administrator consider to optimize performance for this workload? (Choose two.)

Accepted Answer

Enable the Query Acceleration Service (QAS) for the warehouse.. For occasional complex queries on large datasets, increasing warehouse size provides more compute power for faster processing. Additionally, enabling the Query Acceleration Service (QAS) can offload eligible query parts to shared resources, further improving performance. Multi-cluster warehouses target concurrency, not single-query speed, and AUTO_SUSPEND settings affect cost, not performance.

Answer

Configure the warehouse to use a larger number of clusters with scaling policy set to ECONOMY.

Answer

Enable multi-cluster warehouse with a minimum cluster count of 2.

Answer

Set AUTO_SUSPEND to a low value (e.g., 60 seconds) to minimize idle costs.

Question 6

A user needs to query data stored in an external stage that points to an Amazon S3 bucket. The user has been granted the necessary privileges on the stage and the storage integration. Which of the following is required to successfully query the data directly from the stage using a SELECT statement?

Accepted Answer

The external stage must be defined with a file format that matches the data.. To query data directly from an external stage, the stage must have a file format defined that matches the data files. This enables Snowflake to interpret the data correctly. Loading data into a table is optional, and administrative roles or same-region buckets are not required. The key is proper configuration of the stage and appropriate privileges.

Answer

The S3 bucket must be in the same AWS region as the Snowflake account.

Answer

The user must have the ACCOUNTADMIN role to query external stages.

Answer

The data must first be loaded into a Snowflake table using COPY INTO.

Question 7

A data analyst wants to explore semi-structured JSON data stored in a Snowflake table column. They need to extract specific fields and flatten nested arrays. Which Snowflake feature is best suited for this task?

Accepted Answer

VARIANT data type with dot notation and FLATTEN function. Snowflake's VARIANT data type is designed for semi-structured data such as JSON. Analysts can access fields using dot notation (e.g., column:field) and use the FLATTEN function to expand arrays into multiple rows. This native support eliminates the need for external processing and allows SQL-based exploration. The other options reference incompatible data types or functions from other systems.

Answer

OBJECT data type with LATERAL VIEW EXPLODE

Answer

ARRAY data type with UNNEST function

Answer

String data type with JSON_EXTRACT_PATH_TEXT function

Question 8

A company needs to ensure that data stored in a Snowflake table is encrypted at rest with keys that the company manages and rotates independently. Which Snowflake feature should be configured?

Accepted Answer

Tri-Secret Secure with customer-managed keys.. Tri-Secret Secure allows customers to add their own key from a cloud provider's KMS to Snowflake's encryption hierarchy. The customer can rotate or revoke their key at any time, giving them control over data access. This satisfies the need for customer-managed keys with independent rotation.

Answer

Periodic rekeying of Snowflake-managed keys.

Answer

Client-side encryption before loading data into Snowflake.

Answer

Enabling AES-256 encryption on the virtual warehouse.

Question 9

A Snowflake administrator is configuring a new external stage to load data from an Amazon S3 bucket. The storage integration already exists and is named MY_S3_INT. The administrator needs to reference this integration when creating the stage. Which SQL statement correctly creates the stage with the necessary parameters?

Accepted Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION=MY_S3_INT;. To create an external stage that uses a storage integration, the correct syntax includes the STORAGE_INTEGRATION parameter followed by the integration name as an identifier (unquoted). This allows Snowflake to assume the IAM role defined in the integration to access the S3 bucket securely, without embedding credentials.

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' INTEGRATION=MY_S3_INT;

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION='MY_S3_INT';

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');

Question 10

A Snowflake user needs to query data that is stored in an external table pointing to Parquet files in an external stage. The user wants to optimize query performance by reducing the amount of data scanned. Which feature should the user leverage?

Accepted Answer

Use partition pruning by organizing the external files into a directory structure that reflects common filter columns. For external tables, Snowflake can prune partitions based on the directory structure of the external files. By organizing files into folders that correspond to common filter columns (e.g., year/month/day), queries with filters on those columns can skip irrelevant files, significantly reducing data scanned and improving performance.

Answer

Create a materialized view on the external table

Answer

Define a clustering key on the external table

Answer

Enable the search optimization service on the external table

Question 11

An administrator is configuring a virtual warehouse for a data science team that runs unpredictable, long-running training queries. The team wants the warehouse to shut down automatically when idle to save credits, but they also want queries to start immediately when a new request arrives without waiting for a manual resume. Which configuration should the administrator apply?

Accepted Answer

Set the warehouse to auto-suspend after a short idle period and rely on auto-resume to start it when a query is submitted.. Auto-suspend stops a virtual warehouse after a defined idle period, preventing credit usage when no queries are running. Auto-resume, which is enabled by default, automatically starts the warehouse when a new query is submitted. Together they deliver the desired behavior: the warehouse shuts down when idle and starts immediately on demand, with no manual resume step.

Answer

Set the warehouse to auto-suspend and enable multi-cluster scaling to ensure queries start immediately.

Answer

Configure the warehouse to auto-suspend and disable auto-resume so the team controls when the warehouse starts.

Answer

Leave the warehouse running permanently and rely on the Query Result Cache to reduce credit consumption.

Question 12

A platform team is evaluating Snowflake's Time Travel feature for a production database. They need to understand which capabilities Time Travel provides for recovering from accidental data changes and for querying historical data. (Choose two.)

Accepted Answer

Restore a dropped table, schema, or database within the retention period using UNDROP or by cloning from a historical point.. Time Travel provides two core capabilities: querying data as it existed at a past point using AT or BEFORE, and recovering dropped or modified objects within the retention period through UNDROP or historical cloning. Both are directly supported by the feature and are commonly used for auditing and accidental-change recovery, while failover and indefinite retention are handled by other mechanisms.

Answer

Encrypt historical data with a separate customer-managed key that is distinct from the key used for current data.

Answer

Automatically fail over read and write operations to a secondary account in a different region without data loss.

Answer

Permanently retain all historical versions of every table indefinitely without any retention period configuration.

Question 13

A Snowflake administrator is configuring a new virtual warehouse for a data science team. The team requires the ability to run multiple concurrent queries without queuing, and they want to minimize credit consumption during idle periods. The administrator needs to configure the warehouse with appropriate settings. Which two actions should the administrator take? (Choose two.)

Accepted Answer

Enable multi-cluster warehouse with auto-scaling.. To handle multiple concurrent queries without queuing, enabling multi-cluster warehouse with auto-scaling allows Snowflake to add clusters dynamically as needed. To minimize credit consumption during idle periods, setting a short auto-suspend period ensures the warehouse stops when not in use. Together, these settings provide both concurrency and cost efficiency. Other options either do not address the requirements or could hinder performance.

Answer

Configure the warehouse to use a smaller size to reduce credits.

Answer

Set the warehouse to auto-resume when a query is submitted.

Answer

Enable the query result cache to avoid re-execution.

Question 14

A Snowflake user is designing a table to store semi-structured data from JSON logs. The user wants to query specific fields within the JSON efficiently and also retain the ability to query the entire JSON object. The user also wants to minimize storage costs. Which approach should the user take?

Accepted Answer

Store the entire JSON object in a single VARIANT column.. Storing JSON in a VARIANT column leverages Snowflake's automatic optimization for semi-structured data, including columnar storage and path extraction. This provides efficient querying of specific fields while retaining the full JSON object, and it minimizes storage costs through compression and automatic micro-partition optimization. Other approaches either increase storage costs, reduce flexibility, or do not utilize Snowflake's native optimizations.

Answer

Store the JSON as a string in a VARCHAR column.

Answer

Store the JSON in an external stage and query it using external tables.

Answer

Shred the JSON into separate relational columns for each field.

Question 15

A Snowflake administrator needs to ensure that a virtual warehouse automatically suspends after a period of inactivity to save costs. Which parameter should they configure?

Accepted Answer

AUTO_SUSPEND. AUTO_SUSPEND is the parameter that defines the idle time before a virtual warehouse is automatically suspended. Configuring it appropriately ensures that warehouses do not run unnecessarily, reducing credit usage. AUTO_RESUME is complementary but does not suspend the warehouse. The other parameters affect query timeout or warehouse size, not automatic suspension.

Answer

WAREHOUSE_SIZE

Answer

STATEMENT_TIMEOUT_IN_SECONDS

Answer

AUTO_RESUME