COF-C03 Practice Test 5 — 25 Questions

Question 1

A data analyst needs to unload the results of a query from a Snowflake table to a local machine. The analyst wants to use the Snowflake web interface (Snowsight) to download the data as a CSV file. Which of the following is the correct approach?

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

Accepted Answer

Run the query in a worksheet, then use the 'Download results' button to save the output as a CSV file.. Snowsight provides a built-in feature to download query results as a CSV file. After executing a query, the results pane includes a 'Download results' button, which exports the displayed data to a CSV file on the local machine. This is the simplest and most direct method for ad-hoc downloads, especially for smaller datasets, and it does not require staging or command-line tools.

Answer

Create a named internal stage, unload the data using COPY INTO @stage, then use GET to download the file to the local machine.

Answer

Execute a COPY INTO command targeting a local file path, then download the file from the user's home directory.

Answer

Use the Snowflake REST API to execute the query and retrieve the results as a CSV stream.

Question 2

A data engineer is configuring a Snowflake external stage that points to an Amazon S3 bucket. The bucket is in the same region as the Snowflake account. The engineer wants to avoid embedding long-lived AWS credentials in the stage definition and instead use a secure, temporary credential mechanism. Which authentication method should be used for the external stage?

Accepted Answer

Use a storage integration with an IAM role and external ID.. The secure and recommended way to grant Snowflake access to an external S3 bucket without embedding long-lived credentials is to create a storage integration. This integration establishes a trust relationship between Snowflake and AWS, allowing Snowflake to assume an IAM role and obtain temporary credentials. The other options either embed static keys or confuse AWS IAM features with Snowflake capabilities, failing to meet the security requirement.

Answer

Create a Snowflake user with an AWS IAM policy attached directly.

Answer

Enable AWS IAM database authentication on the Snowflake account.

Answer

Configure the stage with AWS_KEY_ID and AWS_SECRET_KEY parameters.

Question 3

A data engineer runs a query that joins a large fact table to a small dimension table, but the Query Profile shows a Cartesian join instead of the intended inner join. The join predicate in the SQL is `ON fact.dim_id = dim.id`. Which action will most reliably correct the plan while preserving the query's result?

Accepted Answer

Verify the data types of `fact.dim_id` and `dim.id` are compatible and add an explicit CAST so the join predicate uses matching types.. The optimizer can only build a hash join when it recognizes a valid equality predicate between compatible columns. If the join columns have mismatched or implicitly coercible data types, Snowflake may not recognize the predicate and may produce a Cartesian join. Explicitly aligning the data types with CAST restores the recognized equality, allowing the intended inner join and preserving the query's result.

Answer

Increase the warehouse size from X-SMALL to LARGE to give the optimizer more compute resources.

Answer

Rewrite the join as `ON fact.dim_id = dim.id AND fact.dim_id IS NOT NULL`.

Answer

Add the `USE_CACHED_RESULT = TRUE` parameter to the session before executing the query.

Question 4

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 5

A data engineer notices that a query performing a large aggregation is spilling to remote disk. The warehouse is a 2XL multi-cluster warehouse with maximum clusters set to 4. The engineer wants to reduce spilling and improve performance without increasing the warehouse size. Which action should the engineer take?

Accepted Answer

Rewrite the query to use a smaller aggregation or break it into stages.. Remote disk spilling occurs when the aggregation operation cannot fit its intermediate results in memory. The most direct way to reduce spilling without increasing warehouse size is to reduce the memory demand of the query itself. Rewriting the query to perform smaller aggregations or breaking it into stages can lower the peak memory usage, allowing the operation to complete within the available memory. This addresses the underlying issue rather than trying to work around it with additional resources.

Answer

Increase the maximum cluster count to 8.

Answer

Enable the query acceleration service for the warehouse.

Answer

Add a clustering key on the group by columns.

Question 6

A data engineer needs to unload data from a Snowflake table to an external stage that references an Amazon S3 bucket. The engineer wants to ensure that the unloaded files are encrypted using a customer-managed key in AWS KMS. Which COPY INTO <location> parameter should be used to specify the KMS key?

Accepted Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS' MASTER_KEY = 'arn:aws:kms:...'). To unload data to an external S3 stage with a customer-managed KMS key, the ENCRYPTION parameter must include TYPE = 'AWS_SSE_KMS' and MASTER_KEY set to the KMS key ARN. This ensures server-side encryption with the specified key. Other options either use S3-managed keys, Snowflake-managed encryption, or omit the necessary key parameter, failing to meet the requirement.

Answer

ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')

Question 7

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 8

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 9

A data engineer is configuring a Snowflake storage integration to allow Snowflake to access an external S3 bucket. The engineer needs to ensure that the integration has the necessary permissions to read and write data. Which two actions must the engineer perform? (Choose two.)

Accepted Answer

Grant the IAM role permissions to access the S3 bucket, such as s3:GetObject, s3:PutObject, s3:ListBucket, and s3:GetBucketLocation.. To configure a storage integration for S3, you must create an IAM role in AWS that trusts Snowflake's AWS account and has the necessary S3 permissions. These two actions are essential for Snowflake to assume the role and access the bucket. Other options are either insecure or optional. The integration uses temporary credentials, so storing access keys is not needed.

Answer

Set the STORAGE_ALLOWED_LOCATIONS parameter in the storage integration to restrict access to specific S3 paths.

Answer

Store AWS access keys and secret keys in the storage integration definition for authentication.

Answer

Configure the S3 bucket policy to allow public access so Snowflake can read the data.

Question 10

A data engineer is configuring a Snowpipe to automatically ingest files as they arrive in an external stage. They need to set up event notifications from the cloud provider to Snowflake. Which two components are required to enable this automated ingestion? (Choose two.)

Accepted Answer

A storage integration object in Snowflake. To enable automated Snowpipe ingestion from an external stage, two key Snowflake objects are required: a storage integration to securely access the external stage, and a notification integration to receive event notifications from the cloud provider. The storage integration provides the necessary permissions, while the notification integration allows Snowpipe to be triggered by events. Other components like file formats or tasks are not required for the event notification setup.

Answer

A named file format object

Answer

A task to periodically check for new files

Answer

An external function to process the notifications

Question 11

A data analyst wants to query data stored in an external stage (Amazon S3) without loading it into a Snowflake table. The analyst creates an external table pointing to the stage. Which statement accurately describes how the data is accessed?

Accepted Answer

The external table reads data directly from the external stage at query time, and the data remains in the external storage location.. External tables provide a way to query data stored in external stages without loading it into Snowflake. They act as metadata pointers to files in the external stage, and queries read the data directly from those files. This allows querying data in place, which is useful for infrequent access or when data must remain in external storage.

Answer

The external table automatically loads all data into Snowflake's storage upon creation, so queries run against local micro-partitions.

Answer

The external table caches the data in the virtual warehouse's local SSD, and subsequent queries do not access the external stage again.

Answer

The external table requires a materialized view to be created on top of it before any queries can return results.

Question 12

A developer needs to flatten a VARIANT column named payload that contains a nested JSON array of order line items into individual rows, preserving the parent order attributes alongside each line item. Which Snowflake construct accomplishes this in a single SELECT statement?

Accepted Answer

A LATERAL FLATTEN of the payload:line_items array joined back to the parent row.. LATERAL FLATTEN is Snowflake's dedicated mechanism for exploding semi-structured arrays and objects into relational rows. Used laterally, it correlates each element with its parent row, so order attributes remain available alongside each line item. This yields a fully relational result set from nested JSON in one statement without manual recursion or aggregation.

Answer

A PARSE_JSON call applied to the payload column in the SELECT list.

Answer

A recursive common table expression that walks the JSON hierarchy level by level.

Answer

A GROUP BY on the VARIANT column with an ARRAY_AGG of the line items.

Question 13

A provider shares a secure view named SALES_VIEW with a consumer. The view references a table in the provider's database. The provider later renames the underlying table. What is the impact on the consumer's access to SALES_VIEW?

Accepted Answer

The consumer's access is unaffected because the view continues to resolve to the renamed table.. Views in Snowflake reference base objects by internal object identifiers, not by name. Renaming a table therefore does not invalidate dependent views, and a shared secure view continues to function for the consumer. The consumer's access remains intact without any action on either side, which is the key behavior tested here.

Answer

The consumer must recreate the database from the share to see the renamed table through the view.

Answer

The consumer loses access because the view definition becomes invalid and must be re-created.

Answer

The consumer sees an error only if the renamed table is also granted directly in the share.

Question 14

A data engineer is loading data from a local file system into a Snowflake table using the PUT command to an internal stage, followed by COPY INTO. The engineer notices that some rows are rejected due to data type mismatches. The engineer wants to capture the rejected records and continue loading valid rows. Which COPY INTO option should be used to achieve this?

Accepted Answer

ON_ERROR = 'CONTINUE'. To load valid rows while capturing rejected records, ON_ERROR = 'CONTINUE' is the appropriate option. It allows the load to proceed, skipping erroneous rows, and the rejected records can be retrieved from the COPY_HISTORY or by using VALIDATION_MODE. Other options either skip the entire file, only validate, or abort the statement, none of which meet the requirement to continue loading valid rows.

Answer

ON_ERROR = 'SKIP_FILE'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Question 15

An analyst runs the same dashboard query every morning at 08:00. The query reads from tables that are loaded by an ELT job finishing at 07:30. The analyst complains that the first run takes 40 seconds while subsequent identical runs during the day return in under a second. The data in the tables does not change between the first run and the later runs. Which mechanism explains the speedup?

Accepted Answer

The result cache returned the previously computed result because the query text and underlying data were unchanged.. When an identical query is submitted and the underlying data has not changed, Snowflake returns the cached result instead of re-executing the query. The first morning run after the ELT job populates the cache with a 40-second result; every subsequent identical run within the 24-hour window returns that stored result in under a second. Local disk caching, metadata, and query acceleration all still require execution work.

Answer

The virtual warehouse's local disk cache retained the table's micro-partitions from the first run.

Answer

The warehouse's query acceleration service automatically rewrote the query into a faster form after the first execution.

Answer

The metadata cache allowed the optimizer to skip scanning because it already knew the aggregate values.

Question 16

A data engineer is investigating a slow-running query. The Query Profile shows a high percentage of time spent in the TableScan operator and a large number of partitions scanned. The engineer wants to reduce the number of partitions scanned by using pruning. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Apply filters early in the query and avoid wrapping filter columns in functions.. Partition pruning is driven by filters that the optimizer can use against micro-partition metadata. Filtering on clustering key columns and avoiding functions on filter columns allow Snowflake to skip irrelevant micro-partitions, directly reducing the number of partitions scanned.

Answer

Increase the size of the virtual warehouse to reduce the number of partitions scanned.

Answer

Use the SEARCH function in the WHERE clause for all string filters.

Answer

Convert the table to a transient table to enable automatic pruning.

Question 17

A data engineer runs a query that joins a large fact table with a small dimension table. The Query Profile shows a Join operation with an exploding number of rows and significant spilling to remote disk. The engineer notices the join condition uses a function on the join key of the large table. Which action is most likely to improve performance?

Accepted Answer

Rewrite the join to avoid applying a function to the join key of the large table.. The function on the join key prevents the optimizer from using an efficient join strategy and can cause a row explosion. Rewriting the join to compare raw columns allows hash join and pruning, directly fixing the root cause. Larger warehouses, clustering on the raw column, or caching do not address the expression in the join predicate.

Answer

Add a clustering key on the join column of the large table.

Answer

Increase the size of the virtual warehouse to a larger multi-cluster warehouse.

Answer

Enable the USE_CACHED_RESULT parameter for the session.

Question 18

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table. The JSON contains nested arrays and objects. Which Snowflake feature should be used to flatten the arrays into separate rows while preserving the parent-child relationship?

Accepted Answer

LATERAL FLATTEN. LATERAL FLATTEN is designed to explode nested arrays and objects into rows while maintaining the association with the source row. It is the correct tool for transforming semi-structured data into a relational format. The other functions either construct objects, aggregate into arrays, or parse strings, none of which expand arrays into multiple rows.

Answer

PARSE_JSON

Answer

ARRAY_AGG

Answer

OBJECT_CONSTRUCT

Question 19

A provider shares a database with a consumer using a direct share. The consumer reports that queries against the shared database fail with an error indicating the database does not exist, even though the share was successfully created and granted to the consumer account. The provider confirmed that the share contains the necessary tables and that the consumer account has been added to the share. What is the most likely cause of the issue?

Accepted Answer

The consumer has not created a database from the share using CREATE DATABASE ... FROM SHARE.. For a consumer to access a direct share, they must create a database from the share using CREATE DATABASE ... FROM SHARE. The provider's role is to create the share, grant privileges on objects to the share, and add the consumer account. The consumer must then mount the share by creating a database. Without this step, the shared database is not visible, leading to errors when querying.

Answer

The provider did not grant the USAGE privilege on the database to the consumer's role.

Answer

The consumer's account is in a different region than the provider's account, and cross-region sharing is not enabled.

Answer

The provider did not include a secure view in the share, so the consumer cannot access the tables.

Question 20

A data engineer is using the COPY INTO command to unload data from a Snowflake table to an external stage (Amazon S3). The engineer wants to ensure the unloaded files are encrypted and can be decrypted by the target system. Which TWO statements are true regarding the encryption of unloaded files? (Choose two.)

Accepted Answer

Client-side encryption with a master key requires the target system to use the same master key to decrypt the files.. For unloading to an external stage, Snowflake supports both server-side encryption (e.g., SSE-S3, SSE-KMS) and client-side encryption with a master key. Server-side encryption relies on the cloud provider's encryption, while client-side encryption encrypts data before upload, requiring the target system to have the master key. Both methods ensure data is encrypted at rest and can be decrypted by authorized parties.

Answer

Unloaded files are always encrypted with AES-256 regardless of the encryption settings on the external stage.

Answer

By default, Snowflake uses client-side encryption with a 128-bit key to encrypt unloaded files.

Answer

If no encryption is specified for the external stage, unloaded files are not encrypted and are stored in plaintext.